How to convert joda DateTime to original jpa request

Question

I have an object with DateTime properties that are saved using hibernate

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime") @Column(name = "EFF_DT") protected DateTime effDt; 

All this works well and is useful for regular spring-data-jpa generated queries.

I am trying to add my own custom query

  @Query(value = "SELECT COUNT(*) FROM wsa_circuit_state_history ch WHERE ch.eff_dt between ?1 and ?2", nativeQuery = true) Integer countEffDateBetween(DateTime start, DateTime end); 

The error I get when trying to call this

  java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got BINARY 

This is the same error that I usually encountered with regular spring-data finders before adding custom type personalization to my object

  @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime") 

How can I make spring -data-jpa / hibernate use custom type matching for parameters for my own queries?

Answer 1

Just set your property in java.util.Date, like this

 @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime") @Column(name = "EFF_DT") @Temporal( TemporalType.TIMESTAMP ) protected Date effDt; 

Then convert to your getter / setter

 public DateTime getEffDt() { return null == effDt ? null : new DateTime( effDt ); } public void setEffDt( final DateTime effDt ) { this.effDt = null == effDt ? null : effDt.toDate(); } 

Native:

 @Query(value = "SELECT COUNT(*) FROM wsa_circuit_state_history ch WHERE ch.eff_dt between ?1 and ?2", nativeQuery = true) Integer countEffDateBetween(Date start, Date end); 

Answer 2

In a similar situation, I resorted to writing a custom CompositeUserType that stored the Joda DateTime in two fields, a date and time field and a time zone field.

When the object was saved, it saved the time value in UTC and the time zone as the initial value; when searching, it did the opposite (i.e., converted the time stored in the database back to the original time zone).

As you have already defined above, although a query such as “dateTime BETWEEN? 1 AND? 2” has an intuitive meaning, of course, it does not make any sense in terms of composite types, since you need to remember the time zone. It is for this reason that I saved all the values ​​in UTC, so I could always compare any two date and time values ​​in the database.

Unfortunately, this meant that all the date / time request parameters that I passed had to be converted to UTC.

Answer 3

Exactly, I have the same requirement. And I enabled it by registering a custom type for hibernation configuration.

For native queries, TypeDefs is not enough for hibernation to find a custom type. To determine the types of its types, type recognizers are needed.

 @Query(value = "SELECT COUNT(*) FROM wsa_circuit_state_history ch WHERE ch.eff_dt between ?1 and ?2", nativeQuery = true) Integer countEffDateBetween(DateTime start, DateTime end); 

In this case, hibernate tries to guess the type (org.joda.time.DateTime) from the converter types.

 Type type = session.getFactory().getTypeResolver().heuristicType(typename); 

Since there is no permission for DateTime.Then it gets the default type (serializable type). And the DateTime value is cast to its type, which is serialized and stored in the database, where it fails due to a large binary value.

To solve this problem, you need to register the DateTime type for the Hibernate configuration as

 configuration.registerTypeOverride(new org.jadira.usertype.dateandtime.joda.PersistentDateTime(), new String[]{"org.joda.time.DateTime"});