Error in SPRING JPA using eclipse link POSTGRESQL when executing findBy[NAME]In(List ids)

Question

I got a problem with my SPRING JpaRepository class when trying to use the findByIdIn(List ids), eg i want to pass a list of ids to the native query and expect it to return a list of objects.

Maven dep.:

spring-data-jpa 1.4.2
org.eclipse.persistence.jpa 2.5.1

DB:

PostgreSQL 9.3

Appserver:

Glassfish 4.0

Here is my repository class

@Repository
public interface TransactionRepository extends JpaRepository<Transaction, Long>, QueryDslPredicateExecutor<Transaction> {

    List<Transaction> findByIdIn(List<Long> ids);
}

Field id in database is BIGINT.

When I pass ONLY 1 object in the list it works, when it's 0 or more than 1 object in the list I receive this error:

Local Exception Stack:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = record
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 160
Error Code: 0
Call: SELECT ID, amount, comment, created_date, currency, status, transaction_step, transaction_type, version, provider_id, user_id FROM anix.transactions WHERE (ID IN ((?,?)))
    bind => [2, 3]
Query: ReadAllQuery(referenceClass=Transaction sql="SELECT ID, amount, comment, created_date, currency, status, transaction_step, transaction_type, version, provider_id, user_id FROM anix.transactions WHERE (ID IN (?))")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)

The SQL works fine in my admin tool if I copy/paste it there.

--- EDIT ---

@Entity
@Table(name = "transactions", schema = "anix")
@XmlRootElement
@XmlAccessorType(FIELD)
public class Transaction implements Serializable {
    @Id
    @SequenceGenerator(name = "transactions_sequence", sequenceName = "transactions_id_seq", initialValue = 1, allocationSize = 1)
    @GeneratedValue(strategy = SEQUENCE, generator = "transactions_sequence")
    @XmlID
    @XmlElement
private Long id;

Would appreciate any help on this!

Regards

What is the type of the `id` field on the `Transaction` entity? — Kevin Bowersox, Dec 17 '13 at 10:19
Can u trying changing the JPA provider once? Like Hibernate or OPEN JPA? — Abhijit Mazumder, Dec 17 '13 at 10:58
Show the query. If it is native, it will not take a list as a parameter, so I don't think it's a native SQL query. — Chris, Dec 17 '13 at 14:00
Ok, so i tried to follow the example given here: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/jpa.repositories.html And the keywords allowed in table : Table 2.3. Supported keywords inside method names — user3110615, Dec 17 '13 at 20:03
The query definition is the method if I understood correctly: List findByIdIn(List ids); — user3110615, Dec 17 '13 at 20:05

score 2 · Answer 1 · answered Sep 01 '14 at 14:29

2

Looks like a BUG. See here: DATAJPA-433 and here: Bug 349477

answered Sep 01 '14 at 14:29

Platon

1,513
13
12

Is there a workaround to this? Can i write a JPQL query? – Sacky San Mar 18 '18 at 06:20

Error in SPRING JPA using eclipse link POSTGRESQL when executing findBy[NAME]In(List ids)

1 Answers1