How can we use mysql restrictions with iBatis in general?

Question

I am using iBatis 2.3.4

I have the following query:

<select id="getUserList" resultMap="userListResult">
    SELECT
            id,
            name,
            login,
            email
    FROM
          users
</select>

And when I need to provide paging, I use:

sqlMap.queryForList("base.getUserList", startPosition, numItems);

Then iBatis generates a request without restrictions and skips additional data during the extraction. I believe that working with restrictions is faster.

How can we get iBatis to use LIMIT as a whole? Is it possible? Maybe we can describe some kind of dialect?

Answer 1

What is wrong with passing the limit, offset as parameters? For example (in Postgresql, I think Mysql is similar):

<select id="getUserList" resultMap="userListResult">
    SELECT  ...
    FROM  users
    LIMIT #limit:INTEGER# OFFSET #offset:INTEGER#
</select>

Then in your dao you can enter the code:

  Map params = new HashMap();
  params.put("limit",10);
  params.put("offset",100);
  res = sqlMap.queryForList("base.getUserList", params);