EJB3 QL查詢
EJB3的查詢語言是一種和SQL非常類似的中間性和對象化查詢語言。它可以被編譯成不同的底層數(shù)據(jù)庫能接受的SQL，從而屏蔽不同數(shù)據(jù)庫的差異，確保用EJB3 QL查詢語言編寫的代碼可在不同的數(shù)據(jù)庫上運行。比起EJB 2.1的查詢語言，EJB3可以運行期構(gòu)造，支持多態(tài)，遠(yuǎn)遠(yuǎn)比EJB 2.1的查詢更靈活和功能強大。在程序中使用EJB3 QL可以使用大寫(SELECT)或者小寫(select)，但不要大小寫(比如:Select)混合使用。
Query接口
javax.persistence.Query是EJB3查詢操作的接口。進(jìn)行查詢，首先要通過EntityManager 獲得Query對象。
public Query createQuery(String ejbqlString);
下面我們做一個最簡單的查詢，查詢所有的com.redsoft.samples.Order類。
final Query query = entityManager.createQuery( "select o from Order o");
final List result = query.getResultList();
final Iterator iterator = result.iterator();
while( iterator.hasNext() ){
// 處理Order
}
注意"from Order"。"Order"在EJB3查詢中稱為com.redsoft.samples.Order類的abstract schema Type。查詢Entity在EJB3 QL中都是針對Entity的Abstract Schema Type進(jìn)行查詢。 在同一個EntityManagerFactory中，不允許同時有兩個Abstract Schema Type相同的Entity類。比如不允許同時有com.redsoft.samples.Order和com.redsoft.foo.Order?！ ?
Query返回一個List的集合結(jié)果，我們可以用Iterator或者List.get( int )的方法來獲得每個符合條件的Entity。Liberator EJB3 Persistence運行環(huán)境的Query查詢 在構(gòu)造Query的時候的只是把EJB3 QL編譯成相應(yīng)的SQL，但并不執(zhí)行。只有當(dāng)應(yīng)用代碼第一次調(diào)用Iterator.next(),Iterator.hasNext()或者List.get( int )方法的時候,編譯后的SQL才會被真正的執(zhí)行。
在Liberator EJB3 Persistence運行環(huán)境返回的結(jié)果集合中，并不保存所有的結(jié)果，而只是保持一個指向JDBC ResultSet或者緩存ResultSet的一個行(row)指針。只有當(dāng)用戶確實需要獲得Entity實例的時候，才會從ResultSet中獲取數(shù)據(jù)并填充到Entity實例中返回給應(yīng)用。
如果查詢結(jié)果結(jié)合中包含所有符合條件的Entity, Liberator EJB3 Persistence運行環(huán)境默認(rèn)會自動緩存每次查詢的結(jié)果。這樣下次同樣的查詢操作就無需訪問數(shù)據(jù)庫，而直接從緩存中返回結(jié)果集合。但如果在下次查詢操作之前，有針對被緩存的Entity類進(jìn)行update/insert/delete操作，則緩存的結(jié)果集合會自動被清空，這樣下次查詢就會從數(shù)據(jù)庫獲得數(shù)據(jù)， 確保查詢總是獲得正確的結(jié)果，避免緩存臟數(shù)據(jù)。
有時候查詢會返回海量的數(shù)據(jù)。Liberator EJB3運行環(huán)境采用了自適應(yīng)的弱引用POJO管理機制，可以處理海量的數(shù)據(jù)。在我們的測試中和客戶的環(huán)境可以處千萬級別的數(shù)據(jù)量。但在處理大數(shù)據(jù)量的時候，注意關(guān)閉對集合結(jié)果的緩存。
// 假設(shè)返回的結(jié)果數(shù)量巨大
final Query query = entityManager.createQuery( "select o from Order o");
// 關(guān)閉對查詢結(jié)果的緩存
query.setHint( Constants.QUERY_RESULT_CACHE, "false");
final List result = query.getResultList();
final Iterator iterator = result.iterator();
// 這里我們可以處理海量的數(shù)據(jù)
while( iterator.hasNext() ){
// 處理Order
}
簡單查詢
下面是一個簡單查詢的例子，可以看到和SQL的使用方法很類似。
final Query query = entityManager.createQuery( "select o from Order o where o.id = 1");
final Query query = entityManager.createQuery( "select o from Order o where o.id = 1 and o.confirm = 'true' ");
final Query query = entityManager.createQuery( "select o from Order o where o.id = 1 or o.customer = 'foo' ");
// address是Order類上的一個對象變量屬性，Address有一個streetNumber的屬性
final Query query = entityManager.createQuery( "select o from Order o where o.address.streetNumber >= 123" );
注意條件語句中查詢的是Entity的屬性，屬性的名字需要和Entity中的屬性變量名字一致。
使用參數(shù)查詢
參數(shù)查詢也和SQL中的參數(shù)查詢類似。EJB3 QL支持兩種方式的參數(shù)定義方式: 命名參數(shù)和位置參數(shù)。在同一個查詢中只允許使用一種參數(shù)定義方式。
命名參數(shù):
final Query query = entityManager.createQuery( "select o from Order o where o.id = :myId");
// 設(shè)置查詢中的參數(shù)
query.setParameter( "myId", 2 );
// 可以使用多個參數(shù)
final Query query = entityManager.createQuery( "select o from Order o where o.id = :myId and o.customer = :customerName" );
// 設(shè)置查詢中的參數(shù)
query.setParameter( "myId", 2 );
query.setParameter( "customerName", "foo" );
注意不允許在同一個查詢中使用兩個相同名字的命名參數(shù)。
位置參數(shù)：
final Query query = entityManager.createQuery( "select o from Order o where o.id = ?1");
// 設(shè)置查詢中的參數(shù)
query.setParameter( 1, 2 );// 1表示第一個參數(shù)，2是參數(shù)的值
//或者
final Query query = entityManager.createQuery( "select o from Order o where o.id = ?1").setParameter( 1, 2 );
// 可以使用多個參數(shù)
final Query query = entityManager.createQuery( "select o from Order o where o.id = ?1 and o.customer = ?2" );
// 設(shè)置查詢中的參數(shù)
query.setParameter( 1, 2 );
query.setParameter( 2, "foo" );
如果在未來需要在不同的EJB3 運行環(huán)境中運行，請使用位置參數(shù)，保證應(yīng)用是可移植的。
排序(order by)
下面是一個簡單查詢的例子，可以看到和SQL的使用方法很類似。"ASC"和"DESC"分別為升序和降序，如果不顯式注明，EJB3 QL中默認(rèn)為asc升序。
// 不注明的話，默認(rèn)為asc為升序,
final Query query = entityManager.createQuery( "select o from Order o order by o.id");
final Query query = entityManager.createQuery( "select o from Order o order by o.address.streetNumber desc");// desc為降序
final Query query = entityManager.createQuery( "select o from Order o order by o.id, o.address.streetNumber");
查詢部分屬性
在前面的例子中，都是對針對Entity類的查詢，返回的也是被查詢的Entity類的實體。EJB3 QL也允許我們直接查詢返回我們需要的屬性，而不是返回整個Entity。在一些Entity中屬性特別多的情況，這樣的查詢可以提高性能。
// 直接查詢我們感興趣的屬性(列）
final Query query = entityManager.createQuery( "select o.id, o.customerName, o.address.streetNumber from Order o order by o.id");
// 集合中的不再是Order,而是一個Object[]對象數(shù)組
final List result = query.getResultList();
// 第一個行
Object[] row = result.get( 0 );
// 數(shù)組中的第一個值是id
int id = Integer.parseInt( row[0].toString() );
String customerName = row[1].toString();
String streetNumber = Integer.parseInt( row[2].toString() );
查詢中使用構(gòu)造器(Constructor)
EJB3 QL支持將查詢的屬性結(jié)果直接作為一個java class的構(gòu)造器參數(shù)，并產(chǎn)生實體作為結(jié)果返回。
// 我們把需要的三個屬性作為一個class( OrderHolder )的構(gòu)造器參數(shù)，并使用new函數(shù)。
Query query = entityManager.createQuery("select new com.redsoft.ejb3.dummy.OrderHolder ( o.id, o.vender, o.partNumber ) FROM Order AS o");
// 集合中的結(jié)果是OrderHolder
List result = query.getResultList();
該java class不需要是Entity Class。
NEW要求java class使用全名。
聚合查詢(Aggregation)
象大部分的SQL一樣,EJB3 QL也支持查詢中的聚合函數(shù)。目前EJB QL支持的聚合函數(shù)包括：
final Query query = entityManager.createQuery( "select MAX( o.id ) from Order where o.customerName='foo'");
// 如果我們知道結(jié)果是單個，我們可以用getSingleResult()獲得結(jié)果
final Object result = query.getSingleResult();
// 由于Order中id的類型為long,
final Long max = (Long)result;
// 在一些數(shù)據(jù)庫中max函數(shù)返回的結(jié)果的類型不一定于id對應(yīng)的列的類型相符，更安全的方式可以采用string來轉(zhuǎn)型
fina long max = Long.parseLong( result.toString() );
聚合函數(shù)也可以作為被查詢的一個屬性返回。
// 返回所有的訂單的生產(chǎn)廠商和他們的訂單價值總額
final Query query
= entityManager.createQuery( "select o.vender, sum(o.amount) FROM Order o group by o.vender");");
和SQL一樣，如果聚合函數(shù)不是select...from的唯一一個返回列，需要使用"GROUP BY"語句。"GROUP BY"應(yīng)該包含select語句中除了聚合函數(shù)外的所有屬性。
// 返回所有的訂單的生產(chǎn)廠商的的名字，貨物號碼和每種貨物的訂單價值總額
// 注意group by后面必須包含o.vender和o.partNumber
final Query query
= entityManager.createQuery( "select o.vender, o.partNumber, sum(o.amount) FROM Order o group by o.vender，o.partNumber");
如果還需要加上查詢條件，需要使用"HAVING"條件語句而不是"WHERE"語句。
// 返回所有的訂單的生產(chǎn)廠商是"foo"的貨物號碼和每種貨物的訂單價值總額
// 這里"having o.vender = 'foo'為條件
final Query query
= entityManager.createQuery( "select o.vender, o.partNumber, sum(o.amount) FROM Order o
group by o.vender，o.partNumber having o.vender='foo'");
在"HAVING"語句里可以跟"WHERE"語句一樣使用參數(shù)。
// 返回所有的訂單的生產(chǎn)廠商是"foo"的貨物號碼和每種貨物的訂單價值總額
// 這里"having o.vender = 'foo'為條件
final Query query
= entityManager.createQuery( "select o.vender, o.partNumber, sum(o.amount) FROM Order o
group by o.vender，o.partNumber having o.vender=?1");
query.setParameter( 1, "foo" );
final List result = query.getResultList();
關(guān)聯(lián)(join)
在EJB3 QL中，大部分的情況下，使用對象屬性都隱含了關(guān)聯(lián)(join)。例如在以下查詢中：
final Query query = entityManager.createQuery( "select o from Order o
where o.address.streetNumber=2000 order by o.id");
當(dāng)這個句EJB3 QL編譯成以下的SQL時就會自動包含了關(guān)聯(lián),EJB3 QL編譯成SQL時關(guān)聯(lián)默認(rèn)取左關(guān)聯(lián)(left join)。
select o.id, o.vender, o.partNumber, o.amount, addressTable.id, addressTable.streetNumber
from orderTable as o left join addressTable where addressTable.streetNumber = 2000
但在一些情況下，我們?nèi)匀恍枰獙﹃P(guān)聯(lián)做精確的控制。因此EJB3 QL仍然支持和SQL中類似的關(guān)聯(lián)語法：
- left out join/left join
- inner join
- left join/inner join fetch
left join, left out join等義，都是允許符合條件的右邊表達(dá)式中的Entiies為空。
// 返回所有地址為2000的Order紀(jì)錄，不管Order中是否有OrderItem
final Query query = entityManager.createQuery( "select o from Order o
left join o.orderItems where o.address.streetNumber=2000 order by o.id");
由于EJB3 QL默認(rèn)采用left join。這樣的查詢和以下的EJB3 QL其實是等價的。
// 返回所有地址為2000的Order紀(jì)錄，不管Order中是否有OrderItem
final Query query = entityManager.createQuery( "select o from Order o
where o.address.streetNumber=2000 order by o.id");
需要顯式使用left join/left outer join的情況會比較少。
inner join要求右邊的表達(dá)式必須返回Entities。
// 返回所有地址為2000的Order紀(jì)錄，Order中必須有OrderItem
final Query query = entityManager.createQuery( "select o from Order o
inner join o.orderItems where o.address.streetNumber=2000 order by o.id");
left/left out/inner join fetch提供了一種靈活的查詢加載方式來提高查詢的性能。在默認(rèn)的查詢中，Entity中的集合屬性默認(rèn)不會被關(guān)聯(lián)，集合屬性默認(rèn)是緩加載( lazy-load )。
``` // 默認(rèn)EJB3 QL編譯后不關(guān)聯(lián)集合屬性變量(orderItems)對應(yīng)的表
final Query query = entityManager.createQuery( "select o from Order o
inner join o.orderItems where o.address.streetNumber=2000 order by o.id");
final List result = query.getResultList();
// 這時獲得Order實體中orderItems( 集合屬性變量 )為空
final Order order = (Order)result.get( 0 )
// 當(dāng)應(yīng)用需要時，EJB3 Runtime才會執(zhí)行一條SQL語句來加載屬于當(dāng)前Order的OrderItems
Collection orderItems = order.getOrderItems();
這樣的查詢性能上有不足的地方。為了查詢N個Order，我們需要一條SQL語句獲得所有的Order的原始/對象屬性， 但需要另外N條語句獲得每個Order的orderItems集合屬性。為了避免N+1的性能問題，我們可以利用join fetch一次過用一條SQL語句把Order的所有信息查詢出來。
// 返回所有地址為2000的Order紀(jì)錄，Order中必須有OrderItem
final Query query = entityManager.createQuery( "select o from Order o
inner join fetch o.orderItems where o.address.streetNumber=2000 order by o.id");
由于使用了fetch,這個查詢只會產(chǎn)生一條SQL語句，比原來需要N+1條SQL語句在性能上有了極大的提升。
比較Entity
在查詢中使用參數(shù)查詢時，參數(shù)類型除了String, 原始數(shù)據(jù)類型( int, double等)和它們的對象類型( Integer, Double等),也可以是Entity的實例。
final Query query = entityManager.createQuery( "select o from Order o where o.address = ?1 order by o.id");
final Address address = new Address( 2001, "foo street", "foo city", "foo province" );
// 直接把address對象作為參數(shù)。
query.setParameter( 1, address );
批量更新(Batch Update)
EJB3 QL支持批量更新。
Query query = managerNew.createQuery("update Order as o set o.vender=:newvender, o.partNumber='fooPart' where o.vender = 'foo'");
query.setParameter("newvender", "barVender");
// update的記錄數(shù)
int result = query.executeUpdate();
批量刪除(Batch Remove)
EJB3 QL支持批量刪除。
Query query = managerNew.createQuery("DELETE FROM Order");
int result = query.executeUpdate();
Query query = managerNew.createQuery("DELETE FROM Order AS o WHERE o.vender='redsoft'");
int result = query.executeUpdate();
使用操作符NOT
// 查詢所有vender不等于"foo"的Order
Query query = managerNew.createQuery("SELECT FROM Order AS o WHERE not(o.vender='foo')");
List result = query.getResultList();
// 刪除所有vender不等于"foo"的Order
Query query = managerNew.createQuery("DELETE FROM Order AS o WHERE not(o.vender='foo')");
int result = query.executeUpdate();
使用操作符BETWEEN
// 查詢所有價值amount在５和10之間的(包含5,10)的Order
Query query = managerNew.createQuery("select o FROM Order AS o left join o.orderItems ot where o.amount BETWEEN 5 AND 10 order by o.vender desc");
List result = query.getResultList();
使用操作符IN
// 查詢所有vender是"foo1", "foo2"或者"foo3"的Order
Query query = managerNew.createQuery("select o FROM Order AS o left join o.orderItems ot where o.vender in ( 'foo1', 'foo2', 'foo3' ) order by o.vender desc");
List result = query.getResultList();
使用操作符LIKE
// 查詢所有vender以字符串"foo"開頭的Order
Query query = managerNew.createQuery("select o FROM Order as o where o.vender like 'foo%' order by o.vender desc");
List result = query.getResultList();
// 查詢所有vender以字符串"foo"結(jié)尾的Order
Query query = managerNew.createQuery("select o FROM Order as o where o.vender like '%foo' order by o.vender desc");
List result = query.getResultList();
// 可以結(jié)合NOT一起使用，比如查詢所有vender不以以字符串"foo"結(jié)尾的Order
Query query = managerNew.createQuery("select o FROM Order as o where o.vender not like '%foo' order by o.vender desc");
List result = query.getResultList();
// 可以結(jié)合escape使用，比如查詢所有vender以"foo"開始的Order并忽略'3'字符。
// 如果vender是"foo1", "foo2", "foo3"符合這個條件, 另外"3foo1", "f3oo4"也符合條件。
Query query = managerNew.createQuery("select o FROM Order as o where o.vender like '%foo' escape '3' order by o.vender desc");
List result = query.getResultList();
使用操作符IS NULL
// 查詢所有沒有地址的Order
Query query = managerNew.createQuery("select o FROM Order as o where o.address is null");
List result = query.getResultList();
// 查詢所有地址非空的Order
Query query = managerNew.createQuery("select o FROM Order as o where o.address is not null");
List result = query.getResultList();
使用操作符IS EMPTY
IS EMPTY是針對集合屬性(Collection)的操作符?？梢院?strong>NOT一起使用。
// 查詢orderItems集合為空的Order
Query query = managerNew.createQuery("select o FROM Order o where o.orderItems is empty by o.vender desc");
List result = query.getResultList();
// 查詢orderItems集合非空的Order
Query query = managerNew.createQuery("select o FROM Order o where o.orderItems is not empty by o.vender desc");
List result = query.getResultList();
使用操作符EXISTS
[NOT]EXISTS需要和子查詢配合使用。
Query query = manager.createQuery("select o FROM Order o where exists (select o from Order o where o.partNumber=?1) order by o.vender desc");
query.setParameter(1, "partNumber");
Query query = manager.createQuery("select o FROM Order o where o.vender='partNumber' and not exists (select o from Order o where o.partNumber=?1) order by o.vender desc");
query.setParameter(1, "partNumber");
使用操作符ALL/SOME/ANY
Query query = managerNew.createQuery("select emp from EmployeeA emp where emp.salary > all ( select m.salary from Manager m where m.department = emp.department)");
List result = query.getResultList();
Query query = managerNew.createQuery("select emp from EmployeeA emp where emp.salary > any ( select m.salary from Manager m where m.department = emp.department)");
List result = query.getResultList();
Query query = managerNew.createQuery("select emp from EmployeeA emp where emp.salary > some ( select m.salary from Manager m where m.department = emp.department)");
List result = query.getResultList();
字符串函數(shù)
EJB3 QL定義了內(nèi)置函數(shù)方便使用。這些函數(shù)的使用方法和SQL中相應(yīng)的函數(shù)方法類似。EJB3 QL中定義的字符串函數(shù)包括：
- CONCAT 字符串拼接
- SUBSTRING 字符串截取
- TRIM 去掉空格
- LOWER 轉(zhuǎn)換成小寫
- UPPER 裝換成大寫
- LENGTH 字符串長度
- LOCATE 字符串定位
// concat將參數(shù)中的兩個字符串并結(jié)成一個字符串,這里firstName是"foo", lastName是"bar"
Query query = entityManager.createQuery("select concat( o.owner.firstName, o.owner.lastName ) FROM Order AS o left outer join o.orderItems as oi where o.owner.firstName='foo'");
List result = query.getResultList();
assertEquals("foobar", result.get(0).toString());
// firstName是"fooBar",結(jié)果應(yīng)該返回"oo"
Query query = entityManager.createQuery("select o.vender,substring( o.owner.firstName, 1, 3 ), o.owner.info.age FROM Order AS o left outer join o.orderItems as oi where o.owner.firstName='charles'");
List result = query.getResultList();
Object[] row1 = (Object[]) result.get(0);
assertEquals("oo", row1[1].toString());
// 獲得"ar"在firstName中地起始位置
Query query = managerNew.createQuery("SELECT emp.firstName , emp.salary , locate( emp.firstName, 'ar') FROM EmployeeA as emp where emp.firstName='charles1111'");
List result = query.getResultList();
計算函數(shù)
EJB3 QL中定義的計算函數(shù)包括：
- ABS 絕對值
- SQRT 平方根
- MOD 取余數(shù)
- SIZE 取集合的數(shù)量
Query query = entityManager.createQuery("select o.vender, size( o.orderItems ) FROM Order o where o.owner.firstName = 'charles' group by o.vender order by o.vender desc");
List result = query.getResultList();
// 函數(shù)也可以用在條件中
Query query = managerNew.createQuery("select o.vender, sum(o.amount) FROM Order AS o left join o.orderItems ot group by o.vender having size(o.orderItems) = 0 or lower( o.vender ) = 'foo' order by o.vender desc");
List result = query.getResultList();
// 取余數(shù)
Query query = managerNew.createQuery("select mod( o.owner.info.age, 10 ) FROM Order o where exists ( select o from Order o where o.partNumber= :name ) and o.vender='order1' and exists ( select o from Order o where o.amount= :name1 ) order by o.vender desc");
子查詢
子查詢可以用于WHERE和HAVING條件語句中。
Query query = managerNew.createQuery("select emp from EmployeeA as emp where ( select count(m) from Manager as m where m.department = emp.department) > 0 ");
List result = query.getResultList();