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.wso2.carbon.apimgt.migration.client.MigrateFrom19to110.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;//from  w ww. 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 {
        APIMgtDBUtil.closeAllConnections(null, null, resultSet);
        APIMgtDBUtil.closeAllConnections(preparedStatementUpdate, null, null);
        APIMgtDBUtil.closeAllConnections(preparedStatementDelete, null, null);

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error("Unable to close the statement", e);
            }
        }
    }
    return continueUpdatingDB;
}

From source file:org.wso2.carbon.apimgt.migration.client.MigrateFrom19to110.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;/*from   w  w  w  .ja  va  2  s .  c  o  m*/
    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.MigrateFrom19to110.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 {//w w  w .  ja  va2 s  . com
        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);
                if (log.isDebugEnabled()) {
                    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;
}

From source file:org.wso2.carbon.apimgt.migration.util.StatDBUtil.java

private static void updateResponseSummaryTable() {
    Connection connection = null;
    Statement statement = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;/*from  ww w. ja v  a2  s  . c om*/

    try {
        String sql = "SELECT CONTEXT, VERSION, API_VERSION FROM API_REQUEST_SUMMARY GROUP BY CONTEXT, VERSION, API_VERSION";

        connection = dataSource.getConnection();
        statement = connection.createStatement();
        connection.setAutoCommit(false);
        statement.setFetchSize(50);
        resultSet = statement.executeQuery(sql);

        preparedStatement = connection
                .prepareStatement("UPDATE API_RESPONSE_SUMMARY SET CONTEXT = concat(?, '/', ?) "
                        + "WHERE CONTEXT = ? AND API_VERSION = ?");
        while (resultSet.next()) {
            final String context = resultSet.getString("CONTEXT");
            final String version = resultSet.getString("VERSION");

            if (!context.endsWith('/' + version)) {
                preparedStatement.setString(1, context);
                preparedStatement.setString(2, version);
                preparedStatement.setString(3, context);
                preparedStatement.setString(4, resultSet.getString("API_VERSION"));
                preparedStatement.addBatch();
            }
        }
        preparedStatement.executeBatch();
        connection.commit();

    } catch (SQLException e) {
        log.error("SQLException when updating API_RESPONSE_SUMMARY table", e);
    } finally {
        try {
            if (preparedStatement != null)
                preparedStatement.close();
            if (statement != null)
                statement.close();
            if (resultSet != null)
                resultSet.close();
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            log.error("SQLException when closing resource", e);
        }
    }
}

From source file:org.wso2.carbon.device.mgt.core.archival.dao.impl.ArchivalDAOImpl.java

private Statement createMemoryEfficientStatement(Connection conn) throws ArchivalDAOException, SQLException {
    Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(Integer.MIN_VALUE);
    return stmt;//from   w  w  w . j  av  a 2 s . c  o  m
}