Java教程
SSM复习总结-MyBatis
本文主要是介绍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的开发步骤
Struts2SpringMVC
- 持久层框架：完成数据库操作的框架
apache DBUtilsHibernateSpring JPAMyBatis
- 胶水框架：
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的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
怎么修改Kafka的JVM参数？-icode9专业技术文章分享12-24
-
线下车企门店如何实现线上线下融合？12-23
-
鸿蒙Next ArkTS编程规范总结12-23
-
物流团队冬至高效运转，哪款办公软件可助力风险评估？12-23
-
优化库存，提升效率：医药企业如何借助看板软件实现仓库智能化12-23
-
项目管理零负担！轻量化看板工具如何助力团队协作12-23
-
电商活动复盘，为何是团队成长的核心环节？12-23
-
鸿蒙Next ArkTS高性能编程实战12-23
-
数据驱动：电商复盘从基础到进阶！12-23
-
从数据到客户：跨境电商如何通过销售跟踪工具提升营销精准度？12-23
-
汽车4S店运营效率提升的核心工具12-23
-
ERP 系统开发案例：用 PERT 图打造高效项目管理12-23
-
初创团队必备！全面解析支持甘特图的项目管理工具与功能特点12-23
-
任务进度跟踪不再难！可视化项目协作工具精选12-23
-
看板工具为汽车销售门店协作赋能：关键方法12-23
栏目导航
