Example usage for java.sql PreparedStatement setMaxRows

List of usage examples for java.sql PreparedStatement setMaxRows

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. 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:org.quartz.impl.jdbcjobstore.StdJDBCDelegate.java

/**
 * <p>/*from   w w w . ja  v  a2s .c om*/
 * 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.rti.zcore.dar.DarSessionSubject.java

/**
 * initialises SessionPatient.//from  www. j  a va 2  s .  com
 * @param conn
 */
public void init(Connection conn) {

    ResultSet rs = null;
    try {
        String sql = "SELECT age_category  " + "FROM patient  " + "WHERE patient.id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setMaxRows(1);
        ps.setLong(1, this.getId());
        rs = ps.executeQuery();
    } catch (Exception ex) {
        log.error(ex);
    }

    try {
        while (rs.next()) {
            Integer ageCategory = rs.getInt("age_category");
            if (ageCategory != null && ageCategory == 3284) {
                this.setChild(true);
            }
        }
        rs.close();
    } catch (SQLException e) {
        log.debug(e);
    }
}

From source file:org.rti.zcore.dar.report.ZEPRSUtils.java

/**
 * Fetches most recent regimen for patient.
 * @param conn/*www. j  a v a2s  .  c o  m*/
 * @param patientId
 * @return ResultSet
 * @throws ServletException
 */
public static ResultSet getPatientArtRegimen(Connection conn, Long patientId) throws ServletException {
    ResultSet rs = null;
    try {
        String sql = "SELECT encounter.id AS id, regimen.code AS code, regimen.name AS name, regimen.id AS regimenId "
                + "FROM art_regimen, encounter, regimen  " + "WHERE encounter.id = art_regimen.id "
                + "AND art_regimen.regimen_1 = regimen.id " + "AND encounter.patient_id = ? "
                + "ORDER BY encounter.id DESC";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setMaxRows(1);
        ps.setLong(1, patientId);
        rs = ps.executeQuery();
    } catch (Exception ex) {
        log.error(ex);
    }
    return rs;
}

From source file:org.rti.zcore.dar.report.ZEPRSUtils.java

/**
 * Fetches the most recent patient regimen during the date range.
 * @param conn/* w  ww  .  j a  v a  2  s .  c o m*/
 * @param patientId
 * @param beginDate
 * @param endDate
 * @return
 * @throws ServletException
 */
public static ResultSet getPatientArtRegimen(Connection conn, Long patientId, Date beginDate, Date endDate)
        throws ServletException {
    ResultSet rs = null;
    String dateRange = "AND date_visit >= ? AND date_visit <= ? ";
    if (endDate == null) {
        dateRange = "AND date_visit = ?";
    }
    try {
        String sql = "SELECT encounter.id AS id, regimen.code AS code, regimen.name AS name, regimen.id AS regimenId "
                + "FROM art_regimen, encounter, regimen  " + "WHERE encounter.id = art_regimen.id "
                + "AND art_regimen.regimen_1 = regimen.id " + "AND encounter.patient_id = ? " + dateRange
                + " ORDER BY encounter.id DESC";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setMaxRows(1);
        ps.setLong(1, patientId);
        ps.setDate(2, beginDate);
        if (endDate != null) {
            ps.setDate(3, endDate);
        }
        rs = ps.executeQuery();
    } catch (Exception ex) {
        log.error(ex);
    }
    return rs;
}

From source file:org.sakaiproject.webservices.SakaiReport.java

protected String getQueryAsString(String query, Object[] args, int rowCount, String type) {

    Connection conn = null;//w  w w  .  ja  va2s  .c  om
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = sqlService.borrowConnection();
        conn.setReadOnly(true);

        ps = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        if (rowCount > 0) {
            ps.setMaxRows(rowCount);
        }

        for (int i = 0; i < args.length; i++) {
            if (args[i] instanceof String) {
                ps.setString(i + 1, (String) args[i]);
            } else if (args[i] instanceof java.util.Date) {
                // select * from sakai_event where event_date between to_date('2001-12-12 12:12','YYYY-MM-DD HH24:MI') and to_date('2017-12-12 12:12','YYYY-MM-DD HH24:MI')
                if (sqlService.getVendor().equals("oracle")) {
                    ps.setString(i + 1, df.format(args[i]));
                    // select * from sakai_event where event_date between '2001-12-12 12:12' and '2017-12-12 12:12';
                } else {
                    ps.setString(i + 1, df.format(args[i]));
                }
            }
        }
        LOG.info("preparing query: " + ps.toString());

        rs = ps.executeQuery();
        //return toJsonString(rs);
        if (type == TYPE_CSV) {
            return stripInvalidXmlCharacters(toCsvString(rs));
        }
        if (type == TYPE_CSV_WITH_HEADER_ROW) {
            return stripInvalidXmlCharacters(toCsvString(rs, true));
        }

        if (type == TYPE_JSON) {
            return stripInvalidXmlCharacters(toJsonString(rs));
        }

        return Xml.writeDocumentToString(toDocument(rs));

    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
        throw new RuntimeException(e.getMessage(), e);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }

        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
}

From source file:org.seasar.dbflute.s2dao.jdbc.TnStatementFactoryImpl.java

protected void doReflectStatementOptions(PreparedStatement ps, StatementConfig actualConfig) {
    if (actualConfig == null || !actualConfig.hasStatementOptions()) {
        return;/*w  ww  . j a  v  a  2 s  .c  om*/
    }
    try {
        if (actualConfig.hasQueryTimeout()) {
            final Integer queryTimeout = actualConfig.getQueryTimeout();
            if (isInternalDebugEnabled()) {
                _log.debug("...Setting queryTimeout of statement: " + queryTimeout);
            }
            ps.setQueryTimeout(queryTimeout);
        }
        if (actualConfig.hasFetchSize()) {
            final Integer fetchSize = actualConfig.getFetchSize();
            if (isInternalDebugEnabled()) {
                _log.debug("...Setting fetchSize of statement: " + fetchSize);
            }
            ps.setFetchSize(fetchSize);
        }
        if (actualConfig.hasMaxRows()) {
            final Integer maxRows = actualConfig.getMaxRows();
            if (isInternalDebugEnabled()) {
                _log.debug("...Setting maxRows of statement: " + maxRows);
            }
            ps.setMaxRows(maxRows);
        }
    } catch (SQLException e) {
        handleSQLException(e, ps);
    }
}

From source file:org.silverpeas.core.comment.dao.jdbc.JDBCCommentRequester.java

public List<Comment> getLastComments(Connection con, String instanceId, int count) throws SQLException {
    String query = "SELECT commentId, commentOwnerId, commentCreationDate, "
            + "commentModificationDate, commentComment, resourceType, resourceId, instanceId "
            + "FROM sb_comment_comment where instanceId = ? ORDER BY commentCreationDate DESC, "
            + "commentId DESC";
    PreparedStatement stmt = null;
    ResultSet rs = null;// w w w  . j a  v a  2 s . c  om
    List<Comment> comments = new ArrayList<>(count);
    try {
        stmt = con.prepareStatement(query);
        stmt.setString(1, instanceId);
        if (count > 0) {
            stmt.setMaxRows(count);
        }
        rs = stmt.executeQuery();
        while (rs.next()) {
            CommentPK pk = new CommentPK(String.valueOf(rs.getInt(COMMENT_ID)));
            pk.setComponentName(rs.getString(INSTANCE_ID));
            WAPrimaryKey resourceId = new CommentPK(rs.getString(RESOURCE_ID));
            try {
                Comment cmt = new Comment(pk, rs.getString(RESOURCE_TYPE), resourceId,
                        rs.getInt(COMMENT_OWNER_ID), "", rs.getString(COMMENT_TEXT),
                        parseDate(rs.getString(COMMENT_CREATION_DATE)),
                        parseDate(rs.getString(COMMENT_MODIFICATION_DATE)));
                comments.add(cmt);
            } catch (ParseException ex) {
                throw new SQLException(ex.getMessage(), ex);
            }
        }
    } finally {
        DBUtil.close(rs, stmt);
    }

    return comments;
}

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.  jav  a  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.springframework.jdbc.core.JdbcTemplateTests.java

private void doTestStrings(JdbcTemplateCallback jdbcTemplateCallback, boolean usePreparedStatement,
        Integer fetchSize, Integer maxRows, Integer queryTimeout, Object argument) throws Exception {

    String sql = "SELECT FORENAME FROM CUSTMR";
    String[] results = { "rod", "gary", " portia" };

    class StringHandler implements RowCallbackHandler {
        private List list = new LinkedList();

        public void processRow(ResultSet rs) throws SQLException {
            list.add(rs.getString(1));//ww  w  . j ava2  s .  com
        }

        public String[] getStrings() {
            return (String[]) list.toArray(new String[list.size()]);
        }
    }

    MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getString(1);
    ctrlResultSet.setReturnValue(results[0]);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getString(1);
    ctrlResultSet.setReturnValue(results[1]);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getString(1);
    ctrlResultSet.setReturnValue(results[2]);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    if (fetchSize != null) {
        mockStatement.setFetchSize(fetchSize.intValue());
    }
    if (maxRows != null) {
        mockStatement.setMaxRows(maxRows.intValue());
    }
    if (queryTimeout != null) {
        mockStatement.setQueryTimeout(queryTimeout.intValue());
    }
    if (argument != null) {
        mockStatement.setObject(1, argument);
    }
    if (usePreparedStatement) {
        mockStatement.executeQuery();
    } else {
        mockStatement.executeQuery(sql);
    }
    ctrlStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    if (usePreparedStatement) {
        mockConnection.prepareStatement(sql);
    } else {
        mockConnection.createStatement();
    }
    ctrlConnection.setReturnValue(mockStatement);

    ctrlResultSet.replay();
    ctrlStatement.replay();
    replay();

    StringHandler sh = new StringHandler();
    JdbcTemplate template = new JdbcTemplate();
    template.setDataSource(mockDataSource);
    if (fetchSize != null) {
        template.setFetchSize(fetchSize.intValue());
    }
    if (maxRows != null) {
        template.setMaxRows(maxRows.intValue());
    }
    if (queryTimeout != null) {
        template.setQueryTimeout(queryTimeout.intValue());
    }
    jdbcTemplateCallback.doInJdbcTemplate(template, sql, sh);

    // Match
    String[] forenames = sh.getStrings();
    assertTrue("same length", forenames.length == results.length);
    for (int i = 0; i < forenames.length; i++) {
        assertTrue("Row " + i + " matches", forenames[i].equals(results[i]));
    }

    ctrlResultSet.verify();
    ctrlStatement.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testConnectionCallbackWithStatementSettings() throws Exception {
    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    mockConnection.prepareStatement("some SQL");
    ctrlConnection.setReturnValue(mockStatement, 1);
    mockStatement.setFetchSize(10);/*from  www .j  a va 2 s.  c  o m*/
    ctrlStatement.setVoidCallable(1);
    mockStatement.setMaxRows(20);
    ctrlStatement.setVoidCallable(1);
    mockStatement.close();
    ctrlStatement.setVoidCallable(1);
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);
    Object result = template.execute(new ConnectionCallback() {
        public Object doInConnection(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement("some SQL");
            ps.close();
            assertSame(mockConnection, new PlainNativeJdbcExtractor().getNativeConnection(con));
            return "test";
        }
    });

    assertEquals("test", result);
}