CODEWITHSUNDEEP
postgresqljpaeclipselink
BeanSalad
BeanSalad

Reputation: 11

List as QueryParameter in JPA EclipseLink with PostgreSQL not working

I am using JPA (Implementation EclipseLink) together with a PostgreSQL database. When using native queries, I get errors when using (named) query parameters that are resolved to Lists, for example "in (#ids)".

Let's say I have a table "car" with columns "id" and "name". I want to find the names of all cars with ids from a set "ids". Then I would utilize a native query with JPA like so:

final String sql = """
  select c.name
  from cars c
  where c.id = (#ids)""";
final List<String> result = em.createNativeQuery(sql)
.setParameter("ids", new ArrayList<>(ids))
.getResultList();

When executing said query I get the following error:

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.12.v20230209-e5c4074ef3): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: Der in SQL für eine Instanz von java.util.ArrayList zu verwendende Datentyp kann nicht abgeleitet werden. Benutzen Sie 'setObject()' mit einem expliziten Typ, um ihn festzulegen.
Error Code: 0
Call:          select c.name
            from cars c
            where c.id = (?)
    bind => [[1]]
Query: DataReadQuery(sql="         select c.name
            from cars c
            where c.id = (?)")
    at app//org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:700)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2105)
    at app//org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:313)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:345)
    at app//org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:327)
    at app//org.eclipse.persistence.queries.DataReadQuery.executeNonCursor(DataReadQuery.java:199)
    at app//org.eclipse.persistence.queries.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:154)
    at app//org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913)
    at app//org.eclipse.persistence.queries.DataReadQuery.execute(DataReadQuery.java:139)
    at app//org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:812)
    at app//org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3004)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1898)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1880)
    at app//org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1845)
    at app//org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262)
    ... 162 more
Caused by: org.postgresql.util.PSQLException: Der in SQL für eine Instanz von java.util.ArrayList zu verwendende Datentyp kann nicht abgeleitet werden. Benutzen Sie 'setObject()' mit einem expliziten Typ, um ihn festzulegen.
    at app//org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1051)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:2703)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:791)
    at app//org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:628)
    ... 179 more

Is anyone familiar with this error? Currently I am looking into Criteria API and Spring Data as possible workarounds.

EDIT: Manipulations of String literals is not an option (SQL Injection)

Versions:

-'org.eclipse.persistence:org.eclipse.persistence.jpa:2.7.12'

-'org.postgresql:postgresql:42.6.0'

Upvotes: 0

Views: 255

Answers (1)

Chris
Chris

Reputation: 21145

What you are after is not supported in JPA or EclipseLink: JPA does not allow passing in parameters. Implementation wise, EclipseLink does not parse the SQL you provide in Native SQL queries, and instead will pass it to the driver as is to create a prepared statement. If you want to be able to expand your list into multiple parameters, use JPQL:

em.createQuery("select c.name from cars c where c.id in :idList")
.setParameter("idList", new ArrayList<>(ids))
.getResultList();

Upvotes: 0

Related Questions