Friday, 23 March 2018

Java JDBC Steps to Connect to Database

JDBC API provides a set of interfaces and classes for connecting to DB, creating SQL statement, executing created SQL statement in database, returning the results and processing that ResultSet.

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

  • Loading driver
  • 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 and 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. Loading driver or Registering driver class

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

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).

Example

For 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.

For 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 an object of Statement. Statement 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 -

  1. To create a table employee
    statement.executeUpdate(“CREATE TABLE employee (id int(11), name varchar(35), age int(11))”);
    
  2. 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();
       }
    }
}

Here note that Class.forName("com.mysql.jdbc.Driver"); statement is also 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

  1. Java JDBC overview - JDBC Tutorial
  2. Types of JDBC Drivers
  3. PreparedStatement Interface in Java-JDBC
  4. DataSource in Java-JDBC
  5. Insert\Update Using JDBCTemplate in Spring Framework

You may also like-

  1. How to remove duplicate elements from an ArrayList in Java
  2. ConcurrentHashMap in Java
  3. Lambda Expressions in Java 8
  4. Transient in Java
  5. Select query using NamedParameterJDBCTemplate in Spring framework
  6. Race condition in Java multi-threading
  7. BigDecimal in Java
  8. Ternary operator in Java

No comments:

Post a Comment