Spring JPA: batch insert query with collections using SpEL possible?

Question

I want to create a an insert query on a repository using the following function

void insertValues(Iterable<Long> keys)

The query must insert multiple rows, but only the primary key changes, the rest are constants;

I tried with the following queries

@Query(nativeQuery = true, value = "INSERT INTO my_table (id, col1, col2, col3) VALUES :#{[0].!['('+#this+',''val1'',''val2'',''val3'')']}" )

and

@Query(nativeQuery = true, value = "INSERT INTO my_table (id, col1, col2, col3) VALUES (:#{[0].![new Object[]{#this, 'val1', 'val2', 'val3'}]})" )

While the former fails because the whole value is inserted with a prepared statement as a single VARCHAR, the latter fails because the SpEL expression terminates on the first closing bracket (where i close the array) instead of the second

Is there any way to do this?

score -1 · Answer 1 · answered Apr 12 '21 at 16:00

I don't think it is possible. Use something like this:

interface MyRepo {
  @Query(nativeQuery = true, value = "INSERT INTO my_table (id, col1, col2, col3) VALUES (?, 'val1', 'val2', 'val3')" )
  void insertValue(long id);

  default void insertValues(Iterable<Long> keys) {
    keys.forEach(this::insertValue);
  }

}

Spring JPA: batch insert query with collections using SpEL possible?

1 Answers1