Mybatis笔记

tech2023-02-23  120

Mybatis

文档: https://mybatis.org/mybatis-3/zh/getting-started.html

1. 第一个Mybatis程序

1.1.搭建环境

搭建数据库 CREATE DATABASE `mybatis`; CREATE TABLE `user` ( `id` INT(20) NOT NULL PRIMARY KEY, `name` VARCHAR(30) DEFAULT NULL, `password` VARCHAR(30) DEFAULT NULL )ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO `user` VALUES (1,'mashiro','123456'), (2,'nagisa','654321'), (3,'panira','223344'); 导入依赖 <dependencies> <!-- MySQL驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.21</version> </dependency> <!-- Mybatis --> <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <!-- junit --> <!-- https://mvnrepository.com/artifact/junit/junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> </dependencies>

1.2.创建模块

配置mybatis核心配置文件(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 核心配置文件 --> <configuration> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&amp;characterEncoding=utf8&amp;useUnicode=true&amp;useSSL=false"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <!-- 将mapper.xml 注册到mappers中 --> <mappers> <mapper resource="com/mashiro/dao/UserMapper.xml"/> </mappers> </configuration> 编写Mybatis工具类 package com.mashiro.com.mashiro.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; public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory = null; static{ try { // 获取sqlSessionFactory 工厂对象 String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } // 获得 SqlSession 的实例。SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。 // 你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。 public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); } }

1.3.编写代码

实体类 package com.mashiro.pojo; /** * 实体类 */ public class User { private int id; private String name; private String password; public User() { } public User(int id, String name, String password) { this.id = id; this.name = name; this.password = password; } 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 getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", password='" + password + '\'' + '}'; } } Dao接口 public interface UserDao { List<User> getUserList(); } 接口实现类 (由原先UserImpl转换为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"> <!-- namespace = 指定一个Dao接口--> <mapper namespace="com.mashiro.dao.UserMapper"> <!-- id: 接口中的方法名 resultType: 返回结果的类型--> <select id="getUserList" resultType="com.mashiro.pojo.User"> select * from user where id = #{id} </select> </mapper>

1.4.测试

package com.mashiro.dao; import com.mashiro.pojo.User; import com.mashiro.com.mashiro.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class UserDaoTest { @Test public void test01(){ SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserDao mapper = sqlSession.getMapper(UserDao.class); // Blog blog = mapper.selectBlog(101); List<User> userList = mapper.getUserList(); for (User user:userList) { System.out.println(user); } } finally { sqlSession.close(); } } }

1.5.其它注意事项

使用build配置,防止maven资源导出失败的问题 <build> <resources> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> </build> Mysql8.0 url写法

jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false"

2.CRBD

UserMapper接口 package com.mashiro.dao; import com.mashiro.pojo.User; public interface UserMapper { // 查询指定ID的信息 User getUserByID(int id); // 插入一个用户信息 int userInsert(User user); // 修改用户信息 int userUpdate(User user); // 删除用户 int userDelete(int id); } UserMapper.xml <?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- namespace = 指定一个Dao接口--> <mapper namespace="com.mashiro.dao.UserMapper"> <select id="getUserByID" parameterType="int" resultType="com.mashiro.pojo.User"> select * from mybatis.user where id = #{id} </select> <!-- 对象中的属性可以直接取出 --> <insert id="userInsert" parameterType="com.mashiro.pojo.User"> insert into user (id,`name`,password) values (#{id},#{name},#{password}) </insert> <update id="userUpdate" parameterType="com.mashiro.pojo.User"> update user set `name`=#{name},password=#{password} where id=#{id}; </update> <delete id="userDelete" parameterType="int"> delete from user where id = #{id} </delete> </mapper> Test类 package com.mashiro.dao; import com.mashiro.pojo.User; import com.mashiro.com.mashiro.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class UserMapperTest { @Test public void testGetUserByID(){ SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.getUserByID(1); System.out.println(user); } finally { sqlSession.close(); } } @Test public void testUserInsert(){ SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int i = mapper.userInsert(new User(1,"mashiro","123456")); if (i > 0){ System.out.println("success"); } // 提交事务 sqlSession.commit(); } finally { sqlSession.close(); } } @Test public void testUserUpdate(){ SqlSession sqlSession=null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); mapper.userUpdate(new User(1,"tomoya","345678")); sqlSession.commit(); }finally { sqlSession.close(); } } @Test public void testUserDelete(){ SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); mapper.userDelete(1); sqlSession.commit(); }finally { sqlSession.close(); } } } Tip: 增删改 要 sqlSession.commit(); (事务提交)。

万能Map

// 传参Map int userInsert2(HashMap<String,Object> map); <insert id="userInsert2" parameterType="map"> insert into user (id,`name`,password) values (#{id},#{name},#{password}) </insert> @Test public void testUserMapInsert(){ SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); Map<String, Object> map = new HashMap<String, Object>(); map.put("id",5); map.put("name","kotomi"); map.put("password","123321"); mapper.userInsert2((HashMap<String, Object>) map); // 提交事务 sqlSession.commit(); } finally { sqlSession.close(); } }

模糊查询

// 查询所有信息 List<User> getUserLike(String values); <select id="getUserLike" resultType="com.mashiro.pojo.User"> select * from user where name like #{value} </select> @Test public void testGetUserLike(){ SqlSession sqlSession = null; try { sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userLike = mapper.getUserLike("%m%"); for (User user:userLike) { System.out.println(user); } }finally { sqlSession.close(); } }

3.配置解析

3.1.属性

编写一个配置文件(dp.properties) driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false username=root password=123456 在mybatis-config.xml中引入外部配置文件 <properties resource="dp.properties"/> Tips: <properties resource=“dp.properties”/> 要写在最上面

3.2.别名(typeAliases)

类型别名可为 Java 类型设置一个缩写名字。它仅用于 XML 配置,意在降低冗余的全限定类名书写。方式1: <typeAliases> <typeAlias type="com.mashiro.pojo.User" alias="User"/> </typeAliases>

==================================================================

<select id="getUserList" resultType="User"> select * from mybatis.user </select> 方式2: 在没有注解的情况下,会使用Bean的首字母小写的非限定类名来作为它的别名 <typeAliases> <package name="com.mashiro.pojo"/> </typeAliases>

==================================================================

<select id="getUserList" resultType="user"> select * from mybatis.user </select> 实体类较少,使用方式1实体类较多,使用方式2方式1可以diy别名,方式2diy别名则需要使用注解 @Alias(“xxx”)

3.3.设置

[https://mybatis.org/mybatis-3/zh/configuration.html#settings]

3.4.其它配置

typeHandlers(类型处理器)objectFactory(对象工厂)plugins(插件) MyBatis Generator CoreMyBatis Plus通用mapper

3.5.映射器(mappers)

方式1: <mappers> <mapper resource="com/mashiro/dao/UserMapper.xml"/> </mappers> 方式2:使用class文件绑定注册 <mappers> <mapper class="com.mashiro.dao.UserMapper"/> </mappers>

Tips:

1.接口和配置文件必须同名

2.接口必须和配置文件在同一包下

方式3:扫描包进行绑定 <mappers> <package name="com.mashiro.dao"/> </mappers>

Tips:

1.接口和配置文件必须同名

2.接口必须和配置文件在同一包下

3.6.作用域(Scope)和生命周期

作用域和生命周期类别是至关重要的,因为错误的使用会导致非常严重的并发问题。

SqlSessionFactoryBuilder

旦创建了 SqlSessionFactory,就不再需要它了SqlSessionFactoryBuilder 实例的最佳作用域是方法作用域(也就是局部方法变量)

SqlSessionFactory

SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在最简单的就是使用单例模式或者静态单例模式

SqlSession

SqlSession 的实例不是线程安全的,因此是不能被共享的,所以它的最佳的作用域是请求或方法作用域把这个关闭操作放到 finally 块中

3.7.ResultMap

解决数据库字段名和实体类属性名不一致的问题 <resultMap id="userMap" type="User"> <!-- <result column="id" property="id"/>--> <!-- <result column="name" property="name"/>--> <result column="password" property="pwd"/> </resultMap> <select id="getUserByID" parameterType="int" resultMap="userMap"> select * from mybatis.user where id = #{id} </select> public class User { private int id; private String name; private String pwd; } resultMap 元素是 MyBatis 中最重要最强大的元素ResultMap 的设计思想是,对简单的语句做到零配置,对于复杂一点的语句,只需要描述语句之间的关系就行了。

4.日志

logImpl SLF4JLOG4JLOG4J2JDK_LOGGINGCOMMONS_LOGGINGSTDOUT_LOGGINGNO_LOGGING

4.1.STDOUT_LOGGING

<settings> <!--STDOUT_LOGGING 标准日志工厂,可直接使用 --> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings>

4.2.LOG4J

1.导入依赖包 <dependencies> <!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> </dependencies> 2.log4j.properties #将等级为DEBUG的日志信息输出到console和file这两个目的地,console和file的定义在下面的代码 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/mashiro.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 3.配置使用log4j <settings> <setting name="logImpl" value="LOG4J"/> </settings> 4.使用log4j import org.apache.log4j.Logger; public class UserMapperTest { static Logger logger = Logger.getLogger(UserMapperTest.class); @Test public void test02(){ logger.info("info:test02"); logger.debug("debug:test02"); logger.error("error:test02"); } } 5.日志级别 infodebugerror

5.分页查询

5.1.使用Limit分页

接口 List<User> getUserListByLimit(HashMap<String,Object> map); 配置文件 <select id="getUserListByLimit" parameterType="map" resultMap="userMap"> select * from user limit #{startIndex},#{pageIndex} </select> 测试 @Test public void test03(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Object> map = new HashMap<String, Object>(); map.put("startIndex",0); map.put("pageIndex",2); List<User> userList = mapper.getUserListByLimit(map); for (User user : userList) { System.out.println(user); } sqlSession.close(); }

5.2.使用RowBounds实现分页

接口 List<User> getUserListByRowBounds(); 配置文件 <select id="getUserListByRowBounds" resultMap="userMap"> select * from user; </select> 测试 public void test04(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); RowBounds rowBounds = new RowBounds(1, 2); List<User> userList = sqlSession.selectList("com.mashiro.dao.UserMapper.getUserListByRowBounds", null, rowBounds); for (User user : userList) { System.out.println(user); } sqlSession.close(); }

5.3.Mybatis分页插件—PageHelper

6.注解

mybatis-config.xml <mappers> <mapper class="com.mashiro.dao.UserMapper"/> </mappers> UserMapper public interface UserMapper { @Select("select * from user") List<User> getUserList(); } 测试 @Test public void test01(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userList = mapper.getUserList(); for (User user : userList) { System.out.println(user); } sqlSession.close(); } 带参数 @Select("select * from user where id = #{id}") User getUserByID(@Param("id") int id);

7.Lombok

安装Lombok插件导入依赖 <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.10</version> <scope>provided</scope> </dependency> 注解 package com.mashiro.pojo; import lombok.Data; @Data @AllArgsConstructor @NoArgsConstructor public class User { private int id; private String name; private String pwd; }

Lombok项目是一个Java库,它会自动插入编辑器和构建工具中,Lombok提供了一组有用的注释,用来消除Java类中的大量样板代码。仅五个字符(@Data)就可以替换数百行代码从而产生干净,简洁且易于维护的Java类。

看情况使用。

8.复杂查询——多对一

8.1.复杂查询环境搭建

数据库搭建 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'); 实体类(Student Teacher) package com.mashiro.pojo; import lombok.Data; @Data public class Teacher { private int id; private String name; } package com.mashiro.pojo; import lombok.Data; @Data public class Student { private int id; private String name; private Teacher teacher; } Mapper接口 package com.mashiro.dao; import com.mashiro.pojo.Teacher; import org.apache.ibatis.annotations.Param; public interface TeacherMapper { Teacher getTeacherByID(@Param("id") int id); } package com.mashiro.dao; public interface StudentMapper { } 编写Mapper对应的xml文件 <?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- namespace = 指定一个Dao接口--> <mapper namespace="com.mashiro.dao.TeacherMapper"> <select id="getTeacherByID" resultType="teacher"> select * from teacher where id = #{id}; </select> </mapper> <?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- namespace = 指定一个Dao接口--> <mapper namespace="com.mashiro.dao.StudentMapper"> </mapper> 测试类 package com.mashiro.dao; import com.mashiro.pojo.Teacher; import com.mashiro.com.mashiro.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class Test{ @Test public void Test01(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacherByID(1); System.out.println(teacher); sqlSession.close(); } }

8.2.查询学生的所有信息和对应的老师姓名

sql语句实现 select s.id,s.name,t.name from student s,teacher t where s.tid = t.id; 方式1:按照查询嵌套处理 <select id="getAllStudent" resultMap="StudentTeacher"> select * from student </select> <resultMap id="StudentTeacher" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!-- 复杂属性单独处理。 collection: 集合 association: 对象 --> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="teacher"> select * from teacher where id = #{id} </select> 方式2:按照结果嵌套处理 <select id="getAllStudent2" resultMap="StudentTeacher2"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid = t.id; </select> <resultMap id="StudentTeacher2" type="student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="teacher"> <result property="name" column="tname"/> </association> </resultMap>

9.复杂查询–一对多处理

9.1.复杂查询环境搭建

环境搭建同上实体类 package com.mashiro.pojo; import lombok.Data; @Data public class Student { private int id; private String name; private int tid; } package com.mashiro.pojo; import lombok.Data; import java.util.List; @Data public class Teacher { private int id; private String name; private List<Student> students; }

9.2.获取老师的信息与该老师下的所有学生

方式1:按照结果嵌套处理 <select id="getTeacher" resultMap="TeacherStudent"> select s.id sid,s.name sname,t.name tname,t.id tid from student s,teacher t where s.tid = t.id and t.id = #{tid} </select> <resultMap id="TeacherStudent" type="Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <collection property="students" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> </collection> </resultMap>

Tips: 指定对象类型使用javaType;获取集合中泛型信息使用ofType

方式2:按照查询嵌套处理 <select id="getTeacher2" resultMap="TeacherStudent2"> select * from teacher where id = #{tid} </select> <resultMap id="TeacherStudent2" type="teacher"> <collection property="students" javaType="ArrayList" ofType="Student" select="getStudent" column="tid"/> </resultMap> <select id="getStudent" resultType="student"> select * from teacher where tid = #{tid} </select>

9.3.Tips

collection: 集合【一对多】association: 关联【多对一】javaType: 指定实体类中属性的类型ofType: 指定映射到List和集合中的pojo类型

10.动态SQL

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。

ifchoose (when, otherwise)trim (where, set)foreach

10.1.环境搭建

数据库搭建 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 实体类 package com.mashiro.pojo; import lombok.Data; import java.util.Date; @Data public class Blog { private String id; private String title; private String author; private Date createTime; private int views; } 实体类对应的接口和xml文件 package com.mashiro.dao; import com.mashiro.pojo.Blog; public interface BlogMapper { // 添加Blog int addBlog(Blog blog); } <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- namespace = 指定一个Dao接口--> <mapper namespace="com.mashiro.dao.BlogMapper"> <insert id="addBlog" parameterType="blog"> insert into blog (id,title,author,create_time,views) values (#{id},#{title},#{author},#{createTime},#{views}); </insert> </mapper> 测试 import com.mashiro.dao.BlogMapper; import com.mashiro.pojo.Blog; import com.mashiro.com.mashiro.utils.IdUtils; import com.mashiro.com.mashiro.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.Date; public class MyTest { @Test public void Test01(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setId(IdUtils.getID()); blog.setAuthor("Mybatis is so easy"); blog.setCreateTime(new Date()); blog.setTitle("Mybatis_08"); blog.setViews(9999); mapper.addBlog(blog); blog.setId(IdUtils.getID()); blog.setTitle("Mybatis_Spring01"); mapper.addBlog(blog); blog.setId(IdUtils.getID()); blog.setTitle("Mybatis_Spring02"); mapper.addBlog(blog); blog.setId(IdUtils.getID()); blog.setTitle("Mybatis_Spring03"); mapper.addBlog(blog); sqlSession.commit(); sqlSession.close(); } }

10.2. IF

BlogMapper List<Blog> quaryBlogIF(Map map); BlogMapper.xml <select id="quaryBlogIF" parameterType="map" resultType="blog"> select * from blog <where> <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author = #{author} </if> </where> </select> Test @Test public void Test02(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String,String> map = new HashMap<String,String>(); map.put("title","Mybatis_08"); map.put("author","mashiro"); List<Blog> blogs = mapper.quaryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } }

10.3.choose(when、otherwise)

MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。 <select id="quaryBlogChoose" parameterType="map" resultType="blog"> select * from blog <where> <choose> <when test="title != null"> title = #{title} </when> <when test="author != null"> author = #{author} </when> <otherwise></otherwise> </choose> </where> </select>

10.4.Set

set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号 <update id="blogUpdate" parameterType="map"> update blog <set> <if test="id != null"> id = #{id}, </if> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author}, </if> <if test="createTime != null"> createTime = #{createTime} </if> </set> where id = #{id}; </update>

10.5.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="quaryBlogIF" parameterType="map" resultType="blog"> select * from blog <where> <include refid="if-title-author"></include> </where> </select> Tips: 最好基于单表查询不要存在where标签可提高代码复用率

10.6.Foreach

<select id="quaryBlogForeach" parameterType="map" resultType="blog"> select * from blog <where> <foreach collection="ids" item="id" open="and (" separator=" or " close=")"> id = #{id} </foreach> </where> </select>

=====================================================================================

@Test public void Test04(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); ArrayList<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(2); ids.add(3); map.put("ids",ids); List<Blog> blogs = mapper.quaryBlogForeach(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }

11.缓存

11.1.缓存

映射语句文件中的所有 select 语句的结果将会被缓存。映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。缓存会使用最近最少使用算法(LRU, Least Recently Used)算法来清除不需要的缓存。缓存不会定时进行刷新(也就是说,没有刷新间隔)。缓存会保存列表或对象(无论查询方法返回哪种)的 1024 个引用。缓存会被视为读/写缓存,这意味着获取到的对象并不是共享的,可以安全地被调用者修改,而不干扰其他调用者或线程所做的潜在修改。

11.2.一级缓存(sqlSession内)

import com.mashiro.dao.UserMapper; import com.mashiro.pojo.User; import com.mashiro.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class MyTest { @Test public void Test01(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.queryUserByID(1); System.out.println(user); System.out.println("==============================================="); User user2 = mapper.queryUserByID(1); System.out.println(user2); sqlSession.close(); } } 事务 Opening JDBC Connection Created connection 1664598529. Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6337c201] ==> Preparing: select * from user where id = ? ==> Parameters: 1(Integer) <== Columns: id, name, password <== Row: 1, mashiro, 123456 <== Total: 1 User(id=1, name=mashiro, password=123456) =============================================== User(id=1, name=mashiro, password=123456) Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6337c201] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6337c201] Returned connection 1664598529 to pool.

11.3.二级缓存(namespace)

步骤一: 开启全局缓存 <setting name="cacheEnabled" value="true"/> 步骤二: 配置缓存 <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/> Test public class MyTest { @Test public void Test01(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); SqlSession sqlSession2 = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.queryUserByID(1); System.out.println(user); sqlSession.close(); System.out.println("============================================================="); UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class); User user2 = mapper2.queryUserByID(1); System.out.println(user2); sqlSession.close(); } } 结果 Opening JDBC Connection Created connection 892965953. Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@35399441] ==> Preparing: select * from user where id = ? ==> Parameters: 1(Integer) <== Columns: id, name, password <== Row: 1, mashiro, 123456 <== Total: 1 User(id=1, name=mashiro, password=123456) Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@35399441] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@35399441] Returned connection 892965953 to pool. ============================================================= Cache Hit Ratio [com.mashiro.dao.UserMapper]: 0.5 User(id=1, name=mashiro, password=123456)

11.4.Ehcache

1.导包 <!-- https://mvnrepository.com/artifact/org.mybatis.caches/mybatis-ehcache --> <dependency> <groupId>org.mybatis.caches</groupId> <artifactId>mybatis-ehcache</artifactId> <version>1.2.1</version> </dependency> 2.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 path="./tmpdir/Tmp_EhCache"/> <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> 3.配置 <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
最新回复(0)