Is there a way to have custom SQL query on top of JPA repository to have BULK UPSERTS?

I have a snowflake database and it doesn't support unique constraint enforcement (https://docs.snowflake.com/en/sql-reference/c开发者_StackOverflowonstraints-overview.html).

I'm planning to have a method on JPA repository with a custom SQL query to check for duplicates before inserting to the table.

Entity

@Entity
@Table(name = "STUDENTS")
public class Students {

  @Id
  @Column(name = "ID", columnDefinition = "serial")
  @GenericGenerator(name = "id_generator", strategy = "increment")
  @GeneratedValue(generator = "id_generator")
  private Long id;

  @Column(name = "NAME")
  private String studentName;

}

Snowflake create table query

CREATE table STUDENTS(
    id int identity(1,1) primary key,
    name VARCHAR NOT NULL,
    UNIQUE(name)
);

Repository

public interface StudentRepository extends JpaRepository<Students, Long> {

//
@Query(value = "???", nativeQuery = true)
    List<Student> bulkUpsertStudents(List<Student> students);

}

You can use a SELECT query to check for duplicate values in the name column before inserting a new record into the table. For example:

@Query(value = "SELECT * FROM STUDENTS WHERE name = :name", nativeQuery = true)
List<Student> findByName(@Param("name") String name);

This method will return a list of Student records with the specified name value. If the list is empty, it means that there are no records with that name value, and you can safely insert a new record with that name value.

List<Student> studentList = new ArrayList<>();
for (Student student : students) {
    List<Student> existingStudents = studentRepository.findByName(student.getName());
    if (existingStudents.isEmpty()) {
        studentsToInsert.add(student);
    }
}
studentRepository.bulkUpsertStudents(studentList)

EDIT

But this will be very time-consuming. Snowflake does not support unique constraint enforcement, but you can use the INSERT INTO ... ON CONFLICT statement to avoid inserting duplicate records. Here is an example of how you could use this statement in your custom SQL query:

@Query(value = "INSERT INTO STUDENTS (name) VALUES (:name) ON CONFLICT (name) DO NOTHING", nativeQuery = true)
List<Student> bulkUpsertStudents(List<Student> students);

This query will insert the name of each Student in the students list into the STUDENTS table, and if a conflict occurs, it will not insert a new record. This will ensure that only unique name values are inserted into the table, without having to perform a separate query for each record.