Java Code Examples for java.sql.PreparedStatement#setMaxRows()

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 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;
}
 

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;
}
 

@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();
}
 

@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);
	}

}
 

@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);
	}
}
 

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;
}
 

@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 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;
}
 

/**
 * 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;
}