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