JPA entityManager.merge converts LocalDateTime to SQLServer 2012 DATETIME2

Question

I have a table with a DateTime column as Primary Key:

USE [idatest]
GO

CREATE TABLE [dbo].[DatesTbl](
    [creationDate] [datetime] NOT NULL
 CONSTRAINT [PK_DatesTbl] PRIMARY KEY CLUSTERED
(
    [creationDate] ASC
))
GO

When I'm doing entityManager.merge I get duplicate, PK violation since datetime holds 3 digits for milisec, but hibernet converts it to datetime2 , which holds 7 digits for milisec. In the java code, I use LocaDatetime which holds 10 digits for milsec.

I have tried the solution explained at Hibernate MSSQL datetime2 mapping but it doesn't work : The java code looks like : pom.xml

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.3.RELEASE</version>
</parent>

<groupId>com.example</groupId>
<artifactId>spring-jap-test</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>
    </plugins>
</build>

<dependencies>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
    </dependency>
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>7.0.0.jre8</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

DatesTbl class

@Entity
@NoArgsConstructor
@AllArgsConstructor
public class DatesTbl {

    @Column(columnDefinition = "DATETIME", nullable = false)
    @Id
    private LocalDateTime creationDate;
}

Main class

@EnableTransactionManagement
public class Main {

    public static void main(String[] args) {

        ApplicationContext context = new AnnotationConfigApplicationContext(Main.class);

        EntityManagerFactory entityManagerFactory = context.getBean(EntityManagerFactory.class);
        final EntityManager entityManager = entityManagerFactory.createEntityManager();
        final LocalDateTime creationDate = LocalDateTime.of(2018, 12, 26, 8, 10, 40, 340);
        entityManager.getTransaction().begin();
        final DatesTbl datesTbl = entityManager.merge(new DatesTbl(creationDate));
        entityManager.getTransaction().commit();

        System.out.println("test");
    }

    @Bean
    @Primary
    public DataSource getDataSource() {

        SQLServerDataSource ds = null;
        try {
            ds = new SQLServerDataSource();
            ds.setServerName("localhost");
            ds.setDatabaseName("idatest");
            ds.setIntegratedSecurity(true);
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }


    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        HibernateJpaVendorAdapter hibernateJpaVendorAdapter = new HibernateJpaVendorAdapter();
        hibernateJpaVendorAdapter.setShowSql(true);
        hibernateJpaVendorAdapter.setGenerateDdl(true);
        hibernateJpaVendorAdapter.setDatabase(Database.SQL_SERVER);
        return hibernateJpaVendorAdapter;
    }


    @Bean
    public LocalContainerEntityManagerFactoryBean abstractEntityManagerFactoryBean(
            JpaVendorAdapter jpaVendorAdapter) {

        Properties properties = new Properties();
         properties.setProperty(FORMAT_SQL, String.valueOf(true));
        properties.setProperty(SHOW_SQL, String.valueOf(true));
        properties.setProperty(DIALECT, ModifiedSQLServerDialect.class.getTypeName());
        LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean =
                new LocalContainerEntityManagerFactoryBean();

        localContainerEntityManagerFactoryBean.setDataSource(getDataSource());
        localContainerEntityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        localContainerEntityManagerFactoryBean.setJpaProperties(properties);
        localContainerEntityManagerFactoryBean.setPackagesToScan("enteties");

        return localContainerEntityManagerFactoryBean;
    }


    @Bean
    public PlatformTransactionManager platformTransactionManager(EntityManagerFactory emf) {
        return new JpaTransactionManager(emf);
    }
}


public class ModifiedSQLServerDialect extends SQLServer2012Dialect {


    public ModifiedSQLServerDialect () {
        super();
        registerColumnType(Types.TIMESTAMP, "timestamp");
        registerColumnType(Types.DATE, "timestamp");
        registerColumnType(Types.TIME, "timestamp");
        registerHibernateType(Types.TIMESTAMP, "timestamp");
        registerHibernateType(Types.DATE, "timestamp");
        registerHibernateType(Types.TIME, "timestamp");
    }
}

but still I see in the SQLServer profiler :

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

What is wrong with the solution ?

Answer 1

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)