Criteria rootCr = session.createCriteria(entityClazz);
Criteria subACr = rootCr.createCriteria("subA","a");
Criteria subBCr = rootCr.createCriteria("subB","b");
String sql = "{alias}.root_time > date_add({a}.a_time, interval 20 hour or {b}.b_time > date_add({a}.a_time, interval 20 hour "
rootCr.add(Restrictions.sqlRestriction(sql));
注:date_add函数是MySQL的,无需理会
上面这段代码使用Hibernate3是会报错的,Hibernate只会将{alias}替换成加入了SQLCriterion的Criteria的别名,此处是rootCr,默认为this_,而对于{a}和{b}都无视。在生成的sql中subA和subB表的别名分别为a1_和b2_,所以会报错
修改SQLCriterion的实现可以达到多表别名替换的效果:
import java.util.Iterator;
import org.hibernate.Criteria;
import org.hibernate.EntityMode;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.engine.TypedValue;
import org.hibernate.impl.CriteriaImpl;
import org.hibernate.impl.CriteriaImpl.Subcriteria;
import org.hibernate.type.Type;
import org.hibernate.util.StringHelper;
/**
* Hack the original class
* 2012-04-10
* @author wangzhen
*
*/
public class SQLCriterion implements Criterion {
private static final long serialVersionUID = 561456879784847643L;
private final String sql;
private final TypedValue[] typedValues;
public String toSqlString(
Criteria criteria,
CriteriaQuery criteriaQuery)
throws HibernateException {
CriteriaImpl rootCriteria = null;
if(criteria instanceof CriteriaImpl){
rootCriteria = (CriteriaImpl)criteria;
}else if(criteria instanceof Subcriteria){
rootCriteria = (CriteriaImpl) ((Subcriteria)criteria).getParent();
}else {
throw new HibernateException("暂不支持其他Criteria的实现");
}
Iterator iterateSubcriteria = rootCriteria.iterateSubcriteria();
String tempSql = sql;
//replace subcriterias' alias
while (iterateSubcriteria.hasNext()) {
Subcriteria subCriteria = (Subcriteria) iterateSubcriteria.next();
tempSql = StringHelper.replace( tempSql, "{"+subCriteria.getAlias()+"}", criteriaQuery.getSQLAlias(subCriteria) );
}
return StringHelper.replace( tempSql, "{alias}", criteriaQuery.getSQLAlias(criteria) );
}
public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
return typedValues;
}
public String toString() {
return sql;
}
public SQLCriterion(String sql, Object[] values, Type[] types) {
this.sql = sql;
typedValues = new TypedValue[values.length];
for ( int i=0; i<typedValues.length; i++ ) {
typedValues[i] = new TypedValue( types[i], values[i], EntityMode.POJO );
}
}
}