Example usage for java.sql PreparedStatement 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:com.baidu.rigel.biplatform.tesseract.dataquery.service.impl.SqlDataQueryServiceImpl.java

/**
 * ?SQL??resultRecord list//  w w  w  .  j av a  2  s  . c o  m
 * @param sqlQuery
 * @param dataSource
 * @param limitStart
 * @param limitEnd
 * @return
 */
private SearchIndexResultSet querySqlList(SqlQuery sqlQuery, DataSource dataSource, long limitStart,
        long limitEnd) {
    long current = System.currentTimeMillis();
    if (sqlQuery == null || dataSource == null || limitEnd < 0) {
        throw new IllegalArgumentException();
    }

    sqlQuery.setLimitMap(limitStart, limitEnd);

    this.initJdbcTemplate(dataSource);

    Meta meta = new Meta(sqlQuery.getSelectList().toArray(new String[0]));
    SearchIndexResultSet resultSet = new SearchIndexResultSet(meta, 1000000);

    jdbcTemplate.query(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement pstmt = con.prepareStatement(sqlQuery.toSql(), ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
            if (con.getMetaData().getDriverName().toLowerCase().contains("mysql")) {
                pstmt.setFetchSize(Integer.MIN_VALUE);
            }
            return pstmt;
        }
    }, new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            List<Object> fieldValues = new ArrayList<Object>();
            String groupBy = "";
            for (String select : sqlQuery.getSelectList()) {
                fieldValues.add(rs.getObject(select));
                if (sqlQuery.getGroupBy() != null && sqlQuery.getGroupBy().contains(select)) {
                    groupBy += rs.getString(select) + ",";
                }
            }

            SearchIndexResultRecord record = new SearchIndexResultRecord(
                    fieldValues.toArray(new Serializable[0]), groupBy);
            resultSet.addRecord(record);
        }
    });
    LOGGER.info(String.format(LogInfoConstants.INFO_PATTERN_FUNCTION_END, "querySqlList",
            "[sqlQuery:" + sqlQuery.toSql() + "][dataSource:" + dataSource + "][limitStart:" + limitStart
                    + "][limitEnd:" + limitEnd + "] cost" + (System.currentTimeMillis() - current + "ms!")));
    return resultSet;
}

---

From source file:org.apache.lucene.store.jdbc.JdbcDirectory.java

public void forceDeleteFile(final String name) throws IOException {
    jdbcTemplate.executeUpdate(table.sqlDeleteByName(), new JdbcTemplate.PrepateStatementAwareCallback() {
        public void fillPrepareStatement(PreparedStatement ps) throws Exception {
            ps.setFetchSize(1);
            ps.setString(1, name);/*from ww  w.j  a va2s  .c om*/
        }
    });
}

---

From source file:org.apache.lucene.store.jdbc.JdbcDirectory.java

/**
 * Returns <code>true</code> if the database table exists.
 *
 * @return <code>true</code> if the database table exists, <code>false</code> otherwise
 * @throws IOException/*from  w w w  . j  a va2s  .com*/
 * @throws UnsupportedOperationException If the database dialect does not support it
 */
public boolean tableExists() throws IOException, UnsupportedOperationException {
    Boolean tableExists = (Boolean) jdbcTemplate.executeSelect(
            dialect.sqlTableExists(table.getCatalog(), table.getSchema()),
            new JdbcTemplate.ExecuteSelectCallback() {
                public void fillPrepareStatement(PreparedStatement ps) throws Exception {
                    ps.setFetchSize(1);
                    ps.setString(1, table.getName().toLowerCase());
                }

                public Object execute(ResultSet rs) throws Exception {
                    if (rs.next()) {
                        return Boolean.TRUE;
                    }
                    return Boolean.FALSE;
                }
            });
    return tableExists.booleanValue();
}

---

From source file:at.stefanproell.ResultSetVerification.ResultSetVerificationAPI.java

public List<Integer> getSortedSequenceListFromQuery(String sqlString) {
    this.logger.info("Trying to execute: " + sqlString);
    List<Integer> sortedIntegerList = new LinkedList<Integer>();

    Connection connection = this.getConnection();
    PreparedStatement preparedStatement = null;
    ResultSet rs = null;//from  w ww . j  a  va 2 s.  c  om
    try {
        preparedStatement = connection.prepareStatement(sqlString);
        preparedStatement.setFetchSize(10000);
        rs = preparedStatement.executeQuery();

        while (rs.next()) {
            int seq = rs.getInt("ID_SYSTEM_SEQUENCE");
            sortedIntegerList.add(seq);
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return sortedIntegerList;

    }

}

---

From source file:at.stefanproell.ResultSetVerification.ResultSetVerificationAPI.java

/**
 * Execute the query provided from the querstore.
 *
 * @param sqlString/*from   w  w w. j a v a  2s . c o m*/
 * @return
 */
public ResultSet executeQuery(String sqlString) {
    this.logger.info("Trying to execute: " + sqlString);

    if (sqlString != null) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            preparedStatement = connection.prepareStatement(sqlString);
            preparedStatement.setFetchSize(10000);

            rs = preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            this.logger.info("Resulset row count: " + this.getResultSetRowCount(rs));

            return rs;

        }

    }
    return null;

}

---

From source file:com.yahoo.ycsb.db.PostgreSQLJsonbClient.java

private PreparedStatement createAndCacheScanStatement(StatementType scanType, String key) throws SQLException {
    StringBuilder select = new StringBuilder("SELECT * FROM ");
    select.append(scanType.tableName);/*from ww w  .j av  a2  s .c o  m*/
    select.append(" WHERE ");
    select.append(PRIMARY_KEY);
    select.append(" >= ?");
    select.append(" ORDER BY ");
    select.append(PRIMARY_KEY);
    select.append(" LIMIT ?");
    PreparedStatement scanStatement = getShardConnectionByKey(key).prepareStatement(select.toString());
    if (this.jdbcFetchSize > 0) {
        scanStatement.setFetchSize(this.jdbcFetchSize);
    }
    PreparedStatement stmt = cachedStatements.putIfAbsent(scanType, scanStatement);
    if (stmt == null) {
        return scanStatement;
    }
    return stmt;
}

---

From source file:com.datatorrent.lib.db.jdbc.AbstractJdbcPollInputOperator.java

/**
 * Function to insert results of a query in emit Queue
 * @param preparedStatement PreparedStatement to execute the query and store the results in emit Queue.
 *///www.ja  v  a  2  s  . com
protected void insertDbDataInQueue(PreparedStatement preparedStatement)
        throws SQLException, InterruptedException {
    preparedStatement.setFetchSize(getFetchSize());
    ResultSet result = preparedStatement.executeQuery();
    if (result.next()) {
        do {
            while (!emitQueue.offer(getTuple(result))) {
                Thread.sleep(DEFAULT_SLEEP_TIME);
            }
        } while (result.next());
        result.close();
    }
    preparedStatement.close();
}

---

From source file:biblivre3.administration.ReportsDAO.java

public AssetHoldingDto getAssetHoldingReportData() {
    AssetHoldingDto dto = new AssetHoldingDto();
    Connection con = null;//from w ww  . j  av  a 2 s .  c o m
    try {
        con = getDataSource().getConnection();
        String sql = " SELECT H.asset_holding, R.record FROM cataloging_holdings H INNER JOIN cataloging_biblio R "
                + " ON R.record_serial = H.record_serial WHERE H.database = 0 " + " ORDER BY H.asset_holding ";

        final PreparedStatement pst = con.prepareStatement(sql);
        pst.setFetchSize(100);

        final ResultSet rs = pst.executeQuery();
        List<String[]> dataList = new ArrayList<String[]>();
        while (rs.next()) {
            Record record = MarcUtils.iso2709ToRecord(rs.getBytes("record"));
            String assetHolding = rs.getString("asset_holding");
            String[] data = new String[5];
            data[0] = assetHolding;
            data[1] = Indexer.listPrimaryAuthor(record);
            data[2] = Indexer.listOneTitle(record);
            data[3] = Indexer.listEdition(record);
            data[4] = Indexer.listYearOfPublication(record);
            dataList.add(data);
        }
        dto.setData(dataList);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION");
    } finally {
        closeConnection(con);
    }
    return dto;
}

---

From source file:biblivre3.administration.ReportsDAO.java

public AssetHoldingDto getAssetHoldingFullReportData() {
    AssetHoldingDto dto = new AssetHoldingDto();
    Connection con = null;/*from  w  w w  .j a  v  a2  s . co  m*/
    try {
        con = getDataSource().getConnection();
        String sql = " SELECT H.holding_serial, H.asset_holding, R.record FROM cataloging_holdings H INNER JOIN cataloging_biblio R "
                + " ON R.record_serial = H.record_serial WHERE H.database = 0 " + " ORDER BY H.asset_holding ";

        final PreparedStatement pst = con.prepareStatement(sql);
        pst.setFetchSize(100);

        final ResultSet rs = pst.executeQuery();
        List<String[]> dataList = new ArrayList<String[]>();
        while (rs.next()) {
            Record record = MarcUtils.iso2709ToRecord(rs.getBytes("record"));
            String assetHolding = rs.getString("asset_holding");
            String serial = rs.getString("holding_serial");
            String[] data = new String[7];
            data[0] = serial;
            data[1] = assetHolding;
            data[2] = Indexer.listOneTitle(record);
            data[3] = Indexer.listPrimaryAuthor(record);
            data[4] = Indexer.listFormattedLocation(record);
            data[5] = Indexer.listEdition(record);
            data[6] = Indexer.listYearOfPublication(record);
            dataList.add(data);
        }
        dto.setData(dataList);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION");
    } finally {
        closeConnection(con);
    }
    return dto;
}

---

From source file:biblivre3.administration.ReportsDAO.java

public SummaryReportDto getSummaryReportData(Database database) {
    SummaryReportDto dto = new SummaryReportDto();
    Connection con = null;// w  w w.j a  v a2 s  .  c om
    try {
        con = getDataSource().getConnection();
        String sql = "SELECT record, record_serial FROM cataloging_biblio WHERE database = ?;";
        String countSql = "SELECT count(holding_serial) FROM cataloging_holdings WHERE record_serial = ?;";

        final PreparedStatement pst = con.prepareStatement(sql);
        pst.setFetchSize(100);

        final PreparedStatement count = con.prepareStatement(countSql);

        pst.setInt(1, database.ordinal());

        final ResultSet rs = pst.executeQuery();
        List<String[]> dataList = new ArrayList<String[]>();
        while (rs.next()) {
            Record record = MarcUtils.iso2709ToRecord(rs.getBytes("record"));
            String[] data = new String[8];
            data[0] = Indexer.listOneTitle(record);
            data[1] = Indexer.listAuthors(record);
            data[2] = Indexer.listIsbn(record);
            data[3] = Indexer.listEditor(record);// editora(50)
            data[4] = Indexer.listYearOfPublication(record);// ano(20)
            data[5] = Indexer.listEdition(record);
            data[6] = Indexer.listLocation(record)[0];

            count.setInt(1, rs.getInt("record_serial"));
            ResultSet countRs = count.executeQuery();
            countRs.next();
            data[7] = countRs.getString(1);
            dataList.add(data);
        }
        dto.setData(dataList);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION");
    } finally {
        closeConnection(con);
    }
    return dto;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• »