ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Dynamic sql statement
Date Fri, 27 Jan 2006 14:31:40 GMT
...And actually, I'm not even certain the removeFirstPrepend="true"
attribute is necessary in the isNotEmpty tag for the area property.
You may want to test it with and without that attribute. Also, be sure
you can see what the sql statement that is being generated by setting
up logging. This always helps when you are assembling dynamic sql.

Brandon

On 1/27/06, Brandon Goodin <brandon.goodin@gmail.com> wrote:
> Building on Yusuf's suggestions... Something like the following should work:
>
> <select id="selectALL" parameterClass="PersoninfoPO"
> resultClass="PersoninfoPO">
>    select * from cto.personinfovo
>        <dynamic prepend="where">
>            <isNotEmpty property="id">
>                id=#id#
>            </isNotEmpty>
>            <isNotEmpty property="area" compareValue="ALL"
> removeFirstPrepend="true">
>                <isEqual prepend="and" property="area" compareValue="ALL">
>                area in ('SC','EC','CC','NC','NE','SW','NW')
>                </isEqual>
>                <isNotEqual prepend="and" property="area" compareValue="ALL">
>                area=#area#
>                </isEqual>
>            </isNotEmpty>
>        </dynamic>
> </select>
>
> Hope that works,
> Brandon
>
> On 1/27/06, stevenlou@gmail.com <stevenlou@gmail.com> wrote:
> > Dear Yusuf,
> >
> > Thanks for your warm-hearted reply.
> >
> > Sorry I miss the case 1, it should be:
> >  "select * from cto.personinfovo where id=#id# and area=#area# "
> >
> > So I think your statement isn't working too.
> >
> > Have a nice weekend.
> >
> > Thanks and Best Regards
> > Steven
> >
> >
> >
> >
> >
> > On 1/27/06, Yusuf <Yusuf@ekalife.co.id> wrote:
> > > Hello, i think the only thing's missing from your query (in the first
> > > message in this thread)  is the property removeFirstPrepend="true" in
> > > the tags inside the <dynamic> tag, for example like this:
> > >
> > > <select id="selectALL" parameterClass="PersoninfoPO"
> > > resultClass="PersoninfoPO">
> > >    select * from cto.personinfovo
> > >        <dynamic prepend="where">
> > >            <isNotEmpty prepend="and" property="id"
> > > removeFirstPrepend="true">
> > >                id=#id#
> > >            </isNotEmpty>
> > >            <isEqual prepend="and" property="area" compareValue="ALL">
> > >                area in
> > ('SC','EC','CC','NC','NE','SW','NW')
> > >            </isEqual>
> > >        </dynamic>
> > > </select>
> > >
> > > And also, for a lot more easier way to debug your sql statements, try
> > > using log4j, you can read the guide in iBatis Data Mapper 2.0 Developer
> > > Guide (page 47 in the pdf) about Logging SqlMapActivity.
> > >
> > > Yusuf.
> > >
> > > -----Original Message-----
> > > From: stevenlou@gmail.com [mailto:stevenlou@gmail.com]
> > > Sent: Friday, January 27, 2006 1:31 PM
> > > To: user-java@ibatis.apache.org
> > > Subject: Re: Dynamic sql statement
> > >
> > >
> > > 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 < brandon.goodin@gmail.com
> > > <mailto:brandon.goodin@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 <mailto:stevenlou@gmail.com>  <
> > > stevenlou@gmail.com <mailto:stevenlou@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!
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>

Mime
View raw message