作者：密叔
Mybatis 是什么？
Mybatis 是一个持久层框架。Java 中通过 Mybatis ，程序可以很方便的对数据库进行操作。使开发者专注于 SQL语句，避免了重复的 JDBC 代码。
使用 MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集
灵活且强大，支持定制化 SQL、存储过程以及高级映射
支持 XML 和注解两种形式编写 SQL
数据库中的记录可以映射为普通 Java 对象
准备
环境：
开发工具：IDEA
包管理：Maven
JDK：1.8
Mybatis ：Mybatis 3.4.6
问题场景
使用 Mybatis 完成基于简单权限设计的数据库表的相关操作、查询
涉及到的表
t_usert_user_infot_rolet_menut_user_role //关系表t_role_menu //关系表
要做哪些操作
t_user 表的简单增删改成
关联复杂查询，查询一个用户的所有权限信息
动态查询，包含某些菜单权限的用户
1、新建数据库表，初始化些数据
-- ------------------------------ Table structure for `t_menu`-- ----------------------------DROP TABLE IF EXISTS `t_menu`;CREATE TABLE `t_menu` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `type` int(11) DEFAULT NULL, `url` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for `t_role`-- ----------------------------DROP TABLE IF EXISTS `t_role`;CREATE TABLE `t_role` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for `t_role_menu`-- ----------------------------DROP TABLE IF EXISTS `t_role_menu`;CREATE TABLE `t_role_menu` ( `role_id` int(10) DEFAULT NULL, `menu_id` int(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for `t_user`-- ----------------------------DROP TABLE IF EXISTS `t_user`;CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `password` varchar(50) DEFAULT NULL, `nickname` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `t_user_username_uindex` (`username`)) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for `t_user_role`-- ----------------------------DROP TABLE IF EXISTS `t_user_role`;CREATE TABLE `t_user_role` ( `user_id` int(10) DEFAULT NULL, `role_id` int(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;SET FOREIGN_KEY_CHECKS = 1;-- ------------------------------ Table structure for `t_user_info`-- ----------------------------DROP TABLE IF EXISTS `t_user_info`;CREATE TABLE `t_user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `name` varchar(100) COLLATE utf8_bin DEFAULT NULL, `email` varchar(100) COLLATE utf8_bin DEFAULT NULL, `address` varchar(255) COLLATE utf8_bin DEFAULT NULL, `phone` varchar(20) COLLATE utf8_bin DEFAULT NULL, `sex` varchar(2) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;SET FOREIGN_KEY_CHECKS = 1;
手动初始化些数据进去，构建一些关联数据，为下面测试所用
2、构建Maven项目
组织 project 目录
└── src ├── main │ ├── java │ │ └── me │ │ └── imcoding │ │ ├── dao │ │ ├── mapper │ │ ├── model │ └── resources │ └── mapper └── test └── java └── me └── imcoding
3、引入 Mybatis 相关依赖
在 maven 环境中，需要在 pom.xml 文件中增加依赖
<dependencies> <!--引入 Mybatis--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> <!-- 引入 MySQL 驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.46</version> </dependency> <!-- JSON 工具包--> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.31</version> </dependency> <!-- 单元测试 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> </dependencies>
4、配置 Mybatis
在 resources 目录新建 mybatis-config.xml
└── resources ├── mapper └── mybatis-config.xml
配置以下内容
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <!--设置properties 属性文件--> <properties resource="dbconfig.properties"></properties> <!--默认使用的配置环境：development--> <environments default="development"> <!--开发环境配置--> <environment id="development"> <!--事务管理器的配置--> <transactionManager type="JDBC"/> <!--配置数据源--> <dataSource type="POOLED"> <!--动态替换属性值--> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <!-- 这里引入 Mapper 文件--> </mappers></configuration>外部属性配置，resources 目录下新增 dbconfig.properties 文件
└── resources ├── dbconfig.properties ├── mapper └── mybatis-config.xml
配置内容：
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://127.0.0.1:3306/mybatis-demo?useUnicode=true&characterEncoding=utf8username=rootpassword=root
数据库连接的相关属性信息
5、获取 SqlSession
核心代码：
// 配置文件位置String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = factory.openSession();
写个工具类 MybatisUtils：
├── java └── me └── imcoding ├── App.java ├── MybatisUtils.java
public class MybatisUtils { private static SqlSessionFactory factory; /** * 根据 mybatis-config.xml 配置初始化 factory */ private static void initialFactory() { String resource = "mybatis-config.xml"; try { InputStream in = Resources.getResourceAsStream(resource); factory = new SqlSessionFactoryBuilder().build(in); } catch (IOException e) { e.printStackTrace(); } } public static SqlSession getSession() { if (factory == null) { initialFactory(); } // factory.openSession(true) - 开启事务自动提交 SqlSession sqlSession = factory.openSession(); return sqlSession; }}基本增删改查（CURD）
1、构建 Model 类
目录：
├── model ├── Menu.java ├── Role.java └── User.java
User
public class User { private Integer id; private String username; private String password; private String nickname; /* setter 和 getter 省略 */}Role
public class Role { private Integer id; private String name; // setter 和 getter 方法省略...}Menu
public class Menu { private Integer id; private String name; private int type; private String url; // setter and getter ... }UserInfo
public class UserInfo { private Integer id; private String name; private String sex; private String address; private String phone; private String email; //setter and getter....}2. 构建 Mapper 接口
目录：
├── mapper └── UserMapper.java
UserMapper
public interface UserMapper { // 新增 User int insertUser(User user); // 根据主键删除 int deleteUser(int id); // 更新 User int updateUser(User user); // 根据主键 id 查询 User selectByKey(int id); // 根据用户名和密码查询 User selectByUsernameAndPassword(String username, String password);}3、定义 Mapper xml
新建 UserMapper.xml
└── resources ├── dbconfig.properties ├── mapper │ └── UserMapper.xml └── mybatis-config.xml
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--映射 Mapper 接口--><mapper namespace="me.imcoding.mapper.UserMapper"> <!-- 对应 UserMapper 接口中的 insertUser() id 要对应接口中的方法名 parameterType: 指定参数类型，可以省略，自动适配 t_user 表设置主键 id 自动新增 --> <insert id="insertUser" parameterType="me.imcoding.model.User"> insert into t_user(username, password, nickname) values (#{username}, #{password}, #{nickname}) </insert> <!--对应 UserMapper 接口中的 deleteUser()--> <delete id="deleteUser" > delete from t_user where id = #{id} </delete> <!-- 对应 UserMapper 接口中的 updateUser() <set> 可以自动处理动态SQL语句，避免拼接语句时造成的语法错误 --> <update id="updateUser" parameterType="me.imcoding.model.User"> update t_user <set> <if test="username != null">username = #{username}</if> <if test="password != null">password = #{password}</if> <if test="nickname != null">nickname = #{nickname}</if> </set> where id = #{id} </update> <!--对应 UserMapper 接口中的 selectUser()--> <select id="selectUser" resultType="me.imcoding.model.User"> select nickname, username, password from t_user where id = #{id} </select> <!-- 对应 UserMapper 接口中的 selectByUsernameAndPassword() <where> 可以自动处理动态SQL语句，避免拼接语句时造成的语法错误 --> <select id="selectByUsernameAndPassword" resultType="me.imcoding.model.User"> select nickname, username, password from t_user <where> <if test="username != null"> username = #{username} </if> <if test="password != null"> and password = #{password} </if> </where> </select></mapper>一定不要忘记在 mybatis-config.xml 中引入 UserMapper.xml
mybatis-config.xml
<mappers> <!-- 这里引入 Mapper xml 文件--> <mapper resource="mapper/UserMapper.xml"></mapper></mappers>
4、构建 UserDao
目录：
└── imcoding ├── App.java ├── MybatisUtils.java ├── dao └── UserDao.java
UserDao
/** * Author：密叔. */public class UserDao { // 新增 public int insertUser(User user) { SqlSession sqlSession = MybatisUtils.getSession(); int bo; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 bo = mapper.insertUser(user); sqlSession.commit();//提交事务 return bo; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 删除 public int deleteUser(int id) { SqlSession sqlSession = MybatisUtils.getSession(); int bo; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 bo = mapper.deleteUser(id); sqlSession.commit();//提交事务 return bo; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 更新 public int updateUser(User user) { SqlSession sqlSession = MybatisUtils.getSession(); int bo; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 bo = mapper.updateUser(user); sqlSession.commit();//提交事务 return bo; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 查询 public User selectUser(int id) { SqlSession sqlSession = MybatisUtils.getSession(); User user; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 user = mapper.selectUser(id); sqlSession.commit();//提交事务 return user; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 根据 username 和 password 查询 public User selectByUsernameAndPassword(String username, String password) { SqlSession sqlSession = MybatisUtils.getSession(); User user; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 user = mapper.selectByUsernameAndPassword(username, password); sqlSession.commit();//提交事务 return user; } finally { //一定要关闭 sqlSession sqlSession.close(); } }}5、测试
目录：
└── test └── java └── me └── imcoding └── AppTest.java
AppTest
/** * Author：密叔. */public class UserDao { // 新增 public int insertUser(User user) { SqlSession sqlSession = MybatisUtils.getSession(); int bo; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 bo = mapper.insertUser(user); sqlSession.commit();//提交事务 return bo; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 删除 public int deleteUser(int id) { SqlSession sqlSession = MybatisUtils.getSession(); int bo; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 bo = mapper.deleteUser(id); sqlSession.commit();//提交事务 return bo; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 更新 public int updateUser(User user) { SqlSession sqlSession = MybatisUtils.getSession(); int bo; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 bo = mapper.updateUser(user); sqlSession.commit();//提交事务 return bo; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 查询 public User selectUser(int id) { SqlSession sqlSession = MybatisUtils.getSession(); User user; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 user = mapper.selectUser(id); sqlSession.commit();//提交事务 return user; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // 根据 username 和 password 查询 public User selectByUsernameAndPassword(String username, String password) { SqlSession sqlSession = MybatisUtils.getSession(); User user; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 user = mapper.selectByUsernameAndPassword(username, password); sqlSession.commit();//提交事务 return user; } finally { //一定要关闭 sqlSession sqlSession.close(); } }}复杂查询
查询某个 user 的详情与菜单权限
SQL：
SELECT u.id, u.username, u.password, u.nickname, ui.name, ui.sex, ui.email, ui.address, ui.phone, r.id as role_id, r.name as role_name, m.id as menu_id, m.name as menu_nameFROM t_user u LEFT outer JOIN t_user_role ur ON u.id = ur.user_id LEFT outer JOIN t_role r ON r.id = ur.role_id left OUTER JOIN t_role_menu rm ON rm.role_id = r.id left outer join t_menu m on m.id = rm.menu_id left outer join t_user_info ui on u.id = ui.user_idWHERE u.username = 'fangf@163.com'
1、新增接口方法
UserMapper.java
public interface UserMapper { //.... // 查询 user 详细信息，以map方式返回结果 User selectUserInfo(int id); //....}2、UserMapper.xml 新增查询
UserMapper.xml
<mapper> .....<select id="selectUserInfo" resultMap="UserResultMap"> SELECT u.id, u.username, u.password, u.nickname, ui.id as ui_id, ui.name as ui_name, ui.sex as ui_sex, ui.email as ui_email, ui.address as ui_address, ui.phone as ui_phone, r.id as role_id, r.name as role_name, m.id as menu_id, m.name as menu_name FROM t_user u LEFT outer JOIN t_user_role ur ON u.id = ur.user_id LEFT outer JOIN t_role r ON r.id = ur.role_id left OUTER JOIN t_role_menu rm ON rm.role_id = r.id left outer join t_menu m on m.id = rm.menu_id left outer join t_user_info ui on u.id = ui.user_id WHERE u.id = #{id} </select> <!--定义返回结果的映射关系--> <resultMap id="UserResultMap" type="me.imcoding.model.User"> <!--对应 User id 参数构造--> <constructor> <idArg column="id" javaType="int" ></idArg> </constructor> <!--User 属性--> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="nickname" property="nickname"></result> <!--对应User 中 userInfo 对象属性--> <association property="userInfo" javaType="me.imcoding.model.UserInfo" > <!--id 很重要不要省略--> <id column="ui_id" property="id"></id> <result column="ui_name" property="name"></result> <result column="ui_sex" property="sex"></result> <result column="ui_email" property="email"></result> <result column="ui_address" property="address"></result> <result column="ui_phone" property="phone"></result> </association> <!--对应 User 的 roles 集合属性--> <collection property="roles" ofType="me.imcoding.model.Role"> <id column="role_id" property="id"></id> <result column="role_name" property="name"></result> </collection> <!--对应 User 的 menus 集合属性--> <collection property="menus" ofType="me.imcoding.model.Menu"> <id column="menu_id" property="id"></id> <result column="menu_name" property="name"></result> </collection> </resultMap> ....</mapper>3、User 类新增属性和构造
User
public class User { // .... // 无参构造 public User() {} // id 参数构造,@Param("id") 与mapper xml 中的 name属性一致 public User(Integer id) { this.id = id; } private UserInfo userInfo; private List<Role> roles; private List<Menu> menus; // ....}4、UserDao 新增查询
UserDao
public class UserDao { //.... // 查询用户的所有详细信息 public User selectUserInfo(int id) { SqlSession sqlSession = MybatisUtils.getSession(); User user; try { // 从 sqlSession 中获取 Mapper 实例 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 调用 mapper 的方法 user = mapper.selectUserInfo(id); return user; } finally { //一定要关闭 sqlSession sqlSession.close(); } } // ....}5、测试
AppTest
public class AppTest { //...... @Test public void selectUserInfo() { UserDao dao = new UserDao(); User user = dao.selectUserInfo(1); System.out.println(JSON.toJSONString(user,true)); } //......}结果：
{ "id":1, "menus":[ { "id":1, "name":"站点信息", "type":0 }, { "id":2, "name":"车辆查询", "type":0 }, { "id":3, "name":"人员管理", "type":0 }, { "id":4, "name":"用户管理", "type":0 }, { "id":5, "name":"角色管理", "type":0 }, { "id":6, "name":"菜单管理", "type":0 }, { "id":7, "name":"站点维护", "type":0 } ], "nickname":"方方", "password":"111111", "roles":[ { "id":1, "name":"管理员" } ], "userInfo":{ "address":"上海闵行区58", "email":"fangf@163.com", "id":1, "name":"方达", "phone":"18276546543", "sex":"男" }, "username":"fangf@163.com"}