Java JDBC Transaction Management With Example

This tutorial explains JDBC transaction types, data types,  transaction management methods, and how to use them in Java program:

In the JDBC ResultSet tutorial of the JDBC tutorial series, we learned to use JDBC ResultSet to retrieve data.

In this tutorial, we will discuss the transaction types in JDBC. We have seen a brief introduction to the transaction types in our previous tutorials. Here we will see in a detailed manner. We will also cover what are the data types in JDBC and how to use it in Java program.

The database will have different data types and Java will have different data types. JDBC will manage this differentiation. Let’s get ready to know the important topic in JDBC.

JDBC Transaction Management

The sequence of actions (SQL statements) is treated as a single unit that is known as a transaction. Transaction Management is important for RDBMS-oriented applications to maintain data integrity and consistency.

While performing the transaction, we will use getXXX and setXXX methods to retrieve and set the data in the ResultSet object.  XXX represents the data types of the columns. We will discuss the transaction and data types of JDBC in this tutorial.

Transaction Types

In JDBC every SQL query will be considered as a transaction. When we create a Database connection in JDBC, it will run in auto-commit mode (auto-commit value is TRUE). After the execution of the SQL statement, it will be committed automatically.

Sometimes, we may want to commit the transaction after the execution of some more SQL statements. At that time, we need to set the auto-commit value to False. So that data won’t be committed before executing all the queries. If we get an exception in the transaction, we can rollback() changes and make it like before. Transaction Management can be explained well – using ACID properties.

ACID means

• A–Atomicity -> If all queries are executed successfully, data will be committed, else won’t.
• C–Consistency -> DB must be in a consistent state after any transaction.
• I– Isolation -> Transaction is isolated from other transactions.
• D–Durability -> If the transaction is committed once, it will remain always committed.

There are three most important functions in Transaction Management. They are:

• Commit: After the execution of the SQL statements, we want to make the changes permanent in the Database. We should call the commit() method. Normally, what is commit means it will make the changes permanently in the Database. We can’t undo/ revoke the changes. But we can change the data in the Database.
• Rollback: Rollback undoes the changes till the last commit or mentioned savepoint. Sometimes we may want to undo the changes. For example, we have one nested query, one part has been executed successfully, and the other has thrown some exception. At that time, we want to undo the changes done by the first part, we should call Rollback() method to do that if an exception has occurred.
• Savepoint: Savepoint helps to create checkpoint in a transaction and it allows to perform a rollback to that particular savepoint. Any savepoint that has been created for a transaction will be automatically destroyed and become invalid once the transaction is committed or rolled back.

Till now we have seen what is commit, rollback, and savepoint and its operations. Below, we will see the methods of it and how to use it in the program.

Methods Of Transaction Management

The connection interface provides 5 methods for transaction management.  They are as follows:

#1) setAutoCommit() Method

By default, the value of AutoCommit value is TRUE. After the execution of the SQL statement, it will be committed automatically. By using the setAutoCommit() method we can set the value to AutoCommit.

#2) Commit() Method

The commit method is used to commit the data. After the execution of the SQL statement, we can call the commit(). It will commit the changes which are made by the SQL statement.

Syntax: conn.commit();

#3) Rollback() Method

The rollback method is used to undo the changes till the last commit has happened. If we face any issue or exception in the execution flow of the SQL statements, we may roll back the transaction.

Syntax: conn.rollback();

#4) setSavepoint() Method

Savepoint gives you additional control over the transaction. When you set a savepoint in the transaction (a group of SQL statements), you can use the rollback() method to undo all the changes till the savepoint or after the savepoint(). setSavepoint() method is used to create a new savepoint.

#5) releaseSavepoint() Method

It is used to delete the created savepoint.

In the below program, you will get to know more about these methods and will also learn how to use it in the Java program.

In this tutorial, all programs are written in Java. We have used Java 8 version and Oracle DB.

>> Click here to download the Oracle software.

>> Click here to download the Java version 8.

It has the step-by-step Java installation process.

Transaction Example Program

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
	
public class Transaction_Management {

	public static void main(String[] args) throws ClassNotFoundException {
		// TODO Auto-generated method stub
		//Select Query to get the Data from employee_details table
		String QUERY = "select * from employee_details where empNum = 2001";
		String QUERY1 = "select * from employee_details where empNum = 2002";
		Boolean autoCommit;
		String update_query = "update employee_details set salary = 41000 where empNum = 2001"; 
		String update_query1 = "update employee_details set salary = 42000 where empNum = 2002"; 
		
		//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
				rs1 = statemnt1.executeQuery(QUERY); 
			
				//Executed the SELECT Query
				System.out.println("Getting the data from employee_details table");
				displayData(rs1);
				//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 to update salary of EMPNUM = 2001");
				System.out.println("Update Query is " + update_query);
				int return_rows = statemnt1.executeUpdate(update_query);
				System.out.println("Updated the data but didn't commit");
				//Getting data after Updation 
				Connection conn1 = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE");
				System.out.println("Opening new connection");
				System.out.println("EMPNUM = 2001 data");
				Statement statement2 = conn1.createStatement();
				ResultSet rs;
				rs = statement2.executeQuery(QUERY); 
				displayData(rs);
				System.out.println("Commit has been done");
				conn.commit();
				Savepoint s1 = conn.setSavepoint();
				System.out.println("SavePoint has been created");
				System.out.println("Displaying data of EMPNUM = 2001");
				System.out.println("Using The Second Connection");
				rs = statement2.executeQuery(QUERY); 
				displayData(rs);
				rs = statemnt1.executeQuery(QUERY); 
				//Rollback the transaction
				System.out.println("Data of EMPNUM = 2002");
				rs1 = statemnt1.executeQuery(QUERY1); 
				displayData(rs1); 
				System.out.println("Updating the salary of EMPNUM = 2002");
				System.out.println("Update Query is " + update_query1);
				statemnt1.executeUpdate(update_query1);
				System.out.println("Data of EMPNUM = 2002 but didn't commit");
				rs1 = statemnt1.executeQuery(QUERY1); 
				displayData(rs1);
				System.out.println("Rollback is done... so updated data won't be reflected");
				
				conn.rollback(s1);
				System.out.println("Data of EMPNUM = 2002 after Rollback till the last savepoint");
				rs1 = statemnt1.executeQuery(QUERY1); 
				displayData(rs1);
					
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
	 public static void displayData(ResultSet rs1) throws SQLException
	 {

			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);
			}
		
	 }
	 }

Output:

Explanation:

What we have done in the above transaction management program is to update the values of the given employee in the EMPLOYEE_DETAILS Table and commit the data. If any error or exception has occurred then we have done the rollback() operation. Now we are going to see the full explanation of the program.

#1) Created 2 Select query for 2 employees based on their EMPID

Below are the 2 select queries.

String QUERY = "select * from employee_details where empNum = 2001";
String QUERY1 = "select * from employee_details where empNum = 2002";

#2) Created two update queries for 2 employees based on their EMPID

Two updated queries:

String update_query = "update employee_details set salary = 41000 where empNum = 2001";
String update_query1 = "update employee_details set salary = 42000 where empNum = 2002";

#3) Open Connection, execute the select query and displayed the data of EMPNUM = 2001.

#4) Using setAutoCommit() method, set the value of autoCommit to false.

#5) Executed the update query of EMPNUM = 2001 and created another connection for the same Database and selected the value of the EMPNUM =2001.

#6) The resultant data of the Employee whose EMPNUM = 2001 is not the updated data. Because we have not done the commit() operation. If you have used the same connection where you have used it to update, it would have shown you the updated Data. Now committed the data. The data has been reflected in the table.

#7) Created one savepoint after the commit operation.

#8) Using the select query to display data of the EMPNUM = 2002. Changed the salary of that employee using an update query. After that displayed the data of the EMPNUM = 2002, using the same connection. It should show the updated data.

#9) Done the rollback till the last savepoint using the rollback method. Now when we have displayed the data of that employee using the same connection, it has the old data because rollback undoes the changes till the last savepoint if we mention savepoint else to the last commit.

#10) We have created one method which will display the data because we are displaying data many times here.

JDBC Data Types

Databases have SQL data types and Java has Java data types. There should be a mechanism for reading and writing data between a Java application and a database. Different Databases support SQL types, but with some variations in the name.

For example, most of the databases support large binary values, Oracle calls it as LONG RAW, Sybase calls it as IMAGE, Informix calls it as BYTE, and DB2 calls it as LONG VARCHAR FOR BIT DATA.

While writing the JDBC program, we don’t need to worry about SQL data types used by the target Database. JDBC has a set of generic SQL type identifiers in the class java.sql.Types. These types are designed to support most of the generic SQL data types. While writing a JDBC program, we will use JDBC data types only.

JDBC driver will convert the Java data types to Database data types back and forth. You can use java.sql.DatabaseMetaData.getTypeInfo method to check which SQL types are actually supported by a given database and then write a program. JDBC uses a default mapping for most of the data types.

For example, the Java string will be converted into a SQL VARCHAR type.

We will see how the mapping is done in the setXXX, getXXX, and updateXXX method of the preparedStatement or CallableStatement or ResultSet interfaces in the following table:

We have setXXX, getXXX and updateXXX method for basic data types in our previous tutorials. You can refer to that.

Here, we have explained clob and blob types in the following example.

BLOB Example Program

package com.STH.JDBC;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
	
public class Blob_Example {

	@SuppressWarnings("resource")
	public static void main(String[] args) throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub

		String QUERY = "Create table Blob_Sample_Example ( PicName varchar2(30), picture BLOB)"; //Select Query to get the Data from employee_details table
		
		//Update query to set the email id for the employee whose empNUM is 1001
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		System.out.println("Establishing Connection");
		try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				Statement statemnt1 = conn.createStatement();
				//Executing the Create Query
				statemnt1.execute(QUERY);
				System.out.println("Table has been created for BLOB type");
				System.out.println("Inserting data into Blob_Sample_Example table");
				String insert_query = " insert into Blob_Sample_Example values(?,?)";
				//Passing the values for preparedStatement
				PreparedStatement pstmnt = conn.prepareStatement(insert_query);
				pstmnt.setString(1, "Sotware Testing Help");
				//Mention Image path to store the image in DB
				FileInputStream inputStream = new FileInputStream("D:\\Bhakiya\\Bhakiya\\JDBC\\Software Testing Pic.png");
				pstmnt.setBlob(2, inputStream);
				//Executing the preparedStatement to insert the data 
				pstmnt.execute();
				System.out.println("Retrieving data");
				 ResultSet rs = statemnt1.executeQuery("select * from Blob_Sample_Example");
				 while(rs.next())
				 {
					//Getting Picture Name
					 System.out.println("Picture Name:" + rs.getString(1));
					 Blob blob1 = rs.getBlob(2);
					//using blob retrieving the Image
			         byte byteArray[] = blob1.getBytes(1,(int)blob1.length());
			         FileOutputStream outPutStream = new FileOutputStream("D:\\Bhakiya\\Bhakiya\\JDBC\\Software Testing Pic output.png");
			         outPutStream.write(byteArray);
			         System.out.println(" Go to the following path: D:\\Bhakiya\\Bhakiya\\JDBC\\Software Testing Pic output.png");
			       
				 }
		
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

Output:

File has been saved successfully in the given path.

Explanation:

In the above program what we have done is First we have created/saved one image in the folder “D:\\Bhakiya\\Bhakiya\\JDBC”. This location is just for example. You can create your own file path in your system. The name of the file is a Software testing pic. Then we created one Java program to store that image in the DB table and retrieve the image from the table and store it in the local system.

Till now we have discussed the overview of the task. Now we will see how we achieved it in the program.

#1) Create one table in DB to store the image.

Syntax:

String QUERY = "Create table Blob_Sample_Example ( PicName varchar2(30), picture BLOB)";

In the above query, we have 2 columns.

1. PicName – varchar2 -> It is used to store the name of the picture
2. picture – BLOB -> It is used to store the Picture in Table.

BLOB data type is used to store the Picture/image in the DB Table.

#2) Connection and statement have been created and called execute method to execute the CREATE Query.

statemnt1.execute(QUERY);

#3) Next, we have Created the insert query and executed it using PreparedStatement.

PreparedStatement syntax:

PreparedStatement pstmnt = conn.prepareStatement(insert_query);

#4) Using setString() – set the name of the Picture.

#5) To set the Picture, use the FileInputStream class to pass the picture location.

Syntax:

FileInputStream inputStream = new FileInputStream("Picture Full Path");

Then, using the setBlob() method, we can set the picture in the PreparedStatement object. After that, called the execute method of PreparedStatement. That will insert the given data in the Table.

After executing the insert query, by using ResultSet object we are retrieving the data from Table (using Select Query).

#6) By using the getString method, we can get the value of the PicName Column.

#7) To get the Picture, follow the below steps:

• Create Blob object and assign the return values of getBlob method of ResultSet object.
  • Syntax for that is: Blob blob1 = rs.getBlob(<ColumnIndex>);
• Create a byte array object and get the value of the Blob object as bytes.
• Create object FileOutputStream object and pass the full path to store the picture in that path. Call write() method of FileOutputStream object–that will store the picture.
• After that the program has been executed successfully.

#8) Go to the location of the Output picture and check whether the input and output are the same.

CLOB Example Program

package com.STH.JDBC;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Clob_Example {
	public static void main(String[] args) throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub
		String QUERY = "Create table Clob_Sample_Example ( FileName varchar2(30), StoreFile CLOB)"; 
		//Create Query to create new table for CLOB example
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		//Creating Connection
		System.out.println("Establishing Connection");
		try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				Statement statemnt1 = conn.createStatement();
				//Executing create query
				statemnt1.execute(QUERY);
				System.out.println("Table has been created for CLOB type");
				System.out.println("Inserting data into Clob_Sample_Example table");
				//Insert Query
				String insert_query = " insert into Clob_Sample_Example values(?,?)";
				//Passing Parameters for PreparedStatement
				PreparedStatement pstmnt = conn.prepareStatement(insert_query);
				pstmnt.setString(1, "File1");
				
				FileReader fileRead = new FileReader("D:\\Bhakiya\\Bhakiya\\JDBC\\file1.txt");
				pstmnt.setClob(2, fileRead);
				//Executing the PreparedStatement
				pstmnt.execute();
				System.out.println("Retrieving data");
				 ResultSet rs = statemnt1.executeQuery("select * from Clob_Sample_Example");
				 while(rs.next())
				 {
					 System.out.println("File Name:" + rs.getString(1));
					//Getting file data
					 Clob clob1 = rs.getClob(2);
			        Reader r = clob1.getCharacterStream();
			        //Using Reader - read the data and Writer - Write the data in file in the given location
			         FileWriter fileWrite = new FileWriter("D:\\Bhakiya\\Bhakiya\\JDBC\\file1 output.txt");
			         int i;
			         while((i=r.read())!=-1)
			         fileWrite.write(i);
			         fileWrite.close();
			         System.out.println(" Go to the following path: D:\\Bhakiya\\Bhakiya\\JDBC\\file1 output.txt");
			       
				 }
		
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

Output:

Input File:

File has been saved successfully in the given path.

Output File:

Explanation:

In the above program what we have done is first we have created/saved one text file “file1.txt” in the folder “D:\\Bhakiya\\Bhakiya\\JDBC”. This location is just for example. You can create your own file path in your system. Then we created one Java program to store that file in the DB table and retrieve that file from the table and store it in the local system.

Till now we have discussed the overview of the task. Now we will see how we achieved it in the program.

#1) Create one table in DB to store the image.

Below is the Syntax of the CREATE TABLE Query:

String QUERY = "Create table Clob_Sample_Example ( FileName varchar2(30), StoreFile CLOB)";

In the above query, we have 2 columns.

1. FileName – varchar2 -> It is used to store the name of the picture.
2. StoreFile – CLOB -> It is used to store the File in Table.

CLOB data type is used to store the Character type of binary data in the DB Table

#2) Connection and statement have been created and called execute method to execute the CREATE Query.

statemnt1.execute(QUERY);

#3) Next, we have created the insert query and executed it using PreparedStatement.

PreparedStatement syntax:

PreparedStatement pstmnt = conn.prepareStatement(insert_query);

#4) Using setString() – set the name of the FileName.

#5) To set/store the File, use the FileReader class to pass the file with fill location.

Syntax:

FileReader fileRead = new FileReader("D:\\Bhakiya\\Bhakiya\\JDBC\\file1.txt");

Then using setClob() method, we can set the file in the PreparedStatement object. After that called the execute method of PreparedStatement. That will insert the given data in the Table.

After executing the insert query, using ResultSet object we are retrieving the data from Table (Using Select Query).

Select Query:

"select * from Clob_Sample_Example"

#6) Using the getString method we can get the value of the FileName Column.

#7) To get the File, we have followed the below steps:

• Create Clob object and assign the return values of the getClob method of ResultSet object.
  • Syntax for that is: Clob clob1 = rs.getClob(<ColumnIndex>);
• Create Reader object and get the value of Clob object as Character.
• Create object FileWriter object and pass the full path to store the File in that path. Call write() method of FileWrite object – that will write the data in the file in the location.
• After that the program has been executed successfully.

#8) Go to the location of the Output file and check whether the input and output files are the same.

Points to remember:

• ACID Properties explains Transaction Management in Database.
• Commit(), rollback(), and savepoint are the most important operations in Transaction Management.
• Commit will make the changes permanently in DB, Rollback will undo the changes till the last commit or the mentioned savepoint and Savepoint helps to create a checkpoint.
• Database maintains SQL data types and Java maintains Java data types. JDBC driver is used to handle this conversion.
• JDBC driver itself has some SQL type of identifiers, so the programmer doesn’t need to worry about data types.

Frequently Asked Questions

Q #1) What is the JDBC data type?

Answer: Java has its data types, and the database has its data types. JDBC driver converts the Java data type to the appropriate SQL data type which will accept by the database. ResultSet objects also provide setXXX() methods and getXXX() methods available for the appropriate data types.

Q #2) What is the data type for the date in Java?

Answer: The date in Java is not only a data type but a class. A Date in Java has the date, the time, the year, the name of the day, the day of the week, and the timezone. Date class has the appropriate methods to retrieve the relevant data.

Q #3) What is the difference between java.util.Date and java.sql.Date?

Answer: The main difference is java.util.Date has information about date and time as well. But java.sql.Date has the information about the date only. This is the main difference we can’t map java.util.Date directly with java.sql.Date.

Q #4) Do we have a class in Java to represent TIME and TIMESTAMP type for SQL?

Answer: Yes, we have a class for TIME and TIMESTAMP type for SQL. java.sql.Time class represents TIME-related information. java.sql.timestamp class represents TIMESTAMP related information.

Q #5) How to start a transaction in JDBC?

Answer: JDBC connection starts with auto-commit mode enabled, where each SQL statement is considered as a transaction. There is no specific process to start a transaction in JDBC. When you create a connection and start to execute the SQL statement, that is where the transaction has started.

Q #6) What is the commit() method in Java?

Answer: Commit() method in Java is used to save the changes which are made since the last commit(). Commit() method is available in the Connection interface. Using a connection object, we can call the commit().

Q #7) What is the rollback method in Java?

Answer: After the last commit(), if any issues have occurred we can call the rollback() method to revert all the changes done till the last commit.rollback() method is available in the Connection interface. Using a connection object, we can call the rollback() method.

Conclusion

Here, we have covered data types in Java and DB and how the JDBC driver handles it. We have discussed ACID properties. That is most important for the banking field. It will be very helpful for your career. In the Transaction Management section, we have discussed commit and rollback methods such as commit(), rollback(), setAutoCommit(), setSavepoint() and releaseSavepoint().

<<PREV Tutorial | NEXT Tutorial>>