ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alin Popa <alin.p...@gmail.com>
Subject Re: iBatis - MySQL pagination
Date Fri, 15 May 2009 16:54:14 GMT
Well, in mysql I'm doing this way:

SELECT * FROM mytable LIMIT 0,10; -- will return first 10 records from mytable
SELECT * FROM mytable LIMIT 10,10; -- will return next 10 records from
mytable (2nd page)
and so on ...
For each page, depending on how many records for page you want, you
need to execute the select with specified LIMIT params.
Hope it helps.

On Fri, May 15, 2009 at 7:50 PM, Bhaarat Sharma <bhaarat.s@gmail.com> wrote:
> Thanks,
> But my question is lets say I want to display 10 results on a page but in
> total I want 50 results. so 10 on each page.
> I have some query like this in iBatis:
>
> SELECT rownum, table_name
> FROM user_tables
>
> WHERE rownum <=<some number I will pass in, in this case 10?>;
> so when I call this from my code. will the above query be ran 5 times??
>
> On Fri, May 15, 2009 at 12:42 PM, Alin Popa <alin.popa@gmail.com> wrote:
>>
>> Bhaarat,
>>
>> If you're using mysql, you could do pagination using LIMIT statement,
>> which is pretty straight forward.
>> On Mssql - TOP (using cursors).
>> On Oracle - ROWNUM.
>>
>>
>> On Fri, May 15, 2009 at 7:14 PM, Bhaarat Sharma <bhaarat.s@gmail.com>
>> wrote:
>> > that is a very interesting question.  We were looking into doing
>> > pagination
>> > with iBatis as well but would not want a full scan on the DB but rather
>> > only
>> > get amount of rows specified by skipResults
>> >
>> > On Fri, May 15, 2009 at 12:10 PM, Alin Popa <alin.popa@gmail.com> wrote:
>> >>
>> >> Hi,
>> >>
>> >> I'm using iBatis with MySQL and also trying to do some pagination:
>> >>
>> >>
>> >> return getSqlMapClientTemplate().queryForList("getAll", skipResults,
>> >> maxRecords);
>> >>
>> >> and the "getAll" query:
>> >>
>> >> <select id="getAllVJobs" resultMap="simpleResult">
>> >>                SELECT * FROM mytable
>> >> </select>
>> >>
>> >> Indeed, the pagination seems to work fine, BUT in mysql logs I see
>> >> executed the entire "SELECT * FROM mytable" which is a full table
>> >> scan.
>> >> Doesn't seems very right to me.
>> >>
>> >> Is there a catch behind this idea ?
>> >> Is fetching all records and after that iterate through them ? (I don't
>> >> think it's very nice this way).
>> >>
>> >> Any ideas ?
>> >>
>> >> Thanks.
>> >> Alin
>> >
>> >
>>
>>
>>
>> --
>> Best Regards,
>>
>> Alin
>
>



-- 
Best Regards,

Alin

Mime
View raw message