java级联添加_JavaWeb学习记录（十三）——商城购物之添加订单的数据库级联操作...

一、方法实现

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;

}

}

});

}