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

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

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

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

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

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

有没有其他方法限制返回的行?没有代码更改?

6 个解决方案

#1

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良好协作,您无法在配置中执行此操作。

#2

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驱动程序直接访问的引用游标中)。

#3

*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查询但仍想拥有干净的代码(这意味着您的代码保持可维护性),您可以使用包装器设计解决方案。也就是说,通过使用包含现有类的一小组类,您可以无缝地实现您想要的其余应用程序,它仍然认为它与真正的DataSource,Connection和Statement一起使用。

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作为应用程序的DataSource。如果您正在使用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,它将自动实现委派方法。

#4

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返回给您的客户端。这样做的好处是可以避免对DB进行硬解析,并提高性能。

#5

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成本的资源管理。

#6

Take a look at this page with a description of 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 App的程度。正如另一篇文章指出的那样,数据库仍会提取所有数据,这更多的是用于控制网络使用,以及Java端的内存。

#1

#2

#3