Hibernate Joda DateTime Sorting

Question

I am using Joda DateTime and the UserType library for hibernate 4

I have a JPA entity with the following field

@Columns(columns = { @Column(name = "lastUsedDateTimeStamp"), @Column(name = "lastUsedDateTimeStamp_TMZ") })
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTimeAsString")
private DateTime lastUsedDateTimeStamp;

I am using a normal Spring Data JPA repository as follows:

return repository.findAll(new PageRequest(0, 5, new Sort(Sort.Direction.DESC, "lastUsedDateTimeStamp"))).getContent();

However when I look at the sql that hibernate throws out in the logs it end as follows:

order by
        entity.lastUsedDateTimeStamp,
        entity.lastUsedDateTimeStamp_TMZ asc limit ?

This means that the sorting is not working on the lastUsedDateTimeStamp column as expected, as the "asc" keyword is after lastUsedDateTimeStamp_TMZ instead of lastUsedDateTimeStamp.

Does anyone know how I can fix it so that the query specifies "asc" on the correct field?

Answer 1

Solved this one myself, had to write my own custom PersistentDateTimeAsString and AbstractMultiColumnDateTime classes that reversed the default order of the 2 fields.

timezone is now first in the order and then date time. So the sql now looks like this:

order by
        entity.lastUsedDateTimeStamp_TMZ,
        entity.lastUsedDateTimeStamp asc limit ?