Java SSM篇3——Mybatis

梦里梦外; 2023-10-03 16:17 6阅读 0赞

## Java SSM篇3——Mybatis ##

### 1、JDBC存在的问题 ###

*  数据库连接创建、释放频繁造成系统资源浪费从而影响系统性能
 *  sql 语句在代码中硬编码，造成代码不易维护，实际应用 sql 变化的可能较大，sql 变动需要改变java 代码
 *  查询操作时，需要手动将结果集中的数据手动封装到实体中

### 2、解决方案 ###

*  数据库连接池
 *  配置文件
 *  反射、内省

### 3、ORM是什么 ###

对象关系映射

*  O（对象模型）： 实体对象，即我们在程序中根据数据库表结构建立的一个个实体javaBean
 *  R（关系型数据库的数据结构）： 关系数据库领域的Relational（建立的数据库表）
 *  M（映射）： 从R（数据库）到O（对象模型）的映射，可通过XML文件映射

### 3、Mybatis简介 ###

MyBatis是一个优秀的基于ORM的半自动轻量级持久层框架，它对jdbc的操作数据库的过程进行封装， 使开发者只需要关注 SQL 本身，而不需要花费精力去处理例如注册驱动、创建connection、创建 statement、手动设置参数、结果集检索等jdbc繁杂的过程代码

[mybatis 官方文档：https://mybatis.org/mybatis-3/][mybatis _https_mybatis.org_mybatis-3]

![在这里插入图片描述][e42d04de03532154f89f848d71ce79b2.png]

### 4、mybatis快速入门 ###

#### 4.1、数据库准备 ####

CREATE TABLE person(
    	id INT PRIMARY KEY,
    	NAME VARCHAR(10),
    	PASSWORD VARCHAR(10)
    );
    INSERT INTO person VALUES (1,"root","123");

#### 4.2、创建maven工程，pom.xml导入依赖 ####

<dependencies>
        
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

**pom设置资源过滤**

<build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

#### 4.3、数据库配置文件（database.properties） ####

driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://132.232.82.49:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone =Asia/Shanghai
    username=root
    password=root

#### 4.4、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="database.properties"></properties>
        
        <typeAliases>
            <package name="club.winkto.bean"></package>
        </typeAliases>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <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="club.winkto.mapper"/>
        </mappers>
    </configuration>

#### 4.5、实体类 ####

public class Person {
        
        private int id;
        private String name;
        private String password;
    }

#### 4.6、Mapper接口 ####

public interface PersonMapper {
        ArrayList<Person> selectPerson();
        int insertPerson(Person person);
        int updatePerson(Person person);
        int deletePerson(int id);
    }

#### 4.7、映射文件 ####

<?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="club.winkto.mapper.PersonService">
        <select id="selectPerson" resultType="Person">
            select * from person;
        </select>
        <insert id="insertPerson" parameterType="Person">
            insert into person values (#{id},#{name},#{password});
        </insert>
        <update id="updatePerson" parameterType="Person">
            update person set name=#{name},password=#{password} where id=#{id};
        </update>
        <delete id="deletePerson" parameterType="int">
            delete from person where id=#{id};
        </delete>
    </mapper>

#### 4.8、mybatis工具类 ####

public class MybatisUtils {
        
        private static SqlSessionFactory sqlSessionFactory=null;
        static{
        
            String resource = "mybatis_config.xml";
            InputStream inputStream = null;
            try {
        
                inputStream = Resources.getResourceAsStream(resource);
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }
        public static SqlSession getSqlSession(){
        
            return sqlSessionFactory.openSession();
        }
    }

#### 4.9、测试类 ####

public class CRUD {
        
        @Test
        public void select(){
        
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            PersonService mapper = sqlSession.getMapper(PersonService.class);
            ArrayList<Person> people = mapper.selectPerson();
            for (Person person : people) {
        
                System.out.println(person);
            }
            System.out.println("========================");
            sqlSession.close();
        }
        @Test
        public void insert(){
        
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            PersonService mapper = sqlSession.getMapper(PersonService.class);
            int i = mapper.insertPerson(new Person(2, "bingbing", "123"));
            if (i>0){
        
                sqlSession.commit();
            }
            sqlSession.close();
        }
        @Test
        public void update(){
        
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            PersonService mapper = sqlSession.getMapper(PersonService.class);
            int i = mapper.updatePerson(new Person(2, "bingbing", "blingbling"));
            if (i>0){
        
                sqlSession.commit();
            }
            sqlSession.close();
        }
        @Test
        public void delete(){
        
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            PersonService mapper = sqlSession.getMapper(PersonService.class);
            int i = mapper.deletePerson(2);
            if (i>0){
        
                sqlSession.commit();
            }
            sqlSession.close();
        }
        @Test
        public void doCRUD(){
        
            select();
            insert();
            select();
            update();
            select();
            delete();
            select();
        }
    }

### 5、Mybatis配置解析 ###

*  properties
 *  settings
 *  typeAliases
 *  typeHandlers
 *  objectFactory
 *  plugins
 *  environments
    
     *  environment
        
         *  transactionManager
         *  dataSource
 *  databaseIdProvider
 *  mappers

#### 5.1、properties ####

##### 5.1.1、直接书写properties内容 #####

##### 5.1.2、引入properties文件 #####

driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://132.232.82.49:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone =Asia/Shanghai
    username=root
    password=root

##### 5.1.3、优先级问题 #####

如果一个属性在多个地方出现，优先级为

*  在方法体中给定的参数值
 *  在类路径或URL资源中读取的属性
 *  属性文件中的属性

#### 5.2、settings ####

[官方属性页：https://mybatis.org/mybatis-3/configuration.html\#settings][https_mybatis.org_mybatis-3_configuration.html_settings]

#### 5.3、typeAliases ####

##### 5.3.1、设置单个 #####

##### 5.3.2、设置包 #####

##### 5.3.3、Mybatis设置好的类型别名 #####

<table> 
 <thead> 
  <tr> 
   <th align="left">别名</th> 
   <th align="left">类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">_byte</td> 
   <td align="left">byte</td> 
  </tr> 
  <tr> 
   <td align="left">_long</td> 
   <td align="left">long</td> 
  </tr> 
  <tr> 
   <td align="left">_short</td> 
   <td align="left">short</td> 
  </tr> 
  <tr> 
   <td align="left">_int</td> 
   <td align="left">int</td> 
  </tr> 
  <tr> 
   <td align="left">_integer</td> 
   <td align="left">int</td> 
  </tr> 
  <tr> 
   <td align="left">_double</td> 
   <td align="left">double</td> 
  </tr> 
  <tr> 
   <td align="left">_float</td> 
   <td align="left">float</td> 
  </tr> 
  <tr> 
   <td align="left">_boolean</td> 
   <td align="left">boolean</td> 
  </tr> 
  <tr> 
   <td align="left">string</td> 
   <td align="left">String</td> 
  </tr> 
  <tr> 
   <td align="left">byte</td> 
   <td align="left">Byte</td> 
  </tr> 
  <tr> 
   <td align="left">long</td> 
   <td align="left">Long</td> 
  </tr> 
  <tr> 
   <td align="left">short</td> 
   <td align="left">Short</td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td align="left">Integer</td> 
  </tr> 
  <tr> 
   <td align="left">integer</td> 
   <td align="left">Integer</td> 
  </tr> 
  <tr> 
   <td align="left">double</td> 
   <td align="left">Double</td> 
  </tr> 
  <tr> 
   <td align="left">float</td> 
   <td align="left">Float</td> 
  </tr> 
  <tr> 
   <td align="left">boolean</td> 
   <td align="left">Boolean</td> 
  </tr> 
  <tr> 
   <td align="left">date</td> 
   <td align="left">Date</td> 
  </tr> 
  <tr> 
   <td align="left">decimal</td> 
   <td align="left">BigDecimal</td> 
  </tr> 
  <tr> 
   <td align="left">bigdecimal</td> 
   <td align="left">BigDecimal</td> 
  </tr> 
  <tr> 
   <td align="left">object</td> 
   <td align="left">Object</td> 
  </tr> 
  <tr> 
   <td align="left">map</td> 
   <td align="left">Map</td> 
  </tr> 
  <tr> 
   <td align="left">hashmap</td> 
   <td align="left">HashMap</td> 
  </tr> 
  <tr> 
   <td align="left">list</td> 
   <td align="left">List</td> 
  </tr> 
  <tr> 
   <td align="left">arraylist</td> 
   <td align="left">ArrayList</td> 
  </tr> 
  <tr> 
   <td align="left">collection</td> 
   <td align="left">Collection</td> 
  </tr> 
  <tr> 
   <td align="left">iterator</td> 
   <td align="left">Iterator</td> 
  </tr> 
 </tbody> 
</table>

#### 5.4、typeHandlers（类型句柄） ####

可以重写类型句柄或者是创建你自己的方式来处理不支持或者是非标准的类型，只需要简单地实现org.mybaits.type包里的TypeHandler,并且映射到一个JAVA类型，然后再选定一个JDBC类型

##### 5.4.1、定义类型 #####

##### 5.4.2、实现TypeHandler #####

import org.apache.ibatis.type.JdbcType;
    import org.apache.ibatis.type.TypeHandler;
    
    import java.sql.CallableStatement;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class RuoyeTypeHandler<T> implements TypeHandler<T> {
        
        public void setParameter(PreparedStatement preparedStatement, int i, T t, JdbcType jdbcType) throws SQLException {
        
            preparedStatement.setString(i, (String) t);
        }
    
        public T getResult(ResultSet resultSet, String s) throws SQLException {
        
            return null;
        }
    
        public T getResult(ResultSet resultSet, int i) throws SQLException {
        
            return null;
        }
    
        public T getResult(CallableStatement callableStatement, int i) throws SQLException {
        
            return null;
        }
    }

##### 5.4.3、使用 #####

#### 5.5、ObjectFactory ####

##### 5.5.1、定义 #####

##### 5.5.2、继承DefaultObjectFactory #####

import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
    
    import java.util.List;
    import java.util.Properties;
    
    public class RuoyeObjectFactory extends DefaultObjectFactory {
        
        @Override
        public <T> T create(Class<T> type) {
        
            return super.create(type);
        }
    
        @Override
        public <T> T create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs) {
        
            return super.create(type, constructorArgTypes, constructorArgs);
        }
    
        public void setProperties(Properties properties) {
        
    
        }
    }

#### 5.6、plugins ####

#### 5.7、environments ####

虽然你可以配置多重环境，但是你只可以选择一对一的SqlsessionFactory实例

#### 5.8、Mappers ####

##### 5.8.1、单个映射 #####

##### 5.8.2、包映射 #####

### 6、注解开发（初步认识） ###

不再书写映射文件PersonMapper.xml

public interface PersonMapper {
        
        @Select("select * from person")
        ArrayList<Person> selectPerson();
        @Insert("insert into person values (#{id},#{name},#{password})")
        int insertPerson(Person person);
        @Update("update person set name=#{name},password=#{password} where id=#{id}")
        int updatePerson(Person person);
        @Delete("delete from person where id=#{id}")
        int deletePerson(@Param("id") int id);
    }

注册class文件，当然你也可以注册包

### 7、结果集映射（初步认识） ###

resultType：如果实体的属性名与表中字段名一致，将查询结果自动封装到实体类中

<select id="selectPerson" resultType="Person">
        select * from person;
    </select>

resutlMap：如果实体的属性名与表中字段名不一致，可以使用ResutlMap实现手动封装到实体类中

如果有查询结果有 字段与属性是对应的，可以省略手动封装
    <resultMap id="PersonMap" type="Person">
        
        <id column="id" property="id" />
        
        <result column="name" property="name" />
        <result column="password" property="password" />
    </resultMap>
    <select id="selectPerson" resultMap="PersonMap">
        select * from person;
    </select>

### 8、参数传递 ###

#### 8.1、封装map传递 ####

<select id="selectPerson1" parameterType="map" resultType="Person">
        select * from person where name=#{name} and password=#{password};
    </select>

public interface PersonMapper {
        
        ArrayList<Person> selectPerson1(Map map);
    }

@Test
    public void select1(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        HashMap<String, Object> hashMap = new HashMap<String, Object>();
        hashMap.put("name","root");
        hashMap.put("password","123");
        ArrayList<Person> people = mapper.selectPerson1(hashMap);
        for (Person person : people) {
        
            System.out.println(person);
        }
        System.out.println("========================");
        sqlSession.close();
    }

#### 8.2、注解传递 ####

<select id="selectPerson1" resultType="Person">
        select * from person where name=#{name} and password=#{password};
    </select>

public interface PersonMapper {
        
        ArrayList<Person> selectPerson1(@Param("name") String name,@Param("password") String password);
    }

@Test
    public void select1(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        ArrayList<Person> people = mapper.selectPerson1("root","123");
        for (Person person : people) {
        
            System.out.println(person);
        }
        System.out.println("========================");
        sqlSession.close();
    }

### 9、模糊查询 ###

#### 9.1、$\{\} 与 \#\{\} ####

*  通过 \#\{\} 可以实现preparedStatement向占位符中设置值，自动进行java类型和jdbc类型转换，\# \{\}可以有效防止sql注入
 *  通过 $\{\} 可以将parameterType 传入的内容拼接在sql中且不进行jdbc类型转换，会出现sql注入问题

#### 9.2、模糊查询操作 ####

<select id="selectPerson2" parameterType="string" resultType="Person">
        select * from person where name like "%"#{name}"%"
    </select>

public interface PersonMapper {
        
        ArrayList<Person> selectPerson2(String name);
    }

@Test
    public void select2(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        ArrayList<Person> people = mapper.selectPerson2("roo");
        for (Person person : people) {
        
            System.out.println(person);
        }
        System.out.println("========================");
        sqlSession.close();
    }

#### 9.3、返回主键 ####

##### 9.3.1、useGeneratedKeys #####

**只适用于主键自增的数据库，mysql和sqlserver支持，oracle不行**

<insert id="insertPerson1" parameterType="Person" useGeneratedKeys="true" keyProperty="id">
        insert into person (name,password) values (#{name},#{password});
    </insert>

public interface PersonMapper {
        
        int insertPerson1(Person person);
    }

@Test
    public void insert1(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        Person bingbing = new Person(0, "bingbing", "123");
        int i = mapper.insertPerson1(bingbing);
        System.out.println(bingbing.getId());
        if (i>0){
        
            sqlSession.commit();
        }
        sqlSession.close();
    }

##### 9.3.2、selectKey #####

**适用范围广，支持所有类型数据库**

<insert id="insertPerson2" parameterType="Person">
        insert into person (name,password) values (#{name},#{password});
        <selectKey  keyColumn="id" keyProperty="id" resultType="int" order="AFTER">
            SELECT LAST_INSERT_ID();
        </selectKey>
    </insert>

public interface PersonMapper {
        
        int insertPerson1(Person person);
    }

@Test
    public void insert2(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        Person bingbing = new Person(0, "bingbing", "123");
        int i = mapper.insertPerson1(bingbing);
        System.out.println(bingbing.getId());
        if (i>0){
        
            sqlSession.commit();
        }
        sqlSession.close();
    }

### 10、动态SQL ###

当我们要根据不同的条件，来执行不同的sql语句的时候，需要用到动态sql

#### 10.1、where、if ####

<select id="selectPerson3" resultType="Person">
        select * from person
        <where>
            <if test="name!=null">
                and name=#{name}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>

public interface PersonMapper {
        
        ArrayList<Person> selectPerson3(@Param("name") String name,@Param("password") String password);
    }

@Test
    public void select3(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        ArrayList<Person> people = mapper.selectPerson3(null,"123");
        for (Person person : people) {
        
            System.out.println(person);
        }
        System.out.println("========================");
        sqlSession.close();
    }

#### 10.2、set、if ####

<update id="updatePerson1" parameterType="Person">
        update person
        <set>
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
        </set>
        where id=#{id};
    </update>

public interface PersonMapper {
        
        int updatePerson1(Person person);
    }

@Test
    public void update1(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        int i = mapper.updatePerson1(new Person(2, "bingbing", "blingbling"));
        if (i>0){
        
            sqlSession.commit();
        }
        sqlSession.close();
    }

#### 10.3、Choose、when、otherwise ####

参考switch

<select id="selectPerson4" resultType="Person">
        select * from person
        <where>
            <choose>
                <when test="name!=null">
                    and name=#{name}
                </when>
                <otherwise>
                    and password=123
                </otherwise>
            </choose>
        </where>
    </select>

public interface PersonMapper {
        
        ArrayList<Person> selectPerson3(@Param("name") String name,@Param("password") String password);
    }

@Test
    public void select4(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        ArrayList<Person> people = mapper.selectPerson4(null,"123");
        for (Person person : people) {
        
            System.out.println(person);
        }
        System.out.println("========================");
        sqlSession.close();
    }

#### 10.4、foreach ####

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

public interface PersonMapper {
        ;
        ArrayList<Person> selectPerson5(ArrayList<Integer> arrayList);
    }

@Test
    public void select5(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        ArrayList<Integer> integers = new ArrayList<Integer>(Arrays.asList(1,5,6));
        ArrayList<Person> people = mapper.selectPerson5(integers);
        for (Person person : people) {
        
            System.out.println(person);
        }
        System.out.println("========================");
        sqlSession.close();
    }

### 11、SQL片段 ###

<update id="updatePerson1" parameterType="Person">
        update person
        <set>
            <include refid="whereif"></include>
        </set>
        where id=#{id};
    </update>
    <sql id="whereif">
        <if test="name!=null">
            name=#{name},
        </if>
        <if test="password!=null">
            password=#{password},
        </if>
    </sql>

public interface PersonMapper {
        
        int updatePerson1(Person person);
    }

### 12、分页插件 ###

#### 12.1、依赖导入 ####

<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>

#### 12.2、mybatis配置分页 ####

#### 12.3、测试 ####

@Test
    public void select6(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        PageHelper.startPage(1,2);
        ArrayList<Person> people = mapper.selectPerson();
        for (Person person : people) {
        
            System.out.println(person);
        }
        System.out.println("========================");
        PageInfo<Person> pageInfo = new PageInfo<Person>(people);
        System.out.println("总条数："+pageInfo.getTotal());
        System.out.println("总页数："+pageInfo.getPages());
        System.out.println("当前页："+pageInfo.getPageNum());
        System.out.println("每页显示长度："+pageInfo.getPageSize());
        System.out.println("是否第一页："+pageInfo.isIsFirstPage());
        System.out.println("是否最后一页："+pageInfo.isIsLastPage());
        sqlSession.close();
    }

### 13、多表查询（根据结果嵌套） ###

*  一对一：人与身份证号的关系
 *  一对多：用户与订单的关系，一个用户可以有多个订单，但是一个订单只能有一个用户
 *  多对多：用户与课程的关系，一个用户可以选多个课程，一个课程可以被多个用户选择

#### 13.1、一对一（多对一） ####

##### 13.1.1、数据库准备 #####

CREATE TABLE person(
    	pid INT PRIMARY KEY AUTO_INCREMENT,
    	pname VARCHAR(20),
    	ppassword VARCHAR(20)
    )
    CREATE TABLE idnum(
    	pid INT,
    	idnum VARCHAR(18)
    )
    ALTER TABLE ID ADD CONSTRAINT t2 FOREIGN KEY (pid) REFERENCES person(pid);

##### 13.1.2、实体类 #####

public class IDNum {
        
        private int pid;
        private String idnum;
    }

public class Person {
        
        private int pid;
        private String pname;
        private String ppassword;
        private IDNum idnum;
    }

##### 13.1.3、接口 #####

public interface PersonMapper {
        
        List<Person> selectPersonWithID();
    }

##### 13.1.4、映射文件 #####

*  一对一使用association标签关联
 *  property=“user” 封装实体的属性名
 *  javaType=“user” 封装实体的属性类型

<resultMap id="PersonIDMap" type="Person">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <result column="ppassword" property="ppassword" />
        <association property="idnum" javaType="IDNum">
            <id column="pid" property="pid" />
            <result column="idnum" property="idnum" />
        </association>
    </resultMap>
    <select id="selectPersonWithID" resultMap="PersonIDMap">
        select * from person,idnum where person.pid=idnum.pid
    </select>

##### 13.1.5、测试 #####

@Test
    public void test(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        List<Person> people = mapper.selectPersonWithID();
        for (Person person : people) {
        
            System.out.println(person);
        }
    }

#### 13.2、一对多 ####

##### 13.2.1、数据库准备 #####

CREATE TABLE person(
    	pid INT PRIMARY KEY AUTO_INCREMENT,
    	pname VARCHAR(20),
    	ppassword VARCHAR(20)
    )
    CREATE TABLE orders(
    	pid INT,
    	oid INT PRIMARY KEY AUTO_INCREMENT,
    	otime DATETIME,
    	oremark  VARCHAR(100)
    )
    ALTER TABLE orders ADD CONSTRAINT t1 FOREIGN KEY (pid) REFERENCES person(pid);

随便加几条数据

##### 13.2.2、实体类 #####

public class Person {
        
        private int pid;
        private String pname;
        private String ppassword;
        private List<Order> orders;
    }

public class Order {
        
        private int pid;
        private int oid;
        private Date date;
        private String oremark;
    }

##### 13.2.3、接口 #####

public interface PersonMapper {
        
        List<Person> selectPersonWithOrder();
    }

##### 13.2.4、映射文件 #####

*  一对多使用collection标签关联
 *  property=“orderList” 封装到集合的属性名
 *  ofType=“order” 封装集合的泛型类型

<resultMap id="PersonOrderMap" type="Person">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <result column="ppassword" property="ppassword" />
        <collection property="orders" ofType="Order">
            <id column="oid" property="oid" />
            <result column="pid" property="pid" />
            <result column="otime" property="otime" />
            <result column="oremark" property="oremark" />
        </collection>
    </resultMap>
    <select id="selectPersonWithOrder" resultMap="PersonOrderMap">
        SELECT * FROM person LEFT JOIN orders ON person.pid=orders.pid
    </select>

##### 13.2.5、测试 #####

@Test
    public void test1(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        List<Person> people = mapper.selectPersonWithOrder();
        for (Person person : people) {
        
            System.out.println(person);
        }
    }

#### 13.3、多对多 ####

##### 13.3.1、数据库准备 #####

CREATE TABLE person(
    	pid INT PRIMARY KEY AUTO_INCREMENT,
    	pname VARCHAR(20),
    	ppassword VARCHAR(20)
    )
    CREATE TABLE course(
    	cid INT PRIMARY KEY AUTO_INCREMENT,
    	cname VARCHAR(20)
    );
    CREATE TABLE person_course(
    	pid INT,
    	cid INT
    );
    ALTER TABLE person_course ADD CONSTRAINT t3 FOREIGN KEY (pid) REFERENCES person(pid);
    ALTER TABLE person_course ADD CONSTRAINT t4 FOREIGN KEY (cid) REFERENCES course(cid);

随便加几条数据

##### 13.3.2、实体类 #####

public class Person {
        
        private int pid;
        private String pname;
        private String ppassword;
        private List<Order> orders;
    }

public class Course {
        
        private int cid;
        private String cname;
    }

##### 13.3.3、接口 #####

public interface PersonMapper {
        
        List<Person> selectPersonWithCourse();
    }

##### 13.3.4、映射文件 #####

*  一对多使用collection标签关联
 *  property=“orderList” 封装到集合的属性名
 *  ofType=“order” 封装集合的泛型类型

<resultMap id="PersonCourseMap" type="Person">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <result column="ppassword" property="ppassword" />
        <collection property="courses" ofType="Course">
            <id column="cid" property="cid" />
            <result column="cname" property="cname" />
        </collection>
    </resultMap>
    <select id="selectPersonWithCourse" resultMap="PersonCourseMap">
        SELECT * FROM person
        LEFT JOIN person_course ON person.pid=person_course.pid
        LEFT JOIN course ON person_course.cid=course.cid
    </select>

##### 13.3.5、测试 #####

@Test
    public void test2(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        List<Person> people = mapper.selectPersonWithCourse();
        for (Person person : people) {
        
            System.out.println(person);
        }
    }

### 14、多表查询（根据查询嵌套） ###

#### 14.1、一对一（多对一） ####

##### 14.1.1、数据库准备 #####

##### 14.1.2、实体类 #####

public class IDNum {
        
        private int pid;
        private String idnum;
    }

public class Person {
        
        private int pid;
        private String pname;
        private String ppassword;
        private IDNum idnum;
    }

##### 14.1.3、接口 #####

public interface PersonMapper {
        
        List<Person> selectPersonWithID();
    }

##### 14.1.4、映射文件 #####

*  一对一使用association标签关联
 *  property=“user” 封装实体的属性名
 *  javaType=“user” 封装实体的属性类型
 *  column传递的参数
 *  select调用的查询语句

<resultMap id="PersonIDMap" type="Person">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <result column="ppassword" property="ppassword" />
        <association property="idnum" javaType="IDNum" column="pid" select="selectID"></association>
    </resultMap>
    <select id="selectPersonWithID" resultMap="PersonIDMap">
        select * from person
    </select>
    <select id="selectID" resultType="IDNum">
        select * from idnum where idnum.pid=#{pid}
    </select>

##### 14.1.5、测试 #####

#### 14.2、一对多 ####

##### 14.2.1、数据库准备 #####

随便加几条数据

##### 14.2.2、实体类 #####

public class Person {
        
        private int pid;
        private String pname;
        private String ppassword;
        private List<Order> orders;
    }

public class Order {
        
        private int pid;
        private int oid;
        private Date date;
        private String oremark;
    }

##### 14.2.3、接口 #####

public interface PersonMapper {
        
        List<Person> selectPersonWithOrder();
    }

##### 14.2.4、映射文件 #####

*  一对多使用collection标签关联
 *  property=“orderList” 封装到集合的属性名
 *  ofType=“order” 封装集合的泛型类型
 *  column传递的参数
 *  select调用的查询语句

<resultMap id="PersonOrderMap" type="Person">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <result column="ppassword" property="ppassword" />
        <collection property="orders" ofType="Order" column="pid" select="selectOrder"></collection>
    </resultMap>
    <select id="selectPersonWithOrder" resultMap="PersonOrderMap">
        SELECT * FROM person
    </select>
    <select id="selectOrder" parameterType="int" resultType="Order">
        SELECT * FROM orders where orders.pid=#{pid}
    </select>

##### 14.2.5、测试 #####

#### 14.3、多对多 ####

##### 14.3.1、数据库准备 #####

随便加几条数据

##### 14.3.2、实体类 #####

public class Person {
        
        private int pid;
        private String pname;
        private String ppassword;
        private List<Order> orders;
    }

public class Course {
        
        private int cid;
        private String cname;
    }

##### 14.3.3、接口 #####

public interface PersonMapper {
        
        List<Person> selectPersonWithCourse();
    }

##### 14.3.4、映射文件 #####

<resultMap id="PersonCourseMap" type="Person">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <result column="ppassword" property="ppassword" />
        <collection property="courses" ofType="Course" column="cid" select="selectCourse"></collection>
    </resultMap>
    <select id="selectPersonWithCourse" resultMap="PersonCourseMap">
        SELECT * FROM person
        LEFT JOIN person_course ON person.pid=person_course.pid
    </select>
    <select id="selectCourse" parameterType="int" resultType="Course">
        select * from course where course.cid=#{cid}
    </select>

##### 14.3.5、测试 #####

### 15、加载策略 ###

就是在需要用到数据时才进行加载，不需要用到数据时就不加载数据，延迟加载也称懒加载

**延迟加载是基于查询嵌套来实现的**

#### 15.1、优点 ####

先从单表查询，需要时再从关联表去关联查询，大大提高数据库性能，因为查询单表要比关联查询多张表 速度要快

#### 15.2、缺点 ####

因为只有当需要用到数据时，才会进行数据库查询，这样在大批量数据查询时，因为查询工作也要消耗时 间，所以可能造成用户等待时间变长，造成用户体验下降

#### 15.3、开启局部延迟加载 ####

在association和collection标签中都有一个fetchType属性，通过修改它的值，可以修改局部的加载策略

*  fetchType=“lazy” 懒加载策略
 *  fetchType=“eager” 立即加载策略

<resultMap id="PersonCourseMap" type="Person">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <result column="ppassword" property="ppassword" />
        <collection property="courses" ofType="Course" column="cid" select="selectCourse" fetchType="lazy"></collection>
    </resultMap>
    <select id="selectPersonWithCourse" resultMap="PersonCourseMap">
        SELECT * FROM person
        LEFT JOIN person_course ON person.pid=person_course.pid
    </select>
    <select id="selectCourse" parameterType="int" resultType="Course">
        select * from course where course.cid=#{cid}
    </select>

#### 15.4、延迟加载触发策略 ####

在配置了延迟加载策略后，发现即使没有调用关联对象的任何方法，但是在你调用当前对象的 equals、clone、hashCode、toString方法时也会触发关联对象的查询，可以在配置文件中使用lazyLoadTriggerMethods配置项覆盖掉上面四个方法

#### 15.5、全局延迟加载 ####

**局部的加载策略优先级高于全局的加载策略**

### 16、缓存 ###

通过缓存策略来减少数据库的查询次数， 从而提高性能

#### 16.1、一级缓存 ####

一级缓存是SqlSession级别的缓存，是默认开启的

在参数和SQL完全一样的情况下，使用同一个SqlSession对象调用一个Mapper方法，往往只执行一次SQL，因为使用SelSession第一次查询后，MyBatis会将其放在缓存中，以后再查询的时 候，如果没有声明需要刷新，并且缓存没有超时的情况下，SqlSession都会取出当前缓存的数据，而不 会再次发送SQL到数据库

![在这里插入图片描述][14928a1c32df9d683dbb7fdfd5773900.png]

一级缓存是SqlSession范围的缓存，执行SqlSession的C（增加）U（更新）D（删除）操作，或者调 用clearCache()、commit()、close()方法，都会清空缓存

**强制清空一级缓存**

sqlSession.clearCache();

**设置每次清空一级缓存**

#### 16.2、二级缓存 ####

二级缓存是namspace级别（跨sqlSession）的缓存，是默认不开启的

二级缓存的开启需要进行配置，实现二级缓存的时候，MyBatis要求返回的POJO必须是可序列化的。 也就是要求实现Serializable接口，配置方法很简单，只需要在映射XML文件配置 就可以开启 二级缓存了

二级缓存是mapper映射级别的缓存，多个SqlSession去操作同一个Mapper映射的sql语句，多个 SqlSession可以共用二级缓存，二级缓存是跨SqlSession的

mybatis的二级缓存因为是namespace级别，所以在进行多表查询时会产生**脏读**问题

![在这里插入图片描述][a35009b769dcac42a9a37aaf86496b78.png]

![在这里插入图片描述][a4202999911bdf5420325640e06a8ed3.png]

##### 16.2.1、mybatis核心配置开启二级缓存（默认开启，此步可以省略） #####

##### 16.2.2、在mapper文件中加入 #####

缓存参数配置

##### 16.2.3、在select语句上开启二级缓存 #####

标签中设置useCache=”true”代表当前这个statement要使用二级缓存

##### 16.2.4、修改实体类 #####

继承Serializable

### 17、注解（深度认识） ###

之前在映射文件中通过配置、、来实现复杂关系映射

使用注解开发后，我们可以使用 @Results、@Result，@One、@Many 注解组合完成复杂关系的配置

#### 17.1、根据结果嵌套 ####

我们直接来看根据结果嵌套，看完这个懂了

#### 17.2、根据查询嵌套 ####

##### 17.2.1、一对一 #####

**接口**

@Select("select * from person")
    @Results({
        
            @Result(column = "pid",property = "pid",id = true),
            @Result(column = "pname",property = "pname"),
            @Result(column = "ppassword",property = "ppassword"),
            @Result(column = "pid",property = "idnum",javaType = IDNum.class,
                    one = @One(select = "club.winkto.mapper.PersonMapperAnno.selectIDNum",fetchType = FetchType.EAGER))
    })
    @Select("select * from idnum where pid=#{pid}")
    IDNum selectIDNum(int pid);

**测试**

@Test
    public void test3(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapperAnno mapper = sqlSession.getMapper(PersonMapperAnno.class);
        List<Person> people = mapper.selectPersonWithID();
        for (Person person : people) {
        
            System.out.println(person);
        }
    }

##### 17.2.2、一对多 #####

**接口**

@Select("select * from person")
    @Results({
        
        @Result(column = "pid",property = "pid",id = true),
        @Result(column = "pname",property = "pname"),
        @Result(column = "ppassword",property = "ppassword"),
        @Result(column = "pid",property = "orders",javaType = List.class,
                many = @Many(select = "club.winkto.mapper.PersonMapperAnno.selectOrder",fetchType = FetchType.EAGER))
    })
    List<Person> selectPersonWithOrder();
    @Select("select * from orders where pid=#{pid}")
    List<Order> selectOrder(int pid);

**测试**

@Test
    public void test4(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapperAnno mapper = sqlSession.getMapper(PersonMapperAnno.class);
        List<Person> people = mapper.selectPersonWithOrder();
        for (Person person : people) {
        
            System.out.println(person);
        }
    }

##### 17.2.3、多对多 #####

**接口**

@Select("select * from person")
    @Results({
        
            @Result(column = "pid",property = "pid",id = true),
            @Result(column = "pname",property = "pname"),
            @Result(column = "ppassword",property = "ppassword"),
            @Result(column = "pid",property = "courses",javaType = List.class,
                    many = @Many(select = "club.winkto.mapper.PersonMapperAnno.selectCourse",fetchType = FetchType.EAGER))
    })
    List<Person> selectPersonWithCourse();
    @Select("select * from course,person_course where person_course.pid=#{pid} and person_course.cid=course.cid")
    List<Course> selectCourse(int pid);

**测试**

@Test
    public void test5(){
        
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PersonMapperAnno mapper = sqlSession.getMapper(PersonMapperAnno.class);
        List<Person> people = mapper.selectPersonWithCourse();
        for (Person person : people) {
        
            System.out.println(person);
        }
    }

#### 17.3、注解开启二级缓存 ####

在接口上使用@CacheNamespace即可

#### 17.4、注解开启延迟加载 ####

*  fetchType = FetchType.LAZY
 *  fetchType = FetchType.EAGER
 *  fetchType = FetchType.DEFAULT（采用全局配置）

[mybatis _https_mybatis.org_mybatis-3]: https://mybatis.org/mybatis-3/
[e42d04de03532154f89f848d71ce79b2.png]: https://img-blog.csdnimg.cn/img_convert/e42d04de03532154f89f848d71ce79b2.png
[https_mybatis.org_mybatis-3_configuration.html_settings]: https://mybatis.org/mybatis-3/configuration.html#settings
[14928a1c32df9d683dbb7fdfd5773900.png]: https://img-blog.csdnimg.cn/img_convert/14928a1c32df9d683dbb7fdfd5773900.png
[a35009b769dcac42a9a37aaf86496b78.png]: https://img-blog.csdnimg.cn/img_convert/a35009b769dcac42a9a37aaf86496b78.png
[a4202999911bdf5420325640e06a8ed3.png]: https://img-blog.csdnimg.cn/img_convert/a4202999911bdf5420325640e06a8ed3.png