HQL查询

创建查询对象

hql

Query hqlQuery = session.createQuery("from User");

 原生语法

Query sqlQuery = session.createSQLQuery(
"select {user.*} from USERS {user}").addEntity("user", User.class);

 Criteria

Criteria crit = session.createCriteria(User.class);

 JPA

Query ejbQuery = em.createQuery("select u from User u");

 

Query sqlQuery = session.createNativeQuery(
"select u.USER_ID, u.FIRSTNAME, u.LASTNAME from USERS u",
User.class
);

分页

Query query =
session.createQuery("from User u order by u.name asc");
query.setMaxResults(10);

 

Criteria crit = session.createCriteria(User.class);
crit.addOrder( Order.asc("name") );
crit.setFirstResult(40);
crit.setMaxResults(20);

 

Query sqlQuery =
session.createSQLQuery("select {u.*} from USERS {u}")
.addEntity("u", User.class);
sqlQuery.setFirstResult(40);
sqlQuery.setMaxResults(20);

 方法链

Query query =
session.createQuery("from User u order by u.name asc")
.setMaxResults(10);
Criteria crit =
session.createCriteria(User.class)
.addOrder( Order.asc("name") )
.setFirstResult(40)
.setMaxResults(20);

 

Query query =
em.createQuery("select u from User u order by u.name asc")
.setFirstResult(40)
.setMaxResults(20)

 参数绑定

String queryString = "from Item item where item.description like :search";
Query q = session.createQuery(queryString) .setString("search", searchString);

 

String queryString = "from Item item"
+ " where item.description like :search"
+ " and item.date > :minDate";
Query q = session.createQuery(queryString)
.setString("search", searchString)
.setDate("minDate", mDate);

 

Query q = em.createQuery(queryString)
.setParameter("search", searchString)
.setParameter("minDate", mDate, TemporalType.DATE);

  绑定持久化实体

session.createQuery("from Item item where item.seller = :seller").setEntity("seller", theSeller);

 绑定任何hibernate类型

String queryString = "from Item item"
+ " where item.seller = :seller and"
+ " item.description like :desc";
session.createQuery(queryString)
.setParameter( "seller",theSeller,
Hibernate.entity(User.class) )
.setParameter( "desc", description, Hibernate.STRING );

  定制的用户自定义类型

Query q = session.createQuery("from Bid where amount > :amount");
q.setParameter( "amount", givenAmount,
Hibernate.custom(MonetaryAmountUserType.class) );

 setProperties()绑定使得JavaBean属性的名称与查询字符串中的具体参数一致，内部调用setParameter()来猜测hibernate类型并绑定值

Item item = new Item();
item.setSeller(seller);
item.setDescription(description);
String queryString = "from Item item"
+ " where item.seller = :seller and"
+ " item.description like :desccription";
session.createQuery(queryString).setProperties(item);

  利用定位参数

String queryString = "from Item item"
+ " where item.description like ?"
+ " and item.date > ?";
Query q = session.createQuery(queryString)
.setString(0, searchString)
.setDate(1, minDate);

 

String queryString = "from Item item"
+ " where item.description like ?1"
+ " and item.date > ?2";
Query q = em.createQuery(queryString).setParameter(1, searchString)
.setParameter(2, minDate, TemporalType.DATE);

 设置查询提示

可以在session中通过setFlushMode()来禁止持久化上下文清除

只对特定的查询之前禁用清除

Query q = session.createQuery(queryString).setFlushMode(FlushMode.COMMIT);
Criteria criteria = session.createCriteria(Item.class).setFlushMode(FlushMode.COMMIT);
Query q = em.createQuery(queryString).setFlushMode(FlushModeType.COMMIT);

  高速缓存模式

Query q = session.createQuery("from Item")
.setCacheMode(CacheMode.IGNORE);
Criteria criteria = session.createCriteria(Item.class)
.setCacheMode(CacheMode.IGNORE);
Query q = em.createQuery(queryString)
.setHint("org.hibernate.cacheMode",
org.hibernate.CacheMode.IGNORE);

 CacheMode.IGNORE告诉hibernate不要为这个查询返回的任何实体而与二级缓存交互

禁用脏查询

Query q = session.createQuery("from Item").setReadOnly(true);
Criteria criteria = session.createCriteria(Item.class).setReadOnly(true);
Query q = em.createQuery("select i from Item i")
.setHint("org.hibernate.readOnly", true);

  设置超时，控制一个查询运行多久

Query q = session.createQuery("from Item").setTimeout(60); // 1 minute
Criteria criteria = session.createCriteria(Item.class).setTimeout(60);
Query q = em.createQuery("select i from Item i").setHint("org.hibernate.timeout", 60);

  抓取大小

Query q = session.createQuery("from Item").setFetchSize(50);
Criteria criteria = session.createCriteria(Item.class).setFetchSize(50);
Query q = em.createQuery("select i from Item i")
.setHint("org.hibernate.fetchSize", 50);

 定制注释

Query q = session.createQuery("from Item").setComment("My Comment...");
Criteria criteria = session.createCriteria(Item.class).setComment("My Comment...");
Query q = em.createQuery("select i from Item i")
.setHint("org.hibernate.comment", "My Comment...");

  强制悲观锁

Query q = session.createQuery("from Item item").setLockMode("item", LockMode.UPGRADE);
Criteria criteria = session.createCriteria(Item.class).setLockMode(LockMode.UPGRADE);

 

执行查询

列表

List result = myQuery.list();
List result = myCriteria.list();
List result = myJPAQuery.getResultList();

 获得单个实例

Bid maxBid =
(Bid) session.createQuery("from Bid b order by b.amount desc").setMaxResults(1).uniqueResult();
Bid bid = (Bid) session.createCriteria(Bid.class).add( Restrictions.eq("id", id) ).uniqueResult();

Bid maxBid = (Bid) em.createQuery("select b from Bid b order by b.amount desc").setMaxResults(1)
.getSingleResult();

  循环访问结果

Query categoryByName =
session.createQuery("from Category c where c.name like :name");
categoryByName.setString("name", categoryNamePattern);
List categories = categoryByName.list();

 

Query categoryByName =
session.createQuery("from Category c where c.name like :name");
categoryByName.setString("name", categoryNamePattern);
Iterator categories = categoryByName.iterate();

  游标滚动

ScrollableResults itemCursor =
session.createQuery("from Item").scroll();
itemCursor.first();
itemCursor.last();
itemCursor.get();
itemCursor.next();
itemCursor.scroll(3);
itemCursor.getRowNumber();
itemCursor.setRowNumber(5);
itemCursor.previous();
itemCursor.scroll(-3);
itemCursor.close();

 

ScrollableResults itemCursor =
session.createCriteria(Item.class)
.scroll(ScrollMode.FORWARD_ONLY);
... // Scroll only forward
itemCursor.close()

 使用具名查询

<query name="findItemsByDescription"><![CDATA[
from Item item where item.description like :desc
]]></query>

 

session.getNamedQuery("findItemsByDescription").setString("desc", description);

 

em.createNamedQuery("findItemsByDescription").setParameter("desc", description);

 

<query name="findItemsByDescription"
cache-mode="ignore"
comment="My Comment..."
fetch-size="50"
read-only="true"
timeout="60"><![CDATA[
from Item item where item.description like :desc
]]></query>

 

<sql-query name="findItemsByDescription">
<return alias="item" class="Item"/>
<![CDATA[
select {item.*} from item where description like :desc
]]>
</sql-query>

 

@NamedQueries({
@NamedQuery(
name = "findItemsByDescription",
query = "select i from Item i where i.description like :desc"
),
...
})
@Entity
@Table(name = "ITEM")
public class Item { ... }

  限制

from User u where u.email = '[email protected]'

 

from Item i where i.isActive = true

 

from Bid bid where bid.amount between 1 and 10
from Bid bid where bid.amount > 100
from User u where u.email in ('[email protected]', '[email protected]')

 

from User u where u.email is null
from Item i where i.successfulBid is not null

 

from User u where u.firstname like 'G%'

 

from User u where u.firstname not like '%Foo B%'
from User u where u.firstname not like '\%Foo%' escape='\'

 

from Bid bid where ( bid.amount / 0.71 ) - 100.0 > 0.0
from User user where user.firstname like 'G%' and user.lastname like 'K%'
from User u where ( u.firstname like 'G%' and u.lastname like 'K%' )
or u.email in ('[email protected]', '[email protected]' )

  包含集合的表达式

from Item i where i.bids is not empty
from Item i, Category c where i.id = '123' and i member of c.items

 调用函数

from User u where lower(u.email) = '[email protected]'
from User user where concat(user.firstname, user.lastname) like 'G% K%'
from Item i where size(i.bids) > 3

 排序

from User u order by u.username
from User u order by u.username desc
from User u order by u.lastname asc, u.firstname asc

 投影

Query q = session.createQuery("from Item i, Bid b");
// Query q = em.createQuery("select i, b from Item i, Bid b");
Iterator pairs = q.list().iterator();
// Iterator pairs = q.getResultList().iterator();
while ( pairs.hasNext() ) {
Object[] pair = (Object[]) pairs.next();
Item item = (Item) pair[0];
Bid bid = (Bid) pair[1];
}

 上面例子在标准JPA QL中是无效的

select i.id, i.description, i.initialPrice
from Item i where i.endDate > current_date()

 distinct

select distinct item.description from Item item

 调用特定函数

select item.startDate, current_date() from Item item
select item.startDate, item.endDate, upper(item.name) from Item item

 联接

隐式关联查询

from User u where u.homeAddress.city = 'Bangkok'
select distinct u.homeAddress.city from User u
from Bid bid where bid.item.description like '%Foo%'

 

from Bid bid
where bid.item.category.name like 'Laptop%'
and bid.item.successfulBid.amount > 100

 在from中表达的联接

from Item i
join i.bids b
where i.description like '%Foo%'
and b.amount > 100

 

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

 

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];
}

 

select i
from Item i join i.bids b
where i.description like '%Foo%'
and b.amount > 100

 

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();
}

 联接的动态抓取策略

from Item i
left join fetch i.bids
where i.description like '%Foo%'

 

from Bid bid
left join fetch bid.item
left join fetch bid.bidder
where bid.amount > 100

 报表查询

Long count =
(Long) session.createQuery("select count(i) from Item i")
.uniqueResult();

 

select count(i.successfulBid) from Item i
select sum(i.successfulBid.amount) from Item i

 

select min(bid.amount), max(bid.amount)
from Bid bid where bid.item.id = 1

 

select count(distinct i.description) from Item i

 

select bid.item.id, avg(bid.amount) from Bid bid
group by bid.item.id

 

select bid.item.id, count(bid), avg(bid.amount)
from Bid bid
where bid.item.successfulBid is null
group by bid.item.id

 

select bidItem.id, count(bid), avg(bid.amount)
from Bid bid
join bid.item bidItem
where bidItem.successfulBid is null
group by bidItem.id

 

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

 子查询

from User u where 10 < (
select count(i) from u.items i where i.successfulBid is not null
)

 

from Bid bid where bid.amount + 1 >= (
select max(b.amount) from Bid b
)

 量词

from Item i where 100 > all ( select b.amount from i.bids b )
from Item i where 100 <= any ( select b.amount from i.bids b )
from Item i where 100 = some ( select b.amount from i.bids b )
from Item i where 100 in ( select b.amount from i.bids b )

 

List result =
session.createQuery("from Category c" +
" where :givenItem in elements(c.items)")
.setEntity("givenItem", item)
.list()