Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Error using setMaxResults in 5.2.5
PostPosted: Thu Dec 15, 2016 10:33 am 
Newbie

Joined: Fri Dec 09, 2016 5:27 pm
Posts: 8
I am upgrading to 5.2.5 from 4.2.6 and I'm seeing an error in all of my Criteria queries that set a max row count using setMaxResults().
I understand that the Criteria API has been deprecated, but I am doing this work on a product that has no developers assigned to it (for various business reasons). Re-writing the queries we do use would involve more time & effort than management would like us to spend.
I'm looking for suggestions for a simple workaround, as this does look like a bug in Hibernate. I've read many of the posts describing the JPA query API, but using that at this point would involve significant re-factoring.

The code is:
Code:
        Criteria criteria = session.createCriteria(CertRequest.class);
        List<CertRequest> certRequests = (List<CertRequest>)criteria
          .setFirstResult(0)
          .setMaxResults(BATCH_SIZE)
          .addOrder(Order.asc("modifyTime"))
          .list();


This generates the following SQL using Hibernate 5.2.5:
Quote:
/* criteria query */ select
TOP ? this_.id as id1_25_0_,
this_.revision as revision2_25_0_,
this_.cert as cert3_25_0_,
this_.csr as csr4_25_0_,
this_.expirationDate as expirati5_25_0_,
this_.modifyTime as modifyTi6_25_0_,
from
dbo.CertRequest this_
order by
this_.modifyTime asc


The same code generates the following query using Hibernate 4.2.6 (the max results argument is embedded in the SQL statement):
Quote:
Hibernate:
/* criteria query */ select
top 101 this_.id as id1_22_0_,
this_.revision as revision2_22_0_,
this_.cert as cert3_22_0_,
this_.csr as csr4_22_0_,
this_.expirationDate as expirati5_22_0_,
this_.modifyTime as modifyTi6_22_0_,
from
CertRequest this_
order by
this_.modifyTime asc


The error is: incorrect syntax near '@P0' - the stack trace is below:
Quote:
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: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.criteria.CriteriaLoader.list(CriteriaLoader.java:109)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1878)
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:365)
at com.XXX.mgmt.testing.TestPeD.testCriteria(TestPeD.java:113)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2444)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
... 15 more


Top
 Profile  
 
 Post subject: Re: Error using setMaxResults in 5.2.5
PostPosted: Thu Dec 15, 2016 11:14 am 
Newbie

Joined: Fri Dec 09, 2016 5:27 pm
Posts: 8
After some research I have written my own LimitHandler to fix this problem.
Below is my code - I've commented out the lines I changed.

Code:
        // Method copied from TopLimitHandler
        @Override
        public String processSql(String sql, RowSelection selection) {
            if (LimitHelper.hasFirstRow( selection )) {
                throw new UnsupportedOperationException( "query result offset is not supported" );
            }

            final int selectIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select" );
            final int selectDistinctIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select distinct" );
            final int insertionPoint = selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
            Integer maxRows = selection.getMaxRows();
            return new StringBuilder( sql.length() + 8 )
                    .append( sql )
//                    .insert( insertionPoint, " TOP ? " )
                    .insert( insertionPoint, " TOP " + maxRows.toString() + " ")
                    .toString();
        }

        // Method copied from AbstractLimitHandler
        @Override
        public int bindLimitParametersAtStartOfQuery(RowSelection selection, PreparedStatement statement, int index)
                throws SQLException {
            return bindLimitParametersFirst() ? bindXxxLimitParameters( selection, statement, index ) : 0;
        }

        // Method copied from AbstractLimitHandler
        @Override
        public int bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index)
                throws SQLException {
            return !bindLimitParametersFirst() ? bindXxxLimitParameters( selection, statement, index ) : 0;
        }
        /**
         * Default implementation of binding parameter values needed by the LIMIT clause.
         *
         * @param selection the selection criteria for rows.
         * @param statement Statement to which to bind limit parameter values.
         * @param index Index from which to start binding.
         * @return The number of parameter values bound.
         * @throws SQLException Indicates problems binding parameter values.
         */
        protected final int bindXxxLimitParameters(RowSelection selection, PreparedStatement statement, int index)
                throws SQLException {
            if ( !supportsVariableLimit() || !LimitHelper.hasMaxRows( selection ) ) {
                return 0;
            }
            final int firstRow = convertToFirstRowValue( LimitHelper.getFirstRow( selection ) );
//            final int lastRow = getMaxOrLimit( selection );
            final boolean hasFirstRow = supportsLimitOffset() && ( firstRow > 0 || forceLimitUsage() );
            final boolean reverse = bindLimitParametersInReverseOrder();
            if ( hasFirstRow ) {
                statement.setInt( index + ( reverse ? 1 : 0 ), firstRow );
            }
//            statement.setInt( index + ( reverse || !hasFirstRow ? 0 : 1 ), lastRow );
            return hasFirstRow ? 2 : 1;
        }


Last edited by pdonahue on Thu Dec 15, 2016 11:40 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Error using setMaxResults in 5.2.5
PostPosted: Thu Dec 15, 2016 11:21 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1582
Location: Romania
If there is some issue, please open a Jira issue. Thanks.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Error using setMaxResults in 5.2.5
PostPosted: Tue Mar 21, 2017 11:18 am 
Newbie

Joined: Tue Mar 21, 2017 11:16 am
Posts: 2
Sorry for beeing reopening this topic, but how do you use this custom LimitHandler? How hibernate identify it?

pdonahue wrote:
After some research I have written my own LimitHandler to fix this problem.
Below is my code - I've commented out the lines I changed.

Code:
        // Method copied from TopLimitHandler
        @Override
        public String processSql(String sql, RowSelection selection) {
            if (LimitHelper.hasFirstRow( selection )) {
                throw new UnsupportedOperationException( "query result offset is not supported" );
            }

            final int selectIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select" );
            final int selectDistinctIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select distinct" );
            final int insertionPoint = selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
            Integer maxRows = selection.getMaxRows();
            return new StringBuilder( sql.length() + 8 )
                    .append( sql )
//                    .insert( insertionPoint, " TOP ? " )
                    .insert( insertionPoint, " TOP " + maxRows.toString() + " ")
                    .toString();
        }

        // Method copied from AbstractLimitHandler
        @Override
        public int bindLimitParametersAtStartOfQuery(RowSelection selection, PreparedStatement statement, int index)
                throws SQLException {
            return bindLimitParametersFirst() ? bindXxxLimitParameters( selection, statement, index ) : 0;
        }

        // Method copied from AbstractLimitHandler
        @Override
        public int bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index)
                throws SQLException {
            return !bindLimitParametersFirst() ? bindXxxLimitParameters( selection, statement, index ) : 0;
        }
        /**
         * Default implementation of binding parameter values needed by the LIMIT clause.
         *
         * @param selection the selection criteria for rows.
         * @param statement Statement to which to bind limit parameter values.
         * @param index Index from which to start binding.
         * @return The number of parameter values bound.
         * @throws SQLException Indicates problems binding parameter values.
         */
        protected final int bindXxxLimitParameters(RowSelection selection, PreparedStatement statement, int index)
                throws SQLException {
            if ( !supportsVariableLimit() || !LimitHelper.hasMaxRows( selection ) ) {
                return 0;
            }
            final int firstRow = convertToFirstRowValue( LimitHelper.getFirstRow( selection ) );
//            final int lastRow = getMaxOrLimit( selection );
            final boolean hasFirstRow = supportsLimitOffset() && ( firstRow > 0 || forceLimitUsage() );
            final boolean reverse = bindLimitParametersInReverseOrder();
            if ( hasFirstRow ) {
                statement.setInt( index + ( reverse ? 1 : 0 ), firstRow );
            }
//            statement.setInt( index + ( reverse || !hasFirstRow ? 0 : 1 ), lastRow );
            return hasFirstRow ? 2 : 1;
        }


Top
 Profile  
 
 Post subject: Re: Error using setMaxResults in 5.2.5
PostPosted: Tue Mar 21, 2017 11:27 am 
Newbie

Joined: Tue Mar 21, 2017 11:16 am
Posts: 2
Where did you referenced the custom LimitHandler to hibernate identify it?


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.