hibernate 三種查詢方式

來源:互聯網
上載者:User

(一)HQL

HQL:Hibernate Qusery Language,如果你已經熟悉它,就會發現它跟SQL非常相像。不過 你不要被表面的假象迷惑,HQL是物件導向的(OO,用生命的眼光看待每一個對象,他們是如此 鮮活)。如果你對JAVA和SQL語句有一定瞭解的話,那麼HQL對你簡直易如反掌,你完全可以利用在公車上的時間掌握它。以下從幾個方面進行慢慢深入:1。大小些敏感大家知道SQL-92 Query是對大小寫不敏感的,但是在HQL(前面提到它是OO的)中對對象類的名稱和屬性確實大小寫敏感的(符合java編程文法)。



HQL 子句本身大小寫無關,但是其中出現的類名和屬性名稱必須注意大小寫區分如:sElect cat.name from Cat as cat和select cat.name from Cat as cat是一樣的但是:sElect cat.name from CAT as cat和select cat.name from Cat as cat確實不一樣的。2。from語句最簡單的:from eg.Cat 它只是簡單的返回所有eg.Cat的執行個體,通常我們此時會為eg.Cat其個別名,因為在query的其餘部分可能會用到(參看上邊關於大小寫敏感時的例子情形),如:from eg.Cat as cat 這裡as可以省略。



上邊只是單表查詢,多表的情況如下寫法:from eg.Cat, eg.Dogfrom eg.Cat as cat, eg.Dog as dog3。join相關(inner) joinleft (outer) joinright (outer) joinfull joinHQL同樣對SQL中的這些特性支援下面插播一個小話題,關於上邊的那些特性,我一直都沒怎麼用,今天既然說到這裡,就想把上邊的幾個特性的用法說一下,也算對自己的一個補充:



假設有兩個表:部門、員工,下面列舉一些資料:員工(Employee):  ID     Name    DepNo  001   Jplateau    01  002    Jony        01  003   Camel      02



部門(Department):  ID   Name  01   研發部  02   營銷部在Hibernate中我們操縱的都是對象,所以我們操縱的是部門類和員工類



 



 



 



 



1).(inner) joinselect employee.ID as id1,employee.Name as name1,



department.ID as id2,department.Name as name2  from Employee as employee



 join  Department as department on employee.DepNo=department.ID (注意到條件陳述式我用on 沒有用where)那麼執行結果是什麼呢?id1 name1 id2 name2++++++++++++++++++++++++++++++++++++++001 Jplateau 01 研發部002 Jony 01 研發部2).left (outer) joinselect employee.ID as id1,employee.Name as name1,department.ID as id2,department.Nameas name2 from Employee as employee left join Department as department on employee.DepNo=department.ID 那麼執行結果又該是什麼呢?id1 name1 id2 name2++++++++++++++++++++++++++++++++++++++001 Jplateau 01 研發部002 Jony 01 研發部 003 Camel null null {就是說此時我要已第一個表的記錄多少為準,第二個表中沒有相應紀錄的時候填充null} 3). right (outer) joinselect employee.ID as id1,employee.Name as name1,department.ID as id2,department.Nameas name2 from Employee as employee right join Department as department on employee.DepNo=department.ID 那麼執行結果又該是什麼呢?id1 name1 id2 name2++++++++++++++++++++++++++++++++++++++001 Jplateau 01 研發部002 Jony 01 研發部 null null 02 營銷部 {就是說此時我要已第二個表的記錄多少為準,第一個表中沒有相應紀錄的時候填充null} 4。select語句就是要確定你要從查詢中返回哪些對象或者哪些對象的屬性。寫幾個例子吧:select employee form Employee as employee select employee form Employee as employee where employee.Name like 'J%'select employee.Name form Employee as employee where employee.Name like 'J%'select employee.ID as id1,employee.Name as name1,department.ID as id2,department.Nameas name2 from Employee as employee right join Department as department on employee.DepNo=department.ID select elements(employee.Name) from Employee as employee (不明白elements到底是做什麼用的?望給於說明) 等等



5。數學函數JDO目前好像還不支援此類特性。avg(...), sum(...), min(...), max(...) count(*) count(...), count(distinct ...), count(all...) 其用法和SQL基本相同select distinct employee.name from Employee as employee select count(distinct employee.name),count(employee) from Employee as employee 6。polymorphism (暫時不知道如何解釋?)from com.test.Animal as animal不光得到所有Animal得執行個體,而且可以得到所有Animal的子類(如果我們定義了一個子類Cat)一個比較極端的例子from java.lang.Object as o可以得到所有持久類的執行個體7。where語句定義查詢語句的條件,舉幾個例子吧:from Employee as employee where employee.Name='Jplateau'from Employee as employee where employee.Name like 'J%'from Employee as employee where employee.Name like '%u'在where語句中“=”不光可以比較對象的屬性,也可以比較對象,如:select animal from com.test.Animal as animal where animal.name=dog8。運算式在SQL語句中大部分的運算式在HQL中都可以使用:mathematical operators +, -, *, / binary comparison operators =, >=, <=, <>, !=, like logical operations and, or, not string concatenation || SQL scalar functions like upper() and lower() Parentheses ( ) indicate grouping in, between, is null JDBC IN parameters ? named parameters :name, :start_date, :x1 (這種應該是另一種"?"的變通解決方案)SQL literals 'foo', 69, '1970-01-01 10:00:01.0' Java public static final constants eg.Color.TABBY 其他不必解釋了,在這裡我只想對查詢中的參數問題說明一下:大家知道在SQL中進行傳遞參數進行查詢的時候,我們通常用PreparedStatement,在語句中寫一大堆的“?”,在hql中也可以用這種方法,如:List mates = sess.find("select employee.name from Employee as employee " +"where employee.Name=? ",name,Hibernate.STRING);(說明:上面利用Session裡的find方法,在hibernate的api Session中重載了很多find方法,它可以滿足你多種形式的查詢)上邊是一個參數的情形,這種情況下緊接著引入參數和定義參數的類型,當為多個參數,調用另一個find方法,它的後兩個參數都是數組的形式。還有另外一種方法來解決上邊的問題,JDO也有這樣的方法,不過和hibernate的表現形式上有差別,但他們兩個骨子裡卻是一樣的,如:Query q = sess.createQuery("select employee.name from Employee as employee where employee.Name=:name");q.setString("name", "Jplateau");//當有多個參數的時候在此逐一定義Iterator employees = q.iterate(); 9。order 語句和sql語句沒什麼差別,如:select employee.name from Employee as employee where employee.Name like 'J%' order by employee.ID desc (或者asc)10。group by 語句同樣和sql語句沒什麼差別,如:select employee.name,employee.DepNo from Employee as employee group by employee.DepNoselect foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) from eg.Foo foo group by foo.id{Note: You may use the elements and indices constructs inside a select clause, even on databases with no subselects.}誰幫我解釋一下上邊兩句,謝過!11。子查詢hibernate同樣支援子查詢,寫幾個例子:from eg.Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from eg.DomesticCat cat )(二)條件查詢Criteria  Query
。數學函數JDO目前好像還不支援此類特性。avg(...), sum(...), min(...), max(...) count(*) count(...), count(distinct ...), count(all...) 其用法和SQL基本相同select distinct employee.name from Employee as employee select count(distinct employee.name),count(employee) from Employee as employee 6。polymorphism (暫時不知道如何解釋?)from com.test.Animal as animal不光得到所有Animal得執行個體,而且可以得到所有Animal的子類(如果我們定義了一個子類Cat)一個比較極端的例子from java.lang.Object as o可以得到所有持久類的執行個體7。where語句定義查詢語句的條件,舉幾個例子吧:from Employee as employee where employee.Name='Jplateau'from Employee as employee where employee.Name like 'J%'from Employee as employee where employee.Name like '%u'在where語句中“=”不光可以比較對象的屬性,也可以比較對象,如:select animal from com.test.Animal as animal where animal.name=dog8。運算式在SQL語句中大部分的運算式在HQL中都可以使用:mathematical operators +, -, *, / binary comparison operators =, >=, <=, <>, !=, like logical operations and, or, not string concatenation || SQL scalar functions like upper() and lower() Parentheses ( ) indicate grouping in, between, is null JDBC IN parameters ? named parameters :name, :start_date, :x1 (這種應該是另一種"?"的變通解決方案)SQL literals 'foo', 69, '1970-01-01 10:00:01.0' Java public static final constants eg.Color.TABBY 其他不必解釋了,在這裡我只想對查詢中的參數問題說明一下:大家知道在SQL中進行傳遞參數進行查詢的時候,我們通常用PreparedStatement,在語句中寫一大堆的“?”,在hql中也可以用這種方法,如:List mates = sess.find("select employee.name from Employee as employee " +"where employee.Name=? ",name,Hibernate.STRING);(說明:上面利用Session裡的find方法,在hibernate的api Session中重載了很多find方法,它可以滿足你多種形式的查詢)上邊是一個參數的情形,這種情況下緊接著引入參數和定義參數的類型,當為多個參數,調用另一個find方法,它的後兩個參數都是數組的形式。還有另外一種方法來解決上邊的問題,JDO也有這樣的方法,不過和hibernate的表現形式上有差別,但他們兩個骨子裡卻是一樣的,如:Query q = sess.createQuery("select employee.name from Employee as employee where employee.Name=:name");q.setString("name", "Jplateau");//當有多個參數的時候在此逐一定義Iterator employees = q.iterate(); 9。order 語句和sql語句沒什麼差別,如:select employee.name from Employee as employee where employee.Name like 'J%' order by employee.ID desc (或者asc)10。group by 語句同樣和sql語句沒什麼差別,如:select employee.name,employee.DepNo from Employee as employee group by employee.DepNoselect foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) from eg.Foo foo group by foo.id{Note: You may use the elements and indices constructs inside a select clause, even on databases with no subselects.}誰幫我解釋一下上邊兩句,謝過!11。子查詢hibernate同樣支援子查詢,寫幾個例子:from eg.Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from eg.DomesticCat cat )(二)條件查詢Criteria  Query
Criteria criteria = session.createCriteria(TUser.class);criteria.add(Expression.eq("name",  "Erica"));criteria.add(Expression.eq("sex",  new Integer(1)));

(三)原生SQL語句查詢




相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。