How to convert joda DateTime in jpa native query

Question

I have an entity with DateTime properties persisted with hibernate

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

This all works well and good for regular spring-data-jpa generated queries.

I am trying to add a custom native 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 is when trying to call this is

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

This is the same error i used to get with regular spring-data finders before adding the custom type mapping to my entity

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

How can i make spring-data-jpa/hibernate use the custom type mapping for parameters to native queries?

Answer 1

Exactly I too got same requirement.And I resolved it by registering custom type to hibernate configuration.

For native queries,TypeDefs are not sufficient for hibernate to find user type.It needs Type resolvers to find its types.

@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 type(org.joda.time.DateTime) from type resolvers.

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

As there is no resolver for DateTime.Then it is getting default type(serializable type).And the value of DateTime is casted to its type,that is serialized and persisting in DB,where it is failing due to large binary value.

To resolve this, you need to register DateTime type to Hibernate Configuration as

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