ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <lmead...@apache.org>
Subject Re: Multiple table insert problem.
Date Tue, 13 Dec 2005 20:54:35 GMT
Why not just simplify your life and make it a stored procedure?

Larry


On 12/13/05, Albert L. Sapp <asapp@uiuc.edu> wrote:
> 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.
>
> > public class GroupCreate extends BaseBean implements TransferObject
> > {
> >     static final long serialVersionUID = 0;
> >
> >     private Group group;
> >     private UserGroup userGroup;
> >     private UserRole userRole1;
> >     private UserRole userRole2;
> >
> >     public GroupCreate()
> >     {
> >         group = null;
> >         userGroup = null;
> >         userRole1 = null;
> >         userRole2 = null;
> >     }
> >
> >     public GroupCreate(Group group, UserGroup userGroup, UserRole
> > userRole1, UserRole userRole2)
> >     {
> >         this.group = group;
> >         this.userGroup = userGroup;
> >         this.userRole1 = userRole1;
> >         this.userRole2 = userRole2;
> >     }
> >
> >     public Group getGroup()
> >     {
> >         return group;
> >     }
> >
> >     public void setGroup(Group group)
> >     {
> >         this.group = group;
> >     }
> >
> >     public UserGroup getUserGroup()
> >     {
> >         return userGroup;
> >     }
> >
> >     public void setUserGroup(UserGroup userGroup)
> >     {
> >         this.userGroup = userGroup;
> >     }
> >
> >     public UserRole getUserRole1()
> >     {
> >         return userRole1;
> >     }
> >
> >     public void setUserRole1(UserRole userRole1)
> >     {
> >         this.userRole1 = userRole1;
> >     }
> >
> >     public UserRole getUserRole2()
> >     {
> >         return userRole2;
> >     }
> >
> >     public void setUserRole2(UserRole userRole2)
> >     {
> >         this.userRole2 = userRole2;
> >     }
> > }
>
>
> In the group.xml, I have the following defined.
>
> > <statement id="GroupInsert"
> > parameterClass="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>
>
> 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="GroupInsertNoBackup" and another bean that does not have
> userRole2 in it.
>
> Thanks for any help or pointers you can give me.
>
> Al
>

Mime
View raw message