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();
}

If you print your "studentIds" list what do you get? are you sure it isn't wrapping an additional ")" inside the SQL? — d1ll1nger
– d1ll1nger, Commented Aug 10, 2016 at 15:56
Shouldn't you use something like id = ANY(:studentIds) since you are providing an array — Nick
– Nick, Commented Aug 10, 2016 at 16:13
Changing SQLQuery query = .... to Query query = .... made the error go away. Anybody know why? — ShoibAhamed
– ShoibAhamed, Commented Aug 10, 2016 at 22:44

fyrkov · Accepted Answer · 2019-10-18 13:39:13Z

13

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

select 
...
where
    students.id in (

    )

answered Oct 18, 2019 at 13:39

fyrkov

2,9053 gold badges23 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dzmitry Hubin · Accepted Answer · 2020-03-24 09:58:58Z

1

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

answered Mar 24, 2020 at 9:58

Dzmitry Hubin

1,38114 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related