Commit / RollBack

[Former Member]

Hi,

I want to execute 2 or more database operation in one transcation with commit/rollback mechanism.

Below is my code,

How can i make it has commit /rollback (in one transaction).

Thanks.

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource) ctx.lookup("jdbc/SAPXXXDB");

Connection con1 = ds.getConnection();

Connection con2 = ds.getConnection();

PreparedStatement pstm1 =con1.prepareStatement("insert into ZTABLE1(...)Values(...)";

PreparedStatement pstm2 =con2.prepareStatement("insert into ZTABLE2(...)Values(...)";

pstm1.executeUpdate();

pstm2.executeUpdate();

con1.close();

con2.close();

[Former Member · Accepted solution]

Hi Cemil Bozlagan ,

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed.

The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode by using Connection object.

con.setAutoCommit(false);

Once auto-commit mode is disabled, no query statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.

Have a look

PreparedStatement pstm1 =con1.prepareStatement("insert into ZTABLE1(...)Values(...)";

PreparedStatement pstm2 =con2.prepareStatement("insert into ZTABLE2(...)Values(...)";

pstm1.executeUpdate();
pstm2.executeUpdate();
con.commit();
con.setAutoCommit(true);

To roll back the transaction based on ur requirement u can use

con.rollback()

Regards,

srikanth