Search code examples
sql-serverhibernateunit-testinghsqldb

Native Query date formats between different database engines

I am creating a native query for Hibernate using the following code

    final Query countQuery = entityManager.createNativeQuery( countSql );

    if ( countCriteria.getStartDate() != null ) {
        final String startDate = countCriteria.getStartDate().toString();
        countQuery.setParameter( START_DATE_PARAMETER, startDate );
    }

Where startDate on my class is an org.joda.time.DateTime and toString() returns "2019-05-13".

These query parameters work fine in production, which is running against MS Sql Server. (ick!)

Now, the unit test is running against HSQLDB. I'm providing the same values and it throws:

javax.persistence.PersistenceException: org.hibernate.exception.DataException: data exception: invalid datetime format

When it attempts to run the query.

Since the parameter is formatted exactly the same, I'm assuming that the problem is the format of the date string for HSQLDB.

So, now what? How do I move forward with this? I've checked the setParameter format that takes TEMPORAL or whatever, but that won't work for JODA time.

I've already talked to my tech lead about not using native queries, but using hibernate queries, but it has fallen on deaf ears. Do I have another option so I don't have to battle my tech lead to write unit tests for our DAOs?

Solution

  • Well, I'm embarrassed to admit how long it took me to figure this one out.

    I simply converted the LocalDate to a java date, then used that to set a TemporalType parameter.

        Date date = localDate.toDate();
    countQuery.setParameter(parmName, date, TemporalType.DATE);

    This seems to be working fine.