Example usage for java.sql ResultSet setFetchSize

List of usage examples for java.sql ResultSet setFetchSize

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

Introduction

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

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {

    Connection conn = getConnection();

    conn.setAutoCommit(false);//from  w  w  w . j  ava 2s. c o  m
    Statement st = conn.createStatement();

    st.setFetchSize(1);

    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
    st.executeUpdate("insert into survey (id,name ) values (2,'nameValue')");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");

    rs.setFetchSize(1);

    outputResultSet(rs);

    checkForWarning(rs.getWarnings());

    rs.close();
    st.close();
    conn.close();

}

From source file:Main.java

public static void main(String[] argv) throws Exception {

    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);//w  w w .jav a2 s  .c o  m

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);

    Statement stmt = connection.createStatement();
    int fetchSize = stmt.getFetchSize();

    // Set the fetch size on the statement
    stmt.setFetchSize(100);

    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

    // Change the fetch size on the result set
    resultSet.setFetchSize(100);

}

From source file:Main.java

public static void main(String[] argv) throws Exception {

    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);//from  w w w  .  j  ava  2s  . com

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);

    // Get the fetch size of a statement
    Statement stmt = connection.createStatement();
    int fetchSize = stmt.getFetchSize();

    // Set the fetch size on the statement
    stmt.setFetchSize(100);

    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

    // Change the fetch size on the result set
    resultSet.setFetchSize(100);

}

From source file:Main.java

public static void main(String[] argv) throws Exception {

    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);//from w  w  w.  jav a 2s.c  o  m

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);

    // Get the fetch size of a statement
    Statement stmt = connection.createStatement();
    int fetchSize = stmt.getFetchSize();

    // Set the fetch size on the statement
    stmt.setFetchSize(100);

    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

    // Change the fetch size on the result set
    resultSet.setFetchSize(100);

}

From source file:net.firejack.platform.core.utils.db.DBUtils.java

private static ResultSet selectDataFromSource(Connection sourceConnection, TablesMapping mapping)
        throws SQLException {
    Map<Column, Column> columnMapping = mapping.getColumnMapping();
    StringBuilder selectQuery = new StringBuilder("select ");
    for (Map.Entry<Column, Column> columnEntry : columnMapping.entrySet()) {
        Column sourceColumn = columnEntry.getKey();
        selectQuery.append(sourceColumn.getName()).append(',');
    }/*from w ww .  java 2  s.c o  m*/
    if (!columnMapping.isEmpty()) {
        selectQuery.replace(selectQuery.length() - 1, selectQuery.length(), "");
    }
    selectQuery.append(" from ").append(mapping.getSourceTable().getName());
    String sql = selectQuery.toString();
    Statement statement = sourceConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(sql);
    rs.setFetchSize(DEFAULT_BATCH_SIZE);
    return rs;
}

From source file:com.dangdang.ddframe.rdb.sharding.jdbc.adapter.AbstractResultSetAdapter.java

@Override
public final void setFetchSize(final int rows) throws SQLException {
    for (ResultSet each : resultSets) {
        each.setFetchSize(rows);
    }/*from   w  ww .j av a 2  s . co  m*/
}

From source file:com.quest.orahive.HiveJdbcClient.java

private static void insertHiveResultsIntoOracleTable(OraHiveOptions opts, String insertSql,
        List<OracleTableColumn> oracleColumns, Connection oracleConnection, ResultSet resultSet,
        OraHiveCounters counters) {//from   ww w .  j a v  a  2s.  c o m

    long timerHiveFetching = 0;
    long timerOracleInserting = 0;
    long rowsProcessed = 0;

    try {

        oracle.jdbc.OraclePreparedStatement statement = (oracle.jdbc.OraclePreparedStatement) oracleConnection
                .prepareStatement(insertSql);

        int rowIdx = 0;
        int batchIdx = 0;
        int numberOfBatchesCommitted = 0;

        try {
            resultSet.setFetchSize(opts.insertBatchSize);
        } catch (SQLException e) {
            try {
                // Apply fetchN hack for much better performance with pre 0.8 JDBC driver
                LOG.info(
                        "Hive ResultSet does not implement setFetchSize. Wrapping with FetchNResultSet for better performance.");
                resultSet = new FetchNResultSet(resultSet);
                resultSet.setFetchSize(opts.insertBatchSize);
            } catch (IllegalArgumentException iae) {
                LOG.warn(
                        "Wrapping Hive ResultSet with FetchNResultSet failed. Performance may be poor for large result sets.");
                LOG.debug("FetchNResultSet exception was:", iae);
            }
        }
        long start = System.nanoTime();
        while (resultSet.next()) {
            for (int idx = 0; idx < oracleColumns.size(); idx++) { // <- JDBC is 1-based
                statement.setObject(idx + 1, resultSet.getObject(idx + 1));
            }
            timerHiveFetching += System.nanoTime() - start;

            rowsProcessed++;
            statement.addBatch();

            rowIdx++;
            if (rowIdx == opts.insertBatchSize) {
                rowIdx = 0;

                start = System.nanoTime();

                // executeBatchWithRetry(statement, oracleConnection);
                statement.executeBatch();
                statement.clearBatch();

                timerOracleInserting += System.nanoTime() - start;

                batchIdx++;
            }

            if (batchIdx == opts.commitBatchCount) {
                batchIdx = 0;
                oracleConnection.commit();
                numberOfBatchesCommitted++;
                LOG.info(String.format("Number of rows inserted so far: %d",
                        numberOfBatchesCommitted * (opts.insertBatchSize * opts.commitBatchCount)));
            }
            start = System.nanoTime();
        }

        if (rowIdx > 0) {
            start = System.nanoTime();

            //executeBatchWithRetry(statement, oracleConnection);
            statement.executeBatch();

            timerOracleInserting += System.nanoTime() - start;
        }

        oracleConnection.commit();

        statement.close();
    } catch (SQLException ex) {

        if (Utilities.oracleSessionHasBeenKilled(ex)) {
            LOG.info("\n*********************************************************"
                    + "\nThe Oracle session in use has been killed by a 3rd party."
                    + "\n*********************************************************");
        } else
            LOG.error("An error occurred within the process of fetching Hive results "
                    + "and inserting them into an Oracle table. (1)", ex);

        try {
            oracleConnection.rollback();
        } catch (SQLException e) {
        }

        System.exit(1);
    } catch (Exception ex) {
        LOG.error("An error occurred within the process of fetching Hive results "
                + "and inserting them into an Oracle table. (2)", ex);
    } finally {
        LOG.info(String.format("Number of rows obtained from Hive: %d", rowsProcessed));
    }

    counters.rowsProcessed = rowsProcessed;
    counters.hiveFetchTimeNanoSec = timerHiveFetching;
    counters.oracleInsertTimeNanoSec = timerOracleInserting;
}

From source file:architecture.ee.jdbc.util.impl.JdbcHelperImpl.java

public void setFetchSize(ResultSet rs, int fetchSize) {
    if (isFetchSizeSupported())
        try {//from   w w  w . j  a  v a  2 s .  co m
            rs.setFetchSize(fetchSize);
        } catch (Throwable t) {
            fetchSizeSupported = false;
        }
}

From source file:com.webbfontaine.valuewebb.report.ScanSelectivityReporter.java

private byte[] generateReport() throws SQLException, IOException, JRException {
    Connection connection = null;
    PreparedStatement ps = null;/*  w  w  w  . j  a  v a2  s. c  om*/
    InputStream templateFile = null;
    ResultSet rs = null;
    try {
        connection = getConnection();
        ps = getPreparedStatement(connection);
        templateFile = getTemplateFileStream(getReportFilePath());
        rs = ps.executeQuery();

        rs.setFetchSize(1000);

        if (XLS_REPORT.equals(reportType)) {
            return processXls(templateFile, rs);
        } else {
            return processPdf(templateFile, rs);
        }
    } finally {
        DBUtils.closeResource(connection);
        DBUtils.closeResource(ps);
        IOUtils.closeQuietly(templateFile);
        DBUtils.closeResource(rs);
    }
}

From source file:com.centeractive.ws.builder.soap.XmlUtils.java

public static String createJdbcXmlResult(Statement statement)
        throws SQLException, ParserConfigurationException {
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    Document xmlDocumentResult = builder.newDocument();
    Element resultsElement = xmlDocumentResult.createElement("Results");
    xmlDocumentResult.appendChild(resultsElement);

    if (statement != null) {
        ResultSet resultSet = statement.getResultSet();
        if (resultSet != null) {
            resultSet.setFetchSize(statement.getFetchSize());
            xmlDocumentResult = addResultSetXmlPart(resultsElement, resultSet, xmlDocumentResult);
            while (statement.getMoreResults()) {
                xmlDocumentResult = addResultSetXmlPart(resultsElement, statement.getResultSet(),
                        xmlDocumentResult);
            }//from  ww w  . j av a  2 s .  c  o m
        } else {
            Element errorElement = xmlDocumentResult.createElement("UpdateCount");
            errorElement
                    .appendChild(xmlDocumentResult.createTextNode(String.valueOf(statement.getUpdateCount())));
            resultsElement.appendChild(errorElement);
        }
    }

    StringWriter out = new StringWriter();

    OutputFormat outputFormat = new OutputFormat(xmlDocumentResult);
    outputFormat.setOmitComments(true);
    outputFormat.setOmitDocumentType(true);
    outputFormat.setOmitXMLDeclaration(true);
    // outputFormat.setLineSeparator( "\n" );
    // add this line //
    // outputFormat.setPreserveSpace( true );
    outputFormat.setIndent(3);
    outputFormat.setIndenting(true);

    try {
        XMLSerializer serializer = new XMLSerializer(new PrintWriter(out), outputFormat);
        serializer.asDOMSerializer();
        serializer.serialize(xmlDocumentResult);
    } catch (IOException e) {
        throw new SoapBuilderException(e);
    }

    return out.toString();
}