Java - JDBC - execTransaction
public class Transactions {
String url = "jdbc:mysql://localhost:3306/bank?autoReconnect=true&useSSL=false";
String username = "root";
String password = "Vasara2016!";
public void execTransaction() {
try {
Connection conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
PreparedStatement pst = null;
Scanner scanner = new Scanner(System.in);
System.out.print("Enter From Account ID: ");
int fromAccID = scanner.nextInt();
System.out.print("Enter To Account ID: ");
int toAccID = scanner.nextInt();
System.out.print("Enter Amount to Transfer: ");
double amnt = scanner.nextDouble();
String withdrawsql = "update baccount set balance = balance - ? where id = ?";
pst = conn.prepareStatement(withdrawsql);
pst.setDouble(1, amnt);
pst.setInt(2, fromAccID);
pst.executeUpdate();
String depositsql = "update baccount set balance = balance + ? where id = ?";
pst = conn.prepareStatement(depositsql);
pst.setDouble(1, amnt);
pst.setInt(2, toAccID);
pst.executeUpdate();
String sql = "select * from baccount where id = ?";
pst = conn.prepareStatement(sql);
pst.setInt(1, fromAccID);
ResultSet rs = pst.executeQuery();
double balance = 0;
if(rs.next()) {
balance = rs.getDouble("balance");
}
if(balance >= 5000) {
conn.commit();
System.out.println("Transfer was successful!");
}
else {
conn.rollback();
double olbal = balance + amnt;
System.out.println("Transfer failed! " + "Insufficient funds " + olbal);
}
scanner.close();
pst.close();
conn.close();
}
catch (SQLException e) {
System.out.println("Error: " + e);
}
}
}