How can we use mysql's limits with iBatis generally?

Question

I use 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 query without limit, and skips extra data during fetching. I belive that work with limits is more faster.

How can we push iBatis to use LIMIT generally? Is It possible? May be we can describe some dialect?

score 3 · Accepted Answer · answered Jul 14 '10 at 16:51

3

What's 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 could code:

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

answered Jul 14 '10 at 16:51

leonbloy

73,180
20
142
190

How would you implement paging on this scenario, if you want to show and search results only page by page? – mico May 24 '11 at 13:15
Your DAO just knows how to treat limit/offset. The upper layer must compute them to do pagination as it wishes, that has nothing to do with iBatis. See eg http://stackoverflow.com/questions/5412059/how-to-implement-general-pagination – leonbloy May 24 '11 at 15:25
Hi, is this for version 2 or 3? My inline (#) parameters are always translated to ? with this code. – zeratul021 Jul 26 '11 at 17:07
@Zeratul: unless I'm mistaking your problem: inline parameters are normally translated to '?', they are parameters in preparedStaments – leonbloy Jul 26 '11 at 17:32
i solved it, hash (inline) params can only be used for values (WHERE id = #id#), for direct dynamic sql they need to be wrapped in dollar signs: LIMIT $limit$ OFFSET $offset$ – zeratul021 Jul 28 '11 at 12:26

How can we use mysql's limits with iBatis generally?

1 Answers1