mybatis基础学习

叁歲伎倆 2022-10-12 12:58 212阅读 0赞

## 1：typeAliases别名 ##

### 1.1：简单说明 ###

当我们在mapper的xml文件中编写statement的时候，会通过resultType指定返回类型，通过parameterType指定参数的类型，正常的都是需要写类的全限定类名称，但是一般的类的全限定名称都是比较长的，这样就会造成文件的冗余，比如下面的配置：

<select id="queryByXxx" resultType="foo.bar.MyObj">
    	select ... from ... where ...
    </select>
    <insert id="insertXxx" parameterType="foo.bar.MyObj">
    	insert ... values ...
    </insert>
    <update id="updateXxx" parameterType="foo.bar.OhterObj">
    	update ... set ...
    </update>
    <insert id="insertOtherXxx" parameterType="foo.bar.OhterObj">
    	insert ... values ...
    </insert>

上面的`foo.bar.MyObj`重复了2次,`foo.bar.OhterObj`重复了2次，在编程中我们遇到这个问题一般都是通过定义变量的方式来解决，mybaits提供了`typeAliases`来实现变量定义的功能，如下：  
全局config配置：

pojo mapper:

<select id="queryByXxx" resultType="MyObj">
    	select ... from ... where ...
    </select>
    <insert id="insertXxx" parameterType="MyObj">
    	insert ... values ...
    </insert>
    <update id="updateXxx" parameterType="OhterObj">
    	update ... set ...
    </update>
    <insert id="insertOtherXxx" parameterType="OhterObj">
    	insert ... values ...
    </insert>

但是注意到`typeAlias`配置了2次，此时还是只有2个pojo，如果有20个pojo，就要配置20次，解决这个问题可以通过`package`配置扫描包，如下：  
全局config配置：

pojo mapper:

会将指定包路径下类的简单类名称作为别名在pojo的mapper中使用。

### 1.2：实例 ###

*  创建表

CREATE TABLE test_mybatis (
       id INT(32) PRIMARY KEY AUTO_INCREMENT,
       full_name VARCHAR(64) DEFAULT NULL,
       age INT(32)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

*  pom

<dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    	<version>5.1.32</version>
    </dependency>
    
    <dependency>
    	<groupId>org.mybatis</groupId>
    	<artifactId>mybatis</artifactId>
    	<version>3.2.8</version>
    </dependency>
    
    <dependency>
    	<groupId>org.slf4j</groupId>
    	<artifactId>slf4j-log4j12</artifactId>
    	<version>1.7.5</version>
    </dependency>

*  log4j.properties

log4j.rootLogger=DEBUG,A1
    log4j.logger.org.apache=INFO
    log4j.appender.A1=org.apache.log4j.ConsoleAppender
    log4j.appender.A1.layout=org.apache.log4j.PatternLayout
    log4j.appender.A1.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c]-[%p] %m%n

*  pojo

public class TestMybatis { 
        private Integer id;
        private String fullName;
        private Integer age;
    
        ...getter setter tostring...
    }

*  mapper 接口

public interface TestMyBatisMapper { 
        void insertTestMybatis(TestMybatis testMybatis);
    
        List<TestMybatis> queryTestMybatisAll();
    
        TestMybatis queryTestMybatisById(Integer id);
    }

*  mapper xml

*  全局mybatis-config.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>
        <settings>
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
        
        
        <environments default="test">
            
            <environment id="test">
                
                <transactionManager type="JDBC" />
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver" />
                    <property name="url" value="jdbc:mysql://127.0.0.1:3366/ssmdemo" />
                    <property name="username" value="root" />
                    <property name="password" value="root" />
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="mybatis/mapper/TestMyBatisMapper.xml"/>
        </mappers>
    </configuration>

*  测试代码

public class TestMyBatisMapperTest { 
        private TestMyBatisMapper testMyBatisMapper;
        private SqlSession sqlSession;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            this.sqlSession = sqlSession;
            this.testMyBatisMapper = sqlSession.getMapper(TestMyBatisMapper.class);
        }
    
        @Test
        public void insertTestMyBatis() { 
            TestMybatis newTestMyBatis = new TestMybatis();
            newTestMyBatis.setFullName(UUID.randomUUID().toString());
            newTestMyBatis.setAge(new Random().nextInt(100));
            testMyBatisMapper.insertTestMybatis(newTestMyBatis);
            this.sqlSession.commit();
        }
    
        @Test
        public void queryTestMybatisAll() { 
            List<TestMybatis> testMybatisList = testMyBatisMapper.queryTestMybatisAll();
            System.out.println(testMybatisList);
        }
    
        @Test
        public void queryTestMybatisById() { 
        }
    }

执行若干次`insertTestMyBatis`插入几条数据，如下：  
![在这里插入图片描述][20210705155121973.jpg_pic_center]

然后通过`queryTestMybatisAll`查询:

...snip...
    2021-07-05 15--53---15,390 [yudaosourcecode.mybatis.mapper.TestMyBatisMapper.queryTestMybatisAll]-[DEBUG] ==>  Preparing: select * from test_mybatis 
    2021-07-05 15--53---15,411 [yudaosourcecode.mybatis.mapper.TestMyBatisMapper.queryTestMybatisAll]-[DEBUG] ==> Parameters: 
    2021-07-05 15--53---15,422 [yudaosourcecode.mybatis.mapper.TestMyBatisMapper.queryTestMybatisAll]-[DEBUG] <==      Total: 2
    [{"age":8,"fullName":"859b0d16-8bb4-45eb-8961-45ba03e95d41","id":2}, {"age":50,"fullName":"f804a2da-85e7-4900-b8d1-a0ce1f0223df","id":3}]

### 1.3：使用typeAliases ###

在全局config文件中增加如下内容：

然后修改mapper xml为如下：

然后可以测试验证，功能依然是正常的。  
但是这种方式有个问题就是，每一个pojo都需要进行配置，解决的方案是使用`<package>`指定扫描的包，这样扫描后，类的简单名称就会作为别名，我们就可以直接在mapper xml文件中直接使用了，修改全局config如下：

然后可以测试验证，功能依然是正常的。  
另外当参数类型为`java.lang.Integer`，`java.lang.Long`等jdk提供的对象时我们是直接写的的简单类名称，如下：

<select id="xxx" resultType="Integer">
    	select count(*) from some_table
    </select>

可以这样做的原因是mybatis已经对这些类内置了别名的功能。

## 2：类型处理器 ##

### 2.1：简单说明 ###

为了处理java类型到数据库类型的转换，mybatis定义了`org.apache.ibatis.type.TypeHandler`接口，源码如下：

public interface TypeHandler<T> { 
      // 程序->数据库，设置参数
      // ps：预编译对象，i：参数的位置， parameter：java参数的值， jdbcType：数据库类型
      void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException;
      // 通过列名称获取结果
      T getResult(ResultSet rs, String columnName) throws SQLException;
      // 通过位置获取结果（ResultSet）
      T getResult(ResultSet rs, int columnIndex) throws SQLException;
      // 通过位置获取结果（CallableStatement）
      T getResult(CallableStatement cs, int columnIndex) throws SQLException;
    }

mybatis定义了很多的类型转换器，如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmcwOTA3_size_16_color_FFFFFF_t_70_pic_center]  
所以日常开发中，一般不需要使用到类型转换器，但是一些特殊的场景，mbatis默认提供的类型转换器无法完成转换时就需要自定义类型转换器了，下面看个例子。

### 2.2：实例 ###

本例在`1：typeAliases别名`基础上分析，因此会省略部分重复内容。

*  创建表

CREATE TABLE test_typehandler(
       id INT(32) PRIMARY KEY AUTO_INCREMENT,
       int_val INT(32)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8

*  定义pojo

public enum MyEnum { 
        ENABLED(100),
        DISABLED(-100);
    
        private int value;
    
        public int getValue() { 
            return value;
        }
    
        private MyEnum(int value) { 
            this.value = value;
        }
    }

public class TestTypehandler { 
        private Integer id;
        // 数据库存储的是int，这里使用枚举
        private MyEnum intVal;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public MyEnum getIntVal() { 
            return intVal;
        }
    
        public void setIntVal(MyEnum intVal) { 
            this.intVal = intVal;
        }
    
        @Override
        public String toString() { 
            return JSON.toJSONString(this);
        }
    }

*  定义mapper 接口

public interface TestTypehandlerMapper { 
        void insertTestTypehandler(TestTypehandler testMybatis);
        List<TestTypehandler> queryTestTypehandlerAll();
    }

*  定义mapper xml

*  定义自定义类型转换器

public class EnumAndIntTypeHandler extends BaseTypeHandler<MyEnum> { 
        // value->枚举MyEnum
        private Map<Integer, MyEnum> myEnumMap = new HashMap<>();
    
        public EnumAndIntTypeHandler() { 
            for (MyEnum value : MyEnum.values()) { 
                myEnumMap.put(value.getValue(), value);
            }
        }
    
        /** * 程序->数据库,转换java对象到参数的方法 * @param ps 指定sql的预编译对象 * @param i 参数索引位置 * @param parameter java类型的参数值，这里是MyEnum * @param jdbcType 数据库类型，这里是INTEGER * @throws SQLException */
        @Override
        public void setNonNullParameter(PreparedStatement ps, int i, MyEnum parameter, JdbcType jdbcType) throws SQLException { 
            System.out.println("EnumAndIntTypeHandler.setNonNullParameter");
            // 直接使用枚举的value作为数据库的值
            ps.setInt(i, parameter.getValue());
        }
    
        @Override
        public MyEnum getNullableResult(ResultSet rs, String columnName) throws SQLException { 
            System.out.println("EnumAndIntTypeHandler.getNullableResult");
            return myEnumMap.get(rs.getInt(columnName));
        }
    
        @Override
        public MyEnum getNullableResult(ResultSet rs, int columnIndex) throws SQLException { 
            System.out.println("EnumAndIntTypeHandler.getNullableResult");
            return myEnumMap.get(rs.getInt(columnIndex));
        }
    
        @Override
        public MyEnum getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { 
            System.out.println("EnumAndIntTypeHandler.getNullableResult");
            return myEnumMap.get(cs.getInt(columnIndex));
        }
    }

*  测试代码

public class TestTypehandlerMapperTest { 
    
        private TestTypehandlerMapper testTypehandlerMapper;
        private SqlSession sqlSession;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            this.sqlSession = sqlSession;
            this.testTypehandlerMapper = sqlSession.getMapper(TestTypehandlerMapper.class);
        }
    
        @Test
        public void insertTestTypehandler() { 
            TestTypehandler testTypehandler = new TestTypehandler();
            testTypehandler.setIntVal(MyEnum.DISABLED);
            this.testTypehandlerMapper.insertTestTypehandler(testTypehandler);
            this.sqlSession.commit();
        }
    
        @Test
        public void queryTestTypehandlerAll() { 
            System.out.println(this.testTypehandlerMapper.queryTestTypehandlerAll());
        }
    }

执行`insertTestTypehandler`，发现报如下错误，是因为mybatis不知道如何转换：

org.apache.ibatis.exceptions.PersistenceException: 
    ### Error updating database.  Cause: java.sql.SQLException: Incorrect integer value: 'DISABLED' for column 'int_val' at row 1
    ### The error may involve yudaosourcecode.mybatis.mapper.TestTypehandlerMapper.insertTestTypehandler-Inline
    ### The error occurred while setting parameters
    ### SQL: INSERT INTO test_typehandler (             int_val         )         VALUES         (             ?         );
    ### Cause: java.sql.SQLException: Incorrect integer value: 'DISABLED' for column 'int_val' at row 1

继续看。

*  在全局config中注册类型转换器

再次运行`insertTestTypehandler`，可以看到正常了，执行`queryTestTypehandlerAll`查询如下：

[{"id":2,"intVal":"DISABLED"}, {"id":3,"intVal":"DISABLED"}]

正常了！！！

## 3：拦截器插件plugin ##

本部分在`2：类型处理器`基础上分析，因此会省略部分重复内容。要实现的效果是打印执行的sql语句，操作如下。  
参考:[这里][Link 1],[这里][Link 2]。

### 3.1：定义拦截器 ###

import org.apache.ibatis.executor.statement.StatementHandler;
    import org.apache.ibatis.mapping.BoundSql;
    import org.apache.ibatis.plugin.*;
    
    import java.sql.Connection;
    import java.util.Properties;
    
    @Intercepts(value = { @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class})})
    public class MyInterceptor implements Interceptor { 
        @Override
        public Object intercept(Invocation invocation) throws Throwable { 
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            BoundSql boundSql = statementHandler.getBoundSql();
            Object obj = boundSql.getParameterObject();
            String sql = boundSql.getSql();
            System.out.println("MyInterceptor.intercept, sql is: " + sql);
            return invocation.proceed();
        }
    
        @Override
        public Object plugin(Object target) { 
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) { 
            System.out.println("MyInterceptor.setProperties");
        }
    }

### 3.2：在全局config中配置 ###

### 3.3：测试 ###

直接运行`2.2：实例`中测试代码方法`insertTestTypehandler`，输出如下：

...snip...
    MyInterceptor.intercept, sql is: INSERT INTO test_typehandler (
                int_val
            )
            VALUES
            (
                ?
            );
    ...snip...
    Process finished with exit code 0

## 4：ObjectFactory ##

当我们从数据库查询数据时，如果是resultType是某种类型的对象，mybatis是需要创建对应的实例对象的，为了完成这个工作定义了接口`org.apache.ibatis.reflection.factory.ObjectFactory`，源码如下：

org.apache.ibatis.reflection.factory.ObjectFactory
    // mybatis用来创建所有需要的对象
    public interface ObjectFactory { 
    
      // 在objectFactory标签中配置的property标签信息，如下配置：
      /* <objectFactory type="yudaosourcecode.mybatis.objectfactory.MyObjectFactory"> <property name="name" value="zhangsan"/> <property name="age" value="90"/> </objectFactory> properties的值是： "age" -> "90" "name" -> "zhangsan" */
      void setProperties(Properties properties);
    
      // 使用默认的构造函数创建实例
      <T> T create(Class<T> type);
    
      // 使用指定的构造函数和参数创建实例
      <T> T create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs);
    
      // 如果是java.util.Collection的子类则为true
      // Collection.class.isAssignableFrom(type) 
      <T> boolean isCollection(Class<T> type);
    
    }

其默认的实现类是`org.apache.ibatis.reflection.factory.DefaultObjectFactory`，源码如下：

public class DefaultObjectFactory implements ObjectFactory, Serializable { 
    
      private static final long serialVersionUID = -8855120656740914948L;
    
      public <T> T create(Class<T> type) { 
        // 调用重载版本
        return create(type, null, null);
      }
    
      public <T> T create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs) { 
        // 将接口类型的class转换为具体实现类的class，
        // 如java.util.Map.class使用java.util.HashMap.class
        // java.util.SortedSet.class使用java.util.TreeSet.class
        // java.util.Set.class使用java.util.HashSet.class等
        Class<?> classToCreate = resolveInterface(type);
        @SuppressWarnings("unchecked")
        T created = (T) instantiateClass(classToCreate, constructorArgTypes, constructorArgs);
        return created;
      }
    
      public void setProperties(Properties properties) { 
      }
    
      private <T> T instantiateClass(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs) { 
        try { 
          Constructor<T> constructor;
          // 默认构造函数的情况
          if (constructorArgTypes == null || constructorArgs == null) { 
            // 获取默认构造函数
            constructor = type.getDeclaredConstructor();
            if (!constructor.isAccessible()) { 
              constructor.setAccessible(true);
            }
            // 反射创建实例
            return constructor.newInstance();
          }
          // 根据给定的参数获取对应的构造函数
          constructor = type.getDeclaredConstructor(constructorArgTypes.toArray(new Class[constructorArgTypes.size()]));
          // 不可访问，则暴力访问
          if (!constructor.isAccessible()) { 
            constructor.setAccessible(true);
          }
          // 使用带有参数构造函数创建实例
          return constructor.newInstance(constructorArgs.toArray(new Object[constructorArgs.size()]));
        } catch (Exception e) { 
          StringBuilder argTypes = new StringBuilder();
          if (constructorArgTypes != null) { 
            for (Class<?> argType : constructorArgTypes) { 
              argTypes.append(argType.getSimpleName());
              argTypes.append(",");
            }
          }
          StringBuilder argValues = new StringBuilder();
          if (constructorArgs != null) { 
            for (Object argValue : constructorArgs) { 
              argValues.append(String.valueOf(argValue));
              argValues.append(",");
            }
          }
          throw new ReflectionException("Error instantiating " + type + " with invalid types (" + argTypes + ") or values (" + argValues + "). Cause: " + e, e);
        }
      }
    
      protected Class<?> resolveInterface(Class<?> type) { 
        Class<?> classToCreate;
        if (type == List.class || type == Collection.class || type == Iterable.class) { 
          classToCreate = ArrayList.class;
        } else if (type == Map.class) { 
          classToCreate = HashMap.class;
        } else if (type == SortedSet.class) {  
          classToCreate = TreeSet.class;
        } else if (type == Set.class) { 
          classToCreate = HashSet.class;
        } else { 
          classToCreate = type;
        }
        return classToCreate;
      }
    
      public <T> boolean isCollection(Class<T> type) { 
        return Collection.class.isAssignableFrom(type);
      }
    
    }

mybaits默认使用的是`org.apache.ibatis.reflection.factory.DefaultObjectFactory`,我们也可以自定义类然后通过`<objectFactory/>`标签进行配置，接下来我们看下如何做。

### 4.1：自定义ObjectFactory实现类 ###

public class MyObjectFactory extends DefaultObjectFactory { 
    
        @Override
        public void setProperties(Properties properties) { 
            System.out.println("MyObjectFactory.setProperties");
        }
    
        @Override
        public <T> T create(Class<T> type) { 
            System.out.println("MyObjectFactory.create 111, type is: " + type);
            // 内部会调用create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs)
            /* public <T> T create(Class<T> type) { return create(type, null, null); } */
            T t = super.create(type);
            return t;
        }
    
        @Override
        public <T> T create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs) { 
            System.out.println("MyObjectFactory.create 222, type is: " + type + ", constructorArgTypes is: "
                    + constructorArgTypes + ", constructorArgs is: " + constructorArgs);
            T t = super.create(type, constructorArgTypes, constructorArgs);
            return t;
        }
    
        @Override
        public <T> boolean isCollection(Class<T> type) { 
            System.out.println("MyObjectFactory.isCollection");
            boolean collection = super.isCollection(type);
            return collection;
        }
    }

### 4.2：在mybatis全局config文件中配置 ###

### 4.3：测试 ###

随意运行一个查询返回对象的方法，如下是我的：

@Test
    public void queryTestTypehandlerById() { 
        TestTypehandler testTypehandler = this.testTypehandlerMapper.queryTestTypehandlerById(6);
        System.out.println(testTypehandler);
    }

运行：

...snip...
    MyObjectFactory.create 111, type is: interface java.util.List
    MyObjectFactory.create 222, type is: interface java.util.List, constructorArgTypes is: null, constructorArgs is: null
    MyObjectFactory.create 111, type is: class yudaosourcecode.mybatis.TestTypehandler
    MyObjectFactory.create 222, type is: class yudaosourcecode.mybatis.TestTypehandler, constructorArgTypes is: null, constructorArgs is: null
    ...snip...

## 5：objectWrapperFactory ##

该标签用于将对象包装为ObjectWraper，主要的接口是ObjectWrapperFactory，ObjectWrapper，前者用户生成ObjectWrapper的包装对象，后者是包装后的对象，源码如下：

*  ObjectWrapperFactory

public interface ObjectWrapperFactory { 
      // 判断当前对象是否需要包装，需要的话则会调用getWrapperFor方法
      boolean hasWrapperFor(Object object);
      // 获取包装对象
      ObjectWrapper getWrapperFor(MetaObject metaObject, Object object);
    }

该类的默认实现类是`org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory`，源码如下：

public class DefaultObjectWrapperFactory implements ObjectWrapperFactory { 
      // 该方法返回false，所以默认不会生成包装对象
      public boolean hasWrapperFor(Object object) { 
        return false;
      }
      // 获取包装对象的方法直接异常，防止误生成包装对象
      public ObjectWrapper getWrapperFor(MetaObject metaObject, Object object) { 
        throw new ReflectionException("The DefaultObjectWrapperFactory should never be called to provide an ObjectWrapper.");
      }
    }

*  ObjectWrapper

public interface ObjectWrapper { 
    
      Object get(PropertyTokenizer prop);
    
      void set(PropertyTokenizer prop, Object value);
    
      String findProperty(String name, boolean useCamelCaseMapping);
    
      String[] getGetterNames();
    
      String[] getSetterNames();
    
      Class<?> getSetterType(String name);
    
      Class<?> getGetterType(String name);
    
      boolean hasSetter(String name);
    
      boolean hasGetter(String name);
    
      MetaObject instantiatePropertyValue(String name, PropertyTokenizer prop, ObjectFactory objectFactory);
      
      boolean isCollection();
      
      public void add(Object element);
      
      public <E> void addAll(List<E> element);
    
    }

## 6：databaseIdProvider ##

用于配置多个数据库的别名，然后在mapper的映射文件中就可以通过`databaseId`设置某个statement语句使用的是哪个数据库厂商。下面看个通过oracle和mysql获取当前时间的实例。

### 6.1：配置全局config ###

<databaseIdProvider type="DB_VENDOR">
        <property name="Oracle" value="oracle"/>
        <property name="MySQL" value="mysql"/>
    </databaseIdProvider>

### 6.2：mapper接口 ###

public interface TestTypehandlerMapper { 
        ...snip...
        String getTime();
    }

### 6.3：mapper xml ###

<select id="getTime" resultType="string" databaseId="mysql">
        select now() from dual
    </select>
    
    <select id="getTime" resultType="string" databaseId="oracle">
        select  'oralce'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  from dual
    </select>

> 有两点说明一下：  
> 1：当前使用的是哪个数据库厂商是mybatis通过数据库连接信息确定的。  
> 2：当存在两个相同statement ID，但是一个设置了databaseId（对应的是当前数据库厂商），一个没有设置databaseId，则后者会被丢弃。比如如下配置：

<select id="getTime" resultType="string">
        select  CONCAT(now(), "---222") from dual
    </select>
    <select id="getTime" resultType="string" databaseId="mysql">
        select  CONCAT(now(), "---111") from dual
    </select>

调用输出结果为`2021-07-13 18:18:54---111`。

### 6.4：测试 ###

@Test
    public void getTime() { 
       System.out.println(this.testTypehandlerMapper.getTime());
    }

运行：

...snip...
    2021-07-13 18:04:57.0
    Process finished with exit code 0

## 7：resultMap和parameterMap ##

paramterType的DTD定义如下：

<!ELEMENT parameterMap (parameter+)?>
    <!ATTLIST parameterMap
    id CDATA #REQUIRED
    type CDATA #REQUIRED
    >
    
    <!ELEMENT parameter EMPTY>
    <!ATTLIST parameter
    property CDATA #REQUIRED
    javaType CDATA #IMPLIED
    jdbcType CDATA #IMPLIED
    mode (IN | OUT | INOUT) #IMPLIED
    resultMap CDATA #IMPLIED
    scale CDATA #IMPLIED
    typeHandler CDATA #IMPLIED
    >

意思是可以有多个`parameter`子节点，可以有一个id属性（定义唯一标识），一个type属性（定义对象类型）。  
不管是传递参数到查询语句中，还是将查询结果返回，使用对象的方式一般都是最方便的，resultMap和parameterMap就是用来支持对象作为参数传递，以及查询结果直接映射到对象的，做法都是将对象的属性映射到参数或者是数据库的一个字段，并且可以指定参数的类型，方便mybatis进行相关对象操作，下面看下例子。

### 7.1：定义表 ###

CREATE TABLE test_resultmap_and_parametermap (
       id INT(32) NOT NULL PRIMARY KEY AUTO_INCREMENT,
       real_name VARCHAR(64),
       real_age INT(32)
    );

### 7.2：定义对应实体 ###

public class TestResultmapAndParametermap { 
        private Integer id;
        private String realName;
        private Integer realAge;
    
        ...getter setter totring...
    }

### 7.3：定义参数dto ###

public class TestResultmapAndParametermapQueryDto { 
        private Integer id;
        private String realName;
        private Integer realAge;
    
        ...getter setter tostring...
    }

### 7.3：定义mapper接口 ###

public interface TestResultmapAndParametermapMapper { 
        List<TestResultmapAndParametermap> getByQueryParamter(TestResultmapAndParametermapQueryDto resultmapAndParametermapQueryDto);
        void insertByQueryParamter(TestResultmapAndParametermapQueryDto resultmapAndParametermapQueryDto);
    }

### 7.4：定义mapper 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="yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper">
        
        <parameterMap id="myParameterMap" type="yudaosourcecode.mybatis.dto.TestResultmapAndParametermapQueryDto">
            
            <parameter property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/>
            <parameter property="realName" jdbcType="VARCHAR" javaType="java.lang.String"/>
            <parameter property="realAge" jdbcType="INTEGER" javaType="integer"/>
        </parameterMap>
    
        
        <resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestResultmapAndParametermap">
            
            
            <id property="id" column="id" javaType="integer" jdbcType="INTEGER"/>
            <result property="realName" column="real_name" javaType="string" jdbcType="VARCHAR"/>
            <result property="realAge" column="real_age" javaType="java.lang.Integer" jdbcType="INTEGER"/>
        </resultMap>
    
        <insert id="insertByQueryParamter" parameterMap="myParameterMap">
            INSERT INTO `test_resultmap_and_parametermap`
            (`id`, `real_name`, `real_age`)
            VALUES (#{id}, #{realName}, #{realAge})
        </insert>
        
        <select id="getByQueryParamter" resultMap="myResultMap">
            select * from test_resultmap_and_parametermap where real_name like #{realName} and real_age > #{realAge}
        </select>
    </mapper>

### 7.5：测试代码 ###

public class TestResultmapAndParametermapMapperTest { 
        private TestResultmapAndParametermapMapper testResultmapAndParametermapMapper;
        private SqlSession sqlSession;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            this.sqlSession = sqlSession;
            this.testResultmapAndParametermapMapper = sqlSession.getMapper(TestResultmapAndParametermapMapper.class);
        }
    
        @Test
        public void getByQueryParamter() { 
            TestResultmapAndParametermapQueryDto newDto = new TestResultmapAndParametermapQueryDto();
            newDto.setRealAge(30);
            newDto.setRealName("%8%");
            List<TestResultmapAndParametermap> resultmapAndParametermapList = testResultmapAndParametermapMapper.getByQueryParamter(newDto);
            JSONArray jsonArray = JSONObject.parseArray(resultmapAndParametermapList.toString());
            String pretty = JSON.toJSONString(jsonArray, SerializerFeature.PrettyFormat, SerializerFeature.WriteMapNullValue,
                    SerializerFeature.WriteDateUseDateFormat);
            System.out.println(pretty);
        }
    
        @Test
        public void insertByQueryParamter() { 
            TestResultmapAndParametermapQueryDto newDto = new TestResultmapAndParametermapQueryDto();
            newDto.setRealAge(new Random().nextInt(100));
            newDto.setRealName(UUID.randomUUID().toString());
            testResultmapAndParametermapMapper.insertByQueryParamter(newDto);
            sqlSession.commit();
        }
    
    }

执行若干次`insertByQueryParamter`，我本地执行后如下图：  
![在这里插入图片描述][20210716151339649.png]  
然后执行`getByQueryParamter`查看结果：

[
    	{ 
    		"realName":"81aeae87-ba10-4383-b288-b81a19065f5c",
    		"realAge":95,
    		"id":1
    	},
    	{ 
    		"realName":"97fe3831-849b-41bd-b26b-be41dd09f002",
    		"realAge":47,
    		"id":4
    	},
    	{ 
    		"realName":"88db23f8-78cb-4323-90ce-8b3c8fe18b36",
    		"realAge":64,
    		"id":6
    	},
    	{ 
    		"realName":"f2675951-d683-4a23-a490-757889f71331",
    		"realAge":87,
    		"id":7
    	}
    ]
    Process finished with exit code 0

## 8：缓存 ##

### 8.1：一级缓存 ###

mybatis的一级缓存默认开启切不可关闭，是内存缓存，在一个SqlSession中生效，如果是同一个查询，第二次将不会查询数据库，如下可能代码:

@Test
    public void getByQueryParamter1() { 
        TestResultmapAndParametermapQueryDto newDto = new TestResultmapAndParametermapQueryDto();
        newDto.setRealAge(30);
        newDto.setRealName("%8%");
        System.out.println("第1次查询。。。");
        List<TestResultmapAndParametermap> resultmapAndParametermapList = testResultmapAndParametermapMapper.getByQueryParamter(newDto);
        System.out.println(resultmapAndParametermapList);
        System.out.println("第2次查询。。。");
        resultmapAndParametermapList = testResultmapAndParametermapMapper.getByQueryParamter(newDto);
        System.out.println(resultmapAndParametermapList);
    }

输出如下：

第1次查询。。。
    ...snip...
    2021-07-17 14--13---27,234 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] ==>  Preparing: select * from test_resultmap_and_parametermap where real_name like ? and real_age > ? 
    2021-07-17 14--13---27,313 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] ==> Parameters: %8%(String), 30(Integer)
    2021-07-17 14--13---27,369 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] <==      Total: 4
    [{"id":2,"realAge":41,"realName":"6b9d73d3-3591-4df7-82ee-99d6a2e475cf"}, {"id":3,"realAge":56,"realName":"ab6a7d01-b05e-474e-8c38-f87345136a2c"}, {"id":4,"realAge":74,"realName":"4b8d91c9-a9af-4f98-998f-bc016fb9e7e5"}, {"id":6,"realAge":63,"realName":"04342b6c-b821-48c0-b745-defc0fbe17e7"}]
    第2次查询。。。
    [{"id":2,"realAge":41,"realName":"6b9d73d3-3591-4df7-82ee-99d6a2e475cf"}, {"id":3,"realAge":56,"realName":"ab6a7d01-b05e-474e-8c38-f87345136a2c"}, {"id":4,"realAge":74,"realName":"4b8d91c9-a9af-4f98-998f-bc016fb9e7e5"}, {"id":6,"realAge":63,"realName":"04342b6c-b821-48c0-b745-defc0fbe17e7"}]

可以看到第二次查询并没有执行查询数据库的操作。注意一级缓存虽然是默认开启不能禁用的，此时如果我们希望一次会话内的每次的查询都不走缓存怎么办呢？可以刷新缓存，具体的做法是在mapper xml对应的statment中配置`flushCache="true"`,如下：

<select id="getByQueryParamter" resultMap="myResultMap" flushCache="true">
        select * from test_resultmap_and_parametermap where real_name like #{realName} and real_age > #{realAge}
    </select>

然后再次测试执行：

第1次查询。。。
    ...snip...
    2021-07-17 14--29---14,328 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] ==>  Preparing: select * from test_resultmap_and_parametermap where real_name like ? and real_age > ? 
    2021-07-17 14--29---14,396 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] ==> Parameters: %8%(String), 30(Integer)
    2021-07-17 14--29---14,464 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] <==      Total: 4
    [{"id":2,"realAge":41,"realName":"6b9d73d3-3591-4df7-82ee-99d6a2e475cf"}, {"id":3,"realAge":56,"realName":"ab6a7d01-b05e-474e-8c38-f87345136a2c"}, {"id":4,"realAge":74,"realName":"4b8d91c9-a9af-4f98-998f-bc016fb9e7e5"}, {"id":6,"realAge":63,"realName":"04342b6c-b821-48c0-b745-defc0fbe17e7"}]
    第2次查询。。。
    2021-07-17 14--29---14,704 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] ==>  Preparing: select * from test_resultmap_and_parametermap where real_name like ? and real_age > ? 
    2021-07-17 14--29---14,707 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] ==> Parameters: %8%(String), 30(Integer)
    2021-07-17 14--29---14,712 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getByQueryParamter]-[DEBUG] <==      Total: 4
    [{"id":2,"realAge":41,"realName":"6b9d73d3-3591-4df7-82ee-99d6a2e475cf"}, {"id":3,"realAge":56,"realName":"ab6a7d01-b05e-474e-8c38-f87345136a2c"}, {"id":4,"realAge":74,"realName":"4b8d91c9-a9af-4f98-998f-bc016fb9e7e5"}, {"id":6,"realAge":63,"realName":"04342b6c-b821-48c0-b745-defc0fbe17e7"}]

可以看到第二次也执行了数据库查询。

### 8.2：二级缓存 ###

一级缓存是默认开启且不可关闭的，但是二级缓存想要使用的话就需要进行配置了，具体如下，首先在mapper xml中配置`<cache/>`,如下：

但是配置了这里也仅仅是代表`希望开启二级缓存`，想要真正的开启还需要在全局配置文件中配置`cacheEnable为true`,如下：

然后mapper xml中statement定义如下：

<select id="getById" resultMap="myResultMap" parameterType="integer">
         select * from test_resultmap_and_parametermap where id = #{id}
    </select>

测试代码：

@Test
    public void getByQueryParamter2() { 
        TestResultmapAndParametermapQueryDto newDto = new TestResultmapAndParametermapQueryDto();
        newDto.setRealAge(30);
        newDto.setRealName("%8%");
        SqlSession sqlSession1 = sqlSessionFactory.openSession();
        TestResultmapAndParametermapMapper testResultmapAndParametermapMapper1 =
                sqlSession1.getMapper(TestResultmapAndParametermapMapper.class);
        System.out.println("第1个sqlsession第1次查询");
        TestResultmapAndParametermap byId = testResultmapAndParametermapMapper1.getById(2);
        // 这里修改realName，只会在写到一级缓存中，不会更新到数据库中
        byId.setRealName("james");
        System.out.println(byId);
        System.out.println("第1个sqlsession第2次查询");
        // 这里会从一级缓存中获取数据
        System.out.println(testResultmapAndParametermapMapper1.getById(2));
        // 调用会话的close方法，数据写到sqlsessionfactory级别的二级缓存中
        sqlSession1.close();
    
        // 开启一个新的会话，会使用到二级缓存
        SqlSession sqlSession2 = sqlSessionFactory.openSession();
        TestResultmapAndParametermapMapper testResultmapAndParametermapMapper2 =
                sqlSession2.getMapper(TestResultmapAndParametermapMapper.class);
        System.out.println("第2个sqlsession第1次查询");
        // 此时会从二级缓存中获取，因此此时的realName值是james
        System.out.println(testResultmapAndParametermapMapper2.getById(2));
        System.out.println("第2个sqlsession第2次查询");
        // 此时会从二级缓存中获取，因此此时的realName值是james
        System.out.println(testResultmapAndParametermapMapper2.getById(2));
    }

运行：

第1个sqlsession第1次查询
    2021-07-17 16--36---23,251 [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]-[DEBUG] Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.0
    2021-07-17 16--36---23,262 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Opening JDBC Connection
    2021-07-17 16--36---23,262 [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Checked out connection 319977154 from pool.
    2021-07-17 16--36---23,262 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@131276c2]
    2021-07-17 16--36---23,264 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById]-[DEBUG] ==>  Preparing: select * from test_resultmap_and_parametermap where id = ? 
    2021-07-17 16--36---23,318 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById]-[DEBUG] ==> Parameters: 2(Integer)
    2021-07-17 16--36---23,356 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById]-[DEBUG] <==      Total: 1
    {"id":2,"realAge":41,"realName":"james"}
    第1个sqlsession第2次查询
    2021-07-17 16--36---23,515 [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]-[DEBUG] Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.0
    {"id":2,"realAge":41,"realName":"james"}
    2021-07-17 16--36---23,524 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@131276c2]
    2021-07-17 16--36---23,528 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@131276c2]
    2021-07-17 16--36---23,528 [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Returned connection 319977154 to pool.
    第2个sqlsession第1次查询
    2021-07-17 16--36---23,606 [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]-[DEBUG] Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.3333333333333333
    {"id":2,"realAge":41,"realName":"james"}
    第2个sqlsession第2次查询
    2021-07-17 16--36---23,607 [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]-[DEBUG] Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.5
    {"id":2,"realAge":41,"realName":"james"}

`第1个sqlsession第1次查询`中输出`Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.0`说明先尝试从二级缓存中获取，但是没有命中，因此此时的命中率为`0/1=0`,然后尝试从一级缓存中获取，但是一级缓存中也没有，最终查询数据库，注意将realName修改为了james（不会影响数据库）。  
`第1个sqlsession第2次查询`，首先尝试从二级缓存中获取，但是没有查到，因此输出`Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.0`,然后尝试从一级缓存中获取，可以查询到，因此不再查询数据库。  
`第2个sqlsession第1次查询`，首先尝试从二级缓存中获取，此时可以获取到因此输出`Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.3333333333333333`,其中命中率是通过`1/3`计算得到的。  
`第2个sqlsession第2次查询`，首先尝试从二级缓存中获取，可以获取到，因此输出`Cache Hit Ratio [yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper]: 0.5`,其中的0.5是通过`2/4`计算得到的。  
此时的二级缓存使用的是系统提供的缓存类，我们也可以自己实现缓存类，方法是实现`org.apache.ibatis.cache.Cache`接口，如下：

public class MyMybatisLevel2Cache implements Cache { 
        private String id;
        public MyMybatisLevel2Cache() { }
        // 强制要求有该构造函数，否则报错
        public MyMybatisLevel2Cache(String id) { 
            this.id = id;
        }
    
        // 返回缓存的唯一标示
        @Override
        public String getId() { 
            return id;
        }
        Map<Object, Object> dongshidaddyCacheMap = new HashMap<>();
        @Override
        public void putObject(Object key, Object value) { 
            System.out.println("存放缓存键是: " + key + ", 值是: " + value);
            dongshidaddyCacheMap.put(key, value);
        }
    
        @Override
        public Object getObject(Object key) { 
            Object cacheValue = dongshidaddyCacheMap.get(key);
            System.out.println("存放缓存键是: " + key + ", 值是: " + cacheValue);
            return cacheValue;
        }
    
        @Override
        public Object removeObject(Object key) { 
            Object remove = dongshidaddyCacheMap.remove(key);
            return remove;
        }
    
        @Override
        public void clear() { 
            dongshidaddyCacheMap.clear();
        }
    
        @Override
        public int getSize() { 
            return dongshidaddyCacheMap.size();
        }
    
        @Override
        public ReadWriteLock getReadWriteLock() { 
            return new ReentrantReadWriteLock();
        }
    }

然后修改`<cache>`标签为`<cache type="yudaosourcecode.mybatis.cache.MyMybatisLevel2Cache"/>`,再次运行，其它都是一样的，除了使用了自定义的缓存类：

第1个sqlsession第1次查询
    存放缓存键是: -543890290:3258524287:yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById:0:2147483647:select * from test_resultmap_and_parametermap where id = ?:2, 值是: null
    2021-07-17 19--27---29,324 [dongshidaddy-cache]-[DEBUG] Cache Hit Ratio [dongshidaddy-cache]: 0.0
    2021-07-17 19--27---29,330 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Opening JDBC Connection
    2021-07-17 19--27---29,330 [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Checked out connection 648680157 from pool.
    2021-07-17 19--27---29,330 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@26aa12dd]
    2021-07-17 19--27---29,332 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById]-[DEBUG] ==>  Preparing: select * from test_resultmap_and_parametermap where id = ? 
    2021-07-17 19--27---29,386 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById]-[DEBUG] ==> Parameters: 2(Integer)
    2021-07-17 19--27---29,456 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById]-[DEBUG] <==      Total: 1
    {"id":2,"realAge":41,"realName":"james"}
    第1个sqlsession第2次查询
    存放缓存键是: -543890290:3258524287:yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById:0:2147483647:select * from test_resultmap_and_parametermap where id = ?:2, 值是: null
    2021-07-17 19--27---29,601 [dongshidaddy-cache]-[DEBUG] Cache Hit Ratio [dongshidaddy-cache]: 0.0
    {"id":2,"realAge":41,"realName":"james"}
    存放缓存键是: -543890290:3258524287:yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById:0:2147483647:select * from test_resultmap_and_parametermap where id = ?:2, 值是: [{"id":2,"realAge":41,"realName":"james"}]
    2021-07-17 19--27---29,602 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@26aa12dd]
    2021-07-17 19--27---29,603 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@26aa12dd]
    2021-07-17 19--27---29,603 [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Returned connection 648680157 to pool.
    第2个sqlsession第1次查询
    存放缓存键是: -543890290:3258524287:yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById:0:2147483647:select * from test_resultmap_and_parametermap where id = ?:2, 值是: [{"id":2,"realAge":41,"realName":"james"}]
    2021-07-17 19--27---29,604 [dongshidaddy-cache]-[DEBUG] Cache Hit Ratio [dongshidaddy-cache]: 0.3333333333333333
    {"id":2,"realAge":41,"realName":"james"}
    第2个sqlsession第2次查询
    存放缓存键是: -543890290:3258524287:yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper.getById:0:2147483647:select * from test_resultmap_and_parametermap where id = ?:2, 值是: [{"id":2,"realAge":41,"realName":"james"}]
    2021-07-17 19--27---29,604 [dongshidaddy-cache]-[DEBUG] Cache Hit Ratio [dongshidaddy-cache]: 0.5
    {"id":2,"realAge":41,"realName":"james"}

我们会有很多的mapper xml，如果是希望某个mapper xml直接使用另一个mapper xml二级缓存的配置则可以使用`<cache-ref/>`，如上例子中如果想要在另一个mapper xml中使用的话，则可以配置如下：

<cache-ref namespace="yudaosourcecode.mybatis.mapper.TestResultmapAndParametermapMapper"/>

相当于是配置`<cache type="yudaosourcecode.mybatis.cache.MyMybatisLevel2Cache"/>`。

> 注意`<cache-ref>`namespace属性的值，是`<cache>`标签中eviction属性对应的缓存类的getId方法的返回值，只不过默认是mapper xml的命名空间，这里需要注意！！！

## 9：resultMap的extend和autoMapping ##

### 9.1：说明 ###

extend是一个resultMap直接继承另一个resultMap中的`<result/>`节点信息。autoMapping是如果是查询结果的某个字段没有在resultMap中配置是否自动映射到pojo中，默认为true，下面分别看个例子。

### 9.2：autoMapping ###

*  建表语句

create table test_extend_and_automapping(
    	id int(32) primary key auto_increment,
    	myname varchar(64),
    	myage int(32)
    );

*  pojo

public class TestExtendAndAutoMapping { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getMyname() { 
            return myname;
        }
    
        public void setMyname(String myname) { 
            this.myname = myname;
        }
    
        public Integer getMyage() { 
            return myage;
        }
    
        public void setMyage(Integer myage) { 
            this.myage = myage;
        }
    
        @Override
        public String toString() { 
            return "TestExtendAndAutoMapping{" +
                    "id=" + id +
                    ", myname='" + myname + '\'' +
                    ", myage=" + myage +
                    '}';
        }
    }

*  dto

public class TestExtendAndAutoMappingDto { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getMyname() { 
            return myname;
        }
    
        public void setMyname(String myname) { 
            this.myname = myname;
        }
    
        public Integer getMyage() { 
            return myage;
        }
    
        public void setMyage(Integer myage) { 
            this.myage = myage;
        }
    
        @Override
        public String toString() { 
            return "TestExtendAndAutoMapping{" +
                    "id=" + id +
                    ", myname='" + myname + '\'' +
                    ", myage=" + myage +
                    '}';
        }
    }

*  mapper 接口

public interface TestExtendAndAutoMappingMapper { 
        TestExtendAndAutoMapping fetchById(int id);
    }

*  mapper xml

*  测试代码

public class TestExtendAndAutoMappingMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestExtendAndAutoMappingMapper testExtendAndAutoMappingMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            this.sqlSession = this.sqlSessionFactory.openSession();
            this.testExtendAndAutoMappingMapper =
                    this.sqlSession.getMapper(TestExtendAndAutoMappingMapper.class);
        }
    
        @Test
        public void fetchById() { 
        }
    
        @Test
        public void insertOne() { 
            TestExtendAndAutoMappingDto testExtendAndAutoMappingDto
                    = new TestExtendAndAutoMappingDto();
            testExtendAndAutoMappingDto.setMyage(new Random().nextInt(100));
            testExtendAndAutoMappingDto.setMyname(UUID.randomUUID().toString());
            testExtendAndAutoMappingMapper.insertOne(testExtendAndAutoMappingDto);
            sqlSession.commit();
            sqlSession.close();
        }
    
        @Test
        public void testSelectOne() { 
            System.out.println(testExtendAndAutoMappingMapper.fetchById(2));
        }
    }

先执行`insertOne`插入数据，然后执行`testSelectOne`获取，尽管在resultMap中我们没有配置`myage`,但是因为在mapper xml的resultMap中配置的`autoMapping="true"`，所以也会自动映射，并查询出来，如下是我本地的：

2021-07-18 15--47---47,471 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestExtendAndAutoMappingMapper.fetchById]-[DEBUG] ==>  Preparing: select * from test_extend_and_automapping where id=? 
    2021-07-18 15--47---47,534 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestExtendAndAutoMappingMapper.fetchById]-[DEBUG] ==> Parameters: 2(Integer)
    2021-07-18 15--47---47,592 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestExtendAndAutoMappingMapper.fetchById]-[DEBUG] <==      Total: 1
    TestExtendAndAutoMapping{id=2, myname='07f07a2c-a9d0-474e-9cff-5576203c2716', myage=27}

现在修改`autoMapping="true"`为`autoMapping="false"`，则myage的值就是null了，如下：

TestExtendAndAutoMapping{id=2, myname='07f07a2c-a9d0-474e-9cff-5576203c2716', myage=null}

### 9.3：extend ###

接上例，myage并没有配置，我们来通过单独的resultMap配置，然后对其进行extend，如下：

然后运行，myage属性就可以正常的查询出来了：

TestExtendAndAutoMapping{id=2, myname='07f07a2c-a9d0-474e-9cff-5576203c2716', myage=27}

## 10：resultMap的constructor ##

一般的，我们在配置resultMap时，都是通过`<result/>`节点来配置数据库字段到pojo属性的映射，此时mybatis是先通过pojo的默认构造函数创建实例，然后再通过对应属性的setter方法设置值，当在某些情况下属性没有对应的setter时，可以通过构造函数设置值，此时就需要用到`<constructor/>`，下面看个实例。

### 10.1：创建数据库表 ###

CREATE TABLE `test_resultmap_constructor` (
      `id` int(32) NOT NULL AUTO_INCREMENT, 
      `myname` varchar(64) DEFAULT NULL, 
      `myage` int(32) DEFAULT NULL, 
      PRIMARY KEY (`id`)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8

### 10.2：实体 ###

public class TestResultmapConstructor { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        //public TestResultmapConstructor() {}
    
        // 接收3个参数的构造函数
        public TestResultmapConstructor(Integer id, String myname, Integer myage) { 
            System.out.println("TestResultmapConstructor.TestResultmapConstructor 构造函数执行了");
            this.id = id;
            this.myname = myname;
            this.myage = myage;
        }
    
        // 注意只有getter，没有setter
        public Integer getId() { 
            return id;
        }
    
        public String getMyname() { 
            return myname;
        }
    
        public Integer getMyage() { 
            return myage;
        }
    
        @Override
        public String toString() { 
            return "TestResultmapConstructor{" +
                    "id=" + id +
                    ", myname='" + myname + '\'' +
                    ", myage=" + myage +
                    '}';
        }
    }

### 10.3：dto ###

public class TestResultmapConstructorDto { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getMyname() { 
            return myname;
        }
    
        public void setMyname(String myname) { 
            this.myname = myname;
        }
    
        public Integer getMyage() { 
            return myage;
        }
    
        public void setMyage(Integer myage) { 
            this.myage = myage;
        }
    
        @Override
        public String toString() { 
            return "TestResultmapConstructorDto{" +
                    "id=" + id +
                    ", myname='" + myname + '\'' +
                    ", myage='" + myage + '\'' +
                    '}';
        }
    }

### 10.4：接口 ###

public interface TestResultmapConstructorMapper { 
        void insertByQueryParamter(TestResultmapConstructorDto testResultmapConstructorDto);
        TestResultmapConstructor getById(int id);
    }

### 10.5：mapper xml ###

### 10.6：测试代码 ###

public class TestResultmapConstructorMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestResultmapConstructorMapper testResultmapConstructorMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            SqlSession sqlSession = sqlSessionFactory.openSession();
            this.sqlSession = sqlSession;
            this.testResultmapConstructorMapper = sqlSession.getMapper(TestResultmapConstructorMapper.class);
        }
    
        @Test
        public void insertByQueryParamter() { 
            TestResultmapConstructorDto testResultmapConstructorDto = new TestResultmapConstructorDto();
            testResultmapConstructorDto.setMyage(new Random().nextInt(100));
            testResultmapConstructorDto.setMyname(UUID.randomUUID().toString());
            testResultmapConstructorMapper.insertByQueryParamter(testResultmapConstructorDto);
            sqlSession.commit();
            sqlSession.close();
        }
    
        @Test
        public void getById() { 
            System.out.println(testResultmapConstructorMapper.getById(2));
        }
    }

先执行`insertByQueryParamter`插入测试数据，然后执行`getById`，输出如下：

2021-07-19 15--30---57,044 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapConstructorMapper.getById]-[DEBUG] ==>  Preparing: select * from test_resultmap_constructor where id=? 
    2021-07-19 15--30---57,063 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapConstructorMapper.getById]-[DEBUG] ==> Parameters: 2(Integer)
    TestResultmapConstructor.TestResultmapConstructor 构造函数执行了
    2021-07-19 15--30---57,074 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapConstructorMapper.getById]-[DEBUG] <==      Total: 1
    TestResultmapConstructor{id=2, myname='e259b850-3e54-402e-8239-6409818568c1', myage=20}

可以看到通过构造函数创建了实例。

## 11：resultMap的collection，association ##

collection是用来指定属性为集合的情况，association是用来指定属性是一个对象的情况，其实就是一对多的关联，下面来看个例子。

### 11.1：创建表 ###

*  一对多中的一方的表

CREATE TABLE `test_resultmap_collection_one` (
      `id` int(32) NOT NULL AUTO_INCREMENT,
      `onename` varchar(64) DEFAULT NULL,
      `oneage` int(32) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

*  一对多中的多方的表

CREATE TABLE `test_resultmap_collection_many` (
      `id` int(32) NOT NULL AUTO_INCREMENT,
      `manyname` varchar(64) DEFAULT NULL,
      `manyage` int(32) DEFAULT NULL,
      `oneid` int(32) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

### 11.2：model ###

*  一对多中的一方的model

public class TestResultmapCollectionOne { 
        private Integer id;
        private String onename;
        private Integer oneage;
        private List<TestResultmapCollectionMany> testResultmapCollectionManyList;
    
        ...snip getter setter tostring...
    
    }

*  一对多中的多方的model

public class TestResultmapCollectionMany { 
        private Integer id;
        private String manyname;
        private Integer manyage;
        private TestResultmapCollectionOne testResultmapCollectionOne;
    
        ...snip getter setter tostring...
    
    }

### 11.3：dto ###

*  一对多中的一方的dto

public class TestResultmapCollectionOneDto { 
        private Integer id;
        private String onename;
        private Integer oneage;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getOnename() { 
            return onename;
        }
    
        public void setOnename(String onename) { 
            this.onename = onename;
        }
    
        public Integer getOneage() { 
            return oneage;
        }
    
        public void setOneage(Integer oneage) { 
            this.oneage = oneage;
        }
    
        @Override
        public String toString() { 
            return "TestResultmapCollectionOneDto{" +
                    "id=" + id +
                    ", onename='" + onename + '\'' +
                    ", oneage=" + oneage +
                    '}';
        }
    }

*  一对多中的多方的dto

public class TestResultmapCollectionManyDto { 
        private Integer id;
        private String manyname;
        private Integer manyage;
        private Integer oneid;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getManyname() { 
            return manyname;
        }
    
        public void setManyname(String manyname) { 
            this.manyname = manyname;
        }
    
        public Integer getManynage() { 
            return manyage;
        }
    
        public void setManynage(Integer manynage) { 
            this.manyage = manynage;
        }
    
        public Integer getOneid() { 
            return oneid;
        }
    
        public void setOneid(Integer oneid) { 
            this.oneid = oneid;
        }
    
        @Override
        public String toString() { 
            return "TestResultmapCollectionManyDto{" +
                    "id=" + id +
                    ", manyname='" + manyname + '\'' +
                    ", manyage=" + manyage +
                    '}';
        }
    }

### 11.4：接口 ###

*  一对多中的一方的接口

public interface TestResultmapCollectionOneMapper { 
        int insertByQueryParamter(TestResultmapCollectionOneDto testResultmapCollectionOneDto);
        TestResultmapCollectionOne getById(int id);
    }

*  一对多中的多方的接口

public interface TestResultmapCollectionManyMapper { 
        int insertByQueryParamter(TestResultmapCollectionManyDto testResultmapCollectionManyDto);
        TestResultmapCollectionMany getById(int id);
    }

### 11.5：mapper xml ###

*  一对多中的一方mapper 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="yudaosourcecode.mybatis.mapper.TestResultmapCollectionOneMapper">
        <resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestResultmapCollectionOne">
            
            <id property="id" javaType="integer" jdbcType="INTEGER" column="id"/>
            <result property="onename" column="onename" javaType="string" jdbcType="VARCHAR"/>
            <result property="oneage" column="oneage" javaType="integer" jdbcType="INTEGER"/>
            
            <collection property="testResultmapCollectionManyList" javaType="java.util.List" ofType="yudaosourcecode.mybatis.model.TestResultmapCollectionMany">
                <result property="id" javaType="integer" jdbcType="INTEGER" column="manyid"/>
                <result property="manyname" column="manyname" javaType="string" jdbcType="VARCHAR"/>
                <result property="manyage" column="manyage" javaType="integer" jdbcType="INTEGER"/>
            </collection>
        </resultMap>
    
        <parameterMap id="myParameterType" type="yudaosourcecode.mybatis.dto.TestResultmapCollectionOneDto">
            <parameter property="id" javaType="integer" jdbcType="INTEGER"/>
            <parameter property="onename" javaType="string" jdbcType="VARCHAR"/>
            <parameter property="onenage" javaType="integer" jdbcType="INTEGER"/>
        </parameterMap>
        
        <insert id="insertByQueryParamter" parameterMap="myParameterType" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
            INSERT INTO `test_resultmap_collection_one`
            (`onename`,
             `oneage`)
            VALUES (#{onename},
                    #{oneage});
        </insert>
    
        <select id="getById" resultMap="myResultMap">
            SELECT
                t1.`id`,
                t1.`oneage`,
                t1.`onename`,
                t2.`id` AS 'manyid',
                t2.`manyname`,
                t2.`manyage`
            FROM
                test_resultmap_collection_one t1
                    LEFT JOIN `test_resultmap_collection_many` t2
                              ON t1.`id` = t2.`oneid`
            WHERE t1.`id` = #{id}
        </select>
    </mapper>

*  一对多中的多方mapper 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="yudaosourcecode.mybatis.mapper.TestResultmapCollectionManyMapper">
        <resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestResultmapCollectionMany">
            
            <id property="id" column="id" javaType="integer" jdbcType="INTEGER"/>
            <result property="manyname" column="manyname" javaType="string" jdbcType="VARCHAR"/>
            <result property="manyage" column="manyage" javaType="integer" jdbcType="INTEGER"/>
            <association property="testResultmapCollectionOne" javaType="yudaosourcecode.mybatis.model.TestResultmapCollectionOne">
                
                <id property="id" column="oneid" javaType="integer" jdbcType="INTEGER"/>
                <result property="onename" column="onename" javaType="string" jdbcType="VARCHAR"/>
                <result property="oneage" column="oneage" javaType="integer" jdbcType="INTEGER"/>
            </association>
        </resultMap>
        
        <parameterMap id="myParameterMap" type="yudaosourcecode.mybatis.dto.TestResultmapCollectionManyDto">
            
            <parameter property="id" javaType="integer" jdbcType="INTEGER"/>
            <parameter property="manyname" javaType="string" jdbcType="VARCHAR"/>
            <parameter property="manynage" javaType="integer" jdbcType="INTEGER"/>
            <parameter property="oneid" javaType="integer" jdbcType="INTEGER"/>
        </parameterMap>
    
        <insert id="insertByQueryParamter" parameterMap="myParameterMap">
            INSERT INTO `test_resultmap_collection_many`
            (`manyname`,
             `manyage`,
             `oneid`)
            VALUES (#{manyname},
                    #{manyage},
                    #{oneid})
        </insert>
        
        <select id="getById" resultMap="myResultMap">
            SELECT
                t1.`id`,
                t1.`manyage`,
                t1.`manyname`,
                t2.`id` 'oneid',
                t2.`oneage`,
                t2.`onename`
            FROM
                `test_resultmap_collection_many` t1
                    LEFT JOIN `test_resultmap_collection_one` t2
                              ON t1.`oneid` = t2.`id`
            WHERE t1.`id` = #{id}
        </select>
    </mapper>

### 11.6：测试代码 ###

*  一对多中的一方测试代码

public class TestResultmapCollectionOneMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestResultmapCollectionOneMapper testResultmapCollectionOneMapper;
        private TestResultmapCollectionManyMapper testResultmapCollectionManyMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            SqlSession sqlSession = sqlSessionFactory.openSession();
            this.sqlSession = sqlSession;
            this.testResultmapCollectionOneMapper = sqlSession.getMapper(TestResultmapCollectionOneMapper.class);
            this.testResultmapCollectionManyMapper = sqlSession.getMapper(TestResultmapCollectionManyMapper.class);
        }
    
        @Test
        public void insertByQueryParamter() { 
            TestResultmapCollectionOneDto testResultmapCollectionOneDto = new TestResultmapCollectionOneDto();
            testResultmapCollectionOneDto.setOneage(new Random().nextInt(100));
            testResultmapCollectionOneDto.setOnename(UUID.randomUUID().toString());
    
            this.testResultmapCollectionOneMapper.insertByQueryParamter(testResultmapCollectionOneDto);
            int generatedId = testResultmapCollectionOneDto.getId();
            System.out.println("生成的主键id是：" + generatedId);
    
            TestResultmapCollectionManyDto testResultmapCollectionManyDto1 = new TestResultmapCollectionManyDto();
            testResultmapCollectionManyDto1.setManynage(new Random().nextInt(100));
            testResultmapCollectionManyDto1.setManyname(UUID.randomUUID().toString());
            testResultmapCollectionManyDto1.setOneid(generatedId);
            this.testResultmapCollectionManyMapper.insertByQueryParamter(testResultmapCollectionManyDto1);
    
            TestResultmapCollectionManyDto testResultmapCollectionManyDto2 = new TestResultmapCollectionManyDto();
            testResultmapCollectionManyDto2.setManynage(new Random().nextInt(100));
            testResultmapCollectionManyDto2.setManyname(UUID.randomUUID().toString());
            testResultmapCollectionManyDto2.setOneid(generatedId);
            this.testResultmapCollectionManyMapper.insertByQueryParamter(testResultmapCollectionManyDto2);
    
            TestResultmapCollectionManyDto testResultmapCollectionManyDto3 = new TestResultmapCollectionManyDto();
            testResultmapCollectionManyDto3.setManynage(new Random().nextInt(100));
            testResultmapCollectionManyDto3.setManyname(UUID.randomUUID().toString());
            testResultmapCollectionManyDto3.setOneid(generatedId);
            this.testResultmapCollectionManyMapper.insertByQueryParamter(testResultmapCollectionManyDto3);
    
            this.sqlSession.commit();
            this.sqlSession.close();
        }
    
        @Test
        public void getById() { 
            TestResultmapCollectionOne testResultmapCollectionOne = testResultmapCollectionOneMapper.getById(10);
            System.out.println(testResultmapCollectionOne);
        }
    }

*  一对多中的多方测试代码

public class TestResultmapCollectionManyMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestResultmapCollectionManyMapper testResultmapCollectionManyMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            SqlSession sqlSession = sqlSessionFactory.openSession();
            this.sqlSession = sqlSession;
            this.testResultmapCollectionManyMapper = sqlSession.getMapper(TestResultmapCollectionManyMapper.class);
        }
    
        @Test
        public void insertByQueryParamter() { 
            TestResultmapCollectionManyDto testResultmapCollectionManyDto = new TestResultmapCollectionManyDto();
            testResultmapCollectionManyDto.setManynage(new Random().nextInt(100));
            testResultmapCollectionManyDto.setManyname(UUID.randomUUID().toString());
            testResultmapCollectionManyDto.setOneid(~(new Random().nextInt(100)));
            testResultmapCollectionManyMapper.insertByQueryParamter(testResultmapCollectionManyDto);
            sqlSession.commit();
            sqlSession.close();
        }
    
        @Test
        public void getById() { 
            TestResultmapCollectionMany testResultmapCollectionMany = testResultmapCollectionManyMapper.getById(2);
            System.out.println(testResultmapCollectionMany);
        }
    }

执行`TestResultmapCollectionOneMapperTest#insertByQueryParamter`插入数据后，执行`TestResultmapCollectionOneMapperTest#getById`测试，如下：

2021-07-20 13--44---19,612 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapCollectionOneMapper.getById]-[DEBUG] ==>  Preparing: SELECT t1.`id`, t1.`oneage`, t1.`onename`, t2.`id` AS 'manyid', t2.`manyname`, t2.`manyage` FROM test_resultmap_collection_one t1 LEFT JOIN `test_resultmap_collection_many` t2 ON t1.`id` = t2.`oneid` WHERE t1.`id` = ? 
    2021-07-20 13--44---19,633 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapCollectionOneMapper.getById]-[DEBUG] ==> Parameters: 10(Integer)
    2021-07-20 13--44---19,648 [dongshidaddy--^^--yudaosourcecode.mybatis.mapper.TestResultmapCollectionOneMapper.getById]-[DEBUG] <==      Total: 3
    {"id":10,"oneage":74,"onename":"fc77c3b0-779e-47b3-bb6d-18c10338c992","testResultmapCollectionManyList":[{"id":19,"manyage":76,"manyname":"70835637-6397-4dd4-a313-0c3957b9c53b"},{"id":20,"manyage":12,"manyname":"74252e88-2ab7-4831-bd5f-1a22b20ae0d1"},{"id":21,"manyage":71,"manyname":"37e1d1b9-b474-4819-af01-8da0cbae32c2"}]}

### 11.7：使用关联查询方式 ###

也可以通过关联查询的方式来进行多表关联的查询，即实现子查询的效果`子使用父的查询结果作为参数`，但是mybatis中的实现是通过定义多个select的statement，如下。

*  在一对多的一方mapper xml增加如下内容

<resultMap id="myResultMap1" type="yudaosourcecode.mybatis.model.TestResultmapCollectionOne">
        
        <id property="id" javaType="integer" jdbcType="INTEGER" column="id"/>
        <result property="onename" column="onename" javaType="string" jdbcType="VARCHAR"/>
        <result property="oneage" column="oneage" javaType="integer" jdbcType="INTEGER"/>
        
        <collection property="testResultmapCollectionManyList" column="{oneid=id}" select="yudaosourcecode.mybatis.mapper.TestResultmapCollectionManyMapper.getById1" ofType="yudaosourcecode.mybatis.model.TestResultmapCollectionMany"/>
    </resultMap>
     
    <select id="getById1" resultMap="myResultMap1">
        SELECT
            t1.`id`,
            t1.`oneage`,
            t1.`onename`
        FROM
            test_resultmap_collection_one t1 where t1.`id` = #{manyid}
    </select>

*  在一对多的多方mapper xml增加如下内容

<select id="getById1" resultMap="myResultMap">
        SELECT
            t1.`id`,
            t1.`manyage`,
            t1.`manyname`
        FROM
            `test_resultmap_collection_many` t1
        WHERE t1.`oneid` = #{oneid}
    </select>

*  修改一对多的一方mapper 接口

TestResultmapCollectionOne getById1(int id);

*  测试代码

@Test
    public void getById1() { 
        TestResultmapCollectionOne testResultmapCollectionOne = testResultmapCollectionOneMapper.getById1(10);
        System.out.println(testResultmapCollectionOne);
    }

运行：

{"id":10,"oneage":74,"onename":"fc77c3b0-779e-47b3-bb6d-18c10338c992","testResultmapCollectionManyList":[{"id":19,"manyage":76,"manyname":"70835637-6397-4dd4-a313-0c3957b9c53b"},{"id":20,"manyage":12,"manyname":"74252e88-2ab7-4831-bd5f-1a22b20ae0d1"},{"id":21,"manyage":71,"manyname":"37e1d1b9-b474-4819-af01-8da0cbae32c2"}]}

## 12：resultMap的discriminator ##

这是mybatis提供的鉴别器功能，用于依赖某列的结果值，来动态的设置另一个属性的值，使用场景不多，但是了解下也比较有必要。我们来改造`11：resultMap的collection，association`中的`yudaosourcecode.mybatis.mapper.TestResultmapCollectionOneMapper.myResultMap`，再来贴下当前的内容：

<resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestResultmapCollectionOne">
        <id property="id" javaType="integer" jdbcType="INTEGER" column="id"/>
        <result property="onename" column="onename" javaType="string" jdbcType="VARCHAR"/>
        <result property="oneage" column="oneage" javaType="integer" jdbcType="INTEGER"/>
        <collection property="testResultmapCollectionManyList" javaType="java.util.List" ofType="yudaosourcecode.mybatis.model.TestResultmapCollectionMany">
            <result property="id" javaType="integer" jdbcType="INTEGER" column="manyid"/>
            <result property="manyname" column="manyname" javaType="string" jdbcType="VARCHAR"/>
            <result property="manyage" column="manyage" javaType="integer" jdbcType="INTEGER"/>
        </collection>
        <collection property="testResultmapCollectionManyMap" javaType="java.util.List" ofType="java.util.Map">
            <result property="id" javaType="integer" jdbcType="INTEGER" column="manyid"/>
            <result property="manyname" column="manyname" javaType="string" jdbcType="VARCHAR"/>
            <result property="manyage" column="manyage" javaType="integer" jdbcType="INTEGER"/>
        </collection>
    </resultMap>
    
    <select id="getById" resultMap="myResultMap">
        SELECT
            t1.`id`,
            t1.`oneage`,
            t1.`onename`,
            t2.`id` AS 'manyid',
            t2.`manyname`,
            t2.`manyage`
        FROM
            test_resultmap_collection_one t1
                LEFT JOIN `test_resultmap_collection_many` t2
                          ON t1.`id` = t2.`oneid`
        WHERE t1.`id` = #{id}
    </select>

其中的`testResultmapCollectionManyList`用来查询一个封装`yudaosourcecode.mybatis.model.TestResultmapCollectionMany`对象的list，`testResultmapCollectionManyMap`查询封装map的list，我们现在改造，当查询结果的id列为`1`的时候，启用`testResultmapCollectionManyList`，当id列的值为`2`时启用`testResultmapCollectionManyMap`，修改后如下：

*  测试代码

@Test
    public void getById() { 
        TestResultmapCollectionOne testResultmapCollectionOne = testResultmapCollectionOneMapper.getById(1);
        if (testResultmapCollectionOne.getTestResultmapCollectionManyList() != null) { 
            System.out.println("testResultmapCollectionManyList元素个数为：" + testResultmapCollectionOne.getTestResultmapCollectionManyList().size());
        } else { 
            System.out.println("未查询出testResultmapCollectionManyList");
        }
        if (testResultmapCollectionOne.getTestResultmapCollectionManyMap() != null) { 
            System.out.println("testResultmapCollectionManyMap元素个数为：" + testResultmapCollectionOne.getTestResultmapCollectionManyMap().size());
        } else { 
            System.out.println("未查询出testResultmapCollectionManyMap");
        }
    }

id为1时测试：

testResultmapCollectionManyList元素个数为：15
    未查询出testResultmapCollectionManyMap

id为2时测试：

未查询出testResultmapCollectionManyList
    testResultmapCollectionManyMap元素个数为：2

## 13：`<sql>`标签 ##

`<sql>`标签，正如其名字，是用来封装sql语句的，定义完毕后,可以通过`<include>`来引用sql语句，这样做的好处sql语句可复用，减少statement的复杂程度，下面来看个例子。

### 13.1：创建表 ###

CREATE TABLE `test_mapper_sql` (
      `id` int(32) NOT NULL AUTO_INCREMENT,
      `myname` varchar(63) DEFAULT NULL,
      `myage` int(32) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

### 13.2：实体 ###

public class TestMapperSql { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...snip getter setter tostring...
    }

### 13.3：dto ###

public class TestMapperSqlDto { 
        private Integer id;
        private String myname;
        private Integer myage;
    
        ...snip getter setter tostring...
    }

### 13.4：接口 ###

public interface TestMapperSqlMapper { 
        List<TestMapperSql> getByQueryParamter(TestMapperSqlDto testMapperSqlDto);
        void insertByQueryParamter(TestMapperSqlDto testMapperSqlDto);
    }

### 13.5：mapper xml ###

### 13.6：测试代码 ###

public class TestMapperSqlMapperTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
        private TestMapperSqlMapper testMapperSqlMapper;
    
        @Before
        public void setUp() throws Exception { 
            String resource = "mybatis/mybatis-config-1.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            this.sqlSessionFactory = sqlSessionFactory;
            this.sqlSession = this.sqlSessionFactory.openSession();
            this.testMapperSqlMapper =
                    this.sqlSession.getMapper(TestMapperSqlMapper.class);
        }
    
    
        @Test
        public void getByQueryParamter() { 
            TestMapperSqlDto testMapperSqlDto = new TestMapperSqlDto();
            testMapperSqlDto.setId(1);
            System.out.println(this.testMapperSqlMapper.getByQueryParamter(testMapperSqlDto));
        }
    
        @Test
        public void insertByQueryParamter() { 
            TestMapperSqlDto testMapperSqlDto = new TestMapperSqlDto();
            testMapperSqlDto.setMyage(new Random().nextInt(100));
            testMapperSqlDto.setMyname(UUID.randomUUID().toString());
            this.testMapperSqlMapper.insertByQueryParamter(testMapperSqlDto);
    
            this.sqlSession.commit();
            this.sqlSession.close();
        }
    }

先执行`insertByQueryParamter`插入数据，然后执行`getByQueryParamter`查询数据，我本地测试如下：

[{"id":1,"myage":18,"myname":"ed2b1868-17bf-40da-a5ce-1af643f99832"}]

文章太长了，再起一篇，到[这里][Link 3]。

## 写在后面 ##

参考文章列表：  
https://www.cnblogs.com/diffx/p/10611082.html\#label1  
https://blog.csdn.net/weixin\_37139197/article/details/82908377  
https://mybatis.org/mybatis-3/zh/sqlmap-xml.html\#cache  
https://blog.csdn.net/qq\_39654446/article/details/78133810

[20210705155121973.jpg_pic_center]: /images/20221005/235b2c64448d4af7bac6678c05b2e017.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmcwOTA3_size_16_color_FFFFFF_t_70_pic_center]: /images/20221005/5cf63ec438604daa8f7baa84408532cd.png
[Link 1]: https://blog.csdn.net/weixin_39494923/article/details/91534658
[Link 2]: https://blog.csdn.net/weixin_36380516/article/details/115683456?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-1&spm=1001.2101.3001.4242
[20210716151339649.png]: /images/20221005/c51fc965f2ca41c88e6f00b5cc40cd4b.png
[Link 3]: https://blog.csdn.net/wang0907/article/details/119416764