Joins, reporting queries, and subselects
在抓取策略這篇文章中有提到dynamic fetching缩焦，用HQL/Criteria來動(dòng)態(tài)抓取力奋，最終是利用JOIN - 連接查詢。
Joining relations and associations
首先要明白inner join和outer join屈留，inner join返回兩張表中都滿足條件的結(jié)果集，left outer join表示主表數(shù)據(jù)全部返回，但是副表只返回滿足條件的數(shù)據(jù)强法，如果主表數(shù)據(jù)在副表中沒有，副表字段用null代替湾笛。
如下兩張表
inner join的結(jié)果饮怯，注意ID為3的item沒有返回：
left join的結(jié)果：
HQL and JPQL join options
HQL/JPQL提供了四種JOIN的表達(dá)方式：
- An implicit association join
- An ordinary join in the FROM clause
- A fetch join in the FROM cluase
- A theta-style join in the WHERE clause
Implicit association joins
隱式連接，使用點(diǎn)操作符來完成嚎研。在HQL/JPQL中點(diǎn)操作符的兩種主要用途：
- Querying components - 查詢組件
- Expressing implicit association joins - 表達(dá)隱式連接
// 查詢組件蓖墅，此時(shí)不會(huì)有表關(guān)聯(lián)
// Address類屬性對應(yīng)的字段都在User表中
from User u where u.homeAddress.city = 'Bangkok'
// 放在SELECT子句中
select distinct u.homeAddress.city from User u
Implicit association join:
// 從BID到ITEM是many-to-one的關(guān)系
from Bid bid where bid.item.description like '%Foo%'
Implicit joins are always directed along many-to-one or one-to-one associations, never through a collection-valued association (you can’t write item.bids.amount).
隱式連接總是從many指向one或從one指向one來引用，永遠(yuǎn)不要從one指向many.
多張表隱式連接：
// 這里假設(shè)從ITEM到CATEGORY是多對一關(guān)系
from Bid bid where bid.item.category.name like 'Laptop%' and bid.item.successfulBid.amount > 100
雖然這樣寫可以临扮，但是要一下子看明白到底關(guān)聯(lián)了幾張表還是很費(fèi)勁论矾，不夠簡單直白。
如果需要多張表關(guān)聯(lián)杆勇，還是不要使用隱式連接贪壳。
select ...
from BID B
inner join ITEM I on B.ITEM_ID = I.ITEM_ID
inner join CATEGORY C on I.CATEGORY_ID = C.CATEGORY_ID
inner join BID SB on I.SUCCESSFUL_BID_ID = SB.BID_ID
where C.NAME like 'Laptop%'
and SB.AMOUNT > 100
Joins expressed in the FROM clause
在HQL/JPQL的FROM子句中使用JOIN，首先來看inner join:
// 在FROM子句中使用JOIN蚜退，為關(guān)聯(lián)集合設(shè)置別名
// join默認(rèn)為inner join
from Item i join i.bids b where i.description like '%Foo%' and b.amount > 100
生成的SQL如下：
select i.DESCRIPTION, i.INITIAL_PRICE, ...
b.BID_ID, b.AMOUNT, b.ITEM_ID, b.CREATED_ON
from ITEM i
inner join BID b on i.ITEM_ID = b.ITEM_ID
where i.DESCRIPTION like '%Foo%' and b.AMOUNT > 100
獲取查詢結(jié)果代碼如下：
Query q = session.createQuery("from Item i join i.bids b");
Iterator pairs = q.list().iterator();
while (pairs.hasNext()) {
Object[] pair = (Object[]) pairs.next();
Item item = (Item) pair[0];
Bid bid = (Bid) pair[1];
}
返回List中的元素是對象數(shù)組闰靴，根據(jù)HQL中對象的順序彪笼，第一個(gè)是Item第二個(gè)是Bid。
這里要注意传黄，從ITEM到BID是一對多的關(guān)系杰扫，所以查詢inner join的查詢結(jié)果中，很可能存在重復(fù)的ITEM行膘掰，但是在Hibernate中只是重復(fù)的對象引用章姓，不是重復(fù)的Item對象。
A particular Item may appear multiple times, once for each associated Bid. These duplicate items are duplicate in-memory references, not duplicate instances!
如果只想獲取ITEM表數(shù)據(jù)：
// 添加SELECT子句來指定要查詢的對象
select i from Item i join i.bids b where i.description like '%Foo%' and b.amount > 100
獲取查詢結(jié)果代碼如下：
Query q = session.createQuery("select i from Item i join i.bids b");
Iterator items = q.list().iterator();
while ( items.hasNext() ) {
Item item = (Item) items.next();
}
以上都是inner join识埋，left join通常用于動(dòng)態(tài)抓取策略(Dynamic fetch strategy)凡伊，動(dòng)態(tài)抓取下節(jié)會(huì)講。
下面看個(gè)使用left join窒舟，但沒有使用動(dòng)態(tài)抓取策略：
// 注意with關(guān)鍵字
from Item i left join i.bids b with b.amount > 100 where i.description like '%Foo%'
首先要知道LEFT JOIN和LEFT OUTER JOIN是一回事系忙，通常不使用RIGHT JOIN。此條HQL仍然返回元素是對象數(shù)組的List惠豺，數(shù)組中第一個(gè)元素是Item银还，第二個(gè)是Bid。
注意with條件洁墙，如果將b.amount > 100放到WHERE子句中蛹疯，則是對LEFT JOIN之后的集合做過濾，所以沒有BID的ITEM就被過濾掉了热监；如果是放在JOIN之后捺弦，則只過濾BID，此時(shí)LEFT JOIN孝扛，沒有BID的ITEM記錄最終仍然會(huì)被查詢出來列吼。
Dynamic fetching strategies with joins
Hibernate默認(rèn)是fetch on demand的抓取策略，所以默認(rèn)會(huì)為關(guān)聯(lián)實(shí)體創(chuàng)建代理苦始，為集合創(chuàng)建collection wrapper寞钥，只有當(dāng)真正需要時(shí)才去初始化；上一節(jié)的所有查詢?nèi)绻褂媚J(rèn)的抓取策略陌选，則關(guān)聯(lián)屬性和collection都不會(huì)真正初始化理郑。
你可以修改全局的抓取策略來改變默認(rèn)行為；還可以保持默認(rèn)行為柠贤，通過Dynamic fetching strategy為某些CASE提供eager fetch香浩。
HQL/JPQL中可以通過FETCH關(guān)鍵字來實(shí)現(xiàn)eager fetch:
from Item i left join fetch i.bids where i.description like '%Foo%'
上面HQL返回元素是Item對象的List，并且其bids集合屬性已經(jīng)被初始化臼勉。
生成的SQL如下：
select i.DESCRIPTION, i.INITIAL_PRICE, ...
b.BID_ID, b.AMOUNT, b.ITEM_ID, b.CREATED_ON
from ITEM i
left outer join BID b on i.ITEM_ID = b.ITEM_ID
where i.DESCRIPTION like '%Foo%'
上面HQL中邻吭，從Item到Bid是one-to-many，對于many-to-one或one-to-one關(guān)聯(lián)關(guān)系宴霸，也可以使用FETCH關(guān)鍵字：
from Bid bid
left join fetch bid.item
left join fetch bid.bidder where bid.amount > 100
生成的SQL如下：
select b.BID_ID, b.AMOUNT, b.ITEM_ID, b.CREATED_ON
i.DESCRIPTION, i.INITIAL_PRICE, ...
u.USERNAME, u.FIRSTNAME, u.LASTNAME, ...
from BID b
left outer join ITEM i on i.ITEM_ID = b.ITEM_ID
left outer join USER u on u.USER_ID = b.BIDDER_ID
where b.AMOUNT > 100
HQL/JPQL注意事項(xiàng)：
- You never assign an alias to any fetch-joined association or collection for further restriction or projection. So
left join fetch i.bids b where b =...is invalid, whereasleft join fetch i.bids b join fetch b.bidderis valid. - You shouldn’t fetch more than one collection in parallel; otherwise you create a Cartesian product. You can fetch as many single-valued associated objects as you like without creating a product.
- HQL and JPA QL ignore any fetching strategy you’ve defined in mapping metadata. For example, mapping the bids collection in XML with fetch="join", has no effect on any HQL or JPA QL statement.
- If you eager-fetch a collection, duplicates may be returned. Look at figure 14.3: This is exactly the SQL operation that is executed for a
select i from Item i join fetch i.bidsHQL or JPA QL query. Each Item is duplicated on the left side of the result table as many times as related Bid data is present. The List returned by the HQL or JPA QL query preserves these duplicates as references. If you prefer to filter out these duplicates you need to either wrap the List in a Set (for example, withSet noDupes = new LinkedHashSet(resultList))or use the DISTINCT keyword:select distinct i from Item i join fetch i.bids—note that in this case the DISTINCT doesn’t operate at the SQL level, but forces Hibernate to filter out duplicates in memory when marshaling the result into objects. Clearly, duplicates can’t be avoided in the SQL result. - Query execution options that are based on the SQL result rows, such as pagination with
setMaxResults()/setFirstResult(), are semantically incorrect if a collection is eagerly fetched. If you have an eager fetched collection in your query, at the time of writing, Hibernate falls back to limiting the result in-memory, instead of using SQL. This may be less efficient, so we don’t recommend the use ofJOIN FETCHwith setMaxResults()/setFirstResult(). Future versions of Hibernate may fall back to a different SQL query strategy (such as two queries and subselect fetching) if setMaxResults()/setFirstResult() is used in combination with a JOIN FETCH.
Theta-style joins
from User user, LogRecord log where user.username = log.username
這就是Theta-style join囱晴，兩張表的關(guān)聯(lián)條件在WHERE子句中指定膏蚓。在HQL/JPQL中，當(dāng)兩個(gè)實(shí)體沒有做外鍵關(guān)聯(lián)時(shí)畸写，可以使用Theta-style join驮瞧，最終是inner join的結(jié)果。
查詢結(jié)果取值：
Iterator i = session.createQuery("from User user, LogRecord log where user.username = log.username").list().iterator();
while (i.hasNext()) {
Object[] pair = (Object[]) i.next();
User user = (User) pair[0];
LogRecord log = (LogRecord) pair[1];
}
Comparing identifiers
HQL/JPQL比較對象引用：
from Item i, User u where i.seller = u and u.username = 'steve'
與此theta-style join等價(jià)的from clause join:
from Item i join i.seller u where u.username = 'steve'
與以上兩條HQL等價(jià)的比較主鍵(標(biāo)識(shí)符)方式：
// 比較主鍵
from Item i, User u where i.seller.id = u.id and u.username = 'steve'
HQL guarantees that id always refers to any arbitrarily named identifier property; JPA QL doesn’t. HQL中id總是指向?qū)嶓w的主鍵屬性枯芬，不管其主鍵屬性名稱是什么论笔。
下面這條theta-style join沒有等價(jià)的from clause join:
// i.seller和b.bidder都外鍵與USER表
from Item i, Bid b where i.seller = b.bidder
但是有等價(jià)的比較主鍵方式：
from Item i, Bid b where i.seller.id = b.bidder.id
主鍵做為查詢參數(shù)：
Long userId = ...
Query q = session.createQuery("from Comment c where c.fromUser.id = :userId");
q.setLong("userId", userId);
List result = q.list();
下面兩條HQL，第一條沒有表連接千所；第二條最終會(huì)生成表連接狂魔。
// no joins at all
from Bid b where b.item.id = 1
// use a implicit table join
from Bid b where b.item.description like '%Foo%'
Reporting queries
利用group by和聚合函數(shù)來查詢數(shù)據(jù)，稱為reporting query.
報(bào)表查詢出的不是實(shí)體對象淫痰，所以Hibernate不用為其查詢結(jié)果維護(hù)狀態(tài)(翻譯的不好最楷，原文如下)。
You don’t need transactional entity instances and can save the overhead of automatic dirty checking and caching in the persistence context.
Projection with aggregation functions
HQL/JPQL識(shí)別的聚合函數(shù)有：count(),min(),max(),sum(),avg()待错。當(dāng)不使用GROUP BY時(shí)籽孙，SELECT子句中使用了聚合函數(shù)，則SELECT子句中只能有聚合函數(shù)火俄，不同有其他字段
Long count = (Long) session.createQuery("select count(i) from Item i").uniqueResult();
// 統(tǒng)計(jì)successfulBid屬性不為null的數(shù)量犯建，為null的被忽略
select count(i.successfulBid) from Item i
// 忽略重復(fù)的
select count(distinct i.description) from Item i
// 因?yàn)閍mount屬性是BigDecimal類型，所以SUM返回的也是BigDecimal類型
// 這里使用了implict join烛占，從Item到Bid
select sum(i.successfulBid.amount) from Item i
// 返回對象數(shù)組Object[]胎挎，包含兩個(gè)BigDecimal對象
select min(bid.amount), max(bid.amount) from Bid bid where bid.item.id = 1
Grouping aggregated results
group by分組：
select u.lastname, count(u) from User u group by u.lastname
生成如下SQL：
// 生成的SQL自動(dòng)count實(shí)體類的主鍵
select u.LAST_NAME, count(u.USER_ID) from USER u group by u.LAST_NAME
// implicit join
select bid.item.id, count(bid), avg(bid.amount) from Bid bid where bid.item.successfulBid is null group by bid.item.id
// 等價(jià)的from clause join
select bidItem.id, count(bid), avg(bid.amount)
from Bid bid join bid.item bidItem
where bidItem.successfulBid is null group by bidItem.id
Restricting groups with having
使用HAVING子句：
select user.lastname, count(user) from User user group by user.lastname having user.lastname like 'A%'
select item.id, count(bid), avg(bid.amount)
from Item item join item.bids bid
where item.successfulBid is null group by item.id having count(bid) > 10
Hibernate將查詢結(jié)果的每一行生成一個(gè)對象數(shù)組Object[]沟启。
Utilizing dynamic instantiation
動(dòng)態(tài)實(shí)例化：
// 將查詢結(jié)果直接封裝成ItemBidSummary對象
select new ItemBidSummary(
bid.item.id, count(bid), avg(bid.amount)
)
from Bid bid where bid.item.successfulBid is null group by bid.item.id
返回的ItemBidSummary對象是transient state忆家；ItemBidSummary類名要含包名。
Improving performance with report queries
Report queries using projection in HQL and JPA QL let you specify which properties you wish to retrieve. For report queries, you aren’t selecting entities in managed state, but only properties or aggregated values.
This query doesn’t return persistent entity instances, so Hibernate doesn’t add any persistent object to the persistence context cache. This means that no object must be watched for dirty state either.
由于report query不是查詢整個(gè)實(shí)體德迹，所以Hibernate不用維護(hù)其查詢結(jié)果的狀態(tài)芽卿，所以更加高效。
Using subselects
HQL/JPQL不支持SELECT子句的子查詢胳搞，支持WHERE子句的子查詢卸例。
Correlated and uncorrelated nesting
相關(guān)子查詢 - correlated subquery：
from User u where 10 < (
select count(i) from u.items i where i.successfulBid is not null
)
uncorrelated subquery:
// 查詢出所有和最高amount相差在1塊之內(nèi)的bid
from Bid bid where bid.amount + 1 >= (
select max(b.amount) from Bid b
)
Quantification
量詞關(guān)鍵字：ALL, ANY/SOME, IN:
// 返回這些Item：Item對應(yīng)的所有Bid的amount都小于100
from Item i where 100 > all ( select b.amount from i.bids b )
// 返回這些Item: Item對應(yīng)的所有Bid的amount只要有一個(gè)大于等于100
from Item i where 100 <= any ( select b.amount from i.bids b )
// 返回這些Item: Item對應(yīng)的所有Bid的amount只要有一個(gè)等于100
from Item i where 100 = some ( select b.amount from i.bids b )
// 返回這些Item: Item對應(yīng)的所有Bid的amount只有有一個(gè)等于100(同上)
from Item i where 100 in ( select b.amount from i.bids b )
此文是對《Java Persistence with Hibernate》第14章第三部分的歸納。
