JPA Native query puts parentheses wrong for List paremeter on INSERT INTO VALUES

Question

So I am trying to write a native named query for JPA (JPQL) to INSERT multiple rows of data in one go, and get back all id's of the inserted records (that don't conflict with unique constrain on the primary key). I am using a Postgres database.

Database looks like this: SELECT * FROM table;

id | sent
---+------
1  | f
(1 row)

What I'd like to achieve is this PSQL statement:

INSERT INTO table VALUES (1, false),(2, false) ON CONFLICT DO NOTHING RETURNING id;

Which should return:

id
---
2
(1 row)

So my initial idea was to make a native query in JPA:

@Repository
interface NotificationRepository : JpaRepository<Foo, Int> {

    @Modifying
    @Query("INSERT INTO Foo VALUES (:foos) ON CONFLICT DO NOTHING RETURNING id", nativeQuery = true)
    fun addAllAndReturnInserted(foos: List<Foo>): List<Int>
}

Which works for one value in the list, but for multiple values it will create a query like:

Hibernate: INSERT INTO Foo VALUES (?, ?) ON CONFLICT DO NOTHING RETURNING id

And it throws an exception:

PSQLException: ERROR: column "sent" is of type boolean but expression is of type int

Is there a way to transform the list into separate perentheses like (?), (?) instead of in one (?, ?) ??

Answer 1

No, your idea is nice but will not work with Spring Data, because:

1. list parameter expansion happens only as a comma-separated list of data values (a, b, ...), not a list of records or rows (a), (b), (...)
2. data-modifying statements that return something are also not supported (yet): https://jira.spring.io/browse/DATAJPA-1389

However, there is a workaround to at least the batching problem as described here:

• How to do bulk (multi row) inserts with JpaRepository?