Spring Data JPA custom insert query

Spring Data JPA is an abstraction layer on top of Hibernate that makes working with databases easy. Not only that, it makes switching databases painless as well. Furthermore, it makes working with data stores seamless since all have a common interface. Examples are using ElasticSearch and Redis with Spring Data. Contrary to popular belief, Spring Data JPA does not make things less flexible compared to Hibernate. In this article, we prove it again by using a custom insert query with Spring Data JPA.

Why using custom insert query?

There are many times that the conventional INSERT statement does not fit well with the existing architecture. Especially, if the system architecture has a flaw that is not easily fixable/replaceable or even it is out of the team control.

Yes, it sounds hacky but it happens frequently often in the software industry. That is why there are many books written to help make developers and architects wiser decisions.

Let say there is a scenario that we need to ignore any unique constraint violation because the record is already there. This usually happens when the service has a concurrency problem. But let’s imagine there is such a problem and it is not fixable due to whatever reason.

A good, yet not ideal approach is to tweak the insert statement to ignore any unique constraint error. In MySQL, that’s possible using INSERT IGNORE … statement. But by default, Spring Data JPA runs INSERT INTO ... statement. And that’s exactly where customer insert comes handy.

Duplicate user email use case

Let say we have a User table with the following structure,

CREATE TABLE IF NOT EXISTS users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(512) NOT NULL,
  last_name VARCHAR(512) NOT NULL,
  email VARCHAR(512) NOT NULL,
  CONSTRAINT uc_user_email UNIQUE(email),
  INDEX idx_user_email (email)
) engine=InnoDB;

As you can see, the email field has a unique constraint. This means if we try to run the following query twice we get constraint violation error,

mysql> INSERT INTO users(first_name, last_name, email) VALUES ('kasra', 'mp', '[email protected]');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO users(first_name, last_name, email) VALUES ('kasra', 'mp', '[email protected]');
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'users.uc_user_email'
mysql>

The same happens with Spring as well.

Spring Data JPA custom insert

Adding a custom insert query is very easy to do in Spring Data JPA. All we have to do is to annotate the entity with @SQLInsert. In our use case, we need to apply in in User entity as follow,

import org.hibernate.annotations.SQLInsert;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

@Entity
@Table(name = "users")
@SQLInsert(sql = "INSERT IGNORE INTO users(first_name, last_name, email) " +
        "VALUES (?, ?, ?)" )
public class User {

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

    @Column(name = "first_name", nullable = false)
    @NotBlank
    @Size(max = 512)
    private String firstName;

    @Column(name = "last_name", nullable = false)
    @NotBlank
    @Size(max = 512)
    private String lastName;

    @Column(nullable = false)
    @NotBlank
    @Email
    @Size(max = 512)
    private String email;

    public long getId() {
        return id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

After that, if any constraint violation happens, the insert statement will be ignored.

To ensure it’s working can enable Hibernate query logging in application.properties as follows,

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

That’s all for this tutorial, hope you enjoyed it!

Inline/featured images credits

• Person using Macbook pro by Christina Morillo on Pexels