在使用hibernate框架,查询数据库多张表或者单张表的某几个需要的字段数据时,往往只能通过sql语句配合setResultTransformer将查询到的数据封装到一个map集合中,再将map集合放入list中返回数据。
示例:
控制层:查询还款明细,还款单号、流水号、还款人、还款日期、还款金额//查询还款明细,还款单号、流水号、还款人、还款日期、还款金额
List<Map<String,Object>> list2 = receivableService.findContactwriteoffByBillId(billId);
System.out.println("list2的大小:"+list2);
System.out.println("list2的大小:"+list2.size());
JSONObject jsonTotal2 = new JSONObject();
JSONArray jsonArray2 = new JSONArray();
if(list2.size() != 0){
for (int i = 0; i < list2.size(); i++) {
Object fBillno =list2.get(i).get("fBillno");
Object banktransactionserialno =list2.get(i).get("banktransactionserialno");
Object remitoutbankaccountname =list2.get(i).get("remitoutbankaccountname");
Object operationdatetime =list2.get(i).get("operationdatetime");
Object amount =list2.get(i).get("amount");
JSONObject jsonObject2 = new JSONObject();
jsonObject2.put("fBillno", fBillno);
jsonObject2.put("banktransactionserialno", banktransactionserialno);
jsonObject2.put("remitoutbankaccountname", remitoutbankaccountname);
jsonObject2.put("operationdatetime", operationdatetime);
jsonObject2.put("amount", amount);
jsonArray2.add(jsonObject2);
jsonTotal2.put("name", "还款明细");
jsonTotal2.put("options", jsonArray2);
}
service层查询数据
//查询还款明细,还款单号、流水号、还款人、还款日期、还款金额CONTACTWRITEOFF
@Override
public List<Map<String, Object>> findContactwriteoffByBillId(String billId) {
String sql = "SELECT ff.`billno` fBillno,ff.`banktransactionserialno`,ff.`remitoutbankaccountname`,ff.`operationdatetime`,ff.`amount` "
+" FROM fin_contactwriteoff ff WHERE ff.`csbill` ='"+billId+"'";
Query query = this.getSession().createSQLQuery(sql);
query.setResultTransformer(new ResultTransformer() {
private static final long serialVersionUID = 1L;
public Object transformTuple(Object[] val, String[] arg1) {
Map<String,Object> m = new HashMap<String,Object>();
m.put("fBillno", val[0]);
m.put("banktransactionserialno", val[1]);
m.put("remitoutbankaccountname", val[2]);
m.put("operationdatetime", val[3]);
m.put("amount", val[4]);
return m;
}
@Override
public List transformList(List arg0) {
return arg0;
}
});
List<Map<String,Object>> list = query.list();
return list;
}
在控制层查询到的数据做个封装返回到前端即可
注:在使用这种方式时有时会遇到报错的问题,原因是类型不对
//查询账单号,账单金额,还款金额,支付方式,账单生成时间
@Override
public List<Map<String, Object>> findFinContactsummaryByBillId(String billId) {
String sql = "SELECT f.billno,c.companyname,c.executive,f.amount,f.writeoffamount,f.journalizedate FROM core_company c,fin_contactsummary f WHERE c.sysid = f.customer and f.sysid='"+billId+"'";
Query query = this.getSession().createSQLQuery(sql);
List<Map<String, Object>> llList = new ArrayList<Map<String, Object>>();
query.setResultTransformer(new ResultTransformer() {
private static final long serialVersionUID = 1L;
public Object transformTuple(Object[] val, String[] aliases) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("billno", val[0]);
map.put("companyname", val[1]);
map.put("executive", val[2]);
map.put("amount", val[3]);
map.put("writeoffamount", val[4]);
map.put("journalizedate", val[5]);
llList.add(map);
return llList;
}
@Override
public List transformList(List collection) {
return null;
}
});
query.list();
return llList;
方式二:
//查询账单号,账单金额,还款金额,支付方式,账单生成时间
@Override
public List<Map<String, Object>> findFinContactsummaryByBillId(String billId) {
String sql = "SELECT f.billno,c.companyname,c.executive,f.amount,f.writeoffamount,f.journalizedate FROM core_company c,fin_contactsummary f WHERE c.sysid = f.customer and f.sysid='"+billId+"'";
Query query = this.getSession().createSQLQuery(sql);
List<Object[]> listm = query.list();
List<Map<String, Object>> list1 =new ArrayList<Map<String, Object>>();
Map<String, Object> m = new HashMap<String, Object>();
for(Object[] s:listm){
m.put("billno", s[0].toString());
m.put("companyname", s[1].toString());
m.put("executive", s[2].toString());
m.put("amount",s[3].toString());
m.put("writeoffamount", s[4].toString());
m.put("journalizedate", s[5].toString());
list1.add(m);
}
return list1;
}