Example usage for java.sql PreparedStatement setFetchDirection

Introduction

In this page you can find the example usage for java.sql PreparedStatement setFetchDirection.

Prototype

void setFetchDirection(int direction) throws SQLException;

Source Link

Document

Gives the driver a hint as to the direction in which rows will be processed in ResultSet objects created using this Statement object.

Usage

From source file:net.solarnetwork.node.dao.jdbc.AbstractJdbcDatumDao.java

/**
 * Find datum entities that have not been uploaded to a specific
 * destination.//from  w  w w .j  a v a  2s.c  o  m
 * 
 * <p>
 * This executes SQL from the {@code findForUploadSql} property. It uses the
 * {@code maxFetchForUpload} property to limit the number of rows returned,
 * so the call may not return all rows available from the database (this is
 * to conserve memory and process the data in small batches).
 * </p>
 * 
 * @param destination
 *        the destination to look for
 * @param rowMapper
 *        a {@link RowMapper} implementation to instantiate entities from
 *        found rows
 * @return the matching rows, never <em>null</em>
 */
protected List<T> findDatumNotUploaded(final RowMapper<T> rowMapper) {
    List<T> result = getJdbcTemplate().query(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            String sql = getSqlResource(SQL_RESOURCE_FIND_FOR_UPLOAD);
            if (log.isTraceEnabled()) {
                log.trace("Preparing SQL to find datum not uploaded [" + sql + "] with maxFetchForUpload ["
                        + maxFetchForUpload + ']');
            }
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setFetchDirection(ResultSet.FETCH_FORWARD);
            ps.setFetchSize(maxFetchForUpload);
            ps.setMaxRows(maxFetchForUpload);
            return ps;
        }
    }, rowMapper);
    if (log.isDebugEnabled()) {
        log.debug("Found " + result.size() + " datum entities not uploaded");
    }
    return result;
}

---

From source file:fr.cnes.sitools.datasource.jdbc.business.SitoolsDataSource.java

/**
 * Make the SQL request starting at offset and returning maxrows records
 * //from   ww w  . j  av  a2s.  c  o m
 * 
 * @param sql
 *          SQL request
 * @param maxrows
 *          the maximal number of rows
 * @param offset
 *          the offset in rows
 * @return ResultSet
 */
public ResultSet limitedQuery(String sql, int maxrows, int offset) {
    Connection conn = null;
    ResultSet rs = null;
    try {
        String sqlCompleted = addLimitOffset(sql, maxrows, offset);
        conn = getConnection();

        if (conn == null) {
            LOG.log(Level.WARNING, "getConnection failed");
            return null;
        }

        // modif inspir par le LAM
        LOG.log(Level.INFO, "Limited query = " + sqlCompleted);
        // set autocommit false to enable the use of cursors
        conn.setAutoCommit(false);
        // Cela permet d'utiliser les mcanismes de streaming de JDBC
        PreparedStatement prep = conn.prepareStatement(sqlCompleted, ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY); // ,

        if (prep == null) {
            LOG.log(Level.WARNING, "prepareStatement failed");
            return null;
        }
        int fetchSize = SitoolsSettings.getInstance().getInt("Starter.JDBC_FETCH_SIZE");
        // modif inspire par le LAM
        // On positionne la taille de chaque streaming
        prep.setFetchSize(fetchSize);
        prep.setFetchDirection(ResultSet.FETCH_FORWARD);

        rs = prep.executeQuery();

        return new DBResultSet(rs, prep, conn);

    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, null, ex);
        closeConnection(conn);
        closeResultSet(rs);
        conn = null;
    } catch (RuntimeException ex) {
        LOG.log(Level.SEVERE, null, ex);
        closeConnection(conn);
        closeResultSet(rs);
    } catch (Exception ex) {
        LOG.log(Level.SEVERE, null, ex);
        closeConnection(conn);
        closeResultSet(rs);
        conn = null;
    }
    return null;
}

---

From source file:org.apache.openjpa.jdbc.sql.SQLBuffer.java

/**
 * Create and populate the parameters of a prepred statement using the
 * SQL in this buffer and the given fetch configuration.
 *//*from w w  w. j av  a 2s  . com*/
public PreparedStatement prepareStatement(Connection conn, JDBCFetchConfiguration fetch, int rsType,
        int rsConcur) throws SQLException {
    if (rsType == -1 && fetch == null)
        rsType = ResultSet.TYPE_FORWARD_ONLY;
    else if (rsType == -1)
        rsType = fetch.getResultSetType();
    if (rsConcur == -1)
        rsConcur = ResultSet.CONCUR_READ_ONLY;

    PreparedStatement stmnt;
    if (rsType == ResultSet.TYPE_FORWARD_ONLY && rsConcur == ResultSet.CONCUR_READ_ONLY)
        stmnt = conn.prepareStatement(getSQL());
    else
        stmnt = conn.prepareStatement(getSQL(), rsType, rsConcur);
    try {
        setParameters(stmnt);
        if (fetch != null) {
            if (fetch.getFetchBatchSize() > 0)
                stmnt.setFetchSize(_dict.getBatchFetchSize(fetch.getFetchBatchSize()));
            if (rsType != ResultSet.TYPE_FORWARD_ONLY && fetch.getFetchDirection() != ResultSet.FETCH_FORWARD)
                stmnt.setFetchDirection(fetch.getFetchDirection());
        }
        return stmnt;
    } catch (SQLException se) {
        try {
            stmnt.close();
        } catch (SQLException se2) {
        }
        throw se;
    }
}

---

From source file:org.bidtime.dbutils.QueryRunnerEx.java

/**
 * Calls query after checking the parameters to ensure nothing is null.
 * @param conn The connection to use for the query call.
 * @param closeConn True if the connection should be closed, false otherwise.
 * @param sql The SQL statement to execute.
 * @param params An array of query replacement parameters.  Each row in
 * this array is one set of batch replacement values.
 * @return The results of the query./*from   w w w  . jav  a2 s  .c o m*/
 * @throws SQLException If there are database or parameter errors.
 */
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
        throws SQLException {
    if (conn == null) {
        throw new SQLException("Null connection");
    }

    if (sql == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null SQL statement");
    }

    if (rsh == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null ResultSetHandler");
    }

    PreparedStatement stmt = null;
    ResultSet rs = null;
    T result = null;
    long startTime = System.currentTimeMillis();
    try {
        //stmt = this.prepareStatement(conn, sql);
        stmt = (PreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(StmtParams.getInstance().getFetchSize());
        stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
        stmt.setQueryTimeout(StmtParams.getInstance().getStmtQueryTimeOut());
        this.fillStatement(stmt, params);
        rs = this.wrap(stmt.executeQuery());
        result = rsh.handle(rs);
    } catch (SQLException e) {
        this.rethrow(e, sql, params);
    } finally {
        try {
            close(rs);
        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }
        if (LogSelectSql.logInfoOrDebug()) {
            LogSelectSql.logFormatTimeNow(startTime, sql, params);
        }
    }

    return result;
}

---

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

public ResultSet openQuery(PreparedStatement ps, RowMetaInterface params, Object[] data)
        throws KettleDatabaseException {
    ResultSet res;//from  www  .  j a v a 2s  .com

    // 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.plista.kornakapi.core.storage.MySqlStorage.java

@Override
public FastIDSet getCandidates(String label) throws IOException {
    Connection conn = null;//  ww  w  .  ja v  a  2 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;/*  w  w w.  j a  v  a2s .  co 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 w w w.j av  a 2s  .  co m
    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.springframework.batch.item.database.AbstractCursorItemReader.java

/**
 * Prepare the given JDBC Statement (or PreparedStatement or
 * CallableStatement), applying statement settings such as fetch size, max
 * rows, and query timeout. @param stmt the JDBC Statement to prepare
 *
 * @param stmt {@link java.sql.PreparedStatement} to be configured
 *
 * @throws SQLException if interactions with provided stmt fail
 *
 * @see #setFetchSize//from  ww  w.j av  a 2s  . c om
 * @see #setMaxRows
 * @see #setQueryTimeout
 */
protected void applyStatementSettings(PreparedStatement stmt) throws SQLException {
    if (fetchSize != VALUE_NOT_SET) {
        stmt.setFetchSize(fetchSize);
        stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
    }
    if (maxRows != VALUE_NOT_SET) {
        stmt.setMaxRows(maxRows);
    }
    if (queryTimeout != VALUE_NOT_SET) {
        stmt.setQueryTimeout(queryTimeout);
    }
}

---

From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java

private PreparedStatement createProcessedPreparedStatement(int queryType, InternalParamCollection params,
        Connection conn) throws DataServiceFault {
    try {/* ww  w .j av  a  2s .  c om*/
        /*
         * lets see first if there's already a batch prepared statement
         * created
         */
        boolean inTheMiddleOfABatch = false;
        PreparedStatement stmt = this.getBatchPreparedStatement();
        int currentParamCount = this.getParamCount();

        /* create a new prepared statement */
        if (stmt == null) {
            /* batch mode is not supported for dynamic queries */
            Object[] result = this.processDynamicQuery(this.getQuery(), params);
            String dynamicSQL = (String) result[0];
            currentParamCount = (Integer) result[1];
            String processedSQL = this.createProcessedQuery(dynamicSQL, params, currentParamCount);
            if (queryType == SQLQuery.DS_QUERY_TYPE_NORMAL) {
                if (this.isReturnGeneratedKeys()) {
                    if (this.getKeyColumns() != null) {
                        stmt = conn.prepareStatement(processedSQL, this.getKeyColumns());
                    } else {
                        stmt = conn.prepareStatement(processedSQL, Statement.RETURN_GENERATED_KEYS);
                    }
                } else {
                    stmt = conn.prepareStatement(processedSQL);
                }
            } else if (queryType == SQLQuery.DS_QUERY_TYPE_STORED_PROC) {
                stmt = conn.prepareCall(processedSQL);
            } else {
                throw new DataServiceFault("Unsupported query type: " + queryType);
            }
        } else {
            inTheMiddleOfABatch = true;
        }

        if (!inTheMiddleOfABatch) {
            /* set query timeout */
            if (this.isHasQueryTimeout()) {
                stmt.setQueryTimeout(this.getQueryTimeout());
            }
            /* adding the try catch to avoid setting this for jdbc drivers that do not implement this method. */
            try {
                /* set fetch direction */
                if (this.isHasFetchDirection()) {
                    stmt.setFetchDirection(this.getFetchDirection());
                }
                /* set fetch size - user's setting */
                if (this.isHasFetchSize()) {
                    stmt.setFetchSize(this.getFetchSize());
                } else {
                    /*
                     * stream data by sections - avoid the full result set
                     * to be loaded to memory, and only stream if there
                     * aren't any OUT parameters, MySQL fails in the
                     * scenario of streaming and OUT parameters, so the
                     * possibility is there for other DBMSs
                     */
                    if (!this.hasOutParams() && this.getFetchSizeProperty().isChangeFetchSize()) {
                        stmt.setFetchSize(this.getFetchSizeProperty().getFetchSize());
                    }
                }
            } catch (Throwable e) {
                log.debug("Exception while setting fetch size: " + e.getMessage(), e);
            }
            /* set max field size */
            if (this.isHasMaxFieldSize()) {
                stmt.setMaxFieldSize(this.getMaxFieldSize());
            }
            /* set max rows */
            if (this.isHasMaxRows()) {
                stmt.setMaxRows(this.getMaxRows());
            }
        }

        int currentOrdinal = 0;
        InternalParam param;
        ParamValue value;
        for (int i = 1; i <= currentParamCount; i++) {
            param = params.getParam(i);
            value = param.getValue();
            /*
             * handle array values, if value is null, this param has to be
             * an OUT param
             */
            if (value != null && value.getValueType() == ParamValue.PARAM_VALUE_ARRAY) {
                for (ParamValue arrayElement : value.getArrayValue()) {
                    this.setParamInPreparedStatement(stmt, param,
                            arrayElement == null ? null : arrayElement.toString(), queryType, currentOrdinal);
                    currentOrdinal++;
                }
            } else { /* scalar value */
                this.setParamInPreparedStatement(stmt, param, value != null ? value.getScalarValue() : null,
                        queryType, currentOrdinal);
                currentOrdinal++;
            }
        }

        /* if we are in JDBC batch processing mode, batch it! */
        if (this.isJDBCBatchRequest()) {
            stmt.addBatch();
        }

        return stmt;
    } catch (SQLException e) {
        throw new DataServiceFault(e, "Error in 'createProcessedPreparedStatement'");
    }
}

---