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 Nilesh Bhattad <ni...@outlinesys.com> on 2005/01/27 22:32:27 UTC
Dynamic insert stmt
Hi there,
I have the below <insert/> statement defined in a sqlmap. If
'BlackListed' is not passed thro the HashMap, and rest of the fields are
passed in, one comma is inserted before 'isMarried' column and it
becomes an invalid sql stmt. I tried surrounding <isNotEmpty/> with
<dynamic/> tag, but that didn't help. (not sure if <dynamic/> can be
used within in insert stmt or not)
<insert id="insert" parameterClass="java.util.HashMap">
insert into Consultant(
<isNotEmpty property="BlackListed">BlackListed</isNotEmpty>
<isNotEmpty property="isMarried"
prepend=",">isMarried</isNotEmpty>
<isNotEmpty property="ActiveInactive"
prepend=",">ActiveInactive</isNotEmpty>
<isNotEmpty property="HomeTelephone"
prepend=",">HomeTelephone</isNotEmpty>
<isNotEmpty property="OfficeTelephone"
prepend=",">OfficeTelephone</isNotEmpty>
<isNotEmpty property="OfficeTelExt"
prepend=",">OfficeTelExt</isNotEmpty>
) values (
<isNotEmpty property="BlackListed">#BlackListed#</isNotEmpty>
<isNotEmpty property="isMarried"
prepend=",">#isMarried#</isNotEmpty>
<isNotEmpty property="ActiveInactive"
prepend=",">#ActiveInactive#</isNotEmpty>
<isNotEmpty property="HomeTelephone"
prepend=",">#HomeTelephone#</isNotEmpty>
<isNotEmpty property="OfficeTelephone"
prepend=",">#OfficeTelephone#</isNotEmpty>
<isNotEmpty property="OfficeTelExt"
prepend=",">#OfficeTelExt#</isNotEmpty>
)
<selectKey resultClass="int" keyProperty="id">SELECT
@@IDENTITY as last_insert_id</selectKey>
</insert>
Would someone please help me out to find what I'm doing wrong?
Thanks
Nilesh
Re: Dynamic insert stmt
Posted by friendVU admin <ma...@friendvu.com>.
Try this syntax instead
INSERT
INTO load_email (
content, subject,
...
)
values (
#content:VARCHAR:NO_ENTRY#, #subject:VARCHAR:NO_ENTRY#,
...
)
Nilesh Bhattad wrote:
> Hi there,
>
> I have the below <insert/> statement defined in a sqlmap. If
> ‘BlackListed’ is not passed thro the HashMap, and rest of the fields
> are passed in, one comma is inserted before ‘isMarried’ column and it
> becomes an invalid sql stmt. I tried surrounding <isNotEmpty/> with
> <dynamic/> tag, but that didn’t help. (not sure if <dynamic/> can be
> used within in insert stmt or not)
>
> <insert id="insert" parameterClass="java.util.HashMap">
>
> insert into Consultant(
>
> <isNotEmpty property="BlackListed">BlackListed</isNotEmpty>
>
> <isNotEmpty property="isMarried" prepend=",">isMarried</isNotEmpty>
>
> <isNotEmpty property="ActiveInactive"
> prepend=",">ActiveInactive</isNotEmpty>
>
> <isNotEmpty property="HomeTelephone"
> prepend=",">HomeTelephone</isNotEmpty>
>
> <isNotEmpty property="OfficeTelephone"
> prepend=",">OfficeTelephone</isNotEmpty>
>
> <isNotEmpty property="OfficeTelExt" prepend=",">OfficeTelExt</isNotEmpty>
>
> ) values (
>
> <isNotEmpty property="BlackListed">#BlackListed#</isNotEmpty>
>
> <isNotEmpty property="isMarried" prepend=",">#isMarried#</isNotEmpty>
>
> <isNotEmpty property="ActiveInactive"
> prepend=",">#ActiveInactive#</isNotEmpty>
>
> <isNotEmpty property="HomeTelephone"
> prepend=",">#HomeTelephone#</isNotEmpty>
>
> <isNotEmpty property="OfficeTelephone"
> prepend=",">#OfficeTelephone#</isNotEmpty>
>
> <isNotEmpty property="OfficeTelExt"
> prepend=",">#OfficeTelExt#</isNotEmpty>
>
> )
>
> <selectKey resultClass="int" keyProperty="id">SELECT @@IDENTITY as
> last_insert_id</selectKey>
>
> </insert>
>
> Would someone please help me out to find what I’m doing wrong?
>
> Thanks
>
> Nilesh
>
RE: Dynamic insert stmt
Posted by Nilesh Bhattad <ni...@outlinesys.com>.
Hi Philippe
Thank you so much for your quick response. It did work as per your
solution and thanks for explaining <dynamic/> tag behavior in detail.
What I was missing was prepend=" " !!
~ Nilesh
-----Original Message-----
From: Philippe Laflamme [mailto:philippe.laflamme@mail.mcgill.ca]
Sent: Thursday, January 27, 2005 4:51 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Dynamic insert stmt
Hi Nilesh,
The purpose of the <dynamic> tag is mostly to solve this problem. Here's
how it works.
Consider the following snippet:
[...]
WHERE
<isNotEmpty property="A" prepend="AND">A = #A#</isNotEmpty>
<isNotEmpty property="B" prepend="AND">B = #B#</isNotEmpty>
[...]
The situation here is similar to yours: if A is empty and B is
non-empty, only the second isNotEmpty tag is evaluated and it would
result in something like this:
[...]
WHERE
AND B = 'BValue'
[...]
Which obviously breaks... Now consider the following snippet:
[...]
<dynamic prepend="WHERE">
<isNotEmpty property="A" prepend="AND">A = #A#</isNotEmpty>
<isNotEmpty property="B" prepend="AND">B = #B#</isNotEmpty>
</dynamic>
[...]
See the "WHERE" keyword? Whenever tags are embedded, "prepend" tags are
pushed down the tree until the body of an embdedded tag is evaluated. In
the situation described above, the prepend="WHERE" would be pushed down
to the isNotEmpty tags. The tag for the "A" property is not evaluated,
so it moves on to the tag for the "B" property. In this case, its body
is evaluated so its "prepend" attribute becomes prepend="WHERE" and
prepend="AND" is pushed down (since no other tags are embedded, it
simply disapears). The result is:
WHERE
B = 'BValue'
In your situation you should simply add <dynamic prepend=" "> and
</dynamic> tags.
<dynamic> tags can be used in any kind of statement (even insert). Read
the SQLMap guide carefully, it's pretty clear on how dynamic tags and
the prepend attribute behave...
Cheers,
Philippe
Nilesh Bhattad wrote:
> Hi there,
>
>
>
> I have the below <insert/> statement defined in a sqlmap. If
> 'BlackListed' is not passed thro the HashMap, and rest of the fields
are
> passed in, one comma is inserted before 'isMarried' column and it
> becomes an invalid sql stmt. I tried surrounding <isNotEmpty/> with
> <dynamic/> tag, but that didn't help. (not sure if <dynamic/> can be
> used within in insert stmt or not)
>
>
>
> <insert id="insert" parameterClass="java.util.HashMap">
>
> insert into Consultant(
>
> <isNotEmpty property="BlackListed">BlackListed</isNotEmpty>
>
> <isNotEmpty property="isMarried"
> prepend=",">isMarried</isNotEmpty>
>
> <isNotEmpty property="ActiveInactive"
> prepend=",">ActiveInactive</isNotEmpty>
>
> <isNotEmpty property="HomeTelephone"
> prepend=",">HomeTelephone</isNotEmpty>
>
> <isNotEmpty property="OfficeTelephone"
> prepend=",">OfficeTelephone</isNotEmpty>
>
> <isNotEmpty property="OfficeTelExt"
> prepend=",">OfficeTelExt</isNotEmpty>
>
> ) values (
>
> <isNotEmpty property="BlackListed">#BlackListed#</isNotEmpty>
>
> <isNotEmpty property="isMarried"
> prepend=",">#isMarried#</isNotEmpty>
>
> <isNotEmpty property="ActiveInactive"
> prepend=",">#ActiveInactive#</isNotEmpty>
>
> <isNotEmpty property="HomeTelephone"
> prepend=",">#HomeTelephone#</isNotEmpty>
>
> <isNotEmpty property="OfficeTelephone"
> prepend=",">#OfficeTelephone#</isNotEmpty>
>
> <isNotEmpty property="OfficeTelExt"
> prepend=",">#OfficeTelExt#</isNotEmpty>
>
> )
>
> <selectKey resultClass="int" keyProperty="id">SELECT
> @@IDENTITY as last_insert_id</selectKey>
>
> </insert>
>
>
>
> Would someone please help me out to find what I'm doing wrong?
>
>
>
> Thanks
>
> Nilesh
>
Re: Dynamic insert stmt
Posted by Philippe Laflamme <ph...@mail.mcgill.ca>.
Hi Nilesh,
The purpose of the <dynamic> tag is mostly to solve this problem. Here's
how it works.
Consider the following snippet:
[...]
WHERE
<isNotEmpty property="A" prepend="AND">A = #A#</isNotEmpty>
<isNotEmpty property="B" prepend="AND">B = #B#</isNotEmpty>
[...]
The situation here is similar to yours: if A is empty and B is
non-empty, only the second isNotEmpty tag is evaluated and it would
result in something like this:
[...]
WHERE
AND B = 'BValue'
[...]
Which obviously breaks... Now consider the following snippet:
[...]
<dynamic prepend="WHERE">
<isNotEmpty property="A" prepend="AND">A = #A#</isNotEmpty>
<isNotEmpty property="B" prepend="AND">B = #B#</isNotEmpty>
</dynamic>
[...]
See the "WHERE" keyword? Whenever tags are embedded, "prepend" tags are
pushed down the tree until the body of an embdedded tag is evaluated. In
the situation described above, the prepend="WHERE" would be pushed down
to the isNotEmpty tags. The tag for the "A" property is not evaluated,
so it moves on to the tag for the "B" property. In this case, its body
is evaluated so its "prepend" attribute becomes prepend="WHERE" and
prepend="AND" is pushed down (since no other tags are embedded, it
simply disapears). The result is:
WHERE
B = 'BValue'
In your situation you should simply add <dynamic prepend=" "> and
</dynamic> tags.
<dynamic> tags can be used in any kind of statement (even insert). Read
the SQLMap guide carefully, it's pretty clear on how dynamic tags and
the prepend attribute behave...
Cheers,
Philippe
Nilesh Bhattad wrote:
> Hi there,
>
>
>
> I have the below <insert/> statement defined in a sqlmap. If
> ‘BlackListed’ is not passed thro the HashMap, and rest of the fields are
> passed in, one comma is inserted before ‘isMarried’ column and it
> becomes an invalid sql stmt. I tried surrounding <isNotEmpty/> with
> <dynamic/> tag, but that didn’t help. (not sure if <dynamic/> can be
> used within in insert stmt or not)
>
>
>
> <insert id="insert" parameterClass="java.util.HashMap">
>
> insert into Consultant(
>
> <isNotEmpty property="BlackListed">BlackListed</isNotEmpty>
>
> <isNotEmpty property="isMarried"
> prepend=",">isMarried</isNotEmpty>
>
> <isNotEmpty property="ActiveInactive"
> prepend=",">ActiveInactive</isNotEmpty>
>
> <isNotEmpty property="HomeTelephone"
> prepend=",">HomeTelephone</isNotEmpty>
>
> <isNotEmpty property="OfficeTelephone"
> prepend=",">OfficeTelephone</isNotEmpty>
>
> <isNotEmpty property="OfficeTelExt"
> prepend=",">OfficeTelExt</isNotEmpty>
>
> ) values (
>
> <isNotEmpty property="BlackListed">#BlackListed#</isNotEmpty>
>
> <isNotEmpty property="isMarried"
> prepend=",">#isMarried#</isNotEmpty>
>
> <isNotEmpty property="ActiveInactive"
> prepend=",">#ActiveInactive#</isNotEmpty>
>
> <isNotEmpty property="HomeTelephone"
> prepend=",">#HomeTelephone#</isNotEmpty>
>
> <isNotEmpty property="OfficeTelephone"
> prepend=",">#OfficeTelephone#</isNotEmpty>
>
> <isNotEmpty property="OfficeTelExt"
> prepend=",">#OfficeTelExt#</isNotEmpty>
>
> )
>
> <selectKey resultClass="int" keyProperty="id">SELECT
> @@IDENTITY as last_insert_id</selectKey>
>
> </insert>
>
>
>
> Would someone please help me out to find what I’m doing wrong?
>
>
>
> Thanks
>
> Nilesh
>