Documentation on change in behaviour with LocalTime

There’s a change in behaviour between 6.1 & 6.2, hinted at in the migration notes, but it’s not obvious that code could end up exhibiting untoward results with no warning.

My code does something like:

String timeString = "12:59:27.457";
String sql = "insert into mytable (TimePass) values (:TimePass)";
LocalTime lt = LocalTime.parse(anotherString);
Query qry = localSession.createNativeQuery(sql);
qry.setParameter("TimePass",timeString);

I’m on SQL Server, with the TimePass field of type time(7) (which should include milliseconds).
I know createNativeQuery is deprecated - there are other reasons for using it, and so far I’ve not found what the replacement should be.

When I .executeUpdate(), the value actually inserted is something entirely different. For the example above, I get the time “19:56:07.000” inserted instead - about 25000 seconds later.

For a timeString of “12:59:02.427”, I get “11:35:42.000” - about 5000 seconds earlier. So something odd is going on.

To get the correct behaviour, I need to do:

qry.setParameter("TimePass",java.sql.Time.valueOf(timeString));

instead.

My hunch is that somewhere something in interpreting the milliseconds part as some sort of offset - but this didn’t happen in Hibernate 6.1.7, only when I moved to 6.2.2!

I’m not sure if this is a bug or an undocumented feature, but either way I thought I ought to publicise it and the workaround in case anyone else is stumped!

Just on my hunch, for a large dataset I plotted the milliseconds part against the error in what ends up in the database. There’s a clear pattern, though I still haven’t spotted why:

You can debug into org.hibernate.type.descriptor.java.LocalTimeJavaType#unwrap where the LocalTime is converted to java.sql.Time. Maybe you can spot the bug directly.
Either way, please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) that reproduces the issue.

Sorry, just spotted a silly error in that code - it should of course be:

String timeString = "12:59:27.457";
String sql = "insert into mytable (TimePass) values (:TimePass)";
LocalTime lt = LocalTime.parse(timeString);
Query qry = localSession.createNativeQuery(sql);
qry.setParameter("TimePass",lt);

OK, I’ll work on a proper test case.

1 Like

Well, I’ve tried.

I’ve got some code that demonstrates the issue:

    String timeString = "12:34:56.789";
    try
    {
      Configuration config = new Configuration();
      config.setProperty("hibernate.connection.driver_class","org.h2.Driver");
      config.setProperty("hibernate.connection.url","jdbc:h2:mem:test");
      config.setProperty("hibernate.dialect","org.hibernate.dialect.H2Dialect");
      config.setProperty("hibernate.show_sql","true");
      config.setProperty("hibernate.connection.username","sa");
      config.setProperty("hibernate.connection.password","");

      SessionFactory sessionFactory = config.buildSessionFactory();

      //create simple table with one field
      try (Session localSession = sessionFactory.openSession())
      {
        Query qryCreate = localSession.createNativeQuery("create table test (mytime time(7));");
        try
        {
          localSession.beginTransaction();
          qryCreate.executeUpdate();
          localSession.getTransaction().commit();
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }

      //write one value to time field
      try (Session localSession = sessionFactory.openSession())
      {
        Query qryInsert = localSession.createNativeQuery("insert into test (mytime) values (:mytime);");
        try
        {
          localSession.beginTransaction();
          LocalTime lt = LocalTime.parse(timeString);
          qryInsert.setParameter("mytime",lt);
          System.out.println("Inserting time string "+timeString+" (as LocalTime: "+lt+")");
          qryInsert.executeUpdate();
          localSession.getTransaction().commit();
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }

      //write value to time field, this time using java.sql.Time.valueOf
      try (Session localSession = sessionFactory.openSession())
      {
        Query qryInsert = localSession.createNativeQuery("insert into test (mytime) values (:mytime);");
        try
        {
          localSession.beginTransaction();
          LocalTime lt = LocalTime.parse(timeString);
          qryInsert.setParameter("mytime",java.sql.Time.valueOf(lt));
          System.out.println("Inserting time string "+timeString+" (as LocalTime: "+lt+") using java.sql.Time.valueOf(lt): "+java.sql.Time.valueOf(lt));
          qryInsert.executeUpdate();
          localSession.getTransaction().commit();
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }

      //now retrieve value
      try (Session localSession = sessionFactory.openSession())
      {
        Query qrySelect = localSession.createNativeQuery("select mytime from test;");
        try
        {
          List<java.sql.Time> resultList = qrySelect.getResultList();

          for (java.sql.Time res: resultList)
          {
            System.out.println("Got value: "+res.toString());
          }
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }

    }
    catch (Exception e)
    {
      e.printStackTrace();
    }

Output:

Inserting time string 12:34:56.789 (as LocalTime: 12:34:56.789)
Inserting time string 12:34:56.789 (as LocalTime: 12:34:56.789) using java.sql.Time.valueOf(lt): 12:34:56
Got value: 15:44:56
Got value: 12:34:56

Unfortunately, I can’t work out how to turn it into a JUnit test case. There are two templates - should I use the JPA one or the ORM one? And surely for a test unit I need to attach it to a class? presumably something in hibernate-orm? but I can’t compile hibernate-orm, as Netbeans complains about:

FAILURE: Build failed with an exception.

* What went wrong:
A problem occurred configuring project ':hibernate-jpamodelgen'.
> Could not resolve all files for configuration ':hibernate-jpamodelgen:xjc'.
   > Could not find org.glassfish.jaxb:jaxb-xjc:4.0.2.
     Required by:
         project :hibernate-jpamodelgen
   > Could not find jakarta.activation:jakarta.activation-api:2.1.1.
     Required by:
         project :hibernate-jpamodelgen > org.glassfish.jaxb:jaxb-runtime:4.0.2 > org.glassfish.jaxb:jaxb-core:4.0.2
         project :hibernate-jpamodelgen > org.glassfish.jaxb:jaxb-runtime:4.0.2 > org.glassfish.jaxb:jaxb-core:4.0.2 > org.eclipse.angus:angus-activation:2.0.0
   > Could not find jakarta.activation:jakarta.activation-api:2.1.1.
     Required by:
         project :hibernate-jpamodelgen > org.glassfish.jaxb:jaxb-runtime:4.0.2 > org.glassfish.jaxb:jaxb-core:4.0.2 > jakarta.xml.bind:jakarta.xml.bind-api:4.0.0

(I’m on NB16, using Gradle 7.3.3, and using JDK11 (I’ve also tried using 17 & 19)).

Did you not create the issue [HHH-16686] - Hibernate JIRA? There is even a fix for this ready: HHH-16686 - Fix for bug in LocalTimeJavaType.unwrap() by jrenaat · Pull Request #6664 · hibernate/hibernate-orm · GitHub