一、使用IDEA新建maven工程
二、引入mybatis以及相關的jar
<!--版本僅供參考-->
<dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.4.5</version></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.44</version></dependency></dependencies>
三、建表
權限管理需求的介紹，一個用戶擁有若干角色，一個角色擁有若干權限，權限就是對某個資源的增刪改查，這樣就構成了用戶-角色-權限之間的模型，在這種關系中，用戶與角色，角色與權限之間一般是多對多的關系
建表sql：
create table sys_user(id int not null auto_increment comment '用戶ID',user_name varchar(32) comment '用戶名',password varchar(50) comment '用戶密碼',usr_info varchar(50) comment '用戶簡介',head_img blob comment '頭像',create_time datetime comment '創建時間',primary key(id) ); alter table sys_user comment '用戶表';create table sys_role(id int not null auto_increment comment '角色ID',role_name varchar(50) comment '角色名稱',enabled int comment '有效標志',create_by bigint comment '創建人',create_time datetime comment '創建時間',primary key(id) ); alter table sys_role comment '角色表';create table sys_privilege(id int not null auto_increment comment '權限ID',privilege_name varchar(50) comment '權限名稱',privilege_url varchar(50) comment '權限URL',primary key(id) ); alter table sys_privilege comment '權限表';create table sys_user_role(user_id bigint not null comment '用戶ID',role_id bigint not null comment '角色ID' ); alter table sys_user comment '用戶角色'; create table sys_role_privilege(role_id bigint not null comment '角色ID',privilege_id bigint not null comment '權限ID' ); alter table sys_role_privilege comment '角色權限表';
給表添加外鍵
-- 給用戶角色表的user_id添加外鍵 alter table sys_user_role add constraint fk_uru foreign key(user_id) references sys_user(id); -- 給用戶角色表的role_id 添加外鍵 alter table sys_user_role add constraint fk_sysrur foreign key(role_id) references sys_role(id); -- 給角色權限表的role_id添加外鍵 alter table sys_role_privilege add constraint fk_sysrpp foreign key(privilege_id) references sys_privilege(id); -- 給角權限的privilege_id 添加外鍵 alter table sys_role_privilege add constraint fk_sysrpr foreign key(role_id) references sys_role(role_id);
插入數據
insert into sys_user(user_name,password,user_info,head_img,create_time )values('Jordan','123','管理員',null,'2018-1-22 12:02:01'),
('Alice','456','測試人員',null,'2018-1-22 12:02:01'),
('Alex','789','開發人員',null,'2018-1-22 12:02:01'),
('James','012','運維人員',null,'2018-1-22 12:02:01')
insert into sys_user_role(role_name,enabled,create_by,create_time) values('管理員',1,'1','2018-01-02'),values('普通用戶',1,'1','2018-01-02');
insert into sys_privilege(privilege_name,privilege_url) values('用戶管理','/users'),('角色管理','/roles'),('系統維護','/system'),('日志管理','/logs');
insert into sys_user_role values(1,1),(2,2),(3,2),(4,2);
insert into sys_role_privilege values(1,1),(2,3),(2,4); 四、使用逆向工程生成entity以及mapper接口和mapper文件
sql語句一對多查詢、 ①：創建mybatis-config.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><settings><setting name="mapUnderscoreToCamelCase" value="true"/></settings><typeAliases><package name="com.jordan.mybatis.entity"></package></typeAliases><environments default="defaultEnv"><environment id="defaultEnv"><transactionManager type="JDBC"></transactionManager><dataSource type="POOLED"><property name="username" value="root"></property><property name="password" value="123456"></property><property name="url" value="jdbc:mysql://localhost:3306/mybatis"></property><property name="driver" value="com.mysql.jdbc.Driver"></property></dataSource></environment></environments><mappers><package name="com.jordan.mybatis.mapper"></package></mappers> </configuration>
②：SysUser.java
package com.jordan.mybatis.entity;import java.util.Arrays; import java.util.Date; import java.util.List;public class SysUser {private Long id;private String userName;private String password;private Date createTime;private String userInfo;private byte[] headImg;private SysRole sysRole;private List<SysRole> sysRoleList;public List<SysRole> getSysRoleList() {return sysRoleList;}public void setSysRoleList(List<SysRole> sysRoleList) {this.sysRoleList = sysRoleList;}public SysRole getSysRole() {return sysRole;}public void setSysRole(SysRole sysRole) {this.sysRole = sysRole;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName == null ? null : userName.trim();}public String getPassword() {return password;}public void setPassword(String password) {this.password = password == null ? null : password.trim();}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}public String getUserInfo() {return userInfo;}public void setUserInfo(String userInfo) {this.userInfo = userInfo == null ? null : userInfo.trim();}public byte[] getHeadImg() {return headImg;}public void setHeadImg(byte[] headImg) {this.headImg = headImg;}@Overridepublic String toString() {return "SysUser{" +"id=" + id +", userName='" + userName + '\'' +", password='" + password + '\'' +", createTime=" + createTime +", userInfo='" + userInfo + '\'' +", headImg=" + Arrays.toString(headImg) +", sysRole=" + sysRole +", sysRoleList=" + sysRoleList +'}';} }
③：SysRole.java
package com.jordan.mybatis.entity;import java.util.Arrays; import java.util.Date; import java.util.List;public class SysUser {private Long id;private String userName;private String password;private Date createTime;private String userInfo;private byte[] headImg;private SysRole sysRole;private List<SysRole> sysRoleList;public List<SysRole> getSysRoleList() {return sysRoleList;}public void setSysRoleList(List<SysRole> sysRoleList) {this.sysRoleList = sysRoleList;}public SysRole getSysRole() {return sysRole;}public void setSysRole(SysRole sysRole) {this.sysRole = sysRole;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName == null ? null : userName.trim();}public String getPassword() {return password;}public void setPassword(String password) {this.password = password == null ? null : password.trim();}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}public String getUserInfo() {return userInfo;}public void setUserInfo(String userInfo) {this.userInfo = userInfo == null ? null : userInfo.trim();}public byte[] getHeadImg() {return headImg;}public void setHeadImg(byte[] headImg) {this.headImg = headImg;}@Overridepublic String toString() {return "SysUser{" +"id=" + id +", userName='" + userName + '\'' +", password='" + password + '\'' +", createTime=" + createTime +", userInfo='" + userInfo + '\'' +", headImg=" + Arrays.toString(headImg) +", sysRole=" + sysRole +", sysRoleList=" + sysRoleList +'}';} }
④：SysPrivilege.java
package com.jordan.mybatis.entity;public class SysPrivilege {private Long id;private String privilegeName;private String privilegeUrl;public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getPrivilegeName() {return privilegeName;}public void setPrivilegeName(String privilegeName) {this.privilegeName = privilegeName == null ? null : privilegeName.trim();}public String getPrivilegeUrl() {return privilegeUrl;}public void setPrivilegeUrl(String privilegeUrl) {this.privilegeUrl = privilegeUrl == null ? null : privilegeUrl.trim();}@Overridepublic String toString() {return "SysPrivilege{" +"id=" + id +", privilegeName='" + privilegeName + '\'' +", privilegeUrl='" + privilegeUrl + '\'' +'}';} }
⑤:SysUserMapper
public interface SysUserMapper {public List<SysUser> getUserRolePrivilege(); }
mybatis自關聯。 ⑥：SysUserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.jordan.mybatis.mapper.SysUserMapper"><resultMap id="getUserRolePrivilege" type="SysUser"><id property="id" column="id"></id><result property="userName" column="user_name"></result><result property="password" column="password"></result><result property="userInfo" column="user_info"></result><result property="createTime" column="create_time"></result><collection property="sysRoleList" ofType="SysRole"><id property="id" column="id"></id><result property="roleName" column="role_name"></result><result property="createTime" column="create_time"></result><collection property="sysPrivilegeList" ofType="SysPrivilege"><id property="id" column="id"></id><result property="privilegeName" column="privilege_name"></result><result property="privilegeUrl" column="privilege_url"></result></collection></collection></resultMap><select id="getUserRolePrivilege" resultMap="getUserRolePrivilege">select a.id,a.user_name,a.password,a.user_info,b.role_name,b.create_time,c.privilege_name,c.privilege_url from sys_user a,sys_role b,sys_privilege c,sys_user_role d,sys_role_privilege e where a.id = d.user_id and b.id = d.role_id and e.role_id=b.id and e.privilege_id=c.id</select> </mapper>
五、在mybatis-config.xml中添加mapper接口以及mapper文件
<mappers><package name="com.jordan.mybatis.mapper"></package> </mappers>
六、創建測試類Test.java
import com.jordan.mybatis.mapper.SysUserMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Before;import java.io.IOException; import java.io.InputStream; import java.io.Reader;/*** @author Jordan* @create * @DESCRIPTION*/ public class Test {private SqlSessionFactory sqlSessionFactory;@Beforepublic void init() throws IOException{//1:讀取mybatis的主配置文件String configFile = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(configFile);//2:通過sqlSessionFactoryBuilder創建一個sqlSessionFactorysqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);}@org.junit.Testpublic void testGetUserRolePrivilege(){SqlSession sqlSession=sqlSessionFactory.openSession();SysUserMapper sysUserMapper=sqlSession.getMapper(SysUserMapper.class);System.out.println(sysUserMapper.getUserRolePrivilege().size()); //4 查詢結果與數據庫查詢結果不匹配，發生數據覆蓋問題，解決辦法參考注意事項②
sqlSession.close();
} }
七、注意事項：
①：idea找不到mapper文件的原因：IDEA不會編譯src的java目錄下的xml文件。所以添加如下代碼
<resources><!-- resources文件 --><resource><directory>src/main/resources</directory><!-- 是否被過濾,如果被過濾則無法使用 --><filtering>false</filtering></resource><!-- java文件夾 --><resource><directory>src/main/java</directory><!-- 引入映射文件等 --><includes><include>**/*.xml</include><include>**/*.properties</include></includes></resource><resource><directory>src/main/webapp</directory><includes><include>**/*.*</include></includes></resource></resources>
②：mybatis在映射依賴的過程中，如果兩個表中的主鍵是一樣的會發生數據覆蓋問題。
解決辦法： ?
sql一對多查詢？ 解決方式一：修改數據庫表中的主鍵（這種方法比較麻煩）；
解決方式二：在查詢語句中修改一個表的主鍵使其不一致即可；
上述代碼測試結果中與數據庫中的查詢記錄不匹配
數據庫查詢結果為8條記錄，但是mybatis查詢只有4條數據，發生數據覆蓋的問題；
mybatis多表查詢。修改SysUserMapper.xml文件：
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.jordan.mybatis.mapper.SysUserMapper"><resultMap id="getUserRolePrivilege" type="SysUser"><id property="id" column="aid"></id><result property="userName" column="user_name"></result><result property="password" column="password"></result><result property="userInfo" column="user_info"></result><result property="createTime" column="create_time"></result><collection property="sysRoleList" ofType="SysRole"><id property="id" column="bid"></id><result property="roleName" column="role_name"></result><result property="createTime" column="create_time"></result><collection property="sysPrivilegeList" ofType="SysPrivilege"><id property="id" column="cid"></id><result property="privilegeName" column="privilege_name"></result><result property="privilegeUrl" column="privilege_url"></result></collection></collection></resultMap><select id="getUserRolePrivilege" resultMap="getUserRolePrivilege">select a.id,a.user_name,a.password,a.user_info,b.role_name,b.create_time,c.privilege_name,c.privilege_url from sys_user a,sys_role b,sys_privilege c,sys_user_role d,sys_role_privilege e where a.id = d.user_id and b.id = d.role_id and e.role_id=b.id and e.privilege_id=c.id</select> </mapper>
?
