ajanuskevicius
8/18/2016 - 6:09 PM

Java - JDBC - execTransaction

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);
		}
	}
}