hibernate查询

1 hibernate原生sql查询
<hibernate-mapping package="com.byd.erpit.clims.model.base">
 <class name="LabItem" table="C_LABITEM">
  <id name="id" column="ITEM_ID" type="long">
   <generator class="sequence">
    <param name="sequence">C_LABITEM_SN</param>
   </generator>
  </id>
  <!-- 配置与实验室的many-to-one关系 -->
  <many-to-one name="lab" column="LAB_ID" class="Lab" />  
  <!-- 配置与使用设备的one-to-many关系   -->
  <bag name="itemTool"  cascade="all,save-update,delete-orphan" inverse="true" >
   <key column="TEST_ID" />
   <one-to-many class="ItemTool" />  
  </bag>
 </class>
 //根据检测项目名称 实验室名称 模糊查询出 所有的检测项目
 <sql-query name="labItem.findLabItemByLabNameAndItemName">
  <return class="LabItem" />
  <![CDATA[
  select * from C_LABITEM where LAB_ID in
   (select LAB_ID from C_LAB where INF_LAB in(select LAB_ID from C_LAB where LAB_NAME like :labName ) and kind_rmk in('2','4'))
   and CITEM_ID in(select id from C_TESTITEM where ITEM_NAME like :itemName)
   and item_id in(select min(item_id) from c_labitem group by citem_id)
  ]]>
 </sql-query>
 <query name="findUserDataByCreateDate">
  select distinct a
  from LabItem a left join a.lab b
  where a.lab in (:infLabId)
 </query>
</hibernate-mapping>

这里的 在hibernate 映射文件中 配置的<sql-query />就是 hibernate原生sql查询：
返回的是一个 “持久态”的持久化对象

DaoImpl：使用
 /**
  * 根据检测项目名称 实验室名称 模糊查询出 所有的检测项目
  */
 public List<LabItem> findLabItemByLabNameAndItemName(String labName,String itemName){
  Map params = new HashMap();
  params.put("labName","%"+labName.trim()+"%");
  params.put("itemName","%"+itemName.trim()+"%"); 
  List list=super.getNamedQueryResult("labItem.findLabItemByLabNameAndItemName",params);
  return list;
 }
 super.getNamedQueryResult（）方法是daoImpl从DaoSupport工具类里面继承过来的方法。
 描述：
   protected final List getNamedQueryResult(String queryName, Map parameterMap){
  Query query = getNamedQuery(queryName);
  setQueryParameters(query, parameterMap);
  return getQueryResults(query);
   }
   private Query getNamedQuery(String queryName) {
  return super.getSession().getNamedQuery(queryName);
  //这里的super.getSession()方法是从HibernateDaoSupport类中继承过来的
   }

   private List getQueryResults(Query query) {
    return query.list();
   }

◇利用HQL 返回javaBean（持久化对象）
 配置在映射文件中： 
 <query name="findUserDataByCreateDate">
   select distinct a
   from Wprojectm a left join a.wprojteam b
   where a.proj_fact in (:proj_fact)
   and (a.proj_manager.id = :user_id or a.manager_window.id = :user_id or b.manager.id = :user_id)
   and a.create_dt between :start_date and :end_date
   order by a.create_dt
 </query>

 @Override
 public List<Wprojectm> getProjByDate(String[] factArr, Long user_id,
   Timestamp start_date, Timestamp end_date) {
  Map params = new HashMap();
  List list = new ArrayList();
  for(String s:factArr){
   list.add(s);
  }
  params.put("proj_fact", list);
  params.put("user_id", user_id);
  params.put("start_date", start_date);
  params.put("end_date", end_date);
  log.info(params);
  return super.getNamedQueryResult("findUserDataByCreateDate",params);
 }

◇利用SQL 返回特定的类型

<!--  查詢專案所有發起年份 -->
  <sql-query name="pqproj.qpprojm.findAllYear"> 
 <return-scalar type="string" column="createYear"/> 
 select distinct to_char(create_dt,'YYYY') createYear from wpqprojm order by to_char(create_dt,'YYYY') desc
  </sql-query>
    /**
     * 抓取專案所有發起年份     
     */
 public List<String> findAllYear() {   
  return super.getNamedQueryResult("pqproj.qpprojm.findAllYear");
 }

◇利用SQL 返回特定的BEAN类型(DTO)

 <sql-query name="findAllChildByParentId"> 
  <return-scalar type="integer" column="level"/>
  <return alias="f" class="ItSysModule" />
  SELECT f.*,level
  FROM p_itsysmodule f START WITH parent_id = :parent_id and lock_rmk='0'
  CONNECT BY PRIOR mis_id = parent_id
 </sql-query>

 @Override
 public ItSysModuleHierDTO[] findAllChildById(long id) {
  Map params = new HashMap();
  params.put("parent_id", new Long(id));
  List list = super.getNamedQueryResult("findAllChildByParentId",params);
  ItSysModuleHierDTO[] result = new ItSysModuleHierDTO[list.size()];
  for(int i = 0; i < list.size(); i++){
   Object[] obj = (Object[]) list.get(i);
   result[i] = new ItSysModuleHierDTO(obj);

  }
  return result;
 }

2 条件查询——DetachedCriteria：
 /**
  * 根据群组id取得此群组所有的有权限的程序
  * @return:返回此群组有权限的程序集合
  */
 public List<GroupAccess> getGroupAccessesByGroupId(Long groupId){
  DetachedCriteria criteria = DetachedCriteria.forClass(GroupAccess.class);
  criteria.add(Property.forName("id.userGroup.id").eq(groupId));
  return super.findByCriteria(criteria);
 }
 public List<T> findByCriteria(DetachedCriteria criteria){
  return getHibernateTemplate().findByCriteria(criteria);
 }

3 DaoImpl中的hql查询：
 /**
  * 根据 报告id 查询出 此报告的 检测结果
  * @param reportId
  * @return
  */
 public OrdermDTO findOrdermReportTestResult(Long reportId){
  String hql="select a from Report a where a.id=:reportId";
  Map myParam = new HashMap();
  myParam.put("reportId", reportId);
  List list = super.getQueryResult(hql, myParam);
  OrdermDTO dto=new OrdermDTO();
  if(list.size()>0){
   Report report=(Report)list.get(0);
   dto.setTestResult(report.getTestResult()==null?"":report.getTestResult());
  }
  return dto;
 }

   protected final List getQueryResult(String hql, Map parameterMap){
    Query query = setQueryParameters(hql, parameterMap);
       return getQueryResults(query);
   }

4 DaoImpl中的sql查询：
 /**
  * 根据委托单id 查找出委托单 所有样品
  * @param orderId
  * @return
  */
 public List<SampleInfo> findOrderApplySamplesByOrderId(Long orderId){
  List<SampleInfo> samplesList=new ArrayList<SampleInfo>();
  String sql="SELECT ID,SAMPLE_NO,SAMPLE_NAME,CS_NO,to_char(DELIVERY_DATE,'yyyy/MM/dd HH24:mi') AS DELIVERY_DATE ,STATUS_RMK,NOW_STATUS" +
    " FROM C_SAMPLE WHERE ORDER_ID=:orderId";
  Map param=new HashMap();
  param.put("orderId", orderId);
  List resultList=super.getSQLQueryResult(sql,param);
  for(int i=0;i<resultList.size();i++){
   Object[] obj = (Object[]) resultList.get(i);
   SampleInfo sampleInfo=new SampleInfo();
   sampleInfo.setId(Long.parseLong(obj[0].toString()));
   sampleInfo.setSampleNo(obj[1].toString());
   sampleInfo.setSampleName(obj[2].toString());
   sampleInfo.setCsNo(null==obj[3]?"":obj[3].toString());
   sampleInfo.setDeliveryDate(null==obj[4]?null:DateUtil.parseToTimestamp(obj[4].toString(),"yyyy/MM/dd HH:mm"));
   sampleInfo.setStatusRmk(null==obj[5]?"":obj[5].toString());
   sampleInfo.setNowStatus(null==obj[6]?"":obj[6].toString());
   samplesList.add(sampleInfo);
  }
  
  return samplesList;
 }

 描叙：
 1 protected final List getSQLQueryResult(String sql, Map parameterMap) {
  return getSQLQueryResult(sql, parameterMap, null);
  }
 2 protected final List getSQLQueryResult(String sql, Map parameterMap, Map entityMap){
   SQLQuery query = createSQLQuery(sql);
   if(entityMap != null){
     Iterator iter = entityMap.keySet().iterator();
     while (iter.hasNext()) {
      String key = (String) iter.next();
      query.addEntity(key, (Class) entityMap.get(key));
     }
    }
    setQueryParameters(query, parameterMap);
    return getQueryResults(query);
   }

 小结：
Hibernate查询：最后都是通过 org.hibernate.Query 类的list()方法得到查询结果，或者是 使用 spring提供的 HibernateDaoSupport类中的getHibernateTemplate().findByCriteria(criteria);