2-Mybatis進階

1.Mybatis 接口代理方式實作Dao層

2.Mybatis 映射配置檔案-動态SQL

3.Mybatis 核心配置檔案-分頁插件

4.Mybatis 多表操作

1.Mybatis 接口代理方式實作Dao層

傳統方式實作Dao層,我們即要寫接口,還要寫實作類,而MyBatis架構可以幫助我們省略編寫Dao層接口實作類的步驟,程式員隻需要編寫接口,由MyBatis架構根據接口定義的來建立該接口的動态代理對象

實作規則

1.映射配置檔案中的名稱空間必須和Dao層接口的全類名相同

2.映射配置檔案中的增删改查标簽的id屬性必須和Dao層接口的方法名相同

3.映射配置檔案中的增删改查标簽的 parameterType屬性必須和Dao層接口方法的參數相同

4.映射配置檔案中的增删改查标簽的 resultType屬性必須和Dao層接口方法的傳回值相同

接口代理方式-(代碼實作)

1.删除mapper層接口的實作類

2.修改映射配置檔案,其他配置不變

<mapper namespace="com.itheima.mapper.StudentMapper">

3.修改service層接口的實作類,采用接口代理方式實作功能

package com.itheima.service.Impl;

import com.itheima.bean.Student;
import com.itheima.mapper.StudentMapper;
import com.itheima.service.StudentService;
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 StudentServiceImpl implements StudentService {

    @Override
    public List<Student> selectAll() {
        List<Student> list = null;
        SqlSession sqlSession = null;
        InputStream is = null;
        try {
            //加載核心配置檔案
            is = Resources.getResourceAsStream("MyBatisConfig.xml");
            //擷取SqlSession工廠對象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            //通過工廠對象擷取SqlSession對象
            sqlSession = sqlSessionFactory.openSession(true);
            //擷取StudentMapper接口的實作類對象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            //通過實作類對象調用方法,接受結果
            list = mapper.selectAll();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //釋放資源
            if (sqlSession != null) {
                sqlSession.close();
            }

            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //傳回結果
        return list;
    }

    @Override
    public Student selectById(Integer id) {
        SqlSession sqlSession = null;
        InputStream is = null;
        Student student = null;
        try {
            //加載核心配置檔案
            is = Resources.getResourceAsStream("MyBatisConfig.xml");
            //擷取SqlSession工廠對象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            //通過工廠對象擷取SqlSession對象
            sqlSession = sqlSessionFactory.openSession(true);
            //擷取StudentMapper接口的實作類對象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            //通過實作類對象調用方法,接受結果
            student = mapper.selectById(id);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //釋放資源
            if (sqlSession != null) {
                sqlSession.close();
            }

            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //傳回結果
        return student;
    }

    @Override
    public Integer insert(Student stu) {

        SqlSession sqlSession = null;
        InputStream is = null;
        Integer result = null;
        try {
            //加載核心配置檔案
            is = Resources.getResourceAsStream("MyBatisConfig.xml");
            //擷取SqlSession工廠對象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            //通過工廠對象擷取SqlSession對象
            sqlSession = sqlSessionFactory.openSession(true);
            //擷取StudentMapper接口的實作類對象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            //通過實作類對象調用方法,接受結果
            result = mapper.insert(stu);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //釋放資源
            if (sqlSession != null) {
                sqlSession.close();
            }

            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //傳回結果
        return result;
    }

    @Override
    public Integer update(Student stu) {
        SqlSession sqlSession = null;
        InputStream is = null;
        Integer result = null;
        try {
            //加載核心配置檔案
            is = Resources.getResourceAsStream("MyBatisConfig.xml");
            //擷取SqlSession工廠對象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            //通過工廠對象擷取SqlSession對象
            sqlSession = sqlSessionFactory.openSession(true);
            //擷取StudentMapper接口的實作類對象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            //通過實作類對象調用方法,接受結果
            result = mapper.update(stu);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //釋放資源
            if (sqlSession != null) {
                sqlSession.close();
            }

            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //傳回結果
        return result;
    }

    @Override
    public Integer delete(Integer id) {
        SqlSession sqlSession = null;
        InputStream is = null;
        Integer result = null;
        try {
            //加載核心配置檔案
            is = Resources.getResourceAsStream("MyBatisConfig.xml");
            //擷取SqlSession工廠對象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            //通過工廠對象擷取SqlSession對象
            sqlSession = sqlSessionFactory.openSession(true);
            //擷取StudentMapper接口的實作類對象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            //通過實作類對象調用方法,接受結果
            result = mapper.delete(id);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //釋放資源
            if (sqlSession != null) {
                sqlSession.close();
            }

            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //傳回結果
        return result;
    }
}

分析動态代理對象如何生成的?

通過動态代理開發模式,我們隻編寫一個接口,不寫實作類,我們通過getMapper()方法最終擷取到org.apache.ibatis.binding.MapperProxy代理對象,然後執行功能,而這個代理對象正是MyBatis使用了JDK的動态代理技術,幫助我們生成了代理實作類對象,進而可以進行相關持久化操作

分析方法是如果執行的?

動态代理實作類對象執行方法的時候最終調用mapperMethod.execute()方法,這個方法中通過switch語句根據操作類型來判斷是新增,修改,删除,查詢操作,最後一步回到了MyBatis最原生的SqlSession方式執行增删改查

2.Mybatis 映射配置檔案-動态SQL

動态SQL指的就是SQL語句可以根據條件或者參數的不同進行動态的變化

動态SQL标簽

1.<if>: 條件判斷标簽

2.<where>: 條件标簽,如果有動态條件,則使用該标簽代替where關鍵字

<if test= "判斷條件">
  查詢條件
</if>

<select id="selectCondition" resultType="student" parameterType="student">
<!--Select * from student WHERE id=#{id} and name =#{name} and age =#{age}-->
<!-- <where>标簽就是替換sql語句中的where關鍵字的-->
        Select * from student
        <where>
            <if test="id != null">
                id =#{id}
            </if>
            <if test="name != null">
                and name =#{name}
            </if>
            <if test="age != null">
                and age =#{age}
            </if>
        </where>
    </select>

3.<foreach>: 循環周遊标簽,适用于多個參數或者的關系

<foreach collection="" open="" item="" separator="">
            擷取參數
</foreach>

屬性

conllection: 參數容器類型,(list-集合,array-數組)

open: 開始的SQL語句

close: 結束的SQL語句

item: 參數變量名

separator: 分隔符

<!-- 查詢多個id-->
<select id="forEach" resultType="student" parameterType="list">
<!--         Select * from student where id in (1,2,3)-->
        Select * from student
        <where>
            <foreach collection="list" open="id in ( " close=")"  item="id" separator=",">
                #{id}
            </foreach>
        </where>
</select>

SQL 片段抽取>

将重複性的SQL語句進行抽取，以達到複用的效果

<sql>: 抽取SQL語句标簽

<sql id="片段唯一辨別"> 抽取的SQL語句 </sql>

<include>: 引入SQL片段标簽

<sql refid="片段唯一辨別"/>

映射配置檔案中的抽取與引用

<sql id="select">Select * from student</sql>
    <!--    查詢功能-->
    <select id="selectAll" resultType="student">
        <include refid="select"/>
    </select>

3.Mybatis 核心配置檔案-分頁插件

PageHelper: 第三方分頁助手,将複雜的分頁操作進行封裝,進而讓分頁功能變得非常簡單

<plugins>: 內建插件标簽

分頁相關API

PageHelper: 分頁助手功能類

startPage(): 設定分頁參數參數1:目前頁參數2:目前頁顯示的條數

分頁插件實作步驟

1.導入jar包

pagehelper-5.1.10.jar
jsqlparser-3.1.jar

2.在核心配置檔案中內建分頁助手插件

<!--內建分頁助手插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

3.在測試類中使用分頁助手相關API實作分類功能

//通過分頁助手實作分頁功能
        //第一頁: 顯示三條
        //PageHelper.startPage(1,3);
        //第二頁:顯示三條,以此類推
        PageHelper.startPage(2,3);
        List<Student> list = mapper.selectAll();
        for (Student student : list) {
            System.out.println(student);
        }
        //分頁相關的參數
        PageInfo<Student> info = new PageInfo<>(list);
        System.out.println("總條數:"+info.getTotal());
        System.out.println("總頁數:"+info.getPages());
        System.out.println("目前頁:"+info.getPageNum());
        System.out.println("每頁顯示的條數:"+info.getPageSize());
        System.out.println("上一頁:"+info.getPrePage());
        System.out.println("下一頁:"+info.getNextPage());
        System.out.println("是否是第一頁:"+info.isIsFirstPage());
        System.out.println("是否是最後頁:"+info.isIsLastPage());

分頁插件相關參數

PageInfo: 封裝分頁相關參數的功能類

核心方法:

2-Mybatis進階

核心方法

4.Mybatis 多表操作

多表模型分類

一對一: 在任意一方建立外鍵,關聯對方主鍵

一對多: 在多的一方建立外鍵,關聯一的一方主鍵

多對多: 借助中間表,中間表至少兩個字段,分别關聯兩張表的主鍵

一對一

<resultMap>: 配置字段和對象屬性的映射關系标簽

id屬性: 唯一辨別

type屬性: 實體類對象類型

<id>: 配置主鍵映射關系标簽

<result>: 配置非主鍵映射關系标簽

column屬性: 表中字段名稱

property屬性: 實體對象變量名稱

<association>: 配置被包含對象的映射關系标簽

property屬性: 被包含對象的變量名

javaType屬性: 被包含對象的資料類型

封裝類

//  空參有構造方法,get和set和toString()方法省略,
public class Card {
    private Integer id;
    private String number;
    private  Person P;
}

// 空參有參構造方法,get和set和toString()方法省略
public class Person {
    private Integer id;
    private String name;
    private Integer age;
  }

資料準備

CREATE DATABASE db12;
USE db12;
CREATE TABLE person(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        age INT
);
INSERT INTO person VALUES(NULL,'張三',23);
INSERT INTO person VALUES(NULL,'李四',24);
INSERT INTO person VALUES(NULL,'王五',25);
CREATE TABLE card (
        id INT PRIMARY KEY AUTO_INCREMENT,
        number VARCHAR(30),
        pid INT,
        CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id)
);
INSERT INTO card VALUES(NULL,'123123',1);
INSERT INTO card VALUES(NULL,'456456',2);
INSERT INTO card VALUES(NULL,'789789',3);

核心配置檔案

<?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"/>

    <!--繼承LOG4J日志資訊-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--起别名-->
    <typeAliases>
        <!--<typeAlias type="com.itheima.bean" alias="***"/>-->
    <!--給bean這個包下所有的包起一個别名,作為了解-->
    <package name="com.itheima.bean"/>
    </typeAliases>

    <!--內建分頁助手插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

    <!--environments配置資料庫環境,環境可以有多個,default屬性指定使用的是哪個-->
    <environments default="mysql">
        <!--environment配置資料庫環境 id屬性代表唯一的辨別-->
        <environment id="mysql">
            <!-- transactionManager事務的管理 type屬性,采用JDBC預設的事務-->
            <transactionManager type="JDBC"></transactionManager>
            <!--dataSource資料源資訊 type屬性 連接配接池-->
            <dataSource type="POOLED">
                <!--property擷取資料庫連接配接的資料資訊-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--mappers引入映射檔案-->
    <mappers>
        <!--mapper 引入指定的映射配置檔案 resource屬性: 指定映射配置檔案的名稱-->
        <mapper resource="com/itheima/one_to_one/OneToOneMapper.xml"></mapper>
    </mappers>
</configuration>

映射配置檔案

<?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.itheima.table01.OneToOneMapper">
    <!--配置字段和實體對象屬性的映射關系-->
    <resultMap id="OneToOne" type="card">
        <!--id标簽是專門對主鍵進行配置的-->
        <id column="cid" property="id"/>
        <!--其他屬性用result标簽-->
        <result column="number" property="number"/>
        <!--
            association: 配置被包含對象的映射關系
            property: 被包含對象的變量名
            javaType: 被包含對象的實際的資料類型
        -->
        <association property="P" javaType="person">
            <id column="pid" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>

    <select id="selectAll" resultMap="OneToOne">
        SELECT c.id cid,number,pid,NAME,age FROM card c,person p WHERE c.pid=p.id
    </select>
</mapper>

測試單元

public class Test01 {
    @Test
    public void selectAll() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);
        List<Card> cards = mapper.selectAll();
        for (Card cs : cards) {
            System.out.println(cs);
        }
        sqlSession.close();
        is.close();
    }
}

列印結果

2-Mybatis進階

image.png

一對多

<collection>: 配置被包含集合對象的映射關系标簽

property屬性: 被包含集合對象的變量名

ofType屬性: 集合中儲存的對象資料類型

封裝類

//  空參有構造方法,get和set和toString()方法省略,
public class Classes {
    private Integer id; //主鍵id
    private  String name;   //班級名稱
    private List<Student> students; //班級中所有學生對象
}

//  空參有構造方法,get和set和toString()方法省略,
public class Student {
    private Integer id; //主鍵id
    private String name;    //學生新明
    private Integer age;    //學生年齡
}

資料準備

CREATE TABLE classes(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20)
);
INSERT INTO classes VALUES(NULL,'一班');
INSERT INTO classes VALUES(NULL,'二班');
CREATE TABLE student(
        id INT  PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(30),
        age INT,
        cid INT,
        CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
);
INSERT INTO student VALUES(NULL,'張三',23,1);
INSERT INTO student VALUES(NULL,'李四',24,1);
INSERT INTO student VALUES(NULL,'王五',25,2);
INSERT INTO student VALUES(NULL,'趙六',26,2);

核心配置檔案,隻需添加映射檔案路徑

<mappers>
        <!--mapper 引入指定的映射配置檔案 resource屬性: 指定映射配置檔案的名稱-->
        <mapper resource="com/itheima/one_to_one/OneToOneMapper.xml"></mapper>
        <mapper resource="com/itheima/one_to_many/OneToManyMapper.xml"></mapper>
    </mappers>

映射配置檔案

<?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.itheima.table02.OneToManyMapper">
    <resultMap id="OnToMany" type="classes">
        <id column="cid" property="id"/>
        <result column="cname" property="name"/>
        <!--
        collection: 配置被包含的集合對象映射關系
        property： 被包含對象的變量名
        ofType： 被包含對象的實際資料類型
        -->
        <!--這裡的ofType：  指的是list集合中儲存的實際資料類型-->
        <collection property="students" ofType="Student">
            <id column="sid" property="id"/>
            <result column="sname" property="name"/>
            <result column="sage" property="age"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="OnToMany">
        SELECT c.id cid,c.name cname, s.id sid,s.name sname,s.age sage FROM classes c,student s WHERE c.id=s.cid;
    </select>
</mapper>

接口類代碼

package com.itheima.table02;

import com.itheima.bean.Classes;
import java.util.List;

public interface OneToManyMapper {
    //查詢全部的功能
    public abstract List<Classes> selectAll();
}

測試單元代碼

public class Test01 {
    @Test
    public void selectAll() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);
        List<Classes> classes = mapper.selectAll();
        for (Classes cs : classes) {
            System.out.println(cs.getId()+","+cs.getName());
            List<Student> students = cs.getStudents();
            for (Student student : students) {
                System.out.println(student);
            }
        }
        sqlSession.close();
        is.close();
    }
}

測試結果

測試結果

封裝類

public class Student {
    private Integer id; //主鍵id
    private String name;    //學生新明
    private Integer age;    //學生年齡
    private List<Course> courses;   //指目前學生所選擇的課程集合
}

public class Course {
    private Integer id; //主鍵id
    private String name;   //課程名稱
}

資料準備

CREATE TABLE course(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20)
);
INSERT INTO course VALUES(NULL,'國文');
INSERT INTO course VALUES(NULL,'數學');

CREATE TABLE stu_cr(
        id INT  PRIMARY KEY AUTO_INCREMENT,
        sid INT,
        cid INT,
        CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
        CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
);
INSERT INTO stu_cr VALUES(NULL,1,1);
INSERT INTO stu_cr VALUES(NULL,1,2);
INSERT INTO stu_cr VALUES(NULL,2,1);
INSERT INTO stu_cr VALUES(NULL,2,2);

接口

public interface ManyToManyMapper {
    //查詢全部的功能
    public abstract List<Student> selectAll();
}

映射配置檔案

<?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.itheima.table03.ManyToManyMapper">
    <resultMap id="manyToMany" type="student">
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="sage" property="age"/>
        <collection property="courses" ofType="course">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="manyToMany">
        SELECT sc.sid ,s.name sname ,s.age sage,sc.cid ,c.name cname FROM student s,course c, stu_cr sc WHERE sc.sid=s.id AND sc.cid=c.id;
    </select>
</mapper>

核心配置檔案隻需添加,映射配置檔案路徑

<!--mappers引入映射檔案-->
    <mappers>
        <!--mapper 引入指定的映射配置檔案 resource屬性: 指定映射配置檔案的名稱-->
        <mapper resource="com/itheima/one_to_one/OneToOneMapper.xml"></mapper>
        <mapper resource="com/itheima/one_to_many/OneToManyMapper.xml"></mapper>
        <mapper resource="com/itheima/many_to_many/ManyToManyMapper.xml"></mapper>
    </mappers>

測試單元

@Test
    public void selectAll() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);
        List<Student> studentList = mapper.selectAll();
        for (Student student : studentList) {
            System.out.println(student.getId()+","+student.getName()+","+student.getAge());
            List<Course> courses = student.getCourses();
            for (Course cours : courses) {
                System.out.println("/t"+cours);
            }
        }
        sqlSession.close();
        is.close();
    }
}

測試結果

測試結果

2-Mybatis進階

1.Mybatis 接口代理方式實作Dao層

2.Mybatis 映射配置檔案-動态SQL

3.Mybatis 核心配置檔案-分頁插件

4.Mybatis 多表操作

1.Mybatis 接口代理方式實作Dao層

2.Mybatis 映射配置檔案-動态SQL

3.Mybatis 核心配置檔案-分頁插件

4.Mybatis 多表操作

繼續閱讀

出現invalid byte 1 of 1-byte UTF-8 sequence問題

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SIP Presence SUBSCRIBE-NOTIFY

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

QName是什麼

關于SQL語言

SQL語言基礎：常用的資料查詢語句

debian9更新4.9.0核心到4.19.2核心過程

neo4j之cypher使用文檔

sqlServer根據經緯查距離