PSQLException syntax error when using values within an 'in' clause in Hibernate
Issue
- Executing an HQL / JPA query
SELECT empl FROM Employee empl WHERE (empl.id) IN (VALUES (?1), (?2)) -
The log records the below failure detail:
... WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/127.0.0.1:8180-5) SQL Error: 0, SQLState: 42601 ... ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/127.0.0.1:8180-5) ERROR: syntax error at or near "$2" Position: ... ... ERROR [org.jboss.as.ejb3] (http-/127.0.0.1:8180-5) JBAS014268: Failure in caller transaction.: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet ... Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88) at org.hibernate.loader.Loader.getResultSet(Loader.java:2062) ... at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268) ... Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2" Position: ... at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:304) at sun.reflect.GeneratedMethodAccessor225.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java) at java.lang.reflect.Method.invoke(Method.java:498) at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453) at com.sun.proxy.$Proxy213.executeQuery(Unknown Source) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) ...
Environment
- Red Hat JBoss Enterprise Application Platform (EAP) 6
- Hibernate 4
- PostgreSQL
Subscriber exclusive content
A Red Hat subscription provides unlimited access to our knowledgebase, tools, and much more.