Updated April 17, 2023
Introduction to JDBC transaction
JDBC transaction is nothing but the unit of work that will execute in a single command, database ACID properties will describe the transaction management using JDBC. ACID in JDBC transaction known as atomicity, consistency, isolation and durability, basically in JDBC connection interface will provide the methods to manage the transaction. We can use the setAutoCommit, commit and rollback methods to manage transactions using JDBC. We can use PreparedStatement in transactions to execute the SQL statement, after using PreparedStatement in the statement will execute as per transaction order.
Syntax:
Below is the syntax of JDBC transactions is as follows.
JDBC transaction using simple statement –
public static void main /* main method */ (string args [])
Class.forName ("database driver name")
Connection con_object DriverManager.getConnection ("connection string")
Con_object.setAutoCommit (value)
Statement stmt_object = con_object.createStatement ();
Stmt_bject.executeUpdate("SQL query");
Stmt_bject.executeUpdate("SQL query");
con_object.commit();
con_object.rollback();
Con.close();JDBC transaction using Prepared statement –
public static void main /* main method */ (string args [])
Class.forName ("database driver name")
Connection con_object DriverManager.getConnection ("connection string")
Con_object.setAutoCommit (value)
PreparedStatement Prp_stmt_object = con_object.prepareStatement ("SQL query");
Prp_stmt_object.executeUpdate (");
con_object.commit ();
con_object.rollback ();
Con.close ();Parameter description syntax of JDBC transaction
- Database driver name – To connect the specified database we need a database driver name. We need to load the database driver to connect to the specified database.
- Connection String – This contains the string as username, password, hostname and port of the database server.
- SetAutoCommit – This is the method of connection interface used in transactions. The default value of this method is true, means we can commit all the transaction without issuing commit command. We can disable it by issuing Boolean values as false.
- Commit – It is a method of connection interface which was used to commit the transaction which was running of the database server. I suppose we have not setAutoCommit method as false we need to issue a commit command after a successful transaction.
- Rollback – This method is used to cancel the previously running transaction on the database server. We can rollback the transaction before it’s committing. We can roll back the transaction after setting the value false of setAutoCommit method.
- Execute update – This method is used to execute the statement of PreparedStatement method. It will execute the statement which was written under the PreparedStatement.
How Transactions work in JDBC?
- Transaction is nothing but the block of SQL query which was executed in a single block of statement.
- As we know that all JDBC connections are in auto-commit mode, using this mode all the transactions will be committed after the successful execution of each transaction.
- By default JDBC connection mode is set as true, we can change this value using defining the setAutoCommit method value as false.
- Below are the key points to need to disable autocommit of transactions in JDBC are as follows.
- We can use distributed transactions by disabling the auto-commit parameter.
- We can increase the performance of the application.
- We can also maintain the integrity of a business by disabling the auto-commit parameter of transactions.
- Below are the methods of connection interface which was used in a transaction are as follows.
- Void commit() method
- Void setAutoCommit() method
- Void rollback() method
- Void setSavePoint() method
- Void releaseSavePoint() method
- JDBC setAutoCommit contains the Boolean value as true or false, we can set the value as true or false using setAutoCommit method. I suppose we have set value as true it will automatically commit all the transactions.
- The savepoint will give us additional control on the transaction which was we have done on database server using transaction.
- The release savepoint method is used to delete the created savepoint. We can use the setSavePoint method to create a new save point.
- We have no need to update the transaction after successful execution.
- Using transactions we can provide the data integrity, consistent view and correct application semantics to the java compliant drivers.
- Below are the most important function which was used in transaction management are as follows.
- Commit
- Rollback
- Savepoint
- Savepoint is very useful in transactions to roll back a particular transaction after executing all the transactions.
- We have created multiple savepoint to perform this operation on the database using transactions.
- Any savepoint which was we have created it will automatically be destroyed or deleted when we have issuing rollback or commit operation on the database server.
Example
The below example shows JDBC transactions are as follows.
1. JDBC transaction using insert query
The below example shows transactions using the insert query are as follows. We have using the commit and setAutoCommit method.
Code:
public static void main /* main method */ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/jdbc_tran";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
Connection conn_tran = DriverManager.getConnection (DB_CON, USER_NAME, PASSWORD);
conn_tran.setAutoCommit (false);
System.out.println ("We have set setAutoCommit method value as false,");
Statement stmt_tran = conn_tran.createStatement ();
stmt_tran.executeUpdate ("insert into jdbc_tran values(1,'ABC')");
stmt_tran.executeUpdate ("insert into jdbc_tran values(2,'PQR')");
System.out.println ("Record inserted.");
conn_tran.commit ();
System.out.println ("Transaction committed.");
}
}
2. JDBC transaction using update query
The below example shows transactions using update query are as follows. We have using the commit, setAutoCommit and rollback methods.
Code:
public static void main /* main method */ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/jdbc_tran";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
Connection conn_tran = DriverManager.getConnection (DB_CON, USER_NAME, PASSWORD);
conn_tran.setAutoCommit (false);
Statement stmt_tran = conn_tran.createStatement ();
stmt_tran.executeUpdate ("update jdbc_tran set id = 11 where id = 2 ");
System.out.println ("Record updated.");
conn_tran.commit ();
System.out.println ("Transaction committed.");
conn_tran.rollback ();
System.out.println ("Transaction roll backed.");
}
}
3. JDBC transaction using delete query
The below example shows transactions using delete query are as follows. We have using commit, setAutoCommit and rollback methods.
Code:
public static void main /* main method */ (String[] args) throws Exception {
final String DB_CON = "jdbc:postgresql://localhost:5432/jdbc_tran";
final String USER_NAME = "postgres";
final String PASSWORD = "postgres";
Connection conn_tran = DriverManager.getConnection (DB_CON, USER_NAME, PASSWORD);
conn_tran.setAutoCommit (false);
Statement stmt_tran = conn_tran.createStatement ();
stmt_tran.executeUpdate ("Delete from jdbc_tran where id = 1");
System.out.println ("Record deleted.");
conn_tran.commit ();
System.out.println ("Transaction committed.");
conn_tran.rollback ();
System.out.println ("Transaction roll backed.");
} }
Conclusion
JDBC transaction is nothing but the work of a single unit. Transaction is very useful to improve the performance of applications. There are three types of functions available in transactions i.e. commit, rollback and savepoint. Commit, rollback, setAutoCommit, setSavePoint, and release savepoint are methods available in transactions.
Recommended Articles
This is a guide to JDBC transaction. Here we discuss How do transactions work in JDBC along with the examples and parameters. You may also have a look at the following articles to learn more –
