實際開發存在的問題
上面的操作具體對于實體的映射,可能是存在的實體映射(資料庫總存在的表)。
我現在遇到的情況是根據sql查詢目前一些字段并且分裝到一個非持久化的對象例如
String sql=“select SUM(t.PRODUCT_QUALITY)AS countQuality,COUNT(t.PRODUCT_QUALITY) AS countNumbe from mms_assess_detail t where t.DELIVERY_ID=’”+deliveryId.trim()+"’ and t.product_quality <>0 AND t.product_quality IS NOT NULL";
Query query =this.getSessionFactory().getCurrentSession().createSQLQuery(sql).addScalar( “countQuality” , Hibernate.LONG).addScalar( “countNumbe” , Hibernate.LONG).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
Query query1 =this.getSessionFactory().getCurrentSession().createSQLQuery(sql).addScalar( “countQuality” , Hibernate.LONG).addScalar( “countNumbe” , Hibernate.LONG).setResultTransformer(Transformers.aliasToBean(Test.class));
Query query2 =this.getSessionFactory().getCurrentSession().createSQLQuery(sql).addScalar( “countQuality” , Hibernate.LONG).addScalar( “countNumbe” , Hibernate.LONG).setResultTransformer(Transformers.TO_LIST);
List<Map<String, Object>> list1=query.list();傳回值是一個List的map集合key值就是别名,value就是查詢出來的值
List list2=query1.list(); 傳回的直接就是實體對應的list,注意Test對象是非持久話對象。
list list3= query2 .list();傳回的是純list這個對于查詢一個字段的時候比較有用。
使用SQLQuery
對原生SQL查詢執行的控制是通過SQLQuery接口進行的,通過執行Session.createSQLQuery()擷取這個接口。最簡單的情況下,我們可以采用以下形式:
List cats= sess.createSQLQuery(" select * from cats " ).addEntity(Cat. class).list();
這個查詢指定了:
SQL查詢字元串
查詢傳回的實體
這裡,結果集字段名被假設為與映射檔案中指明的字段名相同。對于連接配接了多個表的查詢,這就可能造成問題,因為可能在多個表中出現同樣名字的字段。下面的方法就可以避免字段名重複的問題:
List cats= sess.createSQLQuery(" select {cat.*} from cats cat " ).addEntity( " cat " , Cat. class).list();
這個查詢指定了:
SQL查詢語句,它帶一個占位符,可以讓Hibernate使用字段的别名.
查詢傳回的實體,和它的SQL表的别名.
addEntity()方法将SQL表的别名和實體類聯系起來,并且确定查詢結果集的形态。
addJoin()方法可以被用于載入其他的實體和集合的關聯.
List cats= sess.createSQLQuery(
" select {cat.}, {kitten.} from cats cat, cats kitten wherekitten.mother = cat.id " )
.addEntity( " cat " , Cat. class )
.addJoin( " kitten " , " cat.kittens " )
.list();
原生的SQL查詢可能傳回一個簡單的标量值或者一個标量和實體的結合體。
Doublemax = (Double) sess.createSQLQuery(" select max(cat.weight) as maxWeight from cats cat ")
.addScalar( " maxWeight " , Hibernate.DOUBLE);
.uniqueResult();
除此之外,你還可以在你的hbm檔案中描述結果集映射資訊,在查詢中使用。
List cats= sess.createSQLQuery(
" select {cat.}, {kitten.} from cats cat, cats kitten wherekitten.mother = cat.id " )
.setResultSetMapping( " catAndKitten " )
.list();
命名SQL查詢
可以在映射文檔中定義查詢的名字,然後就可以象調用一個命名的HQL查詢一樣直接調用命名SQL查詢.在這種情況下,我們不需要調用addEntity()方法.
<sql - query name = " persons ">
< return alias = " person " class = " eg.Person "/>
Select person.NAME AS {person.name},person.AGE AS{person.age},person.SEX AS {person.sex} FROM PERSON person Whereperson.NAME LIKE :namePattern
</ sql - query >List people= sess.getNamedQuery( " persons " ).setString( " namePattern " ,namePattern)
.setMaxResults( 50 )
.list();
原來的查詢語句:
String sql = “select a.* from tb_doc_catalog a where a.cat_codelike '”+catCode+"%’";
Session session = this.getSession();
try {
List catNameList = session.createSQLQuery(sql).list();
return catNameList ;
} finally {
releaseSession(session); //釋放session
}
分析:原來是查詢出來的字段并不能自動轉換為bean對象。
解決思路一(采用hql查詢):
String sql = “select a from DocCatalogInfo a where a.catCode like’”+catCode+"%’";
List catNameList =getHibernateTemplate().find(sql);
return catNameList ;
ok,測試一下發現沒問題,看來還是因為用原生sql查詢的原因,網上搜一下:createsqlQuery傳回對象,看到一篇文章才覺悟到:
解決思路二(采用原生sql查詢):
String sql = “select a.* from tb_doc_catalog a where a.cat_codelike '”+catCode+"%’";
Session session = this.getSession();
try {
List catNameList =session.createSQLQuery(sql).addEntity(DocCatalogInfo.class).list();
return catNameList ;
} finally {
releaseSession(session); //釋放session
}
又ok了。
該篇文章也貼上來:
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),usernamevarchar(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應用中應當注意。
用法一(傳回數值):
ISQLQuery query = session.CreateSQLQuery(“SELECT COUNT(Id) AS CFROM Cake”).AddScalar(“C”, NHibernateUtil.Int32);
int c = Convert.ToInt32(query.UniqueResult());
或int c =query.UniqueResult;//使用此方法發現sql執行了兩次,故不推薦使用。
用法二(傳回對象實體):
ISQLQuery query = session.CreateSQLQuery(“select * from cakec”).AddEntity(“CAKE.DataTransfer.Entities.Cake”);
或ISQLQuery query = session.CreateSQLQuery(“select * from cakec”).AddEntity(“c”,“CAKE.DataTransfer.Entities.Cake”);
或ISQLQuery query = session.CreateSQLQuery(“select * from cakec”).AddEntity(typeof(Cake));
或ISQLQuery query = session.CreateSQLQuery(“select * from cakec”).AddEntity(“c”, typeof(Cake));
或ISQLQuery query = session.CreateSQLQuery(“select * from cakec”).AddEntity(“c”, typeof(Cake), LockMode.Write);
IList c =query.List();
用法三(連表查詢):
ISQLQuery query = session.CreateSQLQuery(“select cs.* from cake cjoin CakeSize cs on cs.CakeId=c.Id”)
.AddEntity(“cs”, typeof(CakeSize));
IList cs =query.List();
以上是一種常見的用法,通過該例子,相信大家心裡也已經有個數了,也應該知道怎麼操作這個sql了:)
但是過程中難免會遇到些問題,比如:
執行個體二(問題說明):
我需要執行例如這條sql語句:
select CreateTime,Address,Password, (case when EmailType = 0 then’Gmail’ when EmailType = 1 then ‘Yahoo’ when EmailType = 2 then’Hotmail’ else ‘Other’ end) as EmailType fromMailInfo
- 如果使用session.CreateQuery方法執行的話,你可能會遇到這樣的錯誤:
錯誤:undefined alias or unknown mapping
ISession session =DAORepository.Instrance.DbSession;
string queryString = “select CreateTime,Address,Password, (casewhen EmailType = 0 then ‘Gmail’ when EmailType = 1 then 'Yahoo’when EmailType = 2 then ‘Hotmail’ else ‘Other’ end) as EmailTypefrom MailInfo where status=0 Order by CreateTimedesc”;
IQuery query = session.CreateQuery(queryString;
IList lst = query.List();
- 使用CreateSQLQuery方法執行:
錯誤:Return types of SQL query were not specified…
ISession session =DAORepository.Instrance.DbSession;
string queryString = “select CreateTime,Address,Password, (casewhen EmailType = 0 then ‘Gmail’ when EmailType = 1 then 'Yahoo’when EmailType = 2 then ‘Hotmail’ else ‘Other’ end) as EmailTypefrom MailInfo where status=0 Order by CreateTimedesc”;
ISQLQuery query =session.CreateSQLQuery(queryString);
IList lst = query.List();
3.使用CreateSQLQuery方法并指定傳回字段值的類型,即使用AddScalar方法指定字段值類型:
錯誤:could not execute query…
ISession session =DAORepository.Instrance.DbSession;
string queryString = “select CreateTime,Address,Password, (casewhen EmailType = 0 then ‘Gmail’ when EmailType = 1 then 'Yahoo’when EmailType = 2 then ‘Hotmail’ else ‘Other’ end) as EmailTypefrom MailInfo where status=0 Order by CreateTimedesc”;
ISQLQuery query =session.CreateSQLQuery(queryString).AddScalar(“CreateTime”,NHibernateUtil.String)
.AddScalar(“Address”,NHibernateUtil.String)
.AddScalar(“Password”,NHibernateUtil.String)
.AddScalar(“EmailType”,NHibernateUtil.Int32);
IList lst = query.List();
ISession session =DAORepository.Instrance.DbSession;
string queryString = “select CreateTime,Address,Password, (casewhen EmailType = 0 then ‘Gmail’ when EmailType = 1 then 'Yahoo’when EmailType = 2 then ‘Hotmail’ else ‘Other’ end) as EmailTypefrom MailInfo where status=0 Order by CreateTimedesc”;
ISQLQuery query =session.CreateSQLQuery(queryString).AddScalar(“CreateTime”,NHibernateUtil.String)
.AddScalar(“Address”,NHibernateUtil.String)
.AddScalar(“Password”,NHibernateUtil.String)
.AddScalar(“EmailType”,NHibernateUtil.Int32);
IList lst = query.List();
注意:EmailType字段對應資料庫的類型是int,這裡我就誤解為也應該映射為NHibernateUtil.Int32;
- 解決問題:
把AddScalar方法的參數裡一個屬性改了一下:
ISession session =DAORepository.Instrance.DbSession;
string queryString = “select CreateTime,Address,Password, (casewhen EmailType = 0 then ‘Gmail’ when EmailType = 1 then 'Yahoo’when EmailType = 2 then ‘Hotmail’ else ‘Other’ end) as EmailTypefrom MailInfo where status=0 Order by CreateTimedesc”;
ISQLQuery query =session.CreateSQLQuery(queryString).AddScalar(“CreateTime”,NHibernateUtil.String)
.AddScalar(“Address”,NHibernateUtil.String)
.AddScalar(“Password”,NHibernateUtil.String)
.AddScalar(“EmailType”,NHibernateUtil.String);
IList lst = query.List();
ISession session =DAORepository.Instrance.DbSession;
string queryString = “select CreateTime,Address,Password, (casewhen EmailType = 0 then ‘Gmail’ when EmailType = 1 then 'Yahoo’when EmailType = 2 then ‘Hotmail’ else ‘Other’ end) as EmailTypefrom MailInfo where status=0 Order by CreateTimedesc”;
ISQLQuery query =session.CreateSQLQuery(queryString).AddScalar(“CreateTime”,NHibernateUtil.String)
.AddScalar(“Address”,NHibernateUtil.String)
.AddScalar(“Password”,NHibernateUtil.String)
.AddScalar(“EmailType”,NHibernateUtil.String);
IList lst = query.List();