StackStalk
  • Home
  • Java
    • Java Collection
    • Spring Boot Collection
  • Python
    • Python Collection
  • C++
    • C++ Collection
    • Progamming Problems
    • Algorithms
    • Data Structures
    • Design Patterns
  • General
    • Tips and Tricks

Friday, August 1, 2014

JDBC handling transactions

 August 01, 2014     Java     No comments   

Transaction is a set of one or more statements executed as a unit. The intent is that multiple statements which are part of a unit are executed together to make it meaningful. This article provides a quick introduction to handling transactions in JDBC.

Here are some key points to remember when dealing with transactions using JDBC.

  • When a DB connection is created it is by default put in auto commit mode. The key to handle transactions is to disable auto commit mode so that the application can have better control when to commit.
  • connection.setAutoCommit(false);
  • Since auto commit is disabled when working with transactions it is necessary to perform an explicit commit when all the statements that are part of a transaction are completed.
  • connect.commit();
  • To undo the effects of a transaction the rollback method could be used to restore the values before the attempted transaction.
  • connection.rollback();
  • JDBC also supports to set save points and then rollback to the specified save point. The following method could be used to define save points.
  • SavePoint savePoint1 = connection.setSavePoint();
  • Rollback a transaction to an already defined save point using rollback call with an argument.
  • connection.rollback(savePoint1);

JDBC Transaction Example

Let us consider a simple example of a stock trading application where we have to update two tables one for individual transactions and another for total transactions in a day.

We are using a PostgreSQL database here. Refer to these tutorials to get started. JDBC Quick Start with PostgreSQL and JDBC Database Access Examples

Table1: Stock Trades

CREATE TABLE stock_trades
(
  trade_id numeric(6,0) NOT NULL,
  script character(10),
  num_units integer,
  CONSTRAINT stock_trades_pkey PRIMARY KEY (trade_id)
)

Table2: Total Trades

CREATE TABLE total_trades
(
  script character(10) NOT NULL,
  total_units integer,
  CONSTRAINT total_trades_pkey PRIMARY KEY (script)
)
Now let us perform a transaction where we need to update both the stock trades and total trades table.
public static void performTransaction(Connection connection, int id, String script, int units) {
  PreparedStatement insertStockTrades = null;
  PreparedStatement updateTotalTrades = null;
  
  String insertStockSql = "INSERT INTO stock_trades VALUES(?, ?, ?)";
  String updateStockSql = "UPDATE total_trades set total_units = total_units + ? where script = ?";
  
  try {
    connection.setAutoCommit(false);
    
    insertStockTrades = connection.prepareStatement(insertStockSql);
    insertStockTrades.setInt(1, id);
    insertStockTrades.setString(2, script);
    insertStockTrades.setInt(3, units);
    insertStockTrades.executeUpdate();
    
    updateTotalTrades = connection.prepareStatement(updateStockSql);
    updateTotalTrades.setInt(1, units);
    updateTotalTrades.setString(2, script);
    updateTotalTrades.executeUpdate();
    
    connection.commit();
  } catch ( SQLException  e ) {
    e.printStackTrace();
    try {
      connection.rollback();
    } catch (SQLException e1) {
      e1.printStackTrace();
    }
  } finally {
    try {
      connection.setAutoCommit(true);
    } catch (SQLException e2) {
      e2.printStackTrace();
    }
  }
}
  • Share This:  
Newer Post Older Post Home

0 comments:

Post a Comment

Follow @StackStalk
Get new posts by email:
Powered by follow.it

Popular Posts

  • Avro Producer and Consumer with Python using Confluent Kafka
    In this article, we will understand Avro a popular data serialization format in streaming data applications and develop a simple Avro Produc...
  • Monitor Spring Boot App with Micrometer and Prometheus
    Modern distributed applications typically have multiple microservices working together. Ability to monitor and manage aspects like health, m...
  • Server-Sent Events with Spring WebFlux
    In this article we will review the concepts of server-sent events and work on an example using WebFlux. Before getting into this article it ...
  • Implement caching in a Spring Boot microservice using Redis
    In this article we will explore how to use Redis as a data cache for a Spring Boot microservice using PostgreSQL as the database. Idea is to...
  • Python FastAPI microservice with Okta and OPA
    Authentication (AuthN) and Authorization (AuthZ) is a common challenge when developing microservices. In this article, we will explore how t...
  • Spring Boot with Okta and OPA
    Authentication (AuthN) and Authorization (AuthZ) is a common challenge when developing microservices. In this article, we will explore how t...
  • Getting started with Kafka in Python
    This article will provide an overview of Kafka and how to get started with Kafka in Python with a simple example. What is Kafka? ...
  • Getting started in GraphQL with Spring Boot
    In this article we will explore basic concepts on GraphQL and look at how to develop a microservice in Spring Boot with GraphQL support. ...

Copyright © StackStalk