SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got BINARY

2
votes

I would appreciate some help on this exception.

I did some research on this error but I could not find a solution. I use JPA with hibernate and get this error when executing a query.

Obviously, there's something that I don't understand about java LocalDate and oracle Date. :(

This is the object:

@Entity
@Table(name = "MY_TABLE")
public class MyObject implements Serializable {

    private static final long serialVersionUID = 1731972128723930612L;

    @Id
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "OTHER_ID", nullable = false)
    private OtherObject otherObject;

    @Column(name = "MY_DATE", columnDefinition="DATE", nullable = false)
   // @Convert(converter = LocalDateSQLDateConverter.class)
   // @Convert(converter = LocalDateConverter.class)
    private LocalDate date;

As you can see, I tried to use converters which convert LocalDate to java.util.Date or java.sql.Date and viceversa - both did unfortunately not help.

This is the query:

public class MyDao extends BaseDao {

    public void save(MyObject object) {
        entityManager.persist(object);
    }

    public MyObject findByDate(LocalDate date) {
        TypedQuery<MyObject> query = entityManager.createQuery("SELECT w from MyObject w"
                + " where w.date = :date", MyOBject.class)
                .setParameter("date", date);
        return query.getResultList().stream().findFirst().orElse(null);
    }

This is the exception:

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

oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884) oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167) oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289) oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584) oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628) oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493) org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462) org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) org.hibernate.loader.Loader.getResultSet(Loader.java:2090) org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887) org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1866) org.hibernate.loader.Loader.doQuery(Loader.java:905) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:347) org.hibernate.loader.Loader.doList(Loader.java:2578) org.hibernate.loader.Loader.doList(Loader.java:2564) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2394) org.hibernate.loader.Loader.list(Loader.java:2389) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495) org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357) org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:198) org.hibernate.internal.SessionImpl.list(SessionImpl.java:1230) org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268) my.company.path.MyDao.findByDate(MyDaoDao.java:21)

Thanks a lot in advance for any hints!

4
javaoraclehibernatejpa
What version is your Oracle JDBC driver? try a fresher one! - Usagi Miyamoto
Unfortunately, I won't be able to change it as it is given in the jboss docker image. - Nina
I found out that I did not use the newest JPA version & changed it. Unfortunately, the problem remained. I exchanged LocalDate & LocalDateTime in my application with java.util.Date, and that works. But I would really prefer to use LocalDate & LocalDateTime. - Nina
Try something like this: thoughts-on-java.org/persist-localdate-localdatetime-jpa - Usagi Miyamoto
I found that, too, and tried it, unfortunately, no success. - Nina

4 Answers

0
votes

Although it seems like counter intuitive, you should pass a string representation, like this:

SELECT * FROM xxx where date_of_birth = '1990-08-21';

where date_of_birth is of type DATE (MySQL, not Oracle, but shouldn't matter) and mapped to LocalDate in Spring/Hibernate

0
votes

Hibernate 4 came out before java.time.LocalDate was added and it does not understand it. It therefore sends the data as binary to Oracle.

The best fix is to upgrade to Hibernate 5.

0
votes

I found out that our docker image uses JBoss 6.4, which - according to https://access.redhat.com/articles/112673 - uses Hibernate JPA 2.0.

So, this can't work. We need JPA 2.2 to support LocalDate & LocalDateTime.

I changed all occurrences of LocalDate and LocalDateTime in the DAOs with Date, while the rest of my app continues to use LocalDate and LocalDateTime - with the help of a little converter: 

public class LocalDateConverter {

    public static Date convertToDate(LocalDate date) {
        return date != null ? Date.from(date.atStartOfDay(ZoneId.systemDefault()).toInstant()) : null;
    }

    public static LocalDate convertToLocalDate(Date date) {
        return date != null ? LocalDate.from(date.toInstant().atZone(ZoneId.systemDefault())) : null;
    }

    public static Date convertToDate(LocalDateTime dateTime) {
        return dateTime != null ? Date.from(dateTime.atZone(ZoneId.systemDefault()).toInstant()) : null;
    }
}

Theoretically, there is one method missing which converts Date to LocalDateTime, but in my case I don't need it because I use LocalDateTime only for creationDates which are never read.

0
votes

Problem is not in the mapping of the output but the mapping of the parameter.

While hibernate manage beans, it knows it must convert from LocalDate to Date.

I don't know why but entityManager doesn't do the same for parameters input. I assume entityManager has a shorter list of known classes with converters and others are converted using toString().

You have to convert your input parameter to Date:

TypedQuery<MyObject> query = entityManager.createQuery("SELECT w from MyObject w"
            + " where w.date = :date", MyOBject.class)
            .setParameter("date",Date.from(date.atStartOfDay(ZoneId.systemDefault()).toInstant()));

If you use Spring, you may also org.springframework.data.jpa.repository.Query

 @Query("SELECT w from MyObject w where w.date = :date")
 public MyObject findByDate(LocalDate date);

It will automatically convert your input parameter.