下面列出了java.sql.PreparedStatement#setMaxRows ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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 {
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);
fillSqlMetaData(ps, sql);
return ps;
}
private PreparedStatement createFloatStatementForStateChecking(
int[] parameterExpectedValues, int[] PreparedStatementExpectedValues,
Connection conn, String sql)
throws SQLException {
PreparedStatement s =
internalCreateFloatStatementForStateChecking(conn, sql);
s.setCursorName("StokeNewington");
s.setFetchDirection(ResultSet.FETCH_REVERSE);
s.setFetchSize(888);
s.setMaxFieldSize(317);
s.setMaxRows(91);
// PreparedStatement Create
assertStatementState(
parameterExpectedValues, PreparedStatementExpectedValues, s);
return s;
}
/**
* {@inheritDoc}
*/
@Override
public MSubmission findLastSubmissionForJob(long jobId, Connection conn) {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(crudQueries.getStmtSelectSubmissionsForJob());
stmt.setLong(1, jobId);
stmt.setMaxRows(1);
rs = stmt.executeQuery();
if(!rs.next()) {
return null;
}
return loadSubmission(rs, conn);
} catch (SQLException ex) {
logException(ex, jobId);
throw new SqoopException(CommonRepositoryError.COMMON_0037, ex);
} finally {
closeResultSets(rs);
closeStatements(stmt);
}
}
public static QueryCondition read(PreparedStatement statement, long qcid) {
if (statement == null || qcid < 0) {
return null;
}
try {
statement.setMaxRows(1);
statement.setLong(1, qcid);
try ( ResultSet results = statement.executeQuery()) {
if (results.next()) {
return read(results);
}
}
} catch (Exception e) {
failed(e);
}
return null;
}
/**
*
* The test inserts a few test snapshots. A query is issued to retrieve the two most recent snapshots
*/
@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");
}
private PreparedStatement createFloatStatementForStateChecking(
int[] parameterExpectedValues, int[] PreparedStatementExpectedValues,
Connection conn, String sql)
throws SQLException {
PreparedStatement s =
internalCreateFloatStatementForStateChecking(conn, sql);
s.setCursorName("StokeNewington");
s.setFetchDirection(ResultSet.FETCH_REVERSE);
s.setFetchSize(888);
s.setMaxFieldSize(317);
s.setMaxRows(91);
// PreparedStatement Create
assertStatementState(
parameterExpectedValues, PreparedStatementExpectedValues, s);
return s;
}
private PreparedStatement createPreparedStatementInternal(Connection conn, String sql) throws SQLException {
PreparedStatement pstmt;
if (autoGeneratedKeys != -1) {
pstmt = conn.prepareStatement(sql, autoGeneratedKeys);
} else if (columnIndexes != null) {
pstmt = conn.prepareStatement(sql, columnIndexes);
} else if (columnNames != null) {
pstmt = conn.prepareStatement(sql, columnNames);
} else {
int resultSetHoldability = this.resultSetHoldability;
if (resultSetHoldability == -1) {
resultSetHoldability = conn.getHoldability();
}
pstmt = conn.prepareStatement(sql, this.resultSetType, this.resultSetConcurrency, resultSetHoldability);
}
pstmt.setQueryTimeout(queryTimeout);
pstmt.setFetchSize(fetchSize);
pstmt.setMaxRows(maxRows);
setInnerStatement(pstmt);
return pstmt;
}
@Override
public void setMaxRows(RowSelection selection, PreparedStatement statement)
throws SQLException {
if (selection.getMaxRows() >= 0) {
statement.setMaxRows(selection.getMaxRows());
}
}
@Override
protected int __doExecute() throws Exception {
PreparedStatement _statement = null;
ResultSet _resultSet = null;
AccessorEventContext _context = null;
boolean _hasEx = false;
try {
IAccessor _accessor = new BaseAccessor(this.getAccessorConfig());
_statement = _accessor.getPreparedStatement(this.getConnectionHolder().getConnection(), this.getSQL());
if (this.maxRow > 0) {
_statement.setMaxRows(this.maxRow);
}
__doSetParameters(_statement);
if (this.getAccessorConfig() != null) {
this.getAccessorConfig().beforeStatementExecution(_context = new AccessorEventContext(_statement, Type.OPT.QUERY));
}
this.resultSet = this.getResultSetHandler().handle(_resultSet = _statement.executeQuery());
return this.resultSet.size();
} catch (Exception ex) {
_hasEx = true;
throw ex;
} finally {
if (!_hasEx && this.getAccessorConfig() != null && _context != null) {
this.getAccessorConfig().afterStatementExecution(_context);
}
if (_resultSet != null) {
_resultSet.close();
}
if (_statement != null) {
_statement.close();
}
}
}
protected void setupOptions(PreparedStatement preparedStatement) throws SQLException {
if (query.getFetchSize() > 0) {
preparedStatement.setFetchSize(query.getFetchSize());
}
if (query.getMaxRows() > 0) {
preparedStatement.setMaxRows(query.getMaxRows());
}
if (query.getQueryTimeout() > 0) {
preparedStatement.setQueryTimeout(query.getQueryTimeout());
}
}
/**
* Tests that the {@code maxRows} setting takes effect, data is obtained
* from a value clause.
*/
public void testSetMaxRowsValues()
throws SQLException {
PreparedStatement ps = prepareStatement("values 0,1,2,3,4,5,6,7,8,9",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setMaxRows(10);
JDBC.assertDrainResults(ps.executeQuery(), 10);
ps.setMaxRows(2);
JDBC.assertDrainResults(ps.executeQuery(), 2);
}
/**
* Tests that the {@code maxRows} setting takes effect, data is obtained
* from a value clause.
*/
public void testSetMaxRowsValues()
throws SQLException {
PreparedStatement ps = prepareStatement("values 0,1,2,3,4,5,6,7,8,9",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setMaxRows(10);
JDBC.assertDrainResults(ps.executeQuery(), 10);
ps.setMaxRows(2);
JDBC.assertDrainResults(ps.executeQuery(), 2);
}
/**
* <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);
}
}
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();
}
/**
* Execute the query against the database
*
* @param database
* open database
* @param sql
* SQL statement
* @param maxRows
* max rows
* @return results
* @throws SQLException
* upon SQL error
*/
private static SQLExecResult executeQuery(GeoPackage database, String sql,
int maxRows) throws SQLException {
SQLExecResult result = new SQLExecResult();
if (!sql.equals(";")) {
PreparedStatement statement = null;
try {
statement = database.getConnection().getConnection()
.prepareStatement(sql);
statement.setMaxRows(maxRows);
result.setMaxRows(maxRows);
boolean hasResultSet = statement.execute();
if (hasResultSet) {
ResultSet resultSet = statement.getResultSet();
ResultSetMetaData metadata = resultSet.getMetaData();
int numColumns = metadata.getColumnCount();
int[] columnWidths = new int[numColumns];
int[] columnTypes = new int[numColumns];
for (int col = 1; col <= numColumns; col++) {
result.addTable(metadata.getTableName(col));
String columnName = metadata.getColumnName(col);
result.addColumn(columnName);
columnTypes[col - 1] = metadata.getColumnType(col);
columnWidths[col - 1] = columnName.length();
}
while (resultSet.next()) {
List<String> row = new ArrayList<>();
result.addRow(row);
for (int col = 1; col <= numColumns; col++) {
String stringValue = resultSet.getString(col);
if (stringValue != null) {
switch (columnTypes[col - 1]) {
case Types.BLOB:
stringValue = BLOB_DISPLAY_VALUE;
break;
default:
stringValue = stringValue.replaceAll(
"\\s*[\\r\\n]+\\s*", " ");
}
int valueLength = stringValue.length();
if (valueLength > columnWidths[col - 1]) {
columnWidths[col - 1] = valueLength;
}
}
row.add(stringValue);
}
}
result.addColumnWidths(columnWidths);
} else {
int updateCount = statement.getUpdateCount();
if (updateCount >= 0) {
result.setUpdateCount(updateCount);
}
}
} finally {
SQLUtils.closeStatement(statement, sql);
}
}
return result;
}
public OrderedDocumentsList retriveDocuments(final DocumentQuery query, final int limit, final IDocumentChangesCollector changesCollector)
{
logger.debug("Retrieving records: query={}, limit={}", query, limit);
final DocumentEntityDescriptor entityDescriptor = query.getEntityDescriptor();
assertThisRepository(entityDescriptor);
final Document parentDocument = query.getParentDocument();
final Function<DocumentId, Document> existingDocumentsSupplier = query.getExistingDocumentsSupplier();
final List<Object> sqlParams = new ArrayList<>();
final SqlDocumentQueryBuilder sqlBuilder = SqlDocumentQueryBuilder.of(query);
final String sql = sqlBuilder.getSql(sqlParams);
final String adLanguage = sqlBuilder.getAD_Language();
logger.debug("Retrieving records: SQL={} -- {}", sql, sqlParams);
final int loadLimitWarn = getLoadLimitWarn();
final int loadLimitMax = getLoadLimitMax();
int maxRowsToFetch = limit;
if (maxRowsToFetch <= 0)
{
maxRowsToFetch = loadLimitMax;
}
final OrderedDocumentsList documentsCollector = OrderedDocumentsList.newEmpty(query.getOrderBys());
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sql, ITrx.TRXNAME_ThreadInherited);
if (maxRowsToFetch > 0)
{
pstmt.setMaxRows(maxRowsToFetch);
}
DB.setParameters(pstmt, sqlParams);
rs = pstmt.executeQuery();
boolean loadLimitWarnReported = false;
while (rs.next())
{
final ResultSetDocumentValuesSupplier documentValuesSupplier = new ResultSetDocumentValuesSupplier(entityDescriptor, adLanguage, rs);
Document document = null;
if (existingDocumentsSupplier != null)
{
final DocumentId documentId = documentValuesSupplier.getDocumentId();
document = existingDocumentsSupplier.apply(documentId);
}
if (document == null)
{
document = Document.builder(entityDescriptor)
.setParentDocument(parentDocument)
.setChangesCollector(changesCollector)
.initializeAsExistingRecord(documentValuesSupplier);
}
documentsCollector.addDocument(document);
final int loadCount = documentsCollector.size();
// Stop if we reached the limit
if (limit > 0 && loadCount >= limit)
{
break;
}
// Stop if we reached the MAXIMUM limit
if (loadLimitMax > 0 && loadCount >= loadLimitMax)
{
logger.warn("Reached load count MAXIMUM level. Stop loading. \n SQL: {} \n SQL Params: {} \n loadCount: {}"
+ "\n To change this limit check {} sysconfig.",
sql, sqlParams, loadCount, SYSCONFIG_LoadLimitMax);
break;
}
// WARN if we reached the Warning limit
if (!loadLimitWarnReported && loadLimitWarn > 0 && loadCount >= loadLimitWarn)
{
logger.warn("Reached load count Warning level. Continue loading. \n SQL: {} \n SQL Params: {} \n loadCount: {}"
+ "\n To change this limit check {} sysconfig.",
sql, sqlParams, loadCount, SYSCONFIG_LoadLimitWarn);
loadLimitWarnReported = true;
}
}
}
catch (final SQLException e)
{
throw new DBException(e, sql, sqlParams);
}
finally
{
DB.close(rs, pstmt);
}
logger.debug("Retrieved {} records.", documentsCollector.size());
return documentsCollector;
}
@SuppressFBWarnings("OBL_UNSATISFIED_OBLIGATION") // needed for https://github.com/spotbugs/spotbugs/issues/432
private PreparedStatement createPreparedStatement(String sqlSelectStatement) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sqlSelectStatement);
ps.setMaxRows(1);
return ps;
}
public ResultSet openQuery( PreparedStatement ps, RowMetaInterface params, Object[] data )
throws KettleDatabaseException {
ResultSet res;
// Create a Statement
try {
log.snap( Metrics.METRIC_DATABASE_OPEN_QUERY_START, databaseMeta.getName() );
log.snap( Metrics.METRIC_DATABASE_SQL_VALUES_START, databaseMeta.getName() );
setValues( params, data, ps ); // set the parameters!
log.snap( Metrics.METRIC_DATABASE_SQL_VALUES_STOP, databaseMeta.getName() );
if ( canWeSetFetchSize( ps ) ) {
int maxRows = ps.getMaxRows();
int fs = Const.FETCH_SIZE <= maxRows ? maxRows : Const.FETCH_SIZE;
// mysql have some restriction on fetch size assignment
if ( databaseMeta.isMySQLVariant() ) {
setMysqlFetchSize( ps, fs, maxRows );
} else {
// other databases seems not.
ps.setFetchSize( fs );
}
ps.setFetchDirection( ResultSet.FETCH_FORWARD );
}
if ( rowlimit > 0 && databaseMeta.supportsSetMaxRows() ) {
ps.setMaxRows( rowlimit );
}
log.snap( Metrics.METRIC_DATABASE_EXECUTE_SQL_START, databaseMeta.getName() );
res = ps.executeQuery();
log.snap( Metrics.METRIC_DATABASE_EXECUTE_SQL_STOP, databaseMeta.getName() );
// MySQL Hack only. It seems too much for the cursor type of operation on
// MySQL, to have another cursor opened
// to get the length of a String field. So, on MySQL, we ignore the length
// of Strings in result rows.
//
log.snap( Metrics.METRIC_DATABASE_GET_ROW_META_START, databaseMeta.getName() );
rowMeta = getRowInfo( res.getMetaData(), databaseMeta.isMySQLVariant(), false );
log.snap( Metrics.METRIC_DATABASE_GET_ROW_META_STOP, databaseMeta.getName() );
} catch ( SQLException ex ) {
throw new KettleDatabaseException( "ERROR executing query", ex );
} catch ( Exception e ) {
throw new KettleDatabaseException( "ERROR executing query", e );
} finally {
log.snap( Metrics.METRIC_DATABASE_OPEN_QUERY_STOP, databaseMeta.getName() );
}
return res;
}
@Override
public IViewRow retrieveById(final ViewEvaluationCtx viewEvalCtx, final ViewId viewId, final DocumentId rowId)
{
final SqlAndParams sqlAndParams = sqlViewSelect.selectById()
.viewEvalCtx(viewEvalCtx)
.viewId(viewId)
.rowId(rowId)
.build();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
final int limit = 2;
pstmt = DB.prepareStatement(sqlAndParams.getSql(), ITrx.TRXNAME_ThreadInherited);
pstmt.setMaxRows(limit);
DB.setParameters(pstmt, sqlAndParams.getSqlParams());
rs = pstmt.executeQuery();
final List<IViewRow> documents = loadViewRows(rs, viewEvalCtx, viewId, limit);
if (documents.isEmpty())
{
throw new EntityNotFoundException("No document found for rowId=" + rowId + " in viewId=" + viewId);
}
else if (documents.size() > 1)
{
logger.warn("More than one document found for rowId={} in {}. Returning only the first one from: {}", rowId, this, documents);
return documents.get(0);
}
else
{
return documents.get(0);
}
}
catch (final SQLException | DBException e)
{
throw DBException.wrapIfNeeded(e)
.setSqlIfAbsent(sqlAndParams.getSql(), sqlAndParams.getSqlParams());
}
finally
{
DB.close(rs, pstmt);
}
}
@Override
public Page<HuId> retrieveHUIdsPage(final ViewEvaluationCtx viewEvalCtx, final ViewRowIdsOrderedSelection selection, final int firstRow, final int maxRows)
{
final SqlAndParams sqlAndParams = sqlViewSelect.selectByPage()
.viewEvalCtx(viewEvalCtx)
.viewId(selection.getViewId())
.firstRowZeroBased(firstRow)
.pageLength(maxRows)
.build();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = DB.prepareStatement(sqlAndParams.getSql(), ITrx.TRXNAME_ThreadInherited);
pstmt.setMaxRows(maxRows);
DB.setParameters(pstmt, sqlAndParams.getSqlParams());
rs = pstmt.executeQuery();
final Set<HuId> huIds = new LinkedHashSet<>();
int lastRowMax = -1;
while (rs.next())
{
final int huId = rs.getInt(I_M_HU.COLUMNNAME_M_HU_ID);
if (huId <= 0)
{
continue;
}
huIds.add(HuId.ofRepoId(huId));
final int lastRow = rs.getInt(SqlViewSelectData.COLUMNNAME_Paging_SeqNo_OneBased);
lastRowMax = Math.max(lastRowMax, lastRow);
}
if (huIds.isEmpty())
{
// shall not happen
return null;
}
else
{
final int lastRowZeroBased = lastRowMax - 1;
return Page.ofRowsAndLastRowIndex(ImmutableList.copyOf(huIds), lastRowZeroBased);
}
}
catch (final SQLException ex)
{
throw DBException.wrapIfNeeded(ex)
.setSqlIfAbsent(sqlAndParams.getSql(), sqlAndParams.getSqlParams());
}
finally
{
DB.close(rs, pstmt);
}
}