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);
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.