Creating and running queries

Hibernate中三類查詢：

// HQL, JPQL
session.createQuery("from Category c where c.name like 'Laptop%'");
entityManager.createQuery("select c from Category c where c.name like 'Laptop%'");

// Criteria for query by criteria(QBC) and query by example(QBE)
session.createCriteria(Category.class).add( Restrictions.like("name", "Laptop%") );

// Direct SQL with or without automatic mapping of resultsets to objects
session.createSQLQuery("select {c.*} from CATEGORY {c} where NAME like 'Laptop%'").addEntity("c", Category.class);

Preparing a query

Hibernate中有兩大接口來創(chuàng)建查詢:

org.hibernate.Query

org.hibernate.Criteria

JPA標(biāo)準(zhǔn)中沒有Criteria：

javax.persistence.Query

Creating a query object

Hiberante創(chuàng)建Query及Criteria實例：

// 創(chuàng)建HQL Query
Query hqlQuery = session.createQuery("from User");

// 創(chuàng)建SQL Query
Query sqlQuery = session.createSQLQuery("select {user.*} from USERS {user}").addEntity("user", User.class);

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

JPA利用EntityManager創(chuàng)建javax.persistence.Query實例：

// 創(chuàng)建JPA Query
Query ejbQuery = entityManager.createQuery("select u from User u");

// 創(chuàng)建Native SQL query
Query sqlQuery = entityManager.createNativeQuery("select u.USER_ID, u.FIRSTNAME, u.LASTNAME from USERS u", User.class);

注意Hibernate的SQL query同JPA的不同，除了方法不同，SQL語句也不同。

Paging the result

不同數(shù)據(jù)庫的分頁SQL是不一樣的，Hibernate統(tǒng)一了不同數(shù)據(jù)庫的分頁。

// 查詢前10條數(shù)據(jù)
Query query = session.createQuery("from User u order by u.name asc");
query.setMaxResults(10);

// Criteria，從第40條開始，查詢之后的20條數(shù)據(jù)
Criteria crit = session.createCriteria(User.class);
crit.addOrder(Order.asc("name"));
crit.setFirstResult(40);
crit.setMaxResults(20);

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

// method-chaining coding style
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);

// JPQL分頁
Query query = entityManager.createQuery("select u from User u order by u.name asc").setFirstResult(40).setMaxResults(20);

Considering parameter binding

使用參數(shù)綁定可以避免SQL注入，數(shù)據(jù)庫還可以緩存precompiled prepared statements，提高查詢效率。

// Hibernate參數(shù)綁定
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);

// JPA參數(shù)綁定，利用TemporalType來指定參數(shù)是date, time or timestamp
Query q = entityManager.createQuery(queryString).setParameter("search", searchString).setParameter("minDate", mDate, TemporalType.DATE);

JPA只有setParameter()方法來綁定參數(shù)，Hibernate也有此方法，不過還提供了更加具體的方法來綁定不同類型參數(shù)。當(dāng)參數(shù)是日期類型時，需要javax.persistence.TemporalType來指定參數(shù)是date, time or timestamp。

Using Hibernate parameter binding

Hibernate除了可以綁定普通參數(shù)，還可以綁定實體對象(entity)，Hibernate type或自定義類型，JPA沒有此功能。

// setEntity()綁定實體對象
session.createQuery("from Item item where item.seller = :seller").setEntity("seller", theSeller);

// setParameter()綁定Hibernate type
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);

// setParameter()綁定自定義類型
Query q = session.createQuery("from Bid where amount > :amount");
q.setParameter("amount", givenAmount, Hibernate.custom(MonetaryAmountUserType.class));

// 利用setProperties()，不過其他內(nèi)部仍然是調(diào)用setParameter()
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);

Using positional parametersHibernate starts

按照位置綁定參數(shù)，Hibernate從0開始綁定參數(shù)；JPA從1開始綁定參數(shù)，并且要在問號后加上數(shù)字。

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

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

Setting query hints

Hibernate在執(zhí)行Query之前會默認(rèn)flush persistence context，從而在query執(zhí)行之前，將修改同步到數(shù)據(jù)庫中。
可以為Session/EntityManager或具體的Query設(shè)置FlushMode，從而在執(zhí)行query之前不刷新persistence context。

// Hibernate
Query q = session.createQuery(hql).setFlushMode(FlushMode.COMMIT);
Criteria criteria = session.createCriteria(Item.class).setFlushMode(FlushMode.COMMIT);
// JPA
Query q = entityManager.createQuery(jpaql).setFlushMode(FlushModeType.COMMIT);

為Query設(shè)置CacheMode

// Hibernate
Query q = session.createQuery("from Item").setCacheMode(CacheMode.IGNORE);
Criteria criteria = session.createCriteria(Item.class).setCacheMode(CacheMode.IGNORE);

// JPA
Query q = entityManager.createQuery(queryString).setHint("org.hibernate.cacheMode", org.hibernate.CacheMode.IGNORE);

The CacheMode controls how a particular session interacts with the second-level cache:

所有的Hint可以查看org.hibernate.ejb.QueryHints的javadoc。

Hibernate查詢出實體后，persistence context會維護實體的狀態(tài)，對實體的修改會被同步到數(shù)據(jù)庫(dirty checking)。但如果設(shè)置setReadOnly(true)，Hibernate就不再同步實體的修改到數(shù)據(jù)庫，盡管還是persistent state，除非調(diào)用session.setReadOnly(object, false)再啟用dirty checking。

// Hibernate
Query q = session.createQuery("from Item").setReadOnly(true);
Criteria criteria = session.createCriteria(Item.class).setReadOnly(true);

// JPA
Query q = entityManager.createQuery("select i from Item i").setHint("org.hibernate.readOnly", true);

設(shè)置查詢超時時間：

// Hibernate
Query q = session.createQuery("from Item").setTimeout(60); // 1 minute
Criteria criteria = session.createCriteria(Item.class).setTimeout(60);

// JPA
Query q = entityManager.createQuery("select i from Item i").setHint("org.hibernate.timeout", 60);

設(shè)置fetch size，Hibernate底層依然是JDBC，不同數(shù)據(jù)庫的JDBC driver都有默認(rèn)的fetch size，ORACLE是10，所以如果你要查詢100條數(shù)據(jù)，就要在application和database之間往返十次，這將嚴(yán)重影響性能，所以要設(shè)置合適的fetch size。

http://webmoli.com/2009/02/01/jdbc-performance-tuning-with-optimal-fetch-size/

// Hibernate
Query q = session.createQuery("from Item").setFetchSize(50);
Criteria criteria = session.createCriteria(Item.class).setFetchSize(50);

// JPA
Query q = entityManager.createQuery("select i from Item i").setHint("org.hibernate.fetchSize", 50);

強制使用pessimistic lock，JPA沒有相關(guān)實現(xiàn)。

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

如果你的數(shù)據(jù)庫支持悲觀鎖，生成的SQL就會包含FOR UPDATE。

Executing a query

Query和Criteria創(chuàng)建好后，下面該真正執(zhí)行查詢了。通常是一次將所有結(jié)果查詢出來，稱之為listing；還有其他方式：iterating and scrolling.

Listing all results

// Hibernate，list()
List result = myQuery.list();
List result = myCriteria.list();
// JPA，getResultList()
List result = myJPAQuery.getResultList();

// Hibernate 獲得唯一值，uniqueResult()
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();
// JPA 獲得唯一值，getSingleResult()
Bid maxBid = (Bid) entityManager.createQuery("select b from Bid b order by b.amount desc").setMaxResults(1).getSingleResult();

Iterating through the results

iterate()方法，只查詢出主鍵值，然后迭代時，再根據(jù)主鍵從一級或二級緩存中查找該對象，如果沒有被緩存，就再次查詢數(shù)據(jù)庫。這種優(yōu)化非常有限，搞不好就會導(dǎo)致N+1 SELECT問題，不建議使用。

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

Scrolling with database cursors

ScrollableResults就像游標(biāo)一樣：

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
ScrollableResults itemCursor = session.createQuery("from Item").scroll();
int count = 0;
while (itemCursor.next()) {
    Item item = (Item) itemCursor.get(0);
    modifyItem(item);
    if (++count % 100 == 0) {
        session.flush();
        session.clear();
    }
}
tx.commit();
session.close();

ScrollableResults的常用方法：

itemCursor.first();
itemCursor.last();
itemCursor.get();
itemCursor.next();
itemCursor.scroll(3);
itemCursor.getRowNumber();
itemCursor.setRowNumber(5);
itemCursor.previous();
itemCursor.scroll(-3);
itemCursor.close(); // 關(guān)閉游標(biāo)

You can set the cursor to the first and last Item object in the result, or get the Item the cursor is currently pointing to with get(). You can go to a particular Item by jumping to a position with setRowNumber() or scroll backward and forward with next() and previous(). Another option is scrolling forward and backward by an offset, with scroll().

Criteria也支持scroll:

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

ScrollMode.SCROLL_INSENSITIVE，無法看到其他事務(wù)提交的數(shù)據(jù)。
ScrollMode.SCROLL_SENSITIVE，可以看到其他事務(wù)提交的數(shù)據(jù)。

Using named queries

如果不想將HQL/JPQL和Java代碼混合在一起，可以將其獨立到映射文件中。

Calling a named query

// Hibernate
session.getNamedQuery("findItemsByDescription").setString("desc", description);
// JPA
entityManager.createNamedQuery("findItemsByDescription").setParameter("desc", description);

Defining a named query in XML metadata

在XML中定義HQL, JPQL, SQL。

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

如果將命名Query放在<class>元素下，則query的名稱會加上實體名前綴：

auction.model.Item.findItemsByDescription

確保Query的命名唯一。
<query>元素中還可以定義之前講過的各種Hint。

<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:

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

Defining a named query with annotations

在注解中定義SQL/HQL，利用@NamedQuery, @NamedNativeQuery:

@NamedQueries({
    @NamedQuery(
        name = "findItemsByDescription",
        query = "select i from Item i where i.description like :desc"
    ),
    @NamedQuery(
        name = "findItemsByDescription",
        query = "select i from Item i where i.description like :desc)",
        hints = {
            @QueryHint(name = "org.hibernate.comment", value = "My Comment"),
            @QueryHint(name = "org.hibernate.fetchSize", value = "50"),
            @QueryHint(name = "org.hibernate.flushMode", value = "never"),
            @QueryHint(name = "org.hibernate.readOnly", value = "true"),
            @QueryHint(name = "org.hibernate.timeout", value = "60")
        }
    )
})
/*
@NamedNativeQueries({
    @NamedNativeQuery(
         name = "findItemsByDescriptionWithSQL",
         query = "select i.NAME, i.PRICE ... from ITEM i where i.DESC = :desc",
         resultClass = auction.model.Item.class
    )
})
*/
@Entity
@Table(name = "ITEM")
public class Item { ... }

此文是對《Java Persistence with Hibernate》第14章第一部分的歸納。