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

}