Translating HQL Query to executable SQL Query with list parameter

Question

I am writing a util function to get the total record count based on any HQL that I get passed in without loading all data. The passed in HQL might be pretty complex with lots of selects, joins, where conditions, groupings and sortings. For that I want to wrap the query with a SELECT COUNT(*) FROM (<ORIGINAL_QUERY>) x. I found out, that this is not possible with HQL, because Hibernate does not allow subqueries in FROM elements. Now, I am trying to translate this random HQL query which might have some named parameters (some of them might be simple parameters, some might be lists) to an executable SQL statement without inlining the parameter values. This seems to work with simple parameters, but does not work with list parameters.

The hqlString and the namedParameterMap comes from somewhere outside:

final String hqlString = "...";
final Map<String, Object> namedParametersMap = ...;

Code to translate HQL to SQL (and ParameterTranslations:

final QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
final SessionFactoryImplementor factory = getSessionFactory();

final QueryTranslator translator = translatorFactory.createQueryTranslator(hqlString, hqlString, Collections.EMPTY_MAP, factory, null);
translator.compile(Collections.EMPTY_MAP, false);

final String sqlString = translator.getSQLString();
final ParameterTranslations parameterTranslations = translator.getParameterTranslations());

Code to create a SQLQuery, set the Parameters and execute the Query:

SQLQuery sqlQuery = getCurrentSession().createSQLQuery(sqlString);

((Set<?>) parameterTranslations.getNamedParameterNames()).forEach(parameterName -> {
    for (int position : parameterTranslations.getNamedParameterSqlLocations(parameterName)) {
        sqlQuery.setParameter(position, namedParametersMap.get(parameterName));
    }
});

final Long rowCount = ((BigInteger) query.uniqueResult()).longValueExact();

This query works an expected:

final String hqlString = "SELECT p.firstname, p.surname FROM Person p WHERE p.id = :param1";
final Map<String, Object> namedParametersMap = Maps.newHashMap(ImmutableMap.<String, Object>of("param1", Integer.valueOf(1));

This query does not work:

String hqlString = "SELECT p.firstname, p.surname FROM Person p WHERE p.id IN (:param1)";
final Map<String, Object> namedParametersMap = Maps.newHashMap(ImmutableMap.<String, Object>of("param1", Lists.newArrayList(Integer.valueOf(1)));

Exception:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) at org.hibernate.loader.Loader.getResultSet(Loader.java:2313) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2096) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2072) at org.hibernate.loader.Loader.doQuery(Loader.java:941) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:352) at org.hibernate.loader.Loader.doList(Loader.java:2813) at org.hibernate.loader.Loader.doList(Loader.java:2796) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2625) at org.hibernate.loader.Loader.list(Loader.java:2620) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:322) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1996) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966) at HQLQueryUtils.getCount(HQLQueryUtils.java:350)

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 301 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ... 49 more

Environment:

Java 8

Hibernate 5.1.8

Postgres-JDBC 42.2.2

PostgreSQL Server 10.5

score 0 · Answer 1 · answered Aug 13 '18 at 07:17

0

In HQL you can use query parameter and set Collection with setParameterList method.

Query q = session.createQuery("SELECT entity FROM Entity entity WHERE name IN (:names)");
q.setParameterList("names", names);

answered Aug 13 '18 at 07:17

Angad Bansode

619
4
13

This works for HQL queries, but not for SQL queries as stated in the question. The translation removes all named parameters and converts them to positional parameters. The `org.hibernate.SQLQuery` Interface does not have a `setParameterList()` method, that works for positional parameters. – PMcL Aug 13 '18 at 09:55

score 0 · Answer 2 · answered Aug 13 '18 at 07:22

You can use direct sql

     String sql="your query"
     Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
                query.setParameter("paramterName", parameterValue);
                List<Object[]> resultSet = query.list();

     List<YouClass > data= new ArrayList<>();
          for (Object[] row : resultSet) {
                YouClass yourObject=new YouClass ();
                yourObject.setDate((Date) row[0]);
                yourObject.setAmount((BigDecimal) row[1]);
                data.add(yourObject);
            }

Suppose Yourclass has two element date and amount

The translation removes all named parameters and converts them to positional parameters. The `org.hibernate.Query` Interface does not have a `setParameterList()` method, that works for positional parameters. — PMcL, Aug 13 '18 at 09:56

Translating HQL Query to executable SQL Query with list parameter

2 Answers2