MyBatis简讲

简介

• MyBatis3前身是iBatis，本是Apache的一个开源的项目现在开源，代码放在gitjib平台商进行管理

网站

官网：https://mybatis.org/mybatis-3/

中文网：https://mybatis.net.cn/

持久层ORM框架

实体类和SQL语句之间建立映射关系

ORM(Object Relational Mapping)

• 编写程序的时候，以面向对象的方式处理数据
• 保存数据的时候却以关系型数据库的方式储存

ORM解决方案包含下面四个部分

• 在对持久化对象提供一种查询语言或者API
• 持久化对象上执行基本的增、删、改、查操作
• 对象关系映射工具
• 提供与事务对象交互、执行检查、延迟加载以及其他优化功能

特点

基于SQL语法，简单易学
能了解底层封装过程
SQL语句封装在配置文件中，便于统一管理与维护，降低程序的耦合

与JDBC的区别

搭建MyBatis开发环境

下载mybatis-3.2.2.jar包并导入工程

<dependencies>
<!--        mybatis依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.0</version>
        </dependency>
<!--        mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <dependency>
            <groupId>org.hamcrest</groupId>
            <artifactId>hamcrest-core</artifactId>
            <version>1.3</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

使用MyBatis的开发步骤

编写Mybatis核心配置文件(configuration.xml)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/ytzl/mybatis/mapper/StudentMapper.xml"/>
    </mappers>

</configuration>

创建实体类-POJO

package com.ytzl.mybatis.pojo;

import java.util.Date;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/24 14:08
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public class Student {
    /**id*/
    private Integer id;
    /**姓名*/
    private String stuName;
    /**地址*/
    private String address;
    /**出生日期*/
    private Date birthday;
    /**教师id*/
    private Integer tid;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", stuName='" + stuName + '\'' +
                ", address='" + address + '\'' +
                ", birthday=" + birthday +
                ", tid=" + tid +
                '}';
    }
}

DAO层-SQL映射文件(mapper.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="org.mybatis.example.BlogMapper">
    <!--resultType=ctrl+shift+alt+c-->
    <select id="selectStudent" resultType="com.ytzl.mybatis.pojo.Student">
        select * from student where id = #{id}
    </select>
</mapper>

创建测试类

package com.ytzl.mybatis.test;


import com.ytzl.mybatis.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 玫瑰到了花期
 * @data 2022/4/24 14:27
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public class JunitTest {
    @Test
    public void testSelectStudent() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        System.out.println(sqlSessionFactory);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        Student stu = sqlSession.selectOne("org.mybatis.example.BlogMapper.selectStudent", 1);
        System.out.println(stu);
    }
}

读取核心配置文件mybatis-config.xml

String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
System.out.println(sqlSessionFactory);

创建SqlSessionFactory对象，读取配置文件

SqlSession sqlSession = sqlSessionFactory.openSession();

创建SqlSession对象

Student stu = sqlSession.selectOne("org.mybatis.example.BlogMapper.selectStudent", 1);

调用mapper文件进行数据操作

System.out.println(stu);

MyBatis使用接口开发

视图

文件目录

mapper

StudentMapper

package mybatis.mapper;

import mybatis.pojo.Student;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/24 15:57
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public interface StudentMapper {
    /**根据学生id查询学生对象*/
    Student selectStudent(Integer id);
}

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="mybatis.mapper.StudentMapper">
    <!--resultType=ctrl+shift+alt+c-->
    <select id="selectStudent" resultType="mybatis.pojo.Student">
        select * from student where id = #{id}
    </select>
</mapper>

pojo

Student

package mybatis.pojo;

import java.util.Date;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/24 14:08
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public class Student {
    /**id*/
    private Integer id;
    /**姓名*/
    private String stuName;
    /**地址*/
    private String address;
    /**出生日期*/
    private Date birthday;
    /**教师id*/
    private Integer tid;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", stuName='" + stuName + '\'' +
                ", address='" + address + '\'' +
                ", birthday=" + birthday +
                ", tid=" + tid +
                '}';
    }
}

Test

JunTest

package mybatis.test;


import mybatis.mapper.StudentMapper;
import mybatis.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 玫瑰到了花期
 * @data 2022/4/24 14:27
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public class JunitTest {
    @Test
    public void testSelectStudentMapper() {
        //核心配置文件的路径
        String url = "mybatis-config.xml";
        SqlSession session = null;
        try {
            //读取流信息
            InputStream inputStream = Resources.getResourceAsStream(url);
            //创建sqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            //sqlSession一次数据库会话（包含了操作数据库的各种方法）
            session = sqlSessionFactory.openSession();
            //得到StudentMapper的代理对象
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
            System.out.println(studentMapper);
            //调用查询方法
            Student student = studentMapper.selectStudent(1);
            System.out.println(student);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (session!=null){
                session.close();
            }
        }
    }
}

MyBatis核心对象

核心接口和类的结构

SqlSessionFactoryBuilder

用过即丢，其生命周期只存在于方法内
可重用起来创建多个SqlSessionFactory实例
负责构建SqlSessionFactory，并提供多个build方法的重载

SqlSessionFactory

SqlSessionFactory是每个MyBatis应用的核心
作用：创建SqlSession实例
SqlSession session = sqlSessionFactory.openSession(boolean autoCommt); //false true(默认)开启/关闭事务控制
作用域：Application
生命周期与应用的生命周期相同
单例：
存在于整个应用运行时，并且同时只存在一个对象实例

MybatisUtil

package mybatis.util;

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;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/24 16:47
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public class MybatisUtil {
    //核心对象
    private static SqlSessionFactory sqlSessionFactory;

    static {
        String url = "mybatis-config.xml";
        //读取流信息
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(url);

        } catch (IOException e) {
            e.printStackTrace();
        }
        //创建sqlSessionFactory对象
         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    //获取数据库会话对象
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
    //关闭数据库会话
    public static void closeSession(SqlSession session){
        session.close();
    }
}

测试

@Test
public void testUtil() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    System.out.println(mapper);
    //调用查询方法
    Student student = mapper.selectStudent(1);
    System.out.println(student);
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

SqlSession

包含了执行SQL所需的所有方法
对应一次数据库会话，会话结束必须关闭
线程级别，不能共享

SqlSession的获取方式

String resource = "mybatis-config.xml";
InputStream is = Resoutces.getResourceAsStream(resource)
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();

SqlSession的两种使用方式

通过SqlSession实例直接运行映射的SQL语句

基于Mapper接口方式操作数据

小结

核心配置文件

目标

理解核心配置文件中的各项参数配置
掌握通过sQL映射文件进行增删改查

系统核心配置文件

configuration配置
properties	可以配置在Java属性配置文件中
settings	修改MyBatis在运行时的行为方式
typeAliases	为Java类型命命名一个别名（简称）
typeHandlers	类型处理器
objectFactory	对象工厂
plugins	插件
environments	环境
environment	环境变量
|transactionManager	事务管理器
|dataSource	数据源
mappers	映射器

properties

peoperties	通过property标签添加属性
name	属性名
value	属性值
resource	resource=“data.properties”

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>
<!--
    //第一种方式：通过property标签添加属性
    property:设置属性
        name:属性名
        value:属性值

    //第二种：properties标签的resource属性读取属性配置文件※

    //第三种：通过java代码读取配置文件
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, props);
    优先级：3  >  2  >  1
    dataSource获取方式通过键名 ==>${driver}


-->
    <properties resource="data.properties">
<!--        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
        <property name="name" value="root"/>
        <property name="pwd" value="123456"/>-->
    </properties>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mybatis/mapper/StudentMapper.xml"/>
    </mappers>

</configuration>

data.properties

driver:com.mysql.jdbc.Driver
url:jdbc:mysql://localhost:3306/mybatis
name:root
pwd:123456

settings

这是 MyBatis 中极为重要的调整设置，它们会改变 MyBatis 的运行时行为

https://mybatis.net.cn/configuration.html#properties

    <settings>
<!--        设置日志打印方式-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
<!--        开启驼峰命名自动转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

typeAliases

typeAlias

typeAlias标签	单个类配置
alias属性	可以省略，默认是类名的首字母小写，不区分大小写

package

package标签	包路径下统一配置，也可以通过@Alias注解手动配置
name	包的路径
@Alias注解	默认是类名的首字母小写，不区分大小写

    <typeAliases>
<!--        <typeAlias type="mybatis.pojo.Student" alias="student"/>-->
        <package name="mybatis.pojo"/>

typeHandler

typeHandler标签	类型处理器
handler	处理器的完全路径名
jdbcType	数据库类型
javaType	类型

<!--
    类型处理器
        handler：处理器的完全限定名
        jdbcType：数据库类型
        javaType：类型
-->
    <typeHandlers 
        <typeHandler handler="mybatis.util.MyTypeHandler" jdbcType="INTEGER" javaType="String"/>
    </typeHandlers>

MyTypeHandler

package mybatis.util;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/25 13:40
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
/**自定义类型处理器 int <==> String*/
public class MyTypeHandler implements TypeHandler<String> {
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {
        int f = 1;
        if (s.equals("女")){
            f = 0;
        }
        preparedStatement.setInt(i,f);
    }

    @Override
    public String getResult(ResultSet resultSet, String s) throws SQLException {
        System.out.println("String ==>"+s);
        return resultSet.getInt(s)==0?"女":"男";
    }

    @Override
    public String getResult(ResultSet resultSet, int i) throws SQLException {
        System.out.println("int ==>"+i);
        return resultSet.getInt(i)==0?"女":"男";
    }

    @Override
    public String getResult(CallableStatement callableStatement, int i) throws SQLException {
        System.out.println("callableStatement  ==>"+i);
        return callableStatement.getInt(i)==0?"女":"男";
    }
}

objectFactory&plugins

environments

transactionManager

mappers

<!--1.使用相对于类路径的资源引用-->
<mapper resource="mybatis/mapper/StudentMapper.xml"/>
<!--2.通过完全限定资源定位符-->
<mapper url="file:///F:/JavaWorkSpace/MyBatis/MyBatis_4/src/main/java/mybatis/mapper/StudentMapper.xml "></mapper>
<!--3.使用接口类的完全限定类名-->
<mapper class="mybatis.mapper.StudentMapper"></mapper>
<!--4.将包内的映射器接口实现全部注册为映射器-->
<package name="mybatis.mapper"/>

SQL映射的XML文件

MyBatis真正的强大在于映射语句!专注于SQL，功能强大，SQL映射的配置却是相当简单

select

StudentMapper

List<Student> selectAll();

mapper

<select id="selectAll" resultType="Student">
    select * from student
</select>

Test

@Test
public void testSelectAll(){
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    System.out.println(mapper);
    //调用查询方法
    List<Student> students = mapper.selectAll();
    for (Student student:students){
        System.out.println(student);
    }
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

insert

StudentMapper

int addStudent(Student student);

MyTypeHandler

package mybatis.util;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/25 13:40
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
/**自定义类型处理器 int <==> String*/
public class MyTypeHandler implements TypeHandler<String> {
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {
        int f = 1;
        if (s.equals("女")){
            f = 0;
        }
        preparedStatement.setInt(i,f);
    }

    @Override
    public String getResult(ResultSet resultSet, String s) throws SQLException {
        System.out.println("String ==>"+s);
        return resultSet.getInt(s)==0?"女":"男";
    }

    @Override
    public String getResult(ResultSet resultSet, int i) throws SQLException {
        System.out.println("int ==>"+i);
        return resultSet.getInt(i)==0?"女":"男";
    }

    @Override
    public String getResult(CallableStatement callableStatement, int i) throws SQLException {
        System.out.println("callableStatement  ==>"+i);
        return callableStatement.getInt(i)==0?"女":"男";
    }
}		

mybatis-config.xml

    <typeAliases>
<!--        <typeAlias type="mybatis.pojo.Student" alias="student"/>-->
        <typeAlias type="mybatis.util.MyTypeHandler" alias="MyTypeHandler"/>
        <package name="mybatis.pojo"/>
    </typeAliases>

mapper

    <insert id="addStudent" parameterType="Student">
        insert into student values  (0,#{stuName},#{tid},#{address},#{birthday},#{sex,javaType=String,jdbcType=INTEGER,typeHandler=MyTypeHandler})
    </insert>

Test

@Test
public void testAddStudent() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    //调用添加方法
    int addStudent = mapper.addStudent(new Student("任俊楠", "413", new Date(), 45, "男"));
    sqlSession.commit();
    System.out.println(addStudent > 0 ? "增加成功" : "增加失败");
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

updata

StudentMapper

int updateStudent(@Param("id") int id,@Param("name") String name);

mapper

<update id="updateStudent">
    update student set stu_name = #{name} where id = #{id}
</update>

Test

@Test
public void testUpdateStudent() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    //调用修改方法
    int newName = mapper.updateStudent(1, "葛霄");
    System.out.println("newName==>"+newName);
    sqlSession.commit();
    System.out.println(newName > 0 ? "修改成功" : "修改失败");
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

delete

StudentMapper

int deleteStudent(int id);

mapper

<delete id="deleteStudent" parameterType="int">
    delete from student where id = #{id}
</delete>

Test

@Test
public void testDeleteStudent() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    //调用删除方法
    int del = mapper.deleteStudent(1);
    sqlSession.commit();
    System.out.println(del > 0 ? "删除成功" : "删除失败");
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

MyBatis参数使用和量表联查

基础数据类型

单个参数

StudentMapper.xml

<select id="selectByName" parameterType="string" resultType="student">
    select * from student where stu_name like concat('%',#{name},'%')
</select>

StudentMapper

List<Student> selectByName(String name);

Test

@Test
public void testSelectByName() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
        List<Student> student = mapper.selectByName("任");
        System.out.println(student);

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

多个参数

StudentMapper.xml

<!--
    多参数参数获取
        方式一：param1,param2......
        方式二：arg0,arg1......
-->
<select id="selectByNameAndSex" resultType="student">
    select * from student where stu_name like concat('%',#{name},'%') and sex = #{sex,javaType=String,jdbcType=INTEGER,typeHandler=MyTypeHandler}
</select>

StudentMapper

Student selectByNameAndSex(@Param("name") String name,@Param("sex") String sex);

Test

@Test
public void testSelectByNameAndSex() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
    List<Student> student = mapper.selectByName("任");
    System.out.println(student);
    Student student1 = mapper.selectByNameAndSex("任","男");
    System.out.println(student1);


    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

复杂数据类型

数组

StudentMapper

int deleteByIds(int[] ids);

StudentMapper.xml

<delete id="deleteByIds" parameterType="int">
    delete from student where id in
    <foreach collection="array" open="(" close=")" item="id" separator=",">
        #{id}
    </foreach>
</delete>

Test

@Test
public void testDelectByIds() {
        //获取数据库连接
        SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
    int[] ids = {1,2};
    int i = mapper.deleteByIds(ids);
    System.out.println(i);
    sqlSession.commit();

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

List

StudentMapper

int deleteByList(List<Integer> ids);

StudentMapper.xml

<delete id="deleteByList" parameterType="int">
    delete from student where id in
    <foreach collection="list" open="(" close=")" item="id" separator=",">
        #{id}
    </foreach>
</delete>

Test

@Test
public void testDelectList() {
        //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
    List<Integer> list = new ArrayList<>();
    list.add(12);
    int i = mapper.deleteByList(list);
    System.out.println(i);
    sqlSession.commit();

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

Map ***

StudentMapper

/**查询学生：name 任、age 16~20、address 413*/
List<Student> selectStuByCondition(Map<String,Object> map);

StudentMapper.xml

<select id="selectStuByCondition" parameterType="map" resultType="student">
    select * from student s
    where stu_name like concat('%',#{name},'%')
    and TIMESTAMPDIFF(YEAR , s.birthday,now()) &gt;=#{minAge}
    and TIMESTAMPDIFF(YEAR , s.birthday,now()) &lt;=#{maxAge}
    and (address = #{address1} or address = #{address2})
</select>

Test

@Test
public void testSelectMap() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
    //构建map
    Map<String,Object> map = new HashMap<>();
    map.put("name","任");
    map.put("minAge","16");
    map.put("maxAge","20");
    map.put("address1","413");
    map.put("address2","416");

    List<Student> students = mapper.selectStuByCondition(map);
    System.out.println(students);
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

MyBatis中$和#的区别

两者都可以从Mybatis中取出参数
使用场景
一般情况下使用’#’
当数据库表名或字段名需要动态入参的时候需要用"$"
例如：
order by${birthday}根据出生日期排序

	解析方式	防止SQL注入	使用场景
“#”	默认会加上引号，如：	是	一般情况使用
	order by #{id},如果id值为1则解析为order by"1"
“$” |	默认不会加上引号 如：	否	${}方式一般用于传入数据库对象，
	order by #{id},如果id值为1则解析为order by id		例如传入表名、列名

StudentMapper

List<Student> selectStudentOrder(String order);

StudentMapper.xml

<select id="selectStudentOrder" parameterType="string" resultType="Student">
    select * from student order by ${_parameter}
</select>

Test

@Test
public void testOrder() {
        //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    
    List<Student> birthday = mapper.selectStudentOrder("birthday");
    System.out.println(birthday);

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

结果集映射

使用resultMap如何实现自由灵活的控制映射结果
resultMap自动映射匹配前提：字段名与属性名一致
resultMap的自动映射级别–>autoMappingBehaior
PARTIAL(默认)：自动匹配所有

属性	说明
property	实体类的字段
column	数据库里的字段

StudentResultMapper

/**查询所有学生*/
List<Student> selectStudent();

StudentResultMapper.xml

    <resultMap id="StudentResult" type="Student">
        <id column="id" property="id"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="address" property="address"></result>
    </resultMap>
    <select id="selectStudent" resultMap="StudentResult">
        select * from student
    </select>

Test

@Test
public void testResult() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentResultMapper resultMapper = sqlSession.getMapper(StudentResultMapper.class);
    List<Student> students = resultMapper.selectStudent();
    for (Student stu:students){
        System.out.println(stu);
    }
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

关联映射一对一

StudentResultMapper

/**查询所有学生*/
List<Student> selectStudent();
/**查询所有学生包含老师信息*/
List<Student> selectStudentAndTeacher();

StudentResultMapper.xml

<resultMap id="StudentAndTeacherResult" type="Student">
    <id column="id" property="id"></id>
    <result column="stu_name" property="stuName"></result>
    <result column="address" property="address"></result>
    <association property="teacher" javaType="teacher">
        <id column="id" property="id"></id>
        <result column="tName" property="name"></result>
        <result column="tel" property="tel"></result>
        <result column="age" property="age"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
    </association>
</resultMap>
<select id="selectStudentAndTeacher" resultMap="StudentAndTeacherResult">
    select * from student s, teacher t
    where s.tid = t.id
</select>

Student

package mybatis.pojo;

import org.apache.ibatis.type.Alias;

import java.util.Date;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/24 14:08
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
@Alias("Student")
public class Student {
    /**id*/
    private Integer id;
    /**姓名*/
    private String stuName;
    /**地址*/
    private String address;
    /**出生日期*/
    private Date birthday;
    /**教师id*/
    private Integer tid;
    /**性别*/
    private String sex;
    public Teacher teacher;

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    public Student(Integer id, String stuName, String address, Date birthday, Integer tid, String sex) {
        this.id = id;
        this.stuName = stuName;
        this.address = address;
        this.birthday = birthday;
        this.tid = tid;
        this.sex = sex;
    }

    public Student(String stuName, String address, Date birthday, Integer tid, String sex) {
        this.stuName = stuName;
        this.address = address;
        this.birthday = birthday;
        this.tid = tid;
        this.sex = sex;
    }
    public Student(String stuName, String address, Date birthday, Integer tid) {
        this.stuName = stuName;
        this.address = address;
        this.birthday = birthday;
        this.tid = tid;

    }


    public Student() {
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", stuName='" + stuName + '\'' +
                ", address='" + address + '\'' +
                ", birthday=" + birthday +
                ", tid=" + tid +
                ", sex='" + sex + '\'' +
                '}';
    }
}

Teacher

package mybatis.pojo;


/**
 * @author 玫瑰到了花期
 * @data 2022/4/27 1:46
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public class Teacher {
    /**id*/
    private Integer id;
    /**姓名*/
    private String name;
    /**电话*/
    private String tel;
    /**年龄*/
    private Integer age;
    /**性别*/
    private String sex;
    /**地址*/
    private Integer address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAddress() {
        return address;
    }

    public void setAddress(Integer address) {
        this.address = address;
    }

    public Teacher(Integer id, String name, String tel, Integer age, String sex, Integer address) {
        this.id = id;
        this.name = name;
        this.tel = tel;
        this.age = age;
        this.sex = sex;
        this.address = address;
    }
}

Test

@Test
public void testStuAdnTea() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentResultMapper resultMapper = sqlSession.getMapper(StudentResultMapper.class);
    List<Student> students = resultMapper.selectStudentAndTeacher();
    for (Student stu:students){
        System.out.println(stu);
    }
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

关联映射分步查询

StudentResultMapper

package mybatis.mapper;

import mybatis.pojo.Student;

import java.util.List;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/26 9:51
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
/*
* 结果集相关内容
* */
public interface StudentResultMapper {
    /**查询所有学生*/
    List<Student> selectStudent();
    /**查询所有学生包含老师信息*/
    List<Student> selectStudentAndTeacher();
    List<Student> selectStudentAndTeacherTwo();
}

TeacherMapper

package mybatis.mapper;

import mybatis.pojo.Teacher;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/28 0:07
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public interface TeacherMapper {
    /**根据老师id查找老师*/
    Teacher selectByTid(Integer tid);
}

StudentResultMapper.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="mybatis.mapper.StudentResultMapper">
    <!--resultType=ctrl+shift+alt+c-->
<!--    结果集映射-->
    <resultMap id="StudentResult" type="Student">
        <id column="id" property="id"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="address" property="address"></result>
    </resultMap>
    <select id="selectStudent" resultMap="StudentResult">
        select * from student
    </select>
<!--    关联映射一对一-->
<!--    <resultMap id="StudentAndTeacherResult" type="Student">
        <id column="id" property="id"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="address" property="address"></result>
        <association property="teacher" javaType="teacher">
            <id column="id" property="id"></id>
            <result column="tName" property="name"></result>
            <result column="tel" property="tel"></result>
            <result column="age" property="age"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
    <select id="selectStudentAndTeacher" resultMap="StudentAndTeacherResult">
        select * from student s, teacher t
        where s.tid = t.id
    </select>-->
<!--    关联映射分步查询-->
<!--     分步查询多列传值和延迟加载-->
    <resultMap id="StudentAndTeacherResult" type="Student">
        <id column="id" property="id"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="address" property="address"></result>
<!--        <association property="teacher" javaType="teacher" resultMap="TeacherResult"></association>-->
        <association property="teacher" select="mybatis.mapper.TeacherMapper.selectByTid" column="tid"></association>
    </resultMap>
    <resultMap id="TeacherResult" type="Teacher">
        <id column="id" property="id"></id>
        <result column="tName" property="name"></result>
        <result column="tel" property="tel"></result>
        <result column="age" property="age"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
    </resultMap>
<!--
    1.查学生
    2.根据学生里的tid查询老师
    3.建立关系
-->
    <select id="selectStudentAndTeacherTwo" resultMap="StudentAndTeacherResult">
/*        select * from student s, teacher t
        where s.tid = t.id*/
        select * from student
    </select>
</mapper>

TeacherMapper.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="mybatis.mapper.TeacherMapper">
    <!--resultType=ctrl+shift+alt+c-->
    <select id="selectByTid" parameterType="int" resultType="Teacher">
        select * from teacher where id = #{tid}
    </select>
</mapper>

Test

@Test
public void testSelectTeaResult() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    Teacher teacher = mapper.selectByTid(1);
    System.out.println(teacher);

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}
@Test
public void testStuAdnTeaTwo() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentResultMapper resultMapper = sqlSession.getMapper(StudentResultMapper.class);
    List<Student> students = resultMapper.selectStudentAndTeacherTwo();
    for (Student stu:students){
        System.out.println(stu);
    }
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

分步查询多列传值和延迟加载

StudentResultMapper

package mybatis.mapper;

import mybatis.pojo.Student;

import java.util.List;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/26 9:51
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
/*
* 结果集相关内容
* */
public interface StudentResultMapper {
    /**查询所有学生*/
    List<Student> selectStudent();
    /**查询所有学生包含老师信息*/
    List<Student> selectStudentAndTeacher();
    List<Student> selectStudentAndTeacherTwo();
}

StudentResultMapper.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="mybatis.mapper.StudentResultMapper">
    <!--resultType=ctrl+shift+alt+c-->
<!--    结果集映射-->
    <resultMap id="StudentResult" type="Student">
        <id column="id" property="id"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="address" property="address"></result>
    </resultMap>
    <select id="selectStudent" resultMap="StudentResult">
        select * from student
    </select>
<!--    关联映射分步查询-->
<!--     分步查询多列传值和延迟加载-->
    <resultMap id="StudentAndTeacherResult" type="Student">
        <id column="id" property="id"></id>
        <result column="stu_name" property="stuName"></result>
        <result column="address" property="address"></result>
<!--        <association property="teacher" javaType="teacher" resultMap="TeacherResult"></association>-->
<!--
     fetchType="eager" 配置局部延迟加载加载方式
        lazy延迟加载
        eager立即查询
-->
        <association property="teacher" select="mybatis.mapper.TeacherMapper.selectByTid" column="{tid=tid}"></association>
    </resultMap>
    <resultMap id="TeacherResult" type="Teacher">
        <id column="id" property="id"></id>
        <result column="tName" property="name"></result>
        <result column="tel" property="tel"></result>
        <result column="age" property="age"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
    </resultMap>
<!--
    1.查学生
    2.根据学生里的tid查询老师
    3.建立关系
-->
    <select id="selectStudentAndTeacherTwo" resultMap="StudentAndTeacherResult">
/*        select * from student s, teacher t
        where s.tid = t.id*/
        select * from student
    </select>
</mapper>

TeacherMapper

package mybatis.mapper;

import mybatis.pojo.Teacher;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/28 0:07
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public interface TeacherMapper {
    /**根据老师id查找老师*/
    Teacher selectByTid(Integer tid);
}

TeacherMapper.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="mybatis.mapper.TeacherMapper">
    <!--resultType=ctrl+shift+alt+c-->
    <select id="selectByTid"  resultType="Teacher">
        select * from teacher where id = #{tid}
    </select>
</mapper>

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="data.properties">
    </properties>
<!--    设置Mybaits的行为-->
    <settings>
<!--        设置日志打印方式-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
<!--        开启驼峰命名自动转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
<!--
        映射级别：
            NONE:取消自动配置
            PARTIAL:自动映射（没有嵌套的时候）
            FULL:任意复杂查询都会自动映射
-->
        <setting name="autoMappingBehavior" value="FULL"/>
<!--
        开启全局懒加载
-->
        <setting name="lazyLoadingEnabled" value="true"/>
<!--
        加载所有属性
-->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <typeAliases>
        <typeAlias type="mybatis.util.MyTypeHandler" alias="MyTypeHandler"/>
        <package name="mybatis.pojo"/>
    </typeAliases>
    <typeHandlers>
        <typeHandler handler="mybatis.util.MyTypeHandler" jdbcType="INTEGER" javaType="String"/>
    </typeHandlers>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="mybatis.mapper"/>
    </mappers>
</configuration>

关联查询一对多

StudentResultMapper

/**根据老师id查询学生*/
List<Student> selectByTid(Integer tid);

StudentResultMapper.xml

<select id="selectByTid" parameterType="int" resultType="Student">
    select * from student where tid  = #{tid}
</select>

TeacherMapper

/**根据老师id查找老师(包含对应的学生信息)*/
Teacher selectInfoBytid(Integer tid);

TeacherMapper.xml

<resultMap id="TeacherMap" type="Teacher">
    <collection property="stuList" select="mybatis.mapper.StudentResultMapper.selectByTid" column="id"></collection>
</resultMap>
<select id="selectInfoBytid" resultMap="TeacherMap">
    select * from teacher where id=#{id}
</select>

Test

@Test
public void testStuAdnTeaTwo() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentResultMapper mapper = sqlSession.getMapper(StudentResultMapper.class);
    List<Student> students = mapper.selectByTid(1);
    for (Student stu:students){
        System.out.println(stu);
    }
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}
@Test
public void testSelectInfoTeaByTid() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = mapper.selectByTid(1);
    System.out.println(teacher.getName());
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

动态SQL

用户实现动态SQL的元素主要有

元素
if
where
trim
set
choos(when\otherwise)
foreach

if&where

SqlMapper

//根据姓名和性别查询学生
    List<Student> selectByNameAndSex(@Param("stuName") String stuName,@Param("sex") String sex);

SqlMapper.xml

<select id="selectByNameAndSex" resultType="student">
    select * from student
    <where>
        <if test="stuName!=null and stuName!=''">
          and stu_name like concat('%',#{stuName},'%')
        </if>
        <if test="sex!=null and sex!=''">
          and sex =#{sex,javaType=String,jdbcType=INTEGER,typeHandler=MyTypeHandler}
        </if>
    </where>
</select>

Test

@Test
public void testStuAdnTeaTwo() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);
    List<Student> students = mapper.selectByNameAndSex("任", "0");
    System.out.println(students);

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

Set

SqlMapper

//修改学生信息
int updateStudent(Student stu);

SqlMapper.xml

<update id="updateStudent" parameterType="student">
    update student
    <set>
        <if test="stuName!=null and stuName!=''">stu_name=#{stuName},</if>
        <if test="address!=null and address!=''">address=#{address},</if>
        <if test="birthday!=null and birthday!=''">birthday=#{birthday},</if>
        <if test="sex!=null and sex!=''">sex=#{sex,javaType=String,jdbcType=INTEGER,typeHandler=MyTypeHandler},</if>
        <if test="tid!=null and tid!=''">tid=#{tid},</if>
    </set>
    <where>
        <if test="id!=null and id!=''">
             id=#{id}
        </if>
    </where>
</update>

Test

@Test
public void testSqlUpdate() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);
    Student student = new Student();
    student.setId(14);
    student.setStuName("牛雷雨");
    int i = mapper.updateStudent(student);
    System.out.println(i);
    sqlSession.commit();

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

trim

属性
prefix
suffix
prefixOverrides
suffixOverrides

• 更灵活地去除多余关键字
• 替代where和set

修改

SqlMapper

//修改学生信息
int updateStudent(Student stu);

SqlMapper.xml

<update id="updateStudent" parameterType="student">
    update student
   <trim prefix="set" suffix="where id=#{id}" suffixOverrides=",">
       <if test="stuName!=null and stuName!=''">stu_name=#{stuName},</if>
       <if test="address!=null and address!=''">address=#{address},</if>
       <if test="birthday!=null and birthday!=''">birthday=#{birthday},</if>
       <if test="sex!=null and sex!=''">sex=#{sex,javaType=String,jdbcType=INTEGER,typeHandler=MyTypeHandler},</if>
       <if test="tid!=null and tid!=''">tid=#{tid},</if>
   </trim>
</update>

Test

@Test
public void testSqlUpdate() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);
    Student student = new Student();
    student.setId(15);
    student.setSex("男");
    student.setStuName("牛雷雨");
    int i = mapper.updateStudent(student);
    System.out.println(i);
    sqlSession.commit();

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

查询

SqlMapper.xml

<select id="selectByNameAndSex" resultType="student">
    select * from student
    <trim suffix="where" prefixOverrides="and">
        <if test="stuName!=null and stuName!=''">
            stu_name like concat('%',#{stuName},'%')
        </if>
        <if test="sex!=null and sex!=''">
            and sex =#{sex,javaType=String,jdbcType=INTEGER,typeHandler=MyTypeHandler}
        </if>
    </trim>
</select>

foreach&choose

foreach

SqlMapper

//业务：多条件之一查询
List<Student> selectChoose(Map<String,Object> map);

SqlMapper.xml

<select id="selectChoose" parameterType="map" resultType="student">
    select * from student
    <where>
    <choose>
        <when test="stuName!=null and stuName!=''">and stu_name like concat('%',#{stuName},'%')</when>
        <when test="sex!=null and sex!=''">and sex =#{sex,javaType=String,jdbcType=INTEGER,typeHandler=MyTypeHandler}</when>
        <when test="minBirthday!=null and minBirthday!=''">and birthday &gt;=#{minBirthday}</when>
        <when test="maxBirthday!=null and maxBirthday!=''">and birthday &lt;=#{maxBirthday}</when>
        <otherwise>and address like concat('%',#{address},'%')</otherwise>
    </choose>
    </where>
</select>

Test

@Test
public void testSqlSelectChoose() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);
    Map<String,Object> map = new HashMap<String,Object>();
    map.put("stuName","任");
    map.put("minBirthday","1999-04-27");
    List<Student> students = mapper.selectChoose(map);
    System.out.println(students);

    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

分页查询

SqlMapper

/**
 * 分页：
 *  思路
 *      limit a,b
 *          a,从a+1条数据开始
 *          b,查询多少条数据
 *
 * 1.封装page对象
 *  第几页
 *  每页显示多少条
 *  总页数
 *  页面内容
 * 2.写接口方法及SQL语句
 *  查询学生
 *  查询总记录
 * */
List<Student> selectStudent(@Param("start") Integer start,@Param("size")Integer size);

int getCount();

SqlMapper.xml

<select id="selectStudent" resultType="Student">
    select * from student limit #{start} , #{size}
</select>

<select id="getCount" resultType="int">
    select count(id) from student
</select>

Page

package mybatis.pojo;

import java.util.List;

/**
 * @author 玫瑰到了花期
 * @data 2022/4/29 15:34
 * @love 又偷偷看你了
 * @V: CBWR-K
 */
public class Page {
    /**当前页码*/
    private Integer pageIndex;
    /**页码大小*/
    private Integer pageSize;
    /**总记录数*/
    private Integer totalCount;
    /**总页面数*/
    private Integer totalPage;
    /**页面内容*/
    private List<Student> stuList;

    /**当前页码和页码大小的构造*/
    public Page(Integer pageIndex, Integer pageSize) {
        this.pageIndex = pageIndex;
        this.pageSize = pageSize;
    }

    public Page() {
    }

    public Integer getPageIndex() {
        return pageIndex;
    }

    public void setPageIndex(Integer pageIndex) {
        this.pageIndex = pageIndex;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(Integer totalCount) {
        this.totalCount = totalCount;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    public List<Student> getStuList() {
        return stuList;
    }

    public void setStuList(List<Student> stuList) {
        this.stuList = stuList;
    }
}

Test

@Test
public void testSqlPage() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);
    //参数一：pageIndex,从jsp页面获取
    int pageIndex = 1;
    //参数二：pageSize，后台指定
    int pageSize = 3;
    Page page = new Page(pageIndex,pageSize);
    //参数三：TotalCount，数据库查询
    int count = mapper.getCount();
    page.setTotalPage(count);
    //参数四：totalPage总页数，计算
    int totalPage = count%pageSize==0? count/pageSize:count/pageSize+1;
    page.setTotalPage(totalPage);
    //参数五：StuList页面内容，数据库查询
    List<Student> students = mapper.selectStudent((pageIndex - 1) * pageSize, pageSize);
    page.setStuList(students);
    System.out.println(students);


    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}

Mybatis缓存

方法

sqlSession.clearCache()	清楚缓存

一级缓存

• 默认开启
• 同一次数据库会话有效
• 手动调用清楚缓存的方法sqlSession.clearCache();缓存失效
• 调用增删改时缓存也会失效

二级缓存

使用

配置核心配置文件

<!--
        开启二级缓存
-->
        <setting name="cacheEnabled" value="true"></setting>

配置mapper.xml

<cache eviction="LRU" flushInterval="60000" size="10240" readOnly="true"></cache>

特点

• 作用于整个selsessionfactory
• 可以单独进行配置是否使用缓存

<select id="selectByNameAndSexResult" resultType="Student" flushCache="true">

第三方缓存EhCache

自动生成工具generator

导入必要pom依赖：

<dependencies>
    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.3.2</version>
    </dependency>
    <!--        mysql依赖-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
</dependencies>

核心配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
        <property name="beginningDelimiter" value="`" />
        <property name="endingDelimiter" value="`" />
        <!--  <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
              <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
           caseSensitive默认false，当数据库表名区分大小写时，可以将该属性设置为true
           <property name="caseSensitive" value="true"/>
         </plugin> -->

        <plugin type="org.mybatis.generator.plugins.MapperConfigPlugin">
            <property name="fileName" value="GeneratedMapperConfig.xml" />
            <property name="targetPackage" value="mapper" />
            <property name="targetProject" value="C:\code\resources\" />
        </plugin>
        <!-- true去掉多余的注释 -->
        <commentGenerator>
            <property name="suppressAllComments" value="true" />
            <property name="suppressDate" value="true" />
        </commentGenerator>
        <!-- 对应的数据库配置 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mybatis"
                        userId="root"
                        password="123456">
        </jdbcConnection>

        <!-- 实体类 -->
        <javaModelGenerator targetPackage="com.ytzl.pojo" targetProject="src/main/java"/>

        <!-- xml -->
        <sqlMapGenerator targetPackage="com.ytzl.mapper"  targetProject="src/main/java"/>

        <!-- 接口 -->
        <javaClientGenerator targetPackage="com.ytzl.dao" targetProject="src/main/java" type="XMLMAPPER" />

        <table tableName="%" >
            <generatedKey column="id" sqlStatement="Mysql" identity="true"/>
        </table>
    </context>
</generatorConfiguration>

启动 GeneratorDisplay

package com.ytzl.util;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class GeneratorDisplay {

   public void generator() throws Exception{

      List<String> warnings = new ArrayList<String>();
      boolean overwrite = true;
      //指定 逆向工程配置文件
      File configFile = new File("F:\\JavaWorkSpace\\MyBatis\\Genertor\\generatorConfig.xml");
      ConfigurationParser cp = new ConfigurationParser(warnings);
      Configuration config = cp.parseConfiguration(configFile);
      DefaultShellCallback callback = new DefaultShellCallback(overwrite);
      MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config,
            callback, warnings);

      myBatisGenerator.generate(null);

   }

   public static void main(String[] args) throws Exception {
      try {
         GeneratorDisplay generatorSqlmap = new GeneratorDisplay();
         generatorSqlmap.generator();
         System.out.println("ok");
      } catch (Exception e) {
         e.printStackTrace();
      }

   }
}

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="data.properties">
    </properties>
<!--    设置Mybaits的行为-->
    <settings>
<!--        设置日志打印方式-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
<!--        开启驼峰命名自动转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
<!--
        映射级别：
            NONE:取消自动配置
            PARTIAL:自动映射（没有嵌套的时候）
            FULL:任意复杂查询都会自动映射
-->
        <setting name="autoMappingBehavior" value="FULL"/>
<!--
        开启全局懒加载
-->
        <setting name="lazyLoadingEnabled" value="true"/>
<!--
        加载所有属性
-->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <typeAliases>
        <typeAlias type="com.ytzl.util.MyTypeHandler" alias="MyTypeHandler"/>
        <package name="com.ytzl.pojo"/>
    </typeAliases>
    <typeHandlers>
        <typeHandler handler="com.ytzl.util.MyTypeHandler" jdbcType="INTEGER" javaType="String"/>
    </typeHandlers>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/ytzl/mapper/TeacherMapper.xml"></mapper>
        <package name="com.ytzl.dao.TeacherMapper"></package>
    </mappers>
</configuration>

分页插件pagehelper

参考地址https://pagehelper.github.io/

依赖导入

<!--分页插件-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.10</version>
</dependency>

mybatis-config.xml

    <!--
    plugins在配置文件中的位置必须符合要求，否则会报错，顺序如下:
    properties?, settings?,
    typeAliases?, typeHandlers?,
    objectFactory?,objectWrapperFactory?,
    plugins?,
    environments?, databaseIdProvider?, mappers?
-->

    <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
<!--        <plugin interceptor="com.github.pagehelper.PageInterceptor">-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 -->
            <property name="helperDialect" value="mysql"/>
            <property name="pageSizeZero" value="true"/>
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>

测试

@Test
public void test分页() {
    //获取数据库连接
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    PageHelper.startPage(1, 2);
    List<Student> students = mapper.selectAll();
    System.out.println("students==>"+students);
    //调用工具类关闭会话方法
    MybatisUtil.closeSession(sqlSession);
}