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

Question

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

It seems that maxRows is only available if you installed it on a data source in Java code. Entering maxRows="2" in the data source is not applicable.

Is there any other way to limit the returned rows? Without changing the code?

Answer 1

This is not what is available at the configuration level. You can double check that it does what you want one way or another: see the Javadoc for setMaxRows . In Oracle, it will still retrieve every row for the query, and then simply delete those that are out of range. You really need to use rownum to make it work well with Oracle, and you cannot do this in any configuration.

Answer 2

The question is why you want to limit the number of rows returned. There could be many reasons for this. The first would be to simply limit the data returned by the database. In my opinion, this does not make sense in most cases, as if I wanted to get certain data only then I would use another operator or add a filter condition or something else. For instance. if you use rownum of Oracle, you don’t know exactly what data is in the rows that you get, and which data is not included, because you just tell the database that you want row x to be y.
The second approach is to limit memory usage and increase performance so that the ResultSet obtained from the JDBC driver does not include all the data. You can limit the number of rows held by a ResultSet using Statement.setFetchSize () . If you move the cursor in the ResultSet beyond the number of rows selected, the JDBC driver will retrieve the missing data from the database. (In the case of Oracle, the database will store the data in the ref cursor, which is directly accessed by the JDBC driver).

Answer 3

* Beware: the code below is a pure example. It has not been tested. * It could harm yourself or your computer or even hit 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 remains supported), you can develop a solution using wrappers. That is, using a small set of classes that wrap existing ones, you can achieve what you want easily for the rest of the application, which will still think that it works with real DataSource, Connection and Statement.

1 - implement the StatementWrapper or PreparedStatementWrapper class, depending on what your application is already using. These classes are wrappers around regular instances of Statement or PreparedStatement. They are implemented just like using an internal operator as a delegate that does all the work, except when it is a QUERY query (Statement.executeQuery () method). Only in this exact situation does the shell surround the query with the following two lines: "SELECT * FROM (" and ") WHERE ROWNUM <" + maxRowLimit. For a basic code wrapper code, see how it looks for a DataSourceWrapper below.

2 - write another wrapper: ConnectionWrapper, which terminates the connection, which returns StatementWrapper in createStatement () and PreparedStatementWrapper in prepareStatement (). These are previously coded classes that take ConnectionWrapper delegateConnection.createStatement () / prepareStatement () as constructive arguments.

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

 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 a DataSourceWrapper as your DataSource application. If you are using JNDI (NamingContext), this change should be trivial.

Encoding all of this is fast and very easy, especially if you use an intelligent IDE such as Eclipse or IntelliJ, which automatically implements delegation methods.

Answer 4

If you know that you will deal with only one table, then define a view with rownum in the where statement to limit the number of rows. Thus, the number of rows is controlled in the database and does not need to be specified as part of any request from the client application. If you want to change the number of rows returned, redefine the view before executing the query.

A more dynamic method would be to develop a procedure and pass in a series of lines, and the procedure will return ref_cursor to your client. This will have the advantage of avoiding hard database parsing and improving performance.

Answer 5

OK, then it will be a code change.

The script restricts the adhoc reporting tool, so the end user does not discard too many records and generates a report that is unusable.

We already use oracle-based resource management. A.

Answer 6

Take a look at this page for a description of the limit on how much is sucked into a Java application at a time. As another post notes, the database will still retrieve all the data, this is more to control the use of network and memory on the Java side.