<转>hibernate createSQLQuery

原文：http://zhenchengchagangzi.iteye.com/blog/1440990

hibernate 中createQuery与createSQLQuery两者区别是： 

前者用的hql语句进行查询，后者可以用sql语句查询 
前者以hibernate生成的Bean为对象装入list返回 
后者则是以对象数组进行存储 
所以使用createSQLQuery有时候也想以hibernate生成的Bean为对象装入list返回，就不是很方便 
突然发现createSQLQuery有这样一个方法可以直接转换对象 
Query query = session.createSQLQuery(sql).addEntity(XXXXXXX.class); 
XXXXXXX 代表以hibernate生成的Bean的对象，也就是数据表映射出的Bean。 
呵呵以后多注意，还是时不时的要看看hibernate各个对象方法的使用。 

还有另外一个相关的小细节应注意： 
比如有这样一个po 
PO: User.class 
properties: userId,userName 
DDL: create table tuser (userid varchar(10),username varchar(20)); 
当执行： 
    session.createQuery("from User u").list()时生成的SQL： 
　　select userid,username from tuser; 
当执行： 
   
session.createQuery("from User u").iterator()时生成的SQL： 
　　 
select userid from tuser; 
　　 
可以看出list()一次将数据从数据库中读出直接填充到List中 
　　 
iterator()将数据的主键从数据库中读出，当循环这个Iterator时才添加执行： 
　　 
select userid,username from user where userid=?;把数据读出。 
在不同的应用范围使用不同的方法，具体在hibernate应用中应当注意。 


如果使用原生sql语句进行query查询时，hibernate是不会自动把结果包装成实体的。所以要手动调用addEntity(Class class)等一系列方法。 

　　如session.createSQLQuery(sql).addEntity(Class class);注意hibernate3.0.5不支持，单个参数的addEntity方法 

　　另外，hibernate3.2可以对原生sql 查询使用ResultTransformer。这会返回不受Hibernate管理的实体。 

　　session.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS") 

　　.setResultTransformer(Transformers.aliasToBean(Cat DTO.class)) 

　　或setResultTransformer(new AliasToBeanResultTransformer (CatDTO.class)) 

　　上面的查询将会返回CatDTO的列表,它将被实例化并且将NAME和BIRTHDAY的值注射入对应的属性或者字段。 

　　http://bbs.xml.org.cn/blog/more.asp?name=lhwork&id =15351 

　　但必须注意，对每一个列都必须addScalar("列名")    //这个未必的、、 

　　既然如此， 

　　那么setResultTransformer与addEntity的区别是什么呢？一个区别是前者支持查任意的列，后者必须用select * from users的形式或select {a.*},{b.*} from a,b where ....。 


public List getFFAndWitAndNotBpm(FinanceFlow financeFlow){ 

        List lst = null; 
        StringBuffer sql = new StringBuffer(); 
       
    // 16.1.4. 返回多个实体(Returning multiple entities). 
    sql.append("  select {ff.*},{wit.*} from FinanceFlow ff left join WageInfoTable wit  on ff.ffId = wit.ffId  "); 
        System.out.println("-------------------sql="+sql); 
        try{ 

          this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString()).addEntity("ff",FinanceFlow.class).addEntity("wit",WageInfoTable.class).list();
           
        }catch(DataAccessException t){ 
            t.printStackTrace(); 
            throw t; 
        } 
       
        return lst; 
   
    } 

public List getPlanArrlyInfoDistinct<wbr>(int ppfId,String planType) { 
       
        List lst = new ArrayList(); 
        String sql = " SELECT distinct c.ppfId,c.applyType,c.applyField " + 
            " FROM PlanProjectApplyInfo c " + 
            " WHERE c.ppfId = "+ppfId+" AND c.ppaiId NOT IN (SELECT d.ppaiId FROM PlanTimes d " + 
                    " WHERE d.planType = '"+planType+"')"; 
        PlanProjectApplyInfo ppai = null; 
        try{ 
            Query query = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createQuery(sql);
           
            Iterator it = query.iterate(); 
            while(it.hasNext()){ 
                Object[] obj = (Object[])it.next(); 
                ppai = new PlanProjectApplyInfo(); 
                ppai.setPpfId(Integer.parseInt(obj[0].toString())); 
                ppai.setApplyType((String)obj[1]); 
                ppai.setApplyField((String)obj[2]); 
                lst.add(ppai); 
            } 

           
        }catch(DataAccessException dae){ 
            dae.printStackTrace(); 
            throw dae; 
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        } 
        return lst; 
} 

    public List queryMemberInfo(String categoryCode,String userName ,String idCard){ 
       
        StringBuffer sqlHql = new StringBuffer(); 
                sqlHql.append("SELECT distinct {u.*},{ui.*},{ua.*},{ad.*} FROM USER u join USER_INTEGRAL ui on u.USER_ID = ui.F_USER_ID left join USER_AWARD ua on ui.F_USER_ID = ua.F_USER_ID left join ALL_DICTIONARY ad  on  ua.AWARD_ID=ad.CODE and ua.CATEGORY_CODE = ad.CATEGORY_CODE where 1=1 "); 
               
        List memberList = null; 
       
        if(null != categoryCode && !"".equals(categoryCode)){ 
                    //sqlHql.append(" and ad.CATEGORY_CODE ='"+ categoryCode+"' where 1=1 "); 
                } 
                if(null != userName){ 
                   sqlHql.append(" and u.NAME  = '"+userName+"'"); 
                } 
                if(null != idCard){ 
                   sqlHql.append(" and u.ID_CARD  = '"+idCard+"'"); 
                } 
               
                System.out.println("-------------sqlHql =" + sqlHql.toString()); 
                try{ 
                    memberList = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sqlHql.toString()).addEntity("u",User.class).addEntity("ui",UserIntegral.class).addEntity("ua",UserAward.class).addEntity("ad",AllDictionary.class).list();
                    
                      
                }catch(DataAccessException t){ 
                        t.printStackTrace(); 
                        throw t; 
                } 
               
                return memberList; 
               
    } 


public Iterator getFileList(String applyId)throws HibernateException 
        {         
                String sql = "select {a.*},{b.*} from set_appendfile_class a left join app_append_file b on a.code = b.code and b.ori_app_id = '"+applyId+"' and (b.status = '0' or b.status = '2' or b.status = '3') where a.type = 1"; 
                beginTransaction(); 
                Query query = session.createSQLQuery(sql,new String[]{ "a","b"},new Class[]{SetAppendfileClass.class,AppAppendFile.class}); 
                Iterator it = query.list().iterator(); 
                endTransaction(true); 
                return it;              
        } 
        pu 


Query query = session.createSQLQuery("select * from Tree t where pid in (select id from Tree) ").addEntity(Tree.class);  // 返回对象 
List  list = query.list(); 

此时在遍历list时就可以(Tree)list.get[i];将每一行的内容变换为一个对象了。 

工作  验证 实例 

String myQuery = "select i.menuitemid , i.infoitemid   from web_mi_info i where i.menuitemid=2"; 



getSession().createSQLQuery(myQuery) 
                .setResultTransformer(Transformers.aliasToBean(OutWebMiInfoDemo.class)).list() 

createSQLQuery,addScalar列名用大写 

sql 别名 最好写  as 

构造函数 参数的顺序 

两种方式处理 业务逻辑 复杂的 

1 用hql     select new Person(  )      //输出 Person类 

2 用sql     getSession().createSQLQuery(myQuery) 
                .setResultTransformer(Transformers.aliasToBean(Person .class)).list()     //输出 Person类,  类的 构造函数 参数的顺序  ，好像 是 字符串 的排在前面的，    安排hibernate 的规则.... 

     getSession().createSQLQuery(myQuery) 
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list().get(0) 

  //转成map,这个是万能的.. 

String myQuery = "select i.menuitemid, i.infoitemid, (select m.miname from web_midetail as m where m.menuitemid=i.infoitemid) as miname from web_mi_info i"; 

        System.out.println("++++++++++  +++++++++ "  + 
                getSession().createSQLQuery(myQuery) 
                .addScalar("menuitemid", Hibernate.INTEGER).addScalar("infoitemid", Hibernate.INTEGER).addScalar("miname", Hibernate.STRING)   //这行如果有时间就写 
                .setResultTransformer(Transformers.aliasToBean(OutWebMiInfoDemo.class)).list().get(0) 
               
        );