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)).
