The Rowhandler of Ibatis

If a scenario: there are 10 million accounts in the Account table, now we need this 10 million account interest settlement business. The requirement is based on the Ibatis framework to implement this function.

If you follow the general programming pattern, we will write a SQL and then call queryForList to get the account list. It then iterates through the list-by-article operation, but this is likely to have performance problems such as:
1. Heavy consumption of JVM memory;
2. Dense creation and destruction of a large number of objects poses a significant burden on the GC;
The solution to the performance problem is that queryforlist is the result of constructing the query results before handing over to the rest program, which means that the JVM will create 10 million objects into memory, and the 10 million objects will persist in memory for a very long time--because only all these objects lose their references after the account is closed, so they can be judged to be explosive destruction. This consumption of memory is very large.

To deal with this problem, Ibatis provides the Rowhandler interface, which agrees that the program ape handles its own definition of the query results, Rowhandler interface code such as the following:

/**
 * Event handler for row by row processing.
 * <p/>
 * The RowHandler interface is used by the SqlMapSession.queryWithRowHandler() method.
 * Generally a RowHandler implementation will perform some row-by-row processing logic
 * in cases where there are too many rows to efficiently load into memory.
 * <p/>
 * Example:
 * <pre>
 * sqlMap.queryWithRowHandler ("findAllEmployees", null, new MyRowHandler()));
 * </pre>
 */
public interface RowHandler {

  /**
   * Handles a single row of a result set.
   * <p/>
   * This method will be called for each row in a result set.  For each row the result map
   * will be applied to build the value object, which is then passed in as the valueObject
   * parameter.
   *
   * @param valueObject The object representing a single row from the query.
   * @see com.ibatis.sqlmap.client.SqlMapSession
   */
  void handleRow(Object valueObject);

}

Defaultrowhandler is the default implementation of the Rowhandler interface, such as the following code:

public class DefaultRowHandler implements RowHandler {

  private List list = new ArrayList();

  public void handleRow(Object valueObject) {
    list.add(valueObject);
  }

  public List getList() {
    return list;
  }

  public void setList(List list) {
    this.list = list;
  }

}

The list that we get by calling the queryForList method is provided by that interface. Ibatis the process of constructing this list is, for example, the following: When you run out of query SQL to get resultset. Loops through the resultset, invokes the Defaultrowhandler Handlerow method in each loop, and ends the list with the loop, and returns the list. Here I believe you already know the nature of rowhandler, we just need to define our own handler, and then call Querywithrowhandler method, Ibatis run is our own definition of Handlerow method. In the Handlerow method, the interest-bearing operation is done, and there is no memory burst. The handle reference is lost because each loop ends Valueobject. It will be cleared by the GC very quickly.

The

above uses its own definition of Rowhandler to overcome the memory footprint, but there are other problems, handlerow in the operation is synchronous or asynchronous run it? Assume that you are running synchronously. That could lead to new problems:
    First, the synchronization does not reduce the total time, with queryForList and Querywithrowhander total time is-(database-side query time + Java +10000000* time to fetch data).
     again, resultset is not a one-time return of 10 million data, but in the process of loop traversal with take, for example, take 10 first, run to the 11th cycle of time to fetch 10, and so on, And how much at a time and fetchsize settings, then the 10 million account before the end of the database connection can not be released and the database will maintain a cursor with resultset fetch data. This can result in a long, very large resource overhead.
    So. It is a better solution to asynchronous the interest-bearing operation. We can add the account object in the Handlrow method to the rest of one or more threads, then the data and the dividend can be run in parallel, so the total time will be less (fetchsize the same situation, the shorter is The time of the dividend. The query time on the database side and the time of the Java end Fetch data are constant ). But probably not much less. This is related to the setting of the fethseize. Assuming that the fetchsize is larger, [Java's time to fetch data] will be very short, and the parallel time will be very short. The number of accounts that end in a short period of time is not too much; assuming the fetchsize is smaller, the data will take longer. Parallel time will also grow, then the number of accounts will be more.
    You should have seen that, in fact, after the asynchronous. Assuming the fetchsize is very large, querywithrowhandler and queryforlist are no different, because the data can be taken very quickly (I do test: Bettovin record 3 minutes to finish). The same will lead to a burst of memory. and fetchsize too small will lead to Java and database between the frequent network IO, the same impact performance. So the setting of Fetchsize is very critical, too big or too small, you need to consider the rate of [data], the rate of [interest-bearing operation] and the size of the memory three elements.

The above said so much, are biased theoretical things, all need to rely on their own actual situation to choose.
* Assuming the memory is large enough, queryforlist is very OK;
* Assuming that memory is a bottleneck, you can switch to Querywithrowhandler, assuming the connection pool is not tense. Database side There is no resource pressure, synchronous handle as OK.
* Assuming there is an optimization requirement, you can consider asynchronous handle, but this time the fetchsize setting needs to be weighed carefully, not too big or too small.

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

The Rowhandler of Ibatis