MyBatis

环境：

JDK1.8
Mysql 5.7
Maven 3.6.3
IDEA

1. 简介

1.1 什么是`MyBatis`

MyBatis是一款优秀的持久层框架（数据访问层又称为DAL层，有时候也称为是持久层，其功能主要是负责数据库的访问）
它支持自定义 SQL、存储过程以及高级映射。
MyBatis 免除了几乎所有的JDBC代码以及设置参数和获取结果集的工作。
MyBatis可以通过简单的 XML或注解来配置和映射原始类型、接口和Java POJO（Plain Old Java Objects，普通老式Java对象）为数据库中的记录。
MyBatis本是apache的一个开源项目iBatis
2010年这个项目由apache software foundation迁移到了google code，并且改名为MyBatis
2013年11月迁移到Github

1.2 `MyBatis`相关网址链接

GitHub：https://github.com/mybatis/mybatis-3
MyBatis官网中文文档：https://mybatis.org/mybatis-3/zh/index.html
MyBatis官网：https://mybatis.org
Maven仓库：https://mvnrepository.com/search?q=Mybatis

1.3 数据持久化

持久化就是将程序的数据在持久状态和瞬时状态转化的过程
内存：断电即失
数据库、IO文件

1.4 为什么需要使用`MyBatis`

方便
简化JDBC代码
帮助程序员将数据存入到数据库中
框架自动化

2. 第一个`MyBatis`程序

2.1 搭建`MySQL/Oracle`数据库

CREATE DATABASE `MyBatis`;
USE `MyBatis`;
CREATE TABLE `Account` 
(
	`id` INT(8) NOT NULL AUTO_INCREMENT, 
	`accId` VARCHAR(20) NOT NULL, 
	`passWord` VARCHAR(50) NOT NULL, 
	`notes` VARCHAR(200) DEFAULT NULL, 
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
SELECT id, accId, passWord, notes 
FROM Account;

2.2 搭建`Maven`项目环境

2.2.1 导入`POM`依赖

<dependencies>
    <!-- Junit测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <!--Oracle JDBC驱动 依赖-->
    <dependency>
        <groupId>com.github.noraui</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>12.2.0.1</version>
    </dependency>
    <!--MyBatis 依赖-->
    <dependency>
        <groupId>app.myoss.cloud.mybatis</groupId>
        <artifactId>myoss-mybatis</artifactId>
        <version>2.1.7.RELEASE</version>
    </dependency>
    <!--Lombok 依赖-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
        <scope>provided</scope>
    </dependency>
</dependencies>

<!--配置build 防止Maven 资源导出失败-->
<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

2.2.2 在`resources`文件夹下创建一个文件，命名为：`mybatis-config.xml`

<?xml version="1.0" encoding="UTF8" ?>
<!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="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@59.51.66.142:21521:XJBHINFO"/>
                <property name="username" value="qxinterface"/>
                <property name="password" value="qx$interface#0112"/>
            </dataSource>
        </environment>
    </environments>
    
    <!--每一个mapper都必须在MyBatis的核心配置文件中进行mapper注册-->
    <mappers>
        <mapper resource="com/xjbh/mapper/EAS_SaleMapper.xml"/>
    </mappers>
</configuration>

2.2.3 编写代码

编写MyBatis工具类

package com.xjbh.uitls;

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;

public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

编写实体类

package com.xjbh.entity;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class EAS_Sale {
    private String sDate;
    private String shopId;
    private int manageDeptId;
    private double saleValue;
    private double saleTaxRate;
    private int flag;
    private int venderId;
}

编写EAS_SaleMapper接口

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;

import java.util.List;

public interface EAS_SaleMapper {
    List<EAS_Sale> getSaleList();
}

编写实现类转变为EAS_SaleMapper.xml的配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <!--id为接口方法，resultType为实体类-->
    <select id="getSaleList" resultType="com.xjbh.entity.EAS_Sale">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>
</mapper>

2.2.4 测试

import com.xjbh.entity.EAS_Sale;
import com.xjbh.uitls.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class EAS_SaleMapperTest {
    @Test
    public void getSalesTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        // 方法一：通过getMapper()方法
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);
        List<EAS_Sale> saleList = mapper.getSaleList();

        // 方法二：不推荐使用
        // List<EAS_Sale> saleList = sqlSession.selectList("com.xjbh.mapper.EAS_SaleMapper.getSaleList");
        
        for (EAS_Sale eas_sale : saleList) {
            System.out.println(eas_sale);
        }
        sqlSession.close();
    }
}

3. `CRUD`语句

3.1 `namespace`

namespace中的包名必须与mapper接口保持一致

3.2 `Create`

id：对应namespace中的方法名

parameterType：参数类型，如果参数类型为实体类型，则可以在SQL的参数中访问实体类属性

EAS_SaleMapper接口类

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;

import java.util.List;

public interface EAS_SaleMapper {
    int insertEAS_Sale(EAS_Sale easSale);
}

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <!--id为接口方法，parameterType为实体类，如果参数类型为实体类，则在SQL的参数中可以直接访问实体类属性-->
    <insert id="insertEAS_Sale" parameterType="com.xjbh.entity.EAS_Sale">
        INSERT INTO QXINTERFACE.SALE_CWJK(sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid)
        VALUES(#{sDate}, #{shopId}, #{manageDeptId}, #{saleValue}, #{saleTaxRate}, #{flag}, #{venderId})
    </insert>
</mapper>

测试代码

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;
import com.xjbh.uitls.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class EAS_SaleMapperTest {
    @Test
    public void insertEAS_SaleTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);
        mapper.insertEAS_Sale(new EAS_Sale("2021-10-11", "D011", 16, 110.00, 13, 0, 110001));

        // CUD语句需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }
}

3.3 `Retrieve`

id：对应namespace中的方法名
resultType：SQL语句执行的返回值

parameterType：参数类型，如果参数类型为实体类型，则可以在SQL的参数中访问实体类属性

EAS_SaleMapper接口类

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;

import java.util.List;

public interface EAS_SaleMapper {
    List<EAS_Sale> getSaleList();
    List<EAS_Sale> getSaleListByShopId(String shopId);
    EAS_Sale getSaleListByEAS_Sale(EAS_Sale easSale);
}

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <!--id为接口方法，resultType为实体类-->
    <select id="getSaleList" resultType="com.xjbh.entity.EAS_Sale">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>

    <!--id为接口方法，resultType为实体类-->
    <select id="getSaleListByShopId" resultType="com.xjbh.entity.EAS_Sale" parameterType="String">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE shopid = #{shopId}
              AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>

    <!--id为接口方法，resultType为实体类，如果参数类型为实体类，则在SQL的参数中可以直接访问实体类属性-->
    <select id="getSaleListByEAS_Sale" resultType="com.xjbh.entity.EAS_Sale" parameterType="com.xjbh.entity.EAS_Sale">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE managedeptid = #{manageDeptId}
          AND shopid = #{shopId}
          AND venderid = #{venderId}
          AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>
</mapper>

测试代码

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;
import com.xjbh.uitls.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class EAS_SaleMapperTest {
    @Test
    public void getSaleListTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        // 方法一：通过getMapper()方法
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);
        List<EAS_Sale> saleList = mapper.getSaleList();

        // 方法二：不推荐使用
        // List<EAS_Sale> saleList = sqlSession.selectList("com.xjbh.mapper.EAS_SaleMapper.getSaleList");

        for (EAS_Sale eas_sale : saleList) {
            System.out.println(eas_sale);
        }
        sqlSession.close();
    }

    @Test
    public void getSaleListByShopIdTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);
        List<EAS_Sale> saleList = mapper.getSaleListByShopId("D011");

        for (EAS_Sale eas_sale : saleList) {
            System.out.println(eas_sale);
        }
        sqlSession.close();
    }

    @Test
    public void getSaleListByEAS_SaleTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);
        EAS_Sale easSale = mapper.getSaleListByEAS_Sale(new EAS_Sale("2021-10-11", "D011", 16, 110.00, 13, 0, 110001));
        System.out.println(easSale);

        sqlSession.close();
    }
}

3.4 `Update`

id：对应namespace中的方法名

parameterType：参数类型，如果参数类型为实体类型，则可以在SQL的参数中访问实体类属性

EAS_SaleMapper接口类

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;

import java.util.List;

public interface EAS_SaleMapper {
    int updateEAS_SaleByEAS_Sale(EAS_Sale easSale);
}

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <!--id为接口方法，parameterType为实体类，如果参数类型为实体类，则在SQL的参数中可以直接访问实体类属性-->
    <update id="updateEAS_SaleByEAS_Sale" parameterType="com.xjbh.entity.EAS_Sale">
        UPDATE QXINTERFACE.SALE_CWJK
        SET
               sdate=#{sDate},
               managedeptid=#{manageDeptId},
               salevalue=#{saleValue},
               saletaxrate=#{saleTaxRate},
               flag=#{flag},
               venderid=#{venderId}
        WHERE shopid=#{shopId} 
    </update>
</mapper>

测试代码

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;
import com.xjbh.uitls.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class EAS_SaleMapperTest {
    @Test
    public void updateEAS_SaleByEAS_SaleTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);
        mapper.updateEAS_SaleByEAS_Sale(new EAS_Sale("2021-10-11", "D011", 16, 110.00, 13, 0, 110001));

        // CUD语句需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }
}

3.5 `Delete`

id：对应namespace中的方法名

parameterType：参数类型，如果参数类型为实体类型，则可以在SQL的参数中访问实体类属性

EAS_SaleMapper接口类

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;

import java.util.List;

public interface EAS_SaleMapper {
    int deleteEAS_SaleByShopId(String shopId);
}

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <delete id="deleteEAS_SaleByShopId" parameterType="String">
        DELETE FROM QXINTERFACE.SALE_CWJK
        WHERE shopid=#{shopId}
    </delete>
</mapper>

测试代码

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;
import com.xjbh.uitls.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class EAS_SaleMapperTest {
    @Test
    public void deleteEAS_SaleByShopIdTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);
        mapper.deleteEAS_SaleByShopId("D011");

        // CUD语句需要提交事务
        sqlSession.commit();
        sqlSession.close();
    }
}

3.6 如果参数过多，可以使用`Map`集合较灵活

id：对应namespace中的方法名

parameterType：参数类型，如果参数类型为实体类型，则可以在SQL的参数中访问实体类属性

EAS_SaleMapper接口类

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;

import java.util.List;

public interface EAS_SaleMapper {
    List<EAS_Sale> getSaleListByMap(Map<String, Object> map);
}

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <select id="getSaleListByMap" resultType="com.xjbh.entity.EAS_Sale" parameterType="map">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE shopid = #{mapShopId}
              AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>
</mapper>

测试代码

package com.xjbh.mapper;

import com.xjbh.entity.EAS_Sale;
import com.xjbh.uitls.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class EAS_SaleMapperTest {
    @Test
    public void getSaleListByMapTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        EAS_SaleMapper mapper = sqlSession.getMapper(EAS_SaleMapper.class);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("mapShopId", "D011");
        List<EAS_Sale> saleList = mapper.getSaleListByMap(map);

        for (EAS_Sale eas_sale : saleList) {
            System.out.println(eas_sale);
        }
        sqlSession.close();
    }
}

4. 配置解析

4.1 核心配置文件

MyBatis的配置文件包含了会深深影响MyBatis行为的设置和属性信息。配置文档的顶层结构如下：

configuration（配置）
- properties（属性）
- settings（设置）
- typeAliases（类型别名）
- typeHandlers（类型处理器）
- objectFactory（对象工厂）
- plugins（插件）
- environments（环境配置）
  - environment（环境变量）
    - transactionManager（事务管理器）
    - dataSource（数据源）
- databaseIdProvider（数据库厂商标识）
- mappers（映射器）

4.2 环境配置（`environments`）

MyBatis可以配置成适应多种环境，不过要记住：尽管可以配置多个环境，但每个SqlSessionFactory实例只能选择一种环境。

MyBatis默认transactionManager事务管理器类型：JDBC
MyBatis默认dataSource 数据源类型：POOLED

4.3 属性（`properties`）

这些属性可以在外部进行配置，并可以进行动态替换。你既可以在典型的Java属性文件(db.properties)中配置这些属性，也可以在properties元素的子元素中设置

编写一个db.properties属性文件

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@59.51.66.142:21521:XJBHINFO
username=qxinterface
password=qx$interface#0112

配置核心MyBatis-config.xml文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <properties resource="db.properties" />
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@59.51.66.142:21521:XJBHINFO"/>
                <property name="username" value="qxinterface"/>
                <property name="password" value="qx$interface#0112"/>
            </dataSource>
        </environment>
    </environments>
    
    <!--每一个mapper都必须在MyBatis的核心配置文件中进行mapper注册-->
    <mappers>
        <mapper resource="com/xjbh/mapper/EAS_SaleMapper.xml"/>
    </mappers>
</configuration>

PS注意：

可以直接引入外部文件
可以在其中增加一些属性配置
如果两个文件有同一个属性，优先使用外部配置文件的

4.4 类型别名（`typeAliases`）

类型别名可为Java类型设置一个缩写名字
它仅用于XML配置，意在降低冗余的全限定类名书写

类型别名的两种方式

全限定类名指定别名

核心MyBatis-config.xml文件

<typeAliases>
    <typeAlias type="com.xjbh.entity.EAS_Sale" alias="EAS_Sale"/>
</typeAliases>

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <select id="getSaleListByMap" resultType="EAS_Sale" parameterType="map">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE shopid = #{mapShopId}
              AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>
</mapper>

指定一个包名，每一个在包中的Java Bean，在没有注解的情况下，会使用Bean的首字母小写的非限定类名来作为它的别名

核心MyBatis-config.xml文件

<typeAliases>
    <package name="com.xjbh.entity" />
</typeAliases>

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <select id="getSaleListByMap" resultType="eAS_Sale" parameterType="map">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE shopid = #{mapShopId}
              AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>
</mapper>

实体类较少的情况下建议使用第一种，可以DIY别名

实体类较多的情况下建议使用第二种，在不使用注解的情况下不能DIY别名，如若想只用，如下配置

EAS_Sale实体类

package com.xjbh.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.type.Alias;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Alias("EASSALES")
public class EAS_Sale {
    private String sDate;
    private String shopId;
    private int manageDeptId;
    private double saleValue;
    private double saleTaxRate;
    private int flag;
    private int venderId;
}

核心MyBatis-config.xml文件

<typeAliases>
    <package name="com.xjbh.entity" />
</typeAliases>

EAS_SaleMapper.xml接口配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace中的包名必须与mapper接口保持一致-->
<mapper namespace="com.xjbh.mapper.EAS_SaleMapper">
    <select id="getSaleListByMap" resultType="EASSALES" parameterType="map">
        SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
        FROM QXINTERFACE.SALE_CWJK
        WHERE shopid = #{mapShopId}
              AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
        ORDER BY sdate
    </select>
</mapper>

4.5 设置（`settings`）

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

设置名	描述	有效值	默认值
cacheEnabled	全局性地开启或关闭所有映射器配置文件中已配置的任何缓存。	true \| false	true
lazyLoadingEnabled	延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。特定关联关系中可通过设置 `fetchType` 属性来覆盖该项的开关状态。	true \| false	false
logImpl	指定 MyBatis 所用日志的具体实现，未指定时将自动查找。	SLF4J \| LOG4J \| LOG4J2 \| JDK_LOGGING \| COMMONS_LOGGING \| STDOUT_LOGGING \| NO_LOGGING	未设置

4.6 映射器（`mappers`）

方法一：使用相对于类路径的资源引用（推荐使用）

<!-- 使用相对于类路径的资源引用 -->
<mappers>
  <mapper resource="com/xjbh/mapper/EAS_SaleMapper.xml"/>
</mappers>

方法二：使用映射器接口实现类的完全限定类名

<!-- 使用映射器接口实现类的完全限定类名 -->
<mappers>
  <mapper class="com.xjbh.mapper.EAS_SaleMapper"/>
</mappers>

方法三：将包内的映射器接口实现全部注册为映射器

<!-- 将包内的映射器接口实现全部注册为映射器 -->
<mappers>
  <package name="org.xjbh.mapper"/>
</mappers>

注意点：如使用方法二或方法三的情况下，接口和Mapper.xml配置文件必须同名且必须在同一个包下

4.7 作用域（`Scope`）和生命周期

作用域和生命周期类别是至关重要的，因为错误的使用会导致非常严重的并发问题

SqlSessionFactoryBuilder
- 一旦创建了 SqlSessionFactory，就不再需要它了
- 最佳作用域是方法作用域（也就是局部方法变量）
SqlSessionFactory
- 一旦被创建就应该在应用的运行期间一直存在，没有任何理由丢弃它或重新创建另一个实例
- 最佳作用域是应用作用域
SqlSession
- SqlSession 的实例不是线程安全的，因此是不能被共享的
- 最佳的作用域是请求或方法作用域
- 这个关闭操作很重要，为了确保每次都能执行关闭操作，你应该把这个关闭操作放到finally块中

5. `ResultMap`

ResultMap 元素是 MyBatis 中最重要最强大的元素

ResultMap的设计思想是，对简单的语句做到零配置，对于复杂一点的语句，只需要描述语句之间的关系就行

问题：解决属性名与字段名不一致的情况

EAS_Sale.java实体类

package com.xjbh.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.type.Alias;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Alias("EASSALES")
public class EAS_Sale {
    private String sDate;
    private String shopId;
    private int manageDeptId;
    private double saleValue;
    private double saleTaxRate;
    private int flag;
    private int venderId;
}

EAS_SaleMapper.xml配置文件

<select id="getSaleListByShopId" resultType="EASSALES" parameterType="String">
    SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
    FROM QXINTERFACE.SALE_CWJK
    WHERE shopid = #{shopId}
    AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
    ORDER BY sdate
</select>

数据库中的表字段

SELECT sdate, shopId, deptId, saleValues, saleTaxrate, flag, venderId 
FROM QXINTERFACE.SALE_CWJK

结果：会出现类别和销售为空

解决方法：

方法一：对SQL字段名设置别名：

SELECT sdate, shopId, deptId AS managedeptid, saleValues AS saleValue, saleTaxrate, flag, venderId 
FROM QXINTERFACE.SALE_CWJK

方法二：配置ResultMap即可

<resultMap id = "ESale" type="EAS_SALE">
    <!-- column是数据库字段名，property是属性名 -->
	<result column="sdate" property="sdate" />
    <result column="shopId" property="shopId" />
    <result column="deptId" property="managedeptid" />
    <result column="saleValues" property="saleValue" />
    <result column="saleTaxrate" property="saleTaxrate" />
    <result column="flag" property="flag" />
    <result column="venderId" property="venderId" />
</resultMap>

<select id="getSaleListByShopId" resultMap="ESale" parameterType="String">
    SELECT sdate, shopid, managedeptid, salevalue, saletaxrate, flag, venderid
    FROM QXINTERFACE.SALE_CWJK
    WHERE shopid = #{shopId}
    AND sdate = TO_DATE('2021-10-11', 'yyyy-MM-dd')
    ORDER BY sdate
</select>

6. `log4j`

6.1 导入`pom.xml`依赖

<dependencies>
  <dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-api</artifactId>
    <version>2.14.1</version>
  </dependency>
  <dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-core</artifactId>
    <version>2.14.1</version>
  </dependency>
</dependencies>

6.2 `log4j.properties`配置文件

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

6.3 配置`log4j`为日志实现

<settings>
    <setting name="logImpl" value="LOG4J" />
</settings>

6.4 简单使用

6.4.1 导入`Log4j`的包

import org.apache.log4j.Logger;

6.4.2 获取一个日志对象，参数为当前类的`class`

static Logger logger = Logger.getLogger(Log4jDemoTest.class);

6.4.3 常用日志级别（由低到高）

DEBUG：指出细粒度信息事件对调试应用程序是非常有帮助的，就是输出debug的信息
INFO：表明消息在粗粒度级别上突出强调应用程序的运行过程，就是输出提示信息
WARN：表明会出现潜在错误的情形,就是显示警告信息
ERROR：指出虽然发生错误事件，但仍然不影响系统的继续运行，就是显示错误信息
FATAL：指出每个严重的错误事件将会导致应用程序的退出

7. 分页

7.1 使用`SQL LIMIT`分页

SELECT * FROM Table LIMIT startIndex, pageSize;

UserMapper接口

public interface AccountMapper {
    List<User> getAccountByLimit(Map<Stirng, Integer> map);
}

UserMapper.xml映射

<resultMap id = "accountMap" type="Account">
    <!-- column是数据库字段名，property是属性名 -->
    <result column="passWord" property="pwd" />
</resultMap>

<select id="getAccountByLimit" resultMap="accountMap" parameterType="map">
    SELECT id, userId, passWord 
    FROM Account
    Limit #{startIndex}, #{pageSize}
</select>

测试

@Test
public void getAccountByLimitTest() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    EAS_SaleMapper mapper = sqlSession.getMapper(AccountMapper.class);

    Map<String, Integer> map = new HashMap<String, Integer>();
    map.put("startIndex", 0);
    map.put("pageSize", 2);
    List<Account> accountList = mapper.getAccountByLimit(map);

    for (Account account : accountList) {
        System.out.println(account);
    }
    sqlSession.close();
}

7.2 使用`RowBounds`分页

UserMapper接口

public interface AccountMapper {
    List<User> getAccountByRowBounds();
}

UserMapper.xml映射

<resultMap id = "accountMap" type="Account">
    <!-- column是数据库字段名，property是属性名 -->
    <result column="passWord" property="pwd" />
</resultMap>

<select id="getAccountByRowBounds" resultMap="accountMap">
    SELECT id, userId, passWord 
    FROM Account
</select>

测试

@Test
public void getAccountByLimitTest() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    RowBounds rowBounds = new RowBounds(0, 2);
    AccountMapper mapper = sqlSession.selectList("com.xjbh.mapper.AccountMapper.getAccountByRowBounds", null, rowBounds);
    
    List<Account> accountList = mapper.getAccountByRowBounds();

    for (Account account : accountList) {
        System.out.println(account);
    }
    sqlSession.close();
}

7.3 `MyBatis`分页插件`PageHelper`

官网地址：https://pagehelper.github.io/

了解即可

8. 使用注解开发

注解方法：

@select("")：查询SQL
@insert("")：增加SQL
@update("")：修改SQL
@delete("")：删除SQL

注解参数：

@Param
- 基本类型或String类型的参数需要加上注解，其余引用类型不需要加
- 如果只有一个基本类型可以忽略，但建议加上
- 在SQL中引用的参数为注解中的参数

UserMapper接口

public interface AccountMapper {
    @select("SELECT id, name, passWord FROM Account WHERE id = #{accId}")
    Account getAccountById(@Param("accId") int id);
}

核心配置文件mybatisc-config.xml

<?xml version="1.0" encoding="UTF8" ?>
<!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="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@59.51.66.142:21521:XJBHINFO"/>
                <property name="username" value="qxinterface"/>
                <property name="password" value="qx$interface#0112"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper class="com.xjbh.dao.AccountMapper"/>
    </mappers>
</configuration>

测试

@Test
public void getAccountByLimitTest() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    AccountMapper mapper = sqlSession.getMapper(AccountMapper.class);
    
    Account account = mapper.getAccountById(1);
    System.out.println(account);
    
    sqlSession.close();
}

9. 多对一处理

9.1 环境搭建

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.xjbh</groupId>
    <artifactId>MyBatisStudy</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>MyBatis-02</module>
    </modules>

    <dependencies>
        <!-- Junit测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--MySQL JDBC驱动 依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <!--MyBatis 依赖-->
        <dependency>
            <groupId>app.myoss.cloud.mybatis</groupId>
            <artifactId>myoss-mybatis</artifactId>
            <version>2.1.7.RELEASE</version>
        </dependency>
        <!--Lombok 依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
</project>

mybatis-config.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="mysql.properties" />

    <settings>
        <setting name="logImpl" value="NO_LOGGING"/>
    </settings>
    
    <typeAliases>
        <typeAlias alias="account" type="com.xjbh.pojo.Account" />
        <typeAlias alias="teacher" type="com.xjbh.pojo.Teacher" />
        <typeAlias alias="student" type="com.xjbh.pojo.Student" />
    </typeAliases>
    
    <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="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.xjbh.dao"/>
    </mappers>
</configuration>

MyBatisUitl.java

package com.xjbh.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

POJO类

Account

package com.xjbh.pojo;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
    private int id;
    private String accId;
    private String passWord;
    private String notes;
}

Student

package com.xjbh.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private Integer id;
    private String name;
    private Teacher teacher;
    private String notes;
}

Teacher

package com.xjbh.pojo;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private Integer id;
    private String name;
    private String notes;
}

StudentMapper接口

package com.xjbh.dao;

import com.xjbh.pojo.Student;
import com.xjbh.pojo.Teacher;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface StudentMapper {
    List<Student> getStudentList();
}

9.2 按查询嵌套处理

StudentMapper映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.StudentMapper">
    <resultMap id="studentMaps" type="student">
        <!--复杂查询如果是对象则使用association，如果是集合则使用collection-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacherById" />
    </resultMap>
    <select id="getStudentList" resultMap="studentMaps">
        SELECT s.id,
               s.name,
               s.tid,
               s.notes
        FROM student s
    </select>
    <select id="getTeacherById" parameterType="int" resultType="teacher">
        SELECT t.id,
               t.name,
               t.notes
        FROM teacher t
        WHERE t.id = #{tid}
    </select>
</mapper>

9.3 按结果嵌套处理

StudentMapper映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.StudentMapper">
    <!--按结果嵌套处理-->
    <select id="getStudentList" resultMap="studentMaps">
        SELECT s.id AS studentId,
               s.name AS studentName,
               s.notes AS studentNotes,
               s.tid AS teacherId,
               t.name AS teacherName,
               t.notes AS teacherNotes
        FROM student s, teacher t
        WHERE s.tid = t.id
    </select>
    <resultMap id="studentMaps" type="student">
        <result property="id" column="studentId"/>
        <result property="name" column="studentName"/>
        <result property="notes" column="studentNotes"/>
        <association property="teacher" javaType="teacher">
            <result property="id" column="teacherId" />
            <result property="name" column="teacherName" />
            <result property="notes" column="teacherNotes" />
        </association>
    </resultMap>
</mapper>

10. 一对多处理

10.1 环境搭建

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.xjbh</groupId>
    <artifactId>MyBatisStudy</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>MyBatis-02</module>
    </modules>

    <dependencies>
        <!-- Junit测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--MySQL JDBC驱动 依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <!--MyBatis 依赖-->
        <dependency>
            <groupId>app.myoss.cloud.mybatis</groupId>
            <artifactId>myoss-mybatis</artifactId>
            <version>2.1.7.RELEASE</version>
        </dependency>
        <!--Lombok 依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
</project>

mybatis-config.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="mysql.properties" />

    <settings>
        <setting name="logImpl" value="NO_LOGGING"/>
    </settings>
    
    <typeAliases>
        <typeAlias alias="account" type="com.xjbh.pojo.Account" />
        <typeAlias alias="teacher" type="com.xjbh.pojo.Teacher" />
        <typeAlias alias="student" type="com.xjbh.pojo.Student" />
    </typeAliases>
    
    <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="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.xjbh.dao"/>
    </mappers>
</configuration>

MyBatisUitl.java

package com.xjbh.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

POJO类

Student类

package com.xjbh.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private Integer id;
    private String name;
    private Integer tid;
    private String notes;
}

Teacher类

package com.xjbh.pojo;

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

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private Integer id;
    private String name;
    private String notes;
    private List<Student> studentList;
}

TeacherMapper接口

package com.xjbh.dao;

import com.xjbh.pojo.Teacher;
import org.apache.ibatis.annotations.Param;

public interface TeacherMapper {
    Teacher getTeacherById(@Param("tid") Integer id);
}

10.2 按结果嵌套处理

TeacherMapper.xml映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.TeacherMapper">
    <select id="getTeacherById" resultMap="teacherStudent" parameterType="int">
        SELECT a.id AS studentId,
               a.name AS studentName,
               a.notes as studentNotes,
               a.tid AS teacherId,
               b.name AS teacherName,
               b.notes AS teacherNotes
        FROM student a, teacher b
        WHERE a.tid = b.id
              AND a.tid = #{tid}
        ORDER BY a.id;
    </select>

    <resultMap id="teacherStudent" type="teacher">
        <result property="id" column="teacherId"/>
        <result property="name" column="teacherName"/>
        <result property="notes" column="teacherNotes"/>
        <collection property="studentList" ofType="student">
            <result property="id" column="studentId"/>
            <result property="name" column="studentName"/>
            <result property="tid" column="teacherId"/>
            <result property="notes" column="studentNotes"/>
        </collection>
    </resultMap>
</mapper>

11. 多对一与一对多的总结

关联 - association 【多对一】
集合 - collection 【一对多】
javaType & ofType
- javaType：用来指定实体类中属性的类型
- ofType：用来指定List或集合中的POJO类型，泛型中的约束类型
注意点：
- 保证SQL的可读性，尽量保证通俗易懂
- 注意一对多和多对一中属性名和字段的问题
- 如果问题不好排查错误，可以使用日志

12. 动态`SQL`

12.1 什么是动态`SQL`

根据不同的条件生成不同的SQL代码

12.2 搭建环境

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.com.xjbh</groupId>
    <artifactId>MyBatisStudy</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>MyBatis-02</module>
        <module>MyBatis-03</module>
        <module>MyBatis-04</module>
    </modules>

    <dependencies>
        <!-- Junit测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--MySQL JDBC驱动 依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <!--MyBatis 依赖-->
        <dependency>
            <groupId>app.myoss.cloud.mybatis</groupId>
            <artifactId>myoss-mybatis</artifactId>
            <version>2.1.7.RELEASE</version>
        </dependency>
        <!--Lombok 依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
</project>

mybatis-config.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="mysql.properties" />

    <settings>
        <setting name="logImpl" value="NO_LOGGING"/>
    </settings>

    <typeAliases>
        <typeAlias alias="student" type="com.xjbh.pojo.Student" />
    </typeAliases>
    
    <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="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/xjbh/dao/StudentMapper.xml"/>
    </mappers>
</configuration>

MyBatisUtil

package com.xjbh.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

POJO类

Student

package com.xjbh.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private Integer id;
    private String name;
    private Integer tid;
    private String notes;
}

12.3 `IF`

StudentMapper接口

package com.xjbh.dao;

import com.xjbh.pojo.Student;

import java.util.List;
import java.util.Map;

public interface StudentMapper {
    List<Student> getStudentByIF(Map<String, Object> map);
}

StudentMapper.xml映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.StudentMapper">
    <select id="getStudentByIF" resultType="student" parameterType="map">
        SELECT id, name, tid, notes
        FROM Student
        WHERE 1 = 1
        <if test="name != null">
            AND name = #{name}
        </if>
        <if test="id != null">
            AND id = #{id}
        </if>
    </select>
</mapper>

测试代码

package com.xjbh.dao;

import com.xjbh.pojo.Student;
import com.xjbh.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StudentTest {

    @Test
    public void getStudentByIFTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Map map = new HashMap<String, Object>();
        map.put("id", 2);
        map.put("name", "迪丽热巴");
        List<Student> studentList = mapper.getStudentByIF(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

12.4 `choose`、`when`、`otherwise`

StudentMapper接口

package com.xjbh.dao;

import com.xjbh.pojo.Student;

import java.util.List;
import java.util.Map;

public interface StudentMapper {
    List<Student> getStudentByChoose(Map<String, Object> map);
}

StudentMapper.xml映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.StudentMapper">
    <select id="getStudentByChoose" resultType="student" parameterType="map">
        SELECT id, name, tid, notes
        FROM Student
        WHERE 1 = 1
        <choose>
            <when test="id != null">
                AND id = #{id}
            </when>
            <when test="name != null">
                AND name = #{name}
            </when>
            <otherwise>
                AND notes = #{notes}
            </otherwise>
        </choose>
    </select>
</mapper>

测试代码

package com.xjbh.dao;

import com.xjbh.pojo.Student;
import com.xjbh.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StudentTest {

    @Test
    public void getStudentByChooseTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Map map = new HashMap<String, Object>();
        map.put("id", 1);
        map.put("name", "迪丽热巴");
        map.put("notes", "LOL");
        List<Student> studentList = mapper.getStudentByChoose(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

注意：从上至下满足其中之一条件后，后续代码就算满足条件也不会执行

12.5 `where`

StudentMapper接口

package com.xjbh.dao;

import com.xjbh.pojo.Student;

import java.util.List;
import java.util.Map;

public interface StudentMapper {
    List<Student> getStudentByWhere(Map<String, Object> map);
}

StudentMapper.xml映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.StudentMapper">
    <select id="getStudentByWhere" resultType="student" parameterType="map">
        SELECT id, name, tid, notes
        FROM Student
        <where>
            <if test="name != null">
                name = #{name}
            </if>
            <if test="id != null">
                AND id = #{id}
            </if>
            <if test="notes != null">
                AND notes = #{notes}
            </if>
        </where>
    </select>
</mapper>

测试代码

package com.xjbh.dao;

import com.xjbh.pojo.Student;
import com.xjbh.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StudentTest {
    @Test
    public void getStudentByWhereTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Map map = new HashMap<String, Object>();
        map.put("id", 1);
        List<Student> studentList = mapper.getStudentByWhere(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

总结：WHERE元素只会在子元素返回任何内容的情况下才插入WHERE子句。而且，若子句的开头为AND或OR，WHERE元素也会将它们去除

12.5 `set`

StudentMapper接口

package com.xjbh.dao;

import com.xjbh.pojo.Student;

import java.util.List;
import java.util.Map;

public interface StudentMapper {
    int updateStudentBySet(Map<String, Object> map);
}

StudentMapper.xml映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.StudentMapper"><update id="updateStudentBySet" parameterType="map">
        UPDATE student
        <set>
            <if test="name != null">
                name = #{name},
            </if>
            <if test="tid != null">
                tid = #{tid},
            </if>
            <if test="notes != null">
                notes = #{notes},
            </if>
        </set>
        WHERE id = #{id}
    </update>
</mapper>

测试代码

package com.xjbh.dao;

import com.xjbh.pojo.Student;
import com.xjbh.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StudentTest {
    @Test
    public void getStudentBySetTest() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Map map = new HashMap<String, Object>();
        map.put("notes", "小姐姐");
        map.put("id", 1);
        mapper.updateStudentBySet(map);
        sqlSession.commit();
        sqlSession.close();
    }
}

总结：set元素可以用于动态包含需要更新的列，忽略其它不更新的列

12.6 `foreach`

StudentMapper接口

package com.xjbh.dao;

import com.xjbh.pojo.Student;

import java.util.List;
import java.util.Map;

public interface StudentMapper {
    List<Student> getStudentByForeach(Map<String, Object> map);
}

StudentMapper.xml映射

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xjbh.dao.StudentMapper">
    <select id="getStudentByForeach" resultType="student" parameterType="map">
        SELECT id, name, tid, notes
        FROM Student
        <where>
            <if test="idList != null and idList.size > 0">
                id IN
                <foreach collection="idList" open="(" item="id" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

测试代码

package com.xjbh.dao;

import com.xjbh.pojo.Student;
import com.xjbh.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StudentTest {
    @Test
    public void getStudentByForeach() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Map<String, Object> map = new HashMap<String, Object>();
        ArrayList arrayList = new ArrayList();
        arrayList.add(1);
        arrayList.add(3);
        map.put("idList", arrayList);
        List<Student> studentList = mapper.getStudentByForeach(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

13. 缓存

13.1 缓存简介

13.1.1 什么是缓存？

存在内存中的临时数据
将用户经常查询的数据存放在缓存中，用户去查询数据就不用从磁盘上（关系型数据库数据文件）查询，从缓存中查询，从而提高查询效率，解决了高并发系统的性能问题

13.1.2 为什么使用缓存

减少和数据库的交互次数，减少系统开销，提高系统效率

13.1.3 什么样的数据能使用缓存

经常查询并且不经常改变的数据

13.2 `MyBatis`缓存

MyBatis内置了一个强大的事务性查询缓存机制，它可以非常方便地配置和定制
MyBatis系统中默认定义了两级缓存：一级缓存和二级缓存
- 默认情况下，只有一级缓存（SqlSession级别的缓存，也称为本地缓存）
- 二级缓存需要手动开启和配置，它是基于namespace级别的缓存（Mapper）
- 为了提高扩展性，MyBatis定义了缓存接口Cache，我们可以通过实现Cache接口来自定义二级缓存

13.3 一级缓存

一级缓存也叫本地缓存

与数据库痛一次会话期间查询到的数据会放在本地缓存中
以后如果需要获取相同的数据，直接从缓存中拿，没有必要再去查询数据库

13.4 二级缓存

二级缓存也叫全局缓存，一级缓存作用域太低了，所以诞生了二级缓存

基于namespace级别的缓存，一个命名空间，对应一个二级缓存
工作机制
- 一个会话查询一条数据，这个数据就会被放在当前会话的一级缓存中
- 如果当前会话关闭了，这个会话对应的一级缓存就没了，但是我们想要的是，会话关闭了，一级缓存中的数据被保存到二级缓存中
- 新的会话查询信息，就可以从二级缓存中获取内容
- 不同的Mapper查询出的数据会放在自己对应的缓存中
步骤
- 在mybatis-config.xml中配置开启全局缓存
```
<settings>
    <setting name="cacheEnabled" value="true"/>
</settings>
```
- 在对应的Mapper.xml映射文件中配置
  - eviction：清除策略，默认的清除策略是LRU
    - LRU – 最近最少使用：移除最长时间不被使用的对象
    - FIFO – 先进先出：按对象进入缓存的顺序来移除它们
    - SOFT – 软引用：基于垃圾回收器状态和软引用规则移除对象
    - WEAK – 弱引用：更积极地基于垃圾收集器状态和弱引用规则移除对象
  - flushInterval：刷新间隔，设置为任意正整数。默认情况是不设置
  - size：引用数目，设置为任意正整数。默认值是 1024
  - readOnly：只读属性，只读的缓存会给所有调用者返回缓存对象的相同实例。默认值是false
```

<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
```

posted @ 2021-11-11 09:54 爱Ni说不出口阅读(32) 评论(0) 收藏举报

刷新页面返回顶部

停雨在心

MyBatis

MyBatis

1. 简介

1.1 什么是MyBatis

1.2 MyBatis相关网址链接

1.3 数据持久化

1.4 为什么需要使用MyBatis

2. 第一个MyBatis程序

2.1 搭建MySQL/Oracle数据库

2.2 搭建Maven项目环境

2.2.1 导入POM依赖

2.2.2 在resources文件夹下创建一个文件，命名为：mybatis-config.xml

2.2.3 编写代码

2.2.4 测试

3. CRUD语句

3.1 namespace

3.2 Create

3.3 Retrieve

3.4 Update

3.5 Delete

3.6 如果参数过多，可以使用Map集合较灵活

4. 配置解析

4.1 核心配置文件

4.2 环境配置（environments）

4.3 属性（properties）

4.4 类型别名（typeAliases）

4.5 设置（settings）

4.6 映射器（mappers）

4.7 作用域（Scope）和生命周期

5. ResultMap

6. log4j

6.1 导入pom.xml依赖

6.2 log4j.properties配置文件

6.3 配置log4j为日志实现

6.4 简单使用

6.4.1 导入Log4j的包

6.4.2 获取一个日志对象，参数为当前类的class

6.4.3 常用日志级别（由低到高）

7. 分页

7.1 使用SQL LIMIT分页

7.2 使用RowBounds分页

7.3 MyBatis分页插件PageHelper

8. 使用注解开发

9. 多对一处理

9.1 环境搭建

9.2 按查询嵌套处理

9.3 按结果嵌套处理

10. 一对多处理

10.1 环境搭建

10.2 按结果嵌套处理

11. 多对一与一对多的总结

12. 动态SQL

12.1 什么是动态SQL

12.2 搭建环境

12.3 IF

12.4 choose、when、otherwise

12.5 where

12.5 set

12.6 foreach

13. 缓存

13.1 缓存简介

13.1.1 什么是缓存？

13.1.2 为什么使用缓存

13.1.3 什么样的数据能使用缓存

13.2 MyBatis缓存

13.3 一级缓存

13.4 二级缓存

1.1 什么是`MyBatis`

1.2 `MyBatis`相关网址链接

1.4 为什么需要使用`MyBatis`

2. 第一个`MyBatis`程序

2.1 搭建`MySQL/Oracle`数据库

2.2 搭建`Maven`项目环境

2.2.1 导入`POM`依赖

2.2.2 在`resources`文件夹下创建一个文件，命名为：`mybatis-config.xml`

3. `CRUD`语句

3.1 `namespace`

3.2 `Create`

3.3 `Retrieve`

3.4 `Update`

3.5 `Delete`

3.6 如果参数过多，可以使用`Map`集合较灵活

4.2 环境配置（`environments`）

4.3 属性（`properties`）

4.4 类型别名（`typeAliases`）

4.5 设置（`settings`）

4.6 映射器（`mappers`）

4.7 作用域（`Scope`）和生命周期

5. `ResultMap`

6. `log4j`

6.1 导入`pom.xml`依赖

6.2 `log4j.properties`配置文件

6.3 配置`log4j`为日志实现

6.4.1 导入`Log4j`的包

6.4.2 获取一个日志对象，参数为当前类的`class`

7.1 使用`SQL LIMIT`分页

7.2 使用`RowBounds`分页

7.3 `MyBatis`分页插件`PageHelper`

12. 动态`SQL`

12.1 什么是动态`SQL`

12.3 `IF`

12.4 `choose`、`when`、`otherwise`

12.5 `where`

12.5 `set`

12.6 `foreach`

13.2 `MyBatis`缓存