MyBatis辟邪劍譜

夢三發表於2020-07-31

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

漫談程式設計師系列：找工作的辟邪劍譜
2015-01-15
程式設計師
「劍指offer」27道Mybatis面試題含解析
2020-10-08
MyBatis面試題
宗譜鏈app,宗譜鏈傳承記錄家譜
2021-02-06
APP
MyBatis(二)MyBatis入門程式(MyBatis demo)
2018-07-03
MyBatis
《劍指offter》
2017-12-15
FFT
MyBatis——MyBatis開發流程
2020-11-17
MyBatis
【MyBatis】2：MyBatis簡介
2016-07-29
MyBatis
宗譜鏈功能優勢，區塊鏈宗譜鏈讓家譜更科技
2021-02-01
區塊鏈
MyBatis -01- 初識 MyBatis + MyBatis 環境搭建
2018-11-18
MyBatis
php圖譜
2017-01-03
PHP
MyBatis系列(一)：MyBatis入門
2019-06-28
MyBatis
MyBatis1：MyBatis入門
2016-03-08
MyBatis
無人值守的運維到底靠譜不靠譜?
2018-12-27
運維
MyBatis
2024-03-21
MyBatis
事理圖譜，下一代知識圖譜
2018-12-29
知識圖譜|知識圖譜的典型應用
2022-10-18
譜圖論：Laplacian運算元及其譜性質
2023-10-19
圖論
知識圖譜01：知識圖譜的定義
2020-10-05
不靠譜專案經理成長記--“不靠譜”的Jerry
2012-11-06
「Mybatis系列」Mybatis高階應用
2020-02-21
MyBatis
MyBatis基礎：MyBatis快取（5）
2017-09-20
MyBatis快取
MyBatis基礎：MyBatis入門（1）
2017-08-15
MyBatis
Mac 開箱食譜
2017-01-11
Mac
穿越介面圖譜~
2016-12-31
穿越介面圖譜
2016-12-30
智慧的光譜
2015-03-06
Vim命令圖譜
2016-03-16
Mybatis基礎：Mybatis對映配置檔案，Mybatis核心配置檔案，Mybatis傳統方式開發
2021-02-14
MyBatis
知識圖譜學習記錄--知識圖譜概述
2020-12-19
宗譜鏈介紹，區塊鏈宗譜鏈優勢
2021-01-28
區塊鏈
「Mybatis系列」Mybatis開發方式和配置
2020-02-20
MyBatis
MyBatis(十一):MyBatis架構流程淺析
2021-03-14
MyBatis架構
mybatis的外掛：mybatis-generator(MBG)
2018-06-14
MyBatis
深入淺出MyBatis：MyBatis的所有配置
2018-03-26
MyBatis
mybatis 學習--mybatis基本用法入門
2016-11-27
MyBatis
MyBatis基礎：MyBatis動態SQL（3）
2017-09-05
MyBatisSQL
Mybatis20_mybatis的多表操作8
2024-03-13
MyBatis
MyBatis-05-MyBatis使用流程簡讀
2024-04-16
MyBatis

MyBatis辟邪劍譜

相關文章