hibernate 总结

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());
    }