Java PreparedStatement.setMaxRows方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void closeQueryStatement(PreparedStatement ps) throws SQLException {

		try {
			//work around a bug in all known connection pools....
			if ( ps.getMaxRows()!=0 ) ps.setMaxRows(0);
			if ( ps.getQueryTimeout()!=0 ) ps.setQueryTimeout(0);
		}
		catch (Exception e) {
			log.warn("exception clearing maxRows/queryTimeout", e);
			ps.close(); //just close it; do NOT try to return it to the pool!
			return; //NOTE: early exit!
		}
		
		closeStatement(ps);
		if ( lastQuery==ps ) lastQuery = null;
		
	}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
public void testSetMaxRows() throws SQLException    {
    // Add 10 rows
    PreparedStatement ins = conn.prepareCall("{call InsertOrders(?, ?, ?, ?, ?, ?, ?, ?)}");
    for (int i = 0; i < 10; i++) {
   	 ins.setLong(1, i);
     ins.setLong(2, i);
     ins.setLong(3, i);
     ins.setLong(4, i);
     ins.setLong(5, i);
     ins.setLong(6, i);
     ins.setLong(7, i);
     ins.setLong(8, i);
        ins.execute();
    }

    // check for our 10 rows
    PreparedStatement cs = conn.prepareCall("{call SelectOrders}");
    ResultSet rs = cs.executeQuery();
    int count = 0;
    while (rs.next()) {
        count++;
    }
    assertEquals(10, count);

    // constrain to 5 and try again.
    cs.setMaxRows(5);
    assertEquals(5, cs.getMaxRows());
    rs = cs.executeQuery();
    count = 0;
    while (rs.next()) {
        count++;
    }
    assertEquals(5, count);

    // Verify 0 gets us everything again
    cs.setMaxRows(0);
    assertEquals(0, cs.getMaxRows());
    rs = cs.executeQuery();
    count = 0;
    while (rs.next()) {
        count++;
    }
    assertEquals(10, count);

    // Go for spot-on
    cs.setMaxRows(10);
    assertEquals(10, cs.getMaxRows());
    rs = cs.executeQuery();
    count = 0;
    while (rs.next()) {
        count++;
    }
    assertEquals(10, count);
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Executes one query using a newly created PreparedStatement, setting its maxRows limit, and tests if the results
 * count is the expected.
 */
private void testBug71396PrepStatementCheck(Connection testConn, String query, int expRowCount, int maxRows) throws SQLException {
    PreparedStatement chkPStmt;

    chkPStmt = testConn.prepareStatement(query);
    if (maxRows > 0) {
        chkPStmt.setMaxRows(maxRows);
    }
    testBug71396PrepStatementCheck(chkPStmt, query, expRowCount);
    chkPStmt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * <p>
 * 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)
 * @param maxCount 
 *          maximum number of trigger keys allow to acquired in the returning list.
 *          
 * @return A (never null, possibly empty) list of the identifiers (Key objects) of the next triggers to be fired.
 */
public List<TriggerKey> selectTriggerToAcquire(Connection conn, long noLaterThan, long noEarlierThan, int maxCount)
    throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<TriggerKey> nextTriggers = new LinkedList<TriggerKey>();
    try {
        ps = conn.prepareStatement(rtp(SELECT_NEXT_TRIGGER_TO_ACQUIRE));
        
        // Set max rows to retrieve
        if (maxCount < 1)
            maxCount = 1; // we want at least one trigger back.
        ps.setMaxRows(maxCount);
        
        // Try to give jdbc driver a hint to hopefully not pull over more than the few rows we actually need.
        // Note: in some jdbc drivers, such as MySQL, you must set maxRows before fetchSize, or you get exception!
        ps.setFetchSize(maxCount);
        
        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() <= maxCount) {
            nextTriggers.add(triggerKey(
                    rs.getString(COL_TRIGGER_NAME),
                    rs.getString(COL_TRIGGER_GROUP)));
        }
        
        return nextTriggers;
    } finally {
        closeResultSet(rs);
        closeStatement(ps);
    }      
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * <p>
 * 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);
    }      
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void checkStatementExecute(Connection connection,
    boolean prepare, int maxRowCount) throws SQLException {
  final String sql = "select * from (\n"
      + "  values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)";
  final Statement statement;
  final ResultSet resultSet;
  final ParameterMetaData parameterMetaData;
  if (prepare) {
    final PreparedStatement ps = connection.prepareStatement(sql);
    statement = ps;
    ps.setMaxRows(maxRowCount);
    parameterMetaData = ps.getParameterMetaData();
    assertTrue(ps.execute());
    resultSet = ps.getResultSet();
  } else {
    statement = connection.createStatement();
    statement.setMaxRows(maxRowCount);
    parameterMetaData = null;
    assertTrue(statement.execute(sql));
    resultSet = statement.getResultSet();
  }
  if (parameterMetaData != null) {
    assertThat(parameterMetaData.getParameterCount(), equalTo(0));
  }
  final ResultSetMetaData metaData = resultSet.getMetaData();
  assertEquals(2, metaData.getColumnCount());
  assertEquals("C1", metaData.getColumnName(1));
  assertEquals("C2", metaData.getColumnName(2));
  for (int i = 0; i < maxRowCount || (maxRowCount == 0 && i < 3); i++) {
    assertTrue(resultSet.next());
  }
  assertFalse(resultSet.next());
  resultSet.close();
  statement.close();
  connection.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private PreparedStatement createLocationsStatement(LocationsRequest request, Connection conn, QueryColumnsInfo queryInfo) throws Exception
{
	Geometry geom = request.getGeometry();
	Envelope bbox = request.getBBox();

	byte[] geomBytes = geometryToWKB(geom, bbox);

	// at the end, we add virtual column to store the exact distance. 
	String query = "SELECT " + queryInfo.getQuery1Columns() + " FROM " + _tableName;
	
	String whereCondition = "";
	
	String searchCondition = buildSearchFilter(request.getSearchFilter());
	if (!Helper.isEmpty(searchCondition))
		whereCondition += searchCondition;
	
	String stateText = String.format("SELECT %s FROM ORS_FindLocations('(%s) as tmp', '%s', ?, %.3f, %d) AS %s", queryInfo.getQuery2Columns(), query, whereCondition, request.getRadius(), request.getLimit(), queryInfo.getReturnTable());

	if (request.getSortType() != LocationsResultSortType.NONE)
	{
		if (request.getSortType() == LocationsResultSortType.CATEGORY)
			stateText += " ORDER BY category";
		else if (request.getSortType() == LocationsResultSortType.DISTANCE)
			stateText += " ORDER BY distance";
	}

	PreparedStatement statement = conn.prepareStatement(stateText);    
	statement.setMaxRows(request.getLimit());
	if (geomBytes != null)
		statement.setBytes(1, geomBytes);

	return statement;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void testBug36478() throws Exception {

        createTable("testBug36478", "(`limit` varchar(255) not null primary key, id_limit INT, limit1 INT, maxlimit2 INT)");

        this.stmt.execute("INSERT INTO testBug36478 VALUES ('bahblah',1,1,1)");
        this.stmt.execute("INSERT INTO testBug36478 VALUES ('bahblah2',2,2,2)");
        this.pstmt = this.conn.prepareStatement("select 1 FROM testBug36478");

        this.pstmt.setMaxRows(1);
        this.rs = this.pstmt.executeQuery();
        this.rs.first();
        assertTrue(this.rs.isFirst());
        assertTrue(this.rs.isLast());

        this.pstmt = this.conn.prepareStatement("select `limit`, id_limit, limit1, maxlimit2 FROM testBug36478");
        this.pstmt.setMaxRows(0);
        this.rs = this.pstmt.executeQuery();
        this.rs.first();
        assertTrue(this.rs.isFirst());
        assertFalse(this.rs.isLast());

        //SSPS
        Connection _conn = null;
        PreparedStatement s = null;
        try {
            Properties props = new Properties();
            props.setProperty("useServerPrepStmts", "true");

            _conn = getConnectionWithProps(props);
            s = _conn.prepareStatement("select 1 FROM testBug36478");

            s.setMaxRows(1);
            ResultSet _rs = s.executeQuery();
            _rs.first();
            assertTrue(_rs.isFirst());
            assertTrue(_rs.isLast());

            s = _conn.prepareStatement("select `limit`, id_limit, limit1, maxlimit2 FROM testBug36478");
            s.setMaxRows(0);
            _rs = s.executeQuery();
            _rs.first();
            assertTrue(_rs.isFirst());
            assertFalse(_rs.isLast());

        } finally {
            if (s != null) {
                s.close();
            }
            if (_conn != null) {
                _conn.close();
            }
        }

    }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void setMaxRows(PreparedStatement statement) throws SQLException {
	if ( LimitHelper.hasMaxRows( selection ) ) {
		statement.setMaxRows( selection.getMaxRows() + convertToFirstRowValue( LimitHelper.getFirstRow( selection ) ) );
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Use JDBC API to limit the number of rows returned by the SQL query if necessary
 */
private void setMaxRows(final PreparedStatement st, final RowSelection selection) throws SQLException {
	if ( hasMaxRows(selection) ) {
		st.setMaxRows( selection.getMaxRows().intValue() + getFirstRow(selection) );
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void writeData(String destTableName, Connection destConn)
        throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        // To download the entire result set it is supposedly much
        // faster to do forward only. This means that we can only
        // call rs.next() and not rs.first(). So we must make sure
        // that rs.next() is not called before we start downloading
        // the table. This is called in populateTable.
        pstmt = sourceConn.prepareStatement(sql,
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

        log.log(XLevel.DEBUG2, "Querying <SOURCE> data...");
        pstmt.setFetchSize(10000);
        pstmt.setMaxRows(0);

        stat.startInitialFetch();
        rs = pstmt.executeQuery();

        // If no data was found quit.
        if (rs == null) {
            log.warn("No data found in <SOURCE> using sql statement:" + sql);
            return;
        }
        stat.completeInitialFetch();

        log.log(XLevel.DEBUG2, "Processing <SOURCE> data...");

        log.log(XLevel.DEBUG2, "Populating table with data...");
        populateTable(destTableName, rs, destConn);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
        } catch (SQLException ignored) {
        }
    }

    stat.done();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Creates a new {@link MetaData} object for a given sql query.
 * At this time only one table in the from clause can be specified
 * due to a Oracle inefficiency in their JDBC driver meta data fetch.
 * @param sql
 * @param sourceConn
 * @return
 * @throws SQLException
 * @throws IOException
 */
public static MetaData getMeta(String sql, Connection sourceConn)
  throws SQLException, IOException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        // To download the entire result set it is supposedly much
        // faster to do forward only. This means that we can only
        // call rs.next() and not rs.first(). So we must make sure
        // that rs.next() is not called before we start downloading
        // the table. This is called in populateTable.
        pstmt = sourceConn.prepareStatement(sql,
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

        pstmt.setFetchSize(1);
        pstmt.setMaxRows(1);
        rs = pstmt.executeQuery();

        // If no data was found quit.
        if (rs == null) {
            log.warn("No data found in <SOURCE> using sql statement:" + sql);
            return null;
        }
        log.log(XLevel.DEBUG2, "Processing <SOURCE> data...");

        // Get the table's metadata
        ResultSetMetaData meta = rs.getMetaData();

        // For the result metadata set, we need to cache the count and
        // column names. Calls to getXXX for meta data can make calls
        // back to the database. We're pretty sure they're not going to
        // change while were running.
        int cols = meta.getColumnCount() + 1;
        ArrayList<String> sourceCols = new ArrayList<String>();
        for (int i = 1; i < cols; i++)
            sourceCols.add(meta.getColumnName(i));

        // XXX Oracle 10 Sucks!
        // Oracle does not implement the getTableName and getSchemaName
        // methods. Since they do not implement them, we have to parse
        // the query to determine the table name.
        String tableName = meta.getTableName(1);
        if (tableName.trim().length() == 0) {
            Pattern p = Pattern.compile(".*\\s+from\\s+\\w*?\\.?(\\w+).*",
                    Pattern.CASE_INSENSITIVE);
            Matcher m = p.matcher(sql);
            if (m.matches())
                tableName = m.group(1).toUpperCase();
        }

        MetaData metaData = new MetaData(tableName, meta, sourceConn.getMetaData());
        return metaData;
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
        } catch (SQLException ignored) {
        }
    }
}