- Java工程师的进阶之旅
- MybaitsPlus教程
- 本文前置知识需要MySQL,JDBC,Maven
一、Mybatis简介
1.1、历史
MyBatis 本是apache的一个开源项目iBatis,2010年这个项目由apache software foundation迁移到了google code,并且改名为MyBatis,2013年11月迁移到Github。
1.2、特性
- 支持定制化SQL、存储过程及高级映射的持久层框架
- 避免了几乎所有的JDBC代码和手动设置参数以及获取结果集
- 使用简单的XML或注解用于配置和原始映射
将接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)映射为数据库中的记录。 - 半自动的ORM(Object Relation Mapping)框架
1.3、下载
官网地址
1.3.1、进入页面最下
1.3.2、下载zip文件
1.4、对比
- JDBC:耦合度较高,代码冗长
- Hibernate 和 JPA:反射操作太多,导致数据库性能下降,无法编写复杂SQL
- Mybatis:轻量,JAVA 和 SQL分开,但开发效率稍逊Hibernate,可以接受的范围
二、Mybatis搭建
2.1、开发环境
1、创建空工程mybatis-study
2、设置maven路径
3、创建maven工程:mybatis-demo01
4、导入依赖
<!-- 设置打包方式 -->
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.36</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- 注解生成getset toString -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
</dependencies>
5、安装MybatisX
插件(选装),可以自动根据mapper接口方法自动创建mapper.xml语句
2.2、配置
最终效果:
2.2.1、创建数据库和表
create database mybatis;
create table user(
id int(20),
name varchar(20),
age int(20)
);
2.2.2、创建包com.laptoy.pojo和com.laptoy.mapper
2.2.2.1、创建类User
package com.laptoy.pojo;
import lombok.Data;
import lombok.ToString;
@ToString
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private int age;
}
2.2.2.2、创建接口UserMapper
package com.laptoy.mapper;
public interface UserMapper {
//添加用户
int insertUser();
}
2.2.3、在resources
目录创建配置文件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>
<!-- 配置连接数据库的环境 -->
<environments default="development">
<environment id="development">
<!-- 传统JDBC,需要手动提交事务 -->
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 引入映射文件 -->
<mappers>
<mapper resource="mappers/UserMapper.xml"/>
</mappers>
</configuration>
2.3、映射文件
2.3.1、概念
用于编写SQL,访问及操作表中数据
表—>实体类—>mapper接口—>映射文件
ORM(Object Relationship Mapping)对象关系映射
- 对象:Java实体类对象
- 关系:关系型数据库
- 映射:两者的联系
Java概念 | 数据库概念 |
---|---|
类 | 表 |
属性 | 字段/列 |
对象 | 记录/行 |
2.3.2、命名规则
- 放在
/src/main/resources
目录 - 表对应的实体类类名+Mapper.xml,如User类就是UserMapper.xml
- mapper接口的全类名和映射文件的命名空间(namespace)保持一致
- mapper接口中的方法的方法名和映射文件中编写的SQL的标签的id属性保持一致
- resultType与返回值一致,例com.laptoy.User,也可以直接写user、User
2.3.3、创建映射文件
在resources/mappers
目录创建userMappe.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 namespace="com.laptoy.mapper.UserMapper">
<insert id="insertUser">
insert into user
values (1, 'jiali', 21);
</insert>
</mapper>
2.4、测试添加
package com.laptoy.test;
public class MyBatisTest {
//获取sqlSession对象
static SqlSession getSqlSession() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//默认JDBC需要手动提交事务,这里设置方法属性为true设置自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
return sqlSession;
}
@Test
public void insertTest() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int result = mapper.insertUser();
System.out.println("result:" + result);
}
}
2.5、引入Log4j日志记录
2.5.1、pom.xml添加
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
2.5.2、在resources/
下创建log4j.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'>
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n"/>
</layout>
</appender>
<logger name="java.sql">
<level value="debug"/>
</logger>
<logger name="org.apache.ibatis">
<level value="info"/>
</logger>
<root>
<level value="debug"/>
<appender-ref ref="STDOUT"/>
</root>
</log4j:configuration>
2.5.3、测试
2.6、测试修改和删除
2.6.1、UserMapper
public interface UserMapper {
int updateUser();
int deleteUser();
}
2.6.2、UserMapper.xml
<update id="updateUser">
update user set name = 'laptoy' where id = 1;
</update>
<delete id="deleteUser">
delete from user where id = 1;
</delete>
2.6.3、测试
@Test
public void updateTest() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int result = mapper.updateUser();
System.out.println("result:" + result);
}
@Test
public void deleteTest() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int result = mapper.deleteUser();
System.out.println("result:" + result);
}
2.7、测试查询
User getUserById();
List(User) getUserList();
resultType
:映射为User对象
resultMap
:自定义映射
<select id="getUserById" resultType="com.laptoy.pojo.User">
select * from user where id = 1;
</select>
<select id="getUserList" resultType="com.laptoy.pojo.User">
select * from user;
</select>
@Test
public void getUserTest() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById();
System.out.println("result:" + user);
}
@Test
public void getUserListTest() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserList();
userList.forEach(System.out::println);
}
三、配置文件
标签顺序
properties?,settings?,typeAliases?,typeHandlers?
objectFactory?,objectWrapperFactory?,reflectorFactory?,
plugins?,environments?,databaseIdProvider?,mappers?
<?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>
<!-- 配置连接数据库的环境 -->
<environments default="development">
<environment id="development">
<!-- 传统JDBC,需要手动提交事务 -->
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 引入映射文件 -->
<mappers>
<mapper resource="mappers/UserMapper.xml"/>
</mappers>
</configuration>
3.1、environments
3.1.1、environment
可以配置多个环境environment
,使用default属性指定开启哪个
3.1.1.1、transactionManager
- JDBC:原生事务管理,需要手动操作事务
- MANAGED:被管理,例如spring
3.1.1.2、dataSource
- POOLED:使用数据库连接池缓存数据库连接
- UNPOOLED:不使用数据库连接池
- JNDI:使用上下文中的数据源
3.2、properties
在resource/
目录下新建jdbc.properties
文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root
properties
标签引入jdbc.properties
<?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 resource="jdbc.properties" />
<!-- 配置连接数据库的环境 -->
<environments default="development">
<environment id="development">
<!-- 传统JDBC,需要手动提交事务 -->
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 引入映射文件 -->
<mappers>
<mapper resource="mappers/UserMapper.xml"/>
</mappers>
</configuration>
3.3、typeAliases
3.3.1、typeAlias
设置类型别名,设置后resultType
就可以直接写该alias
别名,不区分大小写,不设置alias
默认别名为类名
<typeAliases>
<typeAlias type="com.laptoy.pojo.User" alias="User"/>
</typeAliases>
3.3.2、package
以包为单位,将包下所有类型设置默认类型别名,不区分大小写
<typeAliases>
<package name="com.laptoy.pojo"/>
</typeAliases>
3.4、mappers
3.4.1、mapper
引入映射文件
<mappers>
<mapper resource="mappers/UserMapper.xml"/>
</mappers>
3.4.2、package
resource目录快速创建包需要以/为分隔符
com/laptoy/mapper
- mapper接口所在的包和映射文件所在的包一致
- mapper接口和映射文件名字一致
<mappers>
<package name="com.laptoy.mapper"/>
</mappers>
四、获取参数
4.1、简介
${}
:字符串拼接,若为字符串或日期类型,需要手动添加单引号,特殊sql需要用到
#{}
:占位符拼接,自动添加单引号,防止sql注入(常用)
回顾JDBC
public void ReviewJDBC() throws Exception {
String username = "";
Class.forName("");
Connection connection = DriverManager.getConnection("", "", "");
PreparedStatement ps1 = connection.prepareStatement("select * from user where username = '" + username + "'");
//占位符方式无需加单引号,且避免sql注入
PreparedStatement ps2 = connection.prepareStatement("select * from user where username = ?");
ps2.setString(1, username);
}
4.2、使用
4.2.1、单个参数
User getUserById(int id);
<select id="getUserByCondition" resultType="user">
select *
from user
where id = #{id}
</select>
4.2.2、多个参数
4.2.2.1、默认传参
mybatis会自动把参数放在map中并且使用默认两种方式指定key
1、key 为 arg0,arg1。。。
2、key 为 param1,param2。。。
User getUserByCondition(int id, String name);
<select id="getUserByCondition" resultType="user">
select *
from user
where id = #{arg0} and name = #{arg1};
<!--两者均可-->
where id = #{param1} and name = #{param2};
</select>
@Test
public void getTest() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<>();
User user = mapper.getUserByCondition(1,"jiali");
System.out.println("result:" + user);
}
4.2.2.2、使用map传参
设置默认key的名称
User getUserByCondition(Map<String, Object> map);
通过key获取值
<select id="getUserByCondition" resultType="user">
select *
from user
where id = #{id} and name = #{name};
</select>
@Test
public void getTest() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<>();
map.put("id", 1);
map.put("name", "jiali");
User user = mapper.getUserByCondition(map);
System.out.println("result:" + user);
}
4.2.3、实体类参数
int insertUser(User user);
通过实体类属性名获取属性值,实质上就是动态代理使用get set方法取值
<insert id="insertUser">
insert into user
values (#{id}, #{name}, #{age});
</insert>
@Test
public void insertTest() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int result = mapper.insertUser(new User(2, "laptoy", 20));
System.out.println("result:" + result);
}
4.2.4、最终方案@Param
mybatis会自动把数据放在map中,并且指定key为@Param的属性值
User getUserByCondition(@Param("id") int id, @Param("name") String name);
<select id="getUserByCondition" resultType="user">
select *
from user
where id = #{id}
and name = #{name};
</select>
@Test
public void getTest() throws IOException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserByCondition(1, "jiali");
System.out.println("result:" + user);
}
4.3、@Param源码解析
写在另一篇点击跳转到该文章
五、各种查询
5.1、返回值为单行单列数据
Integer getCount();
<select id="getCount" resultType="java.lang.Integer">
select count(*)
from USER;
</select>
@Test
public void getCount() throws IOException {
SqlSession sqlSession = getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
int count = mapper.getCount();
System.out.println(count);
}
5.2、返回值为Map
5.2.1、单个及多个
//单条数据
Map<String, Object> getUserToMap(@Param("id") Integer id);
//多条数据放List
List<Map<String, Object>> getAllToMap();
<select id="getUserToMap" resultType="map">
select * from USER where id = #{id};
</select>
<select id="getAllToMap" resultType="map">
select * from USER
</select>
@Test
public void getUserToMap() throws IOException {
SqlSession sqlSession = getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> map = mapper.getUserToMap(1);
System.out.println(map);
// {name=jiali, id=1, age=21}
}
@Test
public void getUserToMap() throws IOException {
SqlSession sqlSession = getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> map = mapper.getAll();
System.out.println(map);
// [{name=jiali, id=1, age=21},{name=laptoy,id=2,age=29}]
}
5.2.2、@MapKey
多个map返回除了用List封装还可以用@MapKey
以某个字段的值作为key ,将每条数据转换的map集合作为value,放在一个map集合中
@MapKey("id")
Map<String, Object> getAllToMap();
{1={name=jiali, id=1, age=21}, 2={name=laptoy, id=2, age=20}}
六、特殊SQL
6.1、模糊查询
User getUserByLike(@Param("name") String name);
以下三种都可以,推荐第三种
<select id="getUserByLike" resultType="user">
<!--select * from USER where name like '%${name}%';-->
<!--select * from USER where name like concat('%',#{name},'%')-->
select * from USER where name like "%"#{name}"%"
</select>
@Test
public void getUserByLike() throws IOException {
SqlSession sqlSession = getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User user = mapper.getUserByLike("jia");
System.out.println(user);
}
6.2、批量删除
sql语句为
delete from user where id in ('1,2')
int deleteMore(@Param("ids") String ids);
这里不能使用#{}
,因为会自动拼接引号
<delete id="deleteMore">
delete from user where id in (${ids});
</delete>
@Test
public void deleteMore() throws IOException {
SqlSession sqlSession = getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
int result = mapper.deleteMore("1,2");
System.out.println(result);
}
6.3、动态设置表名
List<User> getUserByTableName(@Param("tableName") String tableName);
表名不能用引号,所以必须用${}
拼接
<select id="getUserByTableName" resultType="User">
select * from ${tableName};
</select>
@Test
public void getUserByTableName() throws IOException {
SqlSession sqlSession = getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<User> users = mapper.getUserByTableName("user");
System.out.println(users);
}
6.4、获取自动递增的主键
void insertUser(User user);
开启useGeneratedKeys=true
将生成的主键放到keyProperty
对应的字段中
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into USER values (null, #{name}, #{age});
</insert>
@Test
public void insertUser() throws IOException {
SqlSession sqlSession = getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User user = new User(null, "aa", 21);
mapper.insertUser(user);
System.out.println(user);
}
七、自定义映射ResultMap
7.1、搭建环境
7.1.1、表和数据
表t_dept
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
`did` int(11) NOT NULL AUTO_INCREMENT,
`dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`did`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_dept` VALUES (1, 'A');
INSERT INTO `t_dept` VALUES (2, 'B');
INSERT INTO `t_dept` VALUES (3, 'C');
SET FOREIGN_KEY_CHECKS = 1;
表t_emp
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
`eid` int(11) NOT NULL AUTO_INCREMENT,
`emp_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`did` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`eid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_emp` VALUES (1, 'laptoy', 20, 1);
INSERT INTO `t_emp` VALUES (2, 'jiali', 21, 2);
INSERT INTO `t_emp` VALUES (3, 'a', 11, 3);
INSERT INTO `t_emp` VALUES (4, 'b', 12, 1);
INSERT INTO `t_emp` VALUES (5, 'c', 12, 2);
SET FOREIGN_KEY_CHECKS = 1;
7.1.2、实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Emp {
private Integer eid;
private String empName;
private Integer age;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Dept {
private Integer did;
private String deptName;
}
7.1.3、接口
public interface EmpMapper {
}
public interface DeptMapper {
}
7.1.4、映射文件
<?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 namespace="com.laptoy.mapper.EmpMapper">
</mapper>
<?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 namespace="com.laptoy.mapper.DeptMapper">
</mapper>
7.1.5、配置文件
<?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 resource="jdbc.properties"/>
<typeAliases>
<package name="com.laptoy.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!-- 传统JDBC,需要手动提交事务 -->
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.laptoy.mapper"/>
</mappers>
</configuration>
7.1.6、工具类sqlSessionUtil
public class SqlSessionUtil {
//获取sqlSession对象
public static SqlSession getSqlSession() {
InputStream is = null;
SqlSession sqlSession = null;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
sqlSession = sqlSessionFactory.openSession(true);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSession;
}
}
7.1.7、最终效果
7.2、处理字段和属性的映射关系
7.2.1、字段与实体类不一致
List<Emp> getAllEmp();
<select id="getAllEmp" resultType="emp">
select eid, emp_name, age
from t_emp;
</select>
@Test
public void getAllEmp() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> allEmp = mapper.getAllEmp();
allEmp.forEach(System.out::println);
}
查出来数据为空
7.2.2、解决
7.2.2.1、别名
<select id="getAllEmp" resultType="emp">
select eid, emp_name empName, age
from t_emp;
</select>
7.2.2.2、配置文件开启驼峰映射
按配置文件的顺序添加该标签,如果configuration标签爆红就是顺序错了
<settings>
<!--将_自动映射为驼峰,emp_name变成empName -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
7.2.2.3、ResultMap解决
当字段和属性一致也可以省略不写
id
为主键,result
为普通字段,type
为实体类类型,property
属性名,column
字段名
<resultMap id="empMap" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
</resultMap>
<select id="getAllEmp" resultMap="empMap">
select * from t_emp;
</select>
7.3、解决多对一
多个员工对应一个部门 多对一对应对象,所以在员工类加Dept
属性
7.3.1、级联属性赋值
Emp类添加Dept字段
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Emp {
private Integer eid;
private String empName;
private Integer age;
private Dept dept;
}
Emp getEmpAndDept(@Param("eid") Integer eid);
<resultMap id="empAndDeptMap" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="dept.did" column="did"/>
<result property="dept.deptName" column="dept_name"/>
</resultMap>
<select id="getEmpAndDept" resultMap="empAndDeptMap">
select *
from t_emp e
join t_dept d
on e.did = d.did
where e.eid = #{eid};
</select>
@Test
public void getEmpAndDept() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp empAndDept = mapper.getEmpAndDept(1);
System.out.println(empAndDept);
}
7.3.2、association
专门用来处理多对一映射
property
:需要处理的映射关系的属性名
javaType
:该属性的类型
<resultMap id="empAndDeptMap" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<association property="dept" javaType="Dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
</association>
</resultMap>
7.3.3、分步查询(主用)
第一步:查出emp表,同时嵌套第二步的查询dept表
Emp getEmpAndDeptByStepOne(@Param("eid") Integer eid);
<resultMap id="stepMap" type="emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<association property="dept"
select="com.laptoy.mapper.DeptMapper.getEmpAndDeptByStepTwo"
column="did"/>
</resultMap>
<select id="getEmpAndDeptByStepOne" resultMap="stepMap">
select * from t_emp where eid = #{eid};
</select>
Dept getEmpAndDeptByStepTwo(@Param("did") Integer did);
第二步:查出dept表
<resultMap id="deptMap" type="dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
</resultMap>
<select id="getEmpAndDeptByStepTwo" resultMap="deptMap">
select *
from t_dept
where did = #{did};
</select>
测试
@Test
public void getEmpAndDept() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp empAndDept = mapper.getEmpAndDeptByStepOne(1);
System.out.println(empAndDept);
}
7.3.3、延迟加载
可以实现延迟加载
lazyLoadingEnabled
:延迟加载全局开关,开启后所有对象都会进行延迟加载(默认false)
aggressiveLazyLoading
:当开启时,任何方法的调用都会加载该对象的所有属性(默认false true in <=3.4.1)
@Test
public void getEmpAndDept() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp empAndDept = mapper.getEmpAndDeptByStepOne(1);
System.out.println(empAndDept.getEmpName());
System.out.println("----------------------------");
System.out.println(empAndDept.getDept());
}
未开启:会先将sql语句执行后才打印数据开启后:分步执行sql语句
<setting name="lazyLoadingEnabled" value="true"/>
如果有部分查询不想使用全局懒加载,可以在assocation
标签设置fetch
属性
7.4、解决一对多
一个部门对应多个员工 一对多对应集合,所有在部门类加List<Emp>
属性
7.4.1、collection
专门用来处理一对多映射
ofTyoe:对应集合中存储的数据类型
Dept getDeptAndEmp(@Param("did") Integer did);
<resultMap id="deptAndEmpMap" type="dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
<collection property="emps" ofType="emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
</collection>
</resultMap>
<select id="getDeptAndEmp" resultMap="deptAndEmpMap">
select *
from t_dept d
left join t_emp e
on d.did = e.did
where d.did = #{did};
</select>
@Test
public void getDeptAndEmp() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.getDeptAndEmp(1);
System.out.println(dept);
}
7.4.2、分步查询
第一步
Dept getDeptAndEmpByStepOne(@Param("did") Integer did);
<resultMap id="deptAndEmpStepMap" type="dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
<collection property="emps" select="com.laptoy.mapper.EmpMapper.getDeptAndEmpByStepTwo" column="did"/>
</resultMap>
<select id="getDeptAndEmpByStepOne" resultMap="deptAndEmpStepMap">
select *
from t_dept
where did = #{did};
</select>
第二步
List<Emp> getDeptAndEmpByStepTwo(@Param("did") Integer did);
<resultMap id="empMap" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
</resultMap>
<select id="getDeptAndEmpByStepTwo" resultMap="empMap">
select *
from t_emp
where did = #{did};
</select>
测试
@Test
public void getDeptAndEmpByStep() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.getDeptAndEmpByStepOne(1);
System.out.println(dept);
}
也可以实现懒加载
八、动态SQL
根据特定条件动态拼装SQL语句的功能,解决SQL拼接语句字符串时的通点问题
8.1、if
通过test属性的表达式进行判断是否执行内容
8.2、where
自动添加和if一起使用,会智能去除语句前面的and、or关键字,当标签内没内容不会拼接where关键字
<select id="getUserByCondition" resultType="com.laptoy.pojo.User">
select * from user
<where>
<if test="id != null and id != ''">
AND id = #{id}
</if>
<if test="name != null and name != ''">
AND name = #{name}
</if>
</where>
</select>
8.3、trim
where标签无法去除尾部的and、or
可以用trim标签代替使用
prefix|suffix
:将trim标签中内容前面或后面添加指定内容
prefixOverrides|suffixOverrides
:将trim标签中内容前面或后面去掉指定内容
以下代码,如果id为空只有name也能拼接成功,但如果是where标签就会报错
如果条件全部不成立不会添加where关键字
<trim prefix="where" suffixOverrides="and">
<if test="id != null and id != ''">
id = #{id}
</if>
<if test="name != null and name != ''">
name = #{name} and
</if>
</trim>
8.4、choose、when、otherwise
这三个标签需要组合在一起使用,类似于 Java 中的 switch、case、default。只有一个条件生效,也就是只执行满足的条件 when,没有满足的条件就执行 otherwise,表示默认条件。
在测试类中,即使同时添加id和name的值,最终的sql也只会添加第一个属性值。
<select id="getUserByCondition" resultType="com.laptoy.pojo.User">
select * from users
<where>
<choose>
<when test="id != null and id!=''">
id=#{id}
</when>
<when test="name!=null and name!=''">
name=#{name}
</when>
<otherwise>
age=#{age}
</otherwise>
</choose>
</where>
</select>
8.4、set
剔除追加到条件末尾的任何不相关的逗号
使用 if+set 标签修改后,在进行表单更新的操作中,哪个字段中有值才去更新,如果某项为 null 则不进行更新,而是保持数据库原值。
<update id="updateSet" parameterType="com.laptoy.pojo.User">
update user
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="county != null and county != ''">
county = #{county},
</if>
</set>
where id = #{id}
</update>
8.5、foreach
实现循环,循环的对象主要是java容器和数组
属性 | 说明 |
---|---|
collection | 需要循环的集合或数组 |
item | 集合或数组的每一个数据 |
separator | 循环体中间的分隔符 |
open | 循环所有内容的开始符 |
close | 循环所有内容的结束符 |
8.5.1、数组实现
int delMoreByArray(@Param("ids") Integer[] ids);
<!-- delete from user where id in (?,?,?) -->
<delete id="delMoreByArray">
delete from user where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!-- delete from user where id = ? or id = ? -->
<delete id="delMoreByArray">
delete from user where
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>
@Test
public void test02() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.delMoreByArray(new Integer[]{1, 2, 3});
System.out.println(i);
}
8.5.2、集合实现
int insertMoreByList(@Param("users") List<User> users);
<insert id="insertMoreByList">
insert into USER
values
<foreach collection="users" item="user" separator=",">
(#{user.id},#{user.name},#{user.age})
</foreach>
</insert>
@Test
public void test03() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.insertMoreByList(Arrays.asList(new User(1, "1", 1), new User(2, "2", 2)));
System.out.println(i);
}
8.6、sql片段
定义
<sql id="mysql">id,name,age</sql>
使用
<include refid="mysql"/>
九、缓存
使用缓存, 我们可以避免频繁的与数据库进行交互, 尤其是在查询越多、缓存命中率越高的情况下, 使用缓存对性能的提高更明显。
mybatis分为一级缓存和二级缓存,默认情况 只开启一级缓存
两次查询之间执行增删改会使一级二级缓存同时失效
9.1、一级缓存
同一个
SqlSession
对象, 在参数和 SQL 完全一样的情况先, 只执行一次 SQL 语句(如果缓存没有过期)
使一级缓存失效的四种情况
- 不同SqlSession对应不同一级缓存
- 同一个SqlSession但是查询条件不同
- 同一个SqlSession两次查询间执行了增删改操作
- 同一个SqlSession两次查询间清除缓存
sqlSession.clearCache()
9.2、二级缓存
9.2.1、使用
范围:同一个SessionFactory创建的sqlsession
- 在核心配置文件设置全局配置属性
cacheEnable="true"
,默认为true - 在映射文件加入
<cache />
- 必须在SqlSession提交或关闭之后生效
- 查询的数据所转换的实体类型必须实现序列化接口
@Test
public void test04() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory build = sqlSessionFactoryBuilder.build(in);
SqlSession sqlSession1 = build.openSession(true);
UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
User user1 = mapper1.getUser();
// 手动关闭
sqlSession1.close();
SqlSession sqlSession2 = build.openSession(true);
UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
User user2 = mapper2.getUser();
}
9.2.2、相关配置
cache标签可以设置一些属性
-
eviction
回收策略, 默认为 LRU。
LRU: 最近最少使用, 移除最长时间不被使用的对象。
FIFO: 先进先出, 按对象进入缓存的顺序来移除对象。
SOFT: 软引用, 移除基于垃圾回收器状态和软引用规则的对象。
WEAK: 弱引用, 移除基于垃圾回收器状态和弱引用规则的对象。 -
flushInterval
对应刷新间隔, 单位毫秒, 默认值不设置, 即没有刷新间隔, 缓存仅仅在刷新语句时刷新 -
size
对应为引用的数量,即最多的缓存对象数据, 默认为 1024。 -
readOnly
为只读属性, 默认为 false
false: 可读写, 在创建对象时, 会通过反序列化得到缓存对象的拷贝。 因此在速度上会相对慢一点, 但重在安全。
true: 只读, 只读的缓存会给所有调用者返回缓存对象的相同实例。 因此性能很好, 但如果修改了对象, 有可能会导致程序出问题。
9.2.3、查询顺序
- 先查询二级缓存,因为二级缓存中可能有其他程序已经查出来的数据,可以之间使用
- 如果二级没有再查一级
- 一级也没有就查数据库
- SqlSession关闭后,一级缓存中的数据会写入二级缓存
9.3、整合第三方缓存EHCache
<dependency>
<groupId>org.mybatis.caches</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.2.1</version>
</dependency>
<!--slf4j日志门面的具体实现-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
创建ehcache.xml
<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd">
<diskStore path="java.io.tmpdir/ehcache"/>
<!-- 默认缓存 -->
<defaultCache
maxEntriesLocalHeap="10000"
eternal="false"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
maxEntriesLocalDisk="10000000"
diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU">
</defaultCache>
</ehcache>
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
十、逆向工程
推荐使用mybatis-plus的逆向工程,这里就不写了,关注我的主页过段时间发
十一、分页插件
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.4</version>
</dependency>
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
在查询数据之前添加,自动分页,返回值为分页数据
// 从第一个开始显示,每页显示5个
Page<Object> objects = PageHelper.startPage(1, 5);
导航分页,左两个右两个
PageHelper.startPage(1, 5);
// 以第五个为导航分页分割
PageInfo<Object> pageInfo = new PageInfo<>(list, 5);