Example usage for java.sql ResultSet FETCH

Introduction

In this page you can find the example usage for java.sql ResultSet FETCH_FORWARD.

Prototype

int FETCH_FORWARD

To view the source code for java.sql ResultSet FETCH_FORWARD.

Click Source Link

Document

The constant indicating that the rows in a result set will be processed in a forward direction; first-to-last.

Usage

From source file:org.inquidia.kettle.plugins.snowflakeplugin.bulkloader.SnowflakeBulkLoader.java

/**
 * Runs the commands to put the data to the Snowflake stage, the copy command to load the table, and finally
 * a commit to commit the transaction.// w  w  w. j  a  v a2  s  .  co m
 * @throws KettleDatabaseException
 * @throws KettleFileException
 * @throws KettleValueException
 */
private void loadDatabase() throws KettleDatabaseException, KettleFileException, KettleValueException {
    boolean filesUploaded = false;
    boolean endsWithSlash = environmentSubstitute(meta.getWorkDirectory()).endsWith("\\")
            || environmentSubstitute(meta.getWorkDirectory()).endsWith("/");
    String SQL = "PUT 'file://" + environmentSubstitute(meta.getWorkDirectory()).replaceAll("\\\\", "/")
            + (endsWithSlash ? "" : "/") + environmentSubstitute(meta.getTargetTable()) + "_"
            + meta.getFileDate() + "_*' " + meta.getStage(this) + ";";

    logDebug("Executing SQL " + SQL);
    data.db.execStatement(SQL);

    String copySQL = meta.getCopyStatement(this, data.getPreviouslyOpenedFiles());
    logDebug("Executing SQL " + copySQL);
    ResultSet resultSet = data.db.openQuery(copySQL, null, null, ResultSet.FETCH_FORWARD, false);
    RowMetaInterface rowMeta = data.db.getReturnRowMeta();

    Object[] row = data.db.getRow(resultSet);
    int rowsLoaded = 0;
    int rowsLoadedField = rowMeta.indexOfValue("rows_loaded");
    int rowsError = 0;
    int errorField = rowMeta.indexOfValue("errors_seen");
    logBasic("====================== Bulk Load Results======================");
    int rowNum = 1;
    while (row != null) {
        logBasic("---------------------- Row " + rowNum + " ----------------------");
        for (int i = 0; i < rowMeta.getFieldNames().length; i++) {
            logBasic(rowMeta.getFieldNames()[i] + " = " + rowMeta.getString(row, i));
        }

        if (rowsLoadedField >= 0) {
            rowsLoaded += rowMeta.getInteger(row, rowsLoadedField);
        }

        if (errorField >= 0) {
            rowsError += rowMeta.getInteger(row, errorField);
        }

        rowNum++;
        row = data.db.getRow(resultSet);
    }
    data.db.closeQuery(resultSet);
    setLinesOutput(rowsLoaded);
    setLinesRejected(rowsError);

    data.db.execStatement("commit");

}

From source file:org.onexus.collection.store.mysql.internal.MysqlCollectionStore.java

@Override
public Statement createReadStatement(Connection dataConn) throws SQLException {
    Statement st = super.createReadStatement(dataConn);

    st.setFetchDirection(ResultSet.FETCH_FORWARD);
    st.setFetchSize(Integer.MIN_VALUE);

    return st;/* w  w  w  .j a  v  a2 s.c  o m*/
}

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

/**
 * Open a query on the database with a set of parameters stored in a Kettle Row
 *
 * @param sql//from  w  w w  .ja v a2  s. c o  m
 *          The SQL to launch with question marks (?) as placeholders for the parameters
 * @param params
 *          The parameters or null if no parameters are used.
 * @data the parameter data to open the query with
 * @return A JDBC ResultSet
 * @throws KettleDatabaseException
 *           when something goes wrong with the query.
 */
public ResultSet openQuery(String sql, RowMetaInterface params, Object[] data) throws KettleDatabaseException {
    return openQuery(sql, params, data, ResultSet.FETCH_FORWARD);
}

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

public ResultSet openQuery(PreparedStatement ps, RowMetaInterface params, Object[] data)
        throws KettleDatabaseException {
    ResultSet res;//www .j a 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

/**
 * Reads the result of an SQL query into an ArrayList
 *
 * @param sql// ww  w . ja  v a  2 s.c  o  m
 *          The SQL to launch
 * @param limit
 *          <=0 means unlimited, otherwise this specifies the maximum number of rows read.
 * @param monitor
 *          The progress monitor to update while getting the rows.
 * @return An ArrayList of rows.
 * @throws KettleDatabaseException
 *           if something goes wrong.
 */
public List<Object[]> getRows(String sql, int limit, ProgressMonitorListener monitor)
        throws KettleDatabaseException {

    return getRows(sql, null, null, ResultSet.FETCH_FORWARD, false, limit, monitor);
}

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

@Override
public FastIDSet getCandidates(String label) throws IOException {
    Connection conn = null;//w w  w  . j ava2s. 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  av  a  2  s. 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;/*  w  w w . ja  va 2 s .c om*/
    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//  w ww .j  a  va 2s.  c  o  m
 * @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 void processAdvancedProps(Map<String, String> props) throws DataServiceFault {
    if (props == null) {
        return;//from  w w w  . j  a v a 2 s .  c  o  m
    }
    /* process fetch direction */
    String fetchDirectionProp = props.get(RDBMS.FETCH_DIRECTION);
    if (!DBUtils.isEmptyString(fetchDirectionProp)) {
        fetchDirectionProp = fetchDirectionProp.trim();
        if (AdvancedSQLProps.FETCH_DIRECTION_FORWARD.equals(fetchDirectionProp)) {
            this.fetchDirection = ResultSet.FETCH_FORWARD;
        } else if (AdvancedSQLProps.FETCH_DIRECTION_REVERSE.equals(fetchDirectionProp)) {
            this.fetchDirection = ResultSet.FETCH_REVERSE;
        } else {
            throw new DataServiceFault("Invalid fetch direction: " + fetchDirectionProp
                    + ", valid values are {'" + AdvancedSQLProps.FETCH_DIRECTION_FORWARD + "', '"
                    + AdvancedSQLProps.FETCH_DIRECTION_REVERSE + "'}");
        }
        this.hasFetchDirection = true;
    } else {
        this.hasFetchDirection = false;
    }
    /* process fetch size */
    String fetchSizeProp = props.get(RDBMS.FETCH_SIZE);
    if (!DBUtils.isEmptyString(fetchSizeProp)) {
        fetchSizeProp = fetchSizeProp.trim();
        try {
            this.fetchSize = Integer.parseInt(fetchSizeProp);
        } catch (NumberFormatException e) {
            throw new DataServiceFault(e,
                    "Invalid fetch size: " + fetchSizeProp + ", fetch size should be an integer");
        }
        this.hasFetchSize = true;
    } else {
        this.hasFetchSize = false;
    }
    /* process max field size */
    String maxFieldSizeProp = props.get(RDBMS.MAX_FIELD_SIZE);
    if (!DBUtils.isEmptyString(maxFieldSizeProp)) {
        maxFieldSizeProp = maxFieldSizeProp.trim();
        try {
            this.maxFieldSize = Integer.parseInt(maxFieldSizeProp);
            if (this.maxFieldSize <= 0) {
                throw new DataServiceFault("Invalid maximum field size: " + maxFieldSizeProp
                        + ", maximum field size should be a positive integer");
            }
        } catch (NumberFormatException e) {
            throw new DataServiceFault(e, "Invalid maximum field size: " + maxFieldSizeProp
                    + ", maximum field size should be a positive integer");
        }
        this.hasMaxFieldSize = true;
    } else {
        this.hasMaxFieldSize = false;
    }
    /* process max rows */
    String maxRowsProp = props.get(RDBMS.MAX_ROWS);
    if (!DBUtils.isEmptyString(maxRowsProp)) {
        maxRowsProp = maxRowsProp.trim();
        try {
            this.maxRows = Integer.parseInt(maxRowsProp);
            if (this.maxRows <= 0) {
                throw new DataServiceFault(
                        "Invalid maximum rows: " + maxRowsProp + ", maximum rows should be a positive integer");
            }
        } catch (NumberFormatException e) {
            throw new DataServiceFault(e,
                    "Invalid maximum rows: " + maxRowsProp + ", maximum rows should be a positive integer");
        }
        this.hasMaxRows = true;
    } else {
        this.hasMaxRows = false;
    }
    /* process query timeout */
    String queryTimeoutProp = props.get(RDBMS.QUERY_TIMEOUT);
    if (!DBUtils.isEmptyString(queryTimeoutProp)) {
        queryTimeoutProp = queryTimeoutProp.trim();
        try {
            this.queryTimeout = Integer.parseInt(queryTimeoutProp);
            if (this.queryTimeout <= 0) {
                throw new DataServiceFault(
                        "Invalid query timeout: " + queryTimeoutProp + ", query timeout be a positive integer");
            }
        } catch (NumberFormatException e) {
            throw new DataServiceFault(e,
                    "Invalid query timeout: " + queryTimeoutProp + ", query timeout be a positive integer");
        }
        this.hasQueryTimeout = true;
    } else {
        this.hasQueryTimeout = false;
    }
    /* auto commit */
    /* first check local auto commit setting */
    String autoCommitProp = props.get(RDBMS.AUTO_COMMIT);
    if (!DBUtils.isEmptyString(autoCommitProp)) {
        autoCommitProp = autoCommitProp.trim();
        try {
            boolean acBool = Boolean.parseBoolean(autoCommitProp);
            if (acBool) {
                this.autoCommit = AutoCommit.AUTO_COMMIT_ON;
            } else {
                this.autoCommit = AutoCommit.AUTO_COMMIT_OFF;
            }
        } catch (Exception e) {
            throw new DataServiceFault(e,
                    "Invalid autocommit value: " + autoCommitProp + ", autocommit should be a boolean value");
        }
    } else {
        /* global auto commit setting */
        this.autoCommit = this.getConfig().getAutoCommit();
    }
    /* force stored procedure */
    String forceStoredProc = props.get(RDBMS.FORCE_STORED_PROC);
    if (!DBUtils.isEmptyString(forceStoredProc)) {
        this.forceStoredProc = Boolean.parseBoolean(forceStoredProc);
    }
    /* force JDBC batch requests */
    String forceJDBCBatchRequests = props.get(RDBMS.FORCE_JDBC_BATCH_REQUESTS);
    if (!DBUtils.isEmptyString(forceJDBCBatchRequests)) {
        this.forceJDBCBatchReqs = Boolean.parseBoolean(forceJDBCBatchRequests);
    }
}

Example usage for java.sql ResultSet FETCH_FORWARD

Introduction

Prototype

Document

Usage