datagrid 查询关联sql

1.A表字段：id,name;B表字段：id,name,fid(A表外键)，现查询A表和B表的所有数据并且查询条件A,B都有，在前台页面list显示

2.后台方法：

@RequestMapping(params = "datagrid_zh")
public void datagrid_zh(CarRouteEntity ldcHandbookOrder,HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
CriteriaQuery cq = new CriteriaQuery(CarRouteEntity.class, dataGrid);
//查询条件组装器
org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil.installHql(cq, ldcHandbookOrder);
try{
//自定义追加查询条件
}catch (Exception e) {
throw new BusinessException(e.getMessage());
}
cq.add();
String tiaojian="";
String name=request.getParameter("name");   

if(StringUtils.isNotEmpty(name)) {
tiaojian+=" and a.name>= '"+name+"'";

}  
String sql="select a.id,a.name,b.name as bname from Aa LEFT JOIN B b on a.id=b.fid where 1=1 "+tiaojian;
List<Map<String, Object>> map=jdbcTemplate.queryForList(sql);
dataGrid.setTotal(map.size());
List<Map<String,Object>> maplist=systemService.findForJdbc(sql +" limit "+(dataGrid.getPage()-1)*dataGrid.getRows()+","+dataGrid.getRows(), null);

--oracle用法

　　

//一页几行
int row = dataGrid.getRows();
//当前页
int page = dataGrid.getPage();
//开始行
int firstRow = row*(page-1)+1; 
//截止行
int endRow=firstRow+row; 

List<Map<String,Object>> maplist=systemService.findForJdbc(sql +"where myno >="+firstRow+" and myno<"+endRow+" order by CREATE_DATE desc ", null);
dataGrid.setResults(maplist); 
for (int i=0;i<maplist.size();i++) {
// 此为针对原来的行数据，拓展的新字段

Map<String,Object> map = maplist.get(i); 
sql = " select count(1) "
+ " from ldc_invt_list a where a.ldc_headid = '"+ map.get("id")+"' and rownum <= 1 ";
int queryForInt = jdbcTemplate.queryForInt(sql);
if(queryForInt==1){
sql = " select goodswuliao,gds_Nm,LAWF_UNITCD_NAME,dcl_Unitcd_Name,dcl_Currcd ||' '|| dcl_Currcd_Name dclCurrcdName, "
+ " natcd ||' '|| natcd_Name natcdName ,putrec_Seqno putrecSeqno,gds_Mtno gdsMtno "
+ "from ldc_invt_list a where a.ldc_headid = '"+ map.get("id")+"' and rownum <= 1 "; 
Map<String, Object> queryForMap = jdbcTemplate.queryForMap(sql); 
map.put("goodswuliao", queryForMap.get("goodswuliao"));
map.put("gdsNm", queryForMap.get("gds_Nm"));
map.put("lawfUnitcdName", queryForMap.get("LAWF_UNITCD_NAME"));
map.put("dclUnitcdName", queryForMap.get("dcl_Unitcd_Name"));
map.put("dclCurrcdName", queryForMap.get("dclCurrcdName"));
map.put("natcdName", queryForMap.get("natcdName"));


map.put("putrecSeqno", queryForMap.get("putrecSeqno"));
map.put("gdsMtno", queryForMap.get("gdsMtno"));

maplist.set(i, map);
}
}

　　

dataGrid.setResults(maplist);
//下面合计项 orderBizhi放在该字段下面

dataGrid.setFooter("orderTax,orderse,orderBizhi:合计");

TagUtil.datagrid(response, dataGrid);
}

3.前台页面

<t:datagrid name="??" checkbox="true" pagination="true" pageSize="10"
sortOrder="desc"
btnCls="bootstrap btn btn-success btn-xs"  
fitColumns="false" title=""
actionUrl="??.do?datagrid_zh"
idField="id" fit="true" queryMode="group">
<t:dgCol title="id" field="id" hidden="true" queryMode="group" width="120"></t:dgCol>
<t:dgCol title="A表名称" field="name"   query="true" width="120"></t:dgCol>
<t:dgCol title="B表名称" field="bname"   query="true" width="120"></t:dgCol>
</t:datagrid>