Java JDBC Steps to Connect to Database

JDBC API provides a set of interfaces and classes for performing the following tasks-

Connecting to database
Creating SQL statement
Executing created SQL statement in database
Returning the results
Processing that ResultSet

Steps for connecting any Java application to DB and fetching results using JDBC can be summarized as follows

Registering driver class
Creating connection to DB
Creating Statement
Executing Query
Processing ResultSet
Closing connection

Interfaces provided by JDBC for these steps are-

Driver (That is the interface implemented by the database vendors to provide a JDBC driver for specific databases)
Connection
Statement
ResultSet

Steps for connecting to Database using JDBC

Steps for connecting to Database using JDBC are explained here along with examples to connect to differenct DBs like Oracle, MySql, DB2.

1. Registering driver class

First thing you need to do is to register JDBC driver for the DB you are connecting to. You can use forName() method provided by class Class to load the JDBC driver in order to register it.

General form using Class.forName()

class.forName(“JDBC Driver Class”);

Using registerDriver() method

You can also use registerDriver() method provided by DriverManager class to load the appropriate driver. Note that it is a static method.

General form using registerDriver() method

DriverManager.registerDriver(Driver class object);

Driver classes for some of the databases are as follows -

MySql– com.mysql.jdbc.Driver (You need to download MySQL Connector/J jar mysql-connector-java-5.1.39.jar (Please check for the latest version) which will have the JDBC driver).
Oracle– oracle.jdbc.driver.OracleDriver (You need to download ojdbc8.jar or higher version for Java 8 and JDBC 4.2).
DB2– com.ibm.db2.jcc.DB2Driver (You need to download db2jcc.jar for the DB2 JDBC driver).

Loading Oracle driver using Class.forName() method-

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

JVM automatically loads the classes that are used in the program. Since the driver class is not explicitly used in the program that’s why JVM won’t load it automatically. That is the reason you need to load driver class using class.forName() that way you explicitly tell JVM to load this driver class.

Loading MySql driver using resgisterDriver method-

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

If you are using JDBC 4.x (Version Java 6 or higher) then actually you don’t need to explicitly load the JDBC driver. As Automatic loading of JDBC drivers is supported from JDBC 4 you just need to have the appropriate jar in the class path. So loading driver step is optional from Java 6 onwards make sure that automatic loading is supported by the driver you are using.

2. Creating connection object

Once you have registered the driver, second step is to open a connection to the DB. For that you can use the static method getConnection() of the java.sql.DriverManager class.

General form of getConnection method

DriverManager.getConnection(DB_URL, DBuser, password)

URL Pattern for some of the databases

Oracle– jdbc:oracle:<drivertype>:@<database>
As example- Connecting user scott with password tiger to a database with SID orcl through port 1521 of host myhost, using the Thin driver.
```
Connection connection = DriverManager.getConnection
     ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");
```
MySQL– jdbc:mysql://hostname:portnumber/dbName
As example- Connecting user root with password admin to a database test through port 3306 of host localhost.
```
Connection connection = DriverManager.getConnection
     ("jdbc:mysql://localhost:3306/test", "root", "admin");
```
DB2– jdbc:db2://hostname:portnumber/dbName
As example- Connecting user dbadmin with password dbadmin to a database mydb through port 5021 of host myhost.
```
Connection connection = DriverManager.getConnection
     ("jdbc:db2://myhost:5021/mydb","dbadmin","dbadmin");
```

3. Creating Statement object

Once Connection object is created that can be used to create a Statement object. This object is needed for specifying the SQL statement that has to be executed by the DB.

Statement statement = connection.createStatement();

4. Executing Query

To execute a query you need to call execute() method of the Statement class.

You can call executeUpdate(String sql) method for INSERT, DELETE, UPDATE or DDL (Data Definition Language) SQL statements. This method returns an integer representing the number of rows affected by the SQL statement so you will know how many rows are inserted, deleted or updated.

You can call executeQuery(String SQL) method for SELECT sql queries. This method returns a ResultSet.

As example-

To create a table employee

statement.executeUpdate(“CREATE TABLE employee (id int(11), name varchar(35), age int(11))”);

To get data for all employees

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

5. Processing ResultSet

Once the query is executed and you have the ResultSet you access the data in a ResultSet object 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. You can use next method to move to the next row of the ResultSet. There are various getter methods based on data type to get the value of the current row.

As example-

If you want to iterate the ResultSet returned from the above query for getting all the employees.

while(rs.next()){
 System.out.println("id : " + rs.getInt("id") + " Name : " + rs.getString("name") + " Age : " + rs.getInt("age"));
}

In the example getter method are using the column labels to retrieve the values you can also use the column indexes to get the values, index starts from 1.

while(rs.next()){
 System.out.println("id : " + rs.getInt(1) + " Name : " + rs.getString(2) + " Age : " + rs.getInt(3));
}

6. Closing the connection

Once you have the processed the ResultSet you can close the connection.

connection.close();

It’s better to close an opened connection in a finally block. That ensures that the connection is closed even if there is an exception in the code.

JDBC Connection example

Let’s put all these steps together in a Java example program connecting to MySQL DB using JDBC. DB schema is netjs 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 JDBCCon {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
      // Another way
      //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
   
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                   "root", "admin");
      // creating Statement
      Statement stmt = connection.createStatement();  
   
      // Executing Query
      ResultSet rs = stmt.executeQuery("Select * from Employee");

      // Processing Resultset
      while(rs.next()){
         System.out.println("id : " + rs.getInt("id") + " Name : " 
          + rs.getString("name") + " Age : " + rs.getInt("age")); 
      }
    
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
    }finally{
      if(connection != null){
         //closing connection 
         try {
           connection.close();
         } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
         }
      }
    }
  }
}

You can put DB credential information in a properties file and read it from there. Refer How to read Properties file in Java to see how to read properties file in Java.

JDBC Connection to DB using try-with-resources

If you are using Java 7 or above, you can use a try-with-resources statement, when connecting to DB using JDBC, to automatically close Connection, Statement, and ResultSet objects, regardless of whether an SQLException has been thrown.

If we modify the above code to include try-with-resources then we can get rid of finally block used to close the connection.

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

public class JDBCCon {
  public static void main(String[] args) {
    try(Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/netjs", "root", "admin"))
    {
      // creating Statement
      Statement stmt = connection.createStatement();  

      // Executing Query
      ResultSet rs = stmt.executeQuery("Select * from Employee");
     
      // Processing Resultset
      while(rs.next()){
         System.out.println("id : " + rs.getInt("id") + " Name : " 
               + rs.getString("name") + " Age : " + rs.getInt("age")); 
      }
    }catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
}

Note that Class.forName("com.mysql.jdbc.Driver"); statement is excluded as that is also optional, from JDBC 4.0 drivers which are in the class path are automatically loaded.

That's all for this topic Java JDBC Steps to Connect to DB. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

Related Topics

You may also like-

Tech Tutorials

Wednesday, June 12, 2024