MyBatis辟邪剑谱

一 MyBatis简介

MyBatis是一个优秀的持久层框架它对JDBC操作数据库的过程进行封装开发者只需要关注SQL本身而不需要花费精力去处理JDBC繁杂的过程代码

MyBatis将要执行的各种Statement配置起来并通过Java对象和Statement中的SQL进行映射生成最终执行的SQL语句最后由MyBatis执行SQL并将结果映射成Java对象返回

二 MyBatis优缺点

优点

1. 通过直接编写SQL语句可以直接对SQL进行性能优化

2. 学习门槛低学习成本低只要有SQL基础就可以学习MyBatis 而且很容易上手

3. 由于直接编写SQL语句所以灵活多变代码维护性更好

缺点

1. 不支持数据库无关性即数据库发生变更要写多套代码进行支持移植性不好比如分页关键字

2. 需要编写结果映射

三 MyBatis框架核心

1. MyBatis配置文件包括MyBatis全局配置文件和MyBatis映射文件其中全局配置文件配置了数据源事务等信息映射文件配置了SQL执行相关的信息

2. MyBatis通过读取配置文件构造出SqlSessionFactory 即会话工厂

3. 通过SqlSessionFactory 可以创建SqlSession 即会话 MyBatis是通过SqlSession来操作数据库

4. SqlSession本身不能直接操作数据库它是通过底层的Executor执行器接口来操作数据库 Executor接口有两个实现类一个是普通执行器一个是缓存执行器(默认)

5. Executor执行器要处理的SQL信息是封装到一个底层对象MappedStatement中该对象包括SQL语句输入参数映射信息输出结果映射信息其中输入参数和输出结果的映射类型包括HashMap集合对象 POJO对象类型

四 MyBatis初体验

1. 新建一个Maven项目

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.hy.mybatis</groupId>
    <artifactId>mybatis-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!-- 定义依赖版本号 -->
    <properties>
        <junit.version>4.12</junit.version>
        <slf4j-log4j12.version>1.7.25</slf4j-log4j12.version>
        <mysql-connector-java.version>8.0.11</mysql-connector-java.version>
        <mybatis.version>3.4.6</mybatis.version>
    </properties>

    <!-- 管理jar版本号 -->
    <dependencyManagement>
        <dependencies>
            <!-- junit -->
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>${junit.version}</version>
            </dependency>
            <!-- slf4j -->
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-log4j12</artifactId>
                <version>${slf4j-log4j12.version}</version>
            </dependency>
            <!-- mysql -->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql-connector-java.version}</version>
            </dependency>
            <!-- mybatis -->
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>${mybatis.version}</version>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <!-- junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
        <!-- slf4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <!-- 编译 -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.7.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

sql

-- 用户表
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',
    username VARCHAR(32) COMMENT '用户名',
    money DOUBLE COMMENT '用户余额'
);

INSERT INTO user VALUES (1, '曹操', 8000);
INSERT INTO user VALUES (2, '孙权', 8000);
INSERT INTO user VALUES (3, '刘备', 8000);
INSERT INTO user VALUES (4, '诸葛亮', 5000);
INSERT INTO user VALUES (5, '司马懿', 5000);
INSERT INTO user VALUES (6, '张飞', 0);
INSERT INTO user VALUES (7, '关羽', 0);
INSERT INTO user VALUES (8, '马超', 1000);
INSERT INTO user VALUES (9, '黄忠', 1000);
INSERT INTO user VALUES (10, '赵云', 3000);

-- 订单表
CREATE TABLE order_ (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单id',
    create_date DATETIME COMMENT '订单创建时间',
    note VARCHAR(100) COMMENT '订单备注',
    uid INT COMMENT '用户id'
);
ALTER TABLE order_ ADD CONSTRAINT order_fk FOREIGN KEY (uid) REFERENCES user (id);

INSERT INTO order_ VALUES (3, '2015-02-04 13:22:35', '配送快一点!', 1);
INSERT INTO order_ VALUES (4, '2015-02-03 13:22:41', NULL, 1);
INSERT INTO order_ VALUES (5, '2015-02-12 16:13:23', NULL, 10);

-- 商品表
CREATE TABLE item (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品id',
    name VARCHAR(32) COMMENT '商品名',
    price DOUBLE COMMENT '商品价格',
    detail VARCHAR(100) COMMENT '订单描述'
);

INSERT INTO item VALUES (1, '台式机', 3000.0, '该电脑质量非常好');
INSERT INTO item VALUES (2, '笔记本', 6000.0, '垃圾货色');
INSERT INTO item VALUES (3, '背包', 200.0, '名牌背包');

-- 订单商品关系表
CREATE TABLE order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
    count INT COMMENT '购买数量',
    oid INT COMMENT '订单id',
    iid INT COMMENT '商品id'
);
ALTER TABLE order_detail ADD CONSTRAINT order_detail_order_fk FOREIGN KEY (oid) REFERENCES order_ (id);
ALTER TABLE order_detail ADD CONSTRAINT order_detail_item_fk FOREIGN KEY (iid) REFERENCES item (id);

INSERT INTO order_detail VALUES (1, 1, 3, 1);
INSERT INTO order_detail VALUES (2, 2, 3, 2);
INSERT INTO order_detail VALUES (3, 3, 4, 3);
INSERT INTO order_detail VALUES (4, 4, 4, 2);


-- 一个用户可以创建多个订单 用户表和订单表是一对多的关系
-- 一个订单可以包含多个商品 一个商品可以拥有多个订单 订单表和商品表是多对多的关系

2. 配置日志输出 resources/log4j.properties

### 输出到控制台 ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### 输出到文件 ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=//Users/HUANGYI/Downloads/x-log.txt
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### 日志级别: fatal > error > warn > info > debug > trace ###
### 输出到哪里: stdout|file ###
#log4j.rootLogger=info, stdout

### 输出所有日志 ###
log4j.rootLogger=all, stdout

### 不输出日志 ###
#log4j.rootLogger=off, stdout

3. 配置数据源 resources/database.properties

url=jdbc:mysql://localhost:3306/demo_hy?characterEncoding=utf8&useSSL=false
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=root

4. 创建实体类 com.hy.mybatis.model.User

public class User implements Serializable {

    private Integer id;
    private String username;
    private Double money;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", money=" + money +
                '}';
    }
}

5. 创建mapper接口 com.hy.mybatis.mapper.UserMapper

/**
 * mapper动态代理开发 MyBatis会自动为mapper接口生成动态代理实现类
 * 必须遵循四个原则
 * 1. mapper接口的全限定名要和mapper映射文件的namespace相同
 * 2. mapper接口的方法名要和mapper映射文件的statement#id相同
 * 3. mapper接口的方法参数只能有一个 且类型要和mapper映射文件的statement#parameterType相同
 * 4. mapper接口的返回值类型要和mapper映射文件的statement#resultType或statement#resultMap#type相同
 * Created by Hy on 2020/7/29.
 */
public interface UserMapper {

    User selectUserById(Integer id);

    List<User> selectUserListByUsername(String username);

    List<User> selectUserListByMoney(Map map);

    Integer selectUserCount();

    Integer insertUser(User user);

    Integer deleteUserById(Integer id);

    Integer updateUserById(User user);
}

6. 创建mapper映射文件 com.hy.mybatis.mapper.UserMapper.xml

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

<!--
    #{} 表示一个占位符 可以实现preparedStatement向占位符中设置值
    #{} 可以有效防止sql注入 可以接收简单类型值或pojo属性值 如果parameterType传输单个简单类型值 括号中可以是value或其它名称

    ${} 表示拼接sql串 可以将parameterType传入的内容拼接在sql中
    ${} 会引起sql注入(谨慎使用) 可以接收简单类型值或pojo属性值 如果parameterType传输单个简单类型值 括号中只能是value
    -->
<mapper namespace="com.hy.mybatis.mapper.UserMapper">
    <!-- id: statement#id 要求在命名空间内唯一 -->
    <!-- parameterType: statement#parameterType 入参的java类型 可以填写别名或java类的全限定名 -->
    <!-- resultType: statement#resultType 单条查询结果对应的java类型 可以填写别名或java类的全限定名 -->
    <select id="selectUserById" parameterType="Integer" resultType="User">
        SELECT *
        FROM user
        WHERE id = #{value}
    </select>

    <select id="selectUserListByUsername" parameterType="String" resultType="User">
        SELECT *
        FROM user
        WHERE username LIKE '%${value}%'
    </select>

    <select id="selectUserListByMoney" parameterType="HashMap" resultType="User">
        SELECT *
        FROM user
        LIMIT #{index}, #{count}
    </select>

    <select id="selectUserCount" resultType="Integer">
        SELECT COUNT(id)
        FROM user
    </select>

    <insert id="insertUser" parameterType="User">
        <!-- 插入数据成功后 返回MySQL自增主键 -->
        <!-- keyProperty: 指定存放生成主键的属性 -->
        <!-- resultType: 生成主键所对应的java类型 -->
        <!-- order: 指定该查询主键sql语句的执行顺序 相对于insert语句 -->
        <selectKey keyProperty="id" resultType="Integer" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO user (username, money)
        VALUES (#{username}, #{money})
    </insert>

    <delete id="deleteUserById" parameterType="Integer">
        DELETE FROM user
        WHERE id = #{value}
    </delete>

    <update id="updateUserById" parameterType="User">
        UPDATE user
        SET username = #{username}, money = #{money}
        WHERE id = #{id}
    </update>
</mapper>

7. 创建全局配置文件 resources/mybatis.xml

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

<configuration>

    <!-- 引入数据源配置文件 -->
    <properties resource="database.properties" />

    <!-- 别名 -->
    <typeAliases>
        <!-- 包指定 -->
        <package name="com.hy.mybatis.model" />
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务管理 -->
            <transactionManager type="JDBC" />
            <!-- 数据库连接池 -->
            <dataSource type="POOLED">
                <property name="url" value="${url}" />
                <property name="driver" value="${driverClassName}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <!-- mapper文件位置 -->
    <mappers>
        <!-- 包指定 -->
        <package name="com.hy.mybatis.mapper" />
    </mappers>
</configuration>

8. 测试

public class XTest {

    SqlSession mSession;

    @Before
    public void before() throws IOException {
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        // 创建会话工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建SqlSession
        mSession = factory.openSession();
    }

    @After
    public void after() {
        if (null != mSession) mSession.close(); //关闭SqlSession
    }

    @Test
    public void test01() throws IOException {
        UserMapper mapper = mSession.getMapper(UserMapper.class);
        User user = mapper.selectUserById(1);
        System.out.println(user.toString());
    }

    @Test
    public void test02() throws IOException {
        UserMapper mapper = mSession.getMapper(UserMapper.class);
        List<User> userList = mapper.selectUserListByUsername("马");
        for (User user : userList) {
            System.out.println(user.toString());
        }
    }

    @Test
    public void test03() throws IOException {
        int page = 1;
        int count = 5;
        int index = (page - 1) * count;
        Map<String, Integer> map = new HashMap<>();
        map.put("index", index);
        map.put("count", count);

        UserMapper mapper = mSession.getMapper(UserMapper.class);
        List<User> userList = mapper.selectUserListByMoney(map);
        for (User user : userList) {
            System.out.println(user.toString());
        }
    }

    @Test
    public void test04() throws IOException {
        UserMapper mapper = mSession.getMapper(UserMapper.class);
        Integer count = mapper.selectUserCount();
        System.out.println("count = " + count);
    }

    @Test
    public void test05() throws IOException {
        User user = new User();
        user.setUsername("貂蝉");
        user.setMoney(10.0);
        UserMapper mapper = mSession.getMapper(UserMapper.class);
        // 返回受影响的行数
        Integer count = mapper.insertUser(user);
        System.out.println("受影响的行数 = " + count);
        // 增删改需要提交事务
        mSession.commit();
        System.out.println("id = " + user.getId());
    }

    @Test
    public void test06() throws IOException {
        UserMapper mapper = mSession.getMapper(UserMapper.class);
        // 返回受影响的行数
        Integer count = mapper.deleteUserById(12);
        System.out.println("受影响的行数 = " + count);
        // 增删改需要提交事务
        mSession.commit();
    }

    @Test
    public void test07() throws IOException {
        User user = new User();
        user.setId(13);
        user.setUsername("黄盖");
        user.setMoney(99.9);
        UserMapper mapper = mSession.getMapper(UserMapper.class);
        // 返回受影响的行数
        Integer count = mapper.updateUserById(user);
        System.out.println("受影响的行数 = " + count);
        // 增删改需要提交事务
        mSession.commit();
    }
}

五动态SQL

<trim></trim> <if></if> <set></set> <where></where> <foreach></foreach> 使用示例

1. 创建实体类 com.hy.mybatis.model.Item

public class Item implements Serializable {

    private Integer id;
    private String name;
    private Double price;
    private String detail;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }

    @Override
    public String toString() {
        return "Item{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", detail='" + detail + '\'' +
                '}';
    }
}

2. 创建mapper接口 com.hy.mybatis.mapper.ItemMapper

public interface ItemMapper {

    Integer insertItem(Item item);

    Integer updateItem(Item item);

    List<Item> selectItemListByNameAndPrice(Item item);

    List<Item> selectItemListByIdArray(int[] array);

    List<Item> selectItemListByIdList(List list);
}

3. 创建mapper映射文件 com.hy.mybatis.mapper.ItemMapper.xml

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

<mapper namespace="com.hy.mybatis.mapper.ItemMapper">

    <insert id="insertItem" parameterType="Item">
        INSERT INTO item
        <!-- <trim></trim> 里面条件都不成立 什么也不添加 -->
        <!-- <trim></trim> 里面有任意条件成立 添加前缀 后缀 去除指定的串 -->
        <!-- prefix: 前缀 -->
        <!-- suffix: 后缀 -->
        <!-- prefixOverrides: 第一个 需要去除的串 -->
        <!-- suffixOverrides: 最后一个 需要去除的串 -->
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <!-- <if></if> 判断入参 如果条件成立 则把标签体内的sql拼接上 -->
            <if test="null != id">
                id,
            </if>
            <if test="null != name">
                name,
            </if>
            <if test="null != price">
                price,
            </if>
            <if test="null != detail">
                detail,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="null != id">
                #{id},
            </if>
            <if test="null != name">
                #{name},
            </if>
            <if test="null != price">
                #{price},
            </if>
            <if test="null != detail">
                #{detail},
            </if>
        </trim>
    </insert>

    <update id="updateItem" parameterType="Item">
        UPDATE item
        <!-- <set></set> 里面条件都不成立 什么也不添加 -->
        <!-- <set></set> 里面有任意条件成立 添加SET 并去掉最后一个逗号 -->
        <set>
            <if test="null != name">
                name = #{name},
            </if>
            <if test="null != price">
                price = #{price},
            </if>
            <if test="null != detail">
                detail = #{detail},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <select id="selectItemListByNameAndPrice" parameterType="Item" resultType="Item">
        SELECT *
        FROM item
        <!-- <where></where> 里面条件都不成立 什么也不添加 -->
        <!-- <where></where> 里面有任意条件成立 添加WHERE 并去掉第一个AND或OR -->
        <where>
            <if test="null != name and '' != name">
                AND name LIKE '%${name}%'
            </if>
            <if test="null != price and '' != price">
                AND price &lt; #{price}
            </if>
        </where>
    </select>

    <select id="selectItemListByIdArray" parameterType="Integer" resultType="Item">
        SELECT *
        FROM item
        <where>
            <if test="null != array and array.length > 0">
                <!-- collection: 数组参数名称 如果传入包装类取数组属性名 如果直接传入数组只能填写array -->
                <!-- item: 每次遍历出来的对象 -->
                <!-- separator: 每次遍历出来的对象 之间需要拼接的串 -->
                <!-- open: 开始遍历时 需要拼接的串 -->
                <!-- close: 结束遍历时 需要拼接的串 -->
                <foreach collection="array" item="value" separator="," open="AND id IN (" close=")">
                    #{value}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectItemListByIdList" parameterType="Integer" resultType="Item">
        SELECT *
        FROM item
        <where>
            <if test="null != list and list.size > 0">
                <!-- collection: 集合参数名称 如果传入包装类取集合属性名 如果直接传入集合只能填写list -->
                <!-- item: 每次遍历出来的对象 -->
                <!-- separator: 每次遍历出来的对象 之间需要拼接的串 -->
                <!-- open: 开始遍历时 需要拼接的串 -->
                <!-- close: 结束遍历时 需要拼接的串 -->
                <foreach collection="list" item="value" separator="," open="AND id IN (" close=")">
                    #{value}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

4. 测试

public class XTest {

    SqlSession mSession;

    @Before
    public void before() throws IOException {
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        // 创建会话工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建SqlSession
        mSession = factory.openSession();
    }

    @After
    public void after() {
        if (null != mSession) mSession.close(); //关闭SqlSession
    }

    @Test
    public void test09() throws IOException {
        Item item = new Item();
        item.setName("自行车");
        item.setPrice(800.0);
        ItemMapper mapper = mSession.getMapper(ItemMapper.class);
        // 返回受影响的行数
        Integer count = mapper.insertItem(item);
        System.out.println("受影响的行数 = " + count);
        // 增删改需要提交事务
        mSession.commit();
    }

    @Test
    public void test10() throws IOException {
        Item item = new Item();
        item.setId(4);
        item.setName("H2");
        item.setPrice(400000.0);
        item.setDetail("川崎");
        ItemMapper mapper = mSession.getMapper(ItemMapper.class);
        // 返回受影响的行数
        Integer count = mapper.updateItem(item);
        System.out.println("受影响的行数 = " + count);
        // 增删改需要提交事务
        mSession.commit();
    }

    @Test
    public void test11() throws IOException {
        Item item = new Item();
        item.setName("包");
        item.setPrice(300.0);
        ItemMapper mapper = mSession.getMapper(ItemMapper.class);
        List<Item> itemList = mapper.selectItemListByNameAndPrice(item);
        for (Item i : itemList) {
            System.out.println(i.toString());
        }
    }

    @Test
    public void test12() throws IOException {
        int[] array = new int[]{1, 2};
        ItemMapper mapper = mSession.getMapper(ItemMapper.class);
        List<Item> itemList = mapper.selectItemListByIdArray(array);
        for (Item i : itemList) {
            System.out.println(i.toString());
        }
    }

    @Test
    public void test13() throws IOException {
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(3);
        ItemMapper mapper = mSession.getMapper(ItemMapper.class);
        List<Item> itemList = mapper.selectItemListByIdList(list);
        for (Item i : itemList) {
            System.out.println(i.toString());
        }
    }
}

六 resultMap功能

1. 如果查询出来的列名和实体类属性名不一致通过定义一个resultMap将列名和实体类属性名绑定

a. 创建实体类 com.hy.mybatis.model.Order

public class Order implements Serializable {

    private Integer id;
    private Date createDate;
    private String note;

    public Integer getId() {
        return id;
    }

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

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", createDate=" + createDate +
                ", note='" + note + '\'' +
                '}';
    }
}

b. 创建mapper接口 com.hy.mybatis.mapper.OrderMapper

public interface OrderMapper {

    List<Order> selectOrderList();
}

c. 创建mapper映射文件 com.hy.mybatis.mapper.OrderMapper.xml

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

<mapper namespace="com.hy.mybatis.mapper.OrderMapper">

    <resultMap id="orderResultMap" type="Order">
        <id column="id" property="id" />
        <result column="create_date" property="createDate" />
        <result column="note" property="note" />
    </resultMap>

    <select id="selectOrderList" resultMap="orderResultMap">
        SELECT *
        FROM order_
    </select>
</mapper>

d. 测试

public class XTest {

    SqlSession mSession;

    @Before
    public void before() throws IOException {
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        // 创建会话工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建SqlSession
        mSession = factory.openSession();
    }

    @After
    public void after() {
        if (null != mSession) mSession.close(); //关闭SqlSession
    }
    
    @Test
    public void test08() throws IOException {
        OrderMapper mapper = mSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.selectOrderList();
        for (Order order : orderList) {
            System.out.println(order.toString());
        }
    }
}

2. 一对一查询根据订单id查找订单包括用户信息

a. 创建实体类 com.hy.mybatis.model.OrderAndUser

public class OrderAndUser implements Serializable {

    private Integer id;
    private Date createDate;
    private String note;
    private User user;

    public Integer getId() {
        return id;
    }

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

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "OrderAndUser{" +
                "id=" + id +
                ", createDate=" + createDate +
                ", note='" + note + '\'' +
                ", user=" + user +
                '}';
    }
}

b. 创建mapper接口 com.hy.mybatis.mapper.OrderMapper

public interface OrderMapper {

    OrderAndUser selectOrderAndUserById(Integer id);
}

c. 创建mapper映射文件 com.hy.mybatis.mapper.OrderMapper.xml

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

<mapper namespace="com.hy.mybatis.mapper.OrderMapper">

    <!-- 一对一 AS可以解决字段名重复问题 -->
    <resultMap id="orderAndUserResultMap" type="OrderAndUser">
        <id column="id" property="id" />
        <result column="create_date" property="createDate" />
        <result column="note" property="note" />
        <!-- 关联内部对象 -->
        <association property="user" javaType="User">
            <id column="uid" property="id" />
            <result column="uusername" property="username" />
            <result column="umoney" property="money" />
        </association>
    </resultMap>

    <select id="selectOrderAndUserById" parameterType="Integer" resultMap="orderAndUserResultMap">
        SELECT o.*, u.id AS uid, u.username AS uusername, u.money AS umoney
        FROM order_ o, user u
        WHERE o.uid = u.id
        AND o.id = #{value}
    </select>
</mapper>

d. 测试

public class XTest {

    SqlSession mSession;

    @Before
    public void before() throws IOException {
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        // 创建会话工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建SqlSession
        mSession = factory.openSession();
    }

    @After
    public void after() {
        if (null != mSession) mSession.close(); //关闭SqlSession
    }

    @Test
    public void test14() throws IOException {
        OrderMapper mapper = mSession.getMapper(OrderMapper.class);
        OrderAndUser orderAndUser = mapper.selectOrderAndUserById(3);
        System.out.println(orderAndUser.toString());
    }
}

3. 一对多查询根据订单id查找订单包括订单明细信息

a. 创建实体类 com.hy.mybatis.model.OrderDetail com.hy.mybatis.model.OrderAndOrderDetail

public class OrderDetail implements Serializable {

    private Integer id;
    private Integer count;
    private Item item;

    public Integer getId() {
        return id;
    }

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

    public Integer getCount() {
        return count;
    }

    public void setCount(Integer count) {
        this.count = count;
    }

    public Item getItem() {
        return item;
    }

    public void setItem(Item item) {
        this.item = item;
    }

    @Override
    public String toString() {
        return "OrderDetail{" +
                "id=" + id +
                ", count=" + count +
                ", item=" + item +
                '}';
    }
}

public class OrderAndOrderDetail implements Serializable {

    private Integer id;
    private Date createDate;
    private String note;
    private List<OrderDetail> orderDetailList;

    public Integer getId() {
        return id;
    }

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

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public List<OrderDetail> getOrderDetailList() {
        return orderDetailList;
    }

    public void setOrderDetailList(List<OrderDetail> orderDetailList) {
        this.orderDetailList = orderDetailList;
    }

    @Override
    public String toString() {
        return "OrderAndOrderDetail{" +
                "id=" + id +
                ", createDate=" + createDate +
                ", note='" + note + '\'' +
                ", orderDetailList=" + orderDetailList +
                '}';
    }
}

b. 创建mapper接口 com.hy.mybatis.mapper.OrderMapper

public interface OrderMapper {

    OrderAndOrderDetail selectOrderAndOrderDetailById(Integer id);
}

c. 创建mapper映射文件 com.hy.mybatis.mapper.OrderMapper.xml

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

<mapper namespace="com.hy.mybatis.mapper.OrderMapper">

    <!-- 一对多 AS可以解决字段名重复问题 -->
    <resultMap id="orderAndOrderDetailResultMap" type="OrderAndOrderDetail">
        <id column="id" property="id" />
        <result column="create_date" property="createDate" />
        <result column="note" property="note" />
        <!-- 关联内部集合 -->
        <collection property="orderDetailList" ofType="OrderDetail">
            <id column="odid" property="id" />
            <result column="odcount" property="count" />
        </collection>
    </resultMap>

    <select id="selectOrderAndOrderDetailById" parameterType="Integer" resultMap="orderAndOrderDetailResultMap">
        SELECT o.*, od.id AS odid, od.count AS odcount
        FROM order_ o, order_detail od
        WHERE o.id = od.oid
        AND o.id = #{value}
    </select>
</mapper>

d. 测试

public class XTest {

    SqlSession mSession;

    @Before
    public void before() throws IOException {
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        // 创建会话工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建SqlSession
        mSession = factory.openSession();
    }

    @After
    public void after() {
        if (null != mSession) mSession.close(); //关闭SqlSession
    }

    @Test
    public void test15() throws IOException {
        OrderMapper mapper = mSession.getMapper(OrderMapper.class);
        OrderAndOrderDetail orderAndOrderDetail = mapper.selectOrderAndOrderDetailById(3);
        System.out.println(orderAndOrderDetail.toString());
    }
}

4. 多对多查询查询所有用户信息及用户购买的商品信息

a. 创建实体类 com.hy.mybatis.model.UserAndOrderDetail

public class UserAndOrderDetail implements Serializable {

    private Integer id;
    private String username;
    private Double money;
    private List<OrderAndOrderDetail> orderAndOrderDetailList;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    public List<OrderAndOrderDetail> getOrderAndOrderDetailList() {
        return orderAndOrderDetailList;
    }

    public void setOrderAndOrderDetailList(List<OrderAndOrderDetail> orderAndOrderDetailList) {
        this.orderAndOrderDetailList = orderAndOrderDetailList;
    }

    @Override
    public String toString() {
        return "UserAndOrderDetail{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", money=" + money +
                ", orderAndOrderDetailList=" + orderAndOrderDetailList +
                '}';
    }
}

b. 创建mapper接口 com.hy.mybatis.mapper.UserMapper

public interface UserMapper {

    List<UserAndOrderDetail> selectUserAndOrderDetail();
}

c. 创建mapper映射文件 com.hy.mybatis.mapper.UserMapper.xml

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

<mapper namespace="com.hy.mybatis.mapper.UserMapper">
    <!-- 多对多 AS可以解决字段名重复问题 -->
    <resultMap id="userAndOrderDetailResultMap" type="UserAndOrderDetail">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="money" property="money" />

        <!-- 关联内部集合 -->
        <collection property="orderAndOrderDetailList" ofType="OrderAndOrderDetail">
            <id column="o0" property="id" />
            <result column="o1" property="createDate" />
            <result column="o2" property="note" />

            <!-- 关联内部集合 -->
            <collection property="orderDetailList" ofType="OrderDetail">
                <id column="od0" property="id" />
                <result column="od1" property="count" />

                <!-- 关联内部对象 -->
                <association property="item" javaType="Item">
                    <id column="i0" property="id" />
                    <result column="i1" property="name" />
                    <result column="i2" property="price" />
                    <result column="i3" property="detail" />
                </association>
            </collection>
        </collection>
    </resultMap>

    <select id="selectUserAndOrderDetail" resultMap="userAndOrderDetailResultMap">
        SELECT u.*, o.id o0, o.create_date o1, o.note o2, od.id od0, od.count od1, i.id i0, i.name i1, i.price i2, i.detail i3
        FROM user u, order_ o, order_detail od, item i
        WHERE u.id = o.uid
        AND o.id = od.oid
        AND od.iid = i.id;
    </select>
</mapper>

d. 测试

public class XTest {

    SqlSession mSession;

    @Before
    public void before() throws IOException {
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        // 创建会话工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建SqlSession
        mSession = factory.openSession();
    }

    @After
    public void after() {
        if (null != mSession) mSession.close(); //关闭SqlSession
    }

    @Test
    public void test16() throws IOException {
        UserMapper mapper = mSession.getMapper(UserMapper.class);
        List<UserAndOrderDetail> userAndOrderDetailList = mapper.selectUserAndOrderDetail();
        for (UserAndOrderDetail detail : userAndOrderDetailList) {
            System.out.println(detail.toString());
        }
    }
}

七 MyBatis缓存

和大多数的持久化框架一样 MyBatis也提供了缓存策略通过缓存策略来减少数据库的查询次数从而提高性能 MyBatis中缓存分为一级缓存二级缓存

1. 一级缓存

默认开启

一级缓存是SqlSession级别的缓存当调用SqlSession的增删改等方法时就会清空一级缓存

第一次发起查询用户id为1的用户信息先去找缓存中是否有id为1的用户信息如果没有从数据库查询用户信息得到用户信息将用户信息存储到一级缓存中

如果SqlSession去执行增删改清空SqlSession中的一级缓存这样做可以让缓存中存储的是最新信息避免脏读

第二次发起查询用户id为1的用户信息先去找缓存中是否有id为1的用户信息缓存中有直接从缓存中获取用户信息

2. 二级缓存

需要手动开启

二级缓存是Mapper级别的缓存同一个Mapper下多个SqlSession可以共用二级缓存当调用其中某个SqlSession的增删改等方法时就会清空二级缓存

SqlSession1去查询用户信息查询到用户信息会将查询数据存储到二级缓存中

如果SqlSession3去执行增删改将会清空该Mapper映射下的二级缓存区域的数据这样做可以让缓存中存储的是最新信息避免脏读

SqlSession2去查询与SqlSession1相同的用户信息首先会去缓存中找是否存在数据如果存在直接从缓存中取出数据

二级缓存的开启与关闭

a. resources/mybatis.xml <properties/>节点和<typeAliases/>节点之间配置

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

b. 配置相关的mapper映射文件

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

<mapper namespace="com.hy.mybatis.mapper.UserMapper">

    <!-- 开启二级缓存 -->
    <cache></cache>

    <!-- useCache: true=使用二级缓存 false=禁用二级缓 -->
    <select id="selectUserById" parameterType="Integer" resultType="User" useCache="true">
        SELECT *
        FROM user
        WHERE id = #{value}
    </select>

    <select id="selectUserListByUsername" parameterType="String" resultType="User">
        SELECT *
        FROM user
        WHERE username LIKE '%${value}%'
    </select>
</mapper>

二级缓存的注意事项

a. 使用二级缓存时所缓存的类一定要实现java.io.Serializable接口这种就可以使用序列化方式来保存对象

b. 默认的二级缓存不支持分布式

c. 二级缓存对细粒度的数据缓存实现不好比如对商品信息进行缓存由于商品信息访问量大但是要求用户每次查询都是最新的商品信息此时如果使用二级缓存就无法实现当一个商品发生变化只刷新该商品缓存信息而不刷新其他商品缓存信息因为二级缓存是Mapper级别当一个商品信息更新所有的商品信息缓存数据都会清空解决此类问题需要在业务层根据需要对数据有针对性的缓存

posted @ 2020-07-31 16:39 梦三阅读(3143) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

梦三

用代码将梦想照进现实

MyBatis辟邪剑谱

公告