org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

Question

this is the DAO method i have to retrieve the list of students from DB and on RunTime it says -

org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

which is driving me crazy. Can anyone please tell me what i might have missed?

public List<Student> getStudentsByIds(List<Integer> studentIds) {
    Session session = SessionFactoryUtils.getSession(getSessionFactory(), true);
    SQLQuery query = session.createSQLQuery("SELECT * FROM students s WHERE s.id IN (:studentIds)");
    query.setParameterList("studentIds", studentIds);
    return query.list();
}

Answer 1

Probably studentIds is an empty list and postgres does not accept empty IN clause in generated code

select 
...
where
    students.id in (

    )

Answer 2

Had this issue recently. So as I got from docs psql can't handle empty lists in IN/NOT IN (:someEmptyList) statements cause criteriaBuilder adds empty curly braces into final sql query: (in ())

Imho the easiest solution will be just to check for empty list and return empty result list back.

Or use more advanced tools if u have complex query and u really need it => https://marcelothebuilder.github.io/java/jpa/2017/09/11/jpa-post.html