2020-5-11-Mybatis

框架和架構、MyBatis概述、入門、增刪改查操作、dao實現類增刪改查、引用外部配置檔案、配置實體類別名、註冊指定包內的dao介面、動態sql、一對多查詢、多對多查詢、延遲載入、mybatis快取、註解開發

框架和架構

1框架

軟體開發的一套解決方案，不同框架解決不同問題。框架中封裝很多細節，開發者使用極簡方式完成，大大提高效率

2三層架構

表現層：用於展示資料

業務層：用於處理業務需求

持久層：和資料庫互動

3持久層技術解決方案

	技術方案	詳細
規範	JDBC	Connection
		PreparedStatement
		ResultSet
工具類	Spring的JdbcTemplate	Spring對Jdbc的封裝
	Apache的DButils	Apache對Jdbc的封裝

---

MyBatis概述

1概述

一個用java編寫的持久層框架，封裝了jdbc操作的很多細節，使開發者只需關注sql語句本身，無需關注註冊驅動、建立連線等複雜過程，使用ORM思想實現對結果集的封裝

2ORM

Object Relational Mapping物件關係對映，把資料庫裡的表和實體類及其屬性關聯對應起來，透過操作實體類實現對資料庫表的操作

---

入門

1環境搭建

1）建立maven工程，並匯入相關依賴座標

<?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>org.zhanghuan</groupId>
    <artifactId>mybatis1</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
        </dependency>
    </dependencies>

</project>

2）建立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="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username"  value="root"/>
                <property name="password"  value=""/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="dao/IStudentdao.xml"/>
    </mappers>
</configuration>

3）建立實體類和dao介面

package doMain;

public class Student {
    private Integer id;
    private String male;
    private  String name;
    private Integer math;
    private Integer english;

    public Integer getId() {
        return id;
    }

    public String getMale() {
        return male;
    }

    public String getName() {
        return name;
    }

    public Integer getMath() {
        return math;
    }

    public Integer getEnglish() {
        return english;
    }

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

    public void setMale(String male) {
        this.male = male;
    }

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

    public void setMath(Integer math) {
        this.math = math;
    }

    public void setEnglish(Integer english) {
        this.english = english;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", male='" + male + '\'' +
                ", name='" + name + '\'' +
                ", math=" + math +
                ", english=" + english +
                '}';
    }
}

package dao;

import doMain.Student;

import java.util.List;

public interface IStudentdao {
    List<Student> findAll();
}

4）建立與dao介面對應的mapper配置檔案（註解配置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="dao.IStudentdao">
    <select id="findAll" resultType="doMain.Student">
        select * from student
    </select>
</mapper>

2使用

import dao.IStudentdao;
import doMain.Student;
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.IOException;
import java.io.InputStream;
import java.util.List;

public class mybatisTest {
    public static void main(String[] args) throws IOException {
        //讀取配置檔案
        InputStream in= Resources.getResourceAsStream ("SqlMapConfig.xml");

        //建立sessionFactory工廠
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder ();
        SqlSessionFactory factory=builder.build (in);

        //建立SqlSession物件
        SqlSession session=factory.openSession ();//可傳參，true表示關閉事務

        //透過SqlSession物件建立對應介面的代理物件
        IStudentdao studentdao=session.getMapper (IStudentdao.class);

        //使用代理物件的方法
        List<Student> students=studentdao.findAll ();
        for (Student student:
             students) {
            System.out.println (student);
        }

        //釋放資源
        session.close ();
        in.close ();
    }
}

3設計模式

操作	設計模式	詳細
建立工廠	構建者模式	把物件的建立細節隱藏，使用者直接呼叫方法便可拿到工廠物件
生成SqlSession	工廠模式	解耦，降低類之間的依賴關係
建立dao例項	代理模式	不修改原始碼的基礎上對已有方法進行增強

4註解方式配置mapper

1）無需建立與dao介面對應的mapper配置檔案，在dao介面的方法上使用註解(@select,@update,@delete,@insert)並傳入sql語句

2）在主配置檔案中配置mapper，使用class屬性指向dao介面的全類名

---

增刪改查操作

1定義dao介面

package dao;

import doMain.Student;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface IStudentdao {
    List<Student> findAll();
    void addStudent(Student st);//插入一條資料
    void updateStudent(Student st);//更新一條記錄
    void deleteStudent(int id);//刪除一條記錄
    Student findOneStudent(int id);//查詢一條資料
    List<Student> findFuzzyByName(String name);//模糊查詢
    int findTotal();//查詢總的記錄數
}

2dao介面的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="dao.IStudentdao">
    <select id="findAll" resultType="doMain.Student">
        select * from student
    </select>
    <insert id="addStudent" parameterType="doMain.Student">
        <selectKey keyProperty="id" keyColumn="id" order="BEFORE" resultType="Integer">
        <!--在資料插入前，該標籤計算出插入之後的id並將其賦值給Student類的id屬性-->
            select last_insert_id()
        </selectKey>
        insert into student (id ,male,name,math,english) value (#{id},#{male},#{name},#{math},#{english})
    </insert>
    <update id="updateStudent" parameterType="doMain.Student">
        update student set name=#{name},male=#{male},math=#{math},english=#{english} where id=#{id}
    </update>
    <delete id="deleteStudent" parameterType="java.lang.Integer">
        delete from student where id=#{id}
    </delete>
    <select id="findOneStudent" parameterType="java.lang.Integer" resultType="doMain.Student">
        select * from student where id=#{id}
    </select>
    <select id="findFuzzyByName" parameterType="java.lang.String" resultType="doMain.Student">
        select * from student where name like '%${vale}%'
    </select>
    <select id="findTotal" resultType="int">
        select count(*) from student
    </select>
</mapper>

3定義測試類

import dao.IStudentdao;
import doMain.Student;
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.After;
import org.junit.Before;
import org.junit.Test;

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

public class mybatisTest {
    static InputStream in= null;
    static SqlSession session=null;

    @Before
    public void init(){
        //讀取配置檔案
        try {
            in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        } catch (IOException e) {
            e.printStackTrace ( );
        }

        //建立sessionFactory工廠
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder ();
        SqlSessionFactory factory=builder.build (in);

        //建立SqlSession物件
        session=factory.openSession ();
    }
    @After
    public void destroy(){
        //釋放資源
        try {
            session.close ();
            in.close ();
        } catch (IOException e) {
            e.printStackTrace ( );
        }
    }

    //檢視所有資料
    @Test
    public void findall(){
        //透過SqlSession物件建立對應介面的代理物件
        IStudentdao studentdao=session.getMapper (IStudentdao.class);

        //使用代理物件的方法
        List<Student> students=studentdao.findAll ();
        for (Student student:
                students) {
            System.out.println (student);
        }
    }

    //插入一條記錄
    @Test
    public void addStudent(){
        Student st=new Student ();
        st.setId (9);
        st.setName ("zhanghuan");
        st.setMale ("男");
        st.setEnglish (66);
        st.setMath (89);
        IStudentdao studentdao=session.getMapper (IStudentdao.class);
        studentdao.addStudent (st);//呼叫代理物件的方法
        session.commit ();
    }

    //更新一條記錄
    @Test
    public void updateStudent(){
        Student st=new Student ();
        st.setId (9);
        st.setName ("張歡");
        st.setMale ("男");
        st.setEnglish (66);
        st.setMath (89);
        IStudentdao studentdao=session.getMapper (IStudentdao.class);
        studentdao.updateStudent (st);//呼叫代理物件的方法
        session.commit ();
    }

    //刪除一條記錄
    @Test
    public void deleteStudent(){
        IStudentdao studentdao=session.getMapper (IStudentdao.class);
        studentdao.deleteStudent (11);//呼叫代理物件的方法
        session.commit ();
    }

    //查詢一條資料
    @Test
    public void findOneStudent(){
        IStudentdao studentdao=session.getMapper (IStudentdao.class);
        Student st=studentdao.findOneStudent (9);//呼叫代理物件的方法
        System.out.println (st);
    }

    //模糊查詢
    @Test
    public void findFuzzyByName(){
        IStudentdao studentdao=session.getMapper (IStudentdao.class);
        List<Student> list=studentdao.findFuzzyByName ("張");//呼叫代理物件的方法
        for (Student student:
                list) {
            System.out.println (student);
        }
    }

    //查詢總的記錄數
    @Test
    public void finTotal(){
        IStudentdao studentdao=session.getMapper (IStudentdao.class);
        int total=studentdao.findTotal();//呼叫代理物件的方法
        System.out.println (total);
    }
}

4類屬性和表列名不對應情況

預設情況下，類的屬性需要和表名對應，否則資料無法封裝到類裡。如果不想對應，可以將類的屬性和表的列名進行對應，對應操作在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="dao.IStudentdao">
	<resultMap id="studentmap" type="doMain.Student">
        <id property="id1" column="id"></id>
        <result property="male1" column="male"></result>
        <result property="name1" column="name"></result>
        <result property="math1" column="math"></result>
        <result property="english1" column="english"></result>
    </resultMap>
    <select id="findAll" resultMap="studentmap">
        select * from student
    </select>
</mapper>

---

dao實現類增刪改查

package dao.daoimpl;

import org.apache.ibatis.session.SqlSessionFactory;
import dao.IStudentdao;
import doMain.Student;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class IStudentdaoimpl implements IStudentdao {
    private SqlSessionFactory factory;
    public IStudentdaoimpl(SqlSessionFactory factory) {
        this.factory=factory;
    }

    public List<Student> findAll() {
        SqlSession session=this.factory.openSession ();
        List<Student> list=session.selectList ("dao.IStudentdao.findAll");
        session.close ();
        return list;
    }

    public void addStudent(Student st) {
        SqlSession session=this.factory.openSession ();
        session.update ("dao.IStudentdao.addStudent",st);
        session.commit ();
        session.close ();
    }

    public void updateStudent(Student st) {
        SqlSession session=this.factory.openSession ();
        session.update ("dao.IStudentdao.updateStudent",st);
        session.commit ();
        session.close ();
    }

    public void deleteStudent(int id) {
        SqlSession session=this.factory.openSession ();
        session.delete ("dao.IStudentdao.deleteStudent",id);
        session.commit ();
        session.close ();
    }

    public Student findOneStudent(int id) {
        SqlSession session=this.factory.openSession ();
        Student s=session.selectOne ("dao.IStudentdao.findOneStudent",id);
        session.close ();
        return s;
    }

    public List<Student> findFuzzyByName(String name) {
        SqlSession session=this.factory.openSession ();
        List<Student> list=session.selectList ("dao.IStudentdao.findFuzzyByName",name);
        session.close ();
        return list;
    }

    public int findTotal() {
        SqlSession session=this.factory.openSession ();
        int count=session.selectOne ("dao.IStudentdao.findTotal");
        session.close ();
        return count;
    }
}

import dao.IStudentdao;
import dao.daoimpl.IStudentdaoimpl;
import doMain.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

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

public class mybatisTest1 {
    static InputStream in= null;
    static SqlSessionFactory factory=null;
    static IStudentdao dao=null;
    @Before
    public void init(){
        //讀取配置檔案
        try {
            in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        } catch (IOException e) {
            e.printStackTrace ( );
        }

        //建立sessionFactory工廠
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder ();
        SqlSessionFactory factory=builder.build (in);

        dao=new IStudentdaoimpl (factory);
    }
    @After
    public void destroy(){
        //釋放資源
        try {
            in.close ();
        } catch (IOException e) {
            e.printStackTrace ( );
        }
    }

    //檢視所有資料
    @Test
    public void findall(){
        List<Student> list=dao.findAll ();
        for (Student s:
             list) {
            System.out.println (s);
        }
    }

    //插入一條記錄
    @Test
    public void addStudent(){
        Student st=new Student ();
        st.setId (9);
        st.setName ("zhanghuan");
        st.setMale ("男");
        st.setEnglish (66);
        st.setMath (89);
        dao.addStudent (st);
    }

    //更新一條記錄
    @Test
    public void updateStudent(){
        Student st=new Student ();
        st.setId (14);
        st.setName ("張歡");
        st.setMale ("男");
        st.setEnglish (66);
        st.setMath (89);
        dao.updateStudent (st);
    }

    //刪除一條記錄
    @Test
    public void deleteStudent(){
        dao.deleteStudent (15);
    }

    //查詢一條資料
    @Test
    public void findOneStudent(){
        Student s=dao.findOneStudent (14);
        System.out.println (s);
    }

    //模糊查詢
    @Test
    public void findFuzzyByName(){
        List<Student> list=dao.findFuzzyByName ("張");
        for (Student s:
                list) {
            System.out.println (s);
        }
    }

    //查詢總的記錄數
    @Test
    public void finTotal(){
        int count=dao.findTotal ();
        System.out.println (count);
    }
}

---

引用外部配置檔案

1基本使用

1）外部定義一個以properties結尾的檔案

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
username=root
password=

2）匯入配置檔案

<?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="db.properties"></properties><!--匯入-->
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED"><!--以下取出屬性值-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username"  value="${username}"/>
                <property name="password"  value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="dao/IStudentdao.xml"/>
    </mappers>
</configuration>

2resource屬性和url屬性

1）resource屬性

用於指定配置檔案的位置，其值按照類路徑的寫法來寫，並檔案必須存在於類路徑下

2）url屬性

Unifor Resource Locator統一資源定位符，可唯一標識一個資源位置。

格式：協議 主機 埠 URI

URI

Uniform Resource Identifier統一資源識別符號，可在應用中唯一定位一個資源

---

配置實體類別名

1配置全限定名和別名

<?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="db.properties"></properties>
    <typeAliases>
        <typeAlias type="doMain.Student" alias="student"></typeAlias>
        <!--type裡放全限定名，alias放別名，配置後在mapper的xml檔案中使用類用別名即可，不區分大小寫-->
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username"  value="${username}"/>
                <property name="password"  value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="dao/IStudentdao.xml"/>
    </mappers>
</configuration>

2指定包名

<?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="db.properties"></properties>
    <typeAliases>
        <package name="doMain"></package>
        <!--name放的是包名，這樣配置後包裡的類名就是別名，使用時不區分大小寫-->
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username"  value="${username}"/>
                <property name="password"  value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="dao/IStudentdao.xml"/>
    </mappers>
</configuration>

---

註冊指定包內的dao介面

<?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="db.properties"></properties>
    <typeAliases>
        <package name="doMain"></package>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username"  value="${username}"/>
                <property name="password"  value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="dao"></package>
        <!--這樣配置後，dao包下所有介面將會被註冊，這裡無需寫mapper標籤進行註冊-->
    </mappers>
</configuration>

---

動態sql

1if標籤

<select id="findStudentByCondition" parameterType="Student" resultType="Student">
        select * from student where 1=1
	<if test="name!=null">
            and name=#{name}
	</if>
</select>

2where標籤

<select id="findStudentByCondition" parameterType="Student" resultType="Student">
	select * from student
	<if test="name!=null">
		<where>
			name=#{name}
		</where>
	</if>
</select>

3foreach標籤

<select id="findStudentByCondition" parameterType="vo" resultType="Student">
	select * from student
        <where>
            <if test="ids!=null and ids.length>0">
                <foreach collection="ids" open="and id in (" close=")" item="id" separator="," >
                    #{id}
                </foreach>
            </if>
        </where>
</select>

4sql標籤

該標籤抽取重複的sql語句

<sql id="default">select * from student</sql>
<select id="findAll" resultType="doMain.Student">
	<include refid="default"></include>
</select>

---

一對多查詢

1表分析

1）user表

id	username	password
1	zhanghuan	123456
2	wangdong	123456
3	liuqiangdong	123456
4	mayun	123456
5	wangjianling	123456

2）account表

id	uid	money
1	1	500
2	1	1000
3	2	545584564
4	1	845987
5	4	323231232
6	5	12555553

account表透過uid與user表關聯，需要透過account表查詢到對應的user資訊，及透過user表查到對應的account資訊

2account表查詢到對應的user程式碼

package doMain;

public class AccountUser {
    private int id;
    private int uid;
    private int money;
    private User user;

    public User getUser() {
        return user;
    }

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

    public int getId() {
        return id;
    }

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

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public int getMoney() {
        return money;
    }

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

    @Override
    public String toString() {
        return "AccountUser{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

package dao;

import doMain.AccountUser;

import java.util.List;

public interface AccountDao {
    List<AccountUser> findAll_account_user();
}

<?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="dao.AccountDao">
    <resultMap id="useraccount" type="AccountUser">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <association property="user" column="uid">
            <id property="id" column="uid"></id>
            <result property="username" column="username"></result>
            <result property="password" column="password"></result>
        </association>
    </resultMap>
    <select id="findAll_account_user" resultMap="useraccount">
        select * from account as a left join user as b on  a.uid=b.id
    </select>
</mapper>

//省略部分程式碼
	@Test
    public void account_user_findall(){
        AccountDao dao=session.getMapper (AccountDao.class);
        List<AccountUser> list=dao.findAll_account_user ();
        for (AccountUser au:
                list) {
            System.out.println (au);
        }
    }

3透過user表查到對應的account程式碼

package doMain;

import java.util.List;

public class UserAccount {
    private int id;
    private String username;
    private String password;
    private List<Account> accounts;

    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<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

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

package dao;

import doMain.UserAccount;

import java.util.List;

public interface UserDao {
    List<UserAccount> fiidAll_UserAccount();
}

<?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="dao.UserDao">
    <resultMap type="UserAccount" id="useraccount">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <collection property="accounts" ofType="Account">
            <id property="id" column="aid"></id>
            <result property="uid" column="id"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>
    <select id="fiidAll_UserAccount" resultMap="useraccount" >
        select u.*,a.id as aid,a.money from user as u left join account as a on u.id=a.uid
    </select>
</mapper>

//省略部分程式碼
	@Test
    public void user_account_findall(){
        UserDao dao=session.getMapper (UserDao.class);
        List<UserAccount> list=dao.fiidAll_UserAccount ();
        for (UserAccount ua:
                list) {
            System.out.println (ua);
        }
    }

---

多對多查詢

1表分析

1）user表

id	username	password
1	zhanghuan	123456
2	wangdong	123456
3	liuqiangdong	123456
4	mayun	123456
5	wangjianling	123456

2）role表

id	role	describe
1	員工	公司職員，需完成工作
2	老闆	公司負責人
3	學生	學校內的學院，主要任務是學習
4	老師	學校內的職工，工作內容是講課

3）user_m_role表（關聯表）

id	uid	rid
1	1	1
2	1	3
3	4	2
4	4	4

user表與role表透過第三張表user_m_role關聯，從user表開始先與user_m_role表進行左聯，其查詢結果再與role表進行左聯，得出user表及其對應的role角色

2程式碼

package doMain;

import java.util.List;

public class User_m_Role {
    private int id;
    private String username;
    private String password;
    private List<Role> roles;

    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<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

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

package dao;

import doMain.User;
import doMain.User_m_Role;

import java.util.List;

public interface UserDao {
    List<User_m_Role> find_User_m_Role();
}

<?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="dao.UserDao">
    <resultMap type="user_m_role" id="user_m_role">
        <id property="id" column="uid"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <collection property="roles" ofType="Role">
            <id property="id" column="rid"></id>
            <result property="role" column="role"></result>
            <result property="describe" column="describe"></result>
        </collection>
    </resultMap>
    <select id="find_User_m_Role" resultMap="user_m_role">
        SELECT a.id AS uid,a.username,a.`password`,c.id AS rid,c.role,c.`describe`
        FROM user AS a LEFT JOIN user_m_role As b
        ON a.id=b.uid
        LEFT JOIN role AS c
        ON  b.rid=c.id
    </select>
</mapper>

注意

1.resultMap標籤內，id，collection,association必寫，其他屬性可以省略，mybatis會自動補充上，association用到的列必須設定result標籤，否則mybatis不會將資料封裝到對應實體類的屬性上

2.collection和association必須防止在result下，否則會報錯

//省略部分程式碼
	@Test
    public void user_m_role(){
        UserDao dao=session.getMapper (UserDao.class);
        List<User_m_Role> list=dao.find_User_m_Role ();
        for (User_m_Role ur:
                list) {
            System.out.println (ur);
        }
    }

---

延遲載入

真正使用資料時發起查詢，不使用不查詢，用於一對多和多對多關係表中

1）主配置檔案中進行配置

<?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>
    <settings><!--在這裡配置-->
        <setting name="lazyLoadingEnabled" value="true"></setting>
        <setting name="aggressiveLazyLoading" value="false"></setting>
    </settings>
    <typeAliases>
        <package name="doMain"></package>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username"  value="root"/>
                <property name="password"  value=""/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="dao"></package>
    </mappers>
</configuration>

2）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="dao.UserDao">
    <resultMap id="default" type="UserAccount">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <collection property="accounts" column="id" select="dao.AccountDao.findByUid"></collection>
        <!--column的內容將作為引數傳入select內dao.AccountDao.findByUid進行查詢-->
    </resultMap>
    <select id="findAll" resultMap="default">
        SELECT * FROM user
    </select>
    <select id="findById" parameterType="int" resultType="User">
        SELECT * FROM user WHERE id=#{id}
    </select>
</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="dao.AccountDao">
    <resultMap id="default" type="AccountUser">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <association property="user" column="uid" javaType="User" select="dao.UserDao.findById"></association>
    </resultMap>
    <select id="findAll" resultMap="default">
        SELECT * FROM account
    </select>
    <select id="findByUid" parameterType="int" resultType="Account">
        SELECT  * FROM account WHERE uid=#{uid}
    </select>
</mapper>

---

mybatis快取

經常查詢且不經常改變，資料的正確與否對最終結果影響不大，可使用快取

1）一級快取

在SqlSession層面，快取內容是SqlSession物件，透過SqlSession.clearCache()或SqlSession.close()清除掉快取

2）二級快取

在namespace層面，快取內容是SqlSession查詢到的資料

(1)主配置檔案中

<settings>
	<setting name="cacheEnabled" value="true"></setting>
</settings>

(2)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="dao.UserDao">
    <cache></cache><!--建立cache-->
    <resultMap id="default" type="UserAccount">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <collection property="accounts" column="id" select="dao.AccountDao.findByUid"></collection>
    </resultMap>
    <select id="findAll" resultMap="default" useCache="true"><!--使用cache-->
        SELECT * FROM user
    </select>
    <select id="findById" parameterType="int" resultType="User">
        SELECT * FROM user WHERE id=#{id}
    </select>
</mapper>

---

註解開發

除xml配置mapper外，還可透過註解方式配置。二者只可選其一，否則會報錯，使用註解方式，resource目錄中對應的xml配置檔案應刪除。

1快速開始

1）主配置檔案中configuration下

<mappers>
	<!--<mapper class="dao.StudentDao"/>-->
	<package name="dao"></package>
</mappers>

2）dao介面

package dao;

import doMain.Student;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentDao {
    @Select ("select * from student")
    @Results(id="all",value = {//物件屬性和查詢結果進行對映,可不配置對映但要求property和column對應
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "male",column = "male"),
            @Result(property = "name",column = "name"),
            @Result(property = "math",column = "math"),
            @Result(property = "english",column = "english"),
    })
    List<Student> findAll();
}

2增刪改查

package dao;

import doMain.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface StudentDao {
    @Select ("select * from student")
    @Results(id="all",value = {//物件屬性和查詢結果進行對映,可不配置對映但要求property和column對應
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "male",column = "male"),
            @Result(property = "name",column = "name"),
            @Result(property = "math",column = "math"),
            @Result(property = "english",column = "english"),
    })
    List<Student> findAll();

    @Insert ("insert into student (id,male,name,math,english) values (#{id},#{male},#{name},#{math},#{english})")
    @SelectKey(statement = "select last_insert_id()", keyProperty = "id", before = true, resultType = int.class)
    void insertOne(Student st);

    @Update ("update student set male=#{male},name=#{name},math=#{math},english=#{english} where id=#{id}")
    void updateOne(Student st);

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

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

    @Select ("select * from student where name like '%${name}%'")
    List<Student> findByNamne(String name);

    @Select ("select count(*) from student ")
    int Count();
}

import dao.StudentDao;
import doMain.Student;
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.After;
import org.junit.Before;
import org.junit.Test;

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

public class mybatisAnnotationTest {
    SqlSessionFactory factory=null;
    SqlSession session=null;
    @Before
    public void init() throws IOException {
        InputStream in= Resources.getResourceAsStream ("sqlMapConfig.xml");
        factory=new SqlSessionFactoryBuilder().build (in);
        session=factory.openSession ();
        in.close ();
    }
    @After
    public void destroy(){
        session.commit ();
        session.close ();
    }
    @Test
    public void studentFindAll(){
        StudentDao dao=session.getMapper (StudentDao.class);
        List<Student> students=dao.findAll ();
        for (Student s:
             students) {
            System.out.println (s);
        }
    }
    @Test
    public void studentinsertOne(){
        Student student=new Student ();
        student.setName ("周迅");
        student.setMale ("女");
        student.setMath (66);
        student.setEnglish (72);
        StudentDao dao=session.getMapper (StudentDao.class);
        dao.insertOne (student);
    }
    @Test
    public void studentupdateOne(){
        Student student=new Student ();
        student.setId (15);
        student.setName ("周迅訊");
        student.setMale ("女");
        student.setMath (66);
        student.setEnglish (72);
        StudentDao dao=session.getMapper (StudentDao.class);
        dao.updateOne (student);
    }
    @Test
    public void studentdeleteOne(){
        StudentDao dao=session.getMapper (StudentDao.class);
        dao.deleteOne (10);
    }
    @Test
    public void studentfindById(){
        StudentDao dao=session.getMapper (StudentDao.class);
        Student student=dao.findById (15);
        System.out.println (student);
    }
    @Test
    public void studentfindByNamne(){
        StudentDao dao=session.getMapper (StudentDao.class);
        List<Student> students=dao.findByNamne ("張");
        for (Student s:
             students) {
            System.out.println (s);
        }
    }
    @Test
    public void studentCount(){
        StudentDao dao=session.getMapper (StudentDao.class);
        int count=dao.Count ();
        System.out.println (count);
    }
}

3一對一和一對多關係表

package dao;

import doMain.Account;
import doMain.AccountUser;
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 org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface AccountDao {
    @Select ("select * from account")
    @Results(id="default" ,value = {
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "uid",column = "uid"),
            @Result(property = "money",column = "money"),
            @Result(property = "user",column = "uid",one=@One(select = "dao.UserDao.findById",fetchType = FetchType.EAGER)),//一對一
    })
    List<AccountUser> findAll();

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

package dao;

import doMain.User;
import doMain.UserAccount;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface UserDao {
    @Select ("select * from user")
    @Results(id="default",value = {
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "accounts",column = "id",many = @Many(select = "dao.AccountDao.findByUid",fetchType = FetchType.LAZY)),//一對多
    })
    List<UserAccount> findAll();

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

4二級快取

1）主配置檔案中

<settings>
	<setting name="cacheEnabled" value="true"></setting><!--預設開啟，可省略-->
</settings>

2）dao介面新增註解

package dao;

import doMain.User;
import doMain.UserAccount;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

@CacheNamespace(blocking = true)//開啟快取
public interface UserDao {
    @Select ("select * from user")
    @Results(id="default",value = {
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "accounts",column = "id",many = @Many(select = "dao.AccountDao.findByUid",fetchType = FetchType.LAZY)),
    })
    List<UserAccount> findAll();

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

注意

開啟快取的前提條件是，doMain裡所有實體類必須實現Serializable介面，否則會報錯