Postgresql transaction handling with java

Question

I have two blocks of queries with preparedStatement.

This is the first:

String sql = "update cikan_malzeme set miktar = ? where proje_id = ? and malzeme_id = ?";
PreparedStatement prep = dbConnect.connection.prepareStatement(sql);
prep.setFloat(1, toplam);
prep.setInt(2, pid);
prep.setInt(3, mid);
prep.executeUpdate();

And this is the second:

String sql2 = "update malzemeler set miktar = ? where malz_adi = ?";
PreparedStatement prep2 = dbConnect.connection.prepareStatement(sql2);
prep2.setFloat(1, fark);
prep2.setString(2, malzemeadi);
prep2.executeUpdate();

Now I want to execute them with the transaction BEGIN; and COMMIT; How can I handle transaction with preparedStatement?

Mark Rotteveel · Accepted Answer · 2021-07-23 07:41:06Z

11

You should use Connection.setAutoCommit(false) to disable auto-commit and Connection.commit() and Connection.rollback().

When auto-commit is disabled, a transaction will be started automatically the first time you execute a command or query that requires a transaction.

You should not be using the database specific transaction control commands, as the driver will most likely do additional cleanup of resources when a commit() or rollback() is issued.

edited Jul 23, 2021 at 7:41

answered Nov 23, 2011 at 11:55

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

duffymo · Accepted Answer · 2011-11-23 09:13:30Z

10

Set auto commit to false.

Put your PreparedStatements in a try block. Commit at the end; rollback in the catch block.

That's how it's usually done in bare bones JDBC.

http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

If you use EJB3 or Spring you can add a transaction manager and specify them declaratively. That's more sophisticated and flexible.

answered Nov 23, 2011 at 9:13

duffymo

310k46 gold badges376 silver badges571 bronze badges

4 Comments

SatyaTNV Over a year ago

Does it needed on auto commit mode for SELECT queries like setAutoCommit(true) after creating session or configuring in hibernate file? I'm using hibernate with postgresql.

duffymo Over a year ago

No need for transactional logic if you're doing a SELECT on its own. It's for write transactions and managing the rollback segment.

SatyaTNV Over a year ago

Means only for non select queries it's required and for select queries it's not required right

duffymo Over a year ago

Required when you have one or more write statements that you want to execute as a single unit of work. You should read about transactions, SQL, and JDBC.

Collectives™ on Stack Overflow

Postgresql transaction handling with java

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related