iBatis2学习笔记：多对多映射(双向)
/*==============================================================*/ /* 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" column="id"/> <result property="username" column="username"/> <result property="remark" column="remark"/> </resultMap> <resultMap id="result" extends="result_basc"> <result property="roleList" column="id" select="role.getByUserId"/> </resultMap> <insert id="insert" parameterresultparameterresultMap="result_basc"> select * from user where id = #value# </select> <select id="getWithCashWithRoleList" parameterresultMap="result"> select * from user where id = #value# </select> <select id="getByRoleId" parameterresultMap="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" column="id"/> <result property="rolename" column="rolename"/> <result property="descp" column="descp"/> </resultMap> <resultMap id="result" extends="result_basc"> <result property="userList" column="id" select="user.getByRoleId"/> </resultMap> <insert id="insert" parameterresultparameterresultMap="result_basc"> select * from role where id = #value# </select> <select id="getRoleByIdWithCashUser" parameterresultMap="result"> select * from role where id = #value# </select> <!--为多对多配置--> <select id="getByUserId" parameterresultresultMap="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" column="id"/> <result property="userId" column="userId"/> <result property="roleId" column="roleId"/> </resultMap> <insert id="insert" parameterresultparameterresultMap="result"> select * from tlink where userId = #value# </select> <select id="getByRoleId" parameterresultMap="result"> select * from tlink where roleId = #value# </select> <delete id="delete" parametername="code">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; } }?