CODEWITHSUNDEEP
javasqljdbc
Waseem Saeed
Waseem Saeed

Reputation: 85

How to run SQL TRANSACTION in a PreparedStatement

I have a SQL Transaction query that I am unable to run. Can any one tell me please why? I have failed to run it using preparedstament.executequery(); as well.

START TRANSACTION;
SELECT total_installment_remaining  FROM payment_loan WHERE loan_id = 1 FOR UPDATE;
UPDATE payment_loan SET total_installment_remaining =total_installment_remaining-1 WHERE loan_id = 1;
COMMIT;

Upvotes: 2

Views: 3341

Answers (2)

user330315
user330315

Reputation:

Turn off autocommit, then use Connection.commit() to end the transaction.

connection.setAutocommit(false);
Statement stmt = con2.createStatement();

// this will automatically start a transaction
ResultSet rs = stmt.executeQuery("SELECT total_installment_remaining  FROM payment_loan WHERE loan_id = 1 FOR UPDATE");

// process the result if needed
...

stmt.executeUpdate("UPDATE payment_loan SET total_installment_remaining =total_installment_remaining-1 WHERE loan_id = 1");

// end the transaction and persist then changes
connection.commit();

If you don't need the result of the SELECT in your code, then you don't really need the SELECT ... FOR UPDATE in the first place, because the UPDATE will lock the row anyway.

Upvotes: 6

Waseem Saeed
Waseem Saeed

Reputation: 85

String query = "START TRANSACTION;SELECT total_installment_remaining  FROMpayment_loan WHERE loan_id = 1 FOR UPDATE;UPDATE payment_loan SET total_installment_remaining =total_installment_remaining-1 WHERE loan_id = 1;COMMIT;";
    try {
        ps2 = con2.prepareStatement(query);
        ResultSet rs2 = ps2.executeQuery();
        while (rs2.next()) {
            rs2.getInt(1);
        }
        // rs.close();
        // con.close();
    } catch (SQLException e) {
        logger.error("",e);
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

Upvotes: -3

Related Questions