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:fr.cnes.sitools.datasource.jdbc.business.SitoolsDataSource.java

/**
 * Make the SQL request//from   www.j  a  v  a  2s. co  m
 * 
 * @param sql
 *          SQL request
 * @param maxrows
 *          maximal number of rows
 * @param fetchSize
 *          fetching size
 * @return ResultSet
 * 
 * 
 */
public ResultSet basicQuery(String sql, int maxrows, int fetchSize) {
    Connection conn = null;
    ResultSet rs = null;
    try {

        conn = getConnection();

        PreparedStatement prep = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (maxrows > -1) {
            prep.setMaxRows(maxrows);
        }
        if (fetchSize > -1) {
            prep.setFetchSize(fetchSize);
        }

        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);
    }
    return null;
}

---

From source file:biblivre3.administration.ReportsDAO.java

public AssetHoldingByDateDto getAssetHoldingByDateReportData(String initialDate, String finalDate) {
    AssetHoldingByDateDto dto = new AssetHoldingByDateDto();
    Connection con = null;// w ww.  ja v a 2  s . c om
    try {
        con = getDataSource().getConnection();
        String sql = " SELECT H.asset_holding, to_char(H.created, 'DD/MM/YYYY'), R.record, H.record "
                + " FROM cataloging_holdings H INNER JOIN cataloging_biblio R "
                + " ON R.record_serial = H.record_serial WHERE H.database = 0 "
                + " AND H.created >= to_date(?, 'DD-MM-YYYY') " + " AND H.created <= to_date(?, 'DD-MM-YYYY') "
                + " ORDER BY H.created, H.asset_holding ";

        final PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, initialDate);
        pst.setString(2, finalDate);
        pst.setFetchSize(100);

        final ResultSet rs = pst.executeQuery();
        List<String[]> dataList = new ArrayList<String[]>();
        while (rs.next()) {
            Record record = MarcUtils.iso2709ToRecord(rs.getBytes(3));
            Record holding = MarcUtils.iso2709ToRecord(rs.getBytes(4));
            String assetHolding = rs.getString("asset_holding");
            String creationDate = rs.getString(2);
            String[] data = new String[6];
            data[0] = creationDate;
            data[1] = assetHolding;
            data[2] = Indexer.listOneTitle(record);
            data[3] = Indexer.listPrimaryAuthor(record);
            data[4] = Indexer.listYearOfPublication(record);
            data[5] = Indexer.listSourceAcquisitionDate(holding);
            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:com.commander4j.db.JDBPrinters.java

public LinkedList<JDBListData> getPrinterIDs() {
    LinkedList<JDBListData> intList = new LinkedList<JDBListData>();
    PreparedStatement stmt;
    ResultSet rs;/*w w  w. j a va  2  s .c om*/
    setErrorMessage("");
    Icon icon = new ImageIcon();
    int index = 0;

    try {
        stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement(
                Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBPrinters.getPrinterIDs"));
        stmt.setFetchSize(250);
        stmt.setString(1, "Y");
        rs = stmt.executeQuery();

        while (rs.next()) {
            getPropertiesfromResultSet(rs);
            icon = getPrinterIcon();
            JDBListData mld = new JDBListData(icon, index, true, rs.getString("printer_id"));
            intList.addLast(mld);
        }
        rs.close();
        stmt.close();

    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }

    return intList;
}

---

From source file:com.strider.datadefender.DatabaseAnonymizer.java

/**
 * Creates the SELECT query for key and update columns.
 * //from w w w .j av  a  2 s  .  co m
 * @param tableName
 * @param keys
 * @param columns
 * @return 
 */
private PreparedStatement getSelectQueryStatement(final IDBFactory dbFactory, final Table table,
        final Collection<String> keys, final Collection<String> columns) throws SQLException {

    final List<String> params = new LinkedList<>();
    final StringBuilder query = new StringBuilder("SELECT DISTINCT ");
    query.append(StringUtils.join(keys, ", ")).append(", ").append(StringUtils.join(columns, ", "))
            .append(" FROM ").append(table.getName());

    final List<Exclude> exclusions = table.getExclusions();
    if (exclusions != null) {
        String separator = " WHERE (";
        for (final Exclude exc : exclusions) {
            final String eq = exc.getEqualsValue();
            final String lk = exc.getLikeValue();
            final boolean nl = exc.isExcludeNulls();
            final String col = exc.getName();

            if (col != null && col.length() != 0) {
                if (eq != null) {
                    query.append(separator).append('(').append(col).append(" != ? OR ").append(col)
                            .append(" IS NULL)");
                    params.add(eq);
                    separator = AND;
                }
                if (lk != null && lk.length() != 0) {
                    query.append(separator).append('(').append(col).append(" NOT LIKE ? OR ").append(col)
                            .append(" IS NULL)");
                    params.add(lk);
                    separator = AND;
                }
                if (nl) {
                    query.append(separator).append(col).append(" IS NOT NULL");
                    separator = AND;
                }
            }
        }

        if (query.indexOf(" WHERE (") != -1) {
            separator = ") AND (";
        }

        for (final Exclude exc : exclusions) {
            final String neq = exc.getNotEqualsValue();
            final String nlk = exc.getNotLikeValue();
            final String col = exc.getName();

            if (neq != null) {
                query.append(separator).append(col).append(" = ?");
                separator = " OR ";
            }
            if (nlk != null && nlk.length() != 0) {
                query.append(separator).append(col).append(" LIKE ?");
                separator = " OR ";
            }

        }

        if (query.indexOf(" WHERE (") != -1) {
            query.append(')');
        }
    }

    final PreparedStatement stmt = dbFactory.getConnection().prepareStatement(query.toString(),
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    if (dbFactory.getVendorName().equalsIgnoreCase("mysql")) {
        stmt.setFetchSize(Integer.MIN_VALUE);
    }

    int paramIndex = 1;
    for (final String param : params) {
        stmt.setString(paramIndex, param);
        ++paramIndex;
    }

    log.debug("Querying for: " + query.toString());
    if (params.size() > 0) {
        log.debug("\t - with parameters: " + StringUtils.join(params, ','));
    }

    return stmt;
}

---

From source file:com.commander4j.db.JDBPrinters.java

public boolean getPrinterProperties() {
    PreparedStatement stmt;
    ResultSet rs;/*from   w  ww  . j  a v a 2  s  . com*/
    boolean result = false;
    setErrorMessage("");

    clear();

    try {
        stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID())
                .prepareStatement(Common.hostList.getHost(getHostID()).getSqlstatements()
                        .getSQL("JDBPrinters.getPrinterProperties"));
        stmt.setString(1, getPrinterID());
        stmt.setString(2, getGroupID());
        stmt.setFetchSize(1);
        rs = stmt.executeQuery();

        if (rs.next()) {
            getPropertiesfromResultSet(rs);
            result = true;
        } else {
            setErrorMessage("Invalid Printer ID [" + getPrinterID() + "]");
        }
        rs.close();
        stmt.close();
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }

    return result;
}

---

From source file:com.cloudera.sqoop.manager.SqlManager.java

/**
 * Executes an arbitrary SQL statement.// w  w w  . ja v a2 s  . c o m
 * @param stmt The SQL statement to execute
 * @param fetchSize Overrides default or parameterized fetch size
 * @return A ResultSet encapsulating the results or null on error
 */
protected ResultSet execute(String stmt, Integer fetchSize, Object... args) throws SQLException {
    // Release any previously-open statement.
    release();

    PreparedStatement statement = null;
    statement = this.getConnection().prepareStatement(stmt, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    if (fetchSize != null) {
        LOG.debug("Using fetchSize for next query: " + fetchSize);
        statement.setFetchSize(fetchSize);
    }
    this.lastStatement = statement;
    if (null != args) {
        for (int i = 0; i < args.length; i++) {
            statement.setObject(i + 1, args[i]);
        }
    }

    LOG.info("Executing SQL statement: " + stmt);
    return statement.executeQuery();
}

---

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

/**
 * Make the SQL request starting at offset and returning maxrows records
 * /*w  w  w .j a v  a 2  s .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:com.dsf.dbxtract.cdc.journal.JournalExecutor.java

/**
 * Gets reference data from journal table.
 * /*from w ww.  j a  v  a2s  .  c o  m*/
 * @param client
 * @param conn
 * @return a Map list with column names and values
 * @throws SQLException
 * @throws ConfigurationException
 */
private List<Map<String, Object>> getJournalKeys(CuratorFramework client, Connection conn)
        throws SQLException, ConfigurationException {

    List<Map<String, Object>> result = new ArrayList<>();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        // Obtem os dados do journal
        if (logger.isDebugEnabled())
            logger.debug(logPrefix + "getting journalized data");
        StringBuilder baseQuery = new StringBuilder("select * from ").append(handler.getJournalTable());
        if (JournalStrategy.WINDOW.equals(handler.getStrategy())) {
            Long lastWindowId = getLastWindowId(client);
            baseQuery.append(" where window_id > ?");
            ps = conn.prepareStatement(baseQuery.toString());
            ps.setLong(1, lastWindowId);
        } else {
            ps = conn.prepareStatement(baseQuery.toString());
        }
        ps.setFetchSize(handler.getBatchSize());
        ps.setMaxRows(handler.getBatchSize());
        rs = ps.executeQuery();
        copyResultsetToMap(rs, result);

    } finally {
        DBUtils.close(rs);
        DBUtils.close(ps);
    }
    return result;
}

---

From source file:com.taobao.tddl.jdbc.group.TGroupPreparedStatement.java

private PreparedStatement createPreparedStatementInternal(Connection conn, String sql) throws SQLException {
    PreparedStatement ps;
    if (autoGeneratedKeys != -1) {
        ps = conn.prepareStatement(sql, autoGeneratedKeys);
    } else if (columnIndexes != null) {
        ps = conn.prepareStatement(sql, columnIndexes);
    } else if (columnNames != null) {
        ps = conn.prepareStatement(sql, columnNames);
    } else {/*w  ww . jav a2 s.  c om*/
        int resultSetHoldability = this.resultSetHoldability;
        if (resultSetHoldability == -1) //setResultSetHoldability
            resultSetHoldability = conn.getHoldability();

        ps = conn.prepareStatement(sql, this.resultSetType, this.resultSetConcurrency, resultSetHoldability);
    }
    setBaseStatement(ps);
    ps.setQueryTimeout(queryTimeout); //setBaseStatement
    ps.setFetchSize(fetchSize);
    ps.setMaxRows(maxRows);

    return ps;
}

---

From source file:com.commander4j.db.JDBUserReport.java

public boolean getUserReportProperties() {
    boolean result = false;

    PreparedStatement stmt;
    ResultSet rs;/*from  ww  w .j  av a 2s .  c om*/
    setErrorMessage("");

    clear();

    try {
        stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement(
                Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBUserReport.getProperties"));
        stmt.setFetchSize(1);
        stmt.setString(1, getReportID());
        rs = stmt.executeQuery();

        if (rs.next()) {
            getPropertiesfromResultSet(rs);
            result = true;
        } else {
            setErrorMessage("Invalid User Report ID");
        }
        rs.close();
        stmt.close();
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }
    return result;
}

---

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