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

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