Return-Path: <dn@videone.dk>
Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm
Delivered-To: mailing list ibatis-user-java@incubator.apache.org
Received: (qmail 84735 invoked by uid 99); 11 Jan 2005 16:28:40 -0000
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=
X-Spam-Check-By: apache.org
Received-SPF: pass (hermes.apache.org: local policy)
Received: from Unknown (HELO post.videoone.dk) (213.150.43.107)
  by apache.org (qpsmtpd/0.28) with ESMTP; Tue, 11 Jan 2005 08:28:39 -0800
Received: from client25.videoone.dk ([192.168.0.25])
	by post.videoone.dk with esmtp (Exim 3.35 #1 (Debian))
	id 1CoOsn-0007wT-00
	for <ibatis-user-java@incubator.apache.org>; Tue, 11 Jan 2005 17:28:37 +0100
Message-ID: <41E3FEA4.7030101@videone.dk>
Date: Tue, 11 Jan 2005 17:28:20 +0100
From: David Nielsen <dn@videone.dk>
User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: ibatis-user-java@incubator.apache.org
Subject: raltation and inserts
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Checked: Checked

Hi again..

Yes i see a very simple insert and update in the Developer Guide, but no 
complex, like this one
se i have an Account class and multiple Group classes for this account. 
and the account class has a property

ArrayList groups
getter
setter

and this property is mapped this way:

    <typeAlias alias="account" type="dk.graveyard.vod.ibatis.Account"/>
    <typeAlias alias="group" type="dk.graveyard.vod.ibatis.Group"/>

    <resultMap id="accountResult" class="account">
        <result property="id" column="ACCOUNT_ID"/>
        <result property="name" column="ACCOUNT_NAME"/>
        <result property="password" column="PASSWORD"/>
        <result property="status" column="STATUS_NAME"/>
        <result property="groups" column="ACCOUNT_ID" 
select="getGroupsByAccountId"/>
    </resultMap>

    <resultMap id="groupResult" class="group">
        <result property="id" column="GROUP_ID"/>
        <result property="name" column="GROUP_NAME"/>
    </resultMap>


i cant just figure out how to map this n:m relation, for the insert and 
update


1 Account can have many Groups
1 Group Can belong to many Accounts


do i need to batch it
with something like:
updateAccount
deleteAccountGroupRealtion
insertNewAccountGroupRelation

every time i change an Account ??

or is there a more friendly way to do it ?




i tried a insert mapping for the relation table but......

    <insert id="insertAccountGroupsByAccount" parameterClass="account">
        <iterate property="groups">
            INSERT INTO ACCOUNT_GROUP_REF
            VALUES (
                #id#, #groups[].id#
            )
        </iterate>
    </insert>

but i get an exception:



2281 [thread-pool-98] DEBUG jdbc.SimpleDataSource  - Checked out 
connection 19318506 from pool.
2312 [thread-pool-98] DEBUG sql.Connection  - {conn-100038} Connection
2312 [thread-pool-98] DEBUG sql.PreparedStatement  - {pstm-100039} 
PreparedStatement:        INSERT INTO ACCOUNT_GROUP_REF    VALUES(     
?, ?    )   ;    INSERT INTO ACCOUNT_GROUP_REF    VALUES(     ?, ?    )
2312 [thread-pool-98] DEBUG sql.PreparedStatement  - {pstm-100039} 
Parameters: [1, 1, 1, 2]
2312 [thread-pool-98] DEBUG sql.PreparedStatement  - {pstm-100039} 
Types: [java.lang.Integer, java.lang.Integer, java.lang.Integer, 
java.lang.Integer]
2312 [thread-pool-98] DEBUG jdbc.SimpleDataSource  - Returned connection 
19318506 to pool.
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the insertAccountGroupsByAccount-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: You have an error in your SQL syntax.  
Check the manual that corresponds to your MySQL server version for the 
right syntax to use near ';                     INSERT INTO 
ACCOUNT_GROUP_REF                   VALUES(
Caused by: java.sql.SQLException: You have an error in your SQL syntax.  
Check the manual that corresponds to your MySQL server version for the 
right syntax to use near ';                     INSERT INTO 
ACCOUNT_GROUP_REF                   VALUES(
        at 
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:86)
        at 
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:446)
        at 
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:87)
        at 
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:56)


 >Hi,
 >
 >
 >
 >
 >I have 2 classes:
 >
 >Account
 >   private int id;
 >   private String username;
 >   private String password;
 >   private Collection groups;
 >
 >Group
 >   private int id;
 >   private String name;
 >
 >the db has 3 tables;
 >
 >ACCOUNT
 >GROUP
 >ACCOUNT_GROUP_REF
 >
 >where ACCOUNT_GROUP_REF is a join table for my relations,
 >with 2 collumns:
 >ACCOUNT_ID
 >GROUP_ID
 >
 >Now my question is how do i keep my ACCOUNT_GROUP_REF updated,
 >
 >
 >the Developer guide describes how to setup selects only,
 >but what about insert and updates, of the relations ??
 >
 >
 >Regards,
 >David Nielsen