You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Sophia <jd...@telenet.be> on 2008/06/23 18:44:06 UTC
Problem with sql statement, help wanted.
Would anyone help me.
When I use this:
List tmpList = accountList2Dao.selectByAccountList2(account2Example);
And this in my sqlmap:
<select id="selectByAccountList2" parameterClass="model.AccountExample" resultMap="abatorgenerated_AccountResult">
Select number, etc……… from ${schema1}.account where number in
(select number from ${schema1}.account where substr(number,5,4) between ‘2002’ and ‘2006’ and office = ‘L2’)
</select>
I get al the data that I want, and it works fine.
The problem is that the sql is dynamic, I mean that the data 2002 – 2006 and L2 is inputted by the user thru a webpage.
When I rewrite in the past my application to Ibatis I have no problem with any select, insert, delete statement (Abator)
But when I now want to write my own specific sql, it will not work.
Now, my problem is when I use this :
account2Example.createCriteria().andNumberDateBetween(startdate, enddate).andOfficeEqualTo(office);
and this as sql
<select id="selectByAccountList2" parameterClass="model.AccountExample" resultMap="abatorgenerated_AccountResult">
Select number, etc……… from ${schema1}.account where number in
(select number from ${schema1}.account )
</select>
I get errors, can anyone tell me a easy way to write your own sql statements in Ibatis?
Help and suggestions are appreciated.
Regards,
Sophia.
Re: Problem with sql statement, help wanted.
Posted by Gwyn Evans <gw...@gmail.com>.
You've got 'dynamic' SQL for the "${schema1}" part, but for changing the
values in the select queries, you just need the normal ibatis usage, i.e.
...between #startYear# and #endYear# and office = #office#
Note: This assumes that your parameterClass class (model.AccountExample) has
either bean properties or accessors for the '#' values, e.g. a 'startYear'
field or a 'getStartYear()' method, etc...
See also http://cwiki.apache.org/WICKET/ibatis.html for an example...
/Gwyn
On Mon, Jun 23, 2008 at 5:44 PM, Sophia <jd...@telenet.be> wrote:
> Would anyone help me.
>
> When I use this:
>
> List tmpList = accountList2Dao.selectByAccountList2(account2Example);
>
> And this in my sqlmap:
>
> <select id="selectByAccountList2" parameterClass="model.AccountExample"
> resultMap="abatorgenerated_AccountResult">
> Select number, etc……… from ${schema1}.account where number in
> (select number from ${schema1}.account where substr(number,5,4) between
> '2002' and '2006' and office = 'L2')
> </select>
>
> I get al the data that I want, and it works fine.
> The problem is that the sql is dynamic, I mean that the data 2002 – 2006
> and L2 is inputted by the user thru a webpage.
> When I rewrite in the past my application to Ibatis I have no problem with
> any select, insert, delete statement (Abator)
> But when I now want to write my own specific sql, it will not work.
>
> Now, my problem is when I use this :
>
> account2Example.createCriteria().andNumberDateBetween(startdate,
> enddate).andOfficeEqualTo(office);
>
> and this as sql
>
> <select id="selectByAccountList2" parameterClass="model.AccountExample"
> resultMap="abatorgenerated_AccountResult">
> Select number, etc……… from ${schema1}.account where number in
> (select number from ${schema1}.account )
> </select>
>
> I get errors, can anyone tell me a easy way to write your own sql
> statements in Ibatis?
> Help and suggestions are appreciated.
>
>
> Regards,
> Sophia.
>
>
>
>
Re: Problem with sql statement, help wanted.
Posted by Jeff Butler <je...@gmail.com>.
You won't be able to use ibator's Example class here - this is a
specialized class and will only work with the ibator generated
*ByExample methods.
So you'll need to write your own parameter class, or use a HashMap.
This is pretty basic iBATIS stuff - I suggest you take a look at an
iBATIS tutorial somewhere.
Jeff Butler
On Mon, Jun 23, 2008 at 11:44 AM, Sophia <jd...@telenet.be> wrote:
> Would anyone help me.
>
> When I use this:
>
> List tmpList = accountList2Dao.selectByAccountList2(account2Example);
>
> And this in my sqlmap:
>
> <select id="selectByAccountList2" parameterClass="model.AccountExample" resultMap="abatorgenerated_AccountResult">
> Select number, etc……… from ${schema1}.account where number in
> (select number from ${schema1}.account where substr(number,5,4) between '2002' and '2006' and office = 'L2')
> </select>
>
> I get al the data that I want, and it works fine.
> The problem is that the sql is dynamic, I mean that the data 2002 – 2006 and L2 is inputted by the user thru a webpage.
> When I rewrite in the past my application to Ibatis I have no problem with any select, insert, delete statement (Abator)
> But when I now want to write my own specific sql, it will not work.
>
> Now, my problem is when I use this :
>
> account2Example.createCriteria().andNumberDateBetween(startdate, enddate).andOfficeEqualTo(office);
>
> and this as sql
>
> <select id="selectByAccountList2" parameterClass="model.AccountExample" resultMap="abatorgenerated_AccountResult">
> Select number, etc……… from ${schema1}.account where number in
> (select number from ${schema1}.account )
> </select>
>
> I get errors, can anyone tell me a easy way to write your own sql statements in Ibatis?
> Help and suggestions are appreciated.
>
>
> Regards,
> Sophia.
>
>
>
>