hibernate查询方法总结

 

1.采用 Criteria 类来查询

eg：

  public StoreOrderConsume getByCode(String consumeCode){
      Criteria criteria = this.getSessionFactory().getCurrentSession().
createCriteria(StoreOrderConsume.class);
      criteria.add(Restrictions.eq("consumerCode", consumeCode));
      @SuppressWarnings("unchecked")
List<StoreOrderConsume> storeOrderConsumes = criteria.list();
      if(!CollectionUtils.isEmpty(storeOrderConsumes)){
         return storeOrderConsumes.get(0);
      }
      return null;
  }

   2.采用  Query查询

    2.1 hql 语句查询

 eg：

public List<StoreOrderConsume> getList() {
   String sql = " FROM StoreOrderConsume WHERE num >1";
   Query query = this.getHibernateTemplate().getSessionFactory().getCurrentSession()
.createQuery(sql.toString());
   return ( List<StoreOrderConsume>) query.list();
}

2.2 茶用 sql语句查询

eg:

@Override
public Integer getConsumerCountByOrderPrdId(Integer orderPrdId) {
   String sql = "  SELECT COUNT(id)  FROM store_order_consume WHERE STATUS=0 
AND order_prd_id= "+ orderPrdId;
   Query query = this.getHibernateTemplate().getSessionFactory().getCurrentSession()
.createSQLQuery(sql.toString());
   return Integer.parseInt(query.list().get(0)+"");
}

采用hql语句和sql语句区别在于 createSQLQuery    是否有SQL.SQL.

 

3.hibernate 查询参数返回封装

 3.1采用 Transformers来转换实体

eg：

(List<ConsumeListVo>) query.setResultTransformer(Transformers.aliasToBean(ConsumeListVo.class))
.list());

 

3.2 单个字段转换 采用 addScalar 方法   

@Override
public List<String> getConsumeBuyerMobile() {
   String sql = "SELECT DISTINCT o.buyer_mobile AS buyerMobile FROM store_order o,store_order_prd p " +
         "WHERE o.id=p.order_id AND o.order_type =1 AND  p.order_status = 2 AND " +
         "TIMESTAMPDIFF(MONTH,o.pay_time,NOW()) >1 ";
Query query = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString())
         .addScalar("buyerMobile", StringType.INSTANCE);
List <String> list = query.list();
   return list;
}

 

4.hibernate 的分页查询

query.setFirstResult((orderListMessDo.getPageNum()-1)*orderListMessDo.getPageSize());

query.setMaxResults(orderListMessDo.getPageSize());

5.带入参数 使用 query.setParameter

public int updateDeliverTime(Integer orderId) throws Exception {
    String sql = "UPDATE store_order SET remind_time=NOW() WHERE status=1 AND id=:orderId";
Query query = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());
query.setParameter("orderId",orderId);
    return query.executeUpdate();
}

6.hibernate 使用 in 查询 采用的方法是 query.setParameterList("ids", ids); 

 

public int updateMult(List<Integer> ids) {
   String hql = "update ServiceOrder mso set mso.isDel = 1 where mso.id in(:ids)";
Query query= this.currentSession().createQuery(hql);  
query.setParameterList("ids", ids); 
   return query.executeUpdate();
}