JDBC Database Connection Steps

In this post we’ll go through the steps to connect your Java application to the Database using JDBC. Steps that are to be followed are as given below-

Register the appropriate driver for the DB
Create connection object by passing Database URL, DB user and DB password.
Create statement
Execute query
Process ResultSet (If you are getting records from DB)
Close connection

Register driver class

First thing is to register JDBC driver for the DB you are connecting to. Once the driver is registered it is loaded into the memory.

You can register a driver in one of two ways-

By using Class.forName()
By using Driver.registerDriver()

Using Class.forName()

You have to pass the driver class name as a string with the method to dynamically load the driver's class file into memory. General form is as follows-

Class.forName(“JDBC Driver Class”);

Registering Oracle driver. Requires ojdbc8.jar or higher in classpath.

Class.forName(“oracle.jdbc.driver.OracleDriver”);

Registering MySQL driver. Requires MySQL Connector/J jar in classpath.

Class.forName(“com.mysql.cj.jdbc.Driver”);

Registering DB2 driver. Requires db2jcc.jar in classpath.

Class.forName(“com.ibm.db2.jcc.DB2Driver”);

Using Driver.registerDriver()

You can also use static method DriverManager.registerDriver() to register a driver, you need to pass Driver class instance as a method argument. General form is as follows-

DriverManager.registerDriver(Driver class instance);

Registering Oracle driver.

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Registering MySQL driver.

DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());

Registering DB2 driver.

DriverManager.registerDriver(new com.ibm.db2.jcc.DB2Driver());

Note that JDBC 4.0 onward you don’t need to explicitly register the JDBC driver. If you have the appropriate jar in the classpath JDBC driver manager can detect and load the driver automatically. So, explicitly registering driver step is optional from Java 6 onward.

Creating connection object

Second step is to establish a connection to the DB. You can use the static method getConnection() of the java.sql.DriverManager class for doing that. There are 3 overloaded getConnection() methods.

getConnection(String url)
getConnection(String url, String user, String password)
getConnection(String url, Properties info)

Here URL is the DB URL that points to the DB server. How database URL is created differs a bit for different databases. Here are DB URL patterns for some of the databases-

Oracle – jdbc:oracle:<drivertype>:@hostname:port:databaseSID

For example you have a DB at hostname myhost, Oracle listener is configured to listen on port 1521, user name is dbuser, password is dbpwd, database SID is emp then the connection for

Connection connection = DriverManager.getConnection
     ("jdbc:oracle:thin:@myhost:1521:emp", "dbuser", "dbpwd");

MySQL– jdbc:mysql://hostname:port/dbName

For example connecting user dbuser with password dbpwd to a DB emp listening at port 3306 of host localhost.

Connection connection = DriverManager.getConnection
     ("jdbc:mysql://localhost:3306/emp", "dbuser", "dbpwd");

DB2– jdbc:db2://hostname:port/dbName

For example connecting user dbuser with password dbpwd to a database emp through port 5021 of host myhost.

Connection connection = DriverManager.getConnection
     ("jdbc:db2://myhost:5021/emp","dbuser","dbpwd");

Creating statement object

The createStatement() method of Connection interface is used to create Statement object. Using statement object you specify the SQL query that has to be executed by the DB.

Statement statement = connection.createStatement();

Execute Query

To execute a query call execute method of the Statement class. There are several execute methods in Statement interface some of them are mentioned here-

execute(String sql)- Executes the given SQL statement, which may return multiple results.
executeQuery(String sql)- Executes the given SQL statement, which returns a single ResultSet object.
executeUpdate(String sql)- Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Execute query examples

To update Student record in Student table

statement.executeUpdate("UPDATE Student SET lastname='sharma' where rollno=32");

To get data for all employees

ResultSet rs = statement.executeQuery("Select * from Employee");

Processing ResultSet

If query execution returns a ResultSet then you have to access the data in that ResultSet through a cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. In ResultSet there is a next method to move to the next row of the ResultSet and there are several getter methods for different datatypes to get values from a record.

For example accessing ResultSet returned from the query to get all employees.

while(rs.next()){
	int empId = rs.getInt("id");
	String empName = rs.getString("EMP_NAME");
	String dept = rs.getString("DEPT");
	int age = rs.getInt("AGE");
	
 	System.out.println("id: " + empId  + " Name: " + empName + " Department: " + dept + " 	Age : " + age );
}

Closing Connection object

You can close the connection using the close() method of Connection interface.

connection.close();

It is a good practice to explicitly close connections to the database to free the resources. As a good practice close an opened DB connection in a finally block. That ensures that the connection is closed even if there is an exception in the code.

JDBC Java example

Here is a Java JDBC example which shows all the JDBC Database connection steps, create statement, execute query, process ResultSet and close the connection. MySQL DB is used in the example, schema it connects to is knpcode and table is employee.

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

public class JDBCDemo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Load driver
      Class.forName("com.mysql.cj.jdbc.Driver");
      // connection object
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
                   "root", "admin");
      // create Statement object
      Statement statement = connection.createStatement(); 
      // Executing Query
      ResultSet rs = statement.executeQuery("SELECT * from employee");
      // Processing Resultset
      while(rs.next()){
        System.out.println("id: " + rs.getInt("id") + 
          " First Name: " + rs.getString("FIRST_NAME") + 
          " Last Name: " + rs.getString("LAST_NAME")+ 
          " Dept: " + rs.getString("DEPARTMENT")); 
      }
    }catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //close connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
  }
}

That's all for the topic JDBC Database Connection Steps. If something is missing or you have something to share about the topic please write a comment.

You may also like

KnpCode

August 5, 2022