0

I have the following query:

@Select("SELECT* FROM "+MyData.TABLE_NAME+" where data_date = #{refDate}")
public List<MyData> getMyData(@Param("refDate") Date refDate);

This table data is HUGE! Loading so many rows in memory is not the best way! Is it possible to have this same query return a resultset so that I can just iterate over one item?

edit: I tried adding:

@ResultType(java.sql.ResultSet.class)
public ResultSet getMyData(@Param("refDate") Date refDate);

but it gives me:

nested exception is org.apache.ibatis.reflection.ReflectionException: Error instantiating interface java.sql.ResultSet with invalid types () or values (). Cause: java.lang.NoSuchMethodException: java.sql.ResultSet.&lt;init>()
Phate
  • 6,066
  • 15
  • 73
  • 138

2 Answers2

1

I'd suggest you use limit in your query. limit X, Y syntax is good for you. Try it.

If the table is huge, the query will become slower and slower. Then the best way to to iterate will be to filter based on id and use limit.

such as select * from table where id>0 limit 100 and then select * from table where id>100 limit 100 etc

Sezin Karli
  • 2,517
  • 19
  • 24
  • 1
    MyBatis supports such result set pagination with its [`RowBounds` parameters](https://mybatis.github.io/mybatis-3/java-api.html#sqlSessions), as described in [this answer](https://stackoverflow.com/questions/17511313/how-to-do-pagination-with-mybatis) – fspinnenhirn May 08 '15 at 18:41
1

There are multiple options you have ...

  • Use pagination on database side

I will just suppose the database is oracle. However other db vendors would also work. In oracle you have a rownum with which you can limit number of records to return. To return desired number of records you need to prepare a where clause using this rownum. Now, the question is how to supply a dynamic rownum in a query. This is where dynamic sqls of mybatis comes in use. You can pass these rownum values inside a parameter map which there onwards you can use in your query inside a mapper xml using a #{} syntax. With this approach you filter the records on db level itself and only bring or prepare java objects which are needed or in the current page.

  • Use pagination on mybatis side

Mybatis select method on sqlSession has a Rowbounds attribute. Populate this as per your needs and it will bring you those number of records only. Here, you are limiting number of records on mybatis side whereas in first approach the same was performed on db side which is better performant .

  • Use a Result handlers

Mybatis will give you control of actual jdbc result set. So, you can do/iterate over the result one by one here itself. See this blog entry for more details.

Shailesh Pratapwar
  • 4,054
  • 3
  • 35
  • 46