ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Albert L. Sapp" <as...@uiuc.edu>
Subject Multiple table insert problem.
Date Tue, 13 Dec 2005 20:30:22 GMT
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