JPA Query with HQL and pageable create subselect with * return and generate alias error

Question

So I wanted to do a multiple object return from a JPA query that also allows pageable so that I can return only the first 10 it returns.

JPA: Query that returns multiple entities

The problem is that if I add pageable to the method defintion then it bundles the whole select statement in a select * from (original_query) where rownum <= size;. If I remove pageable it does the original_query without the subselect and works fine. But every time I add pageable and does the select * it will throw the following error because I have not aliased the return statements.

Exception in thread "main" org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:236) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:219) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) at com.sun.proxy.$Proxy82.retrieveQualifyingOffers(Unknown Source) at com.comcast.qe.dataload.RunApp.main(RunApp.java:35) Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) 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:2123) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1911) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887) at org.hibernate.loader.Loader.doQuery(Loader.java:932) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) at org.hibernate.loader.Loader.doList(Loader.java:2615) at org.hibernate.loader.Loader.doList(Loader.java:2598) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430) at org.hibernate.loader.Loader.list(Loader.java:2425) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1473) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1426) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398) at org.hibernate.Query.getResultList(Query.java:427) at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:119) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:83) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:476) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ... 8 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ... 38 more Blockquote

[UPDATE] I have found the code that causes the problem in . The sql comes in as the right select statement but then uses the oracle dialect to execute a sub query. Its in class org.hibernate.loader.Loader (http://docs.jboss.org/hibernate/orm/4.1/javadocs/index.html?org/hibernate/loader/Loader.html)

comes in as select blah_0.id, blah_0.peropty from table blah where property = value but then gets turned into select * from (select blah_0.id, blah_0.peropty from table blah where property = value) where rownum >= 10

    protected SqlStatementWrapper executeQueryStatement(
        String sqlStatement,
        QueryParameters queryParameters,
        boolean scroll,
        List<AfterLoadAction> afterLoadActions,
        SharedSessionContractImplementor session) throws SQLException {

    // Processing query filters.
    queryParameters.processFilters( sqlStatement, session );

    // Applying LIMIT clause.
    final LimitHandler limitHandler = getLimitHandler(
            queryParameters.getRowSelection()
    );
    String sql = limitHandler.processSql( queryParameters.getFilteredSQL(), queryParameters.getRowSelection() );

    // Adding locks and comments.
    sql = preprocessSQL( sql, queryParameters, getFactory().getDialect(), afterLoadActions );

    final PreparedStatement st = prepareQueryStatement( sql, queryParameters, limitHandler, scroll, session );
    return new SqlStatementWrapper(
            st, getResultSet(
            st,
            queryParameters.getRowSelection(),
            limitHandler,
            queryParameters.hasAutoDiscoverScalarTypes(),
            session
    )
    );
}

Maybe create a new dialect? I can't seem to do with the hibernate vendor adapter tho it just goes to oracle.

score 1 · Answer 1 · answered Apr 21 '17 at 20:51

1

I needed to set the MySQL5Dialect to generate the proper query. Always the simple config things.

    LocalContainerEntityManagerFactoryBean lcemfb = EntityManagerFactory(merlinDataSource(),
            new String[]{"packages.to.be.scaned"});
    Properties props = new Properties();
    props.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
    props.setProperty("hibernate.show_sql", String.valueOf(showQueies));
    lcemfb.setJpaProperties(props);

answered Apr 21 '17 at 20:51

Peter Howells

23
6

If you are using [spring-autoconfiguration](https://docs.spring.io/spring-boot/docs/current/reference/html/using-boot-auto-configuration.html) and have the right dependency to the database-driver in your classpath, spring will figure it out for you :) – Waize Oct 17 '18 at 06:03

score 0 · Answer 2 · answered Apr 21 '17 at 15:42

0

you can have your method return a List<> instead of Page<>, like that i believe it will avoid to fire the count query.

it will simply restricts the query to look up only the given range of entities

http://docs.spring.io/spring-data/jpa/docs/1.4.2.RELEASE/reference/html/repositories.html#repositories.special-parameters

answered Apr 21 '17 at 15:42

Zeromus

4,472
8
32
40

Still get the same exception, its the way it generates the sql, is there a way to force it to include the rownum constraint in the original query rather than encapsulate it in a subselect – Peter Howells Apr 21 '17 at 17:19

JPA Query with HQL and pageable create subselect with * return and generate alias error

2 Answers2