JSP---网上商城-手动提交事务,保证数据一致性

1 public int add(SalesOrder so) {
 2         int orderId = -1;
 3         DB db = new DB();
 4         Connection connection = db.getConn();
 5         boolean autoCommit = true;
 6         try {
 7             autoCommit = connection.getAutoCommit();//将默认的提交方式先记下来，以恢复现场用
 8             connection.setAutoCommit(false);
 9         } catch (SQLException e1) {
10             // TODO Auto-generated catch block
11             e1.printStackTrace();
12         }
13         String sql = "insert into SalesOrder(uid,status) values(?,?)";
14         try {
15             // 订单写入数据库
16             PreparedStatement ps = connection.prepareStatement(sql,
17                     Statement.RETURN_GENERATED_KEYS);//在添加订单的同时,获得该订单的自动生成的id
18             ps.setInt(1, so.getCustomer().getCid());
19             ps.setInt(2, so.getStatus());
20             ps.executeUpdate();
21 
22             // 获得自动生成的新订单号
23             ResultSet rsKey = ps.getGeneratedKeys();
24             rsKey.next();
25             orderId = rsKey.getInt(1);
26 
27             // 将所有的订单项写入数据库
28             String sqlItem = "insert into salesItem(pid,pcount,oid) values(?,?,?)";
29             PreparedStatement psItem = connection.prepareStatement(sqlItem);
30             List<SalesItem> items = so.getItems();
31             Iterator<SalesItem> it = items.iterator();
32             while (it.hasNext()) {
33                 SalesItem item = it.next();
34                 psItem.setInt(1, item.getItem().getProduct().getPid());
35                 psItem.setInt(2, item.getItem().getCount());
36                 psItem.setInt(3, orderId);
37                 psItem.addBatch();//将每个订单项添加操作加入批处理作业中
38             }
39             psItem.executeBatch();//执行批处理
40             connection.commit();//手动提交数据库
41             ps.close();
42             psItem.close();
43 
44         } catch (SQLException e) {
45             try {
46                 connection.rollback();
47             } catch (SQLException e1) {
48                 e1.printStackTrace();
49             }
50             e.printStackTrace();
51         } finally {
52             try {
53                 connection.setAutoCommit(autoCommit);
54                 connection.close();
55                 db.close();
56             } catch (SQLException e) {
57                 e.printStackTrace();
58             }
59         }
60         return orderId;
61     }