如何在 JDBC 数据源级别限制从 Oracle 返回的行数？

How to limit number of rows returned from Oracle at the JDBC data source level?

提问by Codek

Is there a way to limit the rows returned at the Oracle datasource level in a Tomcat application?

有没有办法限制在 Tomcat 应用程序中的 Oracle 数据源级别返回的行？

It seems maxRowsis only available if you set it on the datasource in the Java code. Putting maxRows="2"on the datasource doesn't apply.

似乎maxRows只有在 Java 代码中的数据源上设置它时才可用。放在maxRows="2"数据源上不适用。

Is there any other way limit the rows returned? Without a code change?

有没有其他方法限制返回的行？不改代码？

采纳答案by carson

It isn't something that is available at the configuration level. You may want to double check that it does what you want it to do anyway: see the javadoc for setMaxRows. With Oracle it is still going to fetch every row back for the query and then just drop the ones outside the range. You would really need to use rownum to make it work well with Oracle and you can't do that either in the configuration.

它不是在配置级别可用的东西。您可能需要仔细检查它是否可以执行您想要它做的事情：请参阅 setMaxRows 的 javadoc。使用 Oracle 时，它​​仍然会为查询取回每一行，然后将那些超出范围的行删除。您确实需要使用 rownum 才能使其与 Oracle 良好配合，而在配置中也不能这样做。

回答by reallyinsane

The question is why do you want to limit the number of rows returned. There could be many reasons to do this. The first would be to just limit the data returned by the database. In my opinion this makes no sense in most cases as if I would like to get certain data only then I would use a different statement or add a filter condition or something. E.g. if you use rownum of Oracle you don't exactly know which data is in the rows you get and which data is not included as you just tell the database that you want row x to y.
The second approach is to limit memory usage and increase performance so that the ResultSet you get from the JDBC driver will not include all data. You can limit the number of rows hold by the ResultSet using Statement.setFetchSize(). If you move the cursor in the ResultSet beyond the number of rows fetched the JDBC driver will fetch the missing data from the database. (In case of Oracle the database will store the data in a ref cursor which is directly accessed by the JDBC driver).

问题是为什么要限制返回的行数。这样做的原因可能有很多。第一种方法是限制数据库返回的数据。在我看来，这在大多数情况下是没有意义的，就好像我只想获取某些数据然后我才会使用不同的语句或添加过滤条件或其他东西。例如，如果您使用 Oracle 的 rownum，您并不确切知道哪些数据在您获得的行中，哪些数据不包括在内，因为您只是告诉数据库您想要从 x 到 y 的行。
第二种方法是限制内存使用并提高性能，以便您从 JDBC 驱动程序获得的 ResultSet 不会包含所有数据。您可以使用Statement.setFetchSize()限制 ResultSet 保存的行数. 如果将 ResultSet 中的游标移动到超过获取的行数，JDBC 驱动程序将从数据库中获取丢失的数据。（在 Oracle 的情况下，数据库将数据存储在 JDBC 驱动程序直接访问的引用游标中）。

回答by Chucky

*Beware: the code below is provided as pure example. It has not been tested *It thus may harm yourself or your computer or even punch you in the face.

*注意：以下代码仅作为示例提供。它尚未经过测试 *因此可能会伤害您自己或您的计算机，甚至可能会打您的脸。

If you want to avoid modifying your SQL queries but still want to have clean code (which means that your code stay maintainable), you may design the solution using wrappers. That is, by using a small set of classes wrapping existing ones, you may achieve what you want seamlessly for the rest of the application which will still think it is working with real DataSource, Connection and Statement.

如果您想避免修改您的 SQL 查询，但仍想拥有干净的代码（这意味着您的代码保持可维护性），您可以使用包装器设计解决方案。也就是说，通过使用一小组包装现有类的类，您可以无缝地为应用程序的其余部分实现您想要的，这些应用程序仍然认为它正在使用真正的数据源、连接和语句。

1 - implement a StatementWrapper or PreparedStatementWrapper class, depending what your application already uses. Those classes are wrappers around normal Statement or PreparedStatement instances. They are implemented simply as using the inner statement as a delegate which does all the work, except when this is a QUERY statement (Statement.executeQuery() method). Only in that precise situation, the wrapper surrounds the query by the two following strings : "SELECT * FROM (" and ") WHERE ROWNUM < "+maxRowLimit. For basic code wrapper code, see how it looks for the DataSourceWrapper below.

1 - 实现 StatementWrapper 或 PreparedStatementWrapper 类，具体取决于您的应用程序已经使用的内容。这些类是普通 Statement 或 PreparedStatement 实例的包装器。它们被简单地实现为使用内部语句作为完成所有工作的委托，除非这是一个 QUERY 语句（Statement.executeQuery() 方法）。只有在这种精确的情况下，包装器才会用以下两个字符串包围查询：“SELECT * FROM (”和“) WHERE ROWNUM <“+maxRowLimit。对于基本的代码包装器代码，请参阅下面的 DataSourceWrapper 的查找方式。

2 - write one more wrapper : ConnectionWrapper which wraps a Connection which returns StatementWrapper in createStatement() and PreparedStatementWrapper in prepareStatement(). Those are the previously coded classes taking ConnectionWrapper's delegateConnection.createStatement()/prepareStatement() as construction arguments.

2 - 再写一个包装器：ConnectionWrapper，它包装一个 Connection，它在 createStatement() 中返回 StatementWrapper，在 prepareStatement() 中返回 PreparedStatementWrapper。这些是以前编码的类，将 ConnectionWrapper 的 delegateConnection.createStatement()/prepareStatement() 作为构造参数。

3 - repeat the step with a DataSourceWrapper. Here is a simple code example.

3 - 使用 DataSourceWrapper 重复该步骤。这是一个简单的代码示例。

public class DataSourceWrapper implements DataSource
{
    private DataSource mDelegate;

    public DataSourceWrapper( DataSource delegate )
    {
        if( delegate == null ) { throw new NullPointerException( "Delegate cannot be null" );
        mDelegate = delegate;
    }

    public Connection getConnection(String username, String password)
    {
        return new ConnectionWrapper( mDelegate.getConnection( username, password ) );
    }

    public Connection getConnection()
    {
        ... <same as getConnection(String, String)> ...
    }
}

4 - Finally, use that DataSourceWrapper as your application's DataSource. If you're using JNDI (NamingContext), this change should be trivial.

4 - 最后，使用该 DataSourceWrapper 作为应用程序的数据源。如果您使用的是 JNDI (NamingContext)，则此更改应该是微不足道的。

Coding all this is quick and very straightforward, especially if you're using smart IDE like Eclipse or IntelliJ which will implement the delegating methods automagically.

对所有这些进行编码是快速且非常简单的，特别是如果您使用像 Eclipse 或 IntelliJ 这样的智能 IDE，它们会自动实现委托方法。

回答by Codek

Ok, a code change it'll have to be then.

好的，然后必须更改代码。

The scenario is limiting an adhoc reporting tool so that the end user doesnt pull back too many records and generate a report which is unusable.

该方案限制了临时报告工具，以便最终用户不会撤回太多记录并生成无法使用的报告。

We already use oracle cost based resource management.

我们已经在使用基于 Oracle 成本的资源管理。

回答by Codek

Take a look at this page with a descriptionof limiting how much is sucked into the Java App at a time. As another post points out, the DB will still pull all of the data, this is more for controlling network use, and memory on the Java side.

请查看此页面，其中描述了限制一次吸入 Java 应用程序的数量。正如另一篇文章指出的那样，DB 仍会拉取所有数据，这更多是为了控制网络使用和 Java 端的内存。

回答by Codek

If you know you will be dealing with only one table, then define a view with rownum in the where statement to limit the number of rows. In this way, the number of rows is controlled at the DB and does not need to be specified as part of any query from a client application. If you want to change the number of rows returned, then redefine the view prior to executing query.

如果你知道你将只处理一个表，那么在 where 语句中定义一个带有 rownum 的视图来限制行数。通过这种方式，行数在 DB 中控制，不需要作为来自客户端应用程序的任何查询的一部分进行指定。如果要更改返回的行数，则在执行查询之前重新定义视图。

A more dynamic method would be to develop a procedure and pass in a number of rows, and have the procedure return a ref_cursor to your client. This would have the advantage of avoiding hard parsing on the DB, and increase performance.

一种更动态的方法是开发一个过程并传入许多行，然后让该过程将 ref_cursor 返回给您的客户端。这将具有避免对数据库进行硬解析并提高性能的优点。