dao标注:@Repository("sjspDao")
sql查询:
@SuppressWarnings( { "rawtypes", "unchecked" }) public List getCheckDmqkfxList(String sStartTime, String sEndTime, String jgdm, int firstResult, int maxResult) { StringBuffer sql = new StringBuffer(); sql.append("SELECT A.YLJGDM,B.JGMC,A.YWSJ,A.MZRC,A.JZRC,A.RYRC,A.CYRC,A.ZYRS," + "A.SYCWS,A.MJZYLFY,A.ZYYLFY,A.MJZYPFY,A.ZYYPFY,A.MJZYBYLFY,A.ZYYBYLFY,A.MJZYBYPFY,A.ZYYBYPFY,A.ZYSJFSYS,A.SPBZ"); sql.append(" FROM tba A left outer join tbb B on A.YLJGDM=B.JGDM"); if (StringUtils.isNotBlank(sStartTime) && StringUtils.isNotBlank(sEndTime) && jgdm != null && !"ALL".equals(jgdm)) { sql.append(" WHERE YLJGDM='" + jgdm + "'"); sql.append(" and YWSJ BETWEEN '" + sStartTime + "' and '" + sEndTime + "'"); } else { if (StringUtils.isNotBlank(sStartTime) && StringUtils.isNotBlank(sEndTime)) { sql.append(" WHERE YWSJ BETWEEN '" + sStartTime + "' and '" + sEndTime + "'"); } if (jgdm != null && !"ALL".equals(jgdm)) { sql.append(" WHERE YLJGDM='" + jgdm + "'"); } } sql.append(" order by YWSJ desc"); System.out.println("sql.toString()==" + sql.toString()); Query query = getSession().createSQLQuery(sql.toString());
//用于分页的,起始数与最大值 query.setFirstResult(firstResult); query.setMaxResults(maxResult); return query.list(); }
转换指定bo的list:
bo是一个只有setter/getter方法的bean,最好是实现序列化。
public List getUploadList(Date ksrq, Date jzrq, String jgdm, int pagenum, int pagesize) { StringBuffer sql = new StringBuffer( "select B.YLJGDM,B.YWSJ,B.MZRC,B.JZRC,B.RYRC,B.CYRC,B.ZYRS," + "B.SYCWS,B.MJZYLFY,B.ZYYLFY,B.MJZYPFY,B.ZYYPFY,B.MJZYBYLFY,B.ZYYBYLFY," + "B.MJZYBYPFY,B.ZYYBYPFY,B.ZYSJFSYS from tbb B" + " where rowid in (select rid from (select rownum rn, rid" + " from (select rowid rid from tba where 1=1"); if (ksrq != null && jgdm != null && !"ALL".equals(jgdm)) { sql.append(" AND YLJGDM='" + jgdm + "'"); String startTime = sdf.format(ksrq); String endTime = sdf.format(jzrq); sql.append(" AND YWSJ between '" + startTime + "' and '" + endTime + "'"); } else { if (ksrq != null) { String startTime = sdf.format(ksrq); String endTime = sdf.format(jzrq); sql.append(" AND YWSJ between '" + startTime + "' and '" + endTime + "'"); } if (jgdm != null && !"ALL".equals(jgdm)) { sql.append(" AND YLJGDM='" + jgdm + "'"); } } sql.append(") where rownum <= " + pagesize * pagenum + ")"+" where rn > "+pagesize*(pagenum - 1)+")"); sql.append(" order by B.YWSJ desc"); return getSession().createSQLQuery(sql.toString()).setResultTransformer( Transformers.aliasToBean(com.wondersgroup.qyws.sjzk.model.BusinessItem.class)).list(); }
count计数:
public int getCheckDmqkfxListCount(String sStartTime, String sEndTime, String jgdm) { StringBuffer sql = new StringBuffer(); sql.append(" select count(w) from (select 1 as w "); sql.append("FROM tba A left outer join tbb B on A.YLJGDM=B.JGDM"); if (StringUtils.isNotBlank(sStartTime) && StringUtils.isNotBlank(sEndTime) && jgdm != null && !"ALL".equals(jgdm)) { sql.append(" WHERE YLJGDM='" + jgdm + "'"); sql.append(" and YWSJ BETWEEN '" + sStartTime + "' and '" + sEndTime + "'"); } else { if (StringUtils.isNotBlank(sStartTime) && StringUtils.isNotBlank(sEndTime)) { sql.append(" WHERE YWSJ BETWEEN '" + sStartTime + "' and '" + sEndTime + "'"); } if (jgdm != null && !"ALL".equals(jgdm)) { sql.append(" WHERE YLJGDM='" + jgdm + "'"); } } sql.append(") w"); Query query = getSession().createSQLQuery(sql.toString()); return new Integer(query.uniqueResult().toString()); }