温故知新——MyBatis

最新推荐文章于 2024-05-03 13:13:36 发布

1024的浪漫

最新推荐文章于 2024-05-03 13:13:36 发布

阅读量277

点赞数 1

分类专栏：面试文章标签： MyBatis

本文链接：https://blog.csdn.net/qq_17589253/article/details/102801018

版权

面试专栏收录该内容

5 篇文章 0 订阅

订阅专栏

温故知新——MyBatis

文章部分知识点结构参考了：https://blog.csdn.net/hellozpc/article/details/80878563，在此表示感谢

choose(when,otherwise)

statement中sql中的特殊字符处理

延迟加载

Spring集成MyBatis

SpringBoot集成MyBatis

介绍

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生类型、接口和 Java 的 POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。（介绍来源：https://mybatis.org/mybatis-3/zh/index.html)

如何使用

以Maven项目为例

添加MyBatis依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>

创建配置文件及数据库表

<?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>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true"/>
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </properties>

    <!-- 环境，可以配置多个，default：指定采用哪个环境 -->
    <environments default="test">
        <!-- id：唯一标识 -->
        <environment id="test">
            <!-- 事务管理其，JDBC：JDBC类型的事务管理器 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 数据源，采用连接池方式管理 -->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <!-- 对映射文件的管理进行配置 -->
    <mappers>
    </mappers>

</configuration>

创建表

drop table if exists t_teacher;
create table t_teacher
(
    id      int(11)      not null auto_increment,
    name    varchar(255) default null,
    age     int(2)       default null,
    subject varchar(255) not null,
    primary key (id)
) engine = innodb
  auto_increment = 1
  charset = utf8;

添加测试数据

insert into t_teacher(name, age, subject) values ('John', 30, 'English');
insert into t_teacher(name, age, subject) values ('White', 26, 'Science');
insert into t_teacher(name, age, subject) values ('John', 28, 'History');

创建对应pojo类（省略getter和setter）

@Data
public class Teacher {
    private Integer id;// 主键
    private String name;// 姓名
    private Integer age;// 年龄
    private String subject;// 学科
}

创建一个mapper文件，并注册到MyBatis配置文件上

<?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：命名空间，用于标识一个唯一的mapper文件，类似于Java中的全类名 -->
<mapper namespace="com.example.mybatis.demo.base.TeacherMapper">
    <!-- 声明一个select方式的statement，SqlSession可以通过名称查找一个statement，并赋值参数和执行sql，
        通过namespace加名称的方式来标识一个唯一的statement -->
    <!-- resultType：声明返回的结果集对应的类型，MyBatis可以处理一些简单的对象映射 -->
    <select id="getTeacherById" resultType="com.example.mybatis.demo.base.Teacher">
        select * from t_teacher where id = #{id}
    </select>
</mapper>



<!-- 对映射文件的管理进行配置 -->
<mappers>
    <mapper resource="mappers/TeacherMapper.xml"></mapper>
</mappers>

使用MyBatis查询Teacher信息

public class Main {

    public static void main(String[] args) throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();
        Teacher teacher = session.selectOne("getTeacherById", 1);
        System.out.println(teacher);
    }
}

结果如下：

Teacher(id=1, name=John, age=30, subject=English)

小结

相比于JDBC，MyBatis有如下优点：
1. 支持自定义sql、存储过程、高级映射
2. 实现自动对sql的参数设置
3. 实现对结果集自动封装
4. 通过xml或者注解进行配置和映射，将sql从代码中分离出来，且可以sql复用和优化
5. 通过配置文件进行数据源等配置，还可以使用MyBatis的其它特性，如懒加载，缓存等
使用MyBatis主要是使用MyBatis的SqlSession，而SqlSession由SqlSessionFactory创建，而SqlSessionFactory的创建用到了MyBatis的配置文件
MyBatis的配置文件中，主要的配置部分是数据源信息和mapper文件，还有其它的特性配置如别名管理、开启驼峰属性匹配、懒加载等，这些后面会提到
Mapper文件里主要配置业务与数据库交互的sql，MyBatis的框架的灵活性使我们可以定制sql语句，可以对sql进行优化，提高查询性能，当然也有一些弊端，后面会提到

resultMap

MyBatis可以对结果集作简单的对象映射处理，当遇到结果集中的别名或列名与对象中的字段名不对应时，就需要我们自己去手动作结果集与对象的映射了，这是需要mapper文件中一个新的标签<resultMap>

我们给t_teacher表里新增一个字段 favorite_food，并在pojo里新增对应属性favoriteFood，t_teacher表里新增对应列的数据，然后我们再查询一下Teacher的信息，结果如下：

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=null)

发现favoriteFood对应的值实际上是“炒鸡蛋”，但是没有取出来，这是因为对于表中字段名和pojo类中属性名不对应的情况下如果我们不手动配置映射关系的话，MyBatis是不知道怎么去映射的（毕竟我们只配置了mapper，而MyBatis与Hibernate不同，没有配置对应表的信息的地方），所以我们需要加一个映射关系，就是resultMap，修改mapper文件，新增resultMap，修改select中对应信息

<select id="getTeacherById" resultMap="resultTeacher">
    select * from t_teacher where id = #{id}
</select>

<!-- resultMap：配置映射关系 -->
<!-- type：结果集对应的数据类型；autoMapping：其它没有配置的属性，按照type中的类型进行简单映射 -->
<resultMap id="resultTeacher" type="com.example.mybatis.demo.base.Teacher" autoMapping="true">
    <!-- id：声明结果集中主键列；column：字段名；property：属性名；javaType属性的类型，
        一般建议配置javaType，防止出现字段结果类型转换问题 -->
    <id column="id" property="id" javaType="java.lang.Integer"></id>
    <!-- result：声明结果集中普通列的映射 -->
    <result column="favorite_food" property="favoriteFood" javaType="java.lang.String"></result>
</resultMap>

再次查询，结果如下

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

驼峰匹配

resultMap中提到的问题也可以用驼峰匹配来解决，例如上文中的字段favorite_food和属性favoriteFood，其实就是每一个大写字母变成小写，然后在前边加上下划线，属性名就变成字段名了，使用驼峰匹配的前提是属性名必须符合驼峰命名原则且字段名与属性名符合如上转换性质

在配置文件中开启驼峰匹配，默认不开启

<!-- MyBatis特性配置 -->
<settings>
    <!-- 开启驼峰匹配，默认不开启 -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

将我们的查询statement修改成简单映射的方式

<select id="getTeacherById" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher where id = #{id}
</select>

执行查询，结果如下：

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

注意：如果出现如下错误：

"configuration" 的内容必须匹配 "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)"

这是因为MyBatis的配置文件中，标签是严格按照顺序去配置的，否则会报错

MyBatis动态代理

一般情况下，在实际使用中，是把SqlSession作为dao层对象的属性或者在dao层获取SqlSession去操作的，当然也可以用MyBatis的动态代理来简化上述过程，即使用MyBatis动态代理的好处是，通过声明接口与mapper文件对应，接口中的方法与mapper文件中的statement对应，简化SqlSession的使用，可以直接获取接口类型的对象，调用其方法，而不需要手动获取mapper文件中的statement

使用MyBatis动态代理需要注意以下几点：

Mapper接口的全类名与mapper文件的唯一标识(命名空间+文件名称)对应
Mapper接口中的方法的全方法名与mapper文件中的statement的唯一标识对应
Mapper接口的方法中返回类型与mapper文件的statement的resultType中的类型一致
Mapper接口的方法中参数类型与mapper文件的statement的sql中对应传入参数的类型映射一致（否则可能会出现sql报错，提示类型异常）

使用MyBatis的动态代理对t_teacher表作CRUD

声明Mapper接口

public interface TeacherMapper {
    public Teacher getTeacherById(Integer id);

    public void insertTeacher(Teacher teacher);

    public void updateAgeById(String id, Integer age);
    
    public void deleteTeacherById(Integer id);
}

mapper文件中添加对应statement

<insert id="insertTeacher">
    insert into t_teacher(name, age, subject, favorite_food)
    values(#{name}, #{age}, #{subject}, #{favoriteFood})
</insert>

<update id="updateAgeById">
    update t_teacher set age = #{age} where id = #{id}
</update>

<delete id="deleteTeacherById">
    delete from t_teacher where id = #{id}
</delete>

<select id="getTeacherById" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher where id = #{id}
</select>

编写测试类进行测试

public class TeacherMapperTest {

    private TeacherMapper teacherMapper;
    private SqlSession session;

    @Before
    public void setUp() throws Exception {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        session = factory.openSession();
        teacherMapper = session.getMapper(TeacherMapper.class);
    }

    @Test
    public void getTeacherById() {
        Teacher teacher = teacherMapper.getTeacherById(1);
        System.out.println(teacher);
    }

    @Test
    public void insertTeacher() {
        Teacher teacher = new Teacher();
        teacher.setName("Smith");
        teacher.setAge(23);
        teacher.setSubject("Sports");
        teacher.setFavoriteFood("牛排");
        teacherMapper.insertTeacher(teacher);
    }

    @Test
    public void updateAgeById() {
        Teacher teacher = teacherMapper.getTeacherById(1);
        System.out.println(teacher);
        teacherMapper.updateAgeById(1, 24);
        teacher = teacherMapper.getTeacherById(1);
        System.out.println(teacher);
    }

    @Test
    public void deleteTeacherById() {
        Teacher teacher = teacherMapper.getTeacherById(6);
        teacherMapper.deleteTeacherById(6);
        teacher = teacherMapper.getTeacherById(6);
        System.out.println(teacher);
    }
}

使用logback进行日志输出，打开debug级别日志

<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">

    <!--控制台日志， 控制台输出 -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度,%msg：日志消息，%n是换行符-->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %level %logger - %msg%n</pattern>
        </encoder>
    </appender>

    <!-- 日志输出级别 -->
    <root level="DEBUG">
        <appender-ref ref="STDOUT" />
    </root>
</configuration>

测试方法getTeacherById()，结果如下：

2019-10-27 17:13:12.334 [main] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.

2019-10-27 17:13:12.348 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:12.348 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:12.348 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:12.348 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:12.425 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

2019-10-27 17:13:14.314 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 2077528955.

2019-10-27 17:13:14.314 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7bd4937b]

2019-10-27 17:13:14.318 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==>  Preparing: select * from t_teacher where id = ?

2019-10-27 17:13:14.348 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==> Parameters: 1(Integer)

2019-10-27 17:13:14.375 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - <==      Total: 1

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

测试方法insertTeacher()，结果如下：


2019-10-27 17:13:44.656 [main] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.

2019-10-27 17:13:44.667 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:44.668 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:44.668 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:44.668 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:13:44.746 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

2019-10-27 17:13:46.646 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1483298597.

2019-10-27 17:13:46.646 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@58695725]

2019-10-27 17:13:46.651 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.insertTeacher - ==>  Preparing: insert into t_teacher(name, age, subject, favorite_food) values(?, ?, ?, ?)

2019-10-27 17:13:46.680 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.insertTeacher - ==> Parameters: Smith(String), 23(Integer), Sports(String), 牛排(String)

2019-10-27 17:13:46.683 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.insertTeacher - <==    Updates: 1

去数据库中查看是否新增该条记录

图中内容显示没有插入新数据

发现并没有真的插入一条记录，其实，从debug日志中已经可以看出问题了

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@58695725]

可以看到SqlSession在获取Connection后，会将其auto-commit属性设置成false，所以在进行数据的修改后，我们需要手动进行commit，或者将SqlSession中的connection的属性auto-commit设置成true，这里我们手动commit

@Test
public void insertTeacher() {
    Teacher teacher = new Teacher();
    teacher.setName("Smith");
    teacher.setAge(23);
    teacher.setSubject("Sports");
    teacher.setFavoriteFood("牛排");
    teacherMapper.insertTeacher(teacher);
    session.commit();
}

再次执行，结果如下：

2019-10-27 17:19:10.517 [main] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.

2019-10-27 17:19:10.528 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:19:10.528 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:19:10.528 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:19:10.528 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:19:10.608 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

2019-10-27 17:19:12.469 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1483298597.

2019-10-27 17:19:12.469 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@58695725]

2019-10-27 17:19:12.475 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.insertTeacher - ==>  Preparing: insert into t_teacher(name, age, subject, favorite_food) values(?, ?, ?, ?)

2019-10-27 17:19:12.506 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.insertTeacher - ==> Parameters: Smith(String), 23(Integer), Sports(String), 牛排(String)

2019-10-27 17:19:12.508 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.insertTeacher - <==    Updates: 1

2019-10-27 17:19:12.509 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@58695725]

可以看到日志最后进行了提交操作，数据库中也插入了新的数据

插入一条新数据：6 Smith 23 Sports 牛排

测试方法updateAgeById()，报错如下：

Parameter 'age' not found. Available parameters are [arg1, arg0, param1, param2]

当借口的方法中有多个参数时，运行时MyBatis时不知道接口中方法的参数名称的，也没有意义，所以MyBatis绑定参数时是根据接口中方法的参数列表去匹配绑定的，所以若有多个参数，可以按照报错中所说的，用#{arg0}或者#{param1}来表示第一个参数，以此类推

<update id="updateAgeById">
    update t_teacher set age = #{arg0} where id = #{arg1}
</update>

或者给接口中方法的参数加上MyBatis的Param注解

public void updateAgeById(@Param("id") Integer id, @Param("age") Integer age);

修改测试方法，最后加上提交操作，再次执行，结果如下：

2019-10-27 17:27:24.705 [main] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.

2019-10-27 17:27:24.717 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:27:24.717 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:27:24.717 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:27:24.717 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.

2019-10-27 17:27:24.810 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

2019-10-27 17:27:26.718 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 2077528955.

2019-10-27 17:27:26.719 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7bd4937b]

2019-10-27 17:27:26.723 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==>  Preparing: select * from t_teacher where id = ?

2019-10-27 17:27:26.751 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==> Parameters: 1(Integer)

2019-10-27 17:27:26.776 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - <==      Total: 1

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

2019-10-27 17:27:26.779 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.updateAgeById - ==>  Preparing: update t_teacher set age = ? where id = ?

2019-10-27 17:27:26.779 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.updateAgeById - ==> Parameters: 1(Integer), 24(Integer)

2019-10-27 17:27:26.785 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.updateAgeById - <==    Updates: 0

2019-10-27 17:27:26.786 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==>  Preparing: select * from t_teacher where id = ?

2019-10-27 17:27:26.786 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==> Parameters: 1(Integer)

2019-10-27 17:27:26.787 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - <==      Total: 1

2019-10-27 17:27:26.787 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7bd4937b]

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

为什么在insert的statement中，MyBatis可以绑定我们传入Teacher对象的属性呢，因为insert的接口方法只有一个参数，而且，实际运行时MyBatis虽然获取不到参数名但是可以获取到参数对应的值，在这里，MyBatis获取到Teacher对象，利用反射机制，可以获取到对象中各个属性的名称、类型以及对应的值，所以这样是可以进行绑定，如果我们的接口方法中有多个参数的话，也是会报错的

测试方法deleteTeacherById()，加上commit，结果如下：

2019-10-27 17:30:01.862 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==>  Preparing: select * from t_teacher where id = ?

2019-10-27 17:30:01.891 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==> Parameters: 6(Integer)

2019-10-27 17:30:01.914 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - <==      Total: 1

2019-10-27 17:30:01.916 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.deleteTeacherById - ==>  Preparing: delete from t_teacher where id = ?

2019-10-27 17:30:01.916 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.deleteTeacherById - ==> Parameters: 6(Integer)

2019-10-27 17:30:01.922 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.deleteTeacherById - <==    Updates: 1

2019-10-27 17:30:01.923 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==>  Preparing: select * from t_teacher where id = ?

2019-10-27 17:30:01.923 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==> Parameters: 6(Integer)

2019-10-27 17:30:01.924 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - <==      Total: 0

2019-10-27 17:30:01.924 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7bd4937b]

null

#和$的区别

#{}和${}都是占位符，作用不一样

#{}只是替换，相当于PreparedStatement使用占位符替换参数，可以防止sql注入；${}是进行字符串拼接，相当于JDBC中的Statement，不能防止sql注入

示例如下：

<select id="getByName1" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher where name = #{name}
</select>

<select id="getByName2" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher where name = '${name}'
</select>

动态SQL

MyBatis的动态sql是基于OGNL表达式的，它可以帮助我们方便的在sql语句中实现某些逻辑

if

<if>：简单的条件判断，根据条件是否成立，在sql中补充sql字句

<select id="getByIf" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher
    where name = #{name}
    <if test="null != age">
        and age = #{age}
    </if>
    <if test="null != subject">
        and subject = #{subject}
    </if>
    <if test="null != favoriteFood">
        and favorite_food = #{favoriteFood}
    </if>
</select>

效果如下：

2019-10-27 18:18:35.656 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByIf - ==>  Preparing: select * from t_teacher where name = ? and age = ?

2019-10-27 18:18:35.688 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByIf - ==> Parameters: Black(String), 28(Integer)

2019-10-27 18:18:35.711 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByIf - <==      Total: 1

Teacher(id=3, name=Black, age=28, subject=History, favoriteFood=豆腐脑)

choose(when,otherwise)

choose相当于Java中if else-if else语句，通常与when、otherwise搭配

<select id="getByChoose" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher
    <choose>
        <when test="null != name">
            where name = #{name}
        </when>
        <when test="null != age">
            where age = #{age}
        </when>
        <otherwise>
            where subject = #{subject}
        </otherwise>
    </choose>
</select>

效果如下：

2019-10-27 18:26:07.804 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByChoose - ==>  Preparing: select * from t_teacher where name = ?

2019-10-27 18:26:07.832 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByChoose - ==> Parameters: Black(String)

2019-10-27 18:26:07.856 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByChoose - <==      Total: 1

Teacher(id=3, name=Black, age=28, subject=History, favoriteFood=豆腐脑)

trim

trim元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是prefix和suffix；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides；正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where元素的功能

<select id="getByTrim" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher
    <trim prefix="where" prefixOverrides="and | or">
        <if test="null != name">
            name = #{name}
        </if>
        <if test="null != age">
            and age = #{age}
        </if>
        <if test="null != subject">
            or subject = #{subject}
        </if>
    </trim>
</select>

效果如下：

2019-10-27 18:38:11.230 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByTrim - ==>  Preparing: select * from t_teacher where age = ? or subject = ?

2019-10-27 18:38:11.259 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByTrim - ==> Parameters: 28(Integer), History(String)

2019-10-27 18:38:11.284 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByTrim - <==      Total: 1

Teacher(id=3, name=Black, age=28, subject=History, favoriteFood=豆腐脑)

where

where元素的作用是会在写入where元素的地方输出一个where，另外一个好处是你不需要考虑where元素里面的条件输出是什么样子的，MyBatis会智能的帮你处理，如果所有的条件都不满足那么MyBatis就会查出所有的记录，如果输出后是and 开头的，MyBatis会把第一个and忽略，当然如果是or开头的，MyBatis也会把它忽略；此外，在where元素中你不需要考虑空格的问题，MyBatis会智能的帮你加上

<select id="getByWhere" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher
    <where>
        <if test="null != name">
            and name = #{name}
        </if>
        <if test="null != age">
            and age = #{age}
        </if>
        <if test="null != subject">
            and subject = #{subject}
        </if>
    </where>
</select>

效果如下：

2019-10-27 20:00:46.895 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByWhere - ==>  Preparing: select * from t_teacher WHERE name = ? and age = ? and subject = ?

2019-10-27 20:00:46.933 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByWhere - ==> Parameters: Black(String), 28(Integer), History(String)

2019-10-27 20:00:46.965 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getByWhere - <==      Total: 1

Teacher(id=3, name=Black, age=28, subject=History, favoriteFood=豆腐脑)

set

set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的，主要是在包含的语句前输出一个set，然后如果包含的语句是以逗号结束的话将会把该逗号忽略，如果set包含的内容为空的话则会出错

<update id="updateBySet">
    update t_teacher
    <set>
        <if test="null != name">
            name = #{name},
        </if>
        <if test="null != age">
            age = #{age}
        </if>
    </set>
    where id = #{id}
</update>

效果如下：

2019-10-27 20:09:45.586 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.updateBySet - ==>  Preparing: update t_teacher SET name = ?, age = ? where id = ?

2019-10-27 20:09:45.586 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.updateBySet - ==> Parameters: Black(String), 28(Integer), 3(Integer)

2019-10-27 20:09:45.595 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.updateBySet - <==    Updates: 1

2019-10-27 20:09:45.596 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@43dac38f]

foreach

foreach主要用于构建条件in中，它可以在sql语句中迭代一个集合，foreach的属性主要有item、index、collection、open、separator、close

item：表示集合中遍历的每一个元素，并为其指定别名

index：表示在迭代过程中，每次迭代到的位置

open：表示该语句以什么开始

separator：表示在每次迭代之间以什么作分隔符

close：表示该语句以什么结束

collection：用于指定集合，若传入的是一个List，则属性值为list；若传入的是一个数组，则属性值为array；若传入的参数时多个MyBatis会把它们自动封装成map，map的key为参数名

单参数List类型示例

<select id="findByIdIn" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher where id in
    <foreach collection="list" index="i" item="id" open="(" close=")" separator=", ">
        #{id}
    </foreach>
</select>



2019-10-27 20:31:09.478 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByIdIn - ==>  Preparing: select * from t_teacher where id in ( ? , ? )

2019-10-27 20:31:09.516 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByIdIn - ==> Parameters: 1(Integer), 2(Integer)

2019-10-27 20:31:09.550 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByIdIn - <==      Total: 2

2

单参数数组类型示例：

<select id="findByIdIn2" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher where id in
    <foreach collection="array" index="i" item="id" open="(" close=")" separator=", ">
        #{id}
    </foreach>
</select>



2019-10-27 20:35:54.048 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByIdIn2 - ==>  Preparing: select * from t_teacher where id in ( ? , ? )

2019-10-27 20:35:54.084 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByIdIn2 - ==> Parameters: 1(Integer), 2(Integer)

2019-10-27 20:35:54.119 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByIdIn2 - <==      Total: 2

2

属性值为map的示例：

<select id="findByMap" resultType="com.example.mybatis.demo.base.Teacher">
    select * from t_teacher where id in
    <foreach collection="ids" index="i" item="item" open="(" close=")" separator=", ">
        #{item}
    </foreach>
    and name = #{name}
</select>

@Test
public void findByMap() {
    Integer[] ids = new Integer[] {1, 2};
    Map<String, Object> map = new HashMap<>(2);
    map.put("name", "White");
    map.put("ids", ids);
    List<Teacher> teachers = teacherMapper.findByMap(map);
    System.out.println(teachers.size());
}

效果如下：

2019-10-27 20:44:12.865 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByMap - ==>  Preparing: select * from t_teacher where id in ( ? , ? ) and name = ?

2019-10-27 20:44:12.900 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByMap - ==> Parameters: 1(Integer), 2(Integer), White(String)

2019-10-27 20:44:12.931 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByMap - <==      Total: 1

1

bind

新版本中增加的标签，它的功能是在当前OGNL上下文中创建一个变量并绑定一个值，示例如下：

<select id="findByBind" resultType="com.example.mybatis.demo.base.Teacher">
    <bind name="nameLike" value="'%'+arg0+'%'"/>
    select * from t_teacher where name like #{nameLike}
</select>

效果如下：

2019-10-27 20:52:21.300 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByBind - ==>  Preparing: select * from t_teacher where name like ?

2019-10-27 20:52:21.337 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByBind - ==> Parameters: %John%(String)

2019-10-27 20:52:21.367 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.findByBind - <==      Total: 1

[Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)]

缓存

在MyBatis中有一级缓存和二级缓存，一级缓存的作用域为session，即在session中先后执行两个相同的statement，且参数相同，则第二次可以直接从缓存中查询结果，而不必再次查询数据库

二级缓存的作用域为一个mapper的namespace，即同一个namespace中查询的sql可以从缓存中命中

一级缓存

在MyBatis中，一级缓存默认开启，且无法关闭，示例如下：

@Test
public void testFirstCache() {
    Teacher teacher = teacherMapper.getTeacherById(1);
    System.out.println(teacher);
    teacher = teacherMapper.getTeacherById(1);
    System.out.println(teacher);
    teacherMapper.updateAgeById(1, 30);
    session.commit();
    teacher = teacherMapper.getTeacherById(1);
    System.out.println(teacher);
}

执行结果如下：

2019-10-27 21:05:21.914 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==>  Preparing: select * from t_teacher where id = ?

2019-10-27 21:05:21.958 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==> Parameters: 1(Integer)

2019-10-27 21:05:22.003 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - <==      Total: 1

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

对数据的修改操作，会清空缓存

二级缓存

MyBatis的二级缓存默认是不开启的，需要手动开启，在mapper文件中作如下配置

<mapper namespace="com.example.mybatis.demo.base.TeacherMapper">
    <cache />

进行测试：

@Test
public void testSecondCache() {
    Teacher teacher = teacherMapper.getTeacherById(1);
    System.out.println(teacher);
    session.close();
    SqlSession session = factory.openSession();
    TeacherMapper mapper = session.getMapper(TeacherMapper.class);
    teacher = mapper.getTeacherById(1);
    System.out.println(teacher);
}

Teacher类需要实现序列化接口，效果如下：

2019-10-27 21:14:14.595 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==>  Preparing: select * from t_teacher where id = ?

2019-10-27 21:14:14.644 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - ==> Parameters: 1(Integer)

2019-10-27 21:14:14.679 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherById - <==      Total: 1

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

2019-10-27 21:14:14.700 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@31fa1761]

2019-10-27 21:14:14.702 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@31fa1761]

2019-10-27 21:14:14.709 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 838473569 to pool.

2019-10-27 21:14:14.713 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper - Cache Hit Ratio [com.example.mybatis.demo.base.TeacherMapper]: 0.5

Teacher(id=1, name=John, age=30, subject=English, favoriteFood=炒鸡蛋)

一般情况下，用MyBatis的一级缓存就可以了，MyBatis的二级缓存有其它东西可以替代，比如memcache、redis、ehcache等，配置不开启二级缓存有如下方式：

不配置mapper文件的cache

在配置文件中配置启动二级缓存的开关

<!-- MyBatis特性配置 -->
<settings>
    <!-- 开启驼峰匹配，默认不开启 -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <!-- 开启二级缓存，全局总开关，这里关闭，mapper中的缓存配置将会失效 -->
    <setting name="cacheEnabled" value="false"/>
</settings>

高级查询

MyBatis对sql的高级查询也作了支持，主要有一对一、多对一和多对多三种，下边的示例中，默认开启驼峰匹配

一对一

创建学生表、班级表，添加数据（自行添加即可），创建相应的pojo类

学生对于班级，是一对一

drop table if exists t_student;
create table t_student
(
    id   int(11) not null auto_increment,
    name varchar(255) default null,
    age  int(2)       default null,
    class_id int(11) not null,
    primary key (id)
) engine = innodb
  auto_increment = 1
  charset = utf8;



drop table if exists t_class_info;
create table t_class_info(
    id int(11) not null auto_increment,
    class_name varchar(255) default null,
    primary key (id)
) engine =innodb
  auto_increment = 1
  charset = utf8;



@Data
public class Student {
    private Integer id;// 主键
    private String name;// 姓名
    private Integer age;// 年龄
    private ClassInfo classInfo;// 所在班级信息
}



@Data
public class ClassInfo {
    private Integer id;// 主键
    private String className;// 班级名称
}

创建StudentMapper及对应mapper文件，作查询测试

<select id="getById" resultMap="resultStudent">
    select
    a.id sid, a.name, a.age, a.class_id,
    b.id cid, b.class_name
    from t_student a
    left join t_class_info b on a.class_id = b.id
    where a.id = #{id}
</select>

<resultMap id="resultStudent" type="com.example.mybatis.demo.base.onetoone.Student" autoMapping="true">
    <id property="id" column="sid" javaType="java.lang.Integer"></id>
    <association property="classInfo" column="class_id" javaType="com.example.mybatis.demo.base.onetoone.ClassInfo" autoMapping="true">
        <id property="id" column="cid" javaType="java.lang.Integer"></id>
    </association>
</resultMap>



@Test
public void getById() {
    Student student = studentMapper.getById(1);
    System.out.println(JSON.toJSONString(student, true));
}

结果如下：

2019-10-27 22:17:22.302 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getById - ==>  Preparing: select a.id sid, a.name, a.age, a.class_id, b.id cid, b.class_name from t_student a left join t_class_info b on a.class_id = b.id where a.id = ?

2019-10-27 22:17:22.351 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getById - ==> Parameters: 1(Integer)

2019-10-27 22:17:22.399 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getById - <==      Total: 1

{

"age":12,

"classInfo":{

"className":"3年1班",

"id":1

},

"id":1,

"name":"小明"

}

association标签主要用于一对一，用来配置表中相对于另一个表有关联关系（不一定非得是外键）的字段

Sql查询时记得名称重复的字段用别名区分，否则映射的时候，有的属性会取不到值

一对多（或多对一）

仍然利用一对一中的数据模型，一个班级中有多个学生，修改班级模型

@Data
public class ClassInfo {
    private Integer id;// 主键
    private String className;// 班级名称
    private List<Student> students;// 学生信息
}

创建ClassInfoMapper，查询班级信息

<select id="getById" resultMap="resultClassInfo">
    select
    a.id cid, a.class_name,
    b.id sid, b.name, b.age, b.class_id
    from t_class_info a
    left join t_student b on a.id = b.class_id
    where a.id = #{id}
</select>

<resultMap id="resultClassInfo" type="com.example.mybatis.demo.base.onetoone.ClassInfo"
    autoMapping="true">
    <id property="id" column="cid" javaType="java.lang.Integer"></id>
    <collection property="students" column="class_id" ofType="com.example.mybatis.demo.base.onetoone.Student"
        autoMapping="true" >
        <id property="id" column="sid" javaType="java.lang.Integer"></id>
    </collection>
</resultMap>



@Test
public void getById() {
    ClassInfo classInfo = classInfoMapper.getById(1);
    System.out.println(JSON.toJSONString(classInfo, true));
}

效果如下：

2019-10-27 22:54:16.008 [main] DEBUG com.example.mybatis.demo.base.onetoone.ClassInfoMapper.getById - ==>  Preparing: select a.id cid, a.class_name, b.id sid, b.name, b.age, b.class_id from t_class_info a left join t_student b on a.id = b.class_id where a.id = ?

2019-10-27 22:54:16.052 [main] DEBUG com.example.mybatis.demo.base.onetoone.ClassInfoMapper.getById - ==> Parameters: 1(Integer)

2019-10-27 22:54:16.102 [main] DEBUG com.example.mybatis.demo.base.onetoone.ClassInfoMapper.getById - <==      Total: 2

{

"className":"3年1班",

"id":1,

"students":[

{

"age":12,

"id":1,

"name":"小明"

},

{

"age":13,

"id":3,

"name":"小强"

}

]

}

collection标签主要用于多对一或一对多，用来配置具体关联关系的属性和字段的映射关系，ofType表示集合中对应的类型

多对多

利用我们已经建好的表t_teacher和t_class_info，构建多对多关系，教师可以在多个班级中教课，一个班级可以多个教师教课，新建中间表来表示它们的关系

drop table if exists t_teacher_class_map;
create table t_teacher_class_map
(
    id         int(11) not null auto_increment,
    teacher_id int(11) default null,
    class_id   int(11) default null,
    primary key (id)
) engine = innodb
  auto_increment = 1
  charset = utf8;

修改pojo

@Data
public class Teacher implements Serializable {
    private Integer id;// 主键
    private String name;// 姓名
    private Integer age;// 年龄
    private String subject;// 学科
    private String favoriteFood;// 喜好食物
    private List<ClassInfo> classInfos;// 班级信息
}



@Data
public class ClassInfo {
    private Integer id;// 主键
    private String className;// 班级名称
    private List<Student> students;// 学生信息
    private List<Teacher> teachers;// 教师信息
}

测试多对多查询：

查询id为1的教师在哪些班级教课

<select id="getTeacherAndClassInfoById" resultMap="resultTeacherWithClassInfos">
    select
    a.id tid, a.name, a.age, a.subject, a.favorite_food,
    c.id cid, c.class_name
    from t_teacher a
    left join t_teacher_class_map b on a.id = b.teacher_id
    left join t_class_info c on b.class_id = c.id
    where a.id = #{id}
</select>

<resultMap id="resultTeacherWithClassInfos" type="com.example.mybatis.demo.base.Teacher"
    autoMapping="true">
    <id property="id" column="tid" javaType="java.lang.Integer"></id>
    <collection property="classInfos" ofType="com.example.mybatis.demo.base.onetoone.ClassInfo"
        autoMapping="true">
        <id property="id" column="cid" javaType="java.lang.Integer"></id>
    </collection>
</resultMap>

结果如下：

2019-10-28 00:05:56.448 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherAndClassInfoById - ==>  Preparing: select a.id tid, a.name, a.age, a.subject, a.favorite_food, c.id cid, c.class_name from t_teacher a left join t_teacher_class_map b on a.id = b.teacher_id left join t_class_info c on b.class_id = c.id where a.id = ?

2019-10-28 00:05:56.499 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherAndClassInfoById - ==> Parameters: 1(Integer)

2019-10-28 00:05:56.552 [main] DEBUG com.example.mybatis.demo.base.TeacherMapper.getTeacherAndClassInfoById - <==      Total: 2

{

"age":30,

"classInfos":[

{

"className":"3年1班",

"id":1

},

{

"className":"3年2班",

"id":2

}

],

"favoriteFood":"炒鸡蛋",

"id":1,

"name":"John",

"subject":"English"

}

查询id为1的班级有哪些教师教课

<select id="getClassInfoAndTeacherById" resultMap="resultClassInfoWithTeacher">
    select
    a.id cid, a.class_name,
    c.id tid, c.name, c.age, c.subject, c.favorite_food
    from t_class_info a
    left join t_teacher_class_map b on a.id = b.class_id
    left join t_teacher c on b.teacher_id = c.id
    where a.id = #{id}
</select>

<resultMap id="resultClassInfoWithTeacher" type="com.example.mybatis.demo.base.onetoone.ClassInfo"
    autoMapping="true">
    <id property="id" column="cid" javaType="java.lang.Integer"></id>
    <collection property="teachers" ofType="com.example.mybatis.demo.base.Teacher"
        autoMapping="true">
        <id property="id" column="tid" javaType="java.lang.Integer"></id>
    </collection>
</resultMap>



@Test
public void getClassInfoAndTeacherById() {
    ClassInfo classInfo = classInfoMapper.getClassInfoAndTeacherById(1);
    System.out.println(JSON.toJSONString(classInfo));
}

结果如下：

2019-10-28 00:26:15.949 [main] DEBUG com.example.mybatis.demo.base.onetoone.ClassInfoMapper.getClassInfoAndTeacherById - ==>  Preparing: select a.id cid, a.class_name, c.id tid, c.name, c.age, c.subject, c.favorite_food from t_class_info a left join t_teacher_class_map b on a.id = b.class_id left join t_teacher c on b.teacher_id = c.id where a.id = ?

2019-10-28 00:26:16.003 [main] DEBUG com.example.mybatis.demo.base.onetoone.ClassInfoMapper.getClassInfoAndTeacherById - ==> Parameters: 1(Integer)

2019-10-28 00:26:16.053 [main] DEBUG com.example.mybatis.demo.base.onetoone.ClassInfoMapper.getClassInfoAndTeacherById - <==      Total: 3

{"className":"3年1班","id":1,"teachers":[{"age":30,"favoriteFood":"炒鸡蛋","id":1,"name":"John","subject":"English"},{"age":26,"favoriteFood":"煎饼","id":2,"name":"White","subject":"Science"},{"age":28,"favoriteFood":"豆腐脑","id":3,"name":"Black","subject":"History"}]}

statement中的其它处理

sql片段

可以利用<sql>标签在复用sql中的公共片段，如下所示：

<select id="getById" resultMap="resultStudent">
    select
    a.id sid, a.name, a.age, a.class_id,
    b.id cid, b.class_name
    from t_student a
    left join t_class_info b on a.class_id = b.id
    where a.id = #{id}
</select>

可以改成：

<select id="getById" resultMap="resultStudent">
    select
    <include refid="studentFields"></include>,
    b.id cid, b.class_name
    from t_student a
    left join t_class_info b on a.class_id = b.id
    where a.id = #{id}
</select>

<sql id="studentFields">
    a.id sid, a.name, a.age, a.class_id
</sql>

statement中sql中的特殊字符处理

在statement的sql中，>和<以及其它的一些字符在xml中被认为是特殊字符，所以在sql中这些特殊字符需要转义，有两种做法：

使用xml转义字符，如<转义成<，>转义成>
使用<![CDATA[]]>，包含在这个标签的中括号里的字符，MyBatis认为是已经处理过的字符，不会对其中的内容进行转义，同样的，在里面输入<if>等语义标签同样不起作用，如num < 1可以写成num <![CDATA[<]]> 1

延迟加载

延迟加载的意义是在真正需要pojo对象的具体属性的时候，采取数据库访问对应记录，在MyBatis中设置启用延迟加载

<!-- MyBatis特性配置 -->
<settings>
    <!-- 开启驼峰匹配，默认不开启 -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <!-- 开启二级缓存，全局总开关，这里关闭，mapper中的缓存配置将会失效 -->
    <setting name="cacheEnabled" value="false"/>
    <!-- 启用延迟加载 -->
    <setting name="lazyLoadingEnable" value="true"/>
</settings>

查询学生信息

<select id="getByLazyLoading" resultMap="getByLazyLoadingResult">
    select * from t_student where id = #{id}
</select>

<select id="getClassInfoById" resultType="com.example.mybatis.demo.base.onetoone.ClassInfo">
    select * from t_class_info where id = #{id}
</select>

<resultMap id="getByLazyLoadingResult" type="com.example.mybatis.demo.base.onetoone.Student" autoMapping="true">
    <id property="id" column="id"></id>
    <association property="classInfo" column="class_id" javaType="com.example.mybatis.demo.base.onetoone.ClassInfo"
        select="getClassInfoById" autoMapping="true"></association>
</resultMap>



@Test
public void getByLazyLoading() {
    Student student = studentMapper.getByLazyLoading(1);
    System.out.println(JSON.toJSONString(student, true));
}

结果如下：

2019-10-29 13:03:39.557 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getByLazyLoading - ==>  Preparing: select * from t_student where id = ?

2019-10-29 13:03:39.589 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getByLazyLoading - ==> Parameters: 1(Integer)

2019-10-29 13:03:39.697 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getByLazyLoading - <==      Total: 1

2019-10-29 13:03:39.774 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getClassInfoById - ==>  Preparing: select * from t_class_info where id = ?

2019-10-29 13:03:39.774 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getClassInfoById - ==> Parameters: 1(Integer)

2019-10-29 13:03:39.780 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getClassInfoById - <==      Total: 1

{

"age":12,

"classInfo":{

"className":"3年1班",

"id":1

},

"id":1,

"name":"小明"

}

可以看到第一次查询的时候只查询学生信息，要打印pojo对象时，采取查询学生对应的班级信息，这就是延迟加载

与延迟加载相关的就是按需加载，即按照需求，去数据库加载对应字段而不是查询sql中的所有字段；一般不启用按需加载

优缺点分析：

优点：涉及到关联查询时，先单表查询，真正用到或需要时，再去关联表查询，因为查单表比查关联查表要快，所以可以提高查询性能

缺点：只有当用到的时候才会进行查询，所以查询数据量大时，查询时间变长，会造成用户等待时间变长，影响用户体验

要用延迟加载，要判断是否适合，比如用时加载过程是否耗时过长，是否真的需要用时加载，要结合具体场景去分析

Spring集成MyBatis

Spring集成MyBatis，其实主要是集成MyBatis中的SqlSessionFactory，有一个包mybatis-spring专门处理了spring集成MyBatis的工作，pom文件中引入相关依赖

<!-- spring集成MyBatis -->
<!-- 数据源 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.1</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.18.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.18.RELEASE</version>
</dependency>

新增配置文件db.properties

db.username=root
db.password=root
db.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
db.driverClassName=com.mysql.jdbc.Driver

新增Spring的配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd 
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 扫描配置文件 -->
    <context:property-placeholder location="classpath:*.properties" file-encoding="utf-8" />
    <!-- 数据源配置 -->
    <bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
        <property name="username" value="${db.username}"></property>
        <property name="password" value="${db.password}"></property>
        <property name="url" value="${db.jdbcUrl}"></property>
        <property name="driverClassName" value="${db.driverClassName}"></property>
    </bean>
    <!-- 集成MyBatis -->
    <!-- 集成MyBatis中的SqlSessionFactory -->
    <bean class="org.mybatis.spring.SqlSessionFactoryBean" id="factory">
        <!-- 数据源引用 -->
        <property name="dataSource" ref="dataSource"></property>
        <!-- 若有MyBatis的配置文件，则用下边的配置 -->
        <property name="configLocation" value="classpath:mybatis-config.xml"></property>
        <!-- 扫描指定位置的mapper文件 -->
        <property name="mapperLocations" value="classpath:mappers/*.xml"></property>
    </bean>

    <!-- 集成MyBatis的mapper动态代理 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" id="mapperScannerConfigurer">
        <!-- 查找指定包下的Java文件 -->
        <property name="basePackage" value="com.example.mybatis.demo.base"></property>
        <!-- 引入spring集成的生成SqlSessionFactory的bean -->
        <property name="sqlSessionFactoryBeanName" value="factory"></property>
    </bean>

    <!-- 事务管理 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>



<!-- 配置Annotation驱动，扫描@Transactional注解的类定义事务 -->

<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />


</beans>

在接口方法上添加Transactional注解

@Transactional
public int updateById(Student student);

因为在Spring中已经配置了数据源和mapper文件扫描，所以MyBatis配置文件中可以将数据源和mapper管理的配置部分注释掉

编写test进行测试

private ClassPathXmlApplicationContext context;
private StudentMapper studentMapper;

@Before
public void setUp() {
    context = new ClassPathXmlApplicationContext("spring.xml");
    studentMapper = context.getBean(StudentMapper.class);
}

@Test
public void tearDown() {
    context.close();
}

@Test
public void updateById() {
    Student student = studentMapper.getById(1);
    System.out.println(JSON.toJSONString(student, true));
    student.setAge(12);
    int update = studentMapper.updateById(student);
    System.out.println(JSON.toJSONString(student, true));
}

出现报错：

Malformed database URL, failed to parse the connection string near ';characterEncoding=utf-8&allowMultiQueries=true'.

这个错误的原因是，我们再MyBatis中配置数据源的时候，&要转义成&，而properties文件中则不需要转义，所以讲转义字符反转义即可

db.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&;characterEncoding=utf-8&;allowMultiQueries=true

再次进行测试，结果如下：

2019-10-29 14:32:38.566 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getById - ==>  Preparing: select a.id sid, a.name, a.age, a.class_id , b.id cid, b.class_name from t_student a left join t_class_info b on a.class_id = b.id where a.id = ?

2019-10-29 14:32:38.592 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getById - ==> Parameters: 1(Integer)

2019-10-29 14:32:38.616 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.getById - <==      Total: 1

2019-10-29 14:32:38.618 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@928763c]

{

"age":11,

"classInfo":{

"className":"3年1班",

"id":1

},

"id":1,

"name":"小明"

}

2019-10-29 14:32:38.703 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession

2019-10-29 14:32:38.703 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3adcc812]

2019-10-29 14:32:38.723 [main] DEBUG org.mybatis.spring.transaction.SpringManagedTransaction - JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@29a5f4e7] will be managed by Spring

2019-10-29 14:32:38.723 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.updateById - ==>  Preparing: update t_student SET name = ?, age = ? where id = ?

2019-10-29 14:32:38.723 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.updateById - ==> Parameters: 小明(String), 12(Integer), 1(Integer)

2019-10-29 14:32:38.725 [main] DEBUG com.example.mybatis.demo.base.onetoone.StudentMapper.updateById - <==    Updates: 1

2019-10-29 14:32:38.725 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3adcc812]

2019-10-29 14:32:38.726 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3adcc812]

2019-10-29 14:32:38.726 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3adcc812]

2019-10-29 14:32:38.726 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3adcc812]

{

"age":12,

"classInfo":{

"className":"3年1班",

"id":1

},

"id":1,

"name":"小明"

}

可以看到，Spring对添加了@Transactional的接口方法添加了事务处理，而不需要手动控制事务

SpringBoot集成MyBatis

作为一个快速开发微服务和项目的框架工具，SpringBoot自然也对MyBatis进行了支持

创建springboot项目，添加依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    <exclusions>
        <exclusion>
            <groupId>org.junit.vintage</groupId>
            <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.47</version>
</dependency>

编辑springboot的配置文件：

spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# MyBatis配置
mybatis.mapper-locations=classpath:mappers/*.xml

创建动态代理的接口和对应的mapper文件

@Mapper
public interface StudentMapper {

    public Student getById(Integer id);
}



<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.springbootmybatis.mapper.StudentMapper">
    <select id="getById" resultType="com.example.springbootmybatis.entity.Student">
        select * from t_student where id = #{id}
    </select>
</mapper>

进行测试

@SpringBootTest
public class SpringbootMybatisApplicationTests {

    @Autowired
    private StudentMapper studentMapper;

    @Test
    public void contextLoads() {
        Student student = studentMapper.getById(1);
        System.out.println(JSON.toJSONString(student, true));
    }

}

结果如下：

2019-10-29 15:42:50.117  INFO 8152 --- [           main] c.e.s.SpringbootMybatisApplicationTests  : Started SpringbootMybatisApplicationTests in 1.444 seconds (JVM running for 2.639)



2019-10-29 15:42:50.329  INFO 8152 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...

2019-10-29 15:42:50.333  WARN 8152 --- [           main] com.zaxxer.hikari.util.DriverDataSource  : Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.

2019-10-29 15:42:52.186  INFO 8152 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.

{

"age":"12",

"id":1,

"name":"小明"

}



2019-10-29 15:42:52.437  INFO 8152 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...

2019-10-29 15:42:52.451  INFO 8152 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

1024的浪漫

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
温故知新——MyBatis

温故知新——MyBatis文章部分知识点结构参考了：https://blog.csdn.net/hellozpc/article/details/80878563，在此表示感谢目录温故知新——MyBatis介绍如何使用小结resultMap驼峰匹配MyBatis动态代理#和$的区别动态SQLifchoose(when,otherwise)tr...
复制链接

扫一扫