Querying with criteria and example

当查询需要动态生成时，使用Criteria可能是更好的选择。

Basic criteria queries

创建一个简单的Criteria，只需要一个实体Class，这个实体被称为root entity:

session.createCriteria(Item.class);

使用addOrder()方法添加排序功能：

session.createCriteria(User.class).addOrder( Order.asc("lastname") ).addOrder( Order.asc("firstname") );

不通过Session创建DetachedCriteria对象，然后再关联到Session执行：

DetachedCriteria crit = DetachedCriteria.forClass(User.class).addOrder(Order.asc("lastname"))
        .addOrder(Order.asc("firstname"));
List result = crit.getExecutableCriteria(session).list();

Applying restrictions

为Criteria添加约束，需要创建Criterion对象，Restrictions类提供了许多工厂方法来创建Criterion对象。

// 创建约束条件: email属性等于'foo@hibernate.org'
Criterion emailEq = Restrictions.eq("email", "foo@hibernate.org");
Criteria crit = session.createCriteria(User.class);
// 为Criteria添加Criterion约束
crit.add(emailEq);
User user = (User) crit.uniqueResult();

// method chaining
User user = (User) session.createCriteria(User.class)
                        .add(Restrictions.eq("email", "foo@hibernate.org"))
                        .uniqueResult();

component property:

session.createCriteria(User.class).add( Restrictions.eq("homeAddress.street", "Foo"));

Creating comparison exrpessions

Restrictions的各种方法：

// between
Criterion restriction = Restrictions.between("amount", new BigDecimal(100), new BigDecimal(200));
session.createCriteria(Bid.class).add(restriction);

// 大于
session.createCriteria(Bid.class).add(Restrictions.gt("amount", new BigDecimal(100)));

// in
String[] emails = { "foo@hibernate.org", "bar@hibernate.org" };
session.createCriteria(User.class).add(Restrictions.in("email", emails));

// is null
session.createCriteria(User.class).add( Restrictions.isNull("email") );
// is not null
session.createCriteria(User.class).add( Restrictions.isNotNull("email") );

// 集合isEmpty() 或者 isNotEmpty()
session.createCriteria(Item.class).add( Restrictions.isEmpty("bids"));
// 集合大小大于3
session.createCriteria(Item.class).add( Restrictions.sizeGt("bids", 3));

// 比较两个属性相等
session.createCriteria(User.class).add( Restrictions.eqProperty("firstname", "username") );

String matching

模糊查询，可以使用SQL的通配符%, _，也可以通过指定MatchMode来实现：

// 通配符
session.createCriteria(User.class).add( Restrictions.like("username", "G%") );
// 与上一行等价
session.createCriteria(User.class).add( Restrictions.like("username", "G", MatchMode.START) );

MatchMode有START, END, ANYWHERE, EXACT。

字符串大小写，SQL/HQL可以通过LOWER()函数实现，Criteria方式如下：

session.createCriteria(User.class).add( Restrictions.eq("username", "foo").ignoreCase() );

Combining expression with logical operators

逻辑表达：

// firstname like 'G%' AND lastname like 'K%'
session.createCriteria(User.class).add( Restrictions.like("firstname", "G%") ).add( Restrictions.like("lastname", "K%") );

// (firstname like 'G%' AND lastname like 'K%') OR (email in (emails))
session.createCriteria(User.class)
    .add(
        Restrictions.or(
            Restrictions.and(
                Restrictions.like("firstname", "G%"),
                Restrictions.like("lastname", "K%")
            ),
            Restrictions.in("email", emails)
        )
    );

Adding arbitrary SQL expressions

在Criteria中直接使用SQL添加restriction(即WHERE子句中)：

session.createCriteria(User.class)
    .add( Restrictions.sqlRestriction(
            "length({alias}.PASSWORD) < ?",
            5,
            Hibernate.INTEGER
    )
);

{alias}总是指向root entity，本例中即为User。

甚至可以这样：

session.createCriteria(Item.class)
    .add( Restrictions.sqlRestriction(
                "'100' > all" +
                " ( select b.AMOUNT from BID b" +
                " where b.ITEM_ID = {alias}.ITEM_ID )"
            )
    );

Writing subqueries

A subquery in a criteria query is a WHERE clause subselect.

DetachedCriteria subquery = DetachedCriteria.forClass(Item.class, "i");

subquery.add( Restrictions.eqProperty("i.seller.id", "u.id"))
        .add( Restrictions.isNotNull("i.successfulBid") )
        .setProjection( Property.forName("i.id").count() );

// 10 < subquery，最终查询销售超过10个商品的用户
Criteria criteria = session.createCriteria(User.class, "u")
                        .add( Subqueries.lt(10, subquery) );

Joins and dynamic fetching

就像在HQL中使用JOIN以及dynamically fetch，Criteria中也可以。

Joining associations for restriction

Item与item.bids集合INNER JOIN:

Criteria itemCriteria = session.createCriteria(Item.class);
itemCriteria.add(Restrictions.like("description","Foo",MatchMode.ANYWHERE));
// 嵌套创建Criteria对象
Criteria bidCriteria = itemCriteria.createCriteria("bids");
bidCriteria.add( Restrictions.gt( "amount", new BigDecimal(99) ) );
List result = itemCriteria.list();

// method chaining
List result = session.createCriteria(Item.class)
                .add( Restrictions.like("description","Foo",MatchMode.ANYWHERE))
                .createCriteria("bids")
                .add( Restrictions.gt("amount", new BigDecimal(99) ) ).list();

Item与seller association INNER JOIN：

List result = session.createCriteria(Item.class)
                .createCriteria("seller")
                .add( Restrictions.like("email", "%@hibernate.org") ).list();

还可以只创建一个Criteria对象来实现：

session.createCriteria(Item.class)
    .createAlias("bids", "b")    // 创建集合别名
    .add( Restrictions.like("description", "%Foo%") )
    .add( Restrictions.gt("b.amount", new BigDecimal(99) ) );

// 如果属性是root entity的可以不添加别名约束(如上的description)，或者可以使用this关键字
session.createCriteria(Item.class)
    .createAlias("bids", "b")
    .add( Restrictions.like("this.description", "%Foo%") )
    .add( Restrictions.gt("b.amount", new BigDecimal(99) ) );

session.createCriteria(Item.class)
    .createAlias("seller", "s")
    .add( Restrictions.like("s.email", "%hibernate.org" ) );

Dynamic fetching with criteria queries

Criteria实现dynamically fetch：

// 返回的Item对象，bids集合属性被初始化
session.createCriteria(Item.class)
    .setFetchMode("bids", FetchMode.JOIN)
    .add( Restrictions.like("description", "%Foo%") );

FetchMode.JOIN生成OUTER JOIN，如果要生成INNER JOIN，使用FetchMode.INNER_JOIN。

session.createCriteria(Item.class)
    .createAlias("bids", "b", CriteriaSpecification.INNER_JOIN)
    .setFetchMode("b", FetchMode.JOIN)
    .add( Restrictions.like("description", "%Foo%") );

The same caveats as in HQL and JPA QL apply here: Eager fetching more than one collection in parallel (such as bids and images) results in an SQL Cartesian product that is probably slower than two separate queries.

Criteria的dynamic fetch 同HQL的不同：

A Criteria query doesn’t ignore the global fetching strategies as defined in the mapping metadata.For example, if the bids collection is mapped with fetch="join"or FetchType.EAGER, the following query results in an outer join of the ITEM and BID table:

// 使用设置的全局抓取策略
session.createCriteria(Item.class).add( Restrictions.like("description", "%Foo%") );

The returned Item instances have their bids collections initialized and fully loaded. This doesn’t happen with HQL or JPA QL unless you manually query with LEFT JOIN FETCH (or, of course, map the collection as lazy="false", which results in a second SQL query).

Applying a result transformer

当使用JOIN后，查询结果就可能包含很多重复的主表数据，如何去重呢？
ResultTransformer的默认实现是Criteria.ROOT_ENTITY：

List result = session.createCriteria(Item.class)
                .setFetchMode("bids", FetchMode.JOIN)
                .setResultTransformer(Criteria.ROOT_ENTITY)
                .list();
// 利用Set去除重复
Set distinctResult = new LinkedHashSet(result);

使用Criteria.DISTINCT_ROOT_ENTITY去重：

List distinctResult = session.createCriteria(Item.class)
                        .setFetchMode("bids", FetchMode.JOIN)
                        .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                        .list();

如果想同时查出关联实体对象，使用Criteria.ALIAS_TO_ENTITY_MAP：

Criteria crit = session.createCriteria(Item.class)
            .createAlias("bids", "b")
            .createAlias("seller", "s")
            .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List result = crit.list();
// List中是Map
for (Object aResult : result) {
    Map map = (Map) aResult;
    Item item = (Item) map.get(Criteria.ROOT_ALIAS);
    Bid bid = (Bid) map.get("b");
    User seller = (User) map.get("s");
}

HQL和SQL query也支持ResultTransformer，可将查询结果封装为自定义DTO:

Query q = session.createQuery("select i.id as itemId, i.description as desc, i.initialPrice as price from Item i");
q.setResultTransformer( Transformers.aliasToBean(ItemDTO.class) );

Projection and report queries

本小节介绍Criteria如何指定要查询的字段，以及如何使用分组和聚合。

Simple projection lists

如何为Criteria指定一个或多个projection，如何设置字段别名，如何将查询结果直接封装成一个对象？注意Projections.property()，Property.forName()这两种写法。

// setProjection() 指定一个要查询的字段(属性)
session.createCriteria(Item.class)
    .add( Restrictions.gt("endDate", new Date()) )
    .setProjection( Projections.id() );

// 指定多个要查询的字段，Projections.property()
// 此查询返回 a List of Object[]
session.createCriteria(Item.class)
    .setProjection( Projections.projectionList()
        .add( Projections.id() )
        .add( Projections.property("description") )
        .add( Projections.property("initialPrice") )
    );
// 同上行等价的写法，使用Property.forName()
session.createCriteria(Item.class)
    .setProjection( Projections.projectionList()
        .add( Property.forName("id") )
        .add( Property.forName("description") )
        .add( Property.forName("initialPrice") )
    );

// HQL中可以通过SELECT NEW直接查询出自定义对象的List
// Criteria也可以，使用setResultTransformer()，注意projection使用了别名
session.createCriteria(Item.class)
    .setProjection( Projections.projectionList()
        .add( Projections.id().as("itemId") )
        .add( Projections.property("description").as("itemDescription") )
        .add( Projections.property("initialPrice").as("itemInitialPrice") )
    ).setResultTransformer(
        new AliasToBeanResultTransformer(ItemPriceSummary.class)
    );

Aggregation and grouping

使用分组和聚合函数，作者在写此书时(2006)，Criteria不支持HAVING：

// rowCount()，查询出总行数，相当于count(*)
session.createCriteria(Item.class)
    .setProjection( Projections.rowCount() );

// 根据u.id，u.username分组，并统计bid的数量，计算bid.amount的平均值
// 返回 a collection of Object[]，数组中有四个字段：user identifer,username,number of bids, average bid amount
session.createCriteria(Bid.class)
    .createAlias("bidder", "u")
    .setProjection( Projections.projectionList()
        .add( Property.forName("u.id").group() )
        .add( Property.forName("u.username").group() )
        .add( Property.forName("id").count())
        .add( Property.forName("amount").avg() )
    );
// 同上一行等价的写法
session.createCriteria(Bid.class)
    .createAlias("bidder", "u")
    .setProjection( Projections.projectionList()
        .add( Projections.groupProperty("u.id") )
        .add( Projections.groupProperty("u.username") )
        .add( Projections.count("id") )
        .add( Projections.avg("amount") )
    );

// 添加排序
session.createCriteria(Bid.class)
    .createAlias("bidder", "u")
    .setProjection( Projections.projectionList()
        .add( Projections.groupProperty("u.id") )
        .add( Projections.groupProperty("u.username").as("uname") )
        .add( Projections.count("id") )
        .add( Projections.avg("amount") )
    )
    .addOrder( Order.asc("uname") );

Using SQL projections

添加SQL片断到最终生成的SELECT子句中：

String sqlFragment = "(select count(*) from ITEM i where i.ITEM_ID = ITEM_ID) as numOfItems";

session.createCriteria(Bid.class)
    .createAlias("bidder", "u")
    .setProjection( Projections.projectionList()
        .add( Projections.groupProperty("u.id") )
        .add( Projections.groupProperty("u.username") )
        .add( Projections.count("id") )
        .add( Projections.avg("amount) )
        // 添加SQL片断，并指定字段名称及类型
        .add( Projections.sqlProjection(sqlFragment, new String[] { "numOfItems" }, new Type[] { Hibernate.LONG }) )
    );

生成的SQL：

SELECT u.USER_ID,
       u.USERNAME,
       count(BID_ID),
       avg(BID_AMOUNT),
      (SELECT count(*)
       FROM ITEM i
       WHERE i.ITEM_ID = ITEM_ID) AS numOfItems
FROM BID
INNER JOIN USERS u ON BIDDER_ID = u.USER_ID
GROUP BY u.USER_ID, u.USERNAME

Query by example

QBE，当查询依赖用户的输入时，使用QBE更方便，否则需要拼接SQL/HQL。比如:

// 拼接HQL
public List findUsers(String firstname, String lastname) {
    StringBuffer queryString = new StringBuffer();
    boolean conditionFound = false;
    if (firstname != null) {
        queryString.append("lower(u.firstname) like :firstname ");
        conditionFound = true;
    }
    if (lastname != null) {
        if (conditionFound)
            queryString.append("and ");
        queryString.append("lower(u.lastname) like :lastname ");
        conditionFound = true;
    }
    String fromClause = conditionFound ? "from User u where " : "from User u ";
    queryString.insert(0, fromClause).append("order by u.username");
    
    Query query = getSession().createQuery(queryString.toString());
    if (firstname != null)
        query.setString("firstName", '%' + firstname.toLowerCase() + '%');
    if (lastname != null)
        query.setString("lastName", '%' + lastname.toLowerCase() + '%');
    return query.list();
}

// 使用QBC
public List findUsers(String firstname, String lastname) {
    Criteria crit = getSession().createCriteria(User.class);
    if (firstname != null) {
        // ilike() 忽略大小写
        crit.add(Restrictions.ilike("firstname", firstname, MatchMode.ANYWHERE));
    }
    if (lastname != null) {
        crit.add(Restrictions.ilike("lastname", lastname, MatchMode.ANYWHERE));
    }
    crit.addOrder(Order.asc("username"));
    return crit.list();
}

// 使用QBE
public List findUsersByExample(User u) {
    Example exampleUser = Example.create(u)
                            // 字符串类型查询时忽略大小写
                            .ignoreCase()
                            // 字符串类型查询模糊
                            .enableLike(MatchMode.ANYWHERE)
                            // 指定不包括的属性
                            .excludeProperty("password");
    // 默认所有的value-typed属性，不包括主键属性将用于查询匹配
    return getSession().createCriteria(User.class).add(exampleUser).list();
}

使用QBE可以明显减少代码量，Example就是一个Criterion，所以QBE可以混合QBC。

// QBE混合QBC
public List findUsersByExample(User u) {
    Example exampleUser = Example.create(u)
                            .ignoreCase()
                            .enableLike(MatchMode.ANYWHERE);
    
    return getSession().createCriteria(User.class)
                .add(exampleUser)
                .createCriteria("items")
                    // 为items添加约束
                    .add(Restrictions.isNull("successfulBid"))
                .list();
}

两个QBE:

public List findUsersByExample(User u, Item i) {
    Example exampleUser = Example.create(u).ignoreCase().enableLike(MatchMode.ANYWHERE);
    Example exampleItem = Example.create(i).ignoreCase().enableLike(MatchMode.ANYWHERE);
    
    return getSession().createCriteria(User.class)
            .add(exampleUser)
            .createCriteria("items")
                .add(exampleItem)
            .list();
}

QBE看着好像比较好用，不过通常列表页结果都要联结多张表来查询，所以使用SQL可能是更好的选择，下篇关注在Hibernate中使用SQL。

此文是对《Java Persistence with Hibernate》第15章Criteria部分的归纳。

Hiberante查询之Criteria