JDBC Quick Start

This article provides a quick introduction to JDBC (Java Database Connectivity) API. The JDBC API enables a Java application to connect to a database, send queries to the database and process the results received in a database query.

For the purpose of this tutorial let us use PostgreSQL database. PostgreSQL is an open source object-relational database management system (ORDBMS) and is free to use for commercial application.

PostgreSQL Installation

• Download PostgreSQL from this link http://www.enterprisedb.com/downloads/postgres-postgresql-downloads and run it.
• Installation is straight forward. Just follow the wizard.

To connect with a database JDBC API requires the drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

PostgreSQL JDBC Driver

• Download JDBC driver from this link http://jdbc.postgresql.org/download.html and add the jar to your CLASSPATH of the project. If you are using Java 7 use JDBC41.

PostgreSQL setup a database

• Invoke the "pgAdmin" application which comes with the PostgreSQL installation.
• Create a new database “mytestdb”. Right click "Databases à Create New Database".

Create Database

• Expand Schemas under mytestdb. Go to public and right click on Tables to add “New tables”.

Create Table

• Add the required columns.

Create Columns

• Create the table.
• Insert values using the SQL editor. 

Insert values using SQL Editor

Now we are ready to go.

JDBC Basics Example

Let us try a simple example to connect to the PostgreSQL database we created and select the records inserted from a Java application.

The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers and attempts to establish a connection with the give database URL, username and password.

The Statement object is used for executing SQL statements and to get the results.

The ResultSet object maintains a cursor to the current row of data and provides the getter methods to access the column values.

package com.sourcetricks.jdbcexamples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTest {

  public static void main (String[] args) {
    final String dbUser = "postgres";
    final String dbPass = "test123";
    final String dbUrl = "jdbc:postgresql://localhost:5432/mytestdb";

    try ( Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass); 
          Statement stmt = connection.createStatement(); ) 
    {   
      // Select query
      ResultSet rs = stmt.executeQuery("SELECT * from employee");
      while ( rs.next() ) {
        System.out.println(rs.getInt(1)); // Note starts from 1
        System.out.println(rs.getString(2));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } 
  }
}

In the next chapter on JDBC JDBC Database Access Examples we would see more specific examples on using Statements, PreparedStatement, ResultSet etc.