1 package org.day02;
2
3 import java.sql.Connection;
4 import java.sql.Date;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9
10 import org.day01.ConnectionUtils;
11
12 public class TestPK {
13 /**
14 * for Oracle
15 *
16 * @throws SQLException
17 */
18 public void addOrder1() throws SQLException {
19 Connection con = null;
20 PreparedStatement stmt = null;
21
22 try {
23 con = ConnectionUtils.getConnection();
24 con.setAutoCommit(false);
25 // 获取ID
26 stmt = con.prepareStatement("select my_order_seq.nextval from dual");
27 ResultSet rs = stmt.executeQuery();
28 rs.next();
29 int orderId = rs.getInt(1);
30 System.out.println(orderId);
31 stmt.close();
32
33 // 插入Order
34 stmt = con.prepareStatement("insert into my_order values(?,?)");
35 stmt.setInt(1, orderId);
36 stmt.setDate(2, new Date(System.currentTimeMillis()));
37 stmt.executeUpdate();
38 stmt.close();
39
40 // 插入2个Item
41 stmt = con
42 .prepareStatement("insert into my_item values(my_item_seq.nextval,?,?)");
43
44 stmt.setString(1, "java");
45 stmt.setInt(2, orderId);
46 stmt.executeUpdate();
47
48 stmt.setString(1, "php");
49 stmt.setInt(2, orderId);
50 stmt.executeUpdate();
51 stmt.close();
52
53 con.commit();
54
55 } catch (SQLException e) {
56 e.printStackTrace();
57 con.rollback();
58 throw e;
59 } finally {
60 if (con != null) {
61 con.close();
62 }
63 }
64 }
65
66 /**
67 * for MySQL
68 *
69 * @throws SQLException
70 */
71 public void addOrder2() throws SQLException {
72 Connection con = null;
73 PreparedStatement stmt = null;
74
75 try {
76 con = ConnectionUtils.getConnection();
77 con.setAutoCommit(false);
78
79 // 插入Order
80 stmt = con.prepareStatement("insert into my_order values(null,?)",
81 Statement.RETURN_GENERATED_KEYS);
82 stmt.setDate(1, new Date(System.currentTimeMillis()));
83 stmt.executeUpdate();
84
85 ResultSet rs = stmt.getGeneratedKeys();
86 rs.next();
87 int orderId = rs.getInt(1);
88 System.out.println(orderId);
89 stmt.close();
90
91 // 插入2个Item
92 stmt = con.prepareStatement("insert into my_item values(null,?,?)");
93
94 stmt.setString(1, "java");
95 stmt.setInt(2, orderId);
96 stmt.executeUpdate();
97
98 stmt.setString(1, "php");
99 stmt.setInt(2, orderId);
100 stmt.executeUpdate();
101 stmt.close();
102
103 con.commit();
104
105 } catch (SQLException e) {
106 e.printStackTrace();
107 con.rollback();
108 throw e;
109 } finally {
110 if (con != null) {
111 con.close();
112 }
113 }
114 }
115
116 public static void main(String[] args) throws Exception {
117 TestPK pk = new TestPK();
118 pk.addOrder2();
119 }
120 }
