JPA entityManager.merge converts LocalDateTime to SQLServer 2012 datetime2

Hibernate ORM
1

I have table with Datetime 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
https://stackoverflow.com/questions/30028345/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);
        final DatesTbl datesTbl = entityManager.merge(new DatesTbl(creationDate));

        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 ?

2

I don’t think it’s a good idea to use a DATETIME column as a PK. You should use an IDENTITY column or a SEUQENCE and just index the DATETIME column to speed up queries.