一、方法实现
private JdbcTemplate jdbcTemplate = new JdbcTemplate(DBConn.getDataSource());
@SuppressWarnings("deprecation")
@Override
public boolean insertObject(Orders entity) {
String sql1 = "insert into orders(number,status,uid) values(?,?,?)";
// 保存订单
int num1 = jdbcTemplate.update(sql1, new Object[] { entity.getNumber(),
"未付款", entity.getUser().getId() });
if (num1 > 0) {
//获取订单id
int id = jdbcTemplate.queryForInt(
"select id from orders where number=?", entity.getNumber());
//设置订单id
entity.setId(id);
boolean flag = false;
// 保存订单详情
List ordersItems = entity.getOrdersItems();
String sql2 = "insert into ordersItem(gid,num,price,oid)values(?,?,?,?)";
if (ordersItems != null) {
for (OrdersItem item : ordersItems) {
// 保存订单条目
int num2 = jdbcTemplate.update(
sql2,
new Object[] { item.getGoods().getId(),
item.getNum(), item.getPrice(),
item.getOrders().getId() });
if (num2 > 0) {
} else {
flag = true;
break;
}
}
if (flag) {
System.out.println("OrdersItem 有一个插入失败");
} else {
// 查出总价格
String sql3 = "select sum(price) as sum from ordersItem where oid=?";
Double sum = jdbcTemplate.queryForObject(sql3,
new RowMapper() {
@Override
public Double mapRow(ResultSet rs, int arg1)
throws SQLException {
// TODO Auto-generated method stub
return rs.getDouble("sum");
}
}, entity.getId());
// 更新订单总价格
String sql4 = "update orders set sum=? where id=?";
int num3 = jdbcTemplate.update(sql4, new Object[] { sum,
entity.getId() });
if (num3 > 0) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}
}
} else {
System.out.println("订单插入失败");
}
}
return false;
}
二、测试
public class OrderTest {
private OrdersDao ordersDao=new OrdersDaoImpl();
@Test
public void test1(){
Orders orders=new Orders();
orders.setNumber("0x0014");
User u=new User();
u.setId(1);
orders.setUser(u);
//创建一个订单集合
List ordersItems=new ArrayList();
Goods goods=new Goods(1, "", 5000, null, null);
OrdersItem item1=new OrdersItem(goods, 3, 3*goods.getPrice(), orders);
ordersItems.add(item1);
Goods goods1=new Goods(2, "", 11, null, null);
OrdersItem item2=new OrdersItem(goods1, 3, 3*goods1.getPrice(), orders);
ordersItems.add(item2);
orders.setOrdersItems(ordersItems);
ordersDao.insertObject(orders);
}
}
三、事务处理的方法
private JdbcTemplate jdbcTemplate = new JdbcTemplate(DBConn.getDataSource());
@Override
public boolean insertObject(final Orders entity) {
//事务处理,保证下面的语句要么全都执行,要么全都不执行
//事务管理对象
PlatformTransactionManager transactionManager=new DataSourceTransactionManager(DBConn.getDataSource());
//事务管理的模板对象
TransactionTemplate template=new TransactionTemplate(transactionManager);
//执行操作
return template.execute(new TransactionCallback() {
@Override
public Boolean doInTransaction(TransactionStatus ts) {
String sql1 = "insert into orders(number,status,uid) values(?,?,?)";
// 保存订单
int num1 = jdbcTemplate.update(sql1, new Object[] { entity.getNumber(),
"未付款", entity.getUser().getId() });
if (num1 > 0) {
//获取订单id
@SuppressWarnings("deprecation")
int id = jdbcTemplate.queryForInt(
"select id from orders where number=?", entity.getNumber());
//设置订单id
entity.setId(id);
boolean flag = false;
// 保存订单详情
List ordersItems = entity.getOrdersItems();
String sql2 = "insert into ordersItem(gid,num,price,oid)values(?,?,?,?)";
if (ordersItems != null) {
for (OrdersItem item : ordersItems) {
// 保存订单条目
int num2 = jdbcTemplate.update(
sql2,
new Object[] { item.getGoods().getId(),
item.getNum(), item.getPrice(),
item.getOrders().getId() });
if (num2 > 0) {
} else {
flag = true;
break;
}
}
if (flag) {
System.out.println("OrdersItem 有一个插入失败");
ts.setRollbackOnly();
return false;
} else {
// 查出总价格
String sql3 = "select sum(price) as sum from ordersItem where oid=?";
Double sum = jdbcTemplate.queryForObject(sql3,
new RowMapper() {
@Override
public Double mapRow(ResultSet rs, int arg1)
throws SQLException {
// TODO Auto-generated method stub
return rs.getDouble("sum");
}
}, entity.getId());
// 更新订单总价格
String sql4 = "update orders set sum=? where id=?";
int num3 = jdbcTemplate.update(sql4, new Object[] { sum,
entity.getId() });
if (num3 > 0) {
System.out.println("更新成功");
return true;
} else {
System.out.println("更新失败");
ts.setRollbackOnly();
return false;
}
}
} else{
return true;
}
}else {
System.out.println("订单插入失败");
ts.setRollbackOnly();
return false;
}
}
});
}