How to insert record into database using spring jpa when there is no conflict

Question

I have spring boot application that is connected with the PostgreSQL database using spring-data-jpa, here is my entity with nearly 40 fields

Now for saving the entity into database, I'm just using the studentRepository.save method

studentRepository.save(new StudentEntity());

DAO Entity

  @Table
  @Entity
  public class StudentEntity {

     @Id
     @Generate( using database sequenece)
     private long studentId;

     private String studentName;

     private String dept;

     private String age;
      ..
      ..
      }

Repository

 public interface StudentRepository implements JPARepository<Long, Student> {
   }

But now I have requirement, if there is any student record in table with name and dept I should not insert the new record, I know I can use PostgreSQL ON CONFLICT with native query for this, but if I use native query I have to specify all 40 fields in query and as method arguments which looks ugly.

Is there any way to make it simpler?

Example of native query

@Query(value = "insert into Users (name, age, email, status) values (:name, :age, :email, :status)", nativeQuery = true)

 void insertUser(@Param("name") String name, @Param("age") Integer age, @Param("status") Integer status, @Param("email") String email);

Declare a unique index on those attributes, insert unconditionally, and catch/translate a thrown exception? — chrylis -cautiouslyoptimistic-, Mar 26 '19 at 04:11
We had faced a similar issue where I had to use the "ON DUPLICATE KEY" feature specific to MYSQL database. Initially we went ahead with using @Query initially, but was not readable. So we searched and found [JOOQ](https://www.jooq.org/) to be a suitable alternative for such cases. — Anirudh Simha, Mar 26 '19 at 05:53
can you show an example for the above scenario, and how to integrate that one? — app, Mar 26 '19 at 13:23
take a look at https://stackoverflow.com/a/35083965/11189140 — Anirudh Simha, Mar 26 '19 at 13:36
if I understand but still I have to provide all 40 values in that query ? — app, Mar 26 '19 at 15:04
yeah, but instead of setting these variables in an object, you set it inside the jooq query generator — Anirudh Simha, Mar 27 '19 at 05:53

score 8 · Accepted Answer · answered Apr 24 '19 at 10:54

8

Use the database. Create a unique constraint on the 2 fields and trying to add multiple of those will be prevented.

Something like

ALTER TABLE StudentEntity ADD CONSTRAINT UQ_NAME_DEPT UNIQUE (studentName,dept);

This unique constraint will prevent the insertion of duplicate combinations.

You could also define an constraint on your JPA entity to automatically create the index for testing

@Table(uniqueConstraints=@UniqueConstraint(columnNames = {"studentName", "dept"})

answered Apr 24 '19 at 10:54

M. Deinum

115,695
22
220
224

I will try this thank you, does it throws any exception while inserting duplicate records? or will just ignore? – app Apr 24 '19 at 12:55
In the case of exception spring will take care of closing connection part right? as a programmer do we need to do anything other than handling it? – app Apr 24 '19 at 13:03
I strongly suggest you readup on the technologies you are using (JPA and Spring). No you don't need to do anything (if you use thins in a normal way!). Also you can just use the `save` method on the repository, you don't need a seperate insert method for that nor a dedicated query. – M. Deinum Apr 24 '19 at 13:10
1

The one downside of the unique constraint approach (as opposed to `insert... on conflict do nothing`) is that Hibernate with log the collision as an error. So depending on how often this happens, it may or may not be worth the noise to you. (Yes, you could configure your logger to ignore error messages from Hibernate's SqlExceptionHelper class, but that could hide other real errors). – David Siegal Dec 10 '20 at 22:40
The constraint works out-of-the-box. The `insert .. on conflict do nothing` requires a custom query to be written and doesn't work on all databases. – M. Deinum Dec 11 '20 at 07:42

score 0 · Answer 2 · answered Apr 24 '19 at 10:48

If your priority is to reduce verbosity, as it is native sql you can avoid the names of the fields if you put them in the correct order, but you must put all the values of the table, even the null:

@Query(value = "insert into Users values (:name, :age, :email, :status)", nativeQuery = true)
 void insertUser(@Param("name") String name, @Param("age") Integer age, @Param("status") Integer status, @Param("email") String email);

Or instead of doing it with native insert, you can save if the method findByStudentNameAndDebt(studentName, dept), does not return any result. In StudentRepository (here native is not necessary):

@Query("SELECT a FROM Student as a WHERE lower(a.studentName) = lower(:studentName) AND lower(a.dept) = lower(:dept)")
    public Student findByStudentNameAndDebt(@Param("studentName") final String studentName, @Param("dept") final String dept);

And in your service:

if(studentRepository.findByStudentNameAndDebt(studentName, dept)==null) {
   studentRepository.save(new StudentEntity());
}

This is not thread safe. Imagine if two threads started at the same time: both would get `null` from `findByStudentNameAndDebt` and then both would save new entity. — KarolisL, Nov 13 '19 at 08:36

score 0 · Answer 3 · answered Apr 24 '19 at 11:20

No need of native query. You can do it by writing object oriented queries too. first check is there any record present in table by name and dept. If it returns null, then do your save.

StudentRepository.java

public interface StudentRepository implements JPARepository<Long, Student> {


@Query("FROM Student AS stu WHERE lower(stu.name) = lower(:studentName) AND lower(stu.dept) = lower(:dept)")
public Student findStudentByNameAndDept(@Param("studentName") String studentName, @Param("dept") String dept);

//if you have already duplicates in your db, then you can do the followings too..
@Query("FROM Student AS stu WHERE lower(stu.name) = lower(:studentName) AND lower(stu.dept) = lower(:dept)")
public List<Student> findAllStudentsByNameAndDept(@Param("studentName") String studentName, @Param("dept") String dept);

}

StudentService.java

if(studentRepository.findStudentByNameAndDept(name,dept)==null){
    //save
}else{
    //custom hanlding
    throw StudentExistsException("Student with the given name/dept exists");
}

OR
//if already duplicates exisited in db

if(studentRepository.findStudentByNameAndDept(name,dept).isEmpty()){
    //save
}else{
    //custom hanlding
    throw StudentExistsException("Student with the given name/dept exists");
}

This allows race conditions to happen, when two threads have passed the if condition and entered into the save block at the same time. — René Jahn, Apr 07 '20 at 05:05

corroborator · Answer 4 · 2019-04-26T05:01:01.310

you can use a lightweight query to check whether a student with same name and department already exists

public interface StudentRepository implements JPARepository<Long, Student> {

@Query("SELECT CASE WHEN COUNT(c) > 0 THEN true ELSE false END FROM Student s WHERE s.studentName= :studentName AND s.dept= :dept")
    public boolean existsByNameAndDepartment(@Param("studentName") String studentName, @Param("dept") String dept);

How to insert record into database using spring jpa when there is no conflict

4 Answers4

StudentRepository.java

StudentService.java