iBatis2学习笔记：多对多映射(双向)

用户角色多对多模型

最新推荐文章于 2024-07-15 19:59:25 发布

原创最新推荐文章于 2024-07-15 19:59:25 发布 · 152 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#iBATIS #SQL #Apache #IDEA #XML

Java开发专栏收录该内容

11 篇文章

订阅专栏

本文介绍了用户角色多对多模型的设计，包括角色、用户、连接表的定义及操作，以及相关DAO接口实现。

/*==============================================================*/ 
/* Table: role                                                  */ 
/*==============================================================*/ 
create table role 
( 
   id                   bigint not null auto_increment, 
   rolename             varchar(24), 
   descp                varchar(240), 
   primary key (id) 
); 

alter table role comment '角色'; 

/*==============================================================*/ 
/* Table: tlink                                                 */ 
/*==============================================================*/ 
create table tlink 
( 
   userId               bigint not null, 
   roleId               bigint not null 
); 

alter table tlink comment '连接表'; 

/*==============================================================*/ 
/* Table: user                                                  */ 
/*==============================================================*/ 
create table user 
( 
   id                   bigint not null auto_increment, 
   username             varchar(24), 
   remark               varchar(240), 
   primary key (id) 
); 

alter table user comment '用户'; 

alter table tlink add constraint FK_r foreign key (roleId) 
      references role (id) on delete restrict on update restrict; 

alter table tlink add constraint FK_u foreign key (userId) 
      references user (id) on delete restrict on update restrict;

/** 
* Created by IntelliJ IDEA.<br> 
* <b>User</b>: leizhimin<br> 
* <b>Date</b>: 2008-6-16 0:12:13<br> 
* <b>Note</b>: 用户角色多对多模型：角色 
*/ 
public class User { 
    private Long id; 
    private String username; 
    private String remark; 
    private List<Role> roleList = new ArrayList<Role>(); 


    public String toString() { 
        return "User{" + 
                "id=" + id + 
                ", username='" + username + '\'' + 
                ", remark='" + remark + '\'' + 
                ", roleList='" + roleList.size() + '\'' + 
                '}'; 
    } 

    public String out() { 
        StringBuffer sb = new StringBuffer(); 
        sb.append("User{" + 
                "id=" + id + 
                ", username='" + username + '\'' + 
                ", remark='" + remark + '\'' + 
                ", roleList='" + roleList.size() + '\''); 
        for (Role role : roleList) { 
            sb.append("\n\t").append(role.toString()); 
        } 
        return sb.toString(); 
    }

public class Role { 
    private Long id; 
    private String rolename; 
    private String descp; 
    private List<User> userList= new ArrayList<User>(); 

    public String toString() { 
        return "Role{" + 
                "id=" + id + 
                ", rolename='" + rolename + '\'' + 
                ", descp='" + descp + '\'' + 
                ", userList=" + userList.size() + 
                '}'; 
    } 

    public String out(){ 
        StringBuffer sb= new StringBuffer(); 
        if(userList.size()>0){ 
            sb.append("Role{" + 
                "id=" + id + 
                ", rolename='" + rolename + '\'' + 
                ", descp='" + descp + '\'' + 
                ", userList=" + userList.size()); 
            for(User u: userList){ 
                sb.append("\n\t").append(u.toString()); 
            } 
            sb.append("\n}"); 
        } 
        return sb.toString(); 
    }

/** 
* Created by IntelliJ IDEA.<br> 
* <b>User</b>: leizhimin<br> 
* <b>Date</b>: 2008-6-16 0:17:15<br> 
* <b>Note</b>: 用户角色多对多模型：连接表 
*/ 
public class Tlink { 
    private Long id; 
    private Long userId; 
    private Long roleId;

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" > 
<sqlMap namespace="user"> 
    <typeAlias alias="user" type="com.lavasoft.ssi.domain.User"/> 
    <resultMap id="result_basc" class="user"> 
        <result property="id" column="id"/> 
        <result property="username" column="username"/> 
        <result property="remark" column="remark"/> 
    </resultMap> 

    <resultMap id="result" class="user" extends="result_basc"> 
        <result property="roleList" column="id" select="role.getByUserId"/> 
    </resultMap> 


    <insert id="insert" parameterClass="user"> 
        insert into user(username,remark) values(#username#,#remark#) 
        <selectKey keyProperty="id" resultClass="long"> 
            select LAST_INSERT_ID() 
        </selectKey> 
    </insert> 
    <select id="getById" parameterClass="long" resultMap="result_basc"> 
        select * from user where id = #value# 
    </select> 

    <select id="getWithCashWithRoleList" parameterClass="long" resultMap="result"> 
        select * from user where id = #value# 
    </select> 

    <select id="getByRoleId" parameterClass="long" resultMap="result_basc"> 
        select u.* from user u where u.id in 
        (select userId from tlink where roleId=#value#) 
    </select> 


</sqlMap>

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" > 
<sqlMap namespace="role"> 
    <typeAlias alias="role" type="com.lavasoft.ssi.domain.Role"/> 
    <resultMap id="result_basc" class="role"> 
        <result property="id" column="id"/> 
        <result property="rolename" column="rolename"/> 
        <result property="descp" column="descp"/> 
    </resultMap> 
    <resultMap id="result" class="role" extends="result_basc"> 
        <result property="userList" column="id" select="user.getByRoleId"/> 
    </resultMap> 
    <insert id="insert" parameterClass="role"> 
        insert into role(rolename,descp) values(#rolename#,#descp#) 
        <selectKey keyProperty="id" resultClass="long"> 
            select LAST_INSERT_ID() 
        </selectKey> 
    </insert> 
    <select id="getById" parameterClass="long" resultMap="result_basc"> 
        select * from role where id = #value# 
    </select> 

    <select id="getRoleByIdWithCashUser" parameterClass="long" resultMap="result"> 
        select * from role where id = #value# 
    </select> 

    <!--为多对多配置--> 
    <select id="getByUserId" parameterClass="long" resultClass="role" resultMap="result_basc"> 
        select r.* from role r where r.id in 
        (select roleId from tlink where userId=#value#) 
    </select> 
</sqlMap>

<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" > 
<sqlMap namespace="tlink"> 
    <typeAlias alias="tlink" type="com.lavasoft.ssi.domain.Tlink"/> 
    <resultMap id="result" class="tlink"> 
        <result property="id" column="id"/> 
        <result property="userId" column="userId"/> 
        <result property="roleId" column="roleId"/> 
    </resultMap> 
    <insert id="insert" parameterClass="tlink"> 
        insert into tlink(userId,roleId) values(#userId#,#roleId#) 
        <selectKey keyProperty="id" resultClass="long"> 
            select LAST_INSERT_ID() 
        </selectKey> 
    </insert> 
    <select id="getByUserId" parameterClass="long" resultMap="result"> 
        select * from tlink where userId = #value# 
    </select> 
    <select id="getByRoleId" parameterClass="long" resultMap="result"> 
        select * from tlink where roleId = #value# 
    </select> 
    <delete id="delete" parameterClass="tlink"> 
        delete from tlink where userId = #userId# and roleId = #roleId# 
    </delete> 

</sqlMap>

public interface UserDAO { 
    public Long insert(User user); 
    public Object getById(Long id); 
    public Object getWithCashById(Long id); 
    public User getWithCashWithRoleList(Long id); 
}

public interface RoleDAO { 
    public Long insert(Role role); 
    public Role getById(Long id); 
    public Role getRoleByIdWithCashUser(Long id); 
    public List<Role> getByUserId(Long userId); 
}

public interface TlinkDAO { 
    public void insert(Long userId,Long roleId); 
    public int delete(Long userId,Long roleId); 
    public int update(Long userId,Long roleId); 
}

public class UserDAOImpl extends SqlMapClientDaoSupport implements UserDAO { 
    public Long insert(User user) { 
        return (Long) getSqlMapClientTemplate().insert("user.insert",user); 
    } 

    public Object getById(Long id) { 
        return getSqlMapClientTemplate().queryForList("user.getById",id); 
    } 

    public Object getWithCashById(Long id) { 
        return getSqlMapClientTemplate().queryForList("user.getWithCashById",id); 
    } 

    public User getWithCashWithRoleList(Long userId) { 
        return (User) getSqlMapClientTemplate().queryForObject("user.getWithCashWithRoleList",userId); 
    } 
}

public class RoleDAOImpl extends SqlMapClientDaoSupport implements RoleDAO { 
    public Long insert(Role role) { 
        return (Long) getSqlMapClientTemplate().insert("role.insert",role); 
    } 

    public Role getById(Long id) { 
        return (Role) getSqlMapClientTemplate().queryForObject("role.getById",id); 
    } 

    public Role getRoleByIdWithCashUser(Long id) { 
        return (Role) getSqlMapClientTemplate().queryForObject("role.getRoleByIdWithCashUser",id); 
    } 

    public List<Role> getByUserId(Long userId) { 
        return getSqlMapClientTemplate().queryForList("role.getByUserId",userId); 
    } 
}

public class TlinkDAOImpl extends SqlMapClientDaoSupport implements TlinkDAO { 
    public void insert(Long userId, Long roleId) { 
        Tlink tlink = new Tlink(userId, roleId); 
        getSqlMapClientTemplate().insert("tlink.insert",tlink); 
    } 

    public int delete(Long userId, Long roleId) { 
        Tlink tlink = new Tlink(userId, roleId); 
        return getSqlMapClientTemplate().delete("tlink.delete",tlink); 
    } 

    public int update(Long userId, Long roleId) { 
        return 0; 
    } 
}