Here's the skip results logic from iBATIS 2.0:
// Skip Results
if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
if (skipResults > 0) {
rs.absolute(skipResults);
}
} else {
for (int i = 0; i < skipResults; i++) {
if (!rs
For everyone saying that "iBATIS shouldn't do this": The reason it does is
that JDBC provides an API for it. iBATIS generally defines its scope by
what JDBC allows for (think of iBATIS as an easier to use JDBC).
So generally, if it's available in JDBC, we support it (cautiously). If
there's a bet
I really think you should frame your problem more clearly before you come up
with a solution :)
Paging is an interesting thing and there really isn't a "one size fits all"
solution. It depends on your requirements. If you want help figuring out the
most performant way to handle paging in your proj
I wanted to say that is retrieving all the data from database (into
memory) and after that is iterating over it. What is happening when
"SELECT * FROM mytable" is returning 5 MIL records ? For sure I don't
want that. I don't know if mysql knows how to do optimization without
"helping" it somehow (L
Can anyone please suggest me a link to ibatis documentation regarding
$substitution$ ?
Thanks.
On Fri, May 15, 2009 at 7:15 PM, Larry Meadors wrote:
> Do it in the sql statement instead of using pagination in ibatis.
>
> Look at limit, row_count and offset here:
>
> http://dev.mysql.com/doc/refm
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 exec
Where are you seeing it perform a full table scan? select * does not
*necessarily* mean it will load all of your records into memory. Oracle for
example is smart about this and I would assume that most databases have
followed suit in this sense over the last few years. Most databases will
smartly u
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 <=;
so when I call this from my code. will the above query be ran 5 times
I'm not an expert, but I think the answer is that standard SQL doesn't
provide this mechanism, making it's implementation database dependent.
iBATIS is providing a mechanism for skip/max that will work with any DB
(quite possibly inefficiently), but at least the feature is there.
To be more e
IMO, ibatis shouldn't do this, but it's part of the API now, so it does. :-)
Larry
On Fri, May 15, 2009 at 10:39 AM, Alin Popa wrote:
> My dilemma is: why ibatis is doing this kind of abstraction if on the
> underlying server is doing it wrong ? Might cause confusion and of
> course, bad perfor
Just remember with rownum in oracle to do a subselect:
select * from (
your real select here
) where rownum ...
Larry
On Fri, May 15, 2009 at 10:42 AM, Alin Popa wrote:
> Bhaarat,
>
> If you're using mysql, you could do pagination using LIMIT statement,
> which is pretty straight forward.
>
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 wrote:
> that is a very interesting question. We were looking into doing paginati
Thanks Larry for your quick response.
If I'm doing it in Mysql statement, I'll do it using LIMIT which is
taking exactly the same 2 params like ibatis (skipResults and
maxRecords).
My dilemma is: why ibatis is doing this kind of abstraction if on the
underlying server is doing it wrong ? Might ca
what if we have a stored procedures that takes in 'startrow' and 'endrow' as
parameters. Based on those it returns the results back.
If a sp like that were to be called using iBatis with pagination then will
the SP be called again to get NEXT set of results?
On Fri, May 15, 2009 at 12:15 PM, Larry
Do it in the sql statement instead of using pagination in ibatis.
Look at limit, row_count and offset here:
http://dev.mysql.com/doc/refman/5.0/en/select.html
You will probably need to use $substitution$ for the parameters, but
surely some mysql stud can tell you more about that than I can. :)
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 wrote:
> Hi,
>
> I'm using iBatis with MySQL and also t
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 * FROM mytable
Indeed, the pagination seems to work fine, BUT in mysql logs I see
executed
17 matches
Mail list logo
