I try to use criteria of Hibernate to get data from user search input but it didn't success. I got this error:

SEVERE: Invalid column name 'y1_'.

This is query that Hibernate create in log:

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

I tried to run that query in SQL Server and indeed it's throwing the exact same error. I don't know why Hibernate create a wrong query, please help me fix it. Thank you very much!

This is my code (I used Hibernate 3.2.7, sqljdbc4-3.0 and SQL Server 2022)

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

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        ".0.dtd">
<hibernate-configuration>       
  <session-factory>
    <property name="dialect">.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 mapping

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        ".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 class

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
}

I try to use criteria of Hibernate to get data from user search input but it didn't success. I got this error:

SEVERE: Invalid column name 'y1_'.

This is query that Hibernate create in log:

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

I tried to run that query in SQL Server and indeed it's throwing the exact same error. I don't know why Hibernate create a wrong query, please help me fix it. Thank you very much!

This is my code (I used Hibernate 3.2.7, sqljdbc4-3.0 and SQL Server 2022)

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

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourcefe/hibernate-configuration-3.0.dtd">
<hibernate-configuration>       
  <session-factory>
    <property name="dialect">.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 mapping

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourcefe/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 class

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
}

• java
• sql-server
• hibernate

• Why are you using the oldest lib versions in the universe? It sounds like it's not picking correct dialect and using a construct which isn't allowed in SQL server. – siggemannen Commented yesterday
• This is from the old project and i can't change it, you have any idea to workaround? – Nguyễn Phú Khang Commented yesterday
• If you can't change it how do you expect to work around it? Clearly you need to change it, so upgrade it to modern and supported versions while you're at it. – AlwaysLearning Commented yesterday
• you should add also entities mapping and full code not only a part to let us help you – Luca Basso Ricci Commented yesterday

1 Answer 1

End up I have to remove Projection part and map from Customer to T002Dto manually in order to Hibernate doesn't use alias in WHERE clause