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的开发步骤
  1. Struts2
  2. SpringMVC
• 持久层框架：完成数据库操作的框架
  1. apache DBUtils
  2. Hibernate
  3. Spring JPA
  4. 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

        
        
            org.mybatis
            mybatis
            3.5.8
        
        
        
            mysql
            mysql-connector-java
            8.0.27
        
        
        
            org.projectlombok
            lombok
            1.18.22
            provided
        
         
            com.alibaba
            fastjson
            1.2.49
        
    

创建Mybatis配置文件

idea中创建mybatis配置文件的模板
选择 resources—右键 New – Edit file Templates

• 在resorces中创建名为mybatis-config.xml
• 在mybatis-config.xml文件中配置数据库连接信息

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;


@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中定义的方法进行实现：

    
        insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge})
    
    
        delete from tb_students where stu_num = #{stuNum}
    

将映射文件添加到主配置文件

单元测试 添加单元测试依赖

 
            junit
            junit
            4.12
            test
        

创建单元测试类

鼠标选中类，点击右键，选择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;


public class StudentDAOTest {

    @Test
    public void insertStudent(){
        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 i = studentDAO.insertStudent(new Student(0,"10001","张三","男",21));
            System.out.println(i);
            
            sqlSession.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void deleteStudent() {
        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.deleteStudent("10001");
            System.out.println("rows="+rows);
            sqlSession.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

MyBatis的CRUD操作 修改操作

StudentMapper.xml中内容

    
        update tb_students set stu_name = #{stuName},stu_gender = #{stuGender},stu_age = #{stuAge} where stu_num = #{stuNum}
    

StudentDAO

package com.qfedu.dao;
import com.qfedu.pojo.Student;
public interface StudentDAO {
    
    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中内容

    
    
        
        
        
        
        
    
    
    
        select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
    
    
    
    
        select sid	as stuId,stu_num as stuNum,stu_name as stuName,stu_gender as stuGender,stu_age as stuAge from tb_students
    
    

• StudentDAO

package com.qfedu.dao;

import com.qfedu.pojo.Student;
import java.util.List;
public interface StudentDAO {
    
    public ListlistStudents();
    
    public ListlistAll();
}

• 单元测试

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 {
            
            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);
            
            Liststudents=studentDAO.listAll();
            for (Student student:students){
                System.out.println(student);
            }
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}    

查询一条记录

根据学号查询一个学生信息

• 在StudentDAO接口中定义方法

public interface StudentDAO {
    
    public Student queryStudent(String stuNum);
}    

• 在StudentMapper.xml中配置StudentDAO接口的方法实现–SQL

    
        select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
        where stu_num = #{stuNum}
    
    
    
        
        
        
        
        
    
    
 
单元测试
 
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 {
            
            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 = studentDAO.queryStudent("10001");
            System.out.println(student);
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}    
 
查询总记录数 
在StudentDAO接口中定义方法
 
public interface StudentDAO {
    
    public int getCount();
}    
 
在StudentMapper.xml中配置sql,通过resultType指定当前操作的返回类型为int
 




    
        select count(1) from  tb_students
    
    

• 单元测试

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 {
            
            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 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中代码如下：

        select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
        where stu_num = #{stuNum}
    
    
 
分页查询（参数 start,pageSize) 
如果操作方法有一个Map类型的参数，在Mapper配置中可以直接通过#{key}获取
 StudentDAO中代码如下：
 
public interface StudentDAO {
public ListlistStudentsByPage(HashMap map);
}
 





        select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
        limit #{start},#{pageSize}
    
    

单元测试

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 {
            
            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);
            HashMap map=new HashMap<>();
            map.put("start",0);
            map.put("pageSize",2);
            Listlist=studentDAO.listStudentsByPage(map);
            for (Student student:list){
                System.out.println(student);
            }
            
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

@Param获取参数

• 在StudentDAO中定义操作方法，如果方法有多个参数，使用@Param注解声明参数的别名

public interface StudentDAO {
    public ListlistStudentsByPage2(@Param("start")Integer start,@Param("pageSize")Integer pageSize);
    }

• 在StudentMapper.xml中配置sql时，使用#{别名}获取到指定的参数

    
        select sid,stu_num,stu_name,stu_gender,stu_age from tb_students limit #{start},#{pageSize}
    
    
        select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
        limit #{start},#{pageSize}
    

arg0,arg0获取参数

注意：如果StudentDAO操作方法没有通过@Param指定参数别名，在SQL中也可以通过arg0,arg1...或者param1,param2...获取参数
在StudentDAO中指定方法

public interface StudentDAO {
 public ListlistStudentByParams(int start,int pageSize);
}

在StudentMapper.xml中代码如下：

  
        select sid,stu_num,stu_name,stu_gender,stu_age
        from tb_students
         limit #{arg0},#{arg1}
    
 
单元测试
 
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 {
            
            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);
            Listlist=studentDAO.listStudentByParams(0,2);
            for (Student student:list){
                System.out.println(student);
            }
            
        }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 ListlistStudentByParams(Page page);
}
 
在StudentMapper.xml中代码如下：
 




    
        select sid,stu_num,stu_name,stu_gender,stu_age
        from tb_students
                 limit #{start},#{pageSize}
    
        
        
        
        
        
        
    

• 单元测试

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;


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);
            Page page = new Page(0,2);
            Listlist=studentDAO.listStudentByParams2(page);
            for (Student student:list){
                System.out.println(student);
            }
            
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

添加操作回填生成主键

• StudentMapper.xml的添加操作标签-- insert

    
    
        insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge})
    

• 在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;


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;


public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;
    private static final ThreadLocallocal = 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 getMapper(Classc){
        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;


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;


public class StudentDAOTest {

    @Test
    public void listStudentsByPage() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        try {
            
            StudentDAO studentDAO = MyBatisUtil.getMapper(StudentDAO.class);
            Student student =new Student(0,"10002","李斯","男",21);
            studentDAO.insertStudent(student);
            System.out.println(student);
            
            sqlSession.commit();
        }catch (Exception e){
            
            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 ThreadLocallocal = 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){
            
            
            sqlSession=sqlSessionFactory.openSession(isAutoCommit);
            local.set(sqlSession);
        }
        return sqlSession;
    }

    
    public static SqlSession getSqlSession(){
       return getSqlSession(false);
    }
    public static  T getMapper(Classc){
        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

官网介绍

typeAliases标签

官网介绍

plugins

官网介绍

environments

映射文件 MyBatis初始化

mapper根标签

mapper文件相当于DAO接口的实现，namespace属性要指定实现DAO接口的全限定名

insert标签

声明添加操作(sql:insert…)
常用属性
id属性，绑定对应DAO接口中的方法
parameterType属性，用以指定接口中对应方法的参数类型(可省略)
useGeneratedKeys设置添加操作是否需要回填生成的主键，
keyProperty：设置回填的主键值赋值到参数对象的哪个属性
timeout属性，设置此操作的超时时间，如果不设置则一直等待
主键回填

 
        insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge})
    

主键回填第二种方式
select last_insert_id() 最新插入的id

 
        
            select last_insert_id()
        
        insert into tb_students values (#{stuId} , #{stuNum}, #{stuName}, #{stuGender}, #{stuAge})
    

单元测试

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 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

cache

设置当前DAO进行数据库操作时的缓存属性设置

sql和include

sql：sql片段
inlcude :引用sql片段

    
        sid,stu_num,stu_name,stu_gender,stu_age
    
    
        tb_students
    
    
         select  from 
    
 
分页插件 
 
 
分页插件是一个独立于MyBatis框架之外的第三方插件
 PageHelper
 
 
添加分页插件的依赖 
pagehelper分页插件
 
 
        
            com.github.pagehelper
            pagehelper
            5.3.0
        
 
配置插件 
在mybatis主配置文件mybatis-config.xml中通过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是已经分页后的数据
           Liststudents=studentDAO.listStudents();
           // pageInfo中就包含了数据及分页信息
           PageInfopageInfo=new PageInfo<>(students);
           Listlist=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;


@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;


@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;


public interface UsersDAO {
    
    public Users queryUser(String userName);
}

 
映射文件 
内连接查询
 
   
        
        
        
        
        
        
        
        
    
    
        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}
    

• 第二种方式

    users
    
        
        
        
        
        
        
            
            
            
        
    
    
        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}
    

 
单元测试 
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();
        }
    }
}
 
测试结果
 
 
使用子查询的方式 
使用子查询的方式查询一对一关联关系对应的数据
 实体类
 
package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;


@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;


@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
 



    users
    
        
        
        
        
        
        
        
        
    
    
        SELECT
            u.user_id,
            u.user_name,
            u.user_pwd,
            u.user_realname,
            u.user_img
        FROM
            users u
        where  u.user_name= #{userName}
    

UserDAO

package com.qfedu.dao;

import com.qfedu.pojo.Users;


public interface UsersDAO {
    
    public Users queryUser(String userName);
}

DetailsMapper.xml

    details
    
        
        
        
        
        
    
    
        select d.detail_id,d.user_id,d.user_addr,d.user_desc,d.user_tel
        from details d
        where d.user_id = #{userId}
    

 
package com.qfedu.dao;

import com.qfedu.pojo.Details;


public interface DetailsDAO {
    
    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;


@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;


@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    
    private int stuId;
    private String stuName;
    private int stuAge;
    
    private int studeCid;
}

 
关联查询 
 
 
查询一个班级的时候，要关联查询出这个班级下的所有学生
 
 
连接查询 
实体类
 
package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;


@ToString
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Clazz {
    private int classId;
    private String className;
    private String classDesc;
    
    Liststudents;
}

 
package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;


@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    
    private int stuId;
    private String stuName;
    private int stuAge;
    
    private int stuCid;
}

 
classMapper.xml
 



    
        
        
        
        
        
            
            
            
            
        
    
    
        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}
    

ClassDAO

package com.qfedu.dao;

import com.qfedu.pojo.Clazz;


public interface ClassDAO {
    
    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.*;


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

    
        
        
        
        
        
    
    
        select c.cid,
               c.cname,
               c.cdesc
        from classess c
        where c.cid = #{classId}
    

 
classDAO
 
package com.qfedu.dao;

import com.qfedu.pojo.Clazz;


public interface ClassDAO {
    
    public Clazz queryClass(int classId);
}

 
StudentMapper.xml
 



    
        
        
        
        
    
    
        select s.scid,s.sage,s.sid,s.sname
        from  students s
        where s.scid =#{cid}
    

StudentDAO

package com.qfedu.dao;

import com.qfedu.pojo.Student;

import java.util.List;


public interface StudentDAO {
    public ListlistStudentsByCid(int cid);
}

查询结果

多对一关联

实例：学生(n)-班级(1)
当查询一个学生的时候，关联查询这个学生所在的班级信息

连接查询

StudentMapper.xml

    
        
        
        
        
        
        
        
    
    
        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}
    

 
StudentDAO
 
package com.qfedu.dao;

import com.qfedu.pojo.Student;


public interface StudentDAO {
    public Student queryStudentBySid(String sid);
}

 
实体类
 
package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;


@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    
    private int stuId;
    private String stuName;
    private int stuAge;
    
    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;


@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.*;


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
 



    
        
        
        
        
        
    
    
        select s.scid,s.sage,s.sid,s.sname
        from students s
        where s.sid =#{sid}
    

    
        
        
        
    
    
        select c.cid,
               c.cname,
               c.cdesc
        from classess c
        where c.cid = #{classId}
    

 
修改StudentDAO和ClassDAO
 
package com.qfedu.dao;

import com.qfedu.pojo.Student;


public interface StudentDAO {
    public Student queryStudentBySid(String sid);
}

 
package com.qfedu.dao;

import com.qfedu.pojo.Clazz;


public interface ClassDAO {
    
    public Clazz queryClass(int classId);
}

 
实体类
 
package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;


@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;


@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    
    private int stuId;
    private String stuName;
    private int stuAge;
    
    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.*;


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;


@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
    
    private int stuId;
    private String stuName;
    private int stuAge;
    
    private int stuCid;
    
    private List courses;
}

 
package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;


@ToString
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Course {
    private int courseId;
    private String courseName;
}

 
子查询配置
 



    
        
        
        
    
    
        select course_id, course_name
        from courses
        where course_id = #{courseId}
    

package com.qfedu.dao;

import com.qfedu.pojo.Course;

import java.util.List;


public interface CourseDAO {
    
    public ListqueryCourseById(int courseId);
}

    
        
        
        
    
    
    
        select s.sid,
               s.sname,
               s.sage
        from students s
                 INNER JOIN grade g on s.sid = g.sid
        where g.cid = #{courseId}
    

 
package com.qfedu.dao;

import com.qfedu.pojo.Student;

import java.util.List;


public interface StudentDAO {
    
    public ListqueryStudentByCourseId(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;


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 = 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;


@ToString
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Course {
    private int courseId;
    private String courseName;
    private List students;
}

 
package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;


@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
    
    private int stuId;
    private String stuName;
    private int stuAge;
    
    private int stuCid;
    
    private List courses;
}

 



    
        
        
        
            
            
            
            
        
    
    
        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}
    

CourseDAO

package com.qfedu.dao;

import com.qfedu.pojo.Course;

import java.util.List;


public interface CourseDAO {
    
    public List 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;


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 = 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;


@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;


@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;


public interface MemberDAO {
    
    public List queryMember(HashMapparams);

    
//    public List searchMember(MemberSearchCondition params);
}

    
        
        
        
        
        
    
    
    
        select
        m.member_id,
        m.member_nick,
        m.member_gender,
        m.member_age,
        m.member_city
        from members m
        where 1=1



        
            and m.member_gender = #{gender}
        
        
            and m.member_age ">>= #{minAge}
        
        
            and m.member_age <= #{maxAge}
        
        
            and m.member_city = #{city}
        
    

 
单元测试
 
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;


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 hashMap = new HashMap<>();
            hashMap.put("gender","女");
            hashMap.put("minAge",18);
            hashMap.put("maxAge",22);
            MemberDAO dao = sqlSession.getMapper(MemberDAO.class);
            Listmembers=dao.queryMember(hashMap);
            String result = JSON.toJSONString(members);
            System.out.println(result);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
 
测试结果
 
 
where标签使用 
代替where 1=1
 



    
        
        
        
        
        
    
    
        select
        m.member_id,
        m.member_nick,
        m.member_gender,
        m.member_age,
        m.member_city
        from members m
        
            
                and m.member_gender = #{gender}
            
            
                and m.member_age ">>= #{minAge}
            
            
                and m.member_age <= #{maxAge}
            
            
                and m.member_city = #{city}
            
        

    

trim

    
        
        
        
        
        
    
    
    
        select
        m.member_id,
        m.member_nick,
        m.member_gender,
        m.member_age,
        m.member_city
        from members m
        
            
                and m.member_gender = #{gender}
            
            
                and m.member_age ">>= #{minAge}
            
            
                and m.member_age <= #{maxAge}
            
            
                and m.member_city = #{city}
            
        
    
    

 
foreach 



    
        
        
        
        
        
    
    
    
        select
        m.member_id,
        m.member_nick,
        m.member_gender,
        m.member_age,
        m.member_city
        from members m
        
            and m.member_city in
            
                #{cityName}
            
        
    

 
package com.qfedu.dao;

import com.qfedu.pojo.Member;
import java.util.List;


public interface MemberDAO {
    
    public ListsearchMemberByCity(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;


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={"武汉","宜昌"};
            Listmembers=dao.searchMemberByCity(str);
            String result = JSON.toJSONString(members);
            System.out.println(result);
            members.forEach(System.out::println);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
 
传List集合类型参数 



    
        
        
        
        
        
    
    
    
        select
        m.member_id,
        m.member_nick,
        m.member_gender,
        m.member_age,
        m.member_city
        from members m
        
            and m.member_city in
            
                #{cityName}
            
        
    

package com.qfedu.dao;

import com.qfedu.pojo.Member;
import java.util.List;


public interface MemberDAO {
    
    public ListsearchMemberByCity(Listcities);
}

单元测试

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;


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={"武汉","宜昌"};
            Listcities=new ArrayList<>(Arrays.asList(str));
            Listmembers=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运行过程中的 日志信息。

添加日志框架依赖

         
            log4j
            log4j
            1.2.17
        

添加日志配置文件

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依赖

        
            com.alibaba
            druid
            1.2.8
        

创建Druid连接池工厂

package com.qfedu.utils;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory;



public class DruidDataSourceFactory extends PooledDataSourceFactory {
    public DruidDataSourceFactory(){
        this.dataSource = new DruidDataSource();
    }
}

将DruidDataSourceFactory 配置给MyBatis数据源

mybatis-config.xml

单元测试

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;


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={"武汉","宜昌"};
            Listcities=new ArrayList<>(Arrays.asList(str));
            Listmembers=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;


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;


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;


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是单例的，因此我们二级缓存可以实现全局共享
特性：

1. 二级缓存默认没有开启，需要在mybatis-config.xml中的settings标签开启
2. 二级缓存只能缓存

在mybatis.xml中开启二级缓存

• 在需要使用二级缓存的Maper.xml文件中配置cache标签使用功能二级缓存

    
    
        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
        m.member_id,
        m.member_nick,
        m.member_gender,
        m.member_age,
        m.member_city
        from members m
        
            and m.member_city in
            
                #{cityName}
            
        
    

被缓存的实体类实现序列化接口

package com.qfedu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.io.Serializable;


@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;


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();
            System.out.println("================================");
            SqlSession sqlSession1 = factory.openSession();
            MemberDAO dao1 = sqlSession1.getMapper(MemberDAO.class);
            Member member2=dao1.queryById(1);
            
            System.out.println(member2);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}    

查询操作的缓存开关

 
    
    
        select
            m.member_id,
            m.member_nick,
            m.member_gender,
            m.member_age,
            m.member_city
        from members m
        where m.member_id=#{memberId}
    
 
 
延迟加载 
延迟加载–如果在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;


@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;


@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Category implements Serializable {
    private int id;
    private String name;
    List products;
}

 
创建DAO接口 
 



    
        
        
        
    
    
        select p.id,p.`name`,p.price
        from product_ p where p.cid = #{id}
    

    
        
        
        
        
    
    
        select c.id,c.`name`
        from category_ c where c.id=#{id}
    

package com.qfedu.dao;

import com.qfedu.pojo.Product;

import java.util.List;


public interface ProductDAO {
    
     ListqueryProductById(Integer id);
}

package com.qfedu.dao;

import com.qfedu.pojo.Category;

import java.util.List;


public interface CategoryDAO {
    
    ListqueryCategoryById(Integer id);
}