ResultTransformer with createSQLQuery forces no camelCase in entity fields

Question

I have an sql query as follows:

List<Employee> employees = getCurrentSession()
                    .createSQLQuery(
                            "select"
                                    + e.id as id,e.first_name as firstName,e.password as password
                                    + "from employee e,employee_role er,role r where e.employee_id=er.employee_id and er.role_id=r.role_id and r.name='ROLE_ADMIN' ")
                    .setResultTransformer(Transformers.aliasToBean(Employee.class))
                    .list();

I have a property in the Employee called firstName, but when trying to run above dao in a unit test, I am getting the following exception:

org.hibernate.PropertyNotFoundException: Could not find setter for firstname on class com.app.domain.Employee

I don't know where hibernate get from this firstname property ? I didn't right it in my query ?

any way the workaround was to change the property to firstname, and getters,setters too but any ideas why hibernate is making such behavior, and how to avoid it, since I want to use camelCase in my domain, please advise.

Answer 1

You can use addScalar(String columnAlias, Type type) to explicitly declare the column alias of your native SQL:

  getCurrentSession()
  .createSQLQuery( "select e.id as id,e.first_name as firstName,e.password as password from xxxxxx")
                .addScalar("id",StandardBasicTypes.INTEGER )
                .addScalar("firstName",StandardBasicTypes.STRING )
                .addScalar("password",StandardBasicTypes.STRING )
                .setResultTransformer(Transformers.aliasToBean(Employee.class))
                .list();

Answer 2

For a simpler solution, double-quote the identifier in the query as sent to the server. Instead of

e.first_name as firstName

it should read

e.first_name as "firstName"

In PostgreSQL, double-quoting an identifier forces case-sensitivity. Unquoted, it (mostly) follows the SQL standard and folds to a single case (albeit lower case where the standard is upper case).