Example usage for java.sql PreparedStatement setMaxRows

Introduction

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

Prototype

void setMaxRows(int max) throws SQLException;

Source Link

Document

Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number.

Usage

From source file:com.dynamobi.ws.util.DB.java

@SuppressWarnings(value = { "unchecked" })
public static <T extends DBLoader> void execute(String query, T obj, List<T> list) {
    Connection conn = null;/*from  ww  w.ja  va  2s . c  om*/
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        conn = getConnection();
        ps = conn.prepareStatement(query);
        ps.setMaxRows(0);
        if (ps.execute()) {
            rs = ps.getResultSet();
        }

        while (rs != null && rs.next()) {
            obj.loadRow(rs);
            if (list != null) {
                list.add((T) obj.copy());
            }
        }
        obj.finalize();
    } catch (SQLException ex) {
        obj.exception(ex);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        if (conn != null) {
            releaseConnection();
        }
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex3) {
            ex3.printStackTrace();
        }
    }
}

---

From source file:com.xqdev.sql.MLSQL.java

private static void configureStatement(PreparedStatement stmt, int maxRows, int queryTimeout, int maxFieldSize)
        throws SQLException {
    if (maxRows != -1) {
        stmt.setMaxRows(maxRows);
    }/*from w  w w . j  a v a2  s  .c  om*/
    if (queryTimeout != -1) {
        stmt.setQueryTimeout(queryTimeout);
    }
    if (maxFieldSize != -1) {
        stmt.setMaxFieldSize(maxFieldSize);
    }
}

---

From source file:gobblin.data.management.retention.sql.SqlBasedRetentionPoc.java

/**
 *
 * The test inserts a few test snapshots. A query is issued to retrieve the two most recent snapshots
 *///w w w.  ja  va2 s  .  co m
@Test
public void testKeepLast2Snapshots() throws Exception {

    insertSnapshot(new Path("/data/databases/Forum/Comments/1453743903767-PT-440505235"));
    insertSnapshot(new Path("/data/databases/Forum/Comments/1453830569999-PT-440746131"));
    insertSnapshot(new Path("/data/databases/Forum/Comments/1453860526464-PT-440847244"));
    insertSnapshot(new Path("/data/databases/Forum/Comments/1453889323804-PT-440936752"));

    // Derby does not support LIMIT keyword. The suggested workaround is to setMaxRows in the PreparedStatement
    PreparedStatement statement = connection.prepareStatement("SELECT name FROM Snapshots ORDER BY ts desc");
    statement.setMaxRows(2);

    ResultSet rs = statement.executeQuery();

    // Snapshots to be retained
    rs.next();
    Assert.assertEquals(rs.getString(1), "1453889323804-PT-440936752");
    rs.next();
    Assert.assertEquals(rs.getString(1), "1453860526464-PT-440847244");

}

---

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

@Override
public Date getSettingModificationDate(final String key, final String type) {
    return getJdbcTemplate().query(new PreparedStatementCreator() {

        @Override/*from  www .  ja v a  2 s  . c  om*/
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement stmt = con.prepareStatement(sqlGetDate);
            stmt.setMaxRows(1);
            stmt.setString(1, key);
            stmt.setString(2, type);
            return stmt;
        }
    }, new ResultSetExtractor<Date>() {

        @Override
        public Date extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getTimestamp(1);
            }
            return null;
        }
    });
}

---

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

@Override
public Date getMostRecentModificationDate() {
    return getJdbcTemplate().query(new PreparedStatementCreator() {

        @Override// w  w  w.  j  a v a2s .  co  m
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement stmt = con.prepareStatement(sqlGetMostRecentDate);
            stmt.setMaxRows(1);
            final int mask = SettingFlag.maskForSet(EnumSet.of(SettingFlag.IgnoreModificationDate));
            stmt.setInt(1, mask);
            stmt.setInt(2, mask);
            return stmt;
        }
    }, new ResultSetExtractor<Date>() {

        @Override
        public Date extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getTimestamp(1);
            }
            return null;
        }
    });
}

---

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

/**
 * Find datum entities that have not been uploaded to a specific
 * destination./*from   w ww  .j  a  v a2  s .  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:com.tascape.qa.th.db.H2Handler.java

@Override
protected int getTestCaseId(TestCase test) throws SQLException {
    LOG.info("Query for id of test case {} ", test.format());
    try (Connection conn = this.getConnection()) {
        final String sql = "SELECT * FROM " + TestCase.TABLE_NAME + " WHERE " + TestCase.SUITE_CLASS
                + " = ? AND " + TestCase.TEST_CLASS + " = ? AND " + TestCase.TEST_METHOD + " = ? AND "
                + TestCase.TEST_DATA_INFO + " = ? AND " + TestCase.TEST_DATA + " = ?";

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, test.getSuiteClass());
        stmt.setString(2, test.getTestClass());
        stmt.setString(3, test.getTestMethod());
        stmt.setString(4, test.getTestDataInfo());
        stmt.setString(5, test.getTestData());
        stmt.setMaxRows(1);

        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            return rs.getInt(TestCase.TEST_CASE_ID);
        }//from w w w.  j av  a  2 s .com
    }

    try (Connection conn = this.getConnection()) {
        final String sql = "INSERT INTO " + TestCase.TABLE_NAME + " (" + TestCase.SUITE_CLASS + ", "
                + TestCase.TEST_CLASS + ", " + TestCase.TEST_METHOD + ", " + TestCase.TEST_DATA_INFO + ", "
                + TestCase.TEST_DATA + ") VALUES (?,?,?,?,?);";

        PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, test.getSuiteClass());
        stmt.setString(2, test.getTestClass());
        stmt.setString(3, test.getTestMethod());
        stmt.setString(4, test.getTestDataInfo());
        stmt.setString(5, test.getTestData());
        int i = stmt.executeUpdate();
    }

    try (Connection conn = this.getConnection()) {
        final String sql = "SELECT * FROM " + TestCase.TABLE_NAME + " WHERE " + TestCase.SUITE_CLASS
                + " = ? AND " + TestCase.TEST_CLASS + " = ? AND " + TestCase.TEST_METHOD + " = ? AND "
                + TestCase.TEST_DATA_INFO + " = ? AND " + TestCase.TEST_DATA + " = ? ORDER BY "
                + TestCase.TEST_CASE_ID + " DESC;";

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, test.getSuiteClass());
        stmt.setString(2, test.getTestClass());
        stmt.setString(3, test.getTestMethod());
        stmt.setString(4, test.getTestDataInfo());
        stmt.setString(5, test.getTestData());
        stmt.setMaxRows(1);

        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            return rs.getInt(TestCase.TEST_CASE_ID);
        }
    }
    throw new SQLException();
}

---

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

/**
 * Make the SQL request/*from w  ww. j  ava 2 s  .c  om*/
 * 
 * @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:com.tascape.reactor.report.MySqlBaseBean.java

public List<Map<String, Object>> getSuiteResultDetailHistory(long startTime, long stopTime, int numberOfEntries,
        String suiteName, boolean invisibleIncluded) throws NamingException, SQLException {
    String sr = "SELECT " + SuiteResult.SUITE_RESULT_ID + " FROM " + SuiteResult.TABLE_NAME + " WHERE "
            + SuiteResult.START_TIME + " > ?" + " AND " + SuiteResult.STOP_TIME + " < ?" + " AND "
            + SuiteResult.SUITE_NAME + " = ?";
    if (!invisibleIncluded) {
        sr += " AND NOT " + SuiteResult.INVISIBLE_ENTRY;
    }/*from w  w  w  .j a va  2s.  c  o m*/
    sr += " ORDER BY " + SuiteResult.START_TIME + " DESC;";

    String tr = "SELECT * FROM " + CaseResult.TABLE_NAME + " WHERE " + CaseResult.EXECUTION_RESULT + " IN ("
            + sr + ")" + " ORDER BY " + SuiteResult.START_TIME + " DESC;";
    try (Connection conn = this.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement(tr);
        stmt.setLong(1, startTime);
        stmt.setLong(2, stopTime);
        if (suiteName != null && !suiteName.isEmpty()) {
            stmt.setString(3, suiteName);
        }
        LOG.trace("{}", stmt);
        stmt.setMaxRows(numberOfEntries);
        ResultSet rs = stmt.executeQuery();
        return this.dumpResultSetToList(rs);
    }
}

---

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testSelect() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");

    Statement stmt = conn.createStatement();
    try {/*w w w  .  j a  v a2  s .  c  o  m*/
        stmt.executeQuery("SELECT ID, NAME FROM BIT9");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT \"id\", \"name\" FROM \"employee\"");
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println("id=" + id + ", name=" + name);
    }
    rs.close();
    stmt.close();

    //        conn.setAutoCommit(false);
    PreparedStatement pstmt = conn
            .prepareStatement("SELECT \"id\", \"name\" FROM \"employee\" WHERE \"id\" >= ?");
    pstmt.setMaxRows(3);
    //        pstmt.setFetchSize(3);
    pstmt.setInt(1, 7);
    for (int i = 0; i < 2; i++) {
        rs = pstmt.executeQuery();
        rs.next();
        rs.close();
    }
    rs = pstmt.executeQuery();
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println("id=" + id + ", name=" + name);
    }
    rs.close();
    pstmt.close();

    conn.close();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• »