CODEWITHSUNDEEP
javamysqldatabasetransactions
user2525364
user2525364

Reputation: 93

MySQL Transactions in Java

I am trying to implement some sort of transaction and keep failing.

Here is my code:

PreparedStatement ac = con.prepareStatement("UPDATE Stock "
        + "SET Stock= Stock - ('"+stockinput+"')"
        + "WHERE StockID =('"+stockID+"')");
ac.executeUpdate();
PreparedStatement dc = con.prepareStatement("SELECT StockAmount FROM Stock");
ResultSet results = dc.executeQuery();
while(results.next()){
    int temp = Integer.parseInt(results.getString("StockAmount"));
    if(stockinput < temp ){
        con.rollback();
    }
    else{
        con.commit();
    }
}

However, it doesn't rollback.

Have spent nearly an hour trying to find a fix.

Would really appreciate it if someone could help me out.

Upvotes: 0

Views: 138

Answers (2)

mel3kings
mel3kings

Reputation: 9415

set the connection to autocommit(false);

con.setAutoCommit(false);

How to use prepared statement:

PreparedStatement ac = con.prepareStatement("UPDATE Stock "
        + "SET Stock= Stock - (?) "
        + "WHERE StockID = ?;
ac.setInt(1, stockinput); // setString or whatever data type
ac.setString(2, stockID);

PreparedStatements precompiles and stores in a SQL statement. This object can then be used to efficiently execute this statement multiple times.

Upvotes: 0

duffymo
duffymo

Reputation: 308998

You forgot the first line.

I'd suggest that you need to learn more about JDBC. Who would be so foolish as to use PreparedStatement and not use bind variables?

I wouldn't like this logic, anyway. If the stock amount shouldn't be negative, I'd add that as a constraint in the database. And why check after you've done the UPDATE? Check before. This code makes little sense.

// i don't see this in your code.
con.setAutoCommit(false);
PreparedStatement ac = con.prepareStatement("UPDATE Stock "
        + "SET Stock= Stock - ('"+stockinput+"')"
        + "WHERE StockID =('"+stockID+"')");
ac.executeUpdate();
PreparedStatement dc = con.prepareStatement("SELECT StockAmount FROM Stock");
ResultSet results = dc.executeQuery();
while(results.next()){
    int temp = Integer.parseInt(results.getString("StockAmount"));
    if(stockinput < temp ){
        con.rollback();
    }
    else{
        con.commit();
    }
}

Upvotes: 1

Related Questions