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

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

 

环境：
MySQL5.51b
iBatis 2.3

Spring 2.5.4

 

这个多对多不容易，费了好大劲才实现。

 

一、模型

 

用户（User）和角色（Role）之间是多对多的关系。

 

二、SQL

/*==============================================================*/
/* 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;

 

三、POJO

/**
* 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;

 

四、SqlMap

User.xml

<?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>

 

Role.xml

<?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>

 

Tlink.xml

<?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>

 

五、DAO

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;
    }
}

 

 

六、测试类

public class UserDAOTest{
    private UserDAO userDAO = (UserDAO) ApplicationContextUtils.getApplicationContext().getBean("userDAO");

    public void testInsert() {
        System.out.println("-------insert(User user)--------");
        User user = new User();
        user.setUsername("熔岩");
        user.setRemark("系统用户");
        Long pk = userDAO.insert(user);
        System.out.println("所插入数据ID=" + pk);
    }

    public void testGetById() {
        System.out.println("-------getById(Long id)-------");
        Object object = userDAO.getById(3L);
        System.out.println(object);
    }

    public void testGetWithCashById() {
        System.out.println("-------getWithCashById(Long id)-------");
        Object object = userDAO.getWithCashById(1L);
        System.out.println(object);
    }

    public void getWithCashWithRoleList(){
        System.out.println("-------getWithCashWithRoleList()-------");
        User user = userDAO.getWithCashWithRoleList(1L);
        System.out.println(user.out());
    }

    public static void main(String args[]){
        System.out.println("正在测试UserDAO");
        UserDAOTest userDAOTest  = new UserDAOTest();
        userDAOTest.testInsert();
        userDAOTest.testGetById();
        userDAOTest.getWithCashWithRoleList();
    }
}

 

/**
* Created by IntelliJ IDEA.<br>
* <b>User</b>: leizhimin<br>
* <b>Date</b>: 2008-6-16 1:05:57<br>
* <b>Note</b>: 用户角色多对多模型：角色
*/
public class RoleDAOTest{
    RoleDAO roleDAO = (RoleDAO) ApplicationContextUtils.getApplicationContext().getBean("roleDAO");
    public void testInsert() {
        System.out.println("------insert(Role role)-----");
        Role role = new Role();
        role.setRolename("admin");
        role.setDescp("管理员");
        Long pk = roleDAO.insert(role);
        System.out.println("所插入数据ID="+pk);
    }

    public void testGetById() {
        System.out.println("------getById(Long id)-----");
        Object obj = roleDAO.getById(1L);
        System.out.println("查询结果"+obj);
    }

    public void testGetRoleByIdWithCashUser() {
        System.out.println("------getRoleByIdWithCashUser(Long id)-----");
        Role obj = roleDAO.getRoleByIdWithCashUser(1L);
        System.out.println("查询结果"+obj.out());
    }

    public void testGetByUserId(){
        System.out.println("------getByUserId(Long userId)-----");
        List<Role> roleList= roleDAO.getByUserId(1L);
        for(Role r: roleList){
            System.out.println(r);
        }        
    }

    public static void main(String args[]){
        System.out.println("正在测试RoleDAO");
        RoleDAOTest roleDAOTest = new RoleDAOTest();
        roleDAOTest.testInsert();
        roleDAOTest.testGetById();
        roleDAOTest.testGetRoleByIdWithCashUser();
        roleDAOTest.testGetByUserId();
    }
}

 

public class TlinkDAOTest {
    TlinkDAO tlinkDAO = (TlinkDAO) ApplicationContextUtils.getApplicationContext().getBean("tlinkDAO");

    public void testInsert(){
        tlinkDAO.insert(1L,1L);
        tlinkDAO.insert(1L,2L);
        tlinkDAO.insert(1L,3L);
    }

    public static void main(String args[]){
        TlinkDAOTest tlinkDAOTest = new TlinkDAOTest();
        tlinkDAOTest.testInsert();
    }
}

 

七、测试结果

正在测试UserDAO
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext).
log4j:WARN Please initialize the log4j system properly.
-------insert(User user)--------
所插入数据ID=10
-------getById(Long id)-------
[User{id=3, username='熔岩', remark='系统用户', roleList='0'}]
-------getWithCashWithRoleList()-------
User{id=1, username='熔岩', remark='系统用户', roleList='3'
    Role{id=1, rolename='r1', descp='aaa', userList=0}
    Role{id=2, rolename='r2', descp='bbb', userList=0}
    Role{id=3, rolename='r3', descp='ccc', userList=0}

Process finished with exit code 0

 

 

正在测试RoleDAO
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext).
log4j:WARN Please initialize the log4j system properly.
------insert(Role role)-----
所插入数据ID=10
------getById(Long id)-----
查询结果Role{id=1, rolename='r1', descp='aaa', userList=0}
------getRoleByIdWithCashUser(Long id)-----
查询结果Role{id=1, rolename='r1', descp='aaa', userList=3
    User{id=1, username='熔岩', remark='系统用户', roleList='0'}
    User{id=2, username='熔岩', remark='系统用户', roleList='0'}
    User{id=3, username='熔岩', remark='系统用户', roleList='0'}
}
------getByUserId(Long userId)-----
Role{id=1, rolename='r1', descp='aaa', userList=0}
Role{id=2, rolename='r2', descp='bbb', userList=0}
Role{id=3, rolename='r3', descp='ccc', userList=0}

Process finished with exit code 0

 

测试结果表明：双向多对多彻底搞定！