Spring Data DynamicInsert Annotation Example

1. Introduction

Spring Data JPA supports common JPA providers, e.g. Hibernate, EclipseLink, etc. Hibernate pre-generates and caches static SQL insert statements that include every mapped column by default. The @org.hibernate.annotations.DynamicInsert overwrites the default implementation and dynamically generates the insert SQL statement for non-null fields at runtime. In this example, I will demonstrate the Spring Data DynamicInsert annotation with a simple Spring boot application.

2. Setup

In this step, I will create a gradle project for Java 17 along with lombok, Spring Data JPA, H2 database, and Junit libraries. The project is generated by Spring initializer with the details outlined in Figure 1.

2.1 Generated Files

In this step, I will show three generated files. No modification is needed for this example.

The generated build.gradle file includes needed libraries.

build.gradle

plugins {
	id 'java'
	id 'org.springframework.boot' version '3.3.4'
	id 'io.spring.dependency-management' version '1.1.6'
}

group = 'org.jcg.zheng'
version = '0.0.1-SNAPSHOT'

java {
	toolchain {
		languageVersion = JavaLanguageVersion.of(17)
	}
}

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'com.h2database:h2'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
	useJUnitPlatform()
}

The generated DynamicinsertApplication.java file.

DynamicinsertApplication.java

package org.jcg.zheng.dynamicinsert;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DynamicinsertApplication {

	public static void main(String[] args) {
		SpringApplication.run(DynamicinsertApplication.class, args);
	}

}

The generated DynamicinsertApplicationTests.java file.

DynamicinsertApplicationTests.java

package org.jcg.zheng.dynamicinsert;

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class DynamicinsertApplicationTests {

	@Test
	void contextLoads() {
	}

}

3. Spring Data DynamicInsert in Entity

In this step, I will create a DemoEntity.java that annotates with @DynamicInsert.

DemoEntity.java

package org.jcg.zheng.dynamicinsert.entity;

import org.hibernate.annotations.DynamicInsert;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Entity
@Table(name = "T_DEMOTABLE")
@DynamicInsert
@Data
public class DemoEntity {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	private String firstName;

	private String lastName;

	private String email;

	private Integer age;

}

• Line 3: the @DynamicInsert is not supported by other JPA providers as it’s from Hibernate.
• Line 14: the @DynamicInsert overwrites the default SQL generation. You will see the insert SQL statements generated in step 5.

4. Demo Repository

In this step, I will create a DemoEntityRepo.java that extends from org.springframework.data.jpa.repository.JpaRepository.

DemoEntityRepo.java

package org.jcg.zheng.dynamicinsert.repo;

import org.jcg.zheng.dynamicinsert.entity.DemoEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface DemoEntityRepo extends JpaRepository<DemoEntity, Integer> {

}

4.1 DemoEntityRepoTest

In this step, I will create a DemoEntityRepoTest.java that saves the DemoEntity with the various null fields. The dynamically generated SQL insert statements will be printed out in step 5.

DemoEntityRepoTest.java

package org.jcg.zheng.dynamicinsert.repo;

import org.jcg.zheng.dynamicinsert.entity.DemoEntity;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class DemoEntityRepoTest {

	private static final Logger logger = LoggerFactory.getLogger(DemoEntityRepoTest.class);

	@Autowired
	private DemoEntityRepo testRepo;
	private DemoEntity demoEntity = new DemoEntity();

	@Test
	void test_FirstName_only() {
		demoEntity.setFirstName("Mary");

		saveEntity(demoEntity);
	}

	@Test
	void test_Name_only() {
		demoEntity.setFirstName("Mary");
		demoEntity.setLastName("Zheng");

		saveEntity(demoEntity);
	}

	private void saveEntity(DemoEntity demoEntity) {
		logger.info("before save: " + demoEntity);
		demoEntity = testRepo.save(demoEntity);
		logger.info("after save: " + demoEntity);
	}

	@Test
	void test_Name_email() {
		demoEntity.setFirstName("Mary");
		demoEntity.setLastName("Zheng");
		demoEntity.setEmail("mary.s.zheng@jcg.org");

		saveEntity(demoEntity);
	}

}

• Line 20: test_FirstName_only verifies the generated insert SQL statement only including first_name and primary key Id fields.
• Line 27: test_Name_only verifies the generated insert SQL statement including both first_name and Last_name along with the primary key Id fields.
• Line 35, 36, 37: saveEntity method prints the DemoEntity data before and after the save method.
• Lome 41: test_Name_email verifies the generated insert SQL statement including first_name, last_name, and email fields.

I will configure the application.properties to show the SQL statements generated by Hibernate.

application.properties

spring.application.name=dynamicinsert

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

• Line 3: enables show-sql property.
• Line 4: enables format_sql property.

5. Spring Data DynamicInsert Demo

In this step, I will run the unit tests and capture the console log to verify the insert SQL statements generated by Hibernate.

Junit Output

11:56:16.914 [main] INFO org.springframework.test.context.support.AnnotationConfigContextLoaderUtils -- Could not detect default configuration classes for test class [org.jcg.zheng.dynamicinsert.repo.DemoEntityRepoTest]: DemoEntityRepoTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
11:56:17.005 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper -- Found @SpringBootConfiguration org.jcg.zheng.dynamicinsert.DynamicinsertApplication for test class org.jcg.zheng.dynamicinsert.repo.DemoEntityRepoTest

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/

 :: Spring Boot ::                (v3.3.4)

2024-10-12T11:56:17.526-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : Starting DemoEntityRepoTest using Java 17.0.11 with PID 22312 (started by azpm0 in C:\MaryTools\workspace\dynamicinsert)
2024-10-12T11:56:17.527-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : No active profile set, falling back to 1 default profile: "default"
2024-10-12T11:56:17.903-05:00  INFO 22312 --- [dynamicinsert] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2024-10-12T11:56:17.961-05:00  INFO 22312 --- [dynamicinsert] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 47 ms. Found 1 JPA repository interface.
2024-10-12T11:56:18.390-05:00  INFO 22312 --- [dynamicinsert] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2024-10-12T11:56:18.546-05:00  INFO 22312 --- [dynamicinsert] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:75bfbe68-bfaa-4e14-9e50-259e867c0e02 user=SA
2024-10-12T11:56:18.548-05:00  INFO 22312 --- [dynamicinsert] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2024-10-12T11:56:18.602-05:00  INFO 22312 --- [dynamicinsert] [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2024-10-12T11:56:18.662-05:00  INFO 22312 --- [dynamicinsert] [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 6.5.3.Final
2024-10-12T11:56:18.703-05:00  INFO 22312 --- [dynamicinsert] [           main] o.h.c.internal.RegionFactoryInitiator    : HHH000026: Second-level cache disabled
2024-10-12T11:56:19.041-05:00  INFO 22312 --- [dynamicinsert] [           main] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2024-10-12T11:56:19.871-05:00  INFO 22312 --- [dynamicinsert] [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000489: No JTA platform available (set 'hibernate.transaction.jta.platform' to enable JTA platform integration)
Hibernate: 
    drop table if exists t_demotable cascade 
Hibernate: 
    create table t_demotable (
        age integer,
        id integer generated by default as identity,
        email varchar(255),
        first_name varchar(255),
        last_name varchar(255),
        primary key (id)
    )
2024-10-12T11:56:19.905-05:00  INFO 22312 --- [dynamicinsert] [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2024-10-12T11:56:20.262-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : Started DemoEntityRepoTest in 3.042 seconds (process running for 4.069)
2024-10-12T11:56:20.806-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : before save: DemoEntity(id=null, firstName=Mary, lastName=Zheng, email=null, age=null)
Hibernate: 
    insert 
    into
        t_demotable
        (first_name, last_name, id) 
    values
        (?, ?, default)
2024-10-12T11:56:20.911-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : after save: DemoEntity(id=1, firstName=Mary, lastName=Zheng, email=null, age=null)
2024-10-12T11:56:20.924-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : before save: DemoEntity(id=null, firstName=Mary, lastName=null, email=null, age=null)
Hibernate: 
    insert 
    into
        t_demotable
        (first_name, id) 
    values
        (?, default)
2024-10-12T11:56:20.926-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : after save: DemoEntity(id=2, firstName=Mary, lastName=null, email=null, age=null)
2024-10-12T11:56:20.930-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : before save: DemoEntity(id=null, firstName=Mary, lastName=Zheng, email=mary.s.zheng@jcg.org, age=null)
Hibernate: 
    insert 
    into
        t_demotable
        (email, first_name, last_name, id) 
    values
        (?, ?, ?, default)
2024-10-12T11:56:20.932-05:00  INFO 22312 --- [dynamicinsert] [           main] o.j.z.d.repo.DemoEntityRepoTest          : after save: DemoEntity(id=3, firstName=Mary, lastName=Zheng, email=mary.s.zheng@jcg.org, age=null)
2024-10-12T11:56:20.941-05:00  INFO 22312 --- [dynamicinsert] [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
Hibernate: 
    drop table if exists t_demotable cascade 
2024-10-12T11:56:20.945-05:00  INFO 22312 --- [dynamicinsert] [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2024-10-12T11:56:20.947-05:00  INFO 22312 --- [dynamicinsert] [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

• Line 40-45: the insert SQL statement includes non-null fields: first_name, last_name, id.
• Line 49-54: the insert SQL statement includes non-null fields: first_name, id.
• Line 58-63: the insert SQL statement includes non-null fields: email, first_name, last_name, id.
• Line (38, 46), (47, 55), (56, 64): the DemoEntity's data before and after the save method.

6. Conclusion

As shown in step 5, the insert SQL statements are different based on the non-null fields, therefore the database can not pre-compile the insert SQL statement’s execution plan and reuse it for better performance with @DynamicInsert. It’s good to use @DynamicInsert when the database table has many columns but the application only inserts data with a few columns. It should avoid using @DynamicInsert for a batch process due to can not pre-compile the SQL statement.

7. Download

This was an example of a gradle project which included the @DynamicInsert annotation.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.