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!
>
>
>