Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: RE: Multiple table insert problem.
Date: Tue, 13 Dec 2005 15:24:54 -0600
Message-ID: <2F038EED15EF804A84175613FD39FA420241B9@exchdal04.Parsons.com>
Thread-Topic: Multiple table insert problem.
Thread-Index: AcYAKpQSsxLQkgrVSaarI8xnyM+7+QACQ/aQ
From: "Vu, Thai" <Thai.Vu@parsons.com>
To: <user-java@ibatis.apache.org>

By the way, I'd like to ask some questions.

- Is there any iBATIS example about calling an Oracle stored procedure
which returns a reference cursor?

- When I call a stored procedure, can I use a normal bean class instead
of a map (e.g. HashMap) for the <parameter> tag? I think I can answer
this question by trying both cases, but I'd also like to ask it here in
case I fail in both cases.

Sincerely,

Thai

-----Original Message-----
From: Albert L. Sapp [mailto:asapp@uiuc.edu]=20
Sent: Tuesday, December 13, 2005 3:17 PM
To: user-java@ibatis.apache.org
Subject: Re: Multiple table insert problem.

Larry Meadors wrote:

>Why not just simplify your life and make it a stored procedure?
>
>Larry
>
>
>On 12/13/05, Albert L. Sapp <asapp@uiuc.edu> wrote:
> =20
>
>>Hi, all.
>>
>>Our project has been going along well with iBatis and we have already
>>rolled out 5 of 7 modules.  They like us to automate as much as
possible
>>and not have the users going to multiple screens to do something, so
>>that is where we ran into the problem.
>>
>>This is for a Oracle database and I am learning PL/SQL and SQL as I
>>build this application, so I don't know if there is something in
PL/SQL
>>or SQL that would do this and not in iBatis.  I have been doing
searches
>>to see if anyone has an example of how to do this in PL/SQL or SQL,
but
>>apparently I am not searching correctly.
>>
>>When I create a group, I want to create entries in multiple tables.  A
>>entry in the group table, a entry for the group's leader in a group
>>member table, a entry for the group leader in the user roles table
>>indicating he has the group administrator role and, finally, another
>>entry in the user roles table if a backup group administrator is
entered
>>at group creation time.
>>
>>This is the bean that I pass to the insert function.
>>
>>   =20
>>
>>>public class GroupCreate extends BaseBean implements TransferObject
>>>{
>>>    static final long serialVersionUID =3D 0;
>>>
>>>    private Group group;
>>>    private UserGroup userGroup;
>>>    private UserRole userRole1;
>>>    private UserRole userRole2;
>>>
>>>    public GroupCreate()
>>>    {
>>>        group =3D null;
>>>        userGroup =3D null;
>>>        userRole1 =3D null;
>>>        userRole2 =3D null;
>>>    }
>>>
>>>    public GroupCreate(Group group, UserGroup userGroup, UserRole
>>>userRole1, UserRole userRole2)
>>>    {
>>>        this.group =3D group;
>>>        this.userGroup =3D userGroup;
>>>        this.userRole1 =3D userRole1;
>>>        this.userRole2 =3D userRole2;
>>>    }
>>>
>>>    public Group getGroup()
>>>    {
>>>        return group;
>>>    }
>>>
>>>    public void setGroup(Group group)
>>>    {
>>>        this.group =3D group;
>>>    }
>>>
>>>    public UserGroup getUserGroup()
>>>    {
>>>        return userGroup;
>>>    }
>>>
>>>    public void setUserGroup(UserGroup userGroup)
>>>    {
>>>        this.userGroup =3D userGroup;
>>>    }
>>>
>>>    public UserRole getUserRole1()
>>>    {
>>>        return userRole1;
>>>    }
>>>
>>>    public void setUserRole1(UserRole userRole1)
>>>    {
>>>        this.userRole1 =3D userRole1;
>>>    }
>>>
>>>    public UserRole getUserRole2()
>>>    {
>>>        return userRole2;
>>>    }
>>>
>>>    public void setUserRole2(UserRole userRole2)
>>>    {
>>>        this.userRole2 =3D userRole2;
>>>    }
>>>}
>>>     =20
>>>
>>In the group.xml, I have the following defined.
>>
>>   =20
>>
>>><statement id=3D"GroupInsert"
>>>parameterClass=3D"scs.reaction.user.beans.GroupCreate">
>>>    BEGIN
>>>    INSERT INTO REACTION_USERS.GROUPS (GROUP_ID,
>>>                                       GROUP_LEADER_ID,
>>>                                       GROUP_NAME,
>>>                                       DESCRIPTION,
>>>                                       CREATED_BY,
>>>                                       CREATED_ON,
>>>                                       MODIFIED_BY,
>>>                                       MODIFIED_ON,
>>>                                       CON_CHECK,
>>>                                       GROUP_ADMINISTRATOR)
>>>                               VALUES (#group.groupID:BIGINT#,
>>>                                       #group.groupLeaderID:BIGINT#,
>>>                                       #group.groupName:VARCHAR#,
>>>                                       #group.description:VARCHAR#,
>>>                                       #group.createdBy:BIGINT#,
>>>                                       #group.createdOn:TIMESTAMP#,
>>>                                       #group.modifiedBy:BIGINT#,
>>>                                       #group.modifiedOn:TIMESTAMP#,
>>>                                       #group.conCheck:BIGINT#,
>>>
#group.groupAdministrator:BIGINT#);
>>>    INSERT INTO REACTION_USERS.USER_GROUPS (USER_GROUP_ID,
>>>                                            USER_ID,
>>>                                            GROUP_ID,
>>>                                            MEMBERSHIP_STATUS,
>>>                                            CON_CHECK)
>>>                                    VALUES
>>>(#userGroup.userGroupID:BIGINT#,
>>>
#userGroup.userID:BIGINT#,
>>>
#userGroup.groupID:BIGINT#,
>>>
>>>#userGroup.membershipStatus:VARCHAR#,
>>>
#userGroup.conCheck:BIGINT#);
>>>    INSERT INTO REACTION_USERS.USER_ROLES (USER_ROLE_ID,
>>>                                           USER_ID,
>>>                                           ROLE_ID,
>>>                                           NETID,
>>>                                           ROLE)
>>>                                   VALUES
(#userRole1.userRoleID:BIGINT#,
>>>                                           #userRole1.userID:BIGINT#,
>>>                                           #userRole1.roleID:BIGINT#,
>>>                                           #userRole1.netID:VARCHAR#,
>>>                                           #userRole1.role:VARCHAR#);
>>>    INSERT INTO REACTION_USERS.USER_ROLES (USER_ROLE_ID,
>>>                                           USER_ID,
>>>                                           ROLE_ID,
>>>                                           NETID,
>>>                                           ROLE)
>>>                                   VALUES
(#userRole2.userRoleID:BIGINT#,
>>>                                           #userRole2.userID:BIGINT#,
>>>                                           #userRole2.roleID:BIGINT#,
>>>                                           #userRole2.netID:VARCHAR#,
>>>                                           #userRole2.role:VARCHAR#);
>>>    END;
>>></statement>
>>>     =20
>>>
>>This works if a backup group administrator is defined when the group
is
>>defined.  Otherwise, the last insert will fail because of null values.
>>Is there a way I can set this up to not do the last insert if the
>>userRole2 is null, but do it otherwise?  I don't want to set up a
>>seperate id=3D"GroupInsertNoBackup" and another bean that does not =
have
>>userRole2 in it.
>>
>>Thanks for any help or pointers you can give me.
>>
>>Al
>>
>>   =20
>>
>
> =20
>
I think that may be the only way to do it.  It will take a while to=20
learn to write a Oracle stored procedure to do what I want it to do,=20
then to write a call to the stored procedure.  I have never done either=20
before.  Always something new to learn.

Thanks for the suggestion.

Al