Requirements for an ORM I'm looking for:

  1. Dynamic SQL with:

    1.1. SQL injection prevention

    1.2. named parameters

    1.3. capability to set where clause conditions conditionally

    1.4. capability to call stored procedures and functions

  2. Allows me to set important ResultSet parameters such as the fetch size

  3. Allows me to control when to close the ResultSet

  4. Allows me to iterate through the result without waiting for all rows to be fetched from connection or ResultSet. This is because of the following

    • there will be queries that may return millions of rows (pagination will not be done in the DB using rownum or select top)

    • I don't want to wait for millions of rows to be loaded first in the memory before I can start doing something about the rows that already may have been fetched by the ResultSet

    • we want to control where and when we should stop getting rows from the connection or ResultSet while iterating through the rows

Please include some code snippets to demonstrate this.

UPDATE:

For (3), I should be able to do this without having to wait for a single record to be returned. If only the ORM can return me the resultSet and allow me to manually scroll through the records by letting me call the .next() it would perfectly fit what I'm looking for.

Maybe I'm just looking for a template-based way to generate SQL (as powerful as MyBatis) which allows me to simply pass parameters without worrying about SQL injection.

  • #4 is controlled by the JDBC driver, not necessarily by the Java code iterating over the ResultSet – a_horse_with_no_name May 31 '17 at 8:28
  • @a_horse_with_no_name, the JDBC driver honors the set fetch size. Once the number of rows fetched reaches this number, resultSet.next() becomes immediately available even if there are millions of rows in the search result that are yet to be fetched. This is as far as my experience tells me. This is true, at least for Oracle JDBC. – supertonsky May 31 '17 at 11:19
  • Yes, the Oracle behaves that way. But not all JDBC drivers do that. Some buffer the entire resultset in memory by default. – a_horse_with_no_name May 31 '17 at 11:34

MyBatis meets most of your requirements.

  1. See the Dynamic SQL and Mapper docs. All your requirements are there.
  2. Using the fetchSize property. Search the docs for this.
  3. See the stop() method in ResultContext as mentioned below
  4. Search for RowBounds parameter on this page in mybatis docs. See this Stack Overflow answer for more details.

From the documentation:

Finally, there are three advanced versions of the select methods that allow you to restrict the range of rows to return, or provide custom result handling logic, usually for very large data sets.

<E> List<E> selectList (String statement, Object parameter, RowBounds rowBounds)
<K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowbounds)
void select (String statement, Object parameter, ResultHandler<T> handler)
void select (String statement, Object parameter, RowBounds rowBounds, ResultHandler<T> handler)'

The RowBounds parameter causes MyBatis to skip the number of records specified, as well as limit the number of results returned to some number. The RowBounds class has a constructor to take both the offset and limit, and is otherwise immutable.

int offset = 100;
int limit = 25;
RowBounds rowBounds = new    RowBounds(offset, limit);

Different drivers are able to achieve different levels of efficiency in this regard. For the best performance, use result set types of SCROLL_SENSITIVE or SCROLL_INSENSITIVE (in other words: not FORWARD_ONLY).

The ResultHandler parameter allows you to handle each row however you like. You can add it to a List, create a Map, Set, or throw each result away and instead keep only rolled up totals of calculations. You can do pretty much anything with the ResultHandler, and it's what MyBatis uses internally itself to build result set lists.

The interface is very simple.

package org.apache.ibatis.session;
public interface ResultHandler<T> {
  void handleResult(ResultContext<? extends T> context);
}

The ResultContext parameter gives you access to the result object itself, a count of the number of result objects created, and a Boolean stop() method that you can use to stop MyBatis from loading any more results.

  • for #3, actually I should be able to stop and close the connection without even having to get at least one record in cases where the query is taking too much time to even get the first record. I suppose the handler will not be called until at least one record has been returned by Oracle. Is there a way to actually get the underlying resultset? I just wanted myBatis to construct the SQL and just return me the resultSet – supertonsky Aug 11 '17 at 11:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.