JDBC DriverManager, JDBC PreparedStatement And Statement

This tutorial explains what is JDBC DriverManager and its methods, JDBC PreparedStatement, Statement  and how to use them in Java applications:

In the JDBC Connection tutorial of the JDBC tutorial series, we learned the basic steps to a database with examples.

In this tutorial, we will learn about what is JDBC DriverManager and how we can use it in a Java program. We will also see details about Connection Interface, Statement Interface, PreparedStatement, and CallableStatement interfaces, and what are the commonly used methods in JDBC.

JDBC Driver Manager And Its Methods

Driver Manager Class is in the java.sql package and it extends Object class. Object class is the parent class of all the classes in Java. The main purpose of Driver Manager in JDBC is to provide the basic services to manage the set of drivers that are available to use in the application.

Concurrently, multiple drivers can be accessed by the application; you just need to mention the JDBC driver using URL. The main job of Driver Manager is to register and load the drivers and establish the connection. It acts as an interface between the user and the drivers.

It tracks the details of the driver that are available and handles establishing the connection between the database and an appropriate driver. It maintains the single instance of each registered driver.

DriverManager class has the following methods:

The getConnection() method throws SQLException when there is an error that occurred during an interaction with the Database. We can handle the exception either by using the Try-Catch block or by adding throws keyword with method Signature.

In the below DriverManager Example program, we have used getDriver, getLoginTimeout and setLoginTimeout methods. Here, we are using Oracle DB and Java 8 version.

>> Click here to download the Oracle software.

>> Click here to download the Java version 8.

It has the step by step Java installation process.

Let us see an example that illustrates how to connect to Oracle DB and get the driver details and set the login timeout as 1000.

DriverManager Example Program

package com.STH.JDBC;

import java.sql.DriverManager;
import java.sql.SQLException;

public class DriverManagerExample {	
         public static void main(String[] args) throws ClassNotFoundException, 
SQLException {
		// TODO Auto-generated method stub
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		//Get Driver method
		System.out.println(" Get Driver  =  " + 
DriverManager.getDriver("jdbc:oracle:thin:system/pass123@localhost"));
		//Print default Login Timeout Value
		System.out.println("Default Login Timeout = " + 
DriverManager.getLoginTimeout());
		//Setting Login Timeout Value as 1000ms
		System.out.println("Set Login Timeout");
		DriverManager.setLoginTimeout(1000);
		//Printing Login Timeout Value After set the value
		System.out.println(" After Setting time Max Login Timeout = " + 
DriverManager.getLoginTimeout());
	}
}

Output:

Explanation:

In the above program, first, we are getting the driver details using getDriver() method by passing the driver URL. The default value of the Login Timeout of the connection is 0. We can change the timeout value using the setLoginTimeout() method by passing the “seconds” value as a parameter.

After that by using the getLoginTimeout() method, we can get the Maximum Login Timeout and the value should be the one which we have set recently.

Next, we will move to Connection Interface.

Connection Interface

Connection Interface is also available in the java.sql package. It represents a session between the application and the DB. It helps to establish a connection with DB. The SQL statements that you want to execute and get the results from DB all that happens within the context of a connection.

Connection interface has a lot of methods including transaction management, maintaining DB sessions, and creating a statement. You can get a connection object using the getConnection() method of the DriverManager class. Using the Connection object, we can get a statement object and information about the Database (DatabaseMetadata).

List of common methods used in Connection Interface are as follows:

Here, all the above methods throws the SQLException when there is an error that occurred while interacting with DB i.e retrieving data from the database, updating, or deleting while performing some operations in DB from Java program using JDBC.

In the following Connection Interface program, the Statement interface has also been used to perform commit and rollback operations using the commit() and rollback() methods which are in Connection Interface.

Connection Example Program

package com.STH.JDBC;

import java.sql.*;

public class Sample_JDBC_Program {	

          public static void main(String[] args) throws ClassNotFoundException, 
SQLException {
	        String QUERY = "select * from employee_details"; //Select Query 
to get the Data from employee_details table
		Boolean autoCommit;
		String update_query = "update employee_details set 
email='martinL@gmail.com' where empNum = 1001"; 
		//Update query to set the email id for the employee whose empNUM is 1001
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		try(Connection conn = 
DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				Statement statemnt1 = conn.createStatement();
				ResultSet rs1 =null;
				//Checking whether the SELECT query is executed successfully or not
				Boolean bool = statemnt1.execute(QUERY);
				System.out.println("Execution status of query = "+
 bool);
				autoCommit = conn.getAutoCommit();
				System.out.println(" Default AutoCommit value of the
 Connection = "+ autoCommit);
				
				rs1 = statemnt1.executeQuery(QUERY); 
			
				//Executed the SELECT Query
				System.out.println("Getting the data from employee_details table");
					while(rs1.next())
					{
						int empNum = rs1.getInt("empNum");
						String lastName = rs1.getString("lastName");
						String firstName = rs1.getString("firstName");
						String email = rs1.getString("email");
						String deptNum = rs1.getString("deptNum");
						String salary = rs1.getString("salary");
						System.out.println(empNum + "," 
+lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary);
					}
				 
				statemnt1.close();
				rs1.close();
				//Set the autoCommit value of the connection to FALSE
				System.out.println("Setting the AutoCommit value as  FALSE");
				conn.setAutoCommit(false);
				autoCommit = conn.getAutoCommit();
				System.out.println("AutoCommit value of the
 Connection = "+ autoCommit);
				//Creating Statement to execute the update query
				statemnt1 = conn.createStatement();
				System.out.println("Executing Update query using 
executeUpdate method");
				int return_rows = statemnt1.executeUpdate(update_query);
				System.out.println("No. of Affected Rows = "+ return_rows);
				//Getting data after Updation 
				rs1 = statemnt1.executeQuery(QUERY); 
				{	
					while(rs1.next())
					{
						int empNum = rs1.getInt("empNum");
						String lastName = rs1.getString("lastName");
						String firstName = rs1.getString("firstName");
						String email = rs1.getString("email");
						String deptNum = rs1.getString("deptNum");
						String salary = rs1.getString("salary");
						System.out.println(empNum + "," 
+lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary);
					}
				} 
				//Rollback the transaction
				System.out.println("RollBack the Transaction");
				conn.rollback();
				//Commit after Rollback
				System.out.println("Commit After Rollback");
				conn.commit();
				statemnt1.close();
				statemnt1 = conn.createStatement();
				System.out.println("Display Data After Commit - The
 Data has not changed because of Rollback");
				rs1 = statemnt1.executeQuery(QUERY); 
				{	
					while(rs1.next())
					{
						int empNum = rs1.getInt("empNum");
						String lastName = rs1.getString("lastName");
						String firstName = rs1.getString("firstName");
						String email = rs1.getString("email");
						String deptNum = rs1.getString("deptNum");
						String salary = rs1.getString("salary");
						System.out.println(empNum + ","
 +lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary);
					}
				} 
				
				
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
	}

Output:

Explanation:

#1) We are verifying whether the query has executed successfully or not using the execute() method. This method will return either true or false. If the given query in the parameter is executed successfully, then it will return true, else false.

#2) The default value of the Auto Commit method is True. It means that even if we didn’t call the commit() method to commit the data, it will automatically commit the values after closing the Connection. In some cases we may don’t want to commit the transaction, we can change the value to False using the setAutoCommit() method.

#3) After that, we are displaying the data of the EMPLOYEE_DETAILS table to check what data is present in the table.

#4) We have changed the default of AutoCommit to False using the setAutoCommit() method.

#5) Using the executeUpdate() method, we are updating the email Id of the Employee whose EMPNUM is 1001 to martinL@gmail.com. This method will return the number of rows affected due to the DB operation.

#6) Using the executeQuery() method, we are displaying the data in the EMPLOYEE_DETAILS table and checking whether the email ID of the EMPNUM = 1001 has been updated or not.

#7) We have used the rollback() method to undo the changes till the last commit. After the Rollback() operation, we have called the commit() method to make the changes to be committed so that we won’t lose the data after closing the connection. Now the email Id of the EMPNUM = 1001 has been changed to the initial mail id Emp11@gmail.com before the program starts.

From this program, we got to know about how to use executeQuery(), setAutoCommit(), executeUpdate(), Rollback() and commit() methods.

JDBC provides Statement, PreparedStatement, and CallableStatement interfaces for executing queries. We will use Statement for general purposes, PreparedStatement for Parameterized queries, and CallableStatement for executing stored procedures.

Now let’s discuss the Statement Interface and its methods which are commonly used.

Statement Interface

Statement Interface also presents in the java.sql package. It is used to execute the SQL statements to update or query the database. If you want to use a statement object, you first need to create a statement. Connection.createStatement() method is used to get a Statement object because we can’t create the object of the interface.

Multiple objects can be created by using a single connection object. After the Statement object is created, you can execute the query.

Commonly used Statement Interface methods are listed below:

Statement Example Program

In the below Statement Interface example program, we have implemented the ExecuteBatch() method with using the EMPLOYEE_DETAILS Table.

Data in EMPLOYEE_DETAILS Table before executing the program:

It has only 5 entries.

Java Program:

package com.STH.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
	
public class Statement_ExecuteBatch_Example {

	public static void main(String[] args) throws ClassNotFoundException,
 SQLException {
		
		//Inserting the following 3 rows in EMPLOYEE_DETAILS Table
		String insert_query1 = "insert into employee_details
 values(1006,'Patterson','Tony','TP@gmail.com',6,20000)";
		String insert_query2 = "insert into employee_details
 values(1007,'Potter','Harry','HP@gmail.com',7,21000)";
		String insert_query3 = "insert into employee_details 
values(1008,'Wong','Jane','JW@gmail.com',8,22000)";
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		try(Connection conn = 
DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				Statement statemnt1 = conn.createStatement();
				statemnt1.addBatch(insert_query1);
				statemnt1.addBatch(insert_query2);
				statemnt1.addBatch(insert_query3);
				int No_of_Afffected_Rows[]=   statemnt1.executeBatch();
				//After inserting the data
				System.out.println("No of rows affected = "
 +No_of_Afffected_Rows.length);
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

Output:

Data in EMPLOYEE_DETAILS Table after executing the program:

Now EMPLOYEE_DETAILS table has 8 employee details.

Explanation:

In this program, we have inserted 3 new employee details in the EMPLOYEE_DETAILS table using the same connection and statement objects. We can achieve this by using the executeBatch() method. For that, first, we need to pass the appropriate queries as parameters to the addBatch() method of the Statement interface.

We have used the below the 3 statements to pass the queries to the addBatch() method.

statemnt1.addBatch(insert_query1);
statemnt1.addBatch(insert_query2);
statemnt1.addBatch(insert_query3);

After passing the parameters, we have executed the executeBatch() method. It has inserted the 3 entries which were passed as parameters to addBatch() method. The executeBatch() executes the queries and returns the affected rows in an array format.

After the execution of the program, when we check the EMPLOYEE_DETAILS tables it has the added new entries in it. Now there are 8 entries in the EMPLOYEE_DETAILS table.

JDBC PreparedStatement

It is also in the java.sql package. It is used to execute the Parameterized SQL statements. We can pass the value in parameter to do the operations in the Database. To get the PreparedStatement object, we need to call connection.prepareStatement().

Some advantages of using PreparedStatement are:

1. It is easy to add the parameters in the SQL statement.
2. It is easy to reuse the PreparedStatement with the new parameters.
3. The performance of the executed statements may increase.
4. Batch updates can be easier.
5. Queries that are written in PreparedStatement will prevent common SQL Injection attacks.
6. It allows us to write a dynamic query.
7. All JDBC driver doesn’t support precompilation of SQL query, in that case, the query doesn’t send to DB when you call using prepareStatement(..) method, instead, when you execute PreparedStatement query it would send data to DB.

PreparedStatement Example

Data in EMPLOYEE_DETAILS Table before executing the Program:

Program:

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PrepareStatementExample1 {	

        public static void main(String[] args) throws SQLException, 
ClassNotFoundException {
		
		//Insert Query string
		String insert_query = "insert into employee_details 
values(?,?,?,?,?,?)";
		
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		//Connecting to Oracle DB
		try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:X
E"))
		{
				// Creating prepared Statement
				PreparedStatement pstatemnt1 = conn.prepareStatement(insert_query);
				// Setting the values to insert in the EMPLOYEE_DETAILS Table
				pstatemnt1.setInt(1, 10009);
				pstatemnt1.setString(2,"Knight");
				pstatemnt1.setString(3, "Sebastian");
				pstatemnt1.setString(4, "sebknight@gmail.com");
				pstatemnt1.setInt(5, 9);
				pstatemnt1.setString(6, "40000");
				
				int records_affected = pstatemnt1.executeUpdate();
				System.out.println("No of Rows Inserted = " + 
records_affected);
				
				
		}
		
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

Output:

Data in EMPLOYEE_DETAILS Table after executing the Program:

Explanation:

In this program, our task is to insert 1 new employee into the EMPLOYEE_DETAILS table using PreparedStatement. It is similar to Statement Interface but the difference is that we can get the data from the user at runtime or dynamically and insert it into a table.

Here, the data are hardcoded in the program. We use ‘?’ to pass the parameter to the query and pass the value to ‘?’ using setX() methods. We have used the setInt method for number type Columns and setString method for VARCHAR type columns. We should pass the index number to the setInt so that it will set the value accordingly in the table.

The following statements are used in our program to set the data using the PreparedStatement object:

pstatemnt1.setInt(1, 10009);
pstatemnt1.setString(2,"Knight");
pstatemnt1.setString(3, "Sebastian");
pstatemnt1.setString(4, "sebknight@gmail.com")
pstatemnt1.setInt(5, 9);
pstatemnt1.setString(6, "40000");

You can write a program to get the data from the user at runtime and insert it into a table as well. You can consider it as an exercise also.

PreparedStatement For ExecuteBatch Method

Data in EMPLOYEE_DETAILS Table before executing the program:

Program:

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedStatement_Execute_Batch {

	public static void main(String[] args) throws ClassNotFoundException {
		// TODO Auto-generated method stub
		String Update_Query = "update EMPLOYEE_DETAILS set EmpNum = ? where EmpNum = ? ";
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		//Connecting to Oracle DB
		try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				// Creating prepared Statement
				PreparedStatement pstatemnt1 = conn.prepareStatement(Update_Query);
				
				//Updating value for ID = 1001
				pstatemnt1.setInt(1, 2001);
				pstatemnt1.setInt(2, 1001);
				pstatemnt1.addBatch();
				//Updating value for ID = 1002
				pstatemnt1.setInt(1, 2002);
				pstatemnt1.setInt(2, 1002);
				pstatemnt1.addBatch();
				//Updating value for ID = 1003
				pstatemnt1.setInt(1, 2003);
				pstatemnt1.setInt(2, 1003);
				pstatemnt1.addBatch();
				
				int[] No_Of_Rows_Updated = pstatemnt1.executeBatch();
				
				System.out.println("No of rows Updated = " + No_Of_Rows_Updated.length);
				
		}
		catch (SQLException e) {
			e.printStackTrace();
		}

				
	}

}

Output:

Data in EMPLOYEE_DETAILS Table after the execution of the Program:

Explanation:

In this program, we have updated the EMPNUM of the existing 3 employees in the EMPLOYEE_DETAILS table using the same connection and PreparedStatement objects. It is also similar to Statement Interface. We can pass the value at runtime to update it in the table.

Here, we have used executeBatch() to update the data for more than 1 entry. First, we need to pass the queries to addBatch() method then call the executeBatch(). It will execute many queries using the same objects at a time. After that connect to DB and check for the updated values.

The following statements are used to set the new EMPNUM and old EMPNUM for already existing employees in the EMPLOYEE_DETAILS Table:

//Updating value for ID = 1002         
pstatemnt1.setInt(1, 2001);
pstatemnt1.setInt(2, 1001);
pstatemnt1.addBatch();

//Updating value for ID = 1002
pstatemnt1.setInt(1, 2002);
pstatemnt1.setInt(2, 1002);
pstatemnt1.addBatch();

//Updating value for ID = 1003
pstatemnt1.setInt(1, 2003);
pstatemnt1.setInt(2, 1003);
pstatemnt1.addBatch();

CallableStatement Interface

CallableStatement is also in the java.sql.package. We can execute the SQL Stored procedures through CallableStatement. The CallableStatement object can be created using the prepareCall() method of the Connection interface. We can pass the values to IN, OUT, and INOUT parameters.

In the following example, we can learn more about the CallableStatement Interface.

First, create a Procedure to use that in the program. The following procedure will update the EMPNUM of the employee with the new EMPNUM in EMPLOYEE_DETAILS Table.

Create or replace PROCEDURE UPDATE_EMPLOYEE_DETAILS
(
  PARAM1 IN EMPLOYEE_DETAILS.EMPNUM%TYPE
, PARAM2 IN EMPLOYEE_DETAILS.EMPNUM%TYPE
) AS         
BEGIN
//Update query to update EMPNUM
  UPDATE EMPLOYEE_DETAILS set EMPNUM= param2 where EMPNUM = param1;
//Commit after updating
  commit;
END UPDATE_EMPLOYEE_DETAILS;

Data in EMPLOYEE_DETAILS Table before executing the Program:

Pass the values in the Program to update in the EMPLOYEE_DETAILS table.

Callablestatement Program

package com.STH.JDBC;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CallableStatementExample {

	public static void main(String[] args) throws ClassNotFoundException {
		
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		//Connecting to Oracle DB
		try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:X
E"))
		{
				// Creating prepared Statement
				CallableStatement CallStmt = conn.prepareCall("{call
 UPDATE_EMPLOYEE_DETAILS(?,?)}");
				CallStmt.setInt(1,1004);
				CallStmt.setInt(2, 2004);
				System.out.println(" Result " + CallStmt.executeUpdate());
		}
		catch (SQLException e) {
			e.printStackTrace();
		}	
}

}

Output:

Data in EMPLOYEE_DETAILS Table after the execution of the program:

Explanation:

We can call the stored procedures using the CallableStatement Interface. For example, here we have created one procedure that will update the EMPNUM in the EMPLOYEE_DETAILS table based on the EMPNUM given by the user. UPDATE_EMPLOYEE_DETAILS procedure has 2 IN parameters.

One is to set the new EMPNUM and the other one is to check the old EMPNUM. In the program, we are passing the values for the 2 IN parameters. Here the EMPNUM of the employee has been updated as 2004 where the EMPNUM = 1004.

Key Points to be noted:

• As a first step, the driver should be registered with DriverManager and then should be available to use in Java Program.
• After the registration of Driver is done, we can establish the connection using the getConnection() method in DriverManager.
• The objects of the Statement, PreparedStatement, and CallableStatement can be created using the Connection object.
• We will use the Statement interface to execute normal operations DB. Using executeBatch(), we can execute a batch of SQL statements.
• PreparedStatement will be used in most of the cases to update/insert/delete the data in the Table by passing the values as parameters.
• CallableStatement is used to call Stored Procedures or Functions.

Frequently Asked Questions

Q #1) What is the Base Class for all the classes in Java?

Answer: Object class is the base class for all the classes in Java.

Q #2) What is the main function of DriverManager in JDBC?

Answer: It tracks the set of JDBC drivers that are registered and available to use in the program. It acts as an interface between applications and drivers.

Q #3) What is Connection Interface?

Answer: Connection interface maintains the session between the application and Database. The connection has been established using the getConnection method DriverManager. Once the driver is registered with DriverManager, then only Connection can be established. Using the object of Connection, we get the object of Statement, PreparedStatement, and CallableStatement Interfaces.

Q#4) What is the difference between Statement and PreparedStatement?

Answer: Statement is used to execute the static SQL statements i.e., without passing input parameters.

PreparedStatement is used to execute the SQL statements by passing the input parameters dynamically. It prevents SQL injection attacks.

Q #5) What if Statement or PreparedStatement objects are not closed?

Answer: If we close the connection, then automatically it will close the statement or PreparedStatement objects of that connection object.

Q#6) How to call Stored Procedures or Functions of DB in Java Program?

Answer: CallableStatement interface will be used to call the Stored Procedures or Functions. PrepareCall() method is used to call Stored Procedures in Java Program.

Conclusion

In this tutorial, we have discussed what is DriverManager and what are the methods in it and how to use that in our Java Program.

We also learned about the uses of Connection Interface and how to use that to establish the connection. Statement, PreparedStatement, and CallableStatement interfaces are used to perform operations with Database from our Java Program. We have seen how executeBatch() method is used to execute a batch of SQL statements.

<<PREV Tutorial | NEXT Tutorial>>