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
