关于查询操作,涉及多表查询、复杂条件、分页条件:
public QueryResult selectResultSet(BizConditionDTO bizCdn, Integer pageIndex, Integer pageSize)
{
//准备SQL
StringBuffer querySQL = new StringBuffer("");
querySQL.append("select e.AHAF0001,p.AHAP0016 personName,p.AHAP0015 idCard,e.AHAF0002 jwRegion," +
"e.BHZX0002 fileNo,p.BHAP0046 organId,e.BHAF0003 applyDate,e.BHAF0005 status ");
querySQL.append("from GOMZ_DATA_HAB01 e inner join GOMZ_DATA_HZB02 p " );
querySQL.append("on e.AHAF0001=p.ahaf0001 $condition");
Sql sql = Sqls.create( querySQL.toString() );
Cnd cnd = Cnd.where("1", "=", 1);
if(bizCdn.getBizType()!=null)
cnd = cnd.and("bizNo","=",bizCdn.getBizType());
if(bizCdn.getOperateType()!=null)
cnd = cnd.and("jzEventType","=",bizCdn.getOperateType());
if(bizCdn.getProcessType()!=null)
cnd = cnd.and("","=",bizCdn.getProcessType());
if(bizCdn.getOrganId()!=null)
cnd = cnd.and("regionId","=",bizCdn.getOrganId());
if(bizCdn.getPersonName()!=null)
cnd = cnd.and("personName","=",bizCdn.getPersonName());
if(bizCdn.getIdCard()!=null)
cnd = cnd.and("idCard","=", bizCdn.getIdCard());
if(bizCdn.getRegion()!=null)
cnd = cnd.and("residentId","=",bizCdn.getRegion());
if(bizCdn.getStatus()!=null)
cnd = cnd.and("eventStatus","=",bizCdn.getStatus());
sql.setCondition(cnd); //条件占位符赋值
if(pageIndex==null)
pageIndex = 1;
if(pageSize==null)
pageSize = 10;
Pager pager = dao.createPager(pageIndex, pageSize);
sql.setPager(pager); //设置分页条件
sql.setCallback(Sqls.callback.entities());//设置实体
sql.setEntity(dao.getEntity(JzPersonDO.class));
//执行SQL查询
long count = Daos.queryCount(dao, sql);
dao.execute(sql);
List<JzPersonDO> list = sql.getList(JzPersonDO.class);
pager.setRecordCount((int) count);
QueryResult qr = new QueryResult(list,pager);
return qr;
}