Hibernate and Postgres positional parameteres mismatch

Question

I am currently maintaining a legacy application which uses some old technologies: Hibernate 3.2, Spring 2.5 and the like.

I've been fighting the last days with an exception. I've managed to isolate a simple example:

private void test(String username) {
    String sql = "from org.ojade.aas.authentication.model.User u " +
                 "where u.aasPrincipalName = ?";
    Session session = sessionFactory.getCurrentSession();
    QueryImpl query = (QueryImpl) session.createQuery(sql);
    query.setParameter(0, username);
    List list = query.list();
    log.debug("list = {}", list);
}

The execution of this code throws an exception when query.list() is executed.

Caused by: org.postgresql.util.PSQLException: No value specified for parameter 2.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:102)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:166)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1791)
    at org.hibernate.loader.Loader.doQuery(Loader.java:674)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2217)
    ... 120 more

There is no parameter 2. If I change the query to use named parameters it works, but I can't change the original code (is part of a compiled library).

I've set Hibernate to show me the SQL. It is executing this:

SELECT
    user0_.ID_PRINCIPAL AS ID1_3_,
    user0_.VERSION      AS VERSION3_,
    user0_.NO_PRINCIPAL AS NO4_3_,
    user0_.IN_BLOQUEO   AS IN5_3_,
    user0_.IN_ACTIVO    AS IN6_3_
FROM JAAS_PRINCIPAL user0_
WHERE user0_.TT_DISCRIMINANTE = 'USER' AND user0_.NO_PRINCIPAL =?

Which seems ok.

Any idea what the problem may be?

Looks like this could be related stackoverflow.com/questions/7447922/… although I'm not quite sure what's at fault here. The workaround provided is "remove unique constraint", but I doubt that's a workable general alternative. Which driver version are you using? — Kayaman
– Kayaman, Commented Oct 26, 2017 at 11:17
This seems very related as well postgresql.org/message-id/… — Kayaman
– Kayaman, Commented Oct 26, 2017 at 11:19
Using 8.0-312.jdbc3 driver, which is old. I've tried to upgrade it but newer drivers break the app. Your link is helpful, thanks. — sargue
– sargue, Commented Oct 26, 2017 at 11:20
Nailed it @Kayaman. Disabling comments (hibernate.use_sql_comments) fixes the problem. Feel free to write the answer if you want some points. :-) — sargue
– sargue, Commented Oct 26, 2017 at 11:25

Kayaman · Accepted Answer · 2017-10-26 11:35:36Z

1

This old bug reported against the postgresql-8.1-405.jdbc3.jar driver informs that if you have /* */ comments including a ? in the query, the parser won't understand that it's not a placeholder, but just part of the comment. Apparently other characters such as dollar signs also caused problems.

As given in the original bug report, this can be solved with named parameters, fixing comments (in this case the comments were generated by Hibernate, so turning off Hibernate comments with property hibernate.use_sql_comments=false fixes it) or by upgrading the driver to handle it more gracefully (no idea which version it was fixed though).

Quite a catch to come across this in 2017! :)

answered Oct 26, 2017 at 11:35

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

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

Collectives™ on Stack Overflow

Hibernate and Postgres positional parameteres mismatch

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related