devxlogo

Using Database Transactions with JDBC

Using Database Transactions with JDBC

When a connection is created using JDBC, by default it is in auto-commit mode. This means that each SQL statement is treated as a transaction and will be automatically committed immediately after it is executed. Sometimes, you want a group of statements to execute together or fail together. Transactions are used to group a set of statements so that they all execute successfully, or all fail. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. The following line of code will do this:

 conn.setAutoCommit(false);

Once auto-commit mode is disabled, no SQL statement will be committed until the commit method is called explicitly. All statements starting from the previous call to the commit will be committed together. If there is a problem, the entire set of statements can be rolled back, without committing. The following code illustrates this:

 conn.setAutoCommit(false);try{PreparedStatement updateSales = _conn.prepareStatement(    "UPDATE PRODUCT_SALES SET NUMBER_OF_SALES=NUMBER __OF_SALES+1 WHEREPRODUCT_ID = ?");updateSales.setString(1, productId);updateSales.executeUpdate();PreparedStatement updateInventory = _conn.prepareStatement(    "UPDATE INVENTORY SET STOCK=STOCK-1 _WHERE PRODUCT_ID = ?");updateInventory.setString(1, productId);updateInventory.executeUpdate();conn.commit();}catch(SQLException se){  conn.rollback();}conn.setAutoCommit(true);
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist