Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.concurrent.Callable;
- import java.util.concurrent.ExecutorService;
- import java.util.concurrent.Executors;
- import java.util.concurrent.Future;
- import java.util.concurrent.TimeUnit;
- public class TT {
- private static final String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=q";
- private static final String SQL_SETUP_CONTEXT =
- "DROP TABLE IF EXISTS accounts;"+"\n"+
- "CREATE TABLE accounts(id int, amount int);"+"\n"+
- "INSERT INTO accounts VALUES(1,100);"+"\n"+
- "INSERT INTO accounts VALUES(2,100)";
- private static final String SQL_AMOUNT_DEDUCTING_FUNCTION =
- "CREATE OR REPLACE FUNCTION test (id1 int, id2 int) RETURNS boolean AS $$"+"\n"+
- "BEGIN"+"\n"+
- //"\tUPDATE accounts SET amount=amount WHERE id IN (id1,id2);"+"\n"+
- //"\tUPDATE accounts SET amount=amount WHERE id=id2;"+"\n"+
- "\tIF (SELECT SUM(amount) FROM accounts WHERE id IN (id1,id2)) >= 0 THEN"+"\n"+
- "\t\tUPDATE accounts SET amount=amount-200 WHERE id=id1;"+"\n"+
- "\t\tRETURN true;"+"\n"+
- "\tEND IF;"+"\n"+
- "\tRETURN false;"+"\n"+
- "END;"+"\n"+
- "$$ LANGUAGE plpgsql;";
- private static final String SQL_UPDATE_AMOUNTS =
- "UPDATE accounts SET amount = 100";
- private static final String SQL_GET_AMOUNTS =
- "SELECT amount FROM accounts ORDER BY id";
- private static final String SQL_DEDUCT_AMOUNT =
- "START TRANSACTION ISOLATION LEVEL SERIALIZABLE;"+"\n"+
- "SELECT * FROM test(?,?);"+"\n"+
- "END TRANSACTION;";
- public static void main(String[] args) {
- try {
- Class.forName("org.postgresql.Driver");
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- return;
- }
- Connection conn = null;
- try {
- conn = DriverManager.getConnection(url);
- } catch (SQLException e) {
- e.printStackTrace();
- return;
- }
- Statement stmt = null;
- try{
- stmt = conn.createStatement();
- }
- catch(SQLException e){
- e.printStackTrace();
- try {
- conn.close();
- } catch (SQLException e1) {
- e1.printStackTrace();
- }
- return;
- }
- try {
- stmt.execute(SQL_SETUP_CONTEXT);
- stmt.execute(SQL_AMOUNT_DEDUCTING_FUNCTION);
- Callable<Boolean> deductMoneyFromAccnt1 = new Callable<Boolean>() {
- @Override
- public Boolean call() {
- return deductMoneyFromAccount(1,2);
- }
- };
- Callable<Boolean> deductMoneyFromAccnt2 = new Callable<Boolean>() {
- @Override
- public Boolean call() {
- return deductMoneyFromAccount(2,1);
- }
- };
- ExecutorService e = Executors.newFixedThreadPool(2);
- int cnt=0;
- while(cnt++ < 10){
- stmt.executeUpdate(SQL_UPDATE_AMOUNTS);
- Future<Boolean> futureAccnt1 = e.submit(deductMoneyFromAccnt1);
- Future<Boolean> futureAccnt2 = e.submit(deductMoneyFromAccnt2);
- try {
- futureAccnt1.get();
- futureAccnt2.get();
- } catch (Exception e1) {
- e1.printStackTrace();
- }
- ResultSet rs = stmt.executeQuery(SQL_GET_AMOUNTS);
- rs.next(); int amount1 = rs.getInt(1);
- rs.next(); int amount2 = rs.getInt(1);
- System.out.println("Amounts=["+amount1+","+amount2+"]\n");
- rs.close();
- }
- e.shutdown();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- finally {
- try {
- stmt.close();
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- private static Boolean deductMoneyFromAccount(int id1, int id2){
- /*try {
- TimeUnit.MILLISECONDS.sleep((id1 - 1) * 5);
- } catch (InterruptedException e1) {
- e1.printStackTrace();
- }*/
- Connection conn = null;
- PreparedStatement pstmt = null;
- try {
- conn = DriverManager.getConnection(url);
- pstmt = conn.prepareStatement(SQL_DEDUCT_AMOUNT);
- pstmt.setInt(1, id1);
- pstmt.setInt(2, id2);
- boolean r = pstmt.execute();
- return r;
- } catch (SQLException e) {
- System.out.println("Deducting from account="+id1+" gave "+e.getMessage());
- }
- finally{
- try {
- pstmt.close();
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- return null;
- }
- }
Add Comment
Please, Sign In to add comment