hibernate derived properties with xml

Question

I have table called Student as Below

ID        NAME     DOB
---       ----     ---------
1         A        2009-01-28
2         B        2001-05-20

I am using derived properties to get the AGE from the dob as below in the hbm file

<hibernate-mapping>
<class name="com.arat.derived.Person" table="person" catalog="spring_db">
    <id name="id" type="int">
        <column name="id" />
        <generator class="assigned" />
    </id>
    <property name="name" type="string">
        <column name="name" />
    </property>
    <property name="dob" type="date">
        <column name="dob" length="0" />
    </property>
    <property name="age" type="integer" column="AGE"
        formula="( select TIMESTAMPDIFF(YEAR,p.dob,NOW()) as AGE 
                     from person p where p.ID=ID)"></property>
  </class>
</hibernate-mapping>

and my POJO class Student has all the property id,name,dob and age with setter and getter method.

While fetching the data using load or get method I am getting the error

 private static void loadPerson() {
    Session session = factory.openSession();
    Person person = (Person) session.get(Person.class, 0);
    System.out.println("Person : name " + person.getName() 
            + ", dob: " + person.getDob() + ", age : " + person.getAge());

}

Hibernate: select person0_.id as id1_8_0_, person0_.name as name2_8_0_, person0_.dob as dob3_8_0_, ( select TIMESTAMPDIFF(person0_.YEAR,p.dob,NOW()) as person0_.AGE from person p where p.ID=person0_.ID) as formula0_0_ from spring_db.person person0_ where person0_.id=? Feb 07, 2015 7:20:22 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 1064, SQLState: 42000 Feb 07, 2015 7:20:22 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'person0_.YEAR,p.dob,NOW()) as person0_.AGE from person p where p.ID=person0_.ID)' at line 1 Feb 07, 2015 7:20:22 PM org.hibernate.event.internal.DefaultLoadEventListener onLoad INFO: HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSe**

can you tell me where I am wrong?

Answer 1

This is a small addition to the Radim Köhler's https://stackoverflow.com/a/28383566/2576551 answer. It seems like the keyword registration in a custom dialect is case sensitive for some hibernate versions. I am running hibernate 4.3.8.Final and keyword has to be lowercase.

//doesn't work
registerKeyword("YEAR");

//works
registerKeyword("year");

Related thread Hibernate mapped property to SQL formula fails after update to 3.6.5 that proposed to lowercase keyword.

Answer 2

This is interesting issue, related to Hibernate way how to handle formula="" SQL snippet.

What happened behind the scene is:

unexpected injection/extenion of the formula="" parts with an TABLE alias

That means that this:

formula="( select TIMESTAMPDIFF(YEAR, p.dob, NOW()) 
               as AGE from person p where p.ID = ID )"

Was converted into that:

( select TIMESTAMPDIFF(person0_.YEAR,p.dob,NOW()) 
      as person0_.AGE  from person p where p.ID=person0_.ID)

Firstly we have to change is the as AGE which is not needed and is also extended.

formula="( select TIMESTAMPDIFF(YEAR, p.dob, NOW()) 
            from person p where p.ID = ID )"

Next - the alias person0_ is now used for more parts than we need.

Reason is:

Hibernate does not know, which parts are

• native SQL words/keywords of our DB engine and
• which are column/property names.

The way how to solve it, is to extend the Dialect, and teach it new keyword. E.g. like this

// extend the correct dialect ... in thhis case MySql
public class CustomMySQLDialect extends MySQL5InnoDBDialect
{
    public CustomMySQLDialect()
    {
        super();
        registerKeyword("YEAR");
    }
}

And also do not forget to use this new dialect - CustomMySQLDialect in configuration