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:org.jumpmind.db.sql.JdbcSqlTransaction.java

public <T> List<T> query(final String sql, final ISqlRowMapper<T> mapper, final Object[] args,
        final int[] types) {
    return executeCallback(new IConnectionCallback<List<T>>() {
        public List<T> execute(Connection c) throws SQLException {
            PreparedStatement st = null;
            ResultSet rs = null;//from   w  ww.  j  a  va 2s.com
            try {
                logSql(sql, args);
                st = c.prepareStatement(sql);
                st.setQueryTimeout(jdbcSqlTemplate.getSettings().getQueryTimeout());
                if (args != null) {
                    jdbcSqlTemplate.setValues(st, args, types,
                            jdbcSqlTemplate.getLobHandler().getDefaultHandler());
                }
                st.setFetchSize(jdbcSqlTemplate.getSettings().getFetchSize());
                rs = st.executeQuery();
                List<T> list = new ArrayList<T>();
                while (rs.next()) {
                    Row row = JdbcSqlReadCursor.getMapForRow(rs,
                            jdbcSqlTemplate.getSettings().isReadStringsAsBytes());
                    T value = mapper.mapRow(row);
                    list.add(value);
                }
                return list;
            } finally {
                JdbcSqlTemplate.close(rs);
                JdbcSqlTemplate.close(st);
            }
        }
    });
}

---

From source file:org.openmrs.contrib.databaseexporter.ExportContext.java

public <T> T executeQuery(String sql, ResultSetHandler<T> handler, Object... params) {
    Connection connection = null;
    try {//  w ww .  j a v  a2 s .co  m
        if (getConfiguration().getLogSql() == Boolean.TRUE) {
            log("SQL: " + sql
                    + (params != null && params.length > 0 ? " [" + Util.toString(params) + "]" : ""));
        }
        QueryRunner runner = new QueryRunner() {
            protected PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException {
                PreparedStatement ps = super.prepareStatement(conn, sql);
                ps.setFetchSize(Integer.MIN_VALUE);
                return ps;
            }
        };
        connection = DbUtil.openConnection(configuration);
        T result = runner.query(connection, sql, handler, params);
        if (getConfiguration().getLogSql() == Boolean.TRUE) {
            log("RESULT: " + result);
        }
        return result;
    } catch (Exception e) {
        throw new RuntimeException("Unable to execute query: " + sql, e);
    } finally {
        DbUtil.closeConnection(connection);
    }
}

---

From source file:org.pentaho.di.core.database.Database.java

public ResultSet openQuery(PreparedStatement ps, RowMetaInterface params, Object[] data)
        throws KettleDatabaseException {
    ResultSet res;/*  w  ww  . ja  v a 2 s  . co m*/

    // Create a Statement
    try {
        log.snap(Metrics.METRIC_DATABASE_OPEN_QUERY_START, databaseMeta.getName());

        log.snap(Metrics.METRIC_DATABASE_SQL_VALUES_START, databaseMeta.getName());
        setValues(params, data, ps); // set the parameters!
        log.snap(Metrics.METRIC_DATABASE_SQL_VALUES_STOP, databaseMeta.getName());

        if (canWeSetFetchSize(ps)) {
            int maxRows = ps.getMaxRows();
            int fs = Const.FETCH_SIZE <= maxRows ? maxRows : Const.FETCH_SIZE;
            // mysql have some restriction on fetch size assignment
            if (databaseMeta.isMySQLVariant()) {
                setMysqlFetchSize(ps, fs, maxRows);
            } else {
                // other databases seems not.
                ps.setFetchSize(fs);
            }

            ps.setFetchDirection(ResultSet.FETCH_FORWARD);
        }

        if (rowlimit > 0 && databaseMeta.supportsSetMaxRows()) {
            ps.setMaxRows(rowlimit);
        }

        log.snap(Metrics.METRIC_DATABASE_EXECUTE_SQL_START, databaseMeta.getName());
        res = ps.executeQuery();
        log.snap(Metrics.METRIC_DATABASE_EXECUTE_SQL_STOP, databaseMeta.getName());

        // MySQL Hack only. It seems too much for the cursor type of operation on
        // MySQL, to have another cursor opened
        // to get the length of a String field. So, on MySQL, we ignore the length
        // of Strings in result rows.
        //
        log.snap(Metrics.METRIC_DATABASE_GET_ROW_META_START, databaseMeta.getName());
        rowMeta = getRowInfo(res.getMetaData(), databaseMeta.isMySQLVariant(), false);
        log.snap(Metrics.METRIC_DATABASE_GET_ROW_META_STOP, databaseMeta.getName());
    } catch (SQLException ex) {
        throw new KettleDatabaseException("ERROR executing query", ex);
    } catch (Exception e) {
        throw new KettleDatabaseException("ERROR executing query", e);
    } finally {
        log.snap(Metrics.METRIC_DATABASE_OPEN_QUERY_STOP, databaseMeta.getName());
    }

    return res;
}

---

From source file:org.pentaho.di.core.database.Database.java

void setMysqlFetchSize(PreparedStatement ps, int fs, int getMaxRows)
        throws SQLException, KettleDatabaseException {
    if (databaseMeta.isStreamingResults() && getDatabaseMetaData().getDriverMajorVersion() == 3) {
        ps.setFetchSize(Integer.MIN_VALUE);
    } else if (fs <= getMaxRows) {
        // PDI-11373 do not set fetch size more than max rows can returns
        ps.setFetchSize(fs);/*from   w w w. jav a  2 s  .c om*/
    }
}

---

From source file:org.plista.kornakapi.core.storage.MySqlStorage.java

@Override
public FastIDSet getCandidates(String label) throws IOException {
    Connection conn = null;//from  w w  w  .  j  a  v  a2  s  .c  o  m
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {

        FastIDSet candidates = new FastIDSet();

        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(GET_CANDIDATES_QUERY, ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
        stmt.setFetchSize(1000);
        stmt.setString(1, label);

        rs = stmt.executeQuery();

        while (rs.next()) {
            candidates.add(rs.getLong(1));
        }

        return candidates;

    } catch (SQLException e) {
        throw new IOException(e);
    } finally {
        IOUtils.quietClose(rs, stmt, conn);
    }
}

---

From source file:org.plista.kornakapi.core.storage.MySqlStorage.java

public LinkedList<String> getAllLabels() throws IOException {
    Connection conn = null;/*from  www. j  a  v  a2  s.  c  o m*/
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {

        LinkedList<String> candidates = new LinkedList<String>();

        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(GET_LABELS, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
        stmt.setFetchSize(1000);
        rs = stmt.executeQuery();

        while (rs.next()) {
            candidates.add(rs.getString(1));
        }

        return candidates;

    } catch (SQLException e) {
        throw new IOException(e);
    } finally {
        IOUtils.quietClose(rs, stmt, conn);
    }
}

---

From source file:org.plista.kornakapi.core.storage.MySqlStorage.java

public String getItemsLabel(long itemid) throws IOException {
    Connection conn = null;//from ww w  .j  a  v  a  2 s  . com
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {

        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(GET_ITEMSLABEL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setLong(1, itemid);
        stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
        stmt.setFetchSize(10);
        rs = stmt.executeQuery();
        String label = null;
        if (rs.next()) {
            label = rs.getString(1);
        }

        return label;

    } catch (SQLException e) {
        throw new IOException(e);
    } finally {
        IOUtils.quietClose(rs, stmt, conn);
    }
}

---

From source file:org.pvalsecc.jdbc.JdbcUtilities.java

/**
 * Allows to do something within an existing connection. Takes care of closing everything
 * even in case of errors./*from   www .ja  v  a  2 s .c o m*/
 */
public static void runSelectQuery(String description, String sqlStatement, Connection conn, SelectTask task)
        throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    boolean queryDisplayed = false;

    try {
        //noinspection JDBCResourceOpenedButNotSafelyClosed
        stmt = conn.prepareStatement(sqlStatement, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

        stmt.setFetchSize(500); //for using cursors in the select

        task.setupStatement(stmt);

        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Executing SQL : " + sqlStatement + " (" + description + ")");
            queryDisplayed = true;
        }

        long beginTime = System.currentTimeMillis();

        //noinspection JDBCResourceOpenedButNotSafelyClosed
        rs = stmt.executeQuery();

        long middleTime = System.currentTimeMillis();
        if (TIMING_LOGGER.isDebugEnabled()) {
            TIMING_LOGGER.debug("Time " + description + " (executeQuery): "
                    + UnitUtilities.toElapsedTime(middleTime - beginTime));
        }

        task.run(rs);

        if (TIMING_LOGGER.isDebugEnabled()) {
            TIMING_LOGGER.debug("Time " + description + " (read): "
                    + UnitUtilities.toElapsedTime(System.currentTimeMillis() - middleTime));
        }

    } catch (SQLException ex) {
        if (!queryDisplayed) {
            //add the query if it was not displayed previously (will help to debug)
            LOGGER.error(sqlStatement);
        }
        throw ex;
    } finally {
        safeClose(rs);
        safeClose(stmt);
    }
}

---

From source file:org.quartz.impl.jdbcjobstore.StdJDBCDelegate.java

/**
 * <p>//from w  w  w .j a  v  a  2  s  .  co  m
 * Select the next trigger which will fire to fire between the two given timestamps 
 * in ascending order of fire time, and then descending by priority.
 * </p>
 * 
 * @param conn
 *          the DB Connection
 * @param noLaterThan
 *          highest value of <code>getNextFireTime()</code> of the triggers (exclusive)
 * @param noEarlierThan 
 *          highest value of <code>getNextFireTime()</code> of the triggers (inclusive)
 *          
 * @return A (never null, possibly empty) list of the identifiers (Key objects) of the next triggers to be fired.
 */
public List selectTriggerToAcquire(Connection conn, long noLaterThan, long noEarlierThan) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List nextTriggers = new LinkedList();
    try {
        ps = conn.prepareStatement(rtp(SELECT_NEXT_TRIGGER_TO_ACQUIRE));

        // Try to give jdbc driver a hint to hopefully not pull over 
        // more than the few rows we actually need.
        ps.setFetchSize(5);
        ps.setMaxRows(5);

        ps.setString(1, STATE_WAITING);
        ps.setBigDecimal(2, new BigDecimal(String.valueOf(noLaterThan)));
        ps.setBigDecimal(3, new BigDecimal(String.valueOf(noEarlierThan)));
        rs = ps.executeQuery();

        while (rs.next() && nextTriggers.size() < 5) {
            nextTriggers.add(new Key(rs.getString(COL_TRIGGER_NAME), rs.getString(COL_TRIGGER_GROUP)));
        }

        return nextTriggers;
    } finally {
        closeResultSet(rs);
        closeStatement(ps);
    }
}

---

From source file:org.rimudb.generic.IterativeQuery.java

/**
 * Load the iterator with the SQL Statement and fetch the result set.
 * /*  w  ww  . jav a 2s.c o  m*/
 * @param queryIterator AbstractQueryIterator
 * @param parameters Object[]
 * @throws RimuDBException
 */
private void loadIterator(AbstractQueryIterator queryIterator, Object parameters[]) throws RimuDBException {
    if (getSQL() == null || getSQL().trim().length() == 0) {
        throw new RimuDBException("SQL statement has not been set");
    }
    Connection con = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    // Parse the SQL to replace table names
    String psql = parse(getSQL());

    try {
        con = getDatabase().getDatabaseConnection();
        queryIterator.setConnection(con);

        stmt = con.prepareStatement(psql);

        stmt.setFetchSize(getFetchSize());

        queryIterator.setPreparedStatement(stmt);

        if (parameters != null) {
            boolean charUsesSetObject = getDatabase().getSQLAdapter().isCharUsesSetObject();
            for (int i = 0; i < parameters.length; i++) {
                RecordBinder.bindValue(stmt, i + 1, parameters[i], charUsesSetObject);
            }
        }

        rs = stmt.executeQuery();
        queryIterator.setResultSet(rs);

    } catch (Exception e) {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqle) {
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqle) {
            }
            stmt = null;
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException sqle) {
            }
            con = null;
        }

        StringBuilder sb = new StringBuilder();
        sb.append("SQL statement=");
        sb.append(psql);
        // Log the parameters for the SQL statement that failed
        if (parameters != null) {
            for (int i = 0; i < parameters.length; i++) {
                sb.append(" Parameters[" + i + "]=" + parameters[i]);
            }
        }
        sb.append(" ");
        // Include all the sql info in the exception
        throw new RimuDBException(e, sb.toString());

    }

}

---

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