ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Poitras Christian <Christian.Poit...@ircm.qc.ca>
Subject RE: queryForList()
Date Fri, 17 Oct 2008 18:36:50 GMT
That is, unfortunately, dependent on your database.
On MySQL it would look like this.

SELECT *
FROM SomeTable
LIMIT skipCount, maxCount

Limiting number of results in the queryForList can force your database to return all 10198
records (depending on java connector) even if iBATIS will remove exceeding elements from the
list before returning from call.
Considering this problem, RowHandler is the best solution if you don't want to become database
dependent.

Using both solution combined can also be a good idea depending on what you want to acheive.

Christian


-----Original Message-----
From: John Chien [mailto:john.chien@ncmail.net]
Sent: Friday, October 17, 2008 2:01 PM
To: user-java@ibatis.apache.org
Subject: Re: queryForList()

Christian:

1)  I look at the RowHandler interface.
Eventually, it will return the whole list. That means it will return a list of 10198 records.
That is exactly I tried to avoid. I do not want it to return me such a big list.

I know that the RowHandler itself will try to to the insertion for me.
However, I have no idea of how many insertion has been executed, unless I wait for it to return
me such a big list.

2) How can I put the maxCount in the SQL statement ?
    Although the SQL statement will return me a list of objects, how can I put it in SQL statement
to return me a range of objects while I do not have
    idea of how many objects will be returned.

Thanks,

John Chien

Poitras Christian wrote:
> It would be better if your code would do one of the following.
> 1) Use a RowHandler. This is optimized for these kind of queries.
> 2) Use maxCount in your SQL statement and not in queryForList. Some connectors don't
use maximum count to limit the number of results sent from the database. In these cases limiting
number of results in the SQL statement is your only choice.
>
> I would go with solution 1 unless you cannot use a RowHandler.
>
> Christian
>
>
> -----Original Message-----
> From: John Chien [mailto:john.chien@ncmail.net]
> Sent: Friday, October 17, 2008 11:02 AM
> To: user-java@ibatis.apache.org
> Subject: queryForList()
>
>
> Dear Sir:
>
> I used the SQLMAP' s API queryForList to get data back from database.
> There are many data in database, so I the one that has four input parameters.
> The SQLMAP XML syntax should return me 10198 records, if runs without loop.
> I have already tested this by plugin the values.
>
> However, I have it runs in loop and bring back 500 records every time.
> It should run 21 times. However, it returns null at the 12 loops (very consistently.
I try many times) I am wondering what might be the problem.
>
> Thanks,
>
> John Chien
>
> Following is my code:
> ************************************************************************************************
>    ArrayList list = null;
>    int skipCount = 0;
>    int maxCount = 500;
>    boolean done = false;
>    int facilityCount = 0;
>
>    while (! done) {
>         list = (ArrayList)
> getSqlMapClientTemplate().queryForList("getIIBillableFacilityList",
> formBean, skipCount, maxCount);
>         if ((list != null) && (list.size() > 0)) {
>             for (int i = 0; i < list.size(); i++) {
>                   FacilityNameAddressBean facility =
> (FacilityNameAddressBean) list.get(i);
>                   insertFirstBillingRecord(formBean, facility.getId(),
> userId);
>
>                   facilityCount++;
>                }
>                skipCount += list.size();
>
>                if (list.size() < maxCount) {
>                    done = true;
>                }
>            } else {
>                done = true;
>            }
>        }
> }
>
> **********************************************************************
> ******************************
>
>

Mime
View raw message