Why not just simplify your life and make it a stored procedure?
Larry
On 12/13/05, Albert L. Sapp <[EMAIL PROTECTED]> 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
>
