Support to set maxRows as well as fetchSize of Statement

[quaff]

This is standard way to limit results size without writing dialect.

I'd like to prepare PR if the team accepts proposal.

[harawata]

Hello @quaff ,

Recently, I explained why setMaxRows is not so useful compared to the SQL solution.
#3436 (comment)

[quaff]

The best way is to limit the number of returned rows in SQL using FETCH NEXT x ROWS ONLY or LIMIT x.

Not every database and every version follows SQL:2008 standards, setMaxRows() is the most portable solution.

[harawata]

Here is an excerpt from the linked article.

Conclusion
Although it looks like the setMaxRows is a portable solution to limit the size of the ResultSet, the SQL-level pagination is much more efficient if the database server optimizer doesn't use the JDBC maxRows property.

The main purpose of limiting rows is performance, so even though setMaxRows() is portable, it does not make much sense if the effect depends on the database.
What would you say?

[quaff]

Some use case requires consuming first n rows of given sql, for example, find latest or top rated n comments of an article, the portable way is query all comments as List then call List.subList() to limit, the performance is worse than setMaxRows().

[harawata]

My point is: if the performance matters, you should use the non-portable way.

Anyway, I do not strongly oppose your proposal. It is part of the JDBC API after all.
@jeffgbutler @epochcoder Any opinion on this?

[quaff]

Here is prototype: https://github.com/mybatis/mybatis-3/compare/master...quaff:mybatis-3:patch-2?expand=1