今天项目中碰见多表查询,订单表,商品表(作业更新,商品-门店,每个门店有相同的商品,所有商品重复),门店表,现在要关联查询三个表,根据门店号查出所有商品,订货情况;
我的思路是新建对象,将要展示的数据封装。用sql语句查询
String sql="select isnull(o.oid,0) as oid,g.goodscode,g.goodsname,isnull(o.qty,0) as qty,g.saleqty7,g.saleqty6,g.saleqty5,g.saleqty4,g.saleqty3,g.saleqty2,g.saleqty1,g.orderqty2,g.orderqty1,g.mdkc,g.alcqty,g.rtlprc,g.dhtime
from dbo.zhipei_goods g left join dbo.zhipei_order o on o.goodscode=g.goodscode and convert(varchar,o.bcsj,102)=convert(varchar,getDate(),102) and g.storecode=o.storecode where g.billto=? and g.storecode=?";
Session session = this.getSession();
SQLQuery query=session.createSQLQuery(sql);
query.setInteger(0, gid);
query.setString(1, storecode);
List list=query.list();
List<Zplianhe> cylist=new ArrayList<Zplianhe>();
for(Iterator iterator = list.iterator();iterator.hasNext();){
Object[] objects = (Object[]) iterator.next(); // 将每个对象属性值存入数组
Integer oid=(Integer) objects[0]; //依次取出数组的值转换
...
Zplianhe lianhe=new Zplianhe();
lianhe.setOid(oid);//给对象赋值
...
cylist.add(lianhe);//对象付给集合
}
在页面from表单隐藏订单对象name属性
<input type="hidden" name="orlist[${varStatus.index}].oid" value="${list.oid}"/>
<input type="hidden" name="orlist[${varStatus.index}].storecode" value="${as.storecode}">
必须要有下标,证明每个商品都是一个对象
在action中要有订单集合并设置set,get方法
遍历订单集合,修改/保存