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

 

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