一个关于Hibernate的优化实例：从HQL到QBC，从QBC到QBE，再到“增强的”QBE

先解释一下标题的含义：为了实现一个组合条件查询，先是使用HQL书写，然后改用Query by Criteria方式，再尝试Query by Example，最后自己实现了一个增强的Example类来解决问题。

关于此问题的起源请阅读我以前的一个帖子：http://www.iteye.com/post/523791。在该帖子中已经实现了从HQL到QBC的转变，在这里就不再重复了。

在上一个帖子中没有模型类Product及Category的代码，为了方便讨论补充如下：

Java代码
public class Category {
private Long id;
private String name; //类别名称

//Other code omitted
}

public class Product {
private Long id;
private String name; //商品名称
private Category category; //商品类别
private Date expDate; //有效期
private Float price; //单价

//Other code omitted
}

public class Category {
private Long id;
private String name; //类别名称

//Other code omitted
}

public class Product {
private Long id;
private String name; //商品名称
private Category category; //商品类别
private Date expDate; //有效期
private Float price; //单价

//Other code omitted
}


从前一个帖子中可以看到，使用QBC后代码有所减少，但还是得把构造查询条件的代码写死，这非常不爽。重读了《Java Persistence with Hibernate》一书，发觉QBE是个好东东，于是尝试用改造代码如下：

Java代码
public List<Product> getProducts(Product product) {
final Example exampleProduct =
Example.create(product).
enableLike(MatchMode.ANYWHERE).
excludeZeroes();

return (List<Product>) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Criteria crit =
session.createCriteria(Product.class).
add(exampleProduct);
return crit.list();
}
}
);
}

public List<Product> getProducts(Product product) {
final Example exampleProduct =
Example.create(product).
enableLike(MatchMode.ANYWHERE).
excludeZeroes();

return (List<Product>) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Criteria crit =
session.createCriteria(Product.class).
add(exampleProduct);
return crit.list();
}
}
);
}


代码非常简洁啊！我只要new一个Product实例，然后把要查询的条件值赋值到相应到属性上，如果某项条件未指定则相应的属性保留为默认的空值，将该实例传递给上面的getProducts方法，就能得到需要的结果了。超爽！

但是我却没办法把这段代码用在产品中，这是因为QBE有着严重的局限性：
1.不能查询指定在关联对象的属性上的条件。比如我想仅列出商品类别名称包括xyz的商品，代码如下：

Java代码
Category category = new Category();
category.setName("xyz");
Product product = new Product();
product.setCategory(category);

List<Product> products = getProducts(product);

Category category = new Category();
category.setName("xyz");
Product product = new Product();
product.setCategory(category);

List<Product> products = getProducts(product);


运行这段代码会列出所有的商品。

2.除了字符串条件可以调用enableLike()方法改用模糊查询外，其它数据类型的条件都只能等值比较。比如我无法查询所有有效的商品（有效期≥当前日期）。

难道就没有办法了吗？经过一番搜索，终于在Hibernate的官网论坛上找到一篇文章：http://forum.hibernate.org/viewtopic.php?t=942872。在该文章中，Dencel写了一个AssociationExample，经过大家的完善，终于解决了查询指定在关联对象的属性上的条件的问题。其主要的奥妙在于：

Java代码
//Hibernate的原版Example
//如果属性类型是关联的实体，则忽略
private boolean isPropertyIncluded(Object value, String name, Type type) {
return !excludedProperties.contains(name) &&
!type.isAssociationType() &&
selector.include(value, name, type);
}

//改版的AssociationExample
private boolean includeAssociations = true;

public boolean isIncludeAssociations()
{
return includeAssociations;
}

public void setIncludeAssociations(boolean includeAssociations)
{
this.includeAssociations = includeAssociations;
}

//如果属性类型是关联的实体，且该关联是一对一或多对一，且includeAssociations为true，则包括该属性
private boolean isPropertyIncluded(Object value, String name, Type type) {
return
!excludedProperties.contains(name) &&
selector.include(value, name, type) &&
(!type.isAssociationType() ||
(type.isAssociationType() &&
includeAssociations &&
!type.isCollectionType()));
}

//Hibernate的原版Example
//如果属性类型是关联的实体，则忽略
private boolean isPropertyIncluded(Object value, String name, Type type) {
return !excludedProperties.contains(name) &&
!type.isAssociationType() &&
selector.include(value, name, type);
}

//改版的AssociationExample
private boolean includeAssociations = true;

public boolean isIncludeAssociations()
{
return includeAssociations;
}

public void setIncludeAssociations(boolean includeAssociations)
{
this.includeAssociations = includeAssociations;
}

//如果属性类型是关联的实体，且该关联是一对一或多对一，且includeAssociations为true，则包括该属性
private boolean isPropertyIncluded(Object value, String name, Type type) {
return
!excludedProperties.contains(name) &&
selector.include(value, name, type) &&
(!type.isAssociationType() ||
(type.isAssociationType() &&
includeAssociations &&
!type.isCollectionType()));
}


解决了前面提到的第一个问题，第二个问题又怎么办呢？我想到一个办法：如果某个条件要使用其它的比较方式（比如大于等于），提供一个方法让用户为该属性指定比较方法，对于其它属性仍采用缺省的查询/比较方法：

Java代码
//Hibernate原版的Example
protected void appendPropertyCondition(
String propertyName,
Object propertyValue,
Criteria criteria,
CriteriaQuery cq,
StringBuffer buf)
throws HibernateException {
Criterion crit;
if ( propertyValue!=null ) {
//当属性值不为空时，如果是字符串且指定为模糊查询，则使用模糊查询，否则使用等值比较
boolean isString = propertyValue instanceof String;
SimpleExpression se = ( isLikeEnabled && isString ) ?
Restrictions.like(propertyName, propertyValue) :
Restrictions.eq(propertyName, propertyValue);
crit = ( isIgnoreCaseEnabled && isString ) ?
se.ignoreCase() : se;
}
else {
crit = Restrictions.isNull(propertyName);
}
String critCondition = crit.toSqlString(criteria, cq);
if ( buf.length()>1 && critCondition.trim().length()>0 ) buf.append(" and ");
buf.append(critCondition);
}


//增强后的EnhancedExample
private static final RestrictionHolder holder = new DefaultRestrictionHolder();

/**
* Restriction strategy definitions
*/
public static enum RestrictionStrategy {eq, ne, gt, lt, ge, le}

/**
* Restriction strategy holder for the query criteria
*/
public static interface RestrictionHolder {
/**
* Set a restriction strategy for a POJO's property
*/
public RestrictionHolder set(String propertyName, RestrictionStrategy strategy);

/**
* Get the restriction strategy of the property
*/
public RestrictionStrategy get(String propertyName);
}

static final class DefaultRestrictionHolder implements RestrictionHolder {
private Map<String, RestrictionStrategy> strategies = new HashMap<String, RestrictionStrategy>();

public RestrictionHolder set(String propertyName, RestrictionStrategy strategy) {
strategies.put(propertyName, strategy);
return this;
}

public RestrictionStrategy get(String propertyName) {
return strategies.get(propertyName);
}
}

/**
* Get the restriction strategy holder
*/
public RestrictionHolder getRestrictionHolder() {
return holder;
}

protected void appendPropertyCondition(
String propertyName,
Object propertyValue,
Criteria criteria,
CriteriaQuery cq,
StringBuffer buf)
throws HibernateException {
Criterion crit;
if ( propertyValue!=null ) {
//当属性值不为空时，如果为该属性指定了比较条件，则使用指定的比较条件
RestrictionStrategy strategy = holder.get(propertyName);
if ( strategy != null ) {
switch(strategy) {
//case eq: crit = Restrictions.eq(propertyName, propertyValue);
case ne: crit = Restrictions.ne(propertyName, propertyValue); break;
case gt: crit = Restrictions.gt(propertyName, propertyValue); break;
case lt: crit = Restrictions.lt(propertyName, propertyValue); break;
case ge: crit = Restrictions.ge(propertyName, propertyValue); break;
case le: crit = Restrictions.le(propertyName, propertyValue); break;
default: crit = Restrictions.eq(propertyName, propertyValue);
};
}
else {
//否则使用默认的比较条件：如果是字符串且指定为模糊查询，则使用模糊查询，否则使用等值比较
boolean isString = propertyValue instanceof String;
SimpleExpression se = ( isLikeEnabled && isString ) ?
Restrictions.like(propertyName, propertyValue) :
Restrictions.eq(propertyName, propertyValue);
crit = ( isIgnoreCaseEnabled && isString ) ?
se.ignoreCase() : se;
}
}
else {
crit = Restrictions.isNull(propertyName);
}
String critCondition = crit.toSqlString(criteria, cq);
if ( buf.length()>1 && critCondition.trim().length()>0 ) buf.append(" and ");
buf.append(critCondition);
}

//Hibernate原版的Example
protected void appendPropertyCondition(
String propertyName,
Object propertyValue,
Criteria criteria,
CriteriaQuery cq,
StringBuffer buf)
throws HibernateException {
Criterion crit;
if ( propertyValue!=null ) {
//当属性值不为空时，如果是字符串且指定为模糊查询，则使用模糊查询，否则使用等值比较
boolean isString = propertyValue instanceof String;
SimpleExpression se = ( isLikeEnabled && isString ) ?
Restrictions.like(propertyName, propertyValue) :
Restrictions.eq(propertyName, propertyValue);
crit = ( isIgnoreCaseEnabled && isString ) ?
se.ignoreCase() : se;
}
else {
crit = Restrictions.isNull(propertyName);
}
String critCondition = crit.toSqlString(criteria, cq);
if ( buf.length()>1 && critCondition.trim().length()>0 ) buf.append(" and ");
buf.append(critCondition);
}


//增强后的EnhancedExample
private static final RestrictionHolder holder = new DefaultRestrictionHolder();

/**
* Restriction strategy definitions
*/
public static enum RestrictionStrategy {eq, ne, gt, lt, ge, le}

/**
* Restriction strategy holder for the query criteria
*/
public static interface RestrictionHolder {
/**
* Set a restriction strategy for a POJO's property
*/
public RestrictionHolder set(String propertyName, RestrictionStrategy strategy);

/**
* Get the restriction strategy of the property
*/
public RestrictionStrategy get(String propertyName);
}

static final class DefaultRestrictionHolder implements RestrictionHolder {
private Map<String, RestrictionStrategy> strategies = new HashMap<String, RestrictionStrategy>();

public RestrictionHolder set(String propertyName, RestrictionStrategy strategy) {
strategies.put(propertyName, strategy);
return this;
}

public RestrictionStrategy get(String propertyName) {
return strategies.get(propertyName);
}
}

/**
* Get the restriction strategy holder
*/
public RestrictionHolder getRestrictionHolder() {
return holder;
}

protected void appendPropertyCondition(
String propertyName,
Object propertyValue,
Criteria criteria,
CriteriaQuery cq,
StringBuffer buf)
throws HibernateException {
Criterion crit;
if ( propertyValue!=null ) {
//当属性值不为空时，如果为该属性指定了比较条件，则使用指定的比较条件
RestrictionStrategy strategy = holder.get(propertyName);
if ( strategy != null ) {
switch(strategy) {
//case eq: crit = Restrictions.eq(propertyName, propertyValue);
case ne: crit = Restrictions.ne(propertyName, propertyValue); break;
case gt: crit = Restrictions.gt(propertyName, propertyValue); break;
case lt: crit = Restrictions.lt(propertyName, propertyValue); break;
case ge: crit = Restrictions.ge(propertyName, propertyValue); break;
case le: crit = Restrictions.le(propertyName, propertyValue); break;
default: crit = Restrictions.eq(propertyName, propertyValue);
};
}
else {
//否则使用默认的比较条件：如果是字符串且指定为模糊查询，则使用模糊查询，否则使用等值比较
boolean isString = propertyValue instanceof String;
SimpleExpression se = ( isLikeEnabled && isString ) ?
Restrictions.like(propertyName, propertyValue) :
Restrictions.eq(propertyName, propertyValue);
crit = ( isIgnoreCaseEnabled && isString ) ?
se.ignoreCase() : se;
}
}
else {
crit = Restrictions.isNull(propertyName);
}
String critCondition = crit.toSqlString(criteria, cq);
if ( buf.length()>1 && critCondition.trim().length()>0 ) buf.append(" and ");
buf.append(critCondition);
}


于是前面getProducts方法只需要简单修改一下：

Java代码
public List<Product> getProducts(Product product) {
//改用EnhancedExample来允许关联对象的条件查询
final EnhancedExample exampleProduct =
EnhancedExample.create(product).
enableLike(MatchMode.ANYWHERE).
excludeZeroes();
//指定expDate属性使用大于等于比较方法
exampleProduct.getRestrictionHolder().
set("expDate", EnhancedExample.RestrictionStrategy.ge);

return (List<Product>) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Criteria crit =
session.createCriteria(Product.class).
add(exampleProduct);
return crit.list();
}
}
);
}

public List<Product> getProducts(Product product) {
//改用EnhancedExample来允许关联对象的条件查询
final EnhancedExample exampleProduct =
EnhancedExample.create(product).
enableLike(MatchMode.ANYWHERE).
excludeZeroes();
//指定expDate属性使用大于等于比较方法
exampleProduct.getRestrictionHolder().
set("expDate", EnhancedExample.RestrictionStrategy.ge);

return (List<Product>) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Criteria crit =
session.createCriteria(Product.class).
add(exampleProduct);
return crit.list();
}
}
);
}


经过以上改进，QBE的实用性大大提高，能够真正解决较大多数的组合查询问题。

已知的问题：以上“增强的”QBE还无法解决范围查询（比如价格在0到1000之间），这是因为一个属性只能携带一个值（你不可能指定两个值给Product.price属性）。这种情况下需要修改getProducts方法，增加参数把价格范围传递进来，再以QBC方式把相应的条件加到crit变量上。范例代码就不再给出了。

完整的EnhancedExample源码请见附件。
EnhancedExample.zip (3 KB)
描述: 完整的EnhancedExample源码
下载次数: 219