Author: steve.ebersole(a)jboss.com Date: 2006-11-07 23:30:04 -0500 (Tue, 07 Nov 2006) New Revision: 10764 Modified: branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/Loader.java branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/hql/QueryLoader.java branches/Branch_3_2/Hibernate3/src/org/hibernate/param/ParameterSpecification.java branches/Branch_3_2/Hibernate3/src/org/hibernate/tool/hbm2ddl/SchemaExport.java branches/Branch_3_2/Hibernate3/test/org/hibernate/test/hql/ASTParserLoadingTest.java Log: hhh-2207 : mysql + limit + offset + parameters Modified: branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/Loader.java =================================================================== --- branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/Loader.java 2006-11-08 00:08:19 UTC (rev 10763) +++ branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/Loader.java 2006-11-08 04:30:04 UTC (rev 10764) @@ -1557,7 +1557,10 @@ col += bindLimitParameters( st, col, selection ); } - if ( !useLimit ) setMaxRows( st, selection ); + if ( !useLimit ) { + setMaxRows( st, selection ); + } + if ( selection != null ) { if ( selection.getTimeout() != null ) { st.setQueryTimeout( selection.getTimeout().intValue() ); @@ -1585,8 +1588,13 @@ } /** - * Some dialect-specific LIMIT clauses require the maximium last row number, - * others require the maximum returned row count. + * Some dialect-specific LIMIT clauses require the maximium last row number + * (aka, first_row_number + total_row_count), while others require the maximum + * returned row count (the total maximum number of rows to return). + * + * @param selection The selection criteria + * @param dialect The dialect + * @return The appropriate value to bind into the limit clause. */ private static int getMaxOrLimit(final RowSelection selection, final Dialect dialect) { final int firstRow = getFirstRow( selection ); @@ -1600,13 +1608,22 @@ } /** - * Bind parameters needed by the dialect-specific LIMIT clause + * Bind parameter values needed by the dialect-specific LIMIT clause. + * + * @param statement The statement to which to bind limit param values. + * @param index The bind position from which to start binding + * @param selection The selection object containing the limit information. + * @return The number of parameter values bound. + * @throws java.sql.SQLException Indicates problems binding parameter values. */ - private int bindLimitParameters(final PreparedStatement st, final int index, final RowSelection selection) - throws SQLException { - + private int bindLimitParameters( + final PreparedStatement statement, + final int index, + final RowSelection selection) throws SQLException { Dialect dialect = getFactory().getDialect(); - if ( !dialect.supportsVariableLimit() ) return 0; + if ( !dialect.supportsVariableLimit() ) { + return 0; + } if ( !hasMaxRows( selection ) ) { throw new AssertionFailure( "no max results set" ); } @@ -1614,62 +1631,99 @@ int lastRow = getMaxOrLimit( selection, dialect ); boolean hasFirstRow = firstRow > 0 && dialect.supportsLimitOffset(); boolean reverse = dialect.bindLimitParametersInReverseOrder(); - if ( hasFirstRow ) st.setInt( index + ( reverse ? 1 : 0 ), firstRow ); - st.setInt( index + ( reverse || !hasFirstRow ? 0 : 1 ), lastRow ); + if ( hasFirstRow ) { + statement.setInt( index + ( reverse ? 1 : 0 ), firstRow ); + } + statement.setInt( index + ( reverse || !hasFirstRow ? 0 : 1 ), lastRow ); return hasFirstRow ? 2 : 1; } /** * Use JDBC API to limit the number of rows returned by the SQL query if necessary */ - private void setMaxRows(final PreparedStatement st, final RowSelection selection) - throws SQLException { + private void setMaxRows( + final PreparedStatement st, + final RowSelection selection) throws SQLException { if ( hasMaxRows( selection ) ) { st.setMaxRows( selection.getMaxRows().intValue() + getFirstRow( selection ) ); } } + /** + * Bind all parameter values into the prepared statement in preparation + * for execution. + * + * @param statement The JDBC prepared statement + * @param queryParameters The encapsulation of the parameter values to be bound. + * @param startIndex The position from which to start binding parameter values. + * @param session The originating session. + * @return The number of JDBC bind positions actually bound during this method execution. + * @throws SQLException Indicates problems performing the binding. + */ protected int bindParameterValues( - PreparedStatement st, + PreparedStatement statement, QueryParameters queryParameters, - int col, + int startIndex, SessionImplementor session) throws SQLException { - col += bindPositionalParameters( st, queryParameters, col, session ); - col += bindNamedParameters( st, queryParameters.getNamedParameters(), col, session ); - return col; + int span = 0; + span += bindPositionalParameters( statement, queryParameters, startIndex, session ); + span += bindNamedParameters( statement, queryParameters.getNamedParameters(), startIndex + span, session ); + return span; } /** - * Bind positional parameter values to the <tt>PreparedStatement</tt> - * (these are parameters specified by a JDBC-style ?). + * Bind positional parameter values to the JDBC prepared statement. + * <p/> + * Postional parameters are those specified by JDBC-style ? parameters + * in the source query. It is (currently) expected that these come + * before any named parameters in the source query. + * + * @param statement The JDBC prepared statement + * @param queryParameters The encapsulation of the parameter values to be bound. + * @param startIndex The position from which to start binding parameter values. + * @param session The originating session. + * @return The number of JDBC bind positions actually bound during this method execution. + * @throws SQLException Indicates problems performing the binding. + * @throws org.hibernate.HibernateException Indicates problems delegating binding to the types. */ protected int bindPositionalParameters( - final PreparedStatement st, + final PreparedStatement statement, final QueryParameters queryParameters, - final int start, + final int startIndex, final SessionImplementor session) throws SQLException, HibernateException { - final Object[] values = queryParameters.getFilteredPositionalParameterValues(); final Type[] types = queryParameters.getFilteredPositionalParameterTypes(); int span = 0; for ( int i = 0; i < values.length; i++ ) { - types[i].nullSafeSet( st, values[i], start + span, session ); + types[i].nullSafeSet( statement, values[i], startIndex + span, session ); span += types[i].getColumnSpan( getFactory() ); } return span; } /** - * Bind named parameters to the <tt>PreparedStatement</tt>. This has an empty - * implementation on this superclass and should be implemented by subclasses - * (queries) which allow named parameters. + * Bind named parameters to the JDBC prepared statement. + * <p/> + * This is a generic implementation, the problem being that in the + * general case we do not know enough information about the named + * parameters to perform this in a complete manner here. Thus this + * is generally overridden on subclasses allowing named parameters to + * apply the specific behavior. The most usual limitation here is that + * we need to assume the type span is always one... + * + * @param statement The JDBC prepared statement + * @param namedParams A map of parameter names to values + * @param startIndex The position from which to start binding parameter values. + * @param session The originating session. + * @return The number of JDBC bind positions actually bound during this method execution. + * @throws SQLException Indicates problems performing the binding. + * @throws org.hibernate.HibernateException Indicates problems delegating binding to the types. */ - protected int bindNamedParameters(final PreparedStatement ps, - final Map namedParams, - final int start, - final SessionImplementor session) - throws SQLException, HibernateException { - + protected int bindNamedParameters( + final PreparedStatement statement, + final Map namedParams, + final int startIndex, + final SessionImplementor session) throws SQLException, HibernateException { if ( namedParams != null ) { // assumes that types are all of span 1 Iterator iter = namedParams.entrySet().iterator(); @@ -1684,10 +1738,10 @@ log.debug( "bindNamedParameters() " + typedval.getValue() + " -> " + name + - " [" + ( locs[i] + start ) + "]" + " [" + ( locs[i] + startIndex ) + "]" ); } - typedval.getType().nullSafeSet( ps, typedval.getValue(), locs[i] + start, session ); + typedval.getType().nullSafeSet( statement, typedval.getValue(), locs[i] + startIndex, session ); } result += locs.length; } Modified: branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/hql/QueryLoader.java =================================================================== --- branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/hql/QueryLoader.java 2006-11-08 00:08:19 UTC (rev 10763) +++ branches/Branch_3_2/Hibernate3/src/org/hibernate/loader/hql/QueryLoader.java 2006-11-08 04:30:04 UTC (rev 10764) @@ -478,21 +478,31 @@ return queryTranslator.getParameterTranslations().getNamedParameterSqlLocations( name ); } - + /** + * We specifically override this method here, because in general we know much more + * about the parameters and their appropriate bind positions here then we do in + * our super because we track them explciitly here through the ParameterSpecification + * interface. + * + * @param queryParameters The encapsulation of the parameter values to be bound. + * @param startIndex The position from which to start binding parameter values. + * @param session The originating session. + * @return The number of JDBC bind positions actually bound during this method execution. + * @throws SQLException Indicates problems performing the binding. + */ protected int bindParameterValues( - PreparedStatement st, - QueryParameters queryParameters, - int startPosition, - SessionImplementor session) throws SQLException { - int position = startPosition; - position = bindFilterParameterValues( st, queryParameters, position, session ); + final PreparedStatement statement, + final QueryParameters queryParameters, + final int startIndex, + final SessionImplementor session) throws SQLException { + int position = bindFilterParameterValues( statement, queryParameters, startIndex, session ); List parameterSpecs = queryTranslator.getSqlAST().getWalker().getParameters(); Iterator itr = parameterSpecs.iterator(); while ( itr.hasNext() ) { ParameterSpecification spec = ( ParameterSpecification ) itr.next(); - position += spec.bind( st, queryParameters, session, position ); + position += spec.bind( statement, queryParameters, session, position ); } - return position; + return position - startIndex; } private int bindFilterParameterValues( Modified: branches/Branch_3_2/Hibernate3/src/org/hibernate/param/ParameterSpecification.java =================================================================== --- branches/Branch_3_2/Hibernate3/src/org/hibernate/param/ParameterSpecification.java 2006-11-08 00:08:19 UTC (rev 10763) +++ branches/Branch_3_2/Hibernate3/src/org/hibernate/param/ParameterSpecification.java 2006-11-08 04:30:04 UTC (rev 10764) @@ -24,12 +24,29 @@ * @param position The position from which to start binding value(s). * * @return The number of sql bind positions "eaten" by this bind operation. + * @throws java.sql.SQLException Indicates problems performing the JDBC biind operation. */ public int bind(PreparedStatement statement, QueryParameters qp, SessionImplementor session, int position) throws SQLException; + /** + * Get the type which we are expeting for a bind into this parameter based + * on translated contextual information. + * + * @return The expected type. + */ public Type getExpectedType(); + /** + * Injects the expected type. Called during translation. + * + * @param expectedType The type to expect. + */ public void setExpectedType(Type expectedType); + /** + * Render this parameter into displayable info (for logging, etc). + * + * @return The displayable info. + */ public String renderDisplayInfo(); } Modified: branches/Branch_3_2/Hibernate3/src/org/hibernate/tool/hbm2ddl/SchemaExport.java =================================================================== --- branches/Branch_3_2/Hibernate3/src/org/hibernate/tool/hbm2ddl/SchemaExport.java 2006-11-08 00:08:19 UTC (rev 10763) +++ branches/Branch_3_2/Hibernate3/src/org/hibernate/tool/hbm2ddl/SchemaExport.java 2006-11-08 04:30:04 UTC (rev 10764) @@ -308,7 +308,7 @@ statement.executeUpdate( sql ); SQLWarning warnings = statement.getWarnings(); if ( warnings != null) { - JDBCExceptionReporter.logWarnings( warnings ); + JDBCExceptionReporter.logAndClearWarnings( connectionHelper.getConnection() ); } } Modified: branches/Branch_3_2/Hibernate3/test/org/hibernate/test/hql/ASTParserLoadingTest.java =================================================================== --- branches/Branch_3_2/Hibernate3/test/org/hibernate/test/hql/ASTParserLoadingTest.java 2006-11-08 00:08:19 UTC (rev 10763) +++ branches/Branch_3_2/Hibernate3/test/org/hibernate/test/hql/ASTParserLoadingTest.java 2006-11-08 04:30:04 UTC (rev 10764) @@ -90,7 +90,21 @@ cfg.setProperty( Environment.GENERATE_STATISTICS, "true" ); } + public void testSimpleSelectWithLimitAndOffset() throws Exception { + if ( ! ( getDialect().supportsLimit() && getDialect().supportsLimitOffset() ) ) { + reportSkip( "dialect does not support offset and limit combo", "limit and offset combination" ); + return; + } + // just checking correctness of param binding code... + Session session = openSession(); + session.createQuery( "from Animal" ) + .setFirstResult( 2 ) + .setMaxResults( 1 ) + .list(); + session.close(); + } + public void testJPAPositionalParameterList() { Session s = openSession(); s.beginTransaction();