WildFly 14 + Hibernate 5.3.6 + Named query resulting in empty list being returned
sergiu_pienar Sep 11, 2018 8:22 AMI'm trying to migrate an app from WildFly 13 (with Hibernate 5.1.14) to WildFly 14 + Hibernate 5.3.6.
In the process I've stumbled on a few problems, of which the latest is org.hibernate.Query returning an empty list when doing a query.
The same code works fine with WidlFly 13 + Hibernate 5.1.14:
Session sess = null; try { sess = hibernateSessionFactory.openSession(); org.hibernate.Query query1 = null; // build the query or get the right query by its name if (query.getQueryName() != null) { query1 = sess.getNamedQuery(query.getQueryName()); } else { throw new IllegalArgumentException("The query argument must provide HQL or query name!"); } // if first and max are -1 it means it is a COUNTER if (query.getMaxResults() == -1 && query.getFirstResult() == -1) { query1 = sess.createQuery(DAOHelper.getCountQuery(query1.getQueryString())); } else { // set first and max Results if (query.getFirstResult() != com.acme.persistence.Query.FIRST_RESULTS && query.getFirstResult() >= 0) { query1.setFirstResult(query.getFirstResult()); } if (query.getMaxResults() != com.acme.persistence.Query.MAX_RESULTS && query.getMaxResults() > 0) { query1.setMaxResults(query.getMaxResults()); } } // parameter set for (Parameter p : query.getParameters()) { try { if ((p.getType() == Parameter.TYPE_OBJECT) || p.getValue() == null) { query1.setParameter(p.getName(), p.getValue()); } else if (p.getType() == Parameter.TYPE_LONG) { query1.setLong(p.getName(), ((Long)p.getValue()).longValue()); } else if (p.getType() == Parameter.TYPE_STRING) { query1.setString(p.getName(), (String)p.getValue()); } else if (p.getType() == Parameter.TYPE_TIMESTAMP) { query1.setTimestamp(p.getName(), (Date)p.getValue()); } else if (p.getType() == Parameter.TYPE_LIST) { query1.setParameterList(p.getName(), (List)p.getValue()); } else if (p.getType() == Parameter.TYPE_BIGDECIMAL) { query1.setBigDecimal(p.getName(), (BigDecimal)p.getValue()); } } catch (QueryParameterException qpex) { DAOHelper.LOGGER.warn("Ignoring parameter '" + p.getName() + "' for the query '" + ((StringTools.isValidString(query.getQueryName())) ? query.getQueryName() : query.getSql()) + "' does not recognize this one"); } catch (IllegalArgumentException iaex) { DAOHelper.LOGGER.warn("Ignoring parameter '" + p.getName() + "' for the query '" + ((StringTools.isValidString(query.getQueryName())) ? query.getQueryName() : query.getSql()) + "' does not recognize this one"); } } return query1.list(); } finally { if (sess != null) { sess.close(); } }
I've enabled TRACE logging on both org.hibernate and com.arjuna and the relevant section is below (excerpt from the WildFly 13 server):
2018-09-11 13:36:27,575 TRACE [org.hibernate.engine.query.spi.QueryPlanCache] (default task-3) Located HQL query plan in cache (FROM com.acme.dao.client.ClientPreferences as cp WHERE cp.clientId = :clientId ORDER BY preferenceName ASC) 2018-09-11 13:36:27,575 TRACE [org.hibernate.engine.query.spi.QueryPlanCache] (default task-3) Located HQL query plan in cache (FROM com.acme.dao.client.ClientPreferences as cp WHERE cp.clientId = :clientId ORDER BY preferenceName ASC) 2018-09-11 13:36:27,575 TRACE [org.hibernate.engine.query.spi.HQLQueryPlan] (default task-3) Find: FROM com.acme.dao.client.ClientPreferences as cp WHERE cp.clientId = :clientId ORDER BY preferenceName ASC 2018-09-11 13:36:27,575 TRACE [org.hibernate.engine.spi.QueryParameters] (default task-3) Named parameters: {clientId=2} 2018-09-11 13:36:27,575 DEBUG [org.hibernate.SQL] (default task-3) select clientpref0_.preferenceId as preferen1_13_, clientpref0_.clientId as clientId2_13_, clientpref0_.preferenceName as preferen3_13_, clientpref0_.type as type4_13_, clientpref0_.description as descript5_13_, clientpref0_.ranges as ranges6_13_, clientpref0_.asText as asText7_13_, clientpref0_.asNumber as asNumber8_13_, clientpref0_.asDate as asDate9_13_, clientpref0_.editLevel as editLev10_13_ from ClientPreference_2 clientpref0_ where clientpref0_.clientId=? order by clientpref0_.preferenceName ASC 2018-09-11 13:36:27,575 TRACE [org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl] (default task-3) Registering statement [org.jboss.jca.adapters.jdbc.jdk8.WrappedPreparedStatementJDK8@49a4de96] 2018-09-11 13:36:27,575 TRACE [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl] (default task-3) Registering last query statement [org.jboss.jca.adapters.jdbc.jdk8.WrappedPreparedStatementJDK8@49a4de96] 2018-09-11 13:36:27,575 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (default task-3) binding parameter [1] as [BIGINT] - [2] 2018-09-11 13:36:27,575 TRACE [org.hibernate.loader.Loader] (default task-3) Bound [2] parameters total 2018-09-11 13:36:27,576 TRACE [org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl] (default task-3) Registering result set [com.mysql.jdbc.JDBC4ResultSet@7012c0be] 2018-09-11 13:36:27,576 TRACE [org.hibernate.loader.Loader] (default task-3) Processing result set 2018-09-11 13:36:27,576 DEBUG [org.hibernate.loader.Loader] (default task-3) Result set row: 0
The WildFly 14 logs are exactly the same but the result set is empty:
2018-09-11 13:39:10,921 TRACE [org.hibernate.engine.query.spi.QueryPlanCache] (default task-1) Located HQL query plan in cache (FROM com.acme.dao.client.ClientPreferences as cp WHERE cp.clientId = :clientId ORDER BY preferenceName ASC) 2018-09-11 13:39:10,921 TRACE [org.hibernate.engine.query.spi.QueryPlanCache] (default task-1) Located HQL query plan in cache (FROM com.acme.dao.client.ClientPreferences as cp WHERE cp.clientId = :clientId ORDER BY preferenceName ASC) 2018-09-11 13:39:10,921 TRACE [org.hibernate.engine.query.spi.HQLQueryPlan] (default task-1) Find: FROM com.acme.dao.client.ClientPreferences as cp WHERE cp.clientId = :clientId ORDER BY preferenceName ASC 2018-09-11 13:39:10,921 TRACE [org.hibernate.engine.spi.QueryParameters] (default task-1) Named parameters: {clientId=2} 2018-09-11 13:39:10,921 DEBUG [org.hibernate.SQL] (default task-1) select clientpref0_.preferenceId as preferen1_13_, clientpref0_.clientId as clientId2_13_, clientpref0_.preferenceName as preferen3_13_, clientpref0_.type as type4_13_, clientpref0_.description as descript5_13_, clientpref0_.ranges as ranges6_13_, clientpref0_.asText as asText7_13_, clientpref0_.asNumber as asNumber8_13_, clientpref0_.asDate as asDate9_13_, clientpref0_.editLevel as editLev10_13_ from ClientPreference_2 clientpref0_ where clientpref0_.clientId=? order by clientpref0_.preferenceName ASC 2018-09-11 13:39:10,921 TRACE [org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl] (default task-1) Registering statement [org.jboss.jca.adapters.jdbc.jdk8.WrappedPreparedStatementJDK8@511f6ce2] 2018-09-11 13:39:10,921 TRACE [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl] (default task-1) Registering last query statement [org.jboss.jca.adapters.jdbc.jdk8.WrappedPreparedStatementJDK8@511f6ce2] 2018-09-11 13:39:10,921 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (default task-1) binding parameter [1] as [BIGINT] - [2] 2018-09-11 13:39:10,921 TRACE [org.hibernate.loader.Loader] (default task-1) Bound [2] parameters total 2018-09-11 13:39:10,922 TRACE [org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl] (default task-1) Registering result set [com.mysql.jdbc.JDBC4ResultSet@20811fe1] 2018-09-11 13:39:10,922 TRACE [org.hibernate.loader.Loader] (default task-1) Processing result set 2018-09-11 13:39:10,922 TRACE [org.hibernate.loader.Loader] (default task-1) Done processing result set (0 rows)
The only difference between WF13 and WF14 is in hibernate's session factory where I set hibernate.allow_update_outside_transaction to true.