Examples with RowSelection org.hibernate.engine.spi.RowSelection used on opensource projects

Example 6 with RowSelection

use of org.hibernate.engine.spi.RowSelection in project midpoint by Evolveum.

the class SqlAuditServiceImpl method selectRecordsByMaxAge.

private int selectRecordsByMaxAge(Session session, String tempTable, Date minValue, Dialect dialect) {
    // fill temporary table, we don't need to join task on object on
    // container, oid and id is already in task table
    StringBuilder selectSB = new StringBuilder();
    selectSB.append("select a.id as id from ").append(RAuditEventRecord.TABLE_NAME).append(" a");
    selectSB.append(" where a.").append(RAuditEventRecord.COLUMN_TIMESTAMP).append(" < ###TIME###");
    String selectString = selectSB.toString();
    // batch size
    RowSelection rowSelection = new RowSelection();
    rowSelection.setMaxRows(CLEANUP_AUDIT_BATCH_SIZE);
    LimitHandler limitHandler = dialect.buildLimitHandler(selectString, rowSelection);
    selectString = limitHandler.getProcessedSql();
    // replace ? -> batch size, $ -> ?
    // Sorry for that .... I just don't know how to write this query in HQL,
    // nor I'm not sure if limiting max size in
    // compound insert into ... select ... query via query.setMaxSize()
    // would work - TODO write more nicely if anybody knows how)
    selectString = selectString.replace("?", String.valueOf(CLEANUP_AUDIT_BATCH_SIZE));
    selectString = selectString.replace("###TIME###", "?");
    String queryString = "insert into " + tempTable + " " + selectString;
    LOGGER.trace("Query string = {}", queryString);
    SQLQuery query = session.createSQLQuery(queryString);
    query.setParameter(0, new Timestamp(minValue.getTime()));
    return query.executeUpdate();
}

Example 7 with RowSelection

use of org.hibernate.engine.spi.RowSelection in project hibernate-orm by hibernate.

the class Loader method prepareQueryStatement.

/**
 * Obtain a <tt>PreparedStatement</tt> with all parameters pre-bound.
 * Bind JDBC-style <tt>?</tt> parameters, named parameters, and
 * limit parameters.
 */
protected final PreparedStatement prepareQueryStatement(String sql, final QueryParameters queryParameters, final LimitHandler limitHandler, final boolean scroll, final SharedSessionContractImplementor session) throws SQLException, HibernateException {
    final Dialect dialect = getFactory().getDialect();
    final RowSelection selection = queryParameters.getRowSelection();
    final boolean useLimit = LimitHelper.useLimit(limitHandler, selection);
    final boolean hasFirstRow = LimitHelper.hasFirstRow(selection);
    final boolean useLimitOffset = hasFirstRow && useLimit && limitHandler.supportsLimitOffset();
    final boolean callable = queryParameters.isCallable();
    final ScrollMode scrollMode = getScrollMode(scroll, hasFirstRow, useLimitOffset, queryParameters);
    PreparedStatement st = session.getJdbcCoordinator().getStatementPreparer().prepareQueryStatement(sql, callable, scrollMode);
    try {
        int col = 1;
        // TODO: can we limit stored procedures ?!
        col += limitHandler.bindLimitParametersAtStartOfQuery(selection, st, col);
        if (callable) {
            col = dialect.registerResultSetOutParameter((CallableStatement) st, col);
        }
        col += bindParameterValues(st, queryParameters, col, session);
        col += limitHandler.bindLimitParametersAtEndOfQuery(selection, st, col);
        limitHandler.setMaxRows(selection, st);
        if (selection != null) {
            if (selection.getTimeout() != null) {
                st.setQueryTimeout(selection.getTimeout());
            }
            if (selection.getFetchSize() != null) {
                st.setFetchSize(selection.getFetchSize());
            }
        }
        // handle lock timeout...
        LockOptions lockOptions = queryParameters.getLockOptions();
        if (lockOptions != null) {
            if (lockOptions.getTimeOut() != LockOptions.WAIT_FOREVER) {
                if (!dialect.supportsLockTimeouts()) {
                    if (LOG.isDebugEnabled()) {
                        LOG.debugf("Lock timeout [%s] requested but dialect reported to not support lock timeouts", lockOptions.getTimeOut());
                    }
                } else if (dialect.isLockTimeoutParameterized()) {
                    st.setInt(col++, lockOptions.getTimeOut());
                }
            }
        }
        if (LOG.isTraceEnabled()) {
            LOG.tracev("Bound [{0}] parameters total", col);
        }
    } catch (SQLException | HibernateException e) {
        session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release(st);
        session.getJdbcCoordinator().afterStatementExecution();
        throw e;
    }
    return st;
}

Example 8 with RowSelection

use of org.hibernate.engine.spi.RowSelection in project hibernate-orm by hibernate.

the class SQLServer2005DialectTestCase method testGetLimitStringUsingCTEQueryWithOffset.

@Test
@TestForIssue(jiraKey = "HHH-8916")
public void testGetLimitStringUsingCTEQueryWithOffset() {
    RowSelection selection = toRowSelection(1, 5);
    // test non-top based CTE with single CTE query definition with no odd formatting
    final String query1 = "WITH a (c1, c2) AS (SELECT c1, c2 FROM t) SELECT c1, c2 FROM a";
    assertEquals("WITH a (c1, c2) AS (SELECT c1, c2 FROM t), query AS (SELECT inner_query.*, ROW_NUMBER() OVER " + "(ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM (  SELECT c1 as page0_, c2 as page1_ " + "FROM a ) inner_query ) SELECT page0_, page1_ FROM query WHERE __hibernate_row_nr__ >= ? " + "AND __hibernate_row_nr__ < ?", dialect.getLimitHandler().processSql(query1, selection));
    // test non-top based CTE with single CTE query definition and various tab, newline spaces
    final String query2 = "  \n\tWITH a (c1\n\t,c2)\t\nAS (SELECT\n\tc1,c2 FROM t)\t\nSELECT c1, c2 FROM a";
    assertEquals("  \n\tWITH a (c1\n\t,c2)\t\nAS (SELECT\n\tc1,c2 FROM t), query AS (SELECT inner_query.*, ROW_NUMBER()" + " OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( \t\nSELECT c1 as page0_, c2 " + "as page1_ FROM a ) inner_query ) SELECT page0_, page1_ FROM query WHERE __hibernate_row_nr__ >= " + "? AND __hibernate_row_nr__ < ?", dialect.getLimitHandler().processSql(query2, selection));
    // test non-top based CTE with multiple CTE query definitions with no odd formatting
    final String query3 = "WITH a (c1, c2) AS (SELECT c1, c2 FROM t1), b (b1, b2) AS (SELECT b1, b2 FROM t2) " + " SELECT c1, c2, b1, b2 FROM t1, t2 WHERE t1.c1 = t2.b1";
    assertEquals("WITH a (c1, c2) AS (SELECT c1, c2 FROM t1), b (b1, b2) AS (SELECT b1, b2 FROM t2), query AS (" + "SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM (" + "   SELECT c1 as page0_, c2 as page1_, b1 as page2_, b2 as page3_ FROM t1, t2 WHERE t1.c1 = t2.b1 ) inner_query )" + " SELECT page0_, page1_, page2_, page3_ FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?", dialect.getLimitHandler().processSql(query3, selection));
    // test top-based CTE with multiple CTE query definitions and various tab, newline spaces
    final String query4 = "  \n\r\tWITH a (c1, c2) AS\n\r (SELECT c1, c2 FROM t1)\n\r, b (b1, b2)\tAS\t(SELECT b1, " + "b2 FROM t2)    SELECT c1, c2, b1, b2 FROM t1, t2 WHERE t1.c1 = t2.b1";
    assertEquals("  \n\r\tWITH a (c1, c2) AS\n\r (SELECT c1, c2 FROM t1)\n\r, b (b1, b2)\tAS\t(SELECT b1, b2 FROM t2), query AS (" + "SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM (" + "     SELECT c1 as page0_, c2 as page1_, b1 as page2_, b2 as page3_ FROM t1, t2 WHERE t1.c1 = t2.b1 ) inner_query )" + " SELECT page0_, page1_, page2_, page3_ FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?", dialect.getLimitHandler().processSql(query4, selection));
}

Example 9 with RowSelection

use of org.hibernate.engine.spi.RowSelection in project hibernate-orm by hibernate.

the class DB2DialectTestCase method testIntegerOverflowForMaxResults.

@Test
@TestForIssue(jiraKey = "HHH-12369")
public void testIntegerOverflowForMaxResults() {
    RowSelection rowSelection = new RowSelection();
    rowSelection.setFirstRow(1);
    rowSelection.setMaxRows(Integer.MAX_VALUE);
    String sql = dialect.getLimitHandler().processSql("select a.id from tbl_a a order by a.id", rowSelection);
    assertTrue("Integer overflow for max rows in: " + sql, sql.contains("fetch first 2147483647 rows only"));
}

Example 10 with RowSelection

use of org.hibernate.engine.spi.RowSelection in project hibernate-orm by hibernate.

the class QueryTranslatorImpl method list.

@Override
public List list(SharedSessionContractImplementor session, QueryParameters queryParameters) throws HibernateException {
    // Delegate to the QueryLoader...
    errorIfDML();
    final QueryNode query = (QueryNode) sqlAst;
    final boolean hasLimit = queryParameters.getRowSelection() != null && queryParameters.getRowSelection().definesLimits();
    final boolean needsDistincting = (query.getSelectClause().isDistinct() || getEntityGraphQueryHint() != null || hasLimit) && containsCollectionFetches();
    QueryParameters queryParametersToUse;
    if (hasLimit && containsCollectionFetches()) {
        boolean fail = session.getFactory().getSessionFactoryOptions().isFailOnPaginationOverCollectionFetchEnabled();
        if (fail) {
            throw new HibernateException("firstResult/maxResults specified with collection fetch. " + "In memory pagination was about to be applied. " + "Failing because 'Fail on pagination over collection fetch' is enabled.");
        } else {
            LOG.firstOrMaxResultsSpecifiedWithCollectionFetch();
        }
        RowSelection selection = new RowSelection();
        selection.setFetchSize(queryParameters.getRowSelection().getFetchSize());
        selection.setTimeout(queryParameters.getRowSelection().getTimeout());
        queryParametersToUse = queryParameters.createCopyUsing(selection);
    } else {
        queryParametersToUse = queryParameters;
    }
    List results = queryLoader.list(session, queryParametersToUse);
    if (needsDistincting) {
        int includedCount = -1;
        // NOTE : firstRow is zero-based
        int first = !hasLimit || queryParameters.getRowSelection().getFirstRow() == null ? 0 : queryParameters.getRowSelection().getFirstRow();
        int max = !hasLimit || queryParameters.getRowSelection().getMaxRows() == null ? -1 : queryParameters.getRowSelection().getMaxRows();
        List tmp = new ArrayList();
        IdentitySet distinction = new IdentitySet();
        for (final Object result : results) {
            if (!distinction.add(result)) {
                continue;
            }
            includedCount++;
            if (includedCount < first) {
                continue;
            }
            tmp.add(result);
            // NOTE : ( max - 1 ) because first is zero-based while max is not...
            if (max >= 0 && (includedCount - first) >= (max - 1)) {
                break;
            }
        }
        results = tmp;
    }
    return results;
}