JPA的一些笔记（十三）

参考 《Hibernate实战(第二版)》

JPA的查询语言

• JPA的查询语言分为：JPQL查询语言和条件查询API(CriteriaQuery)
• 这里主要示例JPQL查询语言写法

//实体表介绍： 商品Item 商品类别Category 买家用户User 出价单Bid
//Item与Category多对多，Item与User一对多，Item与Bid一对多，User与Bid一对多

//基础语法
//select i.ID, i.NAME, ...  from Item i
from Item
select i from item i
select i from item as 
select i from item i where i.name = 'Foo'

//限制
select b from Bid b where b.amout between 99 and 110
select b from Bid b where b.amout > 100
select u from User u where u.username in ('johndoe','janeroe')
select i from Item i where i.buyNowPrice is null
select i from Item i where i.buyNowPrice is not null
select u from User u where u.username like 'john%'
select u from User u where u.username not like 'john%'
select i from Item i where (i.name like 'Fo%' and i.buyNowPrice is not null) 
                           or i.name = 'Bar'
//函数
select c from Category c where size(c.items)>1
select c from Category c where :item member of c.items
select value(img) from Item i join i.images img where key(img) like '%.jpg'
select i from Item i where lower(i.name) like 'ba%'
select i from Item i where function('DATEDIFF','DAY',i.createOn,i.auctionEnd)>1

//排序
select u from User u Order by u.username desc

//投影 Object[]集合
select u.id, u.username, u.homeAddress from User u 
select distinct i.name from Item i
select concat(concat(i.name,': ', i.auctionEnd)) from Item i
select i.name, coalesce(i.buyNowPrice,0) from Item i  //ifnull
select u.username, case when length(u.homeAddress.zipcode)=5 then 'Germany' when length(u.homeAddress.zipcode)=4 then 'Switzerland' else 'Other' end from User u

//聚合
select count(i) from Item i //sum max min avg 
select i.name, avg(b.amout) from Bid b join b.item i group by i.name
select u.lastname, count(u) from User u group by u.lastname having u.lastname like 'D%'

//联结 隐式、join、theta
select b from Bid b where b.item.name like 'Fo%'  //隐式总是沿着多对一、一对一
select i from Item i join i.bids b where b.amout>100 //隐式加上 i.ID=b.ITEM_ID
select i, b from Item i left join i.bids b on b.amout>100 //返回 List<Object[]>
select u,log from User u, LogRecord log where u.username = log.username  //返回 List<Object[]>
select i ,b from Item i, Bid b where b.item.id =i.id and i.seller = b.bidder

//子查询 JPA支持where子句子查询，不支持from子句子查询，select子查询一般不支持，但可用特殊实现
select u from User u where (select count(i) from Item i where i.seller =u )>1
select b from Bid b where b.amout+1>=(select max(b2.amout) from Bid b2)
select i from Item i where 10>= all (select b.amout from i.bids b)
select i from Item i where 101.00 = any (select b.amout from i.bids b)
select i from Item i where exists (select b from Bid b where b.item=i)

/此外还有多态查询，JPA会自动union all 这里不做讨论