Mybatis 是什么?
Mybatis 是一個(gè)持久層框架。Java 中通過 Mybatis ,程序可以很方便的對(duì)數(shù)據(jù)庫進(jìn)行操作。使開發(fā)者專注于 SQL語句,避免了重復(fù)的 JDBC 代碼。
- 使用 MyBatis 避免了幾乎所有的 JDBC 代碼和手動(dòng)設(shè)置參數(shù)以及獲取結(jié)果集
- 靈活且強(qiáng)大,支持定制化 SQL、存儲(chǔ)過程以及高級(jí)映射
- 支持 XML 和注解兩種形式編寫 SQL
- 數(shù)據(jù)庫中的記錄可以映射為普通 Java 對(duì)象
準(zhǔn)備
環(huán)境:
開發(fā)工具:IDEA
包管理:Maven
JDK:1.8
Mybatis :Mybatis 3.4.6
問題場(chǎng)景
使用 Mybatis 完成基于簡(jiǎn)單權(quán)限設(shè)計(jì)的數(shù)據(jù)庫表的相關(guān)操作、查詢
涉及到的表
t_user
t_user_info
t_role
t_menu
t_user_role //關(guān)系表
t_role_menu //關(guān)系表
要做哪些操作
- t_user 表的簡(jiǎn)單增刪改成
- 關(guān)聯(lián)復(fù)雜查詢,查詢一個(gè)用戶的所有權(quán)限信息
- 動(dòng)態(tài)查詢,包含某些菜單權(quán)限的用戶
1、新建數(shù)據(jù)庫表,初始化些數(shù)據(jù)
-- ----------------------------
-- 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;
手動(dòng)初始化些數(shù)據(jù)進(jìn)去,構(gòu)建一些關(guān)聯(lián)數(shù)據(jù),為下面測(cè)試所用
2、構(gòu)建Maven項(xiàng)目

組織 project 目錄
└── src
├── main
│ ├── java
│ │ └── me
│ │ └── imcoding
│ │ ├── dao
│ │ ├── mapper
│ │ ├── model
│ └── resources
│ └── mapper
└── test
└── java
└── me
└── imcoding
3、引入 Mybatis 相關(guān)依賴
在 maven 環(huán)境中,需要在 pom.xml 文件中增加依賴
<dependencies>
<!--引入 Mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- 引入 MySQL 驅(qū)動(dòng)-->
<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>
<!-- 單元測(cè)試 -->
<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
配置以下內(nèi)容
<?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>
<!--設(shè)置properties 屬性文件-->
<properties resource="dbconfig.properties"></properties>
<!--默認(rèn)使用的配置環(huán)境:development-->
<environments default="development">
<!--開發(fā)環(huán)境配置-->
<environment id="development">
<!--事務(wù)管理器的配置-->
<transactionManager type="JDBC"/>
<!--配置數(shù)據(jù)源-->
<dataSource type="POOLED">
<!--動(dòng)態(tài)替換屬性值-->
<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
配置內(nèi)容:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis-demo?useUnicode=true&characterEncoding=utf8
username=root
password=root
數(shù)據(jù)庫連接的相關(guān)屬性信息
5、獲取 SqlSession
核心代碼:
// 配置文件位置
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = factory.openSession();
寫個(gè)工具類 MybatisUtils:
├── java
└── me
└── imcoding
├── App.java
├── MybatisUtils.java
public class MybatisUtils {
private static SqlSessionFactory factory;
/**
* 根據(jù) 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) - 開啟事務(wù)自動(dòng)提交
SqlSession sqlSession = factory.openSession();
return sqlSession;
}
}
基本增刪改查(CURD)
1、構(gòu)建 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. 構(gòu)建 Mapper 接口
目錄:
├── mapper
└── UserMapper.java
UserMapper
public interface UserMapper {
// 新增 User
int insertUser(User user);
// 根據(jù)主鍵刪除
int deleteUser(int id);
// 更新 User
int updateUser(User user);
// 根據(jù)主鍵 id 查詢
User selectByKey(int id);
// 根據(jù)用戶名和密碼查詢
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">
<!--
對(duì)應(yīng) UserMapper 接口中的 insertUser()
id 要對(duì)應(yīng)接口中的方法名
parameterType: 指定參數(shù)類型,可以省略,自動(dòng)適配
t_user 表設(shè)置主鍵 id 自動(dòng)新增
-->
<insert id="insertUser" parameterType="me.imcoding.model.User">
insert into t_user(username, password, nickname)
values (#{username}, #{password}, #{nickname})
</insert>
<!--對(duì)應(yīng) UserMapper 接口中的 deleteUser()-->
<delete id="deleteUser" >
delete from t_user where id = #{id}
</delete>
<!--
對(duì)應(yīng) UserMapper 接口中的 updateUser()
<set> 可以自動(dòng)處理動(dòng)態(tài)SQL語句,避免拼接語句時(shí)造成的語法錯(cuò)誤
-->
<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>
<!--對(duì)應(yīng) UserMapper 接口中的 selectUser()-->
<select id="selectUser" resultType="me.imcoding.model.User">
select
nickname,
username,
password
from t_user
where id = #{id}
</select>
<!--
對(duì)應(yīng) UserMapper 接口中的 selectByUsernameAndPassword()
<where> 可以自動(dòng)處理動(dòng)態(tài)SQL語句,避免拼接語句時(shí)造成的語法錯(cuò)誤
-->
<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、構(gòu)建 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 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
bo = mapper.insertUser(user);
sqlSession.commit();//提交事務(wù)
return bo;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 刪除
public int deleteUser(int id) {
SqlSession sqlSession = MybatisUtils.getSession();
int bo;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
bo = mapper.deleteUser(id);
sqlSession.commit();//提交事務(wù)
return bo;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 更新
public int updateUser(User user) {
SqlSession sqlSession = MybatisUtils.getSession();
int bo;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
bo = mapper.updateUser(user);
sqlSession.commit();//提交事務(wù)
return bo;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 查詢
public User selectUser(int id) {
SqlSession sqlSession = MybatisUtils.getSession();
User user;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
user = mapper.selectUser(id);
sqlSession.commit();//提交事務(wù)
return user;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 根據(jù) username 和 password 查詢
public User selectByUsernameAndPassword(String username, String password) {
SqlSession sqlSession = MybatisUtils.getSession();
User user;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
user = mapper.selectByUsernameAndPassword(username, password);
sqlSession.commit();//提交事務(wù)
return user;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
}
5、測(cè)試
目錄:
└── 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 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
bo = mapper.insertUser(user);
sqlSession.commit();//提交事務(wù)
return bo;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 刪除
public int deleteUser(int id) {
SqlSession sqlSession = MybatisUtils.getSession();
int bo;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
bo = mapper.deleteUser(id);
sqlSession.commit();//提交事務(wù)
return bo;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 更新
public int updateUser(User user) {
SqlSession sqlSession = MybatisUtils.getSession();
int bo;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
bo = mapper.updateUser(user);
sqlSession.commit();//提交事務(wù)
return bo;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 查詢
public User selectUser(int id) {
SqlSession sqlSession = MybatisUtils.getSession();
User user;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
user = mapper.selectUser(id);
sqlSession.commit();//提交事務(wù)
return user;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// 根據(jù) username 和 password 查詢
public User selectByUsernameAndPassword(String username, String password) {
SqlSession sqlSession = MybatisUtils.getSession();
User user;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
user = mapper.selectByUsernameAndPassword(username, password);
sqlSession.commit();//提交事務(wù)
return user;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
}
復(fù)雜查詢
查詢某個(gè) user 的詳情與菜單權(quán)限
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_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.username = 'fangf@163.com'
1、新增接口方法
UserMapper.java
public interface UserMapper {
//....
// 查詢 user 詳細(xì)信息,以map方式返回結(jié)果
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>
<!--定義返回結(jié)果的映射關(guān)系-->
<resultMap id="UserResultMap" type="me.imcoding.model.User">
<!--對(duì)應(yīng) User id 參數(shù)構(gòu)造-->
<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>
<!--對(duì)應(yīng)User 中 userInfo 對(duì)象屬性-->
<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>
<!--對(duì)應(yīng) 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>
<!--對(duì)應(yīng) 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 類新增屬性和構(gòu)造
User
public class User {
// ....
// 無參構(gòu)造
public User() {}
// id 參數(shù)構(gòu)造,@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 {
//....
// 查詢用戶的所有詳細(xì)信息
public User selectUserInfo(int id) {
SqlSession sqlSession = MybatisUtils.getSession();
User user;
try {
// 從 sqlSession 中獲取 Mapper 實(shí)例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 調(diào)用 mapper 的方法
user = mapper.selectUserInfo(id);
return user;
} finally {
//一定要關(guān)閉 sqlSession
sqlSession.close();
}
}
// ....
}
5、測(cè)試
AppTest
public class AppTest {
//......
@Test
public void selectUserInfo() {
UserDao dao = new UserDao();
User user = dao.selectUserInfo(1);
System.out.println(JSON.toJSONString(user,true));
}
//......
}
結(jié)果:
{
"id":1,
"menus":[
{
"id":1,
"name":"站點(diǎn)信息",
"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":"站點(diǎn)維護(hù)",
"type":0
}
],
"nickname":"方方",
"password":"111111",
"roles":[
{
"id":1,
"name":"管理員"
}
],
"userInfo":{
"address":"上海閔行區(qū)58",
"email":"fangf@163.com",
"id":1,
"name":"方達(dá)",
"phone":"18276546543",
"sex":"男"
},
"username":"fangf@163.com"
}
源碼
Mybatis 知識(shí)邊界
- Mybatis 引入
- Mybatis 的必要配置
- Mybatis Mapper XML (映射文件)
- Mybatis 動(dòng)態(tài) SQL
- Mybatis SqlSession
- Mybatis 代碼生成
- Mybatis 緩存
- Mybatis 事務(wù)
學(xué)習(xí)資源:http://www.mybatis.org/mybatis-3/zh/index.html
Mybatis 核心知識(shí)
-
Mapper XML 映射文件的使用規(guī)則
學(xué)習(xí):http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html
-
動(dòng)態(tài) SQL 的處理規(guī)則
學(xué)習(xí):http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html