Java PreparedStatement.setFetchSize方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Executes an arbitrary SQL statement.
 * @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, Exception {
  // 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();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
public void testPreparedQueryWithFetch() throws SQLException {
  con.setAutoCommit(false);
  PreparedStatement ps = con.prepareStatement(
    "SELECT * FROM World",
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_READ_ONLY,
    ResultSet.FETCH_FORWARD);
  ps.setFetchSize(2);

  ResultSet resultSet = ps.executeQuery();
  int count = 4;
  while (resultSet.next()) {
    if (count-- == 0) {
      break;
    }
  }
  resultSet.close();
  ps.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public List<Map<String, Object>> selectAllMap(Connection connection, String sql, List<Object> args, MappingVo resultMap, Integer fetchSize)
		throws SQLException {
	PreparedStatement ps = connection.prepareStatement(sql);
	try {
		setParameters(ps, args);
		if (null != fetchSize && fetchSize.intValue() != ps.getFetchSize()) {
			ps.setFetchSize(fetchSize.intValue());
		}
		ResultSet rs = ps.executeQuery();
		return getResults(rs, resultMap);
	} finally {
		try {
			ps.close();
		} catch (SQLException e) {
			// ignore
		}
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Run some SQL, and tidy up afterwards.
 *
 * Note this assumes a predicate on the schema name will be present with a single parameter in position "1".
 *
 * @param sql The SQL to run.
 * @param handler The handler to handle the result-set.
 */
private void runSQL(String sql, ResultSetHandler handler) {
  try {
    PreparedStatement statement = connection.prepareStatement(sql);
    try {
      // We'll inevitably need a lot of meta data so may as well get it in big chunks.
      statement.setFetchSize(100);

      // pass through the schema name
      statement.setString(1, schemaName);

      ResultSet resultSet = statement.executeQuery();
      try {
        handler.handle(resultSet);
      } finally {
        resultSet.close();
      }
    } finally {
      statement.close();
    }
  } catch (SQLException sqle) {
    throw new RuntimeSqlException("Error running SQL: " + sql, sqle);
  }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Test Bug#77217 - ClassCastException when executing a PreparedStatement with Fabric (using a streaming result with timeout set)
 */
public void testBug77217() throws Exception {
    if (!this.isSetForFabricTest) {
        return;
    }

    this.conn = (FabricMySQLConnection) getNewDefaultDataSource().getConnection(this.username, this.password);
    this.conn.setServerGroupName("ha_config1_group");

    PreparedStatement ps = this.conn.prepareStatement("select ? from dual");
    ps.setFetchSize(Integer.MIN_VALUE);
    ps.setString(1, "abc");
    ResultSet rs = ps.executeQuery();
    rs.next();
    assertEquals("abc", rs.getString(1));
    rs.close();
    ps.close();
    this.conn.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/** Executes a query against the underlying data source. Returns a Collection of persistent objects.
 * The Statement object will be automatically closed, once the recordset has been completely traversed.
 * @param statement The query to execute.
 * @param classMetaData The ClassMetaData defintion to be used for molding the ResultSet into Persistent objects.
 * @param criteria The Criteria used for the query. This will provide the values to set the various flags on the Persistent object.
 * @param queryTimeout This will be used for setting the timeout value on the Statement object; zero means there is no limit.
 * @param pagingPlugin The IPagingPlugin implementation that may be used to return a page of Results.
 * @throws SQLException if any database error occurs.
 * @throws PostLoadFailedException if any error is thrown in the PostLoad trigger of the persistent object.
 * @throws DataSourceCursorRuntimeException if any error occurs while molding the row into the Persistent object.
 * @throws IOException if any error occurs in reading the data from the database.
 * @return a Collection of persistent objects.
 */
public Collection executeQuery(PreparedStatement statement, ClassMetaData classMetaData, Criteria criteria, int queryTimeout, IPagingPlugin pagingPlugin)
throws SQLException, PostLoadFailedException, DataSourceCursorRuntimeException, IOException {
    // The following sets the timeout; zero means there is no limit
    // The setting works in MS-Sql-Server only !!
    statement.setQueryTimeout(queryTimeout);

    // set the fetch size
    try {
        statement.setFetchSize(getHitlistSize().intValue());
    } catch (Throwable e) {
        // NOTE: The setFetchSize feature may not be implemented by all the drivers. eg.Postgresql.
        // so just ignore the exception
    }
    ResultSet resultSet = null;
    if (log.isInfoEnabled()) {
        log.info("Executing the Prepared Statement\n" + statement);
        long currentTimeMillis = System.currentTimeMillis();
        resultSet = statement.executeQuery();
        log.info("Elapsed:" + (System.currentTimeMillis() - currentTimeMillis));
    } else {
        resultSet = statement.executeQuery();
    }
    registerStatement(statement, resultSet);
    return new DataSourceCursor(this, statement, resultSet, classMetaData, criteria, pagingPlugin);
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Executes an arbitrary SQL statement.
 * @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();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests that streaming result sets are registered correctly.
 * 
 * @throws Exception
 *             if any errors occur
 */
public void testStreamingRegBug() throws Exception {
    createTable("StreamingRegBug", "( DUMMYID INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
    this.stmt.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
    this.stmt.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
    this.stmt.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
    this.stmt.executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");

    PreparedStatement streamStmt = null;

    try {
        streamStmt = this.conn.prepareStatement("SELECT DUMMYID, DUMMYNAME FROM StreamingRegBug ORDER BY DUMMYID", java.sql.ResultSet.TYPE_FORWARD_ONLY,
                java.sql.ResultSet.CONCUR_READ_ONLY);
        streamStmt.setFetchSize(Integer.MIN_VALUE);

        this.rs = streamStmt.executeQuery();

        while (this.rs.next()) {
            this.rs.getString(1);
        }

        this.rs.close(); // error occurs here
    } catch (SQLException sqlEx) {

    } finally {
        if (streamStmt != null) {
            try {
                streamStmt.close();
            } catch (SQLException exWhileClose) {
                exWhileClose.printStackTrace();
            }
        }
    }
}
 

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包/类
@Test public void testFetchSize() throws Exception {
  Connection connection = ljs();

  Statement statement = connection.createStatement();
  statement.setFetchSize(101);
  assertEquals(statement.getFetchSize(), 101);

  PreparedStatement preparedStatement =
      connection.prepareStatement("select * from (values (1, 'a')) as tbl1 (c1, c2)");
  preparedStatement.setFetchSize(1);
  assertEquals(preparedStatement.getFetchSize(), 1);
}
 

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) {
        }
    }
}
 

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包/类
public void testRowFetch() throws Exception {
    if (versionMeetsMinimum(5, 0, 5)) {
        createTable("testRowFetch", "(field1 int)");

        this.stmt.executeUpdate("INSERT INTO testRowFetch VALUES (1)");

        Connection fetchConn = null;

        Properties props = new Properties();
        props.setProperty("useCursorFetch", "true");

        try {
            fetchConn = getConnectionWithProps(props);

            PreparedStatement fetchStmt = fetchConn.prepareStatement("SELECT field1 FROM testRowFetch WHERE field1=1");
            fetchStmt.setFetchSize(10);
            this.rs = fetchStmt.executeQuery();
            assertTrue(this.rs.next());

            this.stmt.executeUpdate("INSERT INTO testRowFetch VALUES (2), (3)");

            fetchStmt = fetchConn.prepareStatement("SELECT field1 FROM testRowFetch ORDER BY field1");
            fetchStmt.setFetchSize(1);
            this.rs = fetchStmt.executeQuery();

            assertTrue(this.rs.next());
            assertEquals(1, this.rs.getInt(1));
            assertTrue(this.rs.next());
            assertEquals(2, this.rs.getInt(1));
            assertTrue(this.rs.next());
            assertEquals(3, this.rs.getInt(1));
            assertEquals(false, this.rs.next());

            this.rs = fetchStmt.executeQuery();
        } finally {
            if (fetchConn != null) {
                fetchConn.close();
            }
        }

    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#81706 - NullPointerException in driver.
 */
public void testBug81706() throws Exception {
    boolean useSPS = false;
    boolean cacheRsMd = false;
    boolean readOnly = false;

    do {
        final String testCase = String.format("Case [SPS: %s, CacheRsMd: %s, Read-only: %s]", useSPS ? "Y" : "N", cacheRsMd ? "Y" : "N",
                readOnly ? "Y" : "N");

        Properties props = new Properties();
        props.setProperty("useServerPrepStmts", Boolean.toString(useSPS));
        props.setProperty("cacheResultSetMetadata", Boolean.toString(cacheRsMd));
        props.setProperty("statementInterceptors", TestBug81706StatementInterceptor.class.getName());

        Connection testConn = getConnectionWithProps(props);
        testConn.setReadOnly(readOnly);
        Statement testStmt;
        PreparedStatement testPstmt;

        TestBug81706StatementInterceptor.isActive = true;
        TestBug81706StatementInterceptor.testCase = testCase;

        // Statement.executeQuery();
        testStmt = testConn.createStatement();
        testStmt.setFetchSize(Integer.MIN_VALUE);
        testStmt.executeQuery("/* ping */");
        testStmt.close();

        // Statemente.execute();
        testStmt = testConn.createStatement();
        testStmt.setFetchSize(Integer.MIN_VALUE);
        testStmt.execute("/* ping */");
        testStmt.close();

        // PreparedStatement.executeQuery();
        testPstmt = testConn.prepareStatement("/* ping */");
        assertFalse(testCase + ": Not the right Statement type.", testPstmt instanceof ServerPreparedStatement);
        testPstmt.setFetchSize(Integer.MIN_VALUE);
        testPstmt.executeQuery();
        testPstmt.close();

        // PreparedStatement.execute();
        testPstmt = testConn.prepareStatement("/* ping */");
        assertFalse(testCase + ": Not the right Statement type.", testPstmt instanceof ServerPreparedStatement);
        testPstmt.setFetchSize(Integer.MIN_VALUE);
        testPstmt.execute();
        testPstmt.close();

        TestBug81706StatementInterceptor.isActive = false;
        testConn.close();

    } while ((useSPS = !useSPS) || (cacheRsMd = !cacheRsMd) || (readOnly = !readOnly)); // Cycle through all possible combinations.
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Obtain a <tt>PreparedStatement</tt> with all parameters pre-bound.
 * Bind JDBC-style <tt>?</tt> parameters, named parameters, and
 * limit parameters.
 */
protected final PreparedStatement prepareQueryStatement(
	String sql,
	final QueryParameters queryParameters,
	final boolean scroll,
	final SessionImplementor session)
throws SQLException, HibernateException {

	Dialect dialect = session.getFactory().getDialect();
	RowSelection selection = queryParameters.getRowSelection();
	boolean useLimit = useLimit(selection, dialect);
	boolean hasFirstRow = getFirstRow(selection)>0;
	boolean useOffset = hasFirstRow && useLimit && dialect.supportsLimitOffset();
	boolean scrollable = session.getFactory().isScrollableResultSetsEnabled() && (
		scroll || //ie. a query called using scroll()
		( hasFirstRow && !useOffset ) //we want to skip some rows at the start
	);
	ScrollMode scrollMode = scroll ? queryParameters.getScrollMode() : ScrollMode.SCROLL_INSENSITIVE;

	if (useLimit) sql = dialect.getLimitString( sql.trim(), useOffset, getMaxOrLimit(selection, dialect) );

	PreparedStatement st = session.getBatcher().prepareQueryStatement(sql, scrollable, scrollMode);

	try {

		int col=1;

		if ( useLimit && dialect.bindLimitParametersFirst() ) {
			col += bindLimitParameters(st, col, selection, session);
		}
		col += bindPositionalParameters(st, queryParameters, col, session);
		col += bindNamedParameters(st, queryParameters.getNamedParameters(), col, session);

		if ( useLimit && !dialect.bindLimitParametersFirst() ) {
			col += bindLimitParameters(st, col, selection, session);
		}

		if (!useLimit) setMaxRows(st, selection);
		if (selection!=null) {
			if ( selection.getTimeout()!=null ) {
				st.setQueryTimeout( selection.getTimeout().intValue() );
			}
			if ( selection.getFetchSize()!=null ) {
				st.setFetchSize( selection.getFetchSize().intValue() );
			}
		}
	}
	catch (SQLException sqle) {
		JDBCExceptionReporter.logExceptions(sqle);
		session.getBatcher().closeQueryStatement(st, null);
		throw sqle;
	}
	catch (HibernateException he) {
		session.getBatcher().closeQueryStatement(st, null);
		throw he;
	}

	return st;
}