Example usage for java.sql Statement setFetchSize

List of usage examples for java.sql Statement setFetchSize

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

Introduction

In this page you can find the example usage for java.sql Statement 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.sqlhandler.TnAbstractBasicSqlHandler.java

protected void setFetchSize(Statement statement, int fetchSize) {
    if (statement == null) {
        return;/* w w  w  . j a  va 2 s  .  c  om*/
    }
    try {
        statement.setFetchSize(fetchSize);
    } catch (SQLException e) {
        handleSQLException(e, statement);
    }
}

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

public <S> void execute(InputLoader<S> inputLoader, InputConverter<S> converter) {
    long count = 0;
    Connection readConnection = null;
    Statement stmt = null;
    ResultSet rs = null;/*from   www.ja  va2  s  . c  o m*/
    Connection writeConnection = null;
    PreparedStatement writeStatement = null;
    try {
        writeConnection = db.getDataSource().getConnection();
        writeConnection.setAutoCommit(false);
        writeStatement = writeConnection.prepareStatement(converter.updateSql());

        readConnection = db.getDataSource().getConnection();
        readConnection.setAutoCommit(false);

        stmt = readConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(GROUP_SIZE);
        if (db.getDialect().getId().equals(MySql.ID)) {
            stmt.setFetchSize(Integer.MIN_VALUE);
        } else {
            stmt.setFetchSize(GROUP_SIZE);
        }
        rs = stmt.executeQuery(convertSelectSql(inputLoader.selectSql(), db));

        int cursor = 0;
        while (rs.next()) {
            if (converter.convert(inputLoader.load(rs), writeStatement)) {
                writeStatement.addBatch();
                cursor++;
                count++;
            }

            if (cursor == GROUP_SIZE) {
                writeStatement.executeBatch();
                writeConnection.commit();
                cursor = 0;
            }
        }
        if (cursor > 0) {
            writeStatement.executeBatch();
            writeConnection.commit();
        }

    } catch (SQLException e) {
        SqlUtil.log(LOGGER, e);
        throw processError(e);
    } catch (Exception e) {
        throw processError(e);
    } finally {
        DbUtils.closeQuietly(writeStatement);
        DbUtils.closeQuietly(writeConnection);
        DbUtils.closeQuietly(readConnection, stmt, rs);

        LOGGER.info("{} rows have been updated", count);
    }
}

From source file:org.sonar.server.db.migrations.v36.Referentials.java

private Queue<long[]> initGroupOfViolationIds(Database database) throws SQLException {
    Connection connection = database.getDataSource().getConnection();
    Statement stmt = null;
    ResultSet rs = null;// w w w .  j a v a 2s.  co  m
    try {
        connection.setAutoCommit(false);
        stmt = connection.createStatement();
        stmt.setFetchSize(10000);
        rs = stmt.executeQuery("select id from rule_failures");
        Queue<long[]> queue = new ConcurrentLinkedQueue<long[]>();

        totalViolations = 0;
        long[] block = new long[VIOLATION_GROUP_SIZE];
        int cursor = 0;
        while (rs.next()) {
            block[cursor] = rs.getLong(1);
            cursor++;
            totalViolations++;
            if (cursor == VIOLATION_GROUP_SIZE) {
                queue.add(block);
                block = new long[VIOLATION_GROUP_SIZE];
                cursor = 0;
            }
        }
        if (cursor > 0) {
            queue.add(block);
        }
        return queue;
    } finally {
        DbUtils.closeQuietly(connection, stmt, rs);
    }
}

From source file:org.sonar.server.db.migrations.violation.Referentials.java

private Queue<long[]> initGroupOfViolationIds(Database database) throws SQLException {
    Connection connection = database.getDataSource().getConnection();
    Statement stmt = null;
    ResultSet rs = null;/*www  . j  av a  2 s  . co  m*/
    try {
        connection.setAutoCommit(false);
        stmt = connection.createStatement();
        stmt.setFetchSize(10000);
        rs = stmt.executeQuery("select id from rule_failures");
        ConcurrentLinkedQueue<long[]> queue = new ConcurrentLinkedQueue<long[]>();

        totalViolations = 0;
        long[] block = new long[VIOLATION_GROUP_SIZE];
        int cursor = 0;
        while (rs.next()) {
            block[cursor++] = rs.getLong(1);
            totalViolations++;
            if (cursor == VIOLATION_GROUP_SIZE) {
                queue.add(block);
                block = new long[VIOLATION_GROUP_SIZE];
                cursor = 0;
            }
        }
        if (cursor > 0) {
            queue.add(block);
        }
        return queue;
    } finally {
        DbUtils.closeQuietly(connection, stmt, rs);
    }
}

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

public Object query(final String sql, final ResultSetExtractor rse) throws DataAccessException {
    if (sql == null) {
        throw new InvalidDataAccessApiUsageException("SQL must not be null");
    }//from  w  w w.j  av  a  2  s.  c o m
    if (JdbcUtils.countParameterPlaceholders(sql, '?', "'\"") > 0) {
        throw new InvalidDataAccessApiUsageException(
                "Cannot execute [" + sql + "] as a static query: it contains bind variables");
    }
    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL query [" + sql + "]");
    }
    class QueryStatementCallback implements StatementCallback, SqlProvider {
        public Object doInStatement(Statement stmt) throws SQLException {
            ResultSet rs = null;
            try {
                if (getFetchSize() > 0)
                    stmt.setFetchSize(getFetchSize());
                rs = stmt.executeQuery(sql);
                ResultSet rsToUse = rs;
                if (nativeJdbcExtractor != null) {
                    rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
                }
                return rse.extractData(rsToUse);
            } finally {
                JdbcUtils.closeResultSet(rs);
            }
        }

        public String getSql() {
            return sql;
        }
    }
    return execute(new QueryStatementCallback());
}

From source file:org.talend.dq.dbms.DbmsLanguage.java

/**
 * DOC xqliu Comment method "createStatement".
 * //from   w  ww  . jav a 2 s .  c om
 * @param connection
 * @return
 * @throws SQLException
 */
public Statement createStatement(java.sql.Connection connection, int fetchSize) throws SQLException {
    Statement statement = createStatement(connection);
    statement.setFetchSize(fetchSize);
    return statement;
}

From source file:org.talend.dq.dbms.MySQLDbmsLanguage.java

@Override
public Statement createStatement(Connection connection, int fetchSize) throws SQLException {
    // TDQ-10991,TDQ-11124 we use stream result to enhance performance on big data.
    // comment form Mysql API "createStreamingResultSet()":We only stream result sets when they are forward-only,
    // read-only, and the fetch size has been set to Integer.MIN_VALUE.
    Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);
    return statement;
}

From source file:org.wso2.carbon.apimgt.migration.client.MigrateFrom110to200.java

private boolean updateAMApplicationKeyMapping(Connection connection) throws SQLException {
    log.info("Updating consumer keys in AM_APPLICATION_KEY_MAPPING");
    PreparedStatement preparedStatementUpdate = null;
    PreparedStatement preparedStatementDelete = null;
    Statement statement = null;
    ResultSet resultSet = null;/* ww w  . j av  a  2 s . c  o m*/
    boolean continueUpdatingDB = true;
    long totalRecords = 0;
    long decryptionFailedRecords = 0;

    try {
        String query = "SELECT APPLICATION_ID, CONSUMER_KEY, KEY_TYPE FROM AM_APPLICATION_KEY_MAPPING";
        ArrayList<AppKeyMappingTableDTO> appKeyMappingTableDTOs = new ArrayList<>();
        ArrayList<AppKeyMappingTableDTO> appKeyMappingTableDTOsFailed = new ArrayList<>();

        statement = connection.createStatement();
        statement.setFetchSize(50);
        resultSet = statement.executeQuery(query);

        while (resultSet.next()) {
            ConsumerKeyDTO consumerKeyDTO = new ConsumerKeyDTO();
            consumerKeyDTO.setEncryptedConsumerKey(resultSet.getString("CONSUMER_KEY"));

            AppKeyMappingTableDTO appKeyMappingTableDTO = new AppKeyMappingTableDTO();
            appKeyMappingTableDTO.setApplicationId(resultSet.getString("APPLICATION_ID"));
            appKeyMappingTableDTO.setConsumerKey(consumerKeyDTO);
            appKeyMappingTableDTO.setKeyType(resultSet.getString("KEY_TYPE"));
            totalRecords++;
            if (ResourceModifier.decryptConsumerKeyIfEncrypted(consumerKeyDTO)) {

                appKeyMappingTableDTOs.add(appKeyMappingTableDTO);
                log.debug("Successfully decrypted consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " as : " + consumerKeyDTO.getDecryptedConsumerKey()
                        + " in AM_APPLICATION_KEY_MAPPING table");
            } else {
                log.error("Cannot decrypt consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " in AM_APPLICATION_KEY_MAPPING table");
                decryptionFailedRecords++;
                appKeyMappingTableDTOsFailed.add(appKeyMappingTableDTO);

                //If its not allowed to remove decryption failed entries from DB, we will not continue updating 
                // tables even with successfully decrypted entries to maintain DB integrity
                if (!removeDecryptionFailedKeysFromDB) {
                    continueUpdatingDB = false;
                }
            }
        }

        if (continueUpdatingDB) {
            preparedStatementUpdate = connection
                    .prepareStatement("UPDATE AM_APPLICATION_KEY_MAPPING SET CONSUMER_KEY = ?"
                            + " WHERE APPLICATION_ID = ? AND KEY_TYPE = ?");

            for (AppKeyMappingTableDTO appKeyMappingTableDTO : appKeyMappingTableDTOs) {
                preparedStatementUpdate.setString(1,
                        appKeyMappingTableDTO.getConsumerKey().getDecryptedConsumerKey());
                preparedStatementUpdate.setString(2, appKeyMappingTableDTO.getApplicationId());
                preparedStatementUpdate.setString(3, appKeyMappingTableDTO.getKeyType());
                preparedStatementUpdate.addBatch();
            }
            preparedStatementUpdate.executeBatch();

            //deleting rows where consumer key decryption was unsuccessful
            preparedStatementDelete = connection
                    .prepareStatement("DELETE FROM AM_APPLICATION_KEY_MAPPING WHERE CONSUMER_KEY = ?");

            for (AppKeyMappingTableDTO appKeyMappingTableDTO : appKeyMappingTableDTOsFailed) {
                preparedStatementDelete.setString(1,
                        appKeyMappingTableDTO.getConsumerKey().getEncryptedConsumerKey());
                preparedStatementDelete.addBatch();
            }
            preparedStatementDelete.executeBatch();
            log.info("AM_APPLICATION_KEY_MAPPING table updated with " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries deleted as they cannot be decrypted");
        } else {
            log.error("AM_APPLICATION_KEY_MAPPING table not updated as " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries cannot be decrypted");
        }
    } finally {
        if (preparedStatementUpdate != null)
            preparedStatementUpdate.close();
        if (preparedStatementDelete != null)
            preparedStatementDelete.close();
        if (statement != null)
            statement.close();
        if (resultSet != null)
            resultSet.close();
    }

    return continueUpdatingDB;
}

From source file:org.wso2.carbon.apimgt.migration.client.MigrateFrom110to200.java

private boolean updateAMAppKeyDomainMapping(Connection connection) throws SQLException {
    log.info("Updating consumer keys in AM_APP_KEY_DOMAIN_MAPPING");
    Statement selectStatement = null;
    Statement deleteStatement = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;/*www  .  j a  v  a 2  s  .  com*/
    boolean continueUpdatingDB = true;
    long totalRecords = 0;
    long decryptionFailedRecords = 0;

    try {
        ArrayList<KeyDomainMappingTableDTO> keyDomainMappingTableDTOs = new ArrayList<>();
        String query = "SELECT * FROM AM_APP_KEY_DOMAIN_MAPPING";

        selectStatement = connection.createStatement();
        selectStatement.setFetchSize(50);
        resultSet = selectStatement.executeQuery(query);
        while (resultSet.next()) {
            ConsumerKeyDTO consumerKeyDTO = new ConsumerKeyDTO();
            consumerKeyDTO.setEncryptedConsumerKey(resultSet.getString("CONSUMER_KEY"));
            totalRecords++;
            if (ResourceModifier.decryptConsumerKeyIfEncrypted(consumerKeyDTO)) {
                KeyDomainMappingTableDTO keyDomainMappingTableDTO = new KeyDomainMappingTableDTO();
                keyDomainMappingTableDTO.setConsumerKey(consumerKeyDTO);
                keyDomainMappingTableDTO.setAuthzDomain(resultSet.getString("AUTHZ_DOMAIN"));

                keyDomainMappingTableDTOs.add(keyDomainMappingTableDTO);
            } else {
                log.error("Cannot decrypt consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " in AM_APP_KEY_DOMAIN_MAPPING table");
                decryptionFailedRecords++;
                //If its not allowed to remove decryption failed entries from DB, we will not continue updating 
                // tables even with successfully decrypted entries to maintain DB integrity
                if (!removeDecryptionFailedKeysFromDB) {
                    continueUpdatingDB = false;
                }
            }
        }

        if (continueUpdatingDB) { // Modify table only if decryption is successful
            preparedStatement = connection.prepareStatement(
                    "INSERT INTO AM_APP_KEY_DOMAIN_MAPPING " + "(CONSUMER_KEY, AUTHZ_DOMAIN) VALUES (?, ?)");

            for (KeyDomainMappingTableDTO keyDomainMappingTableDTO : keyDomainMappingTableDTOs) {
                preparedStatement.setString(1,
                        keyDomainMappingTableDTO.getConsumerKey().getDecryptedConsumerKey());
                preparedStatement.setString(2, keyDomainMappingTableDTO.getAuthzDomain());
                preparedStatement.addBatch();
            }

            deleteStatement = connection.createStatement();
            deleteStatement.execute("DELETE FROM AM_APP_KEY_DOMAIN_MAPPING");

            preparedStatement.executeBatch();
            log.info("AM_APP_KEY_DOMAIN_MAPPING table updated with " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries deleted as they cannot be decrypted");
        } else {
            log.error("AM_APP_KEY_DOMAIN_MAPPING table not updated as " + decryptionFailedRecords + "/"
                    + totalRecords + " of the CONSUMER_KEY entries" + " cannot be decrypted");
        }
    } finally {
        if (selectStatement != null)
            selectStatement.close();
        if (deleteStatement != null)
            deleteStatement.close();
        if (preparedStatement != null)
            preparedStatement.close();
        if (resultSet != null)
            resultSet.close();
    }

    return continueUpdatingDB;
}

From source file:org.wso2.carbon.apimgt.migration.client.MigrateFrom110to200.java

private boolean updateIdnTableConsumerKeys(Connection connection) throws SQLException {
    log.info("Updating consumer keys in IDN Tables");
    Statement consumerAppsLookup = null;
    PreparedStatement consumerAppsDelete = null;
    PreparedStatement consumerAppsInsert = null;
    PreparedStatement consumerAppsDeleteFailedRecords = null;
    PreparedStatement accessTokenUpdate = null;
    PreparedStatement accessTokenDelete = null;

    ResultSet consumerAppsResultSet = null;
    boolean continueUpdatingDB = true;

    try {/*from   w ww .j av  a2s .  c o  m*/
        String consumerAppsQuery = "SELECT * FROM IDN_OAUTH_CONSUMER_APPS";
        consumerAppsLookup = connection.createStatement();
        consumerAppsLookup.setFetchSize(50);
        consumerAppsResultSet = consumerAppsLookup.executeQuery(consumerAppsQuery);

        ArrayList<ConsumerAppsTableDTO> consumerAppsTableDTOs = new ArrayList<>();
        ArrayList<ConsumerAppsTableDTO> consumerAppsTableDTOsFailed = new ArrayList<>();

        while (consumerAppsResultSet.next()) {
            ConsumerKeyDTO consumerKeyDTO = new ConsumerKeyDTO();
            consumerKeyDTO.setEncryptedConsumerKey(consumerAppsResultSet.getString("CONSUMER_KEY"));

            ConsumerAppsTableDTO consumerAppsTableDTO = new ConsumerAppsTableDTO();
            consumerAppsTableDTO.setConsumerKey(consumerKeyDTO);
            consumerAppsTableDTO.setConsumerSecret(consumerAppsResultSet.getString("CONSUMER_SECRET"));
            consumerAppsTableDTO.setUsername(consumerAppsResultSet.getString("USERNAME"));
            consumerAppsTableDTO.setTenantID(consumerAppsResultSet.getInt("TENANT_ID"));
            consumerAppsTableDTO.setAppName(consumerAppsResultSet.getString("APP_NAME"));
            consumerAppsTableDTO.setOauthVersion(consumerAppsResultSet.getString("OAUTH_VERSION"));
            consumerAppsTableDTO.setCallbackURL(consumerAppsResultSet.getString("CALLBACK_URL"));
            consumerAppsTableDTO.setGrantTypes(consumerAppsResultSet.getString("GRANT_TYPES"));
            if (ResourceModifier.decryptConsumerKeyIfEncrypted(consumerKeyDTO)) {
                consumerAppsTableDTOs.add(consumerAppsTableDTO);
                log.debug("Successfully decrypted consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " in IDN_OAUTH_CONSUMER_APPS table");
            } else {
                consumerAppsTableDTOsFailed.add(consumerAppsTableDTO);
                log.error("Cannot decrypt consumer key : " + consumerKeyDTO.getEncryptedConsumerKey()
                        + " in IDN_OAUTH_CONSUMER_APPS table");
                //If its not allowed to remove decryption failed entries from DB, we will not continue updating 
                // tables even with successfully decrypted entries to maintain DB integrity
                if (!removeDecryptionFailedKeysFromDB) {
                    continueUpdatingDB = false;
                }
            }
        }

        if (continueUpdatingDB) {
            // Add new entries for decrypted consumer keys into IDN_OAUTH_CONSUMER_APPS
            consumerAppsInsert = connection
                    .prepareStatement("INSERT INTO IDN_OAUTH_CONSUMER_APPS (CONSUMER_KEY, "
                            + "CONSUMER_SECRET, USERNAME, TENANT_ID, APP_NAME, OAUTH_VERSION, "
                            + "CALLBACK_URL, GRANT_TYPES) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

            for (ConsumerAppsTableDTO consumerAppsTableDTO : consumerAppsTableDTOs) {
                updateIdnConsumerApps(consumerAppsInsert, consumerAppsTableDTO);
            }
            consumerAppsInsert.executeBatch();
            log.info("Inserted entries in IDN_OAUTH_CONSUMER_APPS");

            // Update IDN_OAUTH2_ACCESS_TOKEN foreign key reference to CONSUMER_KEY
            accessTokenUpdate = connection.prepareStatement(
                    "UPDATE IDN_OAUTH2_ACCESS_TOKEN SET CONSUMER_KEY = ? " + "WHERE CONSUMER_KEY = ?");

            for (ConsumerAppsTableDTO consumerAppsTableDTO : consumerAppsTableDTOs) {
                ConsumerKeyDTO consumerKeyDTO = consumerAppsTableDTO.getConsumerKey();
                updateIdnAccessToken(accessTokenUpdate, consumerKeyDTO);
            }
            accessTokenUpdate.executeBatch();
            log.info("Updated entries in IDN_OAUTH2_ACCESS_TOKEN");

            // Remove redundant records in IDN_OAUTH_CONSUMER_APPS
            consumerAppsDelete = connection
                    .prepareStatement("DELETE FROM IDN_OAUTH_CONSUMER_APPS WHERE " + "CONSUMER_KEY = ?");

            for (ConsumerAppsTableDTO consumerAppsTableDTO : consumerAppsTableDTOs) {
                ConsumerKeyDTO consumerKeyDTO = consumerAppsTableDTO.getConsumerKey();
                deleteIdnConsumerApps(consumerAppsDelete, consumerKeyDTO);
            }
            consumerAppsDelete.executeBatch();
            log.info("Removed redundant entries in IDN_OAUTH_CONSUMER_APPS");

            //deleting rows where consumer key decryption was unsuccessful from IDN_OAUTH_CONSUMER_APPS table
            consumerAppsDeleteFailedRecords = connection
                    .prepareStatement("DELETE FROM IDN_OAUTH_CONSUMER_APPS WHERE " + "CONSUMER_KEY = ?");
            for (ConsumerAppsTableDTO consumerAppsTableDTO : consumerAppsTableDTOsFailed) {
                ConsumerKeyDTO consumerKeyDTO = consumerAppsTableDTO.getConsumerKey();
                deleteIdnConsumerApps(consumerAppsDeleteFailedRecords, consumerKeyDTO);
            }
            consumerAppsDeleteFailedRecords.executeBatch();
            log.info("Removed decryption failed entries in IDN_OAUTH_CONSUMER_APPS");

            //deleting rows where consumer key decryption was unsuccessful from IDN_OAUTH2_ACCESS_TOKEN table
            accessTokenDelete = connection
                    .prepareStatement("DELETE FROM IDN_OAUTH2_ACCESS_TOKEN " + "WHERE CONSUMER_KEY = ?");
            for (ConsumerAppsTableDTO consumerAppsTableDTO : consumerAppsTableDTOsFailed) {
                ConsumerKeyDTO consumerKeyDTO = consumerAppsTableDTO.getConsumerKey();
                deleteIdnAccessToken(consumerAppsDeleteFailedRecords, consumerKeyDTO);
            }
            accessTokenDelete.executeBatch();
            log.info("Removed decryption failed entries in IDN_OAUTH2_ACCESS_TOKEN");
        }
    } finally {
        if (consumerAppsLookup != null)
            consumerAppsLookup.close();
        if (consumerAppsDelete != null)
            consumerAppsDelete.close();
        if (consumerAppsDeleteFailedRecords != null)
            consumerAppsDeleteFailedRecords.close();
        if (consumerAppsInsert != null)
            consumerAppsInsert.close();
        if (accessTokenUpdate != null)
            accessTokenUpdate.close();
        if (accessTokenDelete != null)
            accessTokenDelete.close();
        if (consumerAppsResultSet != null)
            consumerAppsResultSet.close();
    }

    return continueUpdatingDB;
}