订单
对应数据库中两张表:salesorder(id,userid,addr,odate,status);salesitem(id,productid,unitprice,pcount,orderid);
对应两个类:SalesOrder,SalesItem;
properties:id,userId,addr,odate,Cart c;
properties:id,productId,unitPrice,pcount,orderId;
OrderMgr:
methods:saveOrder(SalesOrder so);
OrderMySqlDAO:
public void saveOrder(SalesOrder so) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DB.getConn();
conn.setAutoCommit(false);
String sql = "insert into salesorder values(null, ?, ?, ?, ?)";
pstmt = DB.getPStmt(conn, sql, true);
pstmt.setInt(1, so.getUserId());
pstmt.setString(2, so.getAddr());
pstmt.setTimestamp(3, so.getOdate());
pstmt.setInt(4, so.getStatus());
pstmt.executeUpdate();
//得到主键的值
rs = pstmt.getGeneratedKeys();
rs.next();
int key = rs.getInt(1);
String sqlItem = "insert into salesitem values(null, ?, ?, ?, ?)";
pstmt = DB.getPStmt(conn, sqlItem);
List<CartItem> items = so.getCart().getItems();
for(int i=0; i<items.size(); i++) {
CartItem ci = items.get(i);
pstmt.setInt(1, ci.getProductId());
pstmt.setDouble(2, ci.getPrice());
pstmt.setInt(3, ci.getCount());
pstmt.setInt(4, key);
pstmt.addBatch();
}
pstmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
conn.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
DB.closeStmt(pstmt);
DB.closeResultSet(rs);
DB.closeConn(conn);
}
}
DB中添加方法
public static PreparedStatement getPStmt(Connection conn, String sql, boolean generatedKey){
PreparedStatement pStmt = null;
try {
conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pStmt = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pStmt;
}