SSM复习总结-MyBatis
2022/1/3 23:38:41
本文主要是介绍SSM复习总结-MyBatis,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
SSM复习总结
- Mybatis简介
- 框架概念
- 常用框架
- MyBatis介绍
- MyBati框架部署
- 创建Maven项目
- 在项目中添加MyBatis依赖
- 创建Mybatis配置文件
- MyBatis框架使用
- 创建数据表
- 创建实体类
- 创建dao,定义操作方法
- 创建DAO接口的映射文件
- 将映射文件添加到主配置文件
- 单元测试
- 添加单元测试依赖
- 创建单元测试类
- 测试代码
- MyBatis的CRUD操作
- 修改操作
- 查询所有
- 查询一条记录
- 查询总记录数
- 多条件查询
- 分页查询(参数 start,pageSize)
- @Param获取参数
- arg0,arg0获取参数
- 对象类型参数
- 添加操作回填生成主键
- 工具类的封装
- 事务管理
- 手动提交事务
- 自动提交事务
- MyBatis主配置文件
- properties标签
- settings
- typeAliases标签
- plugins
- environments
- 映射文件
- MyBatis初始化
- mapper根标签
- insert标签
- delete
- update
- select
- resultMap
- cache
- sql和include
- 分页插件
- 添加分页插件的依赖
- 配置插件
- 对学生信息进行分页
- 实体关系映射
- 一对一关联
- 创建实体类
- 创建DAO接口,定义方法
- 映射文件
- 单元测试
- 使用子查询的方式
- 一对多关联查询
- 创建表
- 创建实体类
- 关联查询
- 连接查询
- 子查询
- 多对一关联
- 连接查询
- 子查询
- 多对多关联
- 创建数据表
- 关联查询
- 动态SQL
- 什么是动态SQL
- 动态SQL使用实例
- 创建数据表
- 实体类
- 封装查询条件类
- 创建DAO接口
- where标签使用
- trim
- foreach
- 传List集合类型参数
- # 和$的区别
- 日志配置
- 添加日志框架依赖
- 添加日志配置文件
- 日志信息的级别
- 配置数据库连接池-整合Druid
- 常见的连接池
- 添加Druid依赖
- 创建Druid连接池工厂
- 将DruidDataSourceFactory 配置给MyBatis数据源
- mybatis缓存
- 缓存的工作原理
- mybatis缓存
- 一级缓存
- 二级缓存
- 在mybatis.xml中开启二级缓存
- 被缓存的实体类实现序列化接口
- 查询操作的缓存开关
- 延迟加载
- 延迟加载全局方式
- 创建数据表
- 实体类
- 创建DAO接口
Mybatis简介
框架概念
框架,就是软件的半成品,完成了软件开发过程中的通用操作,程序员只需很少或者不用进行加工就能够实现特定的功能,从而简化开发人员在软件开发中的步骤,提高开发效率。
常用框架
- MVC框架:简化了Servlet的开发步骤
Struts2
SpringMVC
- 持久层框架:完成数据库操作的框架
apache DBUtils
Hibernate
Spring JPA
MyBatis
- 胶水框架:
Spring
SSM Spring SpringMVC MyBatis
SSH Spring Struts2 Hibernate
MyBatis介绍
官网
MyBatis是一个
半自动
的ORM
框架
ORM(Object Relational Mapping)对象关系映射,将Java中的一个对象与数据表中一行记录一一对应。ORM框架提供了实体类与数据表的映射关系,通过映射文件的配置,将对象保存到数据表中,实现对象的持久化。
- Mybatis的前身是iBatis,iBatis是Apache软件基金会提供的一个开源项目
- 2010年iBatis迁移到Google code,正式更名为MyBatis
- 2013年迁移到Github托管
- MyBatis特点:
1. 支持自定义sql,存储过程
2. 对原有的JDBC进行了封装,几乎消除了所有JDBC代码,让开发者只需关注SQL本身
3. 支持XML和注解配置方式自动完成ORM操作,实现结果映射
MyBati框架部署
框架部署,就是将框架引入到我们的项目中
创建Maven项目
- java工程
- Web工程
在项目中添加MyBatis依赖
- 在pom.xml中添加依赖
1. mysql
2. mybatis
3. lombok
<dependencies> <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.8</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.22</version> <scope>provided</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.49</version> </dependency> </dependencies>
创建Mybatis配置文件
idea中创建mybatis配置文件的模板
选择 resources—右键 New – Edit file Templates
- 在resorces中创建名为mybatis-config.xml
- 在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配置数据库连接信息--> <!--在environments标签中可以定义多个environment标签,每个environment标签可以定义一套连接装置--> <!--default属性,用来指定使用哪个environment标签--> <environments default="development"> <environment id="development"> <!--transactionManager标签用于配置数据库管理方式--> <transactionManager type="JDBC"/> <!--dataSource标签就是用来配置数据库连接信息--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> </configuration>
MyBatis框架使用
创建数据表
create table tb_students( sid int primary key auto_increment, stu_num char(5) not null unique, stu_name varchar(20) not null, stu_gender char(2) not null, stu_age int not null );
创建实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Student { private int stuId; private String stuNum; private String stuName; private String stuGender; private int stuAge; }
创建dao,定义操作方法
package com.qfedu.dao; import com.qfedu.pojo.Student; public interface StudentDAO { public int insertStudent(Student student); public int deleteStudent(String stuNum); }
创建DAO接口的映射文件
- 在
resources
目录下,新建名为mappers
文件夹 - 在
mappers
中新建名为StudentMapper.xml
- 在映射文件中对DAO中定义的方法进行实现:
<?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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <insert id="insertStudent" parameterType="com.qfedu.pojo.Student" useGeneratedKeys="true" keyProperty="stuId"> insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge}) </insert> <delete id="deleteStudent"> delete from tb_students where stu_num = #{stuNum} </delete> </mapper>
将映射文件添加到主配置文件
单元测试
添加单元测试依赖
<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency>
创建单元测试类
鼠标选中类,点击右键,选择Generate..
,
然后选择Test..
测试代码
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ public class StudentDAOTest { @Test public void insertStudent(){ try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**会话连接*/ SqlSession sqlSession = factory.openSession(); /**通过会话获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); /**测试StudentDAO中的方法*/ int i = studentDAO.insertStudent(new Student(0,"10001","张三","男",21)); System.out.println(i); /**提交事务*/ sqlSession.commit(); }catch (IOException e){ e.printStackTrace(); } } @Test public void deleteStudent() { try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**SqlSessionFactory表示MyBatis会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**SqlSession表示MyBatis与数据库之间的会话;通过工厂方法设计模式*/ SqlSession sqlSession = factory.openSession(); /**通过sqlSession对象调用getMapper获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); int rows = studentDAO.deleteStudent("10001"); System.out.println("rows="+rows); sqlSession.commit(); }catch (IOException e){ e.printStackTrace(); } } }
MyBatis的CRUD操作
修改操作
StudentMapper.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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <update id="updateStudent" parameterType="com.qfedu.pojo.Student"> update tb_students set stu_name = #{stuName},stu_gender = #{stuGender},stu_age = #{stuAge} where stu_num = #{stuNum} </update> </mapper>
StudentDAO
package com.qfedu.dao; import com.qfedu.pojo.Student; public interface StudentDAO { /** * 修改学生 * @param student * @return */ public int updateStudent(Student student); }
单元测试类
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; public class StudentDAOTest { @Test public void updateStudent() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); int rows = studentDAO.updateStudent(new Student(0,"10001","李斯","女",22)); System.out.println("rows="+rows); sqlSession.commit(); }catch (IOException e){ e.printStackTrace(); } } }
查询所有
- StudentMapper.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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <!-- resultMap标签用于定义实体类与数据表的映射关系 orm --> <resultMap id="listAllMap" type="com.qfedu.pojo.Student"> <id column="stuId" property="stuId"/> <result column="stu_num" property="stuNum"/> <result column="stu_name" property="stuName"/> <result column="stu_gender" property="stuGender"/> <result column="stu_age" property="stuAge"/> </resultMap> <!-- resultMap用于引用一个实体的映射关系,当配置了resultMap之后 resultType可以省略--> <select id="listStudents" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students </select> <!--resultType:指定查询结果封装的对象的实体类--> <!--resultSets 指定当前操作返回的集合类型(可省略)--> <select id="listAll" resultType="com.qfedu.pojo.Student" resultSets="java.util.List"> select sid as stuId,stu_num as stuNum,stu_name as stuName,stu_gender as stuGender,stu_age as stuAge from tb_students </select> </mapper>
- StudentDAO
package com.qfedu.dao; import com.qfedu.pojo.Student; import java.util.List; public interface StudentDAO { /** * 查询所有的学生信息 * @return */ public List<Student>listStudents(); /** * 查询所有的学生信息 * @return */ public List<Student>listAll(); }
- 单元测试
import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class StudentDAOTest { @Test public void listAll() { try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**会话连接*/ SqlSession sqlSession = factory.openSession(); /**通过会话获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); /**测试StudentDAO中的方法*/ List<Student>students=studentDAO.listAll(); for (Student student:students){ System.out.println(student); } }catch (IOException e){ e.printStackTrace(); } } }
查询一条记录
根据学号查询一个学生信息
- 在StudentDAO接口中定义方法
public interface StudentDAO { /** * 查询单个学生信息 * @param stuNum * @return */ public Student queryStudent(String stuNum); }
- 在StudentMapper.xml中配置StudentDAO接口的方法实现–SQL
<?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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <select id="queryStudent" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students where stu_num = #{stuNum} </select> <!-- resultMap标签用于定义实体类与数据表的映射关系 orm --> <resultMap id="listAllMap" type="com.qfedu.pojo.Student"> <id column="stuId" property="stuId"/> <result column="stu_num" property="stuNum"/> <result column="stu_name" property="stuName"/> <result column="stu_gender" property="stuGender"/> <result column="stu_age" property="stuAge"/> </resultMap> </mapper>
- 单元测试
import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class StudentDAOTest { @Test public void testQueryStudent(){ try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**会话连接*/ SqlSession sqlSession = factory.openSession(); /**通过会话获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); /**测试StudentDAO中的方法*/ Student student = studentDAO.queryStudent("10001"); System.out.println(student); }catch (IOException e){ e.printStackTrace(); } } }
查询总记录数
- 在StudentDAO接口中定义方法
public interface StudentDAO { /** * 返回记录的总数 * @return */ public int getCount(); }
- 在StudentMapper.xml中配置sql,通过resultType指定当前操作的返回类型为int
<?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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <select id="getCount" resultType="int"> select count(1) from tb_students </select> </mapper>
- 单元测试
import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class StudentDAOTest { @Test public void testGetCount(){ try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**会话连接*/ SqlSession sqlSession = factory.openSession(); /**通过会话获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); /**测试StudentDAO中的方法*/ int count = studentDAO.getCount(); System.out.println("count="+count); }catch (IOException e){ e.printStackTrace(); } } }
多条件查询
在mybatis进行条件查询操作:
- 如果操作方法中只有一个简单类型或者字符串类型的参数,在Mapper配置中可以直接通过#{key}获取
StudentDAO中代码如下:
public interface StudentDAO { public Student queryStudent(String stuNum); }
StudentMapper.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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <select id="queryStudent" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students where stu_num = #{stuNum} </select> </mapper>
分页查询(参数 start,pageSize)
- 如果操作方法有一个Map类型的参数,在Mapper配置中可以直接通过#{key}获取
StudentDAO中代码如下:
public interface StudentDAO { public List<Student>listStudentsByPage(HashMap<String,Integer> map); }
<?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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <select id="listStudentsByPage" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students limit #{start},#{pageSize} </select> </mapper>
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ public class StudentDAOTest { @Test public void listStudentsByPage() { try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**会话连接*/ SqlSession sqlSession = factory.openSession(); /**通过会话获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); HashMap<String,Integer> map=new HashMap<>(); map.put("start",0); map.put("pageSize",2); List<Student>list=studentDAO.listStudentsByPage(map); for (Student student:list){ System.out.println(student); } /**测试StudentDAO中的方法*/ }catch (IOException e){ e.printStackTrace(); } } }
@Param获取参数
- 在StudentDAO中定义操作方法,如果方法有多个参数,使用@Param注解声明参数的别名
public interface StudentDAO { public List<Student>listStudentsByPage2(@Param("start")Integer start,@Param("pageSize")Integer pageSize); }
- 在StudentMapper.xml中配置sql时,使用#{
别名
}获取到指定的参数
<?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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <select id="listStudentsByPage2" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students limit #{start},#{pageSize} </select> <select id="listStudentsByPage" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students limit #{start},#{pageSize} </select> <!-- limit #{arg0},#{arg1}--> <!-- limit #{param1},#{param2}--> </mapper>
arg0,arg0获取参数
注意:如果StudentDAO操作方法没有通过@Param指定参数别名,在SQL中也可以通过arg0,arg1...
或者param1,param2...
获取参数
在StudentDAO中指定方法
public interface StudentDAO { public List<Student>listStudentByParams(int start,int pageSize); }
在StudentMapper.xml中代码如下:
<select id="listStudentByParams" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students limit #{arg0},#{arg1} </select>
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; public class StudentDAOTest { @Test public void listStudentsByPage() { try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**会话连接*/ SqlSession sqlSession = factory.openSession(); /**通过会话获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); List<Student>list=studentDAO.listStudentByParams(0,2); for (Student student:list){ System.out.println(student); } /**测试StudentDAO中的方法*/ }catch (IOException e){ e.printStackTrace(); } } }
对象类型参数
- 如果操作方法有一个对象类型的参数,在Mapper配置中可以直接通过#{attrName}获取对象的指定属性值(attrName必须是参数对象的属性)
参数类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class Page { private int start; private int pageSize; }
- 在StudentDAO中指定方法
public interface StudentDAO { public List<Student>listStudentByParams(Page page); }
- 在StudentMapper.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文件相当于DAO接口的'实现类',namespace属性要指定实现DAO接口的全限定名--> <mapper namespace="com.qfedu.dao.StudentDAO"> <select id="listStudentByParams2" resultMap="listAllMap"> select sid,stu_num,stu_name,stu_gender,stu_age from tb_students limit #{start},#{pageSize} </select> <resultMap id="listAllMap" type="com.qfedu.pojo.Student"> <id column="stuId" property="stuId"/> <result column="stu_num" property="stuNum"/> <result column="stu_name" property="stuName"/> <result column="stu_gender" property="stuGender"/> <result column="stu_age" property="stuAge"/> </resultMap> </mapper>
- 单元测试
package com.qfedu.dao; import com.qfedu.pojo.Page; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ public class StudentDAOTest { @Test public void listStudentsByPage() { try { /**加载mybatis配置文件*/ InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); /**会话工厂*/ SqlSessionFactory factory = builder.build(inputStream); /**会话连接*/ SqlSession sqlSession = factory.openSession(); /**通过会话获取StudentDAO对象*/ StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); Page page = new Page(0,2); List<Student>list=studentDAO.listStudentByParams2(page); for (Student student:list){ System.out.println(student); } /**测试StudentDAO中的方法*/ }catch (IOException e){ e.printStackTrace(); } } }
添加操作回填生成主键
- StudentMapper.xml的添加操作标签-- insert
<?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.qfedu.dao.StudentDAO"> <!--useGeneratedKeys设置添加操作是否需要回填生成的主键, keyProperty:设置回填的主键值赋值到参数对象的哪个属性 --> <insert id="insertStudent" parameterType="com.qfedu.pojo.Student" useGeneratedKeys="true" keyProperty="stuId"> insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge}) </insert> </mapper>
- 在StudentDAO中添加方法
package com.qfedu.dao; import com.qfedu.pojo.Student; public interface StudentDAO { public int insertStudent(Student student); }
- 单元测试
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ public class StudentDAOTest { @Test public void listStudentsByPage() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); Student student =new Student(0,"10001","张三","男",21); studentDAO.insertStudent(student); System.out.println(student); /**提交事务*/ sqlSession.commit(); }catch (IOException e){ e.printStackTrace(); } } }
测试结果
工具类的封装
package com.qfedu.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.InputStream; /** * @author bxq * @version 1.0 * @createTime 2021/12/22 20:43 * @Description */ public class MyBatisUtil { private static SqlSessionFactory sqlSessionFactory; private static final ThreadLocal<SqlSession>local = new ThreadLocal<>(); static { try { SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream); }catch (Exception e){ e.printStackTrace(); } } public static SqlSession getSqlSession(){ SqlSession sqlSession = local.get(); if(sqlSession==null){ sqlSession=sqlSessionFactory.openSession(); local.set(sqlSession); } return sqlSession; } public static <T extends Object> T getMapper(Class<T>c){ SqlSession sqlSession =getSqlSession(); T dao =sqlSession.getMapper(c); return dao; } }
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Student; import com.qfedu.utils.MyBatisUtil; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ public class StudentDAOTest { @Test public void listStudentsByPage() { SqlSession sqlSession = MyBatisUtil.getSqlSession(); StudentDAO studentDAO = MyBatisUtil.getMapper(StudentDAO.class); Student student =new Student(0,"10002","李斯","男",21); studentDAO.insertStudent(student); System.out.println(student); /**提交事务*/ sqlSession.commit(); } }
测试结果
事务管理
SqlSession对象
getMapper(DAO.class):获取Mapper(DAO接口的实例)
事务管理
手动提交事务
sqlSession.commit();
提交事务sqlSession.rollback();
事务回滚
package com.qfedu.dao; import com.qfedu.pojo.Student; import com.qfedu.utils.MyBatisUtil; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ public class StudentDAOTest { @Test public void listStudentsByPage() { SqlSession sqlSession = MyBatisUtil.getSqlSession(); try { /**1.当我们获取sqlSession对象时,就默认开启了事务*/ StudentDAO studentDAO = MyBatisUtil.getMapper(StudentDAO.class); Student student =new Student(0,"10002","李斯","男",21); studentDAO.insertStudent(student); System.out.println(student); /**2.操作完成,手动提交事务*/ sqlSession.commit(); }catch (Exception e){ /**3.当操作出现异常,调用rollback进行回滚*/ sqlSession.rollback(); } } }
自动提交事务
sqlSessionFactory.openSession(isAutoCommit):isAutoCommit是否自动提交,默认为false
通过SqlSessionFactory调用openSession方法获取SqlSession对象时,可以通过参数设置事务是否自动提交
如果参数设置为true,表示自动提交事务:sqlSessionFactory.openSession(false)
如果参数设置为false,或者不设置参数,表示手动提交
package com.qfedu.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.InputStream; public class MyBatisUtil { private static SqlSessionFactory sqlSessionFactory; private static final ThreadLocal<SqlSession>local = new ThreadLocal<>(); static { try { SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream); }catch (Exception e){ e.printStackTrace(); } } public static SqlSession getSqlSession(boolean isAutoCommit){ SqlSession sqlSession = local.get(); if(sqlSession==null){ /**sqlSessionFactory.openSession(isAutoCommit):isAutoCommit是否自动提交,默认为false*/ /**通过SqlSessionFactory调用openSession方法获取SqlSession对象时,可以通过参数设置事务是否自动提交 * 如果参数设置为true,表示自动提交事务:sqlSessionFactory.openSession(false) * 如果参数设置为false,或者不设置参数,表示手动提交 * */ sqlSession=sqlSessionFactory.openSession(isAutoCommit); local.set(sqlSession); } return sqlSession; } /** * 手动管理事务 * @return */ public static SqlSession getSqlSession(){ return getSqlSession(false); } public static <T extends Object> T getMapper(Class<T>c){ SqlSession sqlSession =getSqlSession(true); T dao =sqlSession.getMapper(c); return dao; } }
MyBatis主配置文件
mybatis-config.xml是MyBatis框架的主配置文件,主要用于配置MyBatis数据源及属性信息
properties标签
用于设置键值对,或者加载属性文件
- 在 resources目录下创建
ds.properties
文件,配置键值对如下:
driver=com.mysql.cj.jdbc.Driver username=root password=root url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
- 在mybatis-config.xml中通过properties标签引用ds.properties,引入之后,在配置environment时可以直接使用ds.properties的key获取对应的value
settings
官网介绍
<!--设置mybatis的属性--> <settings> <!--启动二级缓存--> <setting name="cacheEnabled" value="true"/> <!--启动懒加载--> <setting name="lazyLoadingEnabled" value="true"/> </settings>
typeAliases标签
官网介绍
<!--设置mybatis的属性 typeAliases标签用于给实体类取别名,在映射文件中可以直接使用别名来替代实体类的全限定名 --> <typeAliases> <typeAlias type="com.qfedu.pojo.Student" alias="student"></typeAlias> </typeAliases>
plugins
官网介绍
<!--plugins标签,用于配置MyBatis插件(分页插件)--> <plugins> <plugin interceptor=""></plugin> </plugins>
environments
<?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标签:1.可以定义键值对,2.可以引用属性文件--> <properties resource="ds.properties"/> <!--设置mybatis的属性--> <typeAliases> <typeAlias type="com.qfedu.pojo.Student" alias="student"></typeAlias> </typeAliases> <!--在environments配置数据库连接信息--> <!--在environments标签中可以定义多个environment标签,每个environment标签可以定义一套连接装置--> <!--default属性,用来指定使用哪个environment标签--> <environments default="development"> <environment id="development"> <!--transactionManager标签用于配置数据库管理方式 type="JDBC" 可以进行事务的提交和回滚操作 type="MANAGED" 依赖容器完成事务管理,本身不进行事务的提交和回滚操作 --> <transactionManager type="JDBC"/> <!--dataSource标签就是用来配置数据库连接信息--> <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:载入映射配置(映射文件、DAO注解)--> <mappers> <mapper resource="mappers/StudentMapper.xml"/> </mappers> </configuration>
映射文件
MyBatis初始化
mapper根标签
mapper文件相当于DAO接口的实现,namespace属性要指定实现
DAO接口的全限定名
insert标签
声明添加操作(sql:insert…)
常用属性
id属性,绑定对应DAO接口中的方法
parameterType属性,用以指定接口中对应方法的参数类型(可省略)
useGeneratedKeys设置添加操作是否需要回填生成的主键,
keyProperty:设置回填的主键值赋值到参数对象的哪个属性
timeout属性,设置此操作的超时时间,如果不设置则一直等待
主键回填
<insert id="insertStudent" parameterType="student" useGeneratedKeys="true" keyProperty="stuId"> insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge}) </insert>
主键回填第二种方式
select last_insert_id() 最新插入的id
<insert id="insertStudent" parameterType="student" > <selectKey keyProperty="stuId" resultType="java.lang.Integer"> select last_insert_id() </selectKey> insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge}) </insert>
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description */ public class StudentDAOTest { @Test public void testInsert() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); Student student = new Student(0, "10004", "白青山", "男", 25); studentDAO.insertStudent(student); System.out.println(student); /**提交事务*/ sqlSession.commit(); } catch (IOException e) { e.printStackTrace(); } } }
测试结果
delete
声明删除操作
update
声明修改操作
select
声明查询操作
- id 属性,指定绑定方法 的方法名
- parameterType属性,设置参数类型
- resultType属性,指定当前sql返回数据封装的对象类型(实体类)
- resultMap属性,指定从数据表到实体类的字段和属性的对应关系
- useCache属性,指定此查询操作是否需要缓存
- timeout属性,设置超时时间
resultMap
<!-- resultMap标签用于定义实体类与数据表的映射关系 orm --> <resultMap id="listAllMap" type="com.qfedu.pojo.Student"> <id column="stuId" property="stuId"/> <result column="stu_num" property="stuNum"/> <result column="stu_name" property="stuName"/> <result column="stu_gender" property="stuGender"/> <result column="stu_age" property="stuAge"/> </resultMap>
cache
设置当前DAO进行数据库操作时的缓存属性设置
<cache size="" type="" readOnly="true"/>
sql和include
sql:sql片段
inlcude :引用sql片段
<sql id="column"> sid,stu_num,stu_name,stu_gender,stu_age </sql> <sql id="table"> tb_students </sql> <select id="listStudents" resultMap="listAllMap"> select <include refid="column"/> from <include refid="table"/> </select>
分页插件
分页插件是一个独立于MyBatis框架之外的第三方插件
PageHelper
添加分页插件的依赖
pagehelper分页插件
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.3.0</version> </dependency>
配置插件
在mybatis主配置文件mybatis-config.xml中通过plugins标签进行配置
<!-- plugins标签,用于配置MyBatis插件(分页插件)--> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin> </plugins>
对学生信息进行分页
package com.qfedu.dao; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class StudentDAOTest { @Test public void listStudents() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); PageHelper.startPage(1,4); //students是已经分页后的数据 List<Student>students=studentDAO.listStudents(); // pageInfo中就包含了数据及分页信息 PageInfo<Student>pageInfo=new PageInfo<>(students); List<Student>list=pageInfo.getList(); for (Student student:list){ System.out.println(student); } }catch (Exception e){ e.printStackTrace(); } } }
分页查询结果
实体关系映射
实体–数据实体,实体关系指的是数据与数据之间的关系
例如:用户和角色、房屋和楼栋
实体关系分为以下四种
一对一关联:
- 人和身份证 、 学生和学生证
数据表关系: - 主键关联(用户表主键和详情主键相同时,表示是匹配数据)
- 唯一外键关联
一对多、多对一关联
实例: - 一对多:班级和学生 、 类别和商品 、 楼栋和房屋
- 多对一 :学生和班级、商品和类别
数据表关系:
- 在多的一端添加外键和一的一端进行关联(父关联子,在子的一端添加父的主键)
多对多关联
实例:用户和角色、角色和权限、房屋和业主、学生和社团、订单和商品
数据表关系:建立第三张表关系表添加两个外键分别与两张表进行关联
用户(user_id) 用户角色表(uid,rid) 角色(role_id)
一对一关联
实例:用户—详情
创建数据表
--用户信息表 create table users( user_id int primary key auto_increment, user_name varchar(20) not null unique, user_pwd varchar(20) not null, user_realname varchar(20) not null, user_img varchar(100) not null ); -- 用户详情表 create table details( detail_id int primary key auto_increment, user_addr varchar(50) not null, user_tel char(11) not null, user_desc varchar(200), user_id int not null unique -- 逻辑关联 -- 外键关联(物理关联) -- constraint fk_user foreign key(uid) refrences users(user_id) );
创建实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:00 * @Description */ @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Users { private int userId; private String userName; private String userPwd; private String userRealName; private String userImg; private Details details; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:03 * @Description */ @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Details { private int detailId; private String userAddr; private String userDesc; private int userId; }
创建DAO接口,定义方法
package com.qfedu.dao; import com.qfedu.pojo.Users; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:08 * @Description */ public interface UsersDAO { /** * 根据用户名查询用户信息 * @param userName * @return */ public Users queryUser(String userName); }
映射文件
- 内连接查询
<resultMap id="userMap" type="com.qfedu.pojo.Users"> <id column="user_id" property="userId"/> <result column="user_name" property="userName"/> <result column="user_pwd" property="userPwd"/> <result column="user_realname" property="userRealName"/> <result column="user_img" property="userImg"/> <result column="user_addr" property="details.userAddr"/> <result column="user_desc" property="details.userDesc"/> <result column="user_tel" property="details.userTel"/> </resultMap> <select id="queryUser" resultMap="userMap"> SELECT u.user_id, u.user_name, u.user_pwd, u.user_realname, u.user_img, d.user_addr, d.user_desc, d.user_tel FROM users u INNER JOIN details d ON u.user_id = d.user_id where u.user_name= #{userName} </select>
- 第二种方式
<?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.qfedu.dao.UsersDAO"> <sql id="table">users</sql> <resultMap id="userMap" type="com.qfedu.pojo.Users"> <id column="user_id" property="userId"/> <result column="user_name" property="userName"/> <result column="user_pwd" property="userPwd"/> <result column="user_realname" property="userRealName"/> <result column="user_img" property="userImg"/> <association property="details" javaType="com.qfedu.pojo.Details"> <result column="user_addr" property="userAddr"/> <result column="user_desc" property="userDesc"/> <result column="user_tel" property="userTel"/> </association> </resultMap> <select id="queryUser" resultMap="userMap"> SELECT u.user_id, u.user_name, u.user_pwd, u.user_realname, u.user_img, d.user_addr, d.user_desc, d.user_tel FROM users u INNER JOIN details d ON u.user_id = d.user_id where u.user_name= #{userName} </select> </mapper>
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Details; import com.qfedu.pojo.Users; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:15 * @Description */ public class UsersDAOTest { @Test public void queryUser() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = factory.openSession(); UsersDAO dao = sqlSession.getMapper(UsersDAO.class); DetailsDAO detailsDAO = sqlSession.getMapper(DetailsDAO.class); Users users = dao.queryUser("zhangsan"); System.out.println(users); }catch (IOException e){ e.printStackTrace(); } } }
测试结果
使用子查询的方式
使用子查询的方式查询一对一关联关系对应的数据
实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:03 * @Description */ @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Details { private int detailId; private String userAddr; private String userTel; private String userDesc; private int userId; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:00 * @Description */ @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Users { private int userId; private String userName; private String userPwd; private String userRealName; private String userImg; private Details details; }
映射文件
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 namespace="com.qfedu.dao.UsersDAO"> <sql id="table">users</sql> <resultMap id="userMap" type="com.qfedu.pojo.Users"> <id column="user_id" property="userId"/> <result column="user_name" property="userName"/> <result column="user_pwd" property="userPwd"/> <result column="user_realname" property="userRealName"/> <result column="user_img" property="userImg"/> <!--association调用子查询,关联查询一个对象 column:子查询的列(也就是传递的子查询参数的值)--> <association property="details" select="com.qfedu.dao.DetailsDAO.queryByUserId" column="user_id"> </association> </resultMap> <select id="queryUser" resultMap="userMap"> SELECT u.user_id, u.user_name, u.user_pwd, u.user_realname, u.user_img FROM users u where u.user_name= #{userName} </select> </mapper>
UserDAO
package com.qfedu.dao; import com.qfedu.pojo.Users; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:08 * @Description */ public interface UsersDAO { /** * 根据用户名查询用户信息 * @param userName * @return */ public Users queryUser(String userName); }
DetailsMapper.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.qfedu.dao.DetailsDAO"> <sql id="table">details</sql> <resultMap id="detailsMap" type="com.qfedu.pojo.Details"> <id column="detail_id" property="detailId"/> <result column="user_id" property="userId"/> <result column="user_addr" property="userAddr"/> <result column="user_desc" property="userDesc"/> <result column="user_tel" property="userTel"/> </resultMap> <select id="queryByUserId" resultMap="detailsMap"> select d.detail_id,d.user_id,d.user_addr,d.user_desc,d.user_tel from details d where d.user_id = #{userId} </select> </mapper>
package com.qfedu.dao; import com.qfedu.pojo.Details; /** * @author Helen * @version 1.0 * @createTime 2021/12/24 22:08 * @Description */ public interface DetailsDAO { /** * 根据用户id查询用户详情 * . * @param userId * @return */ public Details queryByUserId(String userId); }
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Details; import com.qfedu.pojo.Users; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; public class UsersDAOTest { @Test public void queryUser() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = factory.openSession(); UsersDAO dao = sqlSession.getMapper(UsersDAO.class); DetailsDAO detailsDAO = sqlSession.getMapper(DetailsDAO.class); Users users = dao.queryUser("zhangsan"); System.out.println(users); }catch (IOException e){ e.printStackTrace(); } } }
测试结果
Users(userId=1, userName=zhangsan, userPwd=123, userRealName=张三, userImg=http://www.baidu.com, details=Details(detailId=1, userAddr=湖北省武汉市, userTel=13009098765, userDesc=武汉人, userId=1))
一对多关联查询
案例:班级(1)-学生(n)
创建表
-- 创建班级信息表 create table classess( cid int primary key auto_increment, cname varchar(30) not null unique, cdesc varchar(100) ); -- 创建学生信息表 create table students( sid char(5) primary key, sname varchar(20) not null, sage int not null, scid int not null );
创建实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 20:38 * @Description 班级 */ @ToString @AllArgsConstructor @NoArgsConstructor @Data public class Clazz { private int classId; private String className; private String classDesc; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description 学生 */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Student { /**学号*/ private int stuId; private String stuName; private int stuAge; /**学生所在班级的id*/ private int studeCid; }
关联查询
查询一个班级的时候,要关联查询出这个班级下的所有学生
连接查询
实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 20:38 * @Description 班级 */ @ToString @AllArgsConstructor @NoArgsConstructor @Data public class Clazz { private int classId; private String className; private String classDesc; /**存储当前班级下的学生信息*/ List<Student>students; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description 学生 */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Student { /**学号*/ private int stuId; private String stuName; private int stuAge; /**学生所在班级的id*/ private int stuCid; }
classMapper.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.qfedu.dao.ClassDAO"> <resultMap id="classMap" type="com.qfedu.pojo.Clazz"> <id column="cid" property="classId"/> <result column="cname" property="className"/> <result column="cdesc" property="classDesc"/> <!--Clazz对象的students是一个list集合,需要使用collection标签 collection标签的ofType属性声明集合中元素的类型 --> <collection property="students" ofType="com.qfedu.pojo.Student"> <id column="sid" property="stuId"/> <result column="scid" property="stuCid"/> <result column="sname" property="stuName"/> <result column="sage" property="stuAge"/> </collection> </resultMap> <select id="queryClass" resultMap="classMap"> select c.cid, c.cname, c.cdesc, s.scid, s.sage, s.sid, s.sname from classess c inner join students s on c.cid = s.scid where c.cid = #{classId} </select> </mapper>
ClassDAO
package com.qfedu.dao; import com.qfedu.pojo.Clazz; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 21:27 * @Description */ public interface ClassDAO { /** * 根据班级id查询学生信息 * @param classId * @return */ public Clazz queryClass(int classId); }
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Clazz; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import static org.junit.Assert.*; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 21:56 * @Description */ public class ClassDAOTest { @Test public void queryClass() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = factory.openSession(); ClassDAO classDAO = sqlSession.getMapper(ClassDAO.class); Clazz clazz = classDAO.queryClass(1); System.out.println(clazz); } catch (IOException e) { e.printStackTrace(); } } }
查询结果
子查询
classMapper.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.qfedu.dao.ClassDAO"> <resultMap id="classMap" type="com.qfedu.pojo.Clazz"> <id column="cid" property="classId"/> <result column="cname" property="className"/> <result column="cdesc" property="classDesc"/> <!--Clazz对象的students是一个list集合,需要使用collection标签 collection标签的ofType属性声明集合中元素的类型 --> <collection property="students" select="com.qfedu.dao.StudentDAO.listStudentsByCid" column="cid"/> </resultMap> <select id="queryClass" resultMap="classMap"> select c.cid, c.cname, c.cdesc from classess c where c.cid = #{classId} </select> </mapper>
classDAO
package com.qfedu.dao; import com.qfedu.pojo.Clazz; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 21:27 * @Description */ public interface ClassDAO { /** * 根据班级id查询学生信息 * @param classId * @return */ public Clazz queryClass(int classId); }
StudentMapper.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.qfedu.dao.StudentDAO"> <resultMap id="studentMap" type="com.qfedu.pojo.Student"> <id column="sid" property="stuId"/> <result column="scid" property="stuCid"/> <result column="sage" property="stuAge"/> <result column="sname" property="stuName"/> </resultMap> <select id="listStudentsByCid" resultMap="studentMap"> select s.scid,s.sage,s.sid,s.sname from students s where s.scid =#{cid} </select> </mapper>
StudentDAO
package com.qfedu.dao; import com.qfedu.pojo.Student; import java.util.List; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:17 * @Description */ public interface StudentDAO { public List<Student>listStudentsByCid(int cid); }
查询结果
多对一关联
实例:学生(n)-班级(1)
当查询一个学生的时候,关联查询这个学生所在的班级信息
连接查询
StudentMapper.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.qfedu.dao.StudentDAO"> <resultMap id="studentMap" type="com.qfedu.pojo.Student"> <id column="sid" property="stuId"/> <result column="scid" property="stuCid"/> <result column="sage" property="stuAge"/> <result column="sname" property="stuName"/> <result column="cid" property="clazz.classId"/> <result column="cname" property="clazz.className"/> <result column="cdesc" property="clazz.classDesc"/> </resultMap> <select id="queryStudentBySid" resultMap="studentMap"> select s.scid,s.sage,s.sid,s.sname,c.cid,c.cname,c.cdesc from students s inner join classess c on s.scid = c.cid where s.sid =#{sid} </select> </mapper>
StudentDAO
package com.qfedu.dao; import com.qfedu.pojo.Student; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:17 * @Description */ public interface StudentDAO { public Student queryStudentBySid(String sid); }
实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description 学生 */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Student { /**学号*/ private int stuId; private String stuName; private int stuAge; /**学生所在班级的id*/ private int stuCid; /**学生所在班级信息*/ private Clazz clazz; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 20:38 * @Description 班级 */ @ToString @AllArgsConstructor @NoArgsConstructor @Data public class Clazz { private int classId; private String className; private String classDesc; }
单元测试
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import static org.junit.Assert.*; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 23:02 * @Description */ public class StudentDAOTest { @Test public void queryStudentBySid() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = factory.openSession(); StudentDAO dao = sqlSession.getMapper(StudentDAO.class); Student student = dao.queryStudentBySid("1004"); System.out.println(student); } catch (IOException e) { e.printStackTrace(); } } }
测试结果
子查询
- 修改StudentMapper.xml和ClassMapper.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.qfedu.dao.StudentDAO"> <resultMap id="studentMap" type="com.qfedu.pojo.Student"> <id column="sid" property="stuId"/> <result column="scid" property="stuCid"/> <result column="sage" property="stuAge"/> <result column="sname" property="stuName"/> <association property="clazz" select="com.qfedu.dao.ClassDAO.queryClass" column="scid"/> </resultMap> <select id="queryStudentBySid" resultMap="studentMap"> select s.scid,s.sage,s.sid,s.sname from students s where s.sid =#{sid} </select> </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.qfedu.dao.ClassDAO"> <resultMap id="classMap" type="com.qfedu.pojo.Clazz"> <id column="cid" property="classId"/> <result column="cname" property="className"/> <result column="cdesc" property="classDesc"/> </resultMap> <select id="queryClass" resultMap="classMap"> select c.cid, c.cname, c.cdesc from classess c where c.cid = #{classId} </select> </mapper>
- 修改StudentDAO和ClassDAO
package com.qfedu.dao; import com.qfedu.pojo.Student; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:17 * @Description */ public interface StudentDAO { public Student queryStudentBySid(String sid); }
package com.qfedu.dao; import com.qfedu.pojo.Clazz; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 21:27 * @Description */ public interface ClassDAO { /** * 根据班级id查询学生信息 * @param classId * @return */ public Clazz queryClass(int classId); }
- 实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 20:38 * @Description 班级 */ @ToString @AllArgsConstructor @NoArgsConstructor @Data public class Clazz { private int classId; private String className; private String classDesc; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author bxq * @version 1.0 * @createTime 2021/12/21 20:18 * @Description 学生 */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Student { /**学号*/ private int stuId; private String stuName; private int stuAge; /**学生所在班级的id*/ private int stuCid; /**学生所在班级信息*/ private Clazz clazz; }
- 单元测试
package com.qfedu.dao; import com.qfedu.pojo.Student; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import static org.junit.Assert.*; /** * @author Helen * @version 1.0 * @createTime 2021/12/27 23:02 * @Description */ public class StudentDAOTest { @Test public void queryStudentBySid() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = factory.openSession(); StudentDAO dao = sqlSession.getMapper(StudentDAO.class); Student student = dao.queryStudentBySid("1004"); System.out.println(student); } catch (IOException e) { e.printStackTrace(); } } }
- 测试结果
多对多关联
相当于两个一对多的关系
案例:学生(m)-课程(n)
创建数据表
-- 学生信息表(如上) create table students( sid char(5) primary key, sname varchar(20) not null, sage int not null, scid int not null ); -- 课程信息表 create table courses( course_id int primary key auto_increment, course_name varchar(50) not null ); -- 选课信息表/成绩表(学号、课程号、成绩) create table grade( sid char(5) not null, cid int not null, score int not null );
关联查询
查询学生时,同时查询学生选择的课程
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 20:59 * @Description 学生类 */ @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Student { /** * 学号 */ private int stuId; private String stuName; private int stuAge; /** * 学生所在班级的id */ private int stuCid; /**学生选择的课程*/ private List<Course> courses; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 21:01 * @Description 课程 */ @ToString @NoArgsConstructor @AllArgsConstructor @Data public class Course { private int courseId; private String courseName; }
子查询配置
<?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.qfedu.dao.CourseDAO"> <resultMap id="courseMap2" type="com.qfedu.pojo.Course"> <id column="course_id" property="courseId"/> <result column="course_name" property="courseName"/> <collection property="students" select="com.qfedu.dao.StudentDAO.queryStudentByCourseId" column="course_id"/> </resultMap> <select id="queryCourseById" resultMap="courseMap2"> select course_id, course_name from courses where course_id = #{courseId} </select> </mapper>
package com.qfedu.dao; import com.qfedu.pojo.Course; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 21:13 * @Description */ public interface CourseDAO { /** * 根据课程id查询课程信息 * @param courseId * @return */ public List<Course>queryCourseById(int courseId); }
<?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.qfedu.dao.StudentDAO"> <resultMap id="studentMap" type="com.qfedu.pojo.Student"> <id column="sid" property="stuId"/> <result column="sname" property="stuName"/> <result column="sage" property="stuAge"/> </resultMap> <!--根据课程id查询选择了这门课程的学生信息--> <select id="queryStudentByCourseId" resultMap="studentMap"> select s.sid, s.sname, s.sage from students s INNER JOIN grade g on s.sid = g.sid where g.cid = #{courseId} </select> </mapper>
package com.qfedu.dao; import com.qfedu.pojo.Student; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 21:40 * @Description */ public interface StudentDAO { /** * 查询学生信息根据课程id * @param courseId * @return */ public List<Student>queryStudentByCourseId(int courseId); }
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.qfedu.pojo.Course; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 21:15 * @Description */ public class CourseDAOTest { @Test public void queryCourseById() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = factory.openSession(); CourseDAO dao = sqlSession.getMapper(CourseDAO.class); List<Course> course = dao.queryCourseById(1); String result = JSON.toJSONString(course); System.out.println(result); } catch (IOException e) { e.printStackTrace(); } } }
单元测试结果
根据课程编号查询课程时,同时查询选择了这门课程的学生
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 21:01 * @Description 课程 */ @ToString @NoArgsConstructor @AllArgsConstructor @Data public class Course { private int courseId; private String courseName; private List<Student> students; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 20:59 * @Description 学生类 */ @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Student { /** * 学号 */ private int stuId; private String stuName; private int stuAge; /** * 学生所在班级的id */ private int stuCid; /**学生选择的课程*/ private List<Course> courses; }
<?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.qfedu.dao.CourseDAO"> <resultMap id="courseMap" type="com.qfedu.pojo.Course"> <id column="course_id" property="courseId"/> <result column="course_name" property="courseName"/> <collection property="students" ofType="com.qfedu.pojo.Student"> <id column="sid" property="stuId"/> <result column="scid" property="stuCid"/> <result column="sname" property="stuName"/> <result column="sage" property="stuAge"/> </collection> </resultMap> <select id="queryCourse" resultMap="courseMap"> select s.sid, s.scid, s.sname, s.sage, c.course_id, c.course_name from courses c INNER JOIN grade g INNER JOIN students s on c.course_id = g.cid and g.sid = s.sid where c.course_id = #{courseId} </select> </mapper>
CourseDAO
package com.qfedu.dao; import com.qfedu.pojo.Course; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 21:13 * @Description */ public interface CourseDAO { /** * 查询课程 * @param courseId * @return */ public List<Course> queryCourse(int courseId ); }
单元测试
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.qfedu.pojo.Course; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 21:15 * @Description */ public class CourseDAOTest { @Test public void queryCourse() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = factory.openSession(); CourseDAO dao = sqlSession.getMapper(CourseDAO.class); List<Course> course = dao.queryCourse(1); String result = JSON.toJSONString(course); System.out.println(result); } catch (IOException e) { e.printStackTrace(); } } }
测试结果
动态SQL
什么是动态SQL
根据查询条件动态完成SQL的拼接
动态SQL使用实例
案例:心仪对象搜索
创建数据表
create table members( member_id int primary key auto_increment, member_nick varchar(20) not null unique, member_gender char(2) not null, member_age int not null, member_city varchar(30) not null );
实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 22:52 * @Description 会员 */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Member { private int memberId; private String memberNick; private String memberGender; private String memberAge; private String memberCity; }
封装查询条件类
package com.qfedu.pojo; import lombok.Data; /** * @author Helen * @version 1.0 * @createTime 2021/12/29 20:11 * @Description 搜索条件所在的 */ @Data public class MemberSearchCondition { private String gender; private int minAge; private int maxAge; private String city; }
创建DAO接口
package com.qfedu.dao; import com.qfedu.pojo.Member; import com.qfedu.pojo.MemberSearchCondition; import java.util.HashMap; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 22:55 * @Description */ public interface MemberDAO { /** * 在多条件查询中,如果查询条件不确定,可以直接使用HashMap作为参数, * 优点:无需单独定义传递查询条件的类 * 缺点:当向Map中存放参数时,key必须与动态sql保持一致 * @param params * @return */ public List<Member> queryMember(HashMap<String,Object>params); /** * 也可以定义专门用于存放查询条件的实体类存放参数 * 优点:设置参数时无需关注属性名 * 缺点:需要单独定义一个类来封装参数 * @param params * @return */ // public List<Member> searchMember(MemberSearchCondition params); }
<?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.qfedu.dao.MemberDAO"> <resultMap id="memberMap" type="com.qfedu.pojo.Member"> <id column="member_id" property="memberId"/> <result column="member_nick" property="memberNick"/> <result column="member_gender" property="memberGender"/> <result column="member_age" property="memberAge"/> <result column="member_city" property="memberCity"/> </resultMap> <!--where 1=1 恒成立 > 大于 < 小于 --> <select id="queryMember" resultMap="memberMap"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m where 1=1 <!-- <if test="memberId !=null and memberId !=''">--> <!-- and m.member_id = #{memberId}--> <!-- </if>--> <if test="gender !=null and gender !=''"> and m.member_gender = #{gender} </if> <if test="minAge !=null and minAge !=''"> and m.member_age >= #{minAge} </if> <if test="maxAge !=null and maxAge !=''"> and m.member_age <= #{maxAge} </if> <if test="city !=null and city !=''"> and m.member_city = #{city} </if> </select> </mapper>
单元测试
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void queryMember() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); HashMap<String, Object> hashMap = new HashMap<>(); hashMap.put("gender","女"); hashMap.put("minAge",18); hashMap.put("maxAge",22); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); List<Member>members=dao.queryMember(hashMap); String result = JSON.toJSONString(members); System.out.println(result); } catch (IOException e) { e.printStackTrace(); } } }
测试结果
where标签使用
代替where 1=1
<?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.qfedu.dao.MemberDAO"> <resultMap id="memberMap" type="com.qfedu.pojo.Member"> <id column="member_id" property="memberId"/> <result column="member_nick" property="memberNick"/> <result column="member_gender" property="memberGender"/> <result column="member_age" property="memberAge"/> <result column="member_city" property="memberCity"/> </resultMap> <select id="queryMember" resultMap="memberMap"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m <where> <if test="gender !=null and gender !=''"> and m.member_gender = #{gender} </if> <if test="minAge !=null and minAge !=''"> and m.member_age >= #{minAge} </if> <if test="maxAge !=null and maxAge !=''"> and m.member_age <= #{maxAge} </if> <if test="city !=null and city !=''"> and m.member_city = #{city} </if> </where> </select> </mapper>
trim
<?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.qfedu.dao.MemberDAO"> <resultMap id="memberMap" type="com.qfedu.pojo.Member"> <id column="member_id" property="memberId"/> <result column="member_nick" property="memberNick"/> <result column="member_gender" property="memberGender"/> <result column="member_age" property="memberAge"/> <result column="member_city" property="memberCity"/> </resultMap> <!--prefix:前缀 prefixOverrides:前缀重写 suffix:后缀 --> <select id="queryMember" resultMap="memberMap"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m <trim prefix="where" prefixOverrides="and | or" suffix="order by member_id desc"> <if test="gender !=null and gender !=''"> and m.member_gender = #{gender} </if> <if test="minAge !=null and minAge !=''"> and m.member_age >= #{minAge} </if> <if test="maxAge !=null and maxAge !=''"> and m.member_age <= #{maxAge} </if> <if test="city !=null and city !=''"> and m.member_city = #{city} </if> </trim> </select> <!--执行的sql语句 select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m where m.member_gender = ? and m.member_age >= ? and m.member_age <= ? order by member_id desc --> </mapper>
foreach
<?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.qfedu.dao.MemberDAO"> <resultMap id="memberMap" type="com.qfedu.pojo.Member"> <id column="member_id" property="memberId"/> <result column="member_nick" property="memberNick"/> <result column="member_gender" property="memberGender"/> <result column="member_age" property="memberAge"/> <result column="member_city" property="memberCity"/> </resultMap> <!-- collection:集合的类型:可以是list,array open:以什么为开头 close:以什么为关闭 separator:分割符 <foreach collection="list" item="cityName" open="(" separator="," close=")" index="index"> #{cityName} </foreach> --> <select id="searchMemberByCity" resultMap="memberMap"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m <where> and m.member_city in <foreach collection="array" item="cityName" open="(" separator="," close=")" index="index"> #{cityName} </foreach> </where> </select> </mapper>
package com.qfedu.dao; import com.qfedu.pojo.Member; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 22:55 * @Description */ public interface MemberDAO { /** * * @param cities * @return */ public List<Member>searchMemberByCity(String[]cities); }
单元测试
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void queryMember() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); String[]str={"武汉","宜昌"}; List<Member>members=dao.searchMemberByCity(str); String result = JSON.toJSONString(members); System.out.println(result); members.forEach(System.out::println); } catch (IOException e) { e.printStackTrace(); } } }
传List集合类型参数
<?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.qfedu.dao.MemberDAO"> <resultMap id="memberMap" type="com.qfedu.pojo.Member"> <id column="member_id" property="memberId"/> <result column="member_nick" property="memberNick"/> <result column="member_gender" property="memberGender"/> <result column="member_age" property="memberAge"/> <result column="member_city" property="memberCity"/> </resultMap> <!-- collection:集合的类型:可以是list,array open:以什么为开头 close:以什么为关闭 separator:分割符 <foreach collection="list" item="cityName" open="(" separator="," close=")" index="index"> #{cityName} </foreach> --> <select id="searchMemberByCity" resultMap="memberMap"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m <where> and m.member_city in <foreach collection="list" item="cityName" open="(" separator="," close=")" index="index"> #{cityName} </foreach> </where> </select> </mapper>
package com.qfedu.dao; import com.qfedu.pojo.Member; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 22:55 * @Description */ public interface MemberDAO { /** * * @param cities * @return */ public List<Member>searchMemberByCity(List<String>cities); }
单元测试
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void queryMember() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); String[]str={"武汉","宜昌"}; List<String>cities=new ArrayList<>(Arrays.asList(str)); List<Member>members=dao.searchMemberByCity(cities); String result = JSON.toJSONString(members); System.out.println(result); members.forEach(System.out::println); } catch (IOException e) { e.printStackTrace(); } } }
测试结果
# 和$的区别
${key}表示获取参数,先获取参数的值拼接到SQL语句中,再编译执行SQL语句
#{key}表示获取参数,先完成SQL编译(预编译),预编译之后再将获取的参数设置到SQL语句中,可以避免SQL注入的问题
日志配置
MyBatis作为一个封装好的ORM框架,其运行过程我们没办法跟踪,为了让开发者了解MyBatis执行流程及每个执行步骤所完成的工作,MyBatis框架本身支持j日志框架,对运行的过程进行跟踪记录。我们只需对MyBats进行相关的日志配置,就可以看到MyBatis运行过程中的 日志信息。
添加日志框架依赖
<dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
添加日志配置文件
mybatis会自动找名为log4j.properties
文件,所以日志配置文件名称只能叫log4j.properties
- 在resource目录下创建名为
log4j.properties
文件 - 在
log4j.properties
文件中配置日志输出格式
# 声明日志的输出级别及输出方式 log4j.rootLogger=DEBUG, stdout #MyBatis logging configuration log4j.logger.org.mybatis.example.BlogMapper=TRACE # Console output log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout # 定义日志的打印格式 %t表示线程名称 %5p 日志级别 %msg 日志信息 #log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %msg %m%n log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
日志信息的级别
在使用日志级别输出日志信息的时候,会根据输出的日志信息的重要程度分为5个级别
配置数据库连接池-整合Druid
MyBatis做为一个ORM框架,在进行数据库操作时是需要和数据库连接的,MyBatis支持基于数据库连接池的连接创建方式。
当我们配置MyBatis数据源时,只要配置了dataSource标签的type属性值为POOLED时,就可以使用MyBatis内置的连接池管理连接
如果想要使用第三方的数据库连接池,则需进行自定义配置
常见的连接池
- dbcp
- c3p0
- Druid 性能也比较好,提供了比较便捷的监控系统
- Hikari 性能最好
添加Druid依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency>
创建Druid连接池工厂
package com.qfedu.utils; import com.alibaba.druid.pool.DruidDataSource; import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory; /** * @author Helen * @version 1.0 * @createTime 2022/1/1 10:47 * @Description */ public class DruidDataSourceFactory extends PooledDataSourceFactory { public DruidDataSourceFactory(){ this.dataSource = new DruidDataSource(); } }
将DruidDataSourceFactory 配置给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> <!--properties标签:1.可以定义键值对,2.可以引用属性文件--> <properties resource="ds.properties"/> <!-- <settings>--> <!-- <setting name="logImpl" value="STDOUT_LOGGING"/>--> <!-- </settings>--> <!--设置mybatis的属性--> <typeAliases> <!-- <typeAlias type="com.qfedu.pojo.Student" alias="student"></typeAlias>--> </typeAliases> <!-- plugins标签,用于配置MyBatis插件(分页插件)--> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin> </plugins> <!--在environments配置数据库连接信息--> <!--在environments标签中可以定义多个environment标签,每个environment标签可以定义一套连接装置--> <!--default属性,用来指定使用哪个environment标签--> <environments default="development"> <environment id="development"> <!--transactionManager标签用于配置数据库管理方式 type="JDBC" 可以进行事务的提交和回滚操作 type="MANAGED" 依赖容器完成事务管理,本身不进行事务的提交和回滚操作 --> <transactionManager type="JDBC"/> <!--dataSource标签就是用来配置数据库连接信息--> <!--POOLED使用MyBatis内置的连接池实现 mybatis需要一个连接池工厂,这个工厂可以生产数据库连接池 PooledDataSourceFactory --> <dataSource type="com.qfedu.utils.DruidDataSourceFactory" > <property name="driverClassName" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <!--mappers:载入映射配置(映射文件、DAO注解)--> <mappers> <mapper resource="mappers/CourseMapper.xml"/> <mapper resource="mappers/StudentMapper.xml"/> <mapper resource="mappers/MemberMapper.xml"/> </mappers> </configuration>
单元测试
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void queryMember() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); String[]str={"武汉","宜昌"}; List<String>cities=new ArrayList<>(Arrays.asList(str)); List<Member>members=dao.searchMemberByCity(cities); members.forEach(System.out::println); } catch (IOException e) { e.printStackTrace(); } } }
运行单元测试,结果如下
mybatis缓存
MyBatis是基于JDBC的封装,使数据库操作更加便捷;MyBatis除了对JDBC操作步骤进行了封装之外也对其他性能进行了优化:
- 在MyBatis中引入了缓存机制,用于提升MyBatis的检索效率
缓存的工作原理
缓存,就是存储数据的内存,减少了对数据库的操作次数,提高了数据查询的效率
mybatis缓存
MyBatis缓存分为一级缓存和二级缓存
一级缓存
一级缓存也叫做SqlSession缓存,为每个SqlSession单独分配的缓存内存,无需手动开启可直接使用;多个SqlSession缓存是不共享的
特性:
1.如果多次查询使用的是同一个SqlSession对象,则第一次查询之后数据会存放到缓存,后续的查询(执行同一条sql语句)则直接访问缓存中存储的数据
2.如果第一次查询完成之后,对查询出的对象进行修改(此修改会影响到缓存),第二次查询会直接访问缓存,造成第二次查询的结果与数据库不一致
3.当我们进行在查询时想要跳过缓存直接查询数据库,则可以通过sqlSession.clearCache();来清除当前SqlSession的缓存
4. 如果第一次查询之后第二次查询之前,使用当前的sqlSession执行了修改操作,此修改操作会使第一次查询并缓存的数据失效,因此第二次查询会再次访问数据库
测试代码
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void queryById() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); Member member1=dao.queryById(1); System.out.println(member1); member1.setMemberAge(23); /**清除一级缓存,注释此行代码,执行的sql查询语句只执行一次*/ sqlSession.clearCache(); System.out.println("==============================================="); MemberDAO dao2 = sqlSession.getMapper(MemberDAO.class); Member member2=dao2.queryById(1); System.out.println(member2); } catch (IOException e) { e.printStackTrace(); } } }
一级缓存测试代码
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void queryById() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); Member member1=dao.queryById(1); System.out.println(member1); member1.setMemberAge(23); /**清除一级缓存*/ // sqlSession.clearCache(); System.out.println("==============================================="); MemberDAO dao2 = sqlSession.getMapper(MemberDAO.class); Member member2=dao2.queryById(1); System.out.println(member2); } catch (IOException e) { e.printStackTrace(); } } }
一级缓存测试代码
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void queryById() { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); Member member1=dao.queryById(1); System.out.println(member1); member1.setMemberAge(23); /**清除一级缓存*/ // sqlSession.clearCache(); System.out.println("==============================================="); MemberDAO dao2 = sqlSession.getMapper(MemberDAO.class); Member member2=dao2.queryById(1); System.out.println(member2); } catch (IOException e) { e.printStackTrace(); } } }
单元测试结果
两次查询与数据库数据不一致的问题
二级缓存
二级缓存也称为SqlSessionFactory级缓存,通过同一个factory对象获取的SqlSession可以共享二级缓存。在应用服务器中SqlSessionFactory是单例的,因此我们二级缓存可以实现全局共享
特性:
- 二级缓存默认没有开启,需要在mybatis-config.xml中的settings标签开启
- 二级缓存只能缓存
在mybatis.xml中开启二级缓存
<settings> <!--开启mybatis二级缓存--> <setting name="cacheEnabled" value="true"/> </settings>
- 在需要使用二级缓存的Maper.xml文件中配置cache标签使用功能二级缓存
<?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.qfedu.dao.MemberDAO"> <cache/> <select id="queryById" resultMap="memberMap"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m where m.member_id=#{memberId} </select> <resultMap id="memberMap" type="com.qfedu.pojo.Member"> <id column="member_id" property="memberId"/> <result column="member_nick" property="memberNick"/> <result column="member_gender" property="memberGender"/> <result column="member_age" property="memberAge"/> <result column="member_city" property="memberCity"/> </resultMap> <!-- collection:集合的类型:可以是list,array open:以什么为开头 close:以什么为关闭 separator:分割符 <foreach collection="list" item="cityName" open="(" separator="," close=")" index="index"> #{cityName} </foreach> --> <select id="searchMemberByCity" resultMap="memberMap"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m <where> and m.member_city in <foreach collection="list" item="cityName" open="(" separator="," close=")" index="index"> #{cityName} </foreach> </where> </select> </mapper>
被缓存的实体类实现序列化接口
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.io.Serializable; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 22:52 * @Description 会员 */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Member implements Serializable { private int memberId; private String memberNick; private String memberGender; private int memberAge; private String memberCity; }
单元测试代码
package com.qfedu.dao; import com.alibaba.fastjson.JSON; import com.qfedu.pojo.Member; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2021/12/28 23:00 * @Description */ public class MemberDAOTest { @Test public void test(){ try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(inputStream); SqlSession sqlSession = factory.openSession(); MemberDAO dao = sqlSession.getMapper(MemberDAO.class); Member member=dao.queryById(1); System.out.println(member); /** * 第一次查询之后执行sqlSession.commit(),会将当前sqlSession的查询结果缓存到二级缓存 */ sqlSession.commit(); System.out.println("================================"); SqlSession sqlSession1 = factory.openSession(); MemberDAO dao1 = sqlSession1.getMapper(MemberDAO.class); Member member2=dao1.queryById(1); /** Mybatis二级缓存是SessionFactory,如果两次查询基于同一个SessionFactory,那么就从二级缓存中取数据,而不用到数据库里去取了。 */ System.out.println(member2); } catch (IOException e) { e.printStackTrace(); } } }
查询操作的缓存开关
<cache/> <!--useCache:是否使用缓存:false,不使用--> <select id="queryById" resultMap="memberMap" useCache="false"> select m.member_id, m.member_nick, m.member_gender, m.member_age, m.member_city from members m where m.member_id=#{memberId} </select>
延迟加载
延迟加载–如果在MyBatis中开启了延迟加载,执行子查询时(至少查询两次及以上),默认只执行第一次查询,当用到子查询的查询结果时,才会触发子查询的执行;如果无需使用子查询结果,则子查询不会执行
延迟加载全局方式
创建数据表
CREATE TABLE `category_` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `product_` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `price` float(10,2) DEFAULT NULL, `cid` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
实体类
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.io.Serializable; /** * @author Helen * @version 1.0 * @createTime 2022/1/1 18:05 * @Description */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Product implements Serializable { private int id; private String name; private float price; }
package com.qfedu.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; import java.io.Serializable; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2022/1/1 18:06 * @Description */ @Data @AllArgsConstructor @NoArgsConstructor @ToString public class Category implements Serializable { private int id; private String name; List<Product> products; }
创建DAO接口
<?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.qfedu.dao.ProductDAO"> <resultMap id="productBean" type="com.qfedu.pojo.Product"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="price" property="price"/> </resultMap> <select id="queryProductById" resultMap="productBean"> select p.id,p.`name`,p.price from product_ p where p.cid = #{id} </select> </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.qfedu.dao.CategoryDAO"> <resultMap id="categoryBean" type="com.qfedu.pojo.Category"> <id column="id" property="id"/> <result column="name" property="name"/> <collection property="products" ofType="com.qfedu.pojo.Product" select="com.qfedu.dao.ProductDAO.queryProductById" column="id"> </collection> </resultMap> <select id="queryCategoryById" resultMap="categoryBean"> select c.id,c.`name` from category_ c where c.id=#{id} </select> </mapper>
package com.qfedu.dao; import com.qfedu.pojo.Product; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2022/1/1 18:09 * @Description */ public interface ProductDAO { /** * 根据id查询 * @param id * @return */ List<Product>queryProductById(Integer id); }
package com.qfedu.dao; import com.qfedu.pojo.Category; import java.util.List; /** * @author Helen * @version 1.0 * @createTime 2022/1/1 18:08 * @Description */ public interface CategoryDAO { /** * 根据id查询 * @param id * @return */ List<Category>queryCategoryById(Integer id); }
这篇关于SSM复习总结-MyBatis的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-28微服务架构中API版本控制的实践
- 2024-09-28AI给的和自己写的Python代码,都无法改变输入框的内容,替换也不行
- 2024-09-27Sentinel配置限流资料:新手入门教程
- 2024-09-27Sentinel配置限流资料详解
- 2024-09-27Sentinel限流资料:新手入门教程
- 2024-09-26Sentinel限流资料入门详解
- 2024-09-26Springboot框架资料:初学者入门教程
- 2024-09-26Springboot框架资料详解:新手入门教程
- 2024-09-26Springboot企业级开发资料:新手入门指南
- 2024-09-26SpringBoot企业级开发资料新手指南