JDBC Transaction example

JDBC transaction make sure a set of SQL statements is executed as a unit, either all of the statements are executed successfully, or NONE of the statements are executed (rolled back all changes).

1. Without JDBC Transaction

1.1 A JDBC example to insert two rows and update one row.

TransactionExample.java

package com.mkyong.jdbc;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;

public class TransactionExample {

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement();
             PreparedStatement psInsert = conn.prepareStatement(SQL_INSERT);
             PreparedStatement psUpdate = conn.prepareStatement(SQL_UPDATE)) {

            statement.execute(SQL_TABLE_DROP);
            statement.execute(SQL_TABLE_CREATE);

            // Run list of insert commands
            psInsert.setString(1, "mkyong");
            psInsert.setBigDecimal(2, new BigDecimal(10));
            psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            psInsert.execute();

            psInsert.setString(1, "kungfu");
            psInsert.setBigDecimal(2, new BigDecimal(20));
            psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            psInsert.execute();

            // Run list of update commands

            // below line caused error, test transaction
            // org.postgresql.util.PSQLException: No value specified for parameter 1.
            psUpdate.setBigDecimal(2, new BigDecimal(999.99));
            
			//psUpdate.setBigDecimal(1, new BigDecimal(999.99));
            psUpdate.setString(2, "mkyong");
            psUpdate.execute();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static final String SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?,?,?)";

    private static final String SQL_UPDATE = "UPDATE EMPLOYEE SET SALARY=? WHERE NAME=?";

    private static final String SQL_TABLE_CREATE = "CREATE TABLE EMPLOYEE"
            + "("
            + " ID serial,"
            + " NAME varchar(100) NOT NULL,"
            + " SALARY numeric(15, 2) NOT NULL,"
            + " CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,"
            + " PRIMARY KEY (ID)"
            + ")";

    private static final String SQL_TABLE_DROP = "DROP TABLE EMPLOYEE";

}

Output, the update is failed, and throwing an exception, at the end, 2 rows are inserted but the update is skipped.


org.postgresql.util.PSQLException: No value specified for parameter 1.
	at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:292)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
	at com.mkyong.jdbc.TransactionExample.main(TransactionExample.java:41)
output

2. Using JDBC Transaction

2.1 To enable transaction, set auto commit to false.


conn.setAutoCommit(false); // default true
// start transaction block

// insert
// update 
// if any errors within the start and end block, 
// rolled back all changes, none of the statements are executed.

// end transaction block
conn.commit();

2.2 Same example with JDBC transaction.

TransactionExample.java

package com.mkyong.jdbc;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;

public class TransactionExample {

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement();
             PreparedStatement psInsert = conn.prepareStatement(SQL_INSERT);
             PreparedStatement psUpdate = conn.prepareStatement(SQL_UPDATE)) {

            statement.execute(SQL_TABLE_DROP);
            statement.execute(SQL_TABLE_CREATE);

            // start transaction block
            conn.setAutoCommit(false); // default true

            // Run list of insert commands
            psInsert.setString(1, "mkyong");
            psInsert.setBigDecimal(2, new BigDecimal(10));
            psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            psInsert.execute();

            psInsert.setString(1, "kungfu");
            psInsert.setBigDecimal(2, new BigDecimal(20));
            psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            psInsert.execute();

            // Run list of update commands

            // error, test roolback
            // org.postgresql.util.PSQLException: No value specified for parameter 1.
            psUpdate.setBigDecimal(2, new BigDecimal(999.99));
            //psUpdate.setBigDecimal(1, new BigDecimal(999.99));
            psUpdate.setString(2, "mkyong");
            psUpdate.execute();

            // end transaction block, commit changes
            conn.commit();

            // good practice to set it back to default true
            conn.setAutoCommit(true);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //...

}

Output, none of the statements are executed, the insert statements are rolled back.


org.postgresql.util.PSQLException: No value specified for parameter 1.
	at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:292)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
	at com.mkyong.jdbc.TransactionExample.main(TransactionExample.java:41)
output

3. Extra…

Fix the parameter 1 error and see the expected result.


	//psUpdate.setBigDecimal(2, new BigDecimal(999.99));
            
	psUpdate.setBigDecimal(1, new BigDecimal(999.99));
	psUpdate.setString(2, "mkyong");
	psUpdate.execute();

Output


2 rows are inserted and 1 row is updated.
output

Download Source Code

$ git clone https://github.com/mkyong/java-jdbc.git
$ cd postgresql

References

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
23 Comment threads
5 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
25 Comment authors
mkyongrogercaptainYisusShi Recent comment authors
newest oldest most voted
Invi
Guest
Invi

but how to do connection rollback in try-with-resources block (without nesting another try-block)?

Edison
Guest
Edison

Do we have to add “dbConnection.setAutoCommit(true);” to finally block?

Yisus
Guest
Yisus

You save me, thanks

roger
Guest
roger

You set them in autoCommit(false); but never change it back :|

captain
Guest
captain

try {
DataBase.insert…
DataBase.update…
DataBase.connection.commit();
System.out.println(“?????????????????”);
} catch (Exception e) {
DataBase.connection.rollback();
System.out.println(“????????????????”);
}
Transaction ???????????catch?MySQLSyntaxErrorException?

Shi
Guest
Shi

Very helpful, love your website.

Andrey
Guest
Andrey

Wonderful clear example.
Really appreciate your work.

Rajeev
Guest
Rajeev

What if ” dbConnection.rollback() ” throws an Exception? .

Ryan
Guest
Ryan

Addition to this, engine must be InnoDB, MyISAM does not support transactions

Victor
Guest
Victor

As a user said… Our lives just became easier. Thanks man

raja
Guest
raja

Hi lokesh
The query will run because the session has been started and even if u stop the JVM the session will run until unless it is in same module

raja
Guest
raja

Hi,
I have a doubt, after updating the values in service level it is not getting committed into the database what may be the reason??

lokesh
Guest
lokesh

Hi,
i have a small doubt in jdbc. suppose i started the query execution which takes more than 5 min from jdbc.after initiating the executequery command.If i stop the jvm.what will happen to that query.

1.Will query run in db?
if yes..how?
2.Will the query execution stops?
if yes ..how?
consider oracle db here…

please reply asap

Sandeep
Guest
Sandeep

Hi Lokesh, you got the anser, if yes please share it

Evan
Guest
Evan

I think Hibernate also use this to do “session.beginTransaction()” and “session.getTransaction().commit()”.

Lisandro Duran
Guest
Lisandro Duran

You’re great man.
Thank you!

Rajesh
Guest
Rajesh

awesome examples, thanks a lot which is helping me a lot

Mustafa Asif
Guest
Mustafa Asif

Simple post even if you want to revise your fundamentals. Thanks.

Binh Thanh Nguyen
Guest
Binh Thanh Nguyen

Thanks, nice post

Marcos
Guest
Marcos

Having a second thought about the right usage finalizers (finally block) and specifically related to the closing of the database connection. I’ve wondered and researched that furthermore to the explicit call of the termination method “dbConnection.close()” within the finally block (which may be used as safety-net measure), it would be more efficient to add an extra “dbConnection.close()” at the end of the “try” block. Could someone tell me if it is worthwhile to do?

karunakaran
Guest
karunakaran

Very good example

Toto
Guest
Toto

I’m very glad that I found your blog ! My life is much easier. Thank you so much :)

pea
Guest
pea

My life is easier since I found your blogs.
Very nice blogs.

arunkumar
Guest
arunkumar

how do i execute sql trigger using jdbc connection ..

Dhinakaran Pragasam
Guest
Dhinakaran Pragasam

Refer below link for execute sql trigger and procedure using jdbc http://techdive.in/java/mysql-createtrigger-using-jdbc-connection