一、通过jdbcTemplate.queryForObject获取查询数据的总数
protected int count(String sql,StringBuilder sb,List<Object> quer){
QLBuilder sqlBuilder = new QLBuilder();
sql = sql.substring(sqlBuilder.findIndexOf(sql, "from"), sql.length());
sql = " SELECT count(*) " + sql;
int count = jdbcTemplate.queryForObject(sql+sb.toString(),quer.toArray(), Integer.class);
return count;
}
第一个参数 String sql是你所写的SQL语句,第二个参数 StringBuilder sb是查询条件,第三个参数 List<Object> quer 就是查询条件的具体值。
二、限制查询的数量
* @param sql sql语句
* @param sb sql查询条件
* @param order sql排序条件
* @param quer sql查询条件值
* @param startIndex 限制几条记录
* @param pageSize 页码
* @return
*/
protected List<Map<String, Object>> query(String sql,StringBuilder sb,String order,List<Object> quer,Integer startIndex, Integer pageSize){
if(sb!=null){
sql=sql+sb.toString();
}
if(order!=null){
sql = sql +order;
}
if (null != startIndex && null != pageSize) {
sql = sql + " limit " + startIndex + "," + pageSize;
} else if (null != startIndex && null == pageSize) {
sql = sql + " limit " + startIndex;
}
List<Map<String, Object>> result= jdbcTemplate.queryForList(sql,quer.toArray());
return result;
}
下面来个简单的Demo:
String sql="select k.id,k.kcmc,k.kcjj,x.xm as xym,j.xm as jgm,o.ddje,o.zffs from t_ywk_kcdg o "
+ "join t_ywk_kckb k on o.kckbid = k.id "
+ "join t_jck_xyxx x on k.xyjsid=x.id "
+ "join t_jck_jzgxx j on k.kcjsid = j.id where o.xyid=?";
</pre><pre name="code" class="sql"><pre name="code" class="java"> //查询条件
List<Object> quer=new ArrayList();
quer.add(xyid);
StringBuilder sb = new StringBuilder("");
if (StringUtils.isNotBlank(kcname)) {
sb.append(" and k.kcmc like ? ");
quer.add("%"+kcname+"%");
}
if(StringUtils.isNotBlank(jsname)){
sb.append(" and ( x.xm like ? or j.xm like ?)");
quer.add("%"+jsname+"%");
quer.add("%"+jsname+"%");
}
if(payway!=null){
sb.append(" and o.zffs=? ");
quer.add(payway);
}
String order =" order by o.cjsj desc";
List<Map<String, Object>> list = this.query(sql, sb,order,quer, pageList.getPageNumber()-1,pageList.getObjectsPerPage());
List<Map<String, Object>> list = this.query(sql, sb,order,quer, pageList.getPageNumber()-1,pageList.getObjectsPerPage());
pageList.setFullListSize( this.count(sql,sb, quer));
由于涉及保密性问题,部分代码不会粘贴出来,但关键部分的代码写出来,逻辑再拼凑一下就可以了。
有什么可以优化的地方,望大牛们提出来