Search code examples
javaspringspring-bootspring-data-jpaspring-data

How to check if record exists before insert - Spring JPA


In the project that I am doing, I want to check if a record with the same data already exists in a specific field in the database, if it already exists then do not insert, if it does not exist that does insert, I am searching the way to reach the expected result, but I don't know if there are other alternatives to obtain the result I expect.

This is my code that I am using.

Entity:

@Entity
@Table(name = "users")
public class Users {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_user")
    private Long idUser;

    @Column(name = "user", nullable = false, length = 150)
    private String user;

    @Column(name = "code_number", nullable = false, length = 10)
    private String codeNumber;

        public Long getIdUser() {
        return idUser;
    }

    public void setIdUser(Long idUser) {
        this.idUser = idUser;
    }

        public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

        public String getCodeNumber() {
        return codeNumber;
    }

    public void setCodeNumber(String codeNumber) {
        this.codeNumber = codeNumber;
    }
}

Query/Repository:

@Query(nativeQuery= true, value="SELECT DISTINCT(code_number) FROM users WHERE code_number= :codeNumber")
boolean findByCodeNumber(String codeNumber);

Conditional:

public String processUserRegister(Users users) {
    if(repo.findByCodeNumber(users.getCodeNumber())) {
        return "redirect:/error";
    } else {
        return "redirect:/success";
    }
}

Solution

  • There are few points that you are missing here

  • Concurrency - your assumption is that your code will always be executed by one thread at a time. But the truth is there could be two or more threads trying to insert the exact code number in parallel.
    you may call bunch of methods from a single method from service layer and could make it synchronized but again what if there are multiple instances of your application running on separate JVM or VM or machines. So, it's better to use unique constraint at the database level.
  • Performance - By executing this check/query everytime, you are wasting resources like CPU cycle of application OS, Database, and consuming network bandwidth too.