Sunday, December 13, 2015

Clumsy mistake on Hibernate Filter

Before I met with Hibernate Filter, where clause would be my best friend doing the filtering in a query. Assuming I have a Use Case which will filter a date range from a resultset, this is what I usually did:
public void hibernateQuery() throws SQLException
{
 Configuration cfg = new Configuration();
 cfg.configure("hibernate.cfg.xml");
 SessionFactory fac = cfg.buildSessionFactory();
 Session s = fac.openSession();
 
 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
 Calendar cal = Calendar.getInstance();
 cal.set(2015, 0, 27);
 
 Query res = s.createQuery("from TheTable where to_char(parseDateTime(theDate, 'yyyy-mm-dd'), 'yyyy-mm-dd') = '" + 
               sdf.format(cal.getTime()) + 
               "'");
 List<thetablet> l = res.list();
 
 /***** process your data *****/

 s.close();
}
And this is the Hibernate construct of the table:
<hibernate-mapping>
    ...
    <class catalog="TEST" name="org.huahsin.model.TheTable" table="THE_TABLE">
        <property name="theDate" type="timestamp">
            <column length="23" name="THE_DATE" not-null="true">
        </column>
    </class>
</hibernate-mapping>
Notice the where clause is attached to the query and it is on object type. But with Hibernate Filter, the where clause will detach from the query and the filtering will be done in Hibernate construct. Just like this:
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Filter filter = s.enableFilter("FilterByDate");

Calendar cal = Calendar.getInstance();
cal.set(2015, 0, 28);
filter.setParameter("beforeDate", sdf.format(cal.getTime()));

Calendar cal2 = Calendar.getInstance();
cal2.setTime(cal.getTime());
cal2.add(Calendar.DATE, 1);
filter.setParameter("afterDate", sdf.format(cal2.getTime()));

Query res = s.createQuery("from TheTable");
And the Hibernate construct would be like this:
<hibernate-mapping>
    ...
    <class name="org.huahsin.model.TheTable" table="THE_TABLE" catalog="TEST">
        ...
        <filter name="FilterByDate" condition="to_char(parseDateTime(THE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') between :beforeDate and :afterDate" />
    </class>
    <filter-def name="FilterByDate">
        <filter-param name="beforeDate" type="string"/>
        <filter-param name="afterDate" type="string"/>
    </filter-def>
</hibernate-mapping>
Notice the condition attribute (which is the same thing as where clause) was in pure SQL pattern. What a clumsy mistake I made was that I'm using Hibernate object in the condition attribute and causing the resultset return nothing. I'm just so clumsy!

No comments: