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 Re: Multiple table insert problem.
Date Tue, 13 Dec 2005 21:16:43 GMT
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:
>  
>
>>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
>>
>>    
>>
>
>  
>
I think that may be the only way to do it.  It will take a while to 
learn to write a Oracle stored procedure to do what I want it to do, 
then to write a call to the stored procedure.  I have never done either 
before.  Always something new to learn.

Thanks for the suggestion.

Al


Mime
View raw message