Hibernate Criteria生成错误SQL，提示无效列名'y1_'求解决

问题详情 #

使用Hibernate Criteria根据用户搜索条件查询数据时失败，报错信息：

SEVERE: Invalid column name 'y1_'.

Hibernate生成的SQL语句如下：

Hibernate: select top 15 this_.CUSTOMER_ID as y0_, this_.CUSTOMER_NAME as y1_, this_.SEX as y2_, this_.BIRTHDAY as y3_, this_.ADDRESS as y4_, this_.EMAIL as y5_ from MSTCUSTOMER this_ where this_.DELETE_YMD is null and y1_ like ? order by y0_ asc

在SQL Server中执行该语句也会触发相同错误，无法理解Hibernate为何生成错误SQL，寻求解决办法。

使用版本 #

• Hibernate 3.2.7
• sqljdbc4-3.0
• SQL Server 2022

相关代码 #

Criteria查询代码 #

// Add criteria
criteria.add(Restrictions.isNull("deleteYMD"));
String customerName = t002Form.getCurrentInput().getCustomerName();

if (!Utils.checkEmpty(customerName)) {
    criteria.add(Restrictions.like("customerName", customerName, MatchMode.ANYWHERE));
}
String sex = t002Form.getCurrentInput().getSex();
if (!Utils.checkEmpty(sex)) {
    criteria.add(Restrictions.eq("sex", sex));
}
String fromBirthday = t002Form.getCurrentInput().getFromBirthday();
if (!Utils.checkEmpty(fromBirthday)) {
    criteria.add(Restrictions.ge("birthday", fromBirthday));
}
String toBirthday = t002Form.getCurrentInput().getToBirthday();
if (!Utils.checkEmpty(toBirthday)) {
    criteria.add(Restrictions.le("birthday", toBirthday));
}
            
// Add paging
int firstResultIndex = t002Form.getCurrentPage() * Constants.PAGE_SIZE;
criteria.setFirstResult(firstResultIndex);
criteria.setMaxResults(Constants.PAGE_SIZE);
criteria.addOrder(Order.asc("customerID"));
            
// Add projection
criteria.setProjection(Projections.projectionList()
                            .add(Projections.property("customerID"), "customerID")
                            .add(Projections.property("customerName"), "customerName")
                            .add(Projections.property("sex"), "sex")
                            .add(Projections.property("birthday"), "birthday")
                            .add(Projections.property("address"), "address")
                            .add(Projections.property("email"), "email"));
                        
// Map to T002Dto
criteria.setResultTransformer(Transformers.aliasToBean(T002Dto.class));
List<T002Dto> listCustomer = Utils.castList(T002Dto.class, criteria.list());

Hibernate配置文件 #

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>       
  <session-factory>
    <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
    <property name="show_sql">true</property> 
    <property name="hibernate.jdbc.batch_size">50</property>
    <property name="hibernate.order_updates">true</property>
    <mapping resource="java/resources/hbm/User.hbm.xml"/>
    <mapping resource="java/resources/hbm/Customer.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

Hibernate映射文件 #

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
        
<hibernate-mapping package="fjs.cs.model">
    <class name="Customer" table="MSTCUSTOMER">
        <id name="customerID" column="CUSTOMER_ID">
            <generator class="native"/>
        </id>
        <property name="customerName" column="CUSTOMER_NAME"/>
        <property name="sex" column="SEX"/>
        <property name="birthday" column="BIRTHDAY"/>
        <property name="email" column="EMAIL"/>
        <property name="address" column="ADDRESS"/>
        <property name="deleteYMD" column="DELETE_YMD"/>
        <property name="insertYMD" column="INSERT_YMD"/>
        <property name="insertPsnCD" column="INSERT_PSN_CD"/>
        <property name="updateYMD" column="UPDATE_YMD"/>
        <property name="updatePsnCD" column="UPDATE_PSN_CD"/>
    </class> 
</hibernate-mapping>

POJO类 #

public class Customer {

    private int customerID;
    private String customerName;
    private String sex;
    private String birthday;
    private String email;
    private String address;
    private Timestamp deleteYMD;
    private Timestamp insertYMD;
    private int insertPsnCD;
    private Timestamp updateYMD;
    private int updatePsnCD;

    // Getters, Setters and Constructor
}

问题原因及解决办法 #

问题原因 #

Hibernate 3.2.7的Criteria API存在Bug：当先添加查询条件（Restrictions）再设置Projection时，Hibernate会错误地将WHERE子句中的实体属性替换为Projection生成的临时别名（如y1_），而SQL Server不支持在WHERE子句中直接引用SELECT子句的别名，因此触发报错。

解决办法 #

调整代码执行顺序，先设置Projection，再添加查询条件、分页和排序规则：

修改后的Criteria代码：

// 先设置Projection
criteria.setProjection(Projections.projectionList()
                        .add(Projections.property("customerID"), "customerID")
                        .add(Projections.property("customerName"), "customerName")
                        .add(Projections.property("sex"), "sex")
                        .add(Projections.property("birthday"), "birthday")
                        .add(Projections.property("address"), "address")
                        .add(Projections.property("email"), "email"));

// 再添加查询条件
criteria.add(Restrictions.isNull("deleteYMD"));
String customerName = t002Form.getCurrentInput().getCustomerName();

if (!Utils.checkEmpty(customerName)) {
    criteria.add(Restrictions.like("customerName", customerName, MatchMode.ANYWHERE));
}
String sex = t002Form.getCurrentInput().getSex();
if (!Utils.checkEmpty(sex)) {
    criteria.add(Restrictions.eq("sex", sex));
}
String fromBirthday = t002Form.getCurrentInput().getFromBirthday();
if (!Utils.checkEmpty(fromBirthday)) {
    criteria.add(Restrictions.ge("birthday", fromBirthday));
}
String toBirthday = t002Form.getCurrentInput().getToBirthday();
if (!Utils.checkEmpty(toBirthday)) {
    criteria.add(Restrictions.le("birthday", toBirthday));
}

// 添加分页和排序
int firstResultIndex = t002Form.getCurrentPage() * Constants.PAGE_SIZE;
criteria.setFirstResult(firstResultIndex);
criteria.setMaxResults(Constants.PAGE_SIZE);
criteria.addOrder(Order.asc("customerID"));

// 映射到DTO
criteria.setResultTransformer(Transformers.aliasToBean(T002Dto.class));
List<T002Dto> listCustomer = Utils.castList(T002Dto.class, criteria.list());

补充说明 #

• Hibernate 3.x版本的Criteria API存在较多类似兼容性问题，若有升级计划，建议升级到Hibernate 5.x及以上版本，此类Bug已被修复。
• SQL Server语法规则限制：WHERE子句无法直接引用SELECT子句定义的列别名，必须使用原始列名或嵌套子查询，调整Projection顺序后，Hibernate会正确生成使用原始列名的WHERE条件。

内容的提问来源于stack exchange，提问作者Nguyễn Phú Khang