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級別當一個商品資訊更新所有的商品資訊快取資料都會清空解決此類問題需要在業務層根據需要對資料有針對性的快取

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/117766.html

標籤：Java

上一篇：探討Netty獲取并檢查Websocket握手請求的兩種方式

下一篇：SpringBoot學習之整合Mybatis