/
Group3AcidDemo1.java
75 lines (62 loc) · 2.36 KB
/
Group3AcidDemo1.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package CS623;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Group3AcidDemo1 {
public static void main(String args[]) throws SQLException, IOException, ClassNotFoundException {
// Load the MySQL driver
//Class.forName("com.mysql.jdbc.Driver");
// Connect to the database
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/CS623?serverTimezone=UTC","username","password");
// For atomicity
conn.setAutoCommit(false);
// For isolation
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
System.out.println("Check initial status...");
Statement stmt1=conn.createStatement();
ResultSet rs=stmt1.executeQuery("Select * FROM product");
System.out.println("In Table <product>");
while (rs.next()){
System.out.println(rs.getString("prod_id")+","+rs.getString("pname"));
}
rs=stmt1.executeQuery("Select * FROM Stock");
System.out.println("In Table <stock>");
while (rs.next()){
System.out.println(rs.getString("prod_id")+","+rs.getString("depo_id")+","+rs.getString("quantity"));
}
Statement stmt2 = null;
try {
// create statement object
stmt2 = conn.createStatement();
//Update table without FOREIGN KEY CONSTRAINT
stmt2.executeUpdate("UPDATE product SET prod_id='pp1' WHERE prod_id='p1'");
//stmt2.executeUpdate("PROMPT FAILURE");
stmt2.executeUpdate("UPDATE stock SET prod_id='pp1' WHERE prod_id='p1'");
} catch (SQLException e) {
System.out.println("Transaction failure, rollback procedure. Catch Exception " +e);
// For atomicity
conn.rollback();
stmt2.close();
conn.close();
return;
} // main
conn.commit();
stmt2.close();
System.out.println("Transaction successful. Check final status...");
rs=stmt1.executeQuery("Select * FROM product");
System.out.println("In Table <product>");
while (rs.next()){
System.out.println(rs.getString("prod_id")+","+rs.getString("pname"));
}
rs=stmt1.executeQuery("Select * FROM Stock");
System.out.println("In Table <stock>");
while (rs.next()){
System.out.println(rs.getString("prod_id")+","+rs.getString("depo_id")+","+rs.getString("quantity"));
}
stmt1.close();
conn.close();
}
}