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 ?
