Main Tutorials

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 Author

author image
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

Subscribe
Notify of
32 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Invi
7 years ago

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

Yisus
6 years ago

You save me, thanks

Ryan
8 years ago

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

Murry
2 years ago
Reply to  Ryan

Save my life and my time :):) Thank you so much.

Edison
9 years ago

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

kanu
3 years ago

Thanks for the good post, mkyong!

Please let me know why you set AutoCommit mode back to false after commit.

I’d like to know if there is any mechanism advantage.

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

Last edited 3 years ago by kanu
Pan.
2 years ago
Reply to  kanu

You changed the commit-state from a datasource pool. When other people or threads use it, it will be considered to be autocommited, some exception will be occured.

Eduardo
3 years ago

thank you bro!! you are awesome!

roger
5 years ago

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

captain
6 years ago

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

Shi
6 years ago

Very helpful, love your website.

Andrey
6 years ago

Wonderful clear example.
Really appreciate your work.

Rajeev
7 years ago

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

Victor
8 years ago

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

raja
8 years ago

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
8 years ago

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
8 years ago

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
7 years ago
Reply to  lokesh

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

Evan
9 years ago

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

Lisandro Duran
9 years ago

You’re great man.
Thank you!

Rajesh
9 years ago

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

Mustafa Asif
9 years ago

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

Binh Thanh Nguyen
10 years ago

Thanks, nice post

Marcos
10 years ago

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
11 years ago

Very good example

Toto
11 years ago

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

pea
11 years ago

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

arunkumar
11 years ago

how do i execute sql trigger using jdbc connection ..

Dhinakaran Pragasam
11 years ago
Reply to  arunkumar

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