Hibernate的几种查询方式

本文总结了项目中用到的Hibernate的几种查询方式

1 使用native sql 方式一 addEntity(Biz.class);

StringBuilder sql = new StringBuilder("select t.* from biztable t where 1=1");
        sql.append(" and t.uplimit <= ?");
        sql.append(" and t.downlimit >= ?");
        sql.append(" ORDER by t.inputdate");
        Query query = getSession().createSQLQuery(sql.toString()).addEntity(Biz.class);
        query.setParameter(0,limit);
        query.setParameter(1,limit);
        List<Biz> = query.list();
2 使用native sql 方式二 addScalar
// return integer
public Integer find(String planyear) {
String sql = "select count(a.b)  as amount from biztable a where a.x= '".concat(x).concat("' and a.c not in ('0')");
SQLQuery query = this.getSession().createSQLQuery(sql).addScalar("amount",Hibernate.INTEGER);
return (Integer) query.uniqueResult();
}
----------------------
//return list
public List<bizclass> find(String planyear) {
String sql = "select count(a.c)  as amount from biztable a where a.c = '".concat(c).concat("' and a.xnot in ('0')");
SQLQuery query = this.getSession().createSQLQuery(sql).addScalar("amount",Hibernate.INTEGER);
return query.list();
}
-------------------
//return bizclass
public bizclass getByPlate(String s1,String s2,String s3) throws Exception{
        String sql = "select t.conditioncheckid,\n" +
                "       t.checktime,\n" +
                "       t.inputer,\n" +
                "       t.inputdate,\n" +
                "  from biztable t,\n" +
                "       vm_s               s,\n" +
                "       vm_t            k,\n" +
                "       vm_d         d,\n" +
                "       VM_T   v\n" +
                " where t.s d = s.s \n" +
                "   and t.t = k.t \n" +
                "   and k.tr  = v.tr \n" +
                "   and k.platenumber="+"\'"+s1+"\'"+"\n" +
                "    and t.checktime = to_date('"+s2+"\','yyyy-mm-dd')\n" +
                "   and t.shiftid="+s3+"\n" +
                "   order by t.inputdate,t.conditioncheckid ";
        SQLQuery q = getSession().createSQLQuery(sql);
        q.addScalar("conditioncheckid", Hibernate.STRING);
        q.addScalar("checktime", Hibernate.STRING);
        q.addScalar("inputer", Hibernate.STRING);
        q.addScalar("inputdate", Hibernate.STRING);

        List list = q.list();
        if(list.size()==0){
            return null;
        }
        SimpleDateFormat sdf =   new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
        Truckruncheck tk= new Biz();
        Object[] p= (Object[]) list.get(0);
        tk.setCond (getStrValue(p[0]));
        tk.setChe (sdf.parse(getStrValue(p[1])));
        tk.setInputer(getStrValue(p[2]));
        tk.setInputdate(sdf.parse(getStrValue(p[3])));
       return tk;
    }


3 hql查询
public List<Human> findByPosition(String positionId, String... fetchProperties) {
StringBuilder hql = new StringBuilder();
hql.append("from Human human");
if (fetchProperties != null)
for (String fetchProperty : fetchProperties) {
hql.append(" left join fetch human.");
hql.append(fetchProperty);
}
hql.append(" where human.valid=true and (human.mainPosition.id = ? or exists(from human.partTimePositions ptp where ptp.id = ?))");
return this.findByHql(hql.toString(), positionId, positionId);
}

public List<T> findByHql(String hql, Object... values) {
        Query query = this.createQuery(hql, values);
        return query.list();
    }
4 createCriteria 查询
public Page<bizclass> find(Page<bizclass> page,Map<String, Object> conditions) {
Criteria criteria = getSession().createCriteria(bizclass.class);

if (conditions.get("userorgid") instanceof List) {
//过滤条件 用户登录单位过滤
@SuppressWarnings("unchecked")
List<String> collierys = (List<String>)conditions.get("userorgid");
if (collierys.size() > 0) {
criteria.add(Restrictions.in("xxxorgid", collierys));
} else {
criteria.add(Restrictions.eq("xxxorgid", "null"));//这里必须要让SQL查不到数据
}
}

criteria.add(Restrictions.eq("Apply.id",conditions.get(" id").toString())); 

criteria.add(Restrictions.ge("usetime", Date.valueOf(String.valueOf(conditions.get("usetimeStart"))))); 
criteria.setFetchMode("vmAttachs", FetchMode.JOIN);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);//去重复

criteria.add(Restrictions.or(Restrictions.eq("inuse","1"),Restrictions.isNull("inuse")));
criteria.add(Restrictions.isNotNull("platenumber"));
criteria.addOrder(Order.asc("platenumber"));
return this.find(page, criteria);
}
---------------------------
public List<Human> findUserByAccountIds(List<String> accountIds) {
        Criteria criteria = userDao.getSession().createCriteria(Human.class);
        criteria.createAlias("unit", "unit");
        // 是否有效用户
        // criteria.add(Restrictions.eq("valid", VALID));

        // 关键字查询
        criteria.createAlias("accounts", "accounts");
        userDao.splitSourceInDc(accountIds, criteria, "in", "accounts.id", 500);
        criteria.addOrder(Order.asc("orderNum"));
        criteria.addOrder(Order.asc("accounts.loginName"));
        return userDao.find(criteria);
    }

5 jdbcTemplate
public List<Human> findSyncUserByOrg(String orgCode) {

        String sql="select h.id as id, employee_code as code,employee_lastname as last_name,employee_firstname as first_name," +
                " (CASE WHEN gender = '男' THEN '1' WHEN gender = '女' then'2'  ELSE '0' END) gender , " +
                " office_telephone as office_phone,mobile_telephone as cell_phone,email as email,to_date(enrolment_date, 'yyyyMMdd') as recruit_date, "+
                " 1 as id_document_type,id_code as id_document_num," +
//                " o.id as unit_id as unit_id,company_code as unit_code,"+
//                " company_name as unit_name,o2.id as dept_id as dept_id,department_code as dept_code,department_name as dept_name,"+
                " 0 as partTime,"+
                " (CASE  WHEN empolyee_group='劳务工' THEN '6' WHEN empolyee_group='认识关系在外单位人员' THEN '4' WHEN empolyee_group='外籍及港澳台人员' THEN '5' WHEN empolyee_group='长期合同工' THEN '1' WHEN empolyee_group='返聘人员' THEN '3' WHEN empolyee_group='短期合同工' THEN '2' WHEN empolyee_group='离退休人员' THEN '9' else '' END) 'group',"+
                " (CASE  WHEN employee_status='离退休移交' THEN '6' WHEN employee_status='死亡' THEN '5' WHEN employee_status='离退休' THEN '3' WHEN employee_status='离职' THEN '4' WHEN employee_status='在岗' THEN '1' WHEN employee_status='不在岗' THEN '2' else '' END) status, "+
                " 0 as immutable,"+
                " 11 as source,"+
                "  1 as valid, "+
                " create_time as create_time "+
                " from (select * from mdm_human h1 " +
                " where exists (select 1 from mdm_human h2 where h2.employee_code = h1.employee_code and h1.create_time < h2.create_time)) h " +
                " join mdm_materialprop p on h.id=p.human_id where p.department_code=? or p.company_code=?";
        List<Human> list=jdbcTemplate.query(sql,new Object[]{orgCode,orgCode},new BeanPropertyRowMapper(Human.class));
        return list;
    }


    @Autowired(required = false)
    private JdbcTemplate jdbcTemplate;


    /**
     * Set the JDBC DataSource to be used by this DAO.
     */
    @Autowired(required = false)
    public void setDataSource(DataSource dataSource) {
        if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
            this.jdbcTemplate =  new JdbcTemplate(dataSource);
        }
    }
public void persist(Human logInfo) {
this.getSession().persist(logInfo);
}
-------------------

public Page<ToFixTruckRecord> findPageByNativeSQL(Page<ToFixTruckRecord> page,String sql, Object... values) {
String pagesql = "select * from ( select row_.*, rownum rownum_ from ( "+sql+
" ) row_) where rownum_<="+(page.getPageSize()*page.getPageNo())+" and rownum_>"+(page.getPageNo()-1) ;
if(page.isAutoCount()) {
long totalCount = this.countSqlResult(sql, values);
page.setTotalCount(totalCount);
}
List<ToFixTruckRecord> list=jdbcTemplate.query(pagesql,values,new BeanPropertyRowMapper(ToFixTruckRecord.class));
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
for(ToFixTruckRecord a : list) {
if (null != a.getBrokedate())
a.setBrokedate(DateTimeTools.stringToSqlDate(a.getBrokedate().toString()));
if(null!=a.getConfirmtime())
a.setConfirmtime(DateTimeTools.stringToSqlDate(a.getConfirmtime().toString()));
if(null!=a.getFixdate())
a.setFixdate(DateTimeTools.stringToSqlDate(a.getFixdate().toString()));
if(null!=a.getInputdate())
a.setInputdate(DateTimeTools.stringToSqlDate(a.getInputdate().toString()));
String truckfixid = a.getTruckfixid();
VmTkFix vmTkFix = (VmTkFix)this.getSession().get(VmTkFix.class, truckfixid);
String truckid = a.getTruckid();
VmTkTruck vmTkTruck =(VmTkTruck)this.getSession().get(VmTkTruck.class, truckid);
a.setVmTkFix(vmTkFix);
a.setVmTkTruck(vmTkTruck);
}
page.setResult(list);
return page;
}
6 sql 删除 更新
public void deletebyId(String  id) {
        Query query = this.getSession().createSQLQuery("delete from biztable where  id = '".concat( id).concat("' "));
        query.executeUpdate();
    }
public void ignoreWarn(String id){
        this.executeSql("update vm_reminder t set t.status = '2' where t.id=?", new Object[]{id});

    }