Dynamic inserts and updates with Spring Data JPA

I show You how To Make Huge Profits In A Short Time With Cryptos!

When you maintain a new entity or update an existing entity with Spring Data JPA, you may have recognized that you always execute the same SQL statement which defines all the columns mapped by the entity. This is even the case if you only update one of its attributes.

This is a performance optimization provided by Hibernate, the JPA implementation that Spring Data JPA uses by default. Hibernate tries to avoid checking which attributes of an entity have changed and generating a specific SQL statement for them. It instead generates 1 SQL UPDATE statement and 1 SQL INSERT statement for each entity class on startup and reuses it for each insert or update operation.

Reusing the same statement over and over makes Hibernate work better. But it also creates side effects. These instructions create overhead if you are modifying only one attribute of a huge feature class. They also cause problems if you need to audit all changes made to a database table. In these cases, it may be better to let Hibernate generate a new SQL INSERT or UPDATE statement for each operation.

Normal behavior

But before showing you how, let’s take a quick look at the default behavior. Here you can see a simple chess player entity that stores the data of each player First name, Last name, and Date of Birth. the identifier The attribute maps the primary key and its values ​​are generated by a database sequence.

@Entity
public class ChessPlayer {

	@Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "player_seq")
    @SequenceGenerator(name = "player_seq", sequenceName = "player_sequence")
	private Long id;

    private String firstName;
    
    private String lastName;

    private LocalDate birthDate;

	...
}

I have prepared a standard repository which just extends Spring Data JPA Jpa repository and does not add any custom queries or other functionality.

public interface ChessPlayerRepository extends JpaRepository<ChessPlayer, Long> { }

And I have prepared a test case that persists a new chess player without adjusting sound Date of Birth attribute. In the next step, I correct a typo in the First name. This will trigger an additional SQL UPDATE statement.

ChessPlayer p = new ChessPlayer();
p.setFirstName("Torben");
p.setLastName("Janssen");
chessPlayerRepository.save(p);

p.setFirstName("Thorben");

As you can see from the log output, Hibernate executed an INSERT SQL statement and an UPDATE statement which set all the columns in the chess player chart. This includes the Date of Birth column, which is set to null.

11:33:15.505 DEBUG 19836 - – [           main] org.hibernate.SQL                        : select nextval ('player_sequence')
11:33:15.514 DEBUG 19836 - – [           main] org.hibernate.SQL                        : select nextval ('player_sequence')
11:33:15.547 DEBUG 19836 - – [           main] org.hibernate.SQL                        : insert into chess_player (birth_date, first_name, last_name, id) values (?, ?, ?, ?)
11:33:15.557 DEBUG 19836 - – [           main] org.hibernate.SQL                        : update chess_player set birth_date=?, first_name=?, last_name=? where id=?

Hibernate’s @DynamicInsert

Spring Data JPA acts as a layer on top of Hibernate. With this you can use all Hibernate proprietary mapping annotations on your entity classes.

If you want to dynamically generate the SQL INSERT statement when persisting a new entity object, you need to annotate the entity class with Hibernate exclusive property @DynamicInsert annotation.

@Entity
@DynamicInsert
public class ChessPlayer {

	@Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "player_seq")
    @SequenceGenerator(name = "player_seq", sequenceName = "player_sequence")
	private Long id;

    private String firstName;
    
    private String lastName;

    private LocalDate birthDate;
	
	...
}

When you then run the same test case as before, you will see in the log output that Hibernate dynamically generated the SQL INSERT statement using only the attributes defined on the new entity object.

ChessPlayer p = new ChessPlayer();
p.setFirstName("Torben");
p.setLastName("Janssen");
chessPlayerRepository.save(p);

p.setFirstName("Thorben");

Hibernation only sets the identifier, First name, and Last name columns in the SQL INSERT statement, but not the Date of Birth column. Hibernate excluded this column because I didn’t define it in the test case before calling the save method on the Spring Data JPA repository.

11:37:20.374 DEBUG 7448 - – [           main] org.hibernate.SQL                        : select nextval ('player_sequence')
11:37:20.386 DEBUG 7448 - – [           main] org.hibernate.SQL                        : select nextval ('player_sequence')
11:37:20.427 DEBUG 7448 - – [           main] org.hibernate.SQL                        : insert into chess_player (first_name, last_name, id) values (?, ?, ?)
11:37:20.435 DEBUG 7448 - – [           main] org.hibernate.SQL                        : update chess_player set birth_date=?, first_name=?, last_name=? where id=?

But the SQL UPDATE statement still updates all the columns mapped by the chess player entity class. If you want to change that, you also need to annotate the entity class with @DynamicUpdate.

@DynamicUpdate from Hibernate

As the @DynamicInsert the annotation described in the previous section, the @DynamicUpdate The annotation tells Hibernate to generate a specific SQL UPDATE statement for each update operation. By doing this, Hibernate detects which attributes have changed and only includes those in the SQL statements.

In the following example, I’ve annotated the chess player entity with Hibernate @DynamicInsert and @DynamicUpdate annotation.

@Entity
@DynamicInsert
@DynamicUpdate
public class ChessPlayer {

	@Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "player_seq")
    @SequenceGenerator(name = "player_seq", sequenceName = "player_sequence")
	private Long id;

    private String firstName;
    
    private String lastName;

    private LocalDate birthDate;
	
	...
}

Let’s run the same test case as in the previous examples.

ChessPlayer p = new ChessPlayer();
p.setFirstName("Torben");
p.setLastName("Janssen");
chessPlayerRepository.save(p);

p.setFirstName("Thorben");

As you can see from the log output, Hibernate has now generated specific SQL INSERT and UPDATE statements.

11:39:45.177 DEBUG 13832 - – [           main] org.hibernate.SQL                        : select nextval ('player_sequence')
11:39:45.185 DEBUG 13832 - – [           main] org.hibernate.SQL                        : select nextval ('player_sequence')
11:39:45.214 DEBUG 13832 - – [           main] org.hibernate.SQL                        : insert into chess_player (first_name, last_name, id) values (?, ?, ?)
11:39:45.224 DEBUG 13832 - – [           main] org.hibernate.SQL                        : update chess_player set first_name=? where id=?

We already discussed the INSERT statement in the previous section, so let’s focus on the update operation.

In the test case, I only changed the value of First name attribute. Hibernate recognized this when it did a dirty check on this entity object. Based on this, Hibernate then generated a SQL UPDATE statement which only changes the value in the First name column.

Conclusion

Spring Data JPA acts as a layer on top of a JPA implementation. In most cases, it’s Hibernate. When you persist or update an entity object, Spring Data JPA delegates this operation to the JPA implementation. Therefore, handling all write operations and generating SQL statements depends on your JPA implementation and its capabilities.

By default, Hibernate does not generate a specific SQL INSERT or UPDATE statement for each entity object. Instead, it generates 1 INSERT statement and 1 UPDATE statement for each entity class at application startup and reuses them for all insert or update operations. This reduces the overhead of these operations but also changes too many columns in the database.

If that’s a problem, you can annotate your entity class with @DynamicInsert and @DynamicUpdate. These proprietary annotations tell Hibernate to dynamically generate the SQL INSERT or UPDATE statement for each entity object. When doing this, please keep in mind that you don’t get it for free, and you can’t enable or disable it for specific use cases. To generate a specific UPDATE or INSERT statement, Hibernate must detect which attributes have changed and generate a new SQL statement based on this information. This slows down all insert or update operations of objects of this entity class.



Source link