Example usage for java.sql PreparedStatement setFetchSize

List of usage examples for java.sql PreparedStatement setFetchSize

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setFetchSize

Introduction

In this page you can find the example usage for java.sql PreparedStatement setFetchSize.

Prototype

void setFetchSize(int rows) throws SQLException;

Source Link

Document

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement.

Usage

From source file:org.seasar.dbflute.s2dao.jdbc.TnStatementFactoryImpl.java

protected void doReflectStatementOptions(PreparedStatement ps, StatementConfig actualConfig) {
    if (actualConfig == null || !actualConfig.hasStatementOptions()) {
        return;/*  w ww  .j ava  2 s  . c  o m*/
    }
    try {
        if (actualConfig.hasQueryTimeout()) {
            final Integer queryTimeout = actualConfig.getQueryTimeout();
            if (isInternalDebugEnabled()) {
                _log.debug("...Setting queryTimeout of statement: " + queryTimeout);
            }
            ps.setQueryTimeout(queryTimeout);
        }
        if (actualConfig.hasFetchSize()) {
            final Integer fetchSize = actualConfig.getFetchSize();
            if (isInternalDebugEnabled()) {
                _log.debug("...Setting fetchSize of statement: " + fetchSize);
            }
            ps.setFetchSize(fetchSize);
        }
        if (actualConfig.hasMaxRows()) {
            final Integer maxRows = actualConfig.getMaxRows();
            if (isInternalDebugEnabled()) {
                _log.debug("...Setting maxRows of statement: " + maxRows);
            }
            ps.setMaxRows(maxRows);
        }
    } catch (SQLException e) {
        handleSQLException(e, ps);
    }
}

From source file:org.sonar.core.measure.MeasureFilterSql.java

List<MeasureFilterRow> execute(Connection connection) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setFetchSize(FETCH_SIZE);
    ResultSet rs = null;/*from  w  w  w .  j a va2s  .  c  o  m*/
    try {
        for (int index = 0; index < dateParameters.size(); index++) {
            statement.setDate(index + 1, dateParameters.get(index));
        }
        rs = statement.executeQuery();
        return process(rs);

    } finally {
        DatabaseUtils.closeQuietly(rs);
        DatabaseUtils.closeQuietly(statement);
    }
}

From source file:org.sonar.db.version.SelectImpl.java

static SelectImpl create(Database db, Connection connection, String sql) throws SQLException {
    // TODO use DbClient#newScrollingSelectStatement()
    PreparedStatement pstmt = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    pstmt.setFetchSize(db.getDialect().getScrollDefaultFetchSize());
    return new SelectImpl(pstmt);
}

From source file:org.sonar.server.db.migrations.SelectImpl.java

static SelectImpl create(Database db, Connection connection, String sql) throws SQLException {
    PreparedStatement pstmt = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    pstmt.setFetchSize(1000);
    return new SelectImpl(pstmt);
}

From source file:org.sonar.server.platform.db.migration.step.SelectImpl.java

public static SelectImpl create(Database db, Connection connection, String sql) throws SQLException {
    // TODO use DbClient#newScrollingSelectStatement()
    PreparedStatement pstmt = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    pstmt.setFetchSize(db.getDialect().getScrollDefaultFetchSize());
    return new SelectImpl(pstmt);
}

From source file:org.springframework.batch.item.database.AbstractCursorItemReader.java

/**
 * Prepare the given JDBC Statement (or PreparedStatement or
 * CallableStatement), applying statement settings such as fetch size, max
 * rows, and query timeout. @param stmt the JDBC Statement to prepare
 *
 * @param stmt {@link java.sql.PreparedStatement} to be configured
 *
 * @throws SQLException if interactions with provided stmt fail
 *
 * @see #setFetchSize//w ww .  j a v  a 2s.  co m
 * @see #setMaxRows
 * @see #setQueryTimeout
 */
protected void applyStatementSettings(PreparedStatement stmt) throws SQLException {
    if (fetchSize != VALUE_NOT_SET) {
        stmt.setFetchSize(fetchSize);
        stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
    }
    if (maxRows != VALUE_NOT_SET) {
        stmt.setMaxRows(maxRows);
    }
    if (queryTimeout != VALUE_NOT_SET) {
        stmt.setQueryTimeout(queryTimeout);
    }
}

From source file:org.springframework.jdbc.core.JdbcTemplate.java

/**
 * Query using a prepared statement, allowing for a PreparedStatementCreator
 * and a PreparedStatementSetter. Most other query methods use this method,
 * but application code will always work with either a creator or a setter.
 * @param psc Callback handler that can create a PreparedStatement given a
 * Connection//from  w w  w.ja  v  a2s .co m
 * @param pss object that knows how to set values on the prepared statement.
 * If this is null, the SQL will be assumed to contain no bind parameters.
 * @param rse object that will extract results.
 * @return an arbitrary result object, as returned by the ResultSetExtractor
 * @throws DataAccessException if there is any problem
 */
protected Object query(PreparedStatementCreator psc, final PreparedStatementSetter pss,
        final ResultSetExtractor rse) throws DataAccessException {
    if (logger.isDebugEnabled()) {
        String sql = getSql(psc);
        logger.debug("Executing SQL query" + (sql != null ? " [" + sql + "]" : ""));
    }
    return execute(psc, new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
            ResultSet rs = null;
            try {
                if (pss != null) {
                    pss.setValues(ps);
                }
                if (getFetchSize() > 0) {
                    ps.setFetchSize(getFetchSize());
                }
                rs = ps.executeQuery();
                ResultSet rsToUse = rs;
                if (nativeJdbcExtractor != null) {
                    rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
                }
                //??pojo
                return rse.extractData(rsToUse);
            } finally {
                JdbcUtils.closeResultSet(rs);
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    });
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

private void doTestStrings(JdbcTemplateCallback jdbcTemplateCallback, boolean usePreparedStatement,
        Integer fetchSize, Integer maxRows, Integer queryTimeout, Object argument) throws Exception {

    String sql = "SELECT FORENAME FROM CUSTMR";
    String[] results = { "rod", "gary", " portia" };

    class StringHandler implements RowCallbackHandler {
        private List list = new LinkedList();

        public void processRow(ResultSet rs) throws SQLException {
            list.add(rs.getString(1));/*from   w ww  . j a v a 2  s.  c o  m*/
        }

        public String[] getStrings() {
            return (String[]) list.toArray(new String[list.size()]);
        }
    }

    MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getString(1);
    ctrlResultSet.setReturnValue(results[0]);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getString(1);
    ctrlResultSet.setReturnValue(results[1]);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getString(1);
    ctrlResultSet.setReturnValue(results[2]);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    if (fetchSize != null) {
        mockStatement.setFetchSize(fetchSize.intValue());
    }
    if (maxRows != null) {
        mockStatement.setMaxRows(maxRows.intValue());
    }
    if (queryTimeout != null) {
        mockStatement.setQueryTimeout(queryTimeout.intValue());
    }
    if (argument != null) {
        mockStatement.setObject(1, argument);
    }
    if (usePreparedStatement) {
        mockStatement.executeQuery();
    } else {
        mockStatement.executeQuery(sql);
    }
    ctrlStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    if (usePreparedStatement) {
        mockConnection.prepareStatement(sql);
    } else {
        mockConnection.createStatement();
    }
    ctrlConnection.setReturnValue(mockStatement);

    ctrlResultSet.replay();
    ctrlStatement.replay();
    replay();

    StringHandler sh = new StringHandler();
    JdbcTemplate template = new JdbcTemplate();
    template.setDataSource(mockDataSource);
    if (fetchSize != null) {
        template.setFetchSize(fetchSize.intValue());
    }
    if (maxRows != null) {
        template.setMaxRows(maxRows.intValue());
    }
    if (queryTimeout != null) {
        template.setQueryTimeout(queryTimeout.intValue());
    }
    jdbcTemplateCallback.doInJdbcTemplate(template, sql, sh);

    // Match
    String[] forenames = sh.getStrings();
    assertTrue("same length", forenames.length == results.length);
    for (int i = 0; i < forenames.length; i++) {
        assertTrue("Row " + i + " matches", forenames[i].equals(results[i]));
    }

    ctrlResultSet.verify();
    ctrlStatement.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testConnectionCallbackWithStatementSettings() throws Exception {
    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    mockConnection.prepareStatement("some SQL");
    ctrlConnection.setReturnValue(mockStatement, 1);
    mockStatement.setFetchSize(10);
    ctrlStatement.setVoidCallable(1);//from ww w . j a va  2s . co  m
    mockStatement.setMaxRows(20);
    ctrlStatement.setVoidCallable(1);
    mockStatement.close();
    ctrlStatement.setVoidCallable(1);
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);
    Object result = template.execute(new ConnectionCallback() {
        public Object doInConnection(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement("some SQL");
            ps.close();
            assertSame(mockConnection, new PlainNativeJdbcExtractor().getNativeConnection(con));
            return "test";
        }
    });

    assertEquals("test", result);
}

From source file:org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsRecordStore.java

public AnalyticsIterator<Record> getRecords(String tableName, List<String> columns, long timeFrom, long timeTo,
        int recordsFrom, int recordsCount, int partitionStart, int partitionEnd) throws AnalyticsException {
    Connection conn = null;//from   w ww.  java2 s. c om
    PreparedStatement stmt = null;
    ResultSet rs;
    try {
        conn = this.getConnection(false);
        if (!this.rdbmsQueryConfigurationEntry.isForwardOnlyReadEnabled()) {
            stmt = conn.prepareStatement(this.getRecordRetrievalQuery(tableName));
        } else {
            stmt = conn.prepareStatement(this.getRecordRetrievalQuery(tableName), ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(this.rdbmsQueryConfigurationEntry.getFetchSize());
        }
        if (recordsCount == -1) {
            recordsCount = Integer.MAX_VALUE;
        }
        stmt.setLong(1, partitionStart);
        stmt.setLong(2, partitionEnd);
        stmt.setLong(3, timeFrom);
        stmt.setLong(4, timeTo);
        int[] paginationIndices = this.calculateIndicesForPaginationMode(recordsFrom, recordsCount);
        stmt.setInt(5, paginationIndices[0]);
        stmt.setInt(6, paginationIndices[1]);
        rs = stmt.executeQuery();
        return new RDBMSResultSetIterator(tableName, columns, conn, stmt, rs);
    } catch (SQLException e) {
        if (conn != null && !this.tableExists(conn, tableName)) {
            RDBMSUtils.cleanupConnection(null, stmt, conn);
            throw new AnalyticsTableNotAvailableException(tableName);
        } else {
            RDBMSUtils.cleanupConnection(null, stmt, conn);
            throw new AnalyticsException("Error in retrieving records: " + e.getMessage(), e);
        }
    }
}