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 Lihui Pang <li...@yahoo.com> on 2006/04/15 03:03:41 UTC
question on dynamic query. Thanks for help.
The following statement always complain bad grammar. I
can't see where it's the problem, neighter can I see
the generated SQL statement. Please help.
<statement id="getUserProfileList"
resultMap="userProfile"
parameterClass="java.util.Map"><![CDATA[
SELECT c.First_Name, c.Surname, e.Email_Address
FROM Customer c, Email_Address e
where c.user_id = e.user_id
<dynamic>
<isNotNull prepend="AND" property="userName">
lower(trim(c.user_name))
$accountNameOperator$ #userName#
</isNotNull>
</dynamic>
ORDER BY $sortColumn$ $sortDirection$
]]></statement>
yihuayishijie yishuyichunqiu
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
RE: question on dynamic query. Thanks for help.
Posted by Lihui Pang <li...@yahoo.com>.
Hi Jean-Francois,
Just read your email again and got your point on
greater or less than comparison without <![CDATA[ and
]]>. Now it works.
Thanks again for you help !
--- Jean-Francois Poilpret <jf...@hcm.vnn.vn>
wrote:
> Hi,
>
> Remove the <![CDATA[ and ]]> tags, they prevent
> iBATIS from parsing the
> <dynamic> tags.
>
> As a general rule of thumb, I would advise to use
> <![CDATA[ only when needed
> (ie, when you need to do a greater/lower than
> comparison in your statement),
> and I would also restrict its use just around the
> problematic characters
> (although it does not make it very readable).
>
> Another option to use "<" ">" is to use "<"
> ">" (not much readable
> either but I find it better than <![CDATA[).
>
> Cheers
>
> Jean-Francois
>
> -----Original Message-----
> From: Lihui Pang [mailto:lihui_pang@yahoo.com]
> Sent: Saturday, April 15, 2006 8:04 AM
> To: user-java@ibatis.apache.org
> Subject: question on dynamic query. Thanks for help.
>
> The following statement always complain bad grammar.
> I
> can't see where it's the problem, neighter can I see
> the generated SQL statement. Please help.
>
> <statement id="getUserProfileList"
> resultMap="userProfile"
> parameterClass="java.util.Map"><![CDATA[
> SELECT c.First_Name, c.Surname, e.Email_Address
>
> FROM Customer c, Email_Address e
>
> where c.user_id = e.user_id
>
> <dynamic>
> <isNotNull prepend="AND"
> property="userName">
> lower(trim(c.user_name))
> $accountNameOperator$ #userName#
> </isNotNull>
> </dynamic>
>
> ORDER BY $sortColumn$ $sortDirection$
> ]]></statement>
>
> yihuayishijie yishuyichunqiu
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
>
>
>
yihuayishijie yishuyichunqiu
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
RE: question on dynamic query. Thanks for help.
Posted by Lihui Pang <li...@yahoo.com>.
Hi Jean-Francois,
Thanks for help.
I got another issue after I removed <![CDATA[ and ]]>.
In the sql, I have a date comparison which use
sysdate. sysdate does not work if remove <![CDATA[ and
]]>.
My SQL looks like:
<statement id="getUserProfileList"
resultMap="userProfile"
parameterClass="java.util.Map"><![CDATA[
SELECT c.First_Name, c.Surname, e.Email_Address
FROM Customer c, Email_Address e
where c.user_id = e.user_id
and c.effective_date > sysdate
<dynamic>
<isNotNull prepend="AND" property="userName">
lower(trim(c.user_name))
$accountNameOperator$ #userName#
</isNotNull>
</dynamic>
ORDER BY $sortColumn$ $sortDirection$
]]></statement>
--- Jean-Francois Poilpret <jf...@hcm.vnn.vn>
wrote:
> Hi,
>
> Remove the <![CDATA[ and ]]> tags, they prevent
> iBATIS from parsing the
> <dynamic> tags.
>
> As a general rule of thumb, I would advise to use
> <![CDATA[ only when needed
> (ie, when you need to do a greater/lower than
> comparison in your statement),
> and I would also restrict its use just around the
> problematic characters
> (although it does not make it very readable).
>
> Another option to use "<" ">" is to use "<"
> ">" (not much readable
> either but I find it better than <![CDATA[).
>
> Cheers
>
> Jean-Francois
>
> -----Original Message-----
> From: Lihui Pang [mailto:lihui_pang@yahoo.com]
> Sent: Saturday, April 15, 2006 8:04 AM
> To: user-java@ibatis.apache.org
> Subject: question on dynamic query. Thanks for help.
>
> The following statement always complain bad grammar.
> I
> can't see where it's the problem, neighter can I see
> the generated SQL statement. Please help.
>
> <statement id="getUserProfileList"
> resultMap="userProfile"
> parameterClass="java.util.Map"><![CDATA[
> SELECT c.First_Name, c.Surname, e.Email_Address
>
> FROM Customer c, Email_Address e
>
> where c.user_id = e.user_id
>
> <dynamic>
> <isNotNull prepend="AND"
> property="userName">
> lower(trim(c.user_name))
> $accountNameOperator$ #userName#
> </isNotNull>
> </dynamic>
>
> ORDER BY $sortColumn$ $sortDirection$
> ]]></statement>
>
> yihuayishijie yishuyichunqiu
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
>
>
>
yihuayishijie yishuyichunqiu
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
RE: question on dynamic query. Thanks for help.
Posted by Jean-Francois Poilpret <jf...@hcm.vnn.vn>.
Hi,
Remove the <![CDATA[ and ]]> tags, they prevent iBATIS from parsing the
<dynamic> tags.
As a general rule of thumb, I would advise to use <![CDATA[ only when needed
(ie, when you need to do a greater/lower than comparison in your statement),
and I would also restrict its use just around the problematic characters
(although it does not make it very readable).
Another option to use "<" ">" is to use "<" ">" (not much readable
either but I find it better than <![CDATA[).
Cheers
Jean-Francois
-----Original Message-----
From: Lihui Pang [mailto:lihui_pang@yahoo.com]
Sent: Saturday, April 15, 2006 8:04 AM
To: user-java@ibatis.apache.org
Subject: question on dynamic query. Thanks for help.
The following statement always complain bad grammar. I
can't see where it's the problem, neighter can I see
the generated SQL statement. Please help.
<statement id="getUserProfileList"
resultMap="userProfile"
parameterClass="java.util.Map"><![CDATA[
SELECT c.First_Name, c.Surname, e.Email_Address
FROM Customer c, Email_Address e
where c.user_id = e.user_id
<dynamic>
<isNotNull prepend="AND" property="userName">
lower(trim(c.user_name))
$accountNameOperator$ #userName#
</isNotNull>
</dynamic>
ORDER BY $sortColumn$ $sortDirection$
]]></statement>
yihuayishijie yishuyichunqiu
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com