【狂神说Java】Mybatis学习笔记
1、简介
1.1 什么是Mybatis
- MyBatis 是一款优秀的持久层框架;
- 它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
1.2 持久化
-
数据持久化
- 持久化就是将程序的数据在持久状态和瞬时状态转化的过程
- 内存:断电即失
- 数据库(Jdbc),io文件持久化
-
为什么要持久化?
- 有一些对象,不能让他丢掉
- 内存太贵
1.3 持久层
Dao层、Service层、Controller层…
- 完成持久化工作的代码块
- 层界限十分明显
1.4 为什么需要MyBatis
- 帮助程序员将数据存入到数据库中
- 方便
- 传统的JDBC代码太复杂了,简化,框架,自动化
- 不用MyBatis也可以,技术没有高低之分
- 优点
- 简单易学
- 灵活
- sql和代码的分离,提高了可维护性
- 提供映射标签,支持对象与数据库的ORM字段关系映射
- 提供对象关系映射标签,支持对象关系组建维护
- 提供xml标签,支持编写动态sql
2、第一个Mybatis程序
思路:搭建环境 --> 导入MyBatis --> 编写代码 --> 测试
2.1 搭建环境
-
创建数据库
CREATE DATABASE mybatis; USE mybatis; CREATE TABLE IF NOT EXISTS `user`( id INT(20) PRIMARY KEY NOT NULL, NAME VARCHAR(30) DEFAULT NULL, pwd VARCHAR(30) DEFAULT NULL )ENGINE=INNODB DEFAULT CHARSET=utf8; DESC USER; INSERT INTO `user`(id, NAME, pwd) VALUES (1, '张三', '123456'), (2, '李四', '123456'), (3, '王五', '123456'); # P19 CREATE TABLE `teacher` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); CREATE TABLE `student` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, `tid` INT(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fktid` (`tid`), CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1'); # P22 CREATE TABLE `blog`( `id` VARCHAR(50) NOT NULL COMMENT '博客id', `title` VARCHAR(100) NOT NULL COMMENT '博客标题', `author` VARCHAR(30) NOT NULL COMMENT '博客作者', `create_time` DATETIME NOT NULL COMMENT '创建时间', `views` INT(30) NOT NULL COMMENT '浏览量' )ENGINE=INNODB DEFAULT CHARSET=utf8;
-
创建一个普通的maven项目
-
删除src目录 (就可以把此工程当做父工程了,然后创建子工程)
-
导入maven依赖(这里一次性导入了多个包,导入到父工程即可)
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <!-- 父工程 --> <groupId>com.zhangyc</groupId> <artifactId>MyCode</artifactId> <packaging>pom</packaging> <version>1.0-SNAPSHOT</version> <modules> <module>mybatis-01</module> <module>mybatis-02</module> <module>mybatis-03</module> <module>mybatis-04</module> <module>mybatis-05</module> <module>mybatis-06</module> </modules> <!-- 导入依赖 --> <dependencies> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.49</version> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <!-- junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.16</version> </dependency> </dependencies> <!-- 在build中配置resources,来防止我们资源导出失败的问题 --> <build> <resources> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <!-- true会报错,只能false,为什么??? --> <filtering>false</filtering> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>false</filtering> </resource> </resources> </build> <!-- 解决idea每次编译设置工程的默认jdk版本1.5问题 --> <properties> <maven.compiler.source>14.0.1</maven.compiler.source> <maven.compiler.target>14.0.1</maven.compiler.target> </properties> </project>
-
创建mybatis-01项目
2.2 创建Mybatis的配置文件
-
在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"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&userUnicode=true&characterEncoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration>
2.3 编写Mybatis工具类
-
MybatisUtils工具类
package com.zhangyc.utils; 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 java.io.IOException; import java.io.InputStream; /** * SqlSessionFactory --> SqlSession */ public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory; static { try { // 第一步:获取SqlSessionFactory对象 String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } /** * 第二步: * 既然有了 SqlSessionFactory,顾名思义,我们可以从中获得 SqlSession 的实例。SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。 * 你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。 */ public static SqlSession getSqlSession() { return sqlSessionFactory.openSession(); } }
2.4 编写代码
2.4.1 实体类POJO
-
User类
package com.zhangyc.pojo; // 实体类 public class User { private int id; private String name; private String pwd; public User() { } public User(int id, String name, String pwd) { this.id = id; this.name = name; this.pwd = pwd; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", pwd='" + pwd + '\'' + '}'; } }
2.4.2 Mapper (Dao) ★★★
-
UserMapper
package com.zhangyc.dao; import com.zhangyc.pojo.User; import java.util.List; import java.util.Map; public interface UserMapper { // 查询全部用户 List<User> getUserList(); // 根据id查询用户 User getUserById(int id); // 插入语句 int addUser(User user); // int addUserMap(Map<String, Object> map); // 修改用户 int updateUser(User user); // 删除用户 int deleteUser(int id); }
-
注意,以前是写UserDaoImpl实现类,现在使用UserMapper.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"> <!-- namespace绑定一个对应的DAO/Mapper接口 --> <mapper namespace="com.zhangyc.dao.UserMapper"> <!-- select查询语句 id: 就是com.zhangyc.dao.UserMapper类中的方法 resultType: 返回值类型,一般查询就是返回User(或customers等等)的类--> <select id="getUserList" resultType="com.zhangyc.pojo.User"> select * from mybatis.user </select> <select id="getUserById" parameterType="int" resultType="com.zhangyc.pojo.User"> select * from mybatis.user where id = #{id} </select> <insert id="addUser" parameterType="com.zhangyc.pojo.User"> insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd}) </insert> <insert id="addUserMap" parameterType="map"> insert into mybatis.user (id, name, pwd) values (#{MapId}, #{MapName}, #{MapPwd}); </insert> <update id="updateUser" parameterType="com.zhangyc.pojo.User"> update mybatis.user set name=#{name}, pwd=#{pwd} where id = #{id}; </update> <delete id="deleteUser" parameterType="int"> delete from mybatis.user where id=#{id}; </delete> </mapper>
2.4.3 绑定xml文件 ★★★
-
每一个Mapper.xml都需要在mybatis-config.xml中注册
<mappers> <mapper resource="com/zhangyc/dao/UserMapper.xml"/> </mappers>
2.4.4 测试
- 规范
-
测试代码
package com.zhangyc.dao; import com.zhangyc.pojo.User; import com.zhangyc.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.HashMap; import java.util.List; public class UserMapperTest { /** * 1. org.apache.ibatis.binding.BindingException: Type interface com.zhangyc.dao.UserDAO is not known to the MapperRegistry. * 解决方案: * <!-- 每一个Mapper.XML都需要在Mybatis核心配置文件中(mybatis-config.xml)注册! --> * <mappers> * <mapper resource="com.zhangyc.dao/UserMapper.xml"/> * </mappers> * * 2. java.lang.ExceptionInInitializerError * 解决方案: * 在pom.xml配置文件下插入:<!-- 在build中配置resources,来防止我们资源导出失败的问题 --> build 字段的内容 * */ @Test public void test() { SqlSession sqlSession = null; try { // 1. 获取sqlSession sqlSession = MybatisUtils.getSqlSession(); // 2. 方式1:执行sql --> getMapper (推荐) UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<User> userList = userMapper.getUserList(); userList.forEach(System.out::println); // 方式2: // List<Object> objects = sqlSession.selectList("com.zhangyc.dao.UserDAO.getUserList"); // objects.forEach(System.out::println); } catch (Exception e) { e.printStackTrace(); } finally { // 3. 关闭sqlSession assert sqlSession != null; sqlSession.close(); } } @Test public void testSelectById() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); User userById = userMapper.getUserById(3); System.out.println(userById); } catch (Exception e) { e.printStackTrace(); } finally { assert sqlSession != null; sqlSession.close(); } } // 增删改 需要提交事务 @Test public void testAddUser() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int addUser = mapper.addUser(new User(4, "陈六", "654321")); if (addUser > 0) System.out.println("添加成功!"); else System.out.println("添加失败!"); // 提交事务 ★★★ sqlSession.commit(); } catch (Exception e) { e.printStackTrace(); } finally { assert sqlSession != null; sqlSession.close(); } } @Test public void testUpdate() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int updateUser = mapper.updateUser(new User(4, "陈1111", "654321")); if (updateUser > 0) System.out.println("修改成功!"); else System.out.println("修改失败!"); // 提交事务 ★★★ sqlSession.commit(); } catch (Exception e) { e.printStackTrace(); } finally { assert sqlSession != null; sqlSession.close(); } } @Test public void testDelete() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); mapper.deleteUser(4); sqlSession.commit(); } catch (Exception e) { e.printStackTrace(); } finally { assert sqlSession != null; sqlSession.close(); } } @Test public void testAddUserByMap() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Object> map = new HashMap<>(); map.put("MapId", 5); map.put("MapName", "汪汪汪"); map.put("MapPwd", 543218); int addUserMap = mapper.addUserMap(map); if (addUserMap > 0) System.out.println("添加成功!"); else System.out.println("添加失败!"); // 提交事务 ★★★ sqlSession.commit(); } catch (Exception e) { e.printStackTrace(); } finally { assert sqlSession != null; sqlSession.close(); } } }
2.4.5 模糊查询
-
如果是模糊查询,为了避免sql注入问题,可以写成如下形式:
<select id="getUserList" resultType="com.zhangyc.pojo.User"> select * from mybatis.user where name like CONCAT("%", #{name}, "%"); </select>
3、配置
3.1 引入外部配置文件
-
db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&userUnicode=true&characterEncoding=UTF-8 username=root password=012400
-
mybatis-config.xml
<!-- 引入外部配置文件 --> <properties resource="db.properties"/> <environments default="test"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&userUnicode=true&characterEncoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="012400"/> </dataSource> </environment> <!-- 引入外部配置文件 --> <environment id="test"> <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>
3.2 别名
-
mybatis-config.xml
<!-- 可以给实体类起别名 --> <typeAliases> <typeAlias type="com.zhangyc.pojo.User" alias="User"/> </typeAliases>
-
UserMapper.xml
<select id="getUserList" resultType="com.zhangyc.pojo.User"> select * from mybatis.user </select> <!-- "com.zhangyc.pojo.User"就可以用别名"User"代替 --> <select id="getUserList" resultType="User"> <!-- 可以给实体类起别名 --> select * from mybatis.user </select>
-
也可以给包起别名
<typeAliases> <package type="com.zhangyc.pojo"/> </typeAliases> <!--调用的时候使用pojo就可以了-->
-
也可以使用注解,不需要配置mybatis-config.xml,直接在User实体类上面添加注解**@Alias**
import org.apache.ibatis.type.Alias; // 实体类 @Alias("User") public class User { private int id; private String name; private String pwd; // ... }
3.3 mapper映射器
-
如果使用类名来加载
<mappers> <mapper class="com.zhangyc.pojo.UserMapper"/> </mappers>
必须保证UserMapper接口类和UserMapper.xml在同一个文件夹下并且同名,如:
-
如果使用路径名来加载,无需保证保证上述要求(推荐)
<mappers> <mapper resource="com/zhangyc/dao/UserMapper.xml"/> </mappers>
-
使用包名注册,一般不用
4、生命周期和作用域
声明周期和作用域是至关重要的,因为错误的使用会导致非常严重的并发问题。
-
SqlSessionFactoryBuilder
- 一旦创建了SqlSessionFactory,就不再需要它了
- 局部变量
-
SqlSessionFactory
- 说白了就可以想象为:数据库连接池
- SqlSessionFactory一旦被创建就应该在应用的运行期间一直存在,没有任何理由丢弃它或重新创建一个实例。
- 因此SqlSessionFactory的最佳作用域是应用作用域(ApplocationContext)。
- 最简单的就是使用单例模式或静态单例模式。
-
SqlSession
- 连接到连接池的一个请求
- SqlSession 的实例不是线程安全的,因此是不能被共享的,所以它的最佳的作用域是请求或方法作用域。
- 用完之后需要赶紧关闭,否则资源被占用!
- 每一个Mapper就代表一个具体的业务!
5、ResultMap结果集映射
解决属性名和字段名不一致的问题
- 数据库中的字段
-
新建一个实体类,测试实体类属性与字段名不一致的情况
// 实体类 @Data @AllArgsConstructor @NoArgsConstructor public class User { private int id; private String name; private String password; }
-
可以看到ORM并没有一一对应,pwd对应的是password
-
解决办法1:起别名
<select id="getUserById" parameterType="int" resultType="com.zhangyc.pojo.User"> <!--select * from mybatis.user where id = #{id}--> select id, name, pwd password from mybatis.user where id=#{id}; </select>
-
解决办法2:结果集映射
注:ORM对应的属性可以不需要在结果集中声明
<!--第二个方法,使用resultMap--> <!--结果集映射--> <resultMap id="UserMap" type="User"> <!--column数据库的字段,property实体类中的属性--> <!--<result column="id" property="id"/>--> <!--<result column="name" property="name"/>--> <result column="pwd" property="password"/> </resultMap> <select id="getUserById" parameterType="int" resultMap="UserMap"> select * from mybatis.user where id = #{id} </select>
-
6、日志工厂
- mybatis-config.xml
<!-- 日志工厂 -->
<settings>
<!--<setting name="logImpl" value="STDOUT_LOGGING"/>-->
<setting name="logImpl" value="LOG4J"/> <!-- 需要配置log4j.properties -->
</settings>
- log4j.properties
log4j.rootLogger=DEBUG,console,file
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/zhangyc.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][c]%m%n
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
7、Limit分页
-
UserMapper接口
// 分页查询 List<User> getUserByLimit(Map<String, Integer> map);
-
UserMapper.xml
<select id="getUserByLimit" parameterType="map" resultMap="UserMap"> select * from mybatis.user limit #{startIndex}, #{pageSize}; </select>
-
test
@Test public void testGetUserByLimit() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Integer> map = new HashMap<>(); map.put("startIndex", 0); map.put("pageSize", 2); List<User> userList = userMapper.getUserByLimit(map); userList.forEach(System.out::println); } catch (Exception e) { e.printStackTrace(); } finally { assert sqlSession != null; sqlSession.close(); } }
8、注解开发
适用于简单的sql语句,但对于复杂的sql语句,注解就变得力不从心了~
-
编写UserMapper接口
// 查询全部用户 @Select("select * from user") List<User> getUserByAt(); // 若方法存在多个参数,需要加上@Param // @Param("MyId") 中的名字才是sql语句中得到的参数 // UserMapper.xml中的参数名也需要使用@Param指定的名字 @Select("Select * from user where id = #{MyId} and name = #{MyName}") User getUserByIdByAt(@Param("MyId") int id, @Param("MyName") String name);
-
绑定接口
<mappers> <!-- 绑定接口(使用注解开发) --> <mapper class="com.zhangyc.dao.UserMapper"/> </mappers>
9、Mybatis执行流程
10、复杂查询
10.1 复杂环境搭建
-
导入lombok
-
新建实体类Teacher,Student
-
Teacher
package com.zhangyc.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; }
-
Student
package com.zhangyc.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor @AllArgsConstructor public class Student { private int id; private String name; private Teacher teacher; }
-
-
编写Mapper接口
-
TeacherMapper
package com.zhangyc.dao; import com.zhangyc.pojo.Teacher; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; public interface TeacherMapper { @Select("select * from teacher where id=#{id}") Teacher getTeacher(@Param("id") int id); }
-
StudentMapper
package com.zhangyc.dao; import com.zhangyc.pojo.Student; import java.util.List; public interface StudentMapper { // 查询所有的学生信息,以及对应的老师信息 List<Student> getStudentAndTea(); // 查询所有的学生信息,以及对应的老师信息 List<Student> getStudentAndTea2(); }
-
-
创建Mapper.xml
-
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.zhangyc.dao.TeacherMapper"> </mapper>
-
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.zhangyc.dao.StudentMapper"> </mapper>
-
-
Mybaits核心配置文件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 resource="db.properties"/> <!-- 日志工厂 --> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <environments default="test"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&userUnicode=true&characterEncoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="012400"/> </dataSource> </environment> <!-- 引入外部配置文件 --> <environment id="test"> <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> <!-- 绑定xml --> <mapper resource="Mapper\TeacherMapper.xml"/> <mapper resource="Mapper\StudentMapper.xml"/> </mappers> </configuration>
注意,这里绑定的xml在resources文件下:
-
测试
package com.zhangyc.dao; import com.zhangyc.pojo.Student; import com.zhangyc.pojo.Teacher; import com.zhangyc.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class MapperTest { @Test public void testTeacher() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); } }
10.2 多对一处理
-
StudentMapper接口
package com.zhangyc.dao; import com.zhangyc.pojo.Student; import java.util.List; public interface StudentMapper { // 查询所有的学生信息,以及对应的老师信息 List<Student> getStudentAndTea(); // 查询所有的学生信息,以及对应的老师信息 List<Student> getStudentAndTea2(); }
-
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.zhangyc.dao.StudentMapper"> <!-- select s.id, s.name, t.name from mybatis.student s, mybatis.teacher t where s.tid = t.id; --> <!-- 方式一:子查询 思路: 1. 查询所有的学生信息 2. 根据查询出来的学生的tid,寻找对应的老师 --> <resultMap id="StudentTeacher" type="com.zhangyc.pojo.Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!-- 复杂的属性:对象:association 集合:collection --> <association property="teacher" column="tid" javaType="com.zhangyc.pojo.Teacher" select="getTeacher"/> </resultMap> <select id="getStudentAndTea" resultMap="StudentTeacher"> select * from student; </select> <select id="getTeacher" resultType="com.zhangyc.pojo.Teacher"> select * from teacher; </select> <!-- 方式二:联表查询 --> <resultMap id="StudentTeacher2" type="com.zhangyc.pojo.Student"> <result property="id" column="s_id"/> <result property="name" column="s_name"/> <!-- 复杂的属性:对象:association 集合:collection --> <association property="teacher" javaType="com.zhangyc.pojo.Teacher"> <result property="name" column="t_name"/> <result property="id" column="t_id"/> </association> </resultMap> <select id="getStudentAndTea2" resultMap="StudentTeacher2"> select s.id s_id, s.name s_name, t.name t_name, t.id t_id from student s, teacher t where s.tid = t.id; </select> </mapper>
-
test
package com.zhangyc.dao; import com.zhangyc.pojo.Student; import com.zhangyc.pojo.Teacher; import com.zhangyc.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class MapperTest { @Test public void testTeacher() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); } @Test public void testStudent() { SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentAndTea = mapper.getStudentAndTea2(); studentAndTea.forEach(System.out::println); sqlSession.close(); } }
-
结果
Student(id=1, name=小明, teacher=Teacher(id=1, name=秦老师)) Student(id=2, name=小红, teacher=Teacher(id=1, name=秦老师)) Student(id=3, name=小张, teacher=Teacher(id=1, name=秦老师)) Student(id=4, name=小李, teacher=Teacher(id=1, name=秦老师)) Student(id=5, name=小王, teacher=Teacher(id=1, name=秦老师))
10.3 一对多处理
-
pojo实体类
-
Student
package com.zhangyc.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor @AllArgsConstructor public class Student { private int id; private String name; private int tid; }
-
Teacher
package com.zhangyc.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.util.List; @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; // 一个老师拥有多个学生 private List<Student> students; }
-
-
TeacherMapper接口
package com.zhangyc.dao; import com.zhangyc.pojo.Teacher; import org.apache.ibatis.annotations.Param; import java.util.List; public interface TeacherMapper { // List<Teacher> getTeacher(); // 获取指定老师下的学生信息及老师信息 Teacher getTeacherStu(@Param("tid") int id); }
-
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.zhangyc.dao.TeacherMapper"> <select id="getTeacher" resultType="com.zhangyc.pojo.Teacher"> select * from teacher; </select> <resultMap id="TeacherStudent" type="com.zhangyc.pojo.Teacher"> <result property="id" column="t_id"/> <result property="name" column="t_name"/> <collection property="students" ofType="com.zhangyc.pojo.Student"> <result property="id" column="s_id"/> <result property="name" column="s_name"/> <result property="tid" column="tid"/> </collection> </resultMap> <select id="getTeacherStu" resultMap="TeacherStudent"> select t.id t_id, t.name t_name, s.name s_name, s.id s_id, tid from teacher t, student s where t.id = s.tid and t.id = #{tid}; </select> </mapper>
-
test
package com.zhangyc.dao; import com.zhangyc.pojo.Teacher; import com.zhangyc.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class MapperTest { @Test public void testTeacher() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); List<Teacher> teacher = mapper.getTeacher(); teacher.forEach(System.out::println); sqlSession.close(); } @Test public void testTeacherStu() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacherStu = mapper.getTeacherStu(1); System.out.println(teacherStu); sqlSession.close(); } }
-
结果
Teacher(id=1, name=秦老师, students=[ Student(id=1, name=小明, tid=1), Student(id=2, name=小红, tid=1), Student(id=3, name=小张, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])
10.4 小结
- 关联–association【多对一】
- 集合–collection【一对多】
- javaType & ofType
- javaType用来指定实体类中的类型
- ofType用来指定映射到List或者集合中的pojo类型,泛型中的约束类型
- 注意点
- 保证SQL的可读性,尽量保证通俗易懂
- 注意一对多和多对一,属性名和字段的问题(ORM)
- 如果问题不好排查错误,可以使用日志,建议使用Log4j
- 高频面试点
- Mysql引擎
- InnoDB底层原理
- 索引
- 索引优化
11、动态SQL
什么是动态SQL:动态SQL就是根据不同的条件生成不同的SQL语句
所谓的动态SQL,本质上还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
11.1 环境搭建
- 数据库
-
Blog实体类
package com.zhangyc.pojo; import lombok.Data; import java.util.Date; @Data public class Blog { private String id; private String title; private String author; private Date createTime; // 驼峰命名 --> create_time private int views; }
-
BlogMapper接口
package com.zhangyc.dao; import com.zhangyc.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { // 插入数据 int addBlog(Blog blog); }
-
BlogMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.zhangyc.dao.BlogMapper"> <insert id="addBlog" parameterType="com.zhangyc.pojo.Blog"> insert into mybatis.blog(id, title, author, create_time, views) values(#{id}, #{title}, #{author}, #{createTime}, #{views}) </insert> </mapper>
-
mybatis-config.xml
<settings> <!-- createTime 驼峰命名 create_time --> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <mappers> <!-- 绑定xml --> <mapper resource="Mapper\BlogMapper.xml"/> </mappers>
-
补充:IDUtils
package com.zhangyc.utils; import org.junit.Test; import java.util.UUID; public class IDUtils { public static String getId() { return UUID.randomUUID().toString().replaceAll("-", ""); } @Test public void test() { System.out.println(IDUtils.getId()); } }
-
test
package com.zhangyc.dao; import com.zhangyc.pojo.Blog; import com.zhangyc.utils.IDUtils; import com.zhangyc.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.Date; import java.util.HashMap; import java.util.List; public class MapperTest { @Test public void testAddBlog() { SqlSession sqlSession = MybatisUtils.getSqlSessionByAutoCommit(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setId(IDUtils.getId()); blog.setTitle("Mybatis"); blog.setAuthor("zhangyc"); blog.setCreateTime(new Date()); blog.setViews(9999); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("Java"); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("Spring"); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("微服务"); mapper.addBlog(blog); sqlSession.close(); } }
-
结果
11.2 IF语句
-
需求
如果传入title就查询指定title的blog,如果传入author就查询指定author的blog,如果都不传,就查询全部blog。
-
BlogMapper
// 查询博客 List<Blog> queryBlogIF(Map map);
-
BlogMapper.xml
<select id="queryBlogIF" parameterType="map" resultType="com.zhangyc.pojo.Blog"> <!--title = #{title} and author = #{author}--> select * from mybatis.blog where 1=1 <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author = #{author} </if> </select>
-
test
@Test public void testQueryBlogIF() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<>(); map.put("title", "Java"); map.put("author", "zhangyc"); List<Blog> blogs = mapper.queryBlogIF(map); blogs.forEach(System.out::println); sqlSession.close(); }
-
结果
Blog(id=393d69d70dcd422fbe6f3521951fbe69, title=Java, author=zhangyc, createTime=Mon Apr 12 16:13:50 CST 2021, views=1000)
11.3 Choose语句
-
BlogMapper
List<Blog> queryBlogChoose(Map map);
-
BlogMapper.xml
注意:where标签
<select id="queryBlogChoose" parameterType="map" resultType="com.zhangyc.pojo.Blog"> select * from mybatis.blog <where> <choose> <when test="title != null"> title = #{title} </when> <when test="author != null"> author = #{author} </when> <otherwise> views = #{views} </otherwise> </choose> </where> </select>
-
test
@Test public void testQueryBlogIF() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<>(); map.put("title", "Java"); map.put("author", "zhangyc"); List<Blog> blogs = mapper.queryBlogChoose(map); blogs.forEach(System.out::println); sqlSession.close(); }
11.4 Set语句
-
用途
用来解决update时拼接set的“,”问题
set会动态前置SET关键字,并删除无关的逗号
-
BlogMapper
// 更新博客 int updateBlog(Map map);
-
BlogMapper.xml
<update id="updateBlog" parameterType="map"> update mybatis.blog <set> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author}, </if> </set> where id = #{id}; </update>
-
test
@Test public void testUpdateBlog() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<>(); map.put("title", "Java111"); map.put("author", "zhangyc111"); map.put("id", "07d5c72d90464e559c41b76bd7085355"); mapper.updateBlog(map); sqlSession.commit(); sqlSession.close(); }
-
结果
11.5 SQL片段
有时候,可能会把一些通用的SQL语句提取出来,实现复用。
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<select id="queryBlogIF" parameterType="map" resultType="com.zhangyc.pojo.Blog">
select * from mybatis.blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
注意事项:
- 最好基于单表来定义SQL片段
- SQL片段不要存在where标签
11.6 Foreach语句
-
预实现的语句
select * from user where 1=1 and (id=1 or id=2 or id=3)
-
(id=1 or id=2 or id=3) 使用foreach实现
<foreach item="id" index="index" collection="ids" open="(" separator="or" close=")"> #{id} </foreach>
-
例子
12、缓存
12.1 一级缓存
Mybatis默认开启,只在一次sqlSession中有效,也就是拿到连接到关闭连接这个区间。
-
缓存失效:
-
查询不同的东西时会刷新缓存
-
映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存
-
查询不同的mapper.xml
-
手动清除缓存
-
12.2 二级缓存
Mybatis默认关闭,需要手动开启,一个命名空间对应一个二级缓存。
-
开启二级缓存:
-
在mybatis-config.xml开启全局缓存,默认开启,也可以显示声明
<setting name="cacheEnabled" value="true"/>
-
在Mapper.xml文件中加入====即可,也可以配置一些属性
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
-
-
只要开启了二级缓存,在同一个Mapper下都有效
-
所有的数据都会先放在一级缓存中,只有当会话提交或关闭时,才会提交到二级缓存
-
第一次进来时,先查二级缓存,再查一级缓存,最后查数据库
12.3 缓存原理
12.4 自定义缓存–ehcache
-
导包
<!-- https://mvnrepository.com/artifact/org.mybatis.caches/mybatis-ehcache --> <dependency> <groupId>org.mybatis.caches</groupId> <artifactId>mybatis-ehcache</artifactId> <version>1.2.0</version> </dependency>
-
在Mapper.xml文件中加入自定义缓存
<!--使用ehcache缓存--> <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
-
在resources文件下新建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" updateCheck="false"> <!-- diskStore:为缓存路径,ehcache分为内存和磁盘两级,此属性定义磁盘的缓存位置。参数解释如下: user.home – 用户主目录 user.dir – 用户当前工作目录 java.io.tmpdir – 默认临时文件路径 --> <diskStore path="java.io.tmpdir/Tmp_EhCache"/> <!-- defaultCache:默认缓存策略,当ehcache找不到定义的缓存时,则使用这个缓存策略。只能定义一个。 --> <!-- name:缓存名称。 maxElementsInMemory:缓存最大数目 maxElementsOnDisk:硬盘最大缓存个数。 eternal:对象是否永久有效,一但设置了,timeout将不起作用。 overflowToDisk:是否保存到磁盘,当系统当机时 timeToIdleSeconds:设置对象在失效前的允许闲置时间(单位:秒)。仅当eternal=false对象不是永久有效时使用,可选属性,默认值是0,也就是可闲置时间无穷大。 timeToLiveSeconds:设置对象在失效前允许存活时间(单位:秒)。最大时间介于创建时间和失效时间之间。仅当eternal=false对象不是永久有效时使用,默认是0.,也就是对象存活时间无穷大。 diskPersistent:是否缓存虚拟机重启期数据 Whether the disk store persists between restarts of the Virtual Machine. The default value is false. diskSpoolBufferSizeMB:这个参数设置DiskStore(磁盘缓存)的缓存区大小。默认是30MB。每个Cache都应该有自己的一个缓冲区。 diskExpiryThreadIntervalSeconds:磁盘失效线程运行时间间隔,默认是120秒。 memoryStoreEvictionPolicy:当达到maxElementsInMemory限制时,Ehcache将会根据指定的策略去清理内存。默认策略是LRU(最近最少使用)。你可以设置为FIFO(先进先出)或是LFU(较少使用)。 clearOnFlush:内存数量最大时是否清除。 memoryStoreEvictionPolicy:可选策略有:LRU(最近最少使用,默认策略)、FIFO(先进先出)、LFU(最少访问次数)。 FIFO,first in first out,这个是大家最熟的,先进先出。 LFU, Less Frequently Used,就是上面例子中使用的策略,直白一点就是讲一直以来最少被使用的。如上面所讲,缓存的元素有一个hit属性,hit值最小的将会被清出缓存。 LRU,Least Recently Used,最近最少使用的,缓存的元素有一个时间戳,当缓存容量满了,而又需要腾出地方来缓存新的元素的时候,那么现有缓存元素中时间戳离当前时间最远的元素将被清出缓存。 --> <defaultCache eternal="false" maxElementsInMemory="10000" overflowToDisk="false" diskPersistent="false" timeToIdleSeconds="1800" timeToLiveSeconds="259200" memoryStoreEvictionPolicy="LRU"/> <cache name="cloud_user" eternal="false" maxElementsInMemory="5000" overflowToDisk="false" diskPersistent="false" timeToIdleSeconds="1800" timeToLiveSeconds="1800" memoryStoreEvictionPolicy="LRU"/> </ehcache>