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 st...@gmail.com on 2006/01/26 18:54:03 UTC
Dynamic sql statement
Dear all,
I want to run one dynamic query like this:
select * from cto.personinfovo where id=#id# and area=#area#
But for "area" field, is has two conditions, one is for "ALL" and one is the
input value, the ibatis XML file is like this and it's not working well,
<select id="selectALL" parameterClass="PersoninfoPO"
resultClass="PersoninfoPO">
<![CDATA[ select * from cto.personinfovo ]]>
<dynamic prepend="where">
<isNotEmpty prepend="and" property="id">
<![CDATA[ (id=#id#) ]]>
</isNotEmpty>
<isNotEmpty prepend="and" propery="area>
( <![CDATA[ (area=#area#) ]]>
<isEqual prepend="or" property="area" compareValue="ALL">
<![CDATA[(area in ('SC','EC','CC','NC','NE','SW','NW'))]]>
</isEqual> )
</isNotEmpty>
</dynamic>
</select>
Could any one give me the help?
Thanks so much!
Re: Dynamic sql statement
Posted by st...@gmail.com.
Dear Brandon,
Thanks for you reply.
I think the problem is not the "property" because I try many times.
Following is what I want to accopmplish:
select * from cto.personinfovo where id=#id# and area=#area#
There are 2 case happen when user do action:
Case 1. when user doesn't input anything in "area" field, it will be:
"select * from cto.personinfovo where id=#id# "
Case 2. when user input "ALL" in "area" field, it will be:
"select * from cto.personinfovo where id=#id# and area in
('SW','SC','NC')"
At the same time, the "id" is dynamic too, this is the most difficult I
can't solve.
Is it clear for it?
Thanks and Best Regards
Steven
On 1/27/06, Brandon Goodin <br...@gmail.com> wrote:
>
> <isNotEmpty prepend="and" propery="area>
>
> You mispelled *property*
>
> Other than that, what is NOT working? are you getting an error? Is the
> result wrong? What is it you want to accomplish?
>
> Brandon
>
> On 1/26/06, stevenlou@gmail.com <st...@gmail.com> wrote:
> > Dear all,
> >
> > I want to run one dynamic query like this:
> >
> > select * from cto.personinfovo where id=#id# and area=#area#
> >
> > But for "area" field, is has two conditions, one is for "ALL" and one is
> the
> > input value, the ibatis XML file is like this and it's not working well,
> >
> > <select id="selectALL" parameterClass="PersoninfoPO"
> > resultClass="PersoninfoPO">
> > <![CDATA[ select * from cto.personinfovo ]]>
> > <dynamic prepend="where">
> > <isNotEmpty prepend="and" property="id">
> > <![CDATA[ (id=#id#) ]]>
> > </isNotEmpty>
> > <isNotEmpty prepend="and" propery="area>
> > ( <![CDATA[ (area=#area#) ]]>
> > <isEqual prepend="or" property="area" compareValue="ALL">
> > <![CDATA[(area in
> > ('SC','EC','CC','NC','NE','SW','NW'))]]>
> > </isEqual> )
> > </isNotEmpty>
> > </dynamic>
> > </select>
> >
> > Could any one give me the help?
> >
> > Thanks so much!
> >
> >
> >
>
Re: Dynamic sql statement
Posted by Brandon Goodin <br...@gmail.com>.
<isNotEmpty prepend="and" propery="area>
You mispelled *property*
Other than that, what is NOT working? are you getting an error? Is the
result wrong? What is it you want to accomplish?
Brandon
On 1/26/06, stevenlou@gmail.com <st...@gmail.com> wrote:
> Dear all,
>
> I want to run one dynamic query like this:
>
> select * from cto.personinfovo where id=#id# and area=#area#
>
> But for "area" field, is has two conditions, one is for "ALL" and one is the
> input value, the ibatis XML file is like this and it's not working well,
>
> <select id="selectALL" parameterClass="PersoninfoPO"
> resultClass="PersoninfoPO">
> <![CDATA[ select * from cto.personinfovo ]]>
> <dynamic prepend="where">
> <isNotEmpty prepend="and" property="id">
> <![CDATA[ (id=#id#) ]]>
> </isNotEmpty>
> <isNotEmpty prepend="and" propery="area>
> ( <![CDATA[ (area=#area#) ]]>
> <isEqual prepend="or" property="area" compareValue="ALL">
> <![CDATA[(area in
> ('SC','EC','CC','NC','NE','SW','NW'))]]>
> </isEqual> )
> </isNotEmpty>
> </dynamic>
> </select>
>
> Could any one give me the help?
>
> Thanks so much!
>
>
>