Software Development Notes

I was debugging a problem in our existing java application. It was ignoring the time while sorting. We use Hibernate to connect to underlying MySQL database.

Database

Entries sorted on Entry Date

Java Code - Buggy

...
...
private final static String FIND_BY_BLOGGER =
    " SELECT t.entry_id AS entry_id FROM blog.blog_entries t " +
    " LEFT JOIN profile.profile p ON t.profile_id=p.profile_id  " +
    " WHERE p.login=:blogger AND t.entry_date < :toDate ORDER BY t.entry_date DESC " ;

public List<Long>
           findEntryIdsForBloggerId(final String userId, final int maxResults, Date toEntryDate)
{
    Session session = getSession();
    try {

        SQLQuery query = session.createSQLQuery(FIND_BY_BLOGGER);
        query.addScalar("entry_id",Hibernate.LONG);
        query.setString("blogger", userId);
        query.setDate("toDate", toEntryDate);
        query.setMaxResults(maxResults);

        return query.list();
    } finally {
        releaseSession(session);
    }
}
...
...

If we pass '2009-08-31 09:00:00', as toEntryDate, it was always missing the first(most recent) record - "Amp Up Your Look With StrangeBeautiful's Luxe Nail Lacquers".

Below database results mimic how the dao method retrieves the results.

Error results with setTime()

After reviewing the code line by line, we figured query.setDate("toDate",toEntryDate) was the culprit.

Java Code - Corrected

...
...
private final static String FIND_BY_BLOGGER =
    " SELECT t.entry_id AS entry_id FROM blog.blog_entries t " +
    " LEFT JOIN profile.profile p ON t.profile_id=p.profile_id  " +
    " WHERE p.login=:blogger AND t.entry_date < :toDate ORDER BY t.entry_date DESC " ;

public List<Long>
           findEntryIdsForBloggerId(final String userId, final int maxResults, Date toEntryDate)
{
    Session session = getSession();
    try {

        SQLQuery query = session.createSQLQuery(FIND_BY_BLOGGER);
        query.addScalar("entry_id",Hibernate.LONG);
        query.setString("blogger", userId);
        query.setTimestamp("toDate", toEntryDate);
        query.setMaxResults(maxResults);

        return query.list();
    } finally {
        releaseSession(session);
    }
}
...
...

setDate() sets only the date portion of the toEntryDate in the query. We should use query.setTimestamp("toDate", toEntryDate) to set both date & time & get correct results.

SQLQuery.setDate() vs SQLQuery.setTimestamp()

After replacing it to setTimeStamp(), we started seeing all the records from said date & time!