How to create insert statement with two parameters dynamically?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How to create insert statement with two parameters dynamically?

陈抒
Hello,All:
   I want to create a statement like following:
insert into UserHasRole (id,rol_id) values (1,5) (1,6) ....  
   I have a User object and one List<Role> object. In the above case,User.id is 1 and two Role objects' id are 5 and 6.
   Do you have any cool idea?

Reply | Threaded
Open this post in threaded view
|

Re: How to create insert statement with two parameters dynamically?

Nathan Maves
Yeah!

Read the developers guide!  I could give you the page number but what fun would that be :)

Nathan

On Thu, Feb 24, 2011 at 4:44 AM, 陈抒 <[hidden email]> wrote:
Hello,All:
   I want to create a statement like following:
insert into UserHasRole (id,rol_id) values (1,5) (1,6) ....  
   I have a User object and one List<Role> object. In the above case,User.id is 1 and two Role objects' id are 5 and 6.
   Do you have any cool idea?


Reply | Threaded
Open this post in threaded view
|

Re: How to create insert statement with two parameters dynamically?

陈抒
I have used foreach statement in MyBatis. Do you mean that?

陈抒
Best regards
http://blog.csdn.net/sheismylife


On Thu, Feb 24, 2011 at 10:34 PM, Nathan Maves <[hidden email]> wrote:
Yeah!

Read the developers guide!  I could give you the page number but what fun would that be :)

Nathan


On Thu, Feb 24, 2011 at 4:44 AM, 陈抒 <[hidden email]> wrote:
Hello,All:
   I want to create a statement like following:
insert into UserHasRole (id,rol_id) values (1,5) (1,6) ....  
   I have a User object and one List<Role> object. In the above case,User.id is 1 and two Role objects' id are 5 and 6.
   Do you have any cool idea?



Reply | Threaded
Open this post in threaded view
|

Fwd: How to create insert statement with two parameters dynamically?

陈抒
I realized these emails were not sent to mybatis mailing list. I forward them now.

陈抒
Best regards
http://blog.csdn.net/sheismylife


---------- Forwarded message ----------
From: 陈抒 <[hidden email]>
Date: Fri, Feb 25, 2011 at 9:16 AM
Subject: Re: How to create insert statement with two parameters dynamically?
To: Nathan Maves <[hidden email]>


It works.Thank you!
I thought XML can do everything,but it seems things have changed,annotation can provide some helps.

  <insert id="addUserRoles">
    insert into userhasrole (id,rol_id) values
    <foreach item="role" index="index" collection="list" open="(" separator="," close=")">
      #{user.id},#{role.id}
    </foreach>
  </insert>

public Integer addUserRoles(@Param("user") User user,@Param("list") List<Role> roles);
On Fri, Feb 25, 2011 at 8:29 AM, Nathan Maves <[hidden email]> wrote:
in your mapper user the @Param annotation to name each parameter.


On Thu, Feb 24, 2011 at 5:25 PM, 陈抒 <[hidden email]> wrote:
But how to do it? 
I know how to use foreach with a  ArrayList parameter in MyBatis,but I don't know how to pass two parameters(i.e. User and List<Role> at one time. 

  <insert id="addUserRoles" parameterType="User" >
    insert into userhasrole (id,rol_id) values
    <foreach item="role" index="index" collection="list" open="(" separator="," close=")">
      #{id},#{role.id}
    </foreach>
  </insert>

I got the following error message:
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.mapping.SqlMapperException: The expression 'list' evaluated to a null value.
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
at $Proxy168.insert(Unknown Source)

I am using MySQL.
On Fri, Feb 25, 2011 at 12:53 AM, Nathan Maves <[hidden email]> wrote:
Yes you can do that if your driver supports it.  Otherwise you will have to make multiple calls to your insert mapper.


On Thu, Feb 24, 2011 at 8:08 AM, 陈抒 <[hidden email]> wrote:
I have used foreach statement in MyBatis. Do you mean that?
On Thu, Feb 24, 2011 at 10:34 PM, Nathan Maves <[hidden email]> wrote:
Yeah!

Read the developers guide!  I could give you the page number but what fun would that be :)

Nathan


On Thu, Feb 24, 2011 at 4:44 AM, 陈抒 <[hidden email]> wrote:
Hello,All:
   I want to create a statement like following:
insert into UserHasRole (id,rol_id) values (1,5) (1,6) ....  
   I have a User object and one List<Role> object. In the above case,User.id is 1 and two Role objects' id are 5 and 6.
   Do you have any cool idea?