Native SQL查詢 createSQLQuery()

 

/* spring + hibernate 中，this.getHibernateTemplate().find()方法，如果多表沒有配置映射關係（one-mang or many-to-one 等）時，不支援 left join on<br />*<br />* createSQLQuery()中用的是表名而不是對象名,也就是寫純sql。<br />*<br />* 如果使用 第 16 章 Native SQL查詢 的 如語句 sess.createSQLQuery("select * from FinanceFlow ff where 1 = 1 and ff.ffId = 54").list();<br />* 則出現錯誤：R org.hibernate.QueryException: addEntity() or addScalar() must be called on a sql query before executing the query.[ select * from FinanceFlow ff where 1 = 1 and ff.ffId = 54 ]<br />* 則應改為：<br />* 1) sql select {ff.*} from FinanceFlow ff where 1 = 1 and ff.ffId = 54；<br />* 2) this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString()).addEntity("ff",FinanceFlow.class).list();<br />* 這裡的 {ff.*} 中的ff 與表別名相同，也與addEntity("ff",FinanceFlow.class)中ff 相同。但據：16.1.4. 返回多個實體(Returning multiple entities)<br />*<br />* sess.createSQLQuery("SELECT {cat.*}, {mother.*} FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")<br />* .addEntity("cat", Cat.class)<br />* .addEntity("mother", Cat.class)<br />* 好像與表別名無關。但我試了卻出錯，不知為什嗎？<br />* 應用addEntity（"ff",FinanceFlow.class），返回的是 一個List包含的實體FinanceFlow。 且無論select {ff.*} 還是select {ff.ffId} 。<br />*<br />*<br />*<br />* sql.append(" select ff.ffId as {ff.ffId},flowType as {ff.flowType},witid as {wit.witId} from FinanceFlow ff , WageInfoTable wit ");<br />*<br />* 如果使用如{ff.ffId} 單個欄位的形式。則必須列出它的主鍵如這裡的ff.ffId as {ff.ffId} 和 witid as {wit.witId} 。<br />* 在執行如下語句：this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString()).addEntity("ff",FinanceFlow.class).addEntity("wit",WageInfoTable.class).list();<br />* 因為用的是 addEntity() ，所以，返回的還是一個List，每個元素是FinanceFlow實體 和 WageInfoTable實體。<br />*<br />*-----------------------------------------------------------------------------<br />* sql.append(" select ff.ffId as c,wit.witId as d from FinanceFlow ff , WageInfoTable wit ");<br />* this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString()).addScalar("c",Hibernate.INTEGER).addScalar("d",Hibernate.INTEGER).list();<br />* 它們都將返回一個Object數組(Object[])組成的List，數組每個元素都是CATS表的一個欄位值。<br />* 就算query是使用*來查詢的，可能獲得超過列出的這三個欄位，也僅僅會返回這兩個欄位。<br />*<br />*-----------------------------------------------------------------------------<br />*sql文如：<br />* sql.append(" select wit.idCard,<br /> wit.personName,<br /> ff.incomeYear,<br /> ff.incomeMonth,<br /> isnull(wit.allWage,0) as money "+<br /> "from FinanceFlow ff , WageInfoTable wit "+<br />* " where ff.ffId = wit.ffId and not EXISTS ( select 1 from busi_people_money bpm where wit.idCard = bpm.idcard and "+<br />* " ff.incomeYear = bpm.money_Year and ff.incomeMonth = bpm.money_Month )");<br />*<br />*執行之：<br />* this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString()).<br />* addScalar("idCard",Hibernate.STRING).<br />* addScalar("personName",Hibernate.STRING).<br />* addScalar("incomeYear",Hibernate.STRING).<br />* addScalar("incomeMonth",Hibernate.STRING).<br />* addScalar("money",Hibernate.FLOAT).list();<br />*<br />*<br />*****參考《Hibernate參考文檔》。<br />*/</p><p>public List getFFAndWitAndNotBpm(FinanceFlow financeFlow){</p><p> List lst = null;<br /> StringBuffer sql = new StringBuffer();</p><p> // 16.1.4. 返回多個實體(Returning multiple entities).<br /> sql.append(" select {ff.*},{wit.*} from FinanceFlow ff left join WageInfoTable wit on ff.ffId = wit.ffId ");<br /> System.out.println("-------------------sql="+sql);<br /> try{</p><p> this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql.toString()).addEntity("ff",FinanceFlow.class).addEntity("wit",WageInfoTable.class).list();</p><p> }catch(DataAccessException t){<br /> t.printStackTrace();<br /> throw t;<br /> }</p><p> return lst;</p><p> }<br />  

 

 

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

 

---------------------------------------------------------------------------------

用sql 文執行多表查詢。

 

/**<br /> * 根據categoryCode,userName,idCard多表聯集查詢。<br /> *<br /> * @param categoryCode 獎品大類代碼<br /> * @param userName 使用者名稱<br /> * @param idCard 社會安全號碼<br /> * @return<br /> */<br /> public List queryMemberInfo(String categoryCode,String userName ,String idCard){</p><p> StringBuffer sqlHql = new StringBuffer();<br />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 ");</p><p> List memberList = null;</p><p> if(null != categoryCode && !"".equals(categoryCode)){<br /> //sqlHql.append(" and ad.CATEGORY_CODE ='"+ categoryCode+"' where 1=1 ");<br />}<br /> if(null != userName){<br /> sqlHql.append(" and u.NAME = '"+userName+"'");<br /> }<br /> if(null != idCard){<br /> sqlHql.append(" and u.ID_CARD = '"+idCard+"'");<br /> }</p><p> System.out.println("-------------sqlHql =" + sqlHql.toString());<br /> try{<br /> 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(); </p><p> }catch(DataAccessException t){<br /> t.printStackTrace();<br /> throw t;<br /> }</p><p> return memberList;</p><p> }

 

 或者

public Iterator getFileList(String applyId)throws HibernateException<br />{<br />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";<br />beginTransaction();<br />Query query = session.createSQLQuery(sql,new String[]{ "a","b"},new Class[]{SetAppendfileClass.class,AppAppendFile.class});<br />Iterator it = query.list().iterator();<br />endTransaction(true);<br />return it;<br />}<br />pu

 

Hibernate -- createQuery與createSQLQuery兩者區別 

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應用中應當注意。

 

------------------------------------------------------------------------------------------------------

 

 public void updateFrontSerialNumber(String orgnum,long seq,int updateFlag){
  
  final String sql;
  if(updateFlag == 1){
   sql = "update FrontSerialNumber set lseq=lseq+1 where strorgnum='"+orgnum+"'";
  }else if(updateFlag == 2){
   sql = "update FrontSerialNumber set lseq=lseq-999999 where strorgnum='"+orgnum+"'";
  }else{
   sql = "insert into FrontSerialNumber(strorgnum,dttrdate,lseq,iflag) values ('"+orgnum+"',current date,0,0)";
  }
  
  this.getHibernateTemplate().execute(new HibernateCallback(){
   public Object doInHibernate(Session session) throws HibernateException, SQLException {
    Query q = session.createSQLQuery(sql); 
    return q.executeUpdate();
   }   
  });

 }