Mybatis框架知識整理-有解無憂

Mybatis簡介

學習框架前，先梳理原始JDBC如何操作資料庫

原始JDBC操作

查詢資料

引入依賴

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>
    </dependencies>

測驗查詢資料
測驗用表
在這里插入圖片描述

資料庫表映射類

package com.jtyhnet.domain;

public class Account {

    private int id;
    private String name;
    private float money;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public float getMoney() {
        return money;
    }

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

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

測驗代碼

import com.jtyhnet.domain.Account;
import org.junit.Test;

import java.sql.*;

public class JDBCTest1 {

    @Test
    public void test1() throws ClassNotFoundException, SQLException {
        //注冊驅動
        Class.forName("com.mysql.jdbc.Driver");
        //獲得連接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "root");
        //獲得statement
        PreparedStatement preparedStatement = connection.prepareStatement("select * from account");
        //執行查詢
        ResultSet resultSet = preparedStatement.executeQuery();
        //遍歷結果集
        while (resultSet.next()){
            //封裝物體
            Account account = new Account();
            account.setId(resultSet.getInt("id"));
            account.setName(resultSet.getString("name"));
            account.setMoney(resultSet.getFloat("money"));
            //物體封裝完成
            System.out.println(account);
        }

        //釋放資源
        resultSet.close();
        preparedStatement.close();
        connection.close();


    }

}

在這里插入圖片描述

插入資料

    @Test
    public void test2() throws ClassNotFoundException, SQLException {
        Account account = new Account();
        account.setName("wangwu");
        account.setMoney(6000);

        //注冊驅動
        Class.forName("com.mysql.jdbc.Driver");
        //獲得連接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "root");
        //獲得statement
        PreparedStatement preparedStatement = connection.prepareStatement("insert into account(id,name,money) values (?,?,?)");
        //設定占位符引數
        preparedStatement.setInt(1,account.getId());
        preparedStatement.setString(2,account.getName());
        preparedStatement.setFloat(3,account.getMoney());
        //執行更新操作
        preparedStatement.executeUpdate();
        //釋放資源
        preparedStatement.close();
        connection.close();
    }

在這里插入圖片描述

原始JDBC操作分析

原始jdbc開發存在的問題如下：
① 資料庫連接創建、釋放頻繁造成系統資源浪費從而影響系統性能
② sql 陳述句在代碼中硬編碼，造成代碼不易維護，實際應用 sql 變化的可能較大，sql 變動需要改變java代碼，
③ 查詢操作時，需要手動將結果集中的資料手動封裝到物體中，插入操作時，需要手動將物體的資料設定到sql陳述句的占位符位置
應對上述問題給出的解決方案：
① 使用資料庫連接池初始化連接資源
② 將sql陳述句抽取到xml組態檔中
③ 使用反射、內省等底層技術，自動將物體與表進行屬性與欄位的自動映射

Mybatis簡介

mybatis是一個優秀的基于java的持久層框架，它內部封裝了jdbc，使開發者只需要關注sql陳述句本身，而不需要花費精力去處理加載驅動、創建連接、創建statement等繁雜的程序，
mybatis通過xml或注解的方式將要執行的各種 statement配置起來，并通過java物件和statement中sql的動態引數進行映射生成最終執行的sql陳述句，
最后mybatis框架執行sql并將結果映射為java物件并回傳，采用ORM思想解決了物體和資料庫映射的問題，對jdbc進行了封裝，屏蔽了jdbc api 底層訪問細節，使我們不用與jdbc api打交道，就可以完成對資料庫的持久化操作，

快速入門

MyBatis開發步驟：
① 添加MyBatis的坐標
② 創建user資料表
③ 撰寫User物體類
④ 撰寫映射檔案UserMapper.xml
⑤ 撰寫核心檔案SqlMapConfig.xml
⑥ 撰寫測驗類

依賴匯入

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>
    </dependencies>

為查看詳細日志，resources下新建log4j.properties組態檔

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=debug, stdout

創建測驗用表
在這里插入圖片描述

創建物體類

package com.jtyhnet.domain;

public class User {

    private int id;
    private String username;
    private String password;

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

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

撰寫映射檔案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="userMapper">
    <select id="findAll" resultType="com.jtyhnet.domain.User">
        select * from USER
    </select>
</mapper>

撰寫mybatis核心組態檔SqlMapConfig.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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///test"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/jtyhnet/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

進行測驗

import com.jtyhnet.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest1 {

    @Test
    public void test1() throws IOException {
        //加載核心組態檔
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //獲得SqlSession工廠物件
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //獲得sqlSession物件
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //執行sql陳述句
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        //列印結果
        System.out.println(userList);
        //釋放資源
        sqlSession.close();
    }


}

在這里插入圖片描述

MyBatis的映射檔案概述

在這里插入圖片描述

MyBatis的增刪改查操作

MyBatis的插入資料操作

UserMapper.xml中添加

    <insert id="add" parameterType="com.jtyhnet.domain.User">
        insert into user values (#{id},#{username},#{password})
    </insert>

測驗代碼

    @Test
    public void test2() throws IOException {
        User user = new User();
        user.setUsername("lisi");
        user.setPassword("12233");
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int insert = sqlSession.insert("userMapper.add",user);
        System.out.println("userMapper.add : "+insert);
        sqlSession.commit();
        sqlSession.close();
    }

在這里插入圖片描述
查詢資料庫確認插入成功

插入操作注意問題
? 插入陳述句使用insert標簽
? 在映射檔案中使用parameterType屬性指定要插入的資料型別
? Sql陳述句中使用#{物體屬性名}方式參考物體中的屬性值
? 插入操作使用的API是sqlSession.insert(“命名空間.id”,物體物件);
? 插入操作涉及資料庫資料變化，所以要使用sqlSession物件顯示的提交事務，即sqlSession.commit()

MyBatis的修改資料操作

UserMapper.xml中添加

    <update id="update" parameterType="com.jtyhnet.domain.User">
        update user set username=#{username},password=#{password} where id = #{id}
    </update>

    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        User user = new User();
        user.setId(3);
        user.setUsername("lisi");
        user.setPassword("12222");

        int update = sqlSession.update("userMapper.update", user);
        System.out.println("更新條數:"+update);

        sqlSession.commit();
        sqlSession.close();
    }

在這里插入圖片描述
查詢資料庫確認資料完成更新

修改操作注意問題
? 修改陳述句使用update標簽
? 修改操作使用的API是sqlSession.update(“命名空間.id”,物體物件);

MyBatis的洗掉資料操作

    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>

    @Test
    public void test4() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int delete = sqlSession.delete("userMapper.delete", 3);
        System.out.println("洗掉條數:"+delete);
        sqlSession.commit();
        sqlSession.close();
    }

在這里插入圖片描述
查詢資料庫確認資料已洗掉

洗掉操作注意問題
? 洗掉陳述句使用delete標簽
? Sql陳述句中使用#{任意字串}方式參考傳遞的單個引數
? 洗掉操作使用的API是sqlSession.delete(“命名空間.id”,Object);

增刪改查配置小結

<?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="userMapper">
    <!--查-->
    <select id="findAll" resultType="com.jtyhnet.domain.User">
        select * from USER
    </select>
    <!--增-->  
    <insert id="add" parameterType="com.jtyhnet.domain.User">
        insert into user values (#{id},#{username},#{password})
    </insert>
    <!--改-->
    <update id="update" parameterType="com.jtyhnet.domain.User">
        update user set username=#{username},password=#{password} where id = #{id}
    </update>
    <!--刪-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>
    
    
</mapper>

//查
List<User> userList = sqlSession.selectList("userMapper.findAll");

//增
int insert = sqlSession.insert("userMapper.add",user);
sqlSession.commit();

//刪
int update = sqlSession.update("userMapper.update", user);
sqlSession.commit();

//改
int delete = sqlSession.delete("userMapper.delete", 3);
sqlSession.commit();

MyBatis核心組態檔概述

核心組態檔層級關系
在這里插入圖片描述

MyBatis常用配置決議

environments標簽

資料庫環境的配置，支持多環境配置
在這里插入圖片描述
其中，事務管理器（transactionManager）型別有兩種：

JDBC：這個配置就是直接使用了JDBC 的提交和回滾設定，它依賴于從資料源得到的連接來管理事務作用域，
MANAGED：這個配置幾乎沒做什么，它從來不提交或回滾一個連接，而是讓容器來管理事務的整個生命周期（比如JEE 應用服務器的背景關系），默認情況下它會關閉連接，然而一些容器并不希望這樣，因此需要將closeConnection 屬性設定為 false 來阻止它默認的關閉行為，

其中，資料源（dataSource）型別有三種：

UNPOOLED：這個資料源的實作只是每次被請求時打開和關閉連接，
POOLED：這種資料源的實作利用“池”的概念將 JDBC 連接物件組織起來，
JNDI：這個資料源的實作是為了能在如 EJB 或應用服務器這類容器中使用，容器可以集中或在外部配置資料源，然后放置
一個 JNDI 背景關系的參考，

mapper標簽

該標簽的作用是加載映射的，加載方式有如下幾種：

使用相對于類路徑的資源參考，例如：<mapper resource=“org/mybatis/builder/AuthorMapper.xml”/>
使用完全限定資源定位符（URL），例如：<mapper url=“file:///var/mappers/AuthorMapper.xml”/>
使用映射器介面實作類的完全限定類名，例如：<mapper class=“org.mybatis.builder.AuthorMapper”/>
將包內的映射器介面實作全部注冊為映射器，例如：<package name=“org.mybatis.builder”/>

Properties標簽

實際開發中，習慣將資料源的配置資訊單獨抽取成一個properties檔案，該標簽可以加載額外配置的properties檔案

在這里插入圖片描述

typeAliases標簽

型別別名是為Java 型別設定一個短的名字，原來的型別名稱配置如下

    <select id="findAll" resultType="com.jtyhnet.domain.User">
        select * from USER
    </select>

配置typeAliases，為com.jtyhnet.domain.User定義別名為user

<?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="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.jtyhnet.domain.User" alias="user"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/jtyhnet/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

使用別名配置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="userMapper">
    <!--查-->
    <select id="findAll" resultType="user">
        select * from USER
    </select>
    <!--增-->
    <insert id="add" parameterType="com.jtyhnet.domain.User">
        insert into user values (#{id},#{username},#{password})
    </insert>
    <!--改-->
    <update id="update" parameterType="com.jtyhnet.domain.User">
        update user set username=#{username},password=#{password} where id = #{id}
    </update>
    <!--刪-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>
    
    
</mapper>

執行查詢測驗
在這里插入圖片描述
上面我們是自定義的別名，mybatis框架已經為我們設定好的一些常用的型別的別名

別名	資料型別
string	String
long	Long
int	Integer
double	Double
boolean	Boolean
… …	… …

知識小結

核心組態檔常用配置：
1、properties標簽：該標簽可以加載外部的properties檔案

<properties resource="jdbc.properties"/>

2、typeAliases標簽：設定型別別名

<typeAliases>
	<typeAlias type="com.jtyhnet.domain.User" alias="user"/>
</typeAliases>

3、mappers標簽：加載映射配置

<mappers>
	<mapper resource="com/jtyhnet/mapper/UserMapper.xml"/>
</mappers>

4、environments標簽：資料源環境配置標簽

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="${jdbc.driver}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
        </dataSource>
    </environment>
</environments>

MyBatis相應API

SqlSession工廠構建器SqlSessionFactoryBuilder

常用API：SqlSessionFactory build(InputStream inputStream)
通過加載mybatis的核心檔案的輸入流的形式構建一個SqlSessionFactory物件

//加載核心組態檔
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//獲得SqlSession工廠物件
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

其中， Resources 工具類，這個類在 org.apache.ibatis.io 包中，Resources 類幫助你從類路徑下、檔案系統或一個 web URL 中加載資源檔案，

SqlSession工廠物件SqlSessionFactory

SqlSessionFactory 有多個個方法創建 SqlSession 實體，常用的有如下兩個：

方法	解釋
openSession()	會默認開啟一個事務，但事務不會自動提交，也就意味著需要手動提交該事務，更新操作資料才會持久化到資料庫中
openSession(boolean autoCommit)	引數為是否自動提交，如果設定為true，那么不需要手動提交事務

SqlSession會話物件

SqlSession 實體在 MyBatis 中是非常強大的一個類，在這里你會看到所有執行陳述句、提交或回滾事務和獲取映射器實體的方法，
執行陳述句的方法主要有：

<T> T selectOne(String statement, Object parameter) 
<E> List<E> selectList(String statement, Object parameter) 
int insert(String statement, Object parameter) 
int update(String statement, Object parameter) 
int delete(String statement, Object parameter)

操作事務的方法主要有：

void commit()
void rollback()

MyBatis的Dao層實作方式

傳統開發方式

package com.jtyhnet.dao;

import com.jtyhnet.domain.User;

import java.util.List;

public interface UserDao {

    List<User> findAll() throws Exception;
}

package com.jtyhnet.dao.impl;

import com.jtyhnet.dao.UserDao;
import com.jtyhnet.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public List<User> findAll() throws Exception {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        return userList;
    }
}

測驗

    @Test
    public void test5() throws Exception {
        UserDao userDao = new UserDaoImpl();
        List<User> userList = userDao.findAll();
        System.out.println(userList);
    }

在這里插入圖片描述

代理開發方式

采用 Mybatis 的代理開發方式實作 DAO 層的開發，這種方式是我們后面進入企業的主流，
Mapper 介面開發方法只需要程式員撰寫Mapper 介面（相當于Dao 介面），由Mybatis 框架根據介面定義創建介面的動態代理物件，代理物件的方法體同上邊Dao介面實作類方法，
Mapper 介面開發需要遵循以下規范：
1、 Mapper.xml檔案中的namespace與mapper介面的全限定名相同
2、 Mapper介面方法名和Mapper.xml中定義的每個statement的id相同
3、 Mapper介面方法的輸入引數型別和mapper.xml中定義的每個sql的parameterType的型別相同
4、 Mapper介面方法的輸出引數型別和mapper.xml中定義的每個sql的resultType的型別相同

介面類

package com.jtyhnet.dao;

import com.jtyhnet.domain.User;

import java.io.IOException;
import java.util.List;

public interface UserDao {

    List<User> findAll() throws IOException;
    User findById(int id);
}

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

    <select id="findAll" resultType="user">
        select * from user
    </select>

    <select id="findById" parameterType="int" resultType="user">
        select * from user where id = #{id}
    </select>
</mapper>

    @Test
    public void test21() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDaoMapper = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDaoMapper.findAll();
        System.out.println(userList);
        System.out.println("------");
        User user1 = userDaoMapper.findById(1);
        System.out.println(user1);

    }

在這里插入圖片描述

傳統方式實作介面
代理方式在xml組態檔中對應介面類配置映射

MyBatis映射檔案深入

動態sql陳述句

Mybatis 的映射檔案中，前面我們的 SQL 都是比較簡單的，有些時候業務邏輯復雜時，我們的 SQL是動態變化的，此時在前面的學習中我們的 SQL 就不能滿足要求了，

動態 SQL 之<if>

我們根據物體類的不同取值，使用不同的 SQL陳述句來進行查詢，比如在 id如果不為空時可以根據id查詢，如果username 不同空時還要加入用戶名作為條件，這種情況在我們的多條件組合查詢中經常會碰到，

介面類

package com.jtyhnet.dao;

import com.jtyhnet.domain.User;

import java.io.IOException;
import java.util.List;

public interface UserDao {

    List<User> findAll() throws IOException;
    User findById(int id);
    User findByCondition(User user);
}

mapper配置

    <select id="findByCondition" parameterType="user" resultType="user">
        select * from user
        <where>
            <if test="id!=0">
                and id = #{id}
            </if>
            <if test="username!=null">
                and username = #{username}
            </if>
        </where>
    </select>

測驗代碼

    @Test
    public void test22() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDaoMapper = sqlSession.getMapper(UserDao.class);

        User user = new User();
        user.setId(1);
        user.setUsername("zhangsan");

        User user1 = userDaoMapper.findByCondition(user);
        System.out.println(user1);

    }

在這里插入圖片描述
改變條件再次測驗

    @Test
    public void test22() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDaoMapper = sqlSession.getMapper(UserDao.class);

        User user = new User();
        user.setId(1);
        //user.setUsername("zhangsan");

        User user1 = userDaoMapper.findByCondition(user);
        System.out.println(user1);

    }

在這里插入圖片描述
可見查詢陳述句隨引數的改變動態變化

動態 SQL 之<foreach>

回圈執行sql的拼接操作，例如：SELECT * FROM USER WHERE id IN (1,2,5)，

介面增加

    List<User> findByIds(List<Integer> ids);

mapper增加

    <select id="findByIds" parameterType="list" resultType="user">
        select * from user
        <where>
            <foreach collection="list" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

    @Test
    public void test23() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDaoMapper = sqlSession.getMapper(UserDao.class);
        ArrayList<Integer> idList = new ArrayList<>();
        idList.add(1);
        idList.add(4);
        idList.add(5);
        List<User> userList = userDaoMapper.findByIds(idList);
        System.out.println(userList);


    }

在這里插入圖片描述

在這里插入圖片描述
foreach標簽的屬性含義如下：
<foreach>標簽用于遍歷集合，它的屬性：
? collection：代表要遍歷的集合元素，注意撰寫時不要寫#{}
? open：代表陳述句的開始部分
? close：代表結束部分
? item：代表遍歷集合的每個元素，生成的變數名
? sperator：代表分隔符

SQL片段抽取

Sql 中可將重復的 sql 提取出來，使用時用 include 參考即可，最終達到 sql 重用的目的

<sql id="selectAllUser">select * from user</sql>
<include refid="selectAllUser"/>

<?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.jtyhnet.dao.UserDao">

    <sql id="selectAllUser">select * from user</sql>

    <select id="findAll" resultType="user">
        <include refid="selectAllUser"/>
    </select>

    <select id="findById" parameterType="int" resultType="user">
        <include refid="selectAllUser"/> where id = #{id}
    </select>
    
    <select id="findByCondition" parameterType="user" resultType="user">
        <include refid="selectAllUser"/>
        <where>
            <if test="id!=0">
                and id = #{id}
            </if>
            <if test="username!=null">
                and username = #{username}
            </if>
        </where>
    </select>

    <select id="findByIds" parameterType="list" resultType="user">
        <include refid="selectAllUser"/>
        <where>
            <foreach collection="list" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

MyBatis核心組態檔深入

typeHandlers標簽

無論是 MyBatis 在預處理陳述句（PreparedStatement）中設定一個引數時，還是從結果集中取出一個值時，都會用型別處理器將獲取的值以合適的方式轉換成 Java 型別，下表描述了一些默認的型別處理器（截取部分），

在這里插入圖片描述
你可以重寫型別處理器或創建你自己的型別處理器來處理不支持的或非標準的型別，具體做法為：實作org.apache.ibatis.type.TypeHandler 介面，或繼承一個很便利的類org.apache.ibatis.type.BaseTypeHandler，然后可以選擇性地將它映射到一個JDBC型別，例如需求：一個Java中的Date資料型別，我想將之存到資料庫的時候存成一個1970年至今的毫秒數，取出來時轉換成java的Date，即java的Date與資料庫的varchar毫秒值之間轉換，

開發步驟：
① 定義轉換類繼承類BaseTypeHandler
② 覆寫4個未實作的方法，其中setNonNullParameter為java程式設定資料到資料庫的回呼方法，getNullableResult為查詢時 mysql的字串型別轉換成 java的Type型別的方法
③ 在MyBatis核心組態檔中進行注冊
④ 測驗轉換是否正確

創建測驗用表

CREATE TABLE userinfo (
    id INT NOT NULL auto_increment,	 
    username VARCHAR ( 50 ) ,	
    password VARCHAR ( 50 )  ,		
    birthday VARCHAR ( 50 )  ,		 
    PRIMARY KEY ( id ) 				 
);
insert  into userinfo values (null,'zhangsan','123','1640784746140');

創建物體類

package com.jtyhnet.domain;

import java.util.Date;

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

插入資料介面方法

package com.jtyhnet.dao;

import com.jtyhnet.domain.Userinfo;

public interface UserinfoDao {
    void add(Userinfo userinfo);
}

定義轉換類

package com.jtyhnet.typeHandler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class MyDateTypeHandler extends BaseTypeHandler<Date> {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i,date.getTime()+"");
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return new Date(resultSet.getLong(s));
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return new Date(resultSet.getLong(i));
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getDate(i);
    }
}

mybatis核心配置類
添加userinfo別名，<typeHandlers>注冊自定義轉換類

<?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="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.jtyhnet.domain.User" alias="user"/>
        <typeAlias type="com.jtyhnet.domain.Userinfo" alias="userinfo"/>
    </typeAliases>

    <typeHandlers>
        <typeHandler handler="com.jtyhnet.typeHandler.MyDateTypeHandler"/>
    </typeHandlers>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="com/jtyhnet/mapper/UserDaoMapper.xml"/>
        <mapper resource="com/jtyhnet/mapper/UserinfoMapper.xml"/>
    </mappers>
</configuration>

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.jtyhnet.dao.UserinfoDao">
    <insert id="add" parameterType="userinfo">
        insert into userinfo values(#{id},#{username},#{password},#{birthday});
    </insert>
</mapper>

測驗

    @Test
    public void test31() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserinfoDao userinfoDaoMapper = sqlSession.getMapper(UserinfoDao.class);

        Userinfo userinfo = new Userinfo();
        userinfo.setUsername("lisi");
        userinfo.setPassword("1234");
        userinfo.setBirthday(new Date());
        userinfoDaoMapper.add(userinfo);

        sqlSession.commit();
        sqlSession.close();
    }

在這里插入圖片描述
查詢資料庫確認插入成功，date型別被轉為varchar

測驗查詢

    @Test
    public void test32() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserinfoDao userinfoDaoMapper = sqlSession.getMapper(UserinfoDao.class);

        Userinfo userinfo = userinfoDaoMapper.findById(1);

        System.out.println(userinfo);
        sqlSession.close();
    }

在這里插入圖片描述
可以發現查詢結果已經由varchar轉為Date型別

關于自定義轉換類

package com.jtyhnet.typeHandler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class MyDateTypeHandler extends BaseTypeHandler<Date> {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i,date.getTime()+"");
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return new Date(resultSet.getLong(s));
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return new Date(resultSet.getLong(i));
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getDate(i);
    }
}

自定義轉換類繼承BaseTypeHandler<T>，其中T是javaType，上述案例中，birthday欄位在java中為Date型別，在資料庫中為varchar型別，因此繼承BaseTypeHandler<Date>
繼承后需復寫4個方法
setNonNullParameter，用于preparedStatement執行sql時，對引數進行設定，i為引數角標，即第幾個引數，上述案例中，java端設定的birthday為Date型別，在執行資料庫insert操作時，需轉為String型別才能插入到資料庫Varchar型別欄位中，因此，preparedStatement.setString(i,date.getTime()+"");
getNullableResult(ResultSet resultSet, String s)，getNullableResult(ResultSet resultSet, int i)，用于將從資料庫查詢的結果封裝為javaBean，即將查詢出的String型別轉為Date型別，其中s為欄位名，i為欄位角標，
getNullableResult(CallableStatement callableStatement, int i)為存盤程序使用，i為欄位角標，

plugins標簽

MyBatis可以使用第三方的插件來對功能進行擴展，分頁助手PageHelper是將分頁的復雜操作進行封裝，使用簡單的方式即可獲得分頁的相關資料
開發步驟：
① 匯入通用PageHelper的坐標
② 在mybatis核心組態檔中配置PageHelper插件
③ 測驗分頁資料獲取

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>3.7.5</version>
        </dependency>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>0.9.1</version>
        </dependency>

核心組態檔中配置分頁插件

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!--指定方言-->
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>

新建查詢所有介面方法

    List<Userinfo> findAll();

配置mapper

    <select id="findAll" resultType="userinfo">
        select * from userinfo
    </select>

測驗

    @Test
    public void test33() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserinfoDao userinfoDaoMapper = sqlSession.getMapper(UserinfoDao.class);
        PageHelper.startPage(1,2);
        List<Userinfo> userinfoList = userinfoDaoMapper.findAll();
        System.out.println("userinfoList-------"+userinfoList);
        for (Userinfo userinfo : userinfoList) {
            System.out.println(userinfo);
        }
        System.out.println("==================================");
        PageInfo<Userinfo> userinfoPageInfo = new PageInfo<>(userinfoList);
        System.out.println("總條數:"+userinfoPageInfo.getTotal());
        System.out.println("總頁數:"+userinfoPageInfo.getPages());
        System.out.println("當前頁:"+userinfoPageInfo.getPageNum());
        System.out.println("每頁顯示長度:"+userinfoPageInfo.getPageSize());
        System.out.println("是否第一頁:"+userinfoPageInfo.isIsFirstPage());
        System.out.println("是否最后一頁:"+userinfoPageInfo.isIsLastPage());
    }

在這里插入圖片描述

MyBatis的多表操作

Mybatis多表查詢

一對一查詢

案例：
用戶表和訂單表的關系為，一個用戶有多個訂單，一個訂單只從屬于一個用戶
一對一查詢的需求：查詢一個訂單，與此同時查詢出該訂單所屬的用戶

create table orderlist (
id INT NOT NULL auto_increment,
ordertime VARCHAR ( 255 ) ,
totalmoney decimal(20,2),
uid int,
PRIMARY KEY ( id ) 
)

insert into orderlist values (null,'2021-12-30 21:46:30',5000,1);
insert into orderlist values (null,'2021-12-29 21:46:30',400,1);
insert into orderlist values (null,'2021-12-28 21:46:30',40,2);
insert into orderlist values (null,'2021-12-27 21:46:30',2321,2);
insert into orderlist values (null,'2021-12-26 21:46:30',4211,3);
insert into orderlist values (null,'2021-12-25 21:46:30',2000,4);

CREATE TABLE userinfo (
id INT NOT NULL auto_increment,	 
username VARCHAR ( 50 ) ,	
password VARCHAR ( 50 )  ,		
birthday VARCHAR ( 50 )  ,		 
PRIMARY KEY ( id ) 				 
);

insert  into userinfo values (null,'zhangsan','123','1640784746140');
insert  into userinfo values (null,'lisi','1234','1640785271387');
insert  into userinfo values (null,'wangwu','123','1640784746140');
insert  into userinfo values (null,'zhaoliu','123','1640784746140');

在這里插入圖片描述

對應的sql陳述句：select * from orderlist o,userinfo u where o.uid=u.id;

在這里插入圖片描述
IDEA中創建物體類

package com.jtyhnet.domain;

import java.util.Date;

public class Order {

    private int id;
    private Date ordertime;
    private double totalmoney;

    //代表當前訂單屬于哪個客戶
    private OrderUserinfo orderUserinfo;

    public int getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotalmoney() {
        return totalmoney;
    }

    public void setTotalmoney(double totalmoney) {
        this.totalmoney = totalmoney;
    }

    public OrderUserinfo getOrderUserinfo() {
        return orderUserinfo;
    }

    public void setOrderUserinfo(OrderUserinfo orderUserinfo) {
        this.orderUserinfo = orderUserinfo;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", totalmoney=" + totalmoney +
                ", orderUserinfo=" + orderUserinfo +
                '}';
    }
}

package com.jtyhnet.domain;


public class OrderUserinfo {

    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

}

創建介面類

package com.jtyhnet.mapper;

import com.jtyhnet.domain.Order;

import java.util.List;

public interface OrderMapper {
    List<Order> findAll();
}

mybatis核心組態檔

<?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="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.jtyhnet.domain.OrderUserinfo" alias="orderUserinfo"/>
        <typeAlias type="com.jtyhnet.domain.Order" alias="order"/>
    </typeAliases>
    

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/jtyhnet/mapper/orderUserinfoMapper.xml"/>
        <mapper resource="com/jtyhnet/mapper/orderMapper.xml"/>
    </mappers>
</configuration>

配置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.jtyhnet.mapper.OrderMapper">
    
    <resultMap id="orderMap1" type="order">
        <id column="id" property="id"/>
        <result column="ordertime" property="ordertime"/>
        <result column="totalmoney" property="totalmoney"/>
        <result column="uid" property="orderUserinfo.id"/>
        <result column="username" property="orderUserinfo.username"/>
        <result column="password" property="orderUserinfo.password"/>
    </resultMap>

    <resultMap id="orderMap2" type="order">
        <result property="id" column="id"/>
        <result property="ordertime" column="ordertime"/>
        <result property="totalmoney" column="totalmoney"/>
        <association property="orderUserinfo" javaType="com.jtyhnet.domain.OrderUserinfo">
            <result column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </association>
    </resultMap>
    
    <select id="findAll" resultMap="orderMap1">
        select * from orderlist T1,userinfo T2 where T1.uid = T2.id
    </select>
</mapper>

由于關聯后的結果，映射到order類中，欄位orderUserinfo參考了另一個類OrderUserinfo，因此不能直接將結果封裝，需定義resultMap，設定java類中的屬性與資料庫表欄位相關聯，設定方式有兩種，一種是column為資料庫中欄位，property采用類名.屬性，另一種是使用association 標簽，單獨定義參考類，

測驗

    @Test
    public void test11() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = orderMapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }

        sqlSession.close();
    }

在這里插入圖片描述

一對多查詢

案例：
用戶表和訂單表的關系為，一個用戶有多個訂單，一個訂單只從屬于一個用戶
一對多查詢的需求：查詢一個用戶，與此同時查詢出該用戶具有的訂單

改造用戶OrderUserinfo類，添加訂單屬性

package com.jtyhnet.domain;


import java.util.List;

public class OrderUserinfo {

    private int id;
    private String username;
    private String password;

    //當前用戶下的所有訂單
    private List<Order> orderList;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    @Override
    public String toString() {
        return "OrderUserinfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", orderList=" + orderList +
                '}';
    }
}

撰寫介面類

package com.jtyhnet.mapper;

import com.jtyhnet.domain.OrderUserinfo;

import java.util.List;

public interface OrderUserinfoMapper {
    List<OrderUserinfo> findAll();
}

撰寫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.jtyhnet.mapper.OrderUserinfoMapper">
    <resultMap id="orderUserinfoResultMap1" type="com.jtyhnet.domain.OrderUserinfo">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <collection property="orderList" ofType="com.jtyhnet.domain.Order">
            <result column="oid" property="id"/>
            <result column="ordertime" property="ordertime"/>
            <result column="totalmoney" property="totalmoney"/>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="orderUserinfoResultMap1">
        select *,o.id oid from userinfo u left join orderlist o on o.uid=u.id
    </select>
</mapper>

由于一個用戶對應多個訂單，此處訂單List使用collection標簽定義
測驗

    @Test
    public void test12() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderUserinfoMapper orderUserinfoMapper = sqlSession.getMapper(OrderUserinfoMapper.class);
        List<OrderUserinfo> orderUserinfoList = orderUserinfoMapper.findAll();
        for (OrderUserinfo orderUserinfo : orderUserinfoList) {
            System.out.println(orderUserinfo.getUsername());
            for (Order order : orderUserinfo.getOrderList()) {
                System.out.println(order);
            }
            System.out.println("--------------------------");
        }
        sqlSession.close();
    }

在這里插入圖片描述

多對多查詢

案例：
用戶表和角色表的關系為，一個用戶有多個角色，一個角色被多個用戶使用
多對多查詢的需求：查詢用戶同時查詢出該用戶的所有角色

用戶表增加屬性角色串列

package com.jtyhnet.domain;


import java.util.List;

public class OrderUserinfo {

    private int id;
    private String username;
    private String password;

    //當前用戶下的所有訂單
    private List<Order> orderList;

    //當前用戶所具有的角色
    private List<Role> roleList;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    @Override
    public String toString() {
        return "OrderUserinfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                '}';
    }
}

介面類增加方法

    List<OrderUserinfo> findAllUserAndRole();

設定mapper

    <resultMap id="orderMap2" type="order">
        <result property="id" column="id"/>
        <result property="ordertime" column="ordertime"/>
        <result property="totalmoney" column="totalmoney"/>
        <association property="orderUserinfo" javaType="com.jtyhnet.domain.OrderUserinfo">
            <result column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </association>
    </resultMap>
    
    <select id="findAll" resultMap="orderMap1">
        select * from orderlist T1,userinfo T2 where T1.uid = T2.id
    </select>

測驗

    @Test
    public void test13() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderUserinfoMapper orderUserinfoMapper = sqlSession.getMapper(OrderUserinfoMapper.class);
        List<OrderUserinfo> allUserAndRole = orderUserinfoMapper.findAllUserAndRole();
        for (OrderUserinfo orderUserinfo : allUserAndRole) {
            System.out.println(orderUserinfo.getUsername());
            for (Role role : orderUserinfo.getRoleList()) {
                System.out.println(role);
            }
            System.out.println("---------------");
        }
        sqlSession.close();
    }

在這里插入圖片描述
總結：
MyBatis多表配置方式：
一對一配置：使用<resultMap>做配置
一對多配置：使用<resultMap>+<collection>做配置
多對多配置：使用<resultMap>+<collection>做配置

MyBatis注解開發

MyBatis的常用注解

這幾年來注解開發越來越流行，Mybatis也可以使用注解開發方式，這樣我們就可以減少撰寫Mapper映射檔案了，我們先圍繞一些基本的CRUD來學習，再學習復雜映射多表操作，
@Insert：實作新增
@Update：實作更新
@Delete：實作洗掉
@Select：實作查詢
@Result：實作結果集封裝
@Results：可以與@Result 一起使用，封裝多個結果集
@One：實作一對一結果集封裝
@Many：實作一對多結果集封裝

MyBatis的增刪改查

@Insert插入資料

測驗注解開發向資料庫表中insert資料

物體類

package com.jtyhnet.domain;

import java.util.Date;

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

介面類

package com.jtyhnet.mapper;

import com.jtyhnet.domain.Userinfo;
import org.apache.ibatis.annotations.Insert;

public interface UserinfoMapper {
    @Insert("insert into userinfo values (#{id},#{username},#{password},#{birthday})")
    void add(Userinfo userinfo);
}

自定義轉換器

package com.jtyhnet.typeHandler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class MyDateTypeHandler3 extends BaseTypeHandler<Date> {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i,date.getTime()+"");
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return new Date(resultSet.getLong(s));
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return new Date(resultSet.getLong(i));
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getDate(i);
    }
}

核心配置類

<?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="jdbc.properties"/>

    <typeHandlers>
        <typeHandler handler="com.jtyhnet.typeHandler.MyDateTypeHandler3"/>
    </typeHandlers>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--掃描使用注解的類-->
        <mapper class="com.jtyhnet.mapper.UserinfoMapper"/>

        <!--掃描使用注解的類所在的包-->
<!--        <package name="com.jtyhnet.mapper"/>-->
    </mappers>

</configuration>

使用注解開發后，<mappers>標簽內使用<mapper class>掃描使用注解的類或<package>掃描使用注解的類所在的包

測驗

import com.jtyhnet.domain.Userinfo;
import com.jtyhnet.mapper.UserinfoMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

public class test1 {

    private UserinfoMapper userinfoMapper;
    private SqlSession sqlSession;

    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        sqlSession = sqlSessionFactory.openSession();
        userinfoMapper = sqlSession.getMapper(UserinfoMapper.class);
    }

    @Test
    public void testAdd() {
        Userinfo userinfo = new Userinfo();
        userinfo.setUsername("張三");
        userinfo.setPassword("1234");
        userinfo.setBirthday(new Date());
        userinfoMapper.add(userinfo);
        sqlSession.commit();
        sqlSession.close();
    }

}

在這里插入圖片描述

@Delete洗掉資料

介面類中增加

    @Delete("delete from userinfo where id = #{id}")
    void delete(int id);

測驗

    @Test
    public void testDelete(){
        userinfoMapper.delete(8);
        sqlSession.commit();
        sqlSession.close();
    }

在這里插入圖片描述

@Update更新資料

    @Update("update userinfo set username=#{username},password=#{password},birthday=#{birthday} where id = #{id}")
    void update(Userinfo userinfo);

    @Test
    public void testUpdate(){
        Userinfo userinfo = new Userinfo();
        userinfo.setId(7);
        userinfo.setUsername("李四1");
        userinfo.setPassword("wer111");
        userinfo.setBirthday(new Date());
        userinfoMapper.update(userinfo);
    }

在這里插入圖片描述

@Select查詢資料

    @Select("select * from userinfo")
    List<Userinfo> findAll();

    @Select("select * from userinfo where id = #{id}")
    Userinfo findById(int id);

    @Test
    public void testFindAll(){
        List<Userinfo> userinfoList = userinfoMapper.findAll();
        for (Userinfo userinfo : userinfoList) {
            System.out.println(userinfo);
        }
    }

    @Test
    public void testFindById(){
        Userinfo userinfo = userinfoMapper.findById(1);
        System.out.println(userinfo);
    }

在這里插入圖片描述

復雜映射

實作復雜關系映射之前我們可以在映射檔案中通過配置來實作，使用注解開發后，我們可以使用@Results注解，@Result注解，@One注解，@Many注解組合完成復雜關系的配置

注解	說明
@Results	代替的是標簽<resultMap>該注解中可以使用單個@Result注解，也可以使用@Result集合，使用格式：@Results（{@Result（），@Result（）}）或@Results（@Result（））
@Result	代替了<id>標簽和<result>標簽 @Result中屬性介紹： column：資料庫的列名 property：需要裝配的屬性名 one：需要使用的@One 注解（@Result（one=@One）（））） many：需要使用的@Many 注解（@Result（many=@many）（）））
@One(一對一)	代替了<assocation> 標簽，是多表查詢的關鍵，在注解中用來指定子查詢回傳單一物件， @One注解屬性介紹：select: 指定用來多表查詢的 sqlmapper 使用格式：@Result(column=" “,property=”",one=@One(select=""))
@Many(多對一)	代替了<collection>標簽, 是是多表查詢的關鍵，在注解中用來指定子查詢回傳物件集合，使用格式：@Result(property="",column="",many=@Many(select=""))

一對一查詢

訂單與客戶案例
物體類

package com.jtyhnet.domain;


public class Order {

    private int id;
    private Double totalMoney;
    private Userinfo userinfo;

    public int getId() {
        return id;
    }

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

    public Double getTotalMoney() {
        return totalMoney;
    }

    public void setTotalMoney(Double totalMoney) {
        this.totalMoney = totalMoney;
    }

    public Userinfo getUserinfo() {
        return userinfo;
    }

    public void setUserinfo(Userinfo userinfo) {
        this.userinfo = userinfo;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", totalMoney=" + totalMoney +
                ", userinfo=" + userinfo +
                '}';
    }
}

介面類

package com.jtyhnet.mapper;

import com.jtyhnet.domain.Order;
import com.jtyhnet.domain.Userinfo;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderMapper {

    @Select("select * from orderlist")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "totalMoney",column = "totalmoney"),
            @Result(property = "userinfo",column = "uid",javaType = Userinfo.class,
                    one = @One(select = "com.jtyhnet.mapper.UserinfoMapper.findById"))
    })
    List<Order> findAll();


}

測驗

    @Test
    public void testFindAllOrder(){
        List<Order> orderList = orderMapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }

在這里插入圖片描述
一對一查詢，使用注解開發，由于結果中有參考物件，使用@Results定義結果集，@Result定義每一個欄位，

@Result(property = "userinfo",column = "uid",javaType = Userinfo.class,
                    one = @One(select = "com.jtyhnet.mapper.UserinfoMapper.findById"))

參考類的結果，property 為java類中屬性名稱，column 為主體類關聯參考類，即資料庫中主表關聯附表使用的關聯欄位，javaType 定義該屬性的型別，one用來定義根據該關聯欄位查詢該條資料，

一對多查詢

用戶下有多個訂單

package com.jtyhnet.domain;

import java.util.Date;
import java.util.List;

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    //用戶下所有訂單
    private List<Order> orderList;


    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", orderList=" + orderList +
                '}';
    }
}

    @Select("select * from userinfo")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "orderList",column = "id",javaType = List.class,
                    many = @Many(select = "com.jtyhnet.mapper.OrderMapper.findByUid"))
    })
    List<Userinfo> findAllUserAndOrder();

    @Select("select * from orderlist where uid = #{uid}")
    List<Order> findByUid(int uid);

    @Test
    public void findAllUserAndOrder(){
        List<Userinfo> allUserAndOrder = userinfoMapper.findAllUserAndOrder();
        for (Userinfo userinfo : allUserAndOrder) {
            System.out.println(userinfo.getUsername());
            for (Order order : userinfo.getOrderList()){
                System.out.println(order);
            }
        }

    }

在這里插入圖片描述
一對多查詢時，與一對一查詢基本類似，只是將參考類javaType改為List.class，使用many查詢附表

#####多對多查詢

用戶與角色關聯案例

角色類

package com.jtyhnet.domain;

public class Role {

    private int id;
    private String roleName;

    public int getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                '}';
    }
}

用戶類

package com.jtyhnet.domain;

import java.util.Date;
import java.util.List;

public class Userinfo {

    private int id;
    private String username;
    private String password;
    private Date birthday;

    //用戶下所有訂單
    private List<Order> orderList;

    private List<Role> roleList;


    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                '}';
    }
}

    @Select("select * from userinfo")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "roleList",column = "id",javaType = List.class,
                    many = @Many(select = "com.jtyhnet.mapper.RoleMapper.findRoleByUid"))
    })
    List<Userinfo> findAllUserAndRole();

    @Select("select T1.* from role T1 inner join user_role T2 on T1.id = T2.role_id where T2.user_id = #{uid}")
    List<Role> findRoleByUid(int uid);

測驗

    @Test
    public void findAllUserAndRole(){
        List<Userinfo> allUserAndRole = userinfoMapper.findAllUserAndRole();
        for (Userinfo userinfo : allUserAndRole) {
            System.out.println(userinfo.getUsername());
            for (Role role : userinfo.getRoleList()){
                System.out.println(role);
            }
            System.out.println("+++++++++++++++++++++");
        }
    }

在這里插入圖片描述

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

標籤：java

上一篇：原型spring bean之Lookup

下一篇：C站萬字詳解二叉樹基本操作演示程式（附圖）