JPA entityManager.merge converts LocalDateTime to SQLServer 2012 DATETIME2

I don’t think it’s a good idea to use a DATETIME column as a PK. There could be two entities created in the same nanosecond which will conflict.

You should use an IDENTITY column or a SEQUENCE and just index the DATETIME column to speed up queries.

It is a legacy system - I can't change it. The primary key consists of 2 fields of int and this datetime column.

In this case, you need to use a composite identifier.

Also, I don't think you need these:

registerColumnType(Types.TIMESTAMP, "timestamp");
registerColumnType(Types.DATE, "timestamp");
registerColumnType(Types.TIME, "timestamp");

registerHibernateType(Types.TIMESTAMP, "timestamp");
registerHibernateType(Types.DATE, "timestamp");
registerHibernateType(Types.TIME, "timestamp");

The SQL Server Dialect should provide the right type of mappings.

Explanation

The problem is related to an issue in mssql-jdbc (version 4.x and 6.x), PreparedStatement.setTimestamp(index, timestamp, calendar) has datatype conversion issues, which always send the LocalDateTime parameter with datetime2 data type to SQL server(ignoring the column type of the table). Due to the different in accuracy of datetime (0.00333sec) and datetime2(100 nanoseconds), and datetime is used as PK, Hibernate works wrongly in this case.

As we run the main program, the creationDate is having value 2018-12-26 08:10:40.000000340 and the value is saved as 2018-12-26 08:10:40.000 in DB as Hibernate find no record with same key in DB. When we run the main program again, Hibernate first check if there is any record with same key, using

'select datestbl0_.creationDate as creation1_0_0_ from DatesTbl datestbl0_ where datestbl0_.creationDate=@P0 ',N'@P0 'datetime2'','2018-12-26 08:10:40.0000003'

It seems that SQL Server upcast the datetime value in the table to datetime2 for comparison and no record is returned. Hence Hibernate insert the record again, and result in Primary Key Violation.

Workaround

As suggested by Vlad Mihalcea, it is not a good idea to use a DATETIME column as a PK.
However, suppose we still need the datetime column as PK, the following workaround should work. The key to solve this problem is to make the comparison between datetime and datetime2 return true. To achieve this, we can truncate/round the datetime2 value to the corresponding datetime value before passing to DB. The following changes to the main program is tested with SQL Server 2012 Express with no error.

public static void main(String[] args) {
    ApplicationContext context = new AnnotationConfigApplicationContext(Main.class);

    EntityManagerFactory entityManagerFactory = context.getBean(EntityManagerFactory.class);
    final EntityManager entityManager = entityManagerFactory.createEntityManager();

    LocalDateTime creationDate0 = LocalDateTime.of(2018, 12, 26, 8, 10, 40, 341340340);
    LocalDateTime creationDate3 = LocalDateTime.of(2018, 12, 26, 8, 10, 40, 343340340);
    LocalDateTime creationDate7 = LocalDateTime.of(2018, 12, 26, 8, 10, 40, 346670340);
    LocalDateTime creationDate10 = LocalDateTime.of(2018, 12, 26, 8, 10, 40, 349670340);
    entityManager.getTransaction().begin();
    final DatesTbl datesTbl0 = entityManager.merge(new DatesTbl(roundNanoSecForDateTime(creationDate0)));
    final DatesTbl datesTbl3 = entityManager.merge(new DatesTbl(roundNanoSecForDateTime(creationDate3)));
    final DatesTbl datesTbl7 = entityManager.merge(new DatesTbl(roundNanoSecForDateTime(creationDate7)));
    final DatesTbl datesTbl10 = entityManager.merge(new DatesTbl(roundNanoSecForDateTime(creationDate10)));
    entityManager.getTransaction().commit();
    System.out.println("test");
}

private static LocalDateTime roundNanoSecForDateTime(LocalDateTime localDateTime) {
    int nanoSec = localDateTime.getNano();
    // The rounding is based on following results on SQL server 2012 express
    // select cast(cast('2018-12-26 08:10:40.3414999' as datetime2) as datetime);
    // 2018-12-26 08:10:40.340
    // select cast(cast('2018-12-26 08:10:40.3415000' as datetime2) as datetime);
    // select cast(cast('2018-12-26 08:10:40.3444999' as datetime2) as datetime);
    // 2018-12-26 08:10:40.343
    // select cast(cast('2018-12-26 08:10:40.3445000' as datetime2) as datetime);
    // select cast(cast('2018-12-26 08:10:40.3484999' as datetime2) as datetime);
    // 2018-12-26 08:10:40.347
    // select cast(cast('2018-12-26 08:10:40.3485000' as datetime2) as datetime);
    // 2018-12-26 08:10:40.350
    int last7DigitOfNano = nanoSec - (nanoSec / 10000000) * 10000000;
    int roundedNanoSec = 0;
    if (last7DigitOfNano < 1500000) {
        roundedNanoSec = nanoSec - last7DigitOfNano;
    } else if (last7DigitOfNano < 4500000) {
        roundedNanoSec = nanoSec - last7DigitOfNano + 3000000;
    } else if (last7DigitOfNano < 8500000) {
        roundedNanoSec = nanoSec - last7DigitOfNano + 7000000;
    } else {
        roundedNanoSec = nanoSec - last7DigitOfNano + 10000000;
    }
    System.out.println("Before Rounding" + nanoSec);
    System.out.println("After Rounding" + roundedNanoSec);
    return localDateTime.withNano(roundedNanoSec);
}

Reference:
1. DateTime2 vs DateTime in SQL Server
2. Date and Time Data Types and Functions (Transact-SQL)