Issues with JPA Queries with Date constraints

Lately I have been trying to update an Oracle table with a primary key made up of a start date and end date using JPA merge. The merge step was throwing ConstraintViolationExceptions saying that the primary key already existed, which was strange because as far as I thought merge was only supposed to try and insert rows if an existing match didn't exist, in which case we should not ever see constraint violations of this sort.


Due to my still sketchy understanding of JPA I figured creating a new entity object could somehow be causing an automatic database synchronisation step, which results in the Constraint violation exception.

I decided to try searching for the existing record before creating it if the search result came back with null. I added the following query:

   @NamedNativeQuery(
   name = "getUserLogEntry",
   query = "SELECT * " +
   "FROM USER_LOG_ENTRY " +
   "WHERE start_date_time = ?1 " +
   "AND end_date_time = ?2 ",
   resultClass = UserLogEntry.class
   )

Which is called passing in Date objects as parameters:

   Query query = entityManager.createNamedQuery("getUserLogEntry")
   .setParameter(1, startDate, TemporalType.DATETIME)
   .setParameter(2, endDate, TemporalType.DATETIME);

   if(query.getResultList().size() > 0) {
     entry = (UserLogEntry)query.getSingleResult();   
   }

This however, was not finding any entry in the database, even though there definitely was such an entry there!

After much wasted time I did find a way aroung this (although not a full understanding of why the previous attempts were not working). It seems passing java Date objects to the SQL query somehow scrambles somewhere in the Date translation, resulting in no matches. Therefore I casted my java dates to Strings and then used SQL's TO_DATE to format the dates exactly as I wanted:

   @NamedNativeQuery(
   name = "getUserLogEntry",
   query = "SELECT * " +
   "FROM HC_DYN_USER_LOG_ENTRY " +
   "WHERE start_date_time = TO_DATE(?1, 'dd/MM/yyyy hh:mi:ss PM') " +
   "AND end_date_time = TO_DATE(?2, 'dd/MM/yyyy hh:mi:ss PM') ",
   resultClass = UserLogEntry.class
   )


    private static SimpleDateFormat reportDateFormat = new SimpleDateFormat("dd/MM/yyyy hh:mm:ss a"); 

...


  String startDateStr = reportDateFormat.format(startDateTime);
  String endDateStr = reportDateFormat.format(endDateTime);
  Query query = entityManager.createNamedQuery("getUserLogEntry")
   .setParameter(1, startDateStr)
   .setParameter(2, endDateStr);
 
   if(query.getResultList().size() > 0) {
     entry = (UserLogEntry)query.getSingleResult();   
   }