mybatisの使用とソース分析(9)mybatis xmlの使用の2つの方法で複数の関連クエリーを1対1にする

XMLネスト方式を使用して関連クエリーを実現するには2つの方法があり、1つ目はネストクエリー方式を使用し、この方式は複数回のクエリーの問題をもたらし、効率が低下する可能性があります.もう1つの方法は、関連クエリー、結果ネストの方法であり、効率が理想的です.例は1つの省の表で、1つの都市の表で、都市は1つのフィールドの関連する省があって、IDを通じてある都市を照会して、関連して省の実体を取得します
本プロジェクトの構築ソース:https://github.com/zhuquanwen/mybatis-learn/releases/tag/for-xml-association 構築プロセス:https://blog.csdn.net/u011943534/article/details/104911104文章の基礎の上で構築して、一部の過程は詳しく説明しない.
一、ネストされた方法をクエリーする1、本試験で使用したsqlスクリプトをインポートする

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50722
Source Host           : localhost:3306
Source Database       : mybatis_learn

Target Server Type    : MYSQL
Target Server Version : 50722
File Encoding         : 65001

Date: 2020-09-04 21:22:20
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for province
-- ----------------------------
DROP TABLE IF EXISTS `province`;
CREATE TABLE `province` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of province
-- ----------------------------
INSERT INTO `province` VALUES ('1', '   ');
INSERT INTO `province` VALUES ('2', '  ');

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50722
Source Host           : localhost:3306
Source Database       : mybatis_learn

Target Server Type    : MYSQL
Target Server Version : 50722
File Encoding         : 65001

Date: 2020-09-04 21:23:33
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for city
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of city
-- ----------------------------
INSERT INTO `city` VALUES ('1', '   ', '1');
INSERT INTO `city` VALUES ('2', '  ', '1');
INSERT INTO `city` VALUES ('3', '   ', '1');
INSERT INTO `city` VALUES ('4', '    ', '1');
INSERT INTO `city` VALUES ('5', '  ', '1');
INSERT INTO `city` VALUES ('6', '  ', '1');
INSERT INTO `city` VALUES ('7', '  ', '2');
INSERT INTO `city` VALUES ('8', '  ', '2');
INSERT INTO `city` VALUES ('9', '  ', '2');

2 generatorプラグインを構成し、com.learn.zqw.generator.Generatorのメイン関数を実行してbirdテーブルからエンティティとmapperを自動的に生成します.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>

    <!-- context     -->
    <context id="MyGererator" targetRuntime="MyBatis3">

        <!--                  ，         -->
        <commentGenerator>
            <!--      -->
            <property name="suppressAllComments" value="true"/>
            <!--       -->
            <property name="suppressDate" value="true"/>
        </commentGenerator>

        <!--         -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mybatis_learn?useUnicode=true&characterEncoding=utf8"
                        userId="root"
                        password="root">
        </jdbcConnection>

        <!-- JAVA JDBC      ,         -->
        <javaTypeResolver >
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>

        <!--  javaModelGenerator javaBean  
        targetPackage          
        targetProject        -->
        <javaModelGenerator targetPackage="com.learn.zqw.association.domain" targetProject="src/main/java">
            <!-- enableSubPackages                   scheme   -->
            <property name="enableSubPackages" value="false" />
            <!--  Set     .trim -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!--     mapper.xml   -->
        <sqlMapGenerator targetPackage="com.learn.zqw.association.mapper"  targetProject="src/main/java">
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>

        <!--        ， mapper.xml        -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.learn.zqw.association.mapper"  targetProject="src/main/java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!--                  -->
        <!--<table tableName="student"/>-->
        <!--<table tableName="bird"/>-->
        <table tableName="province"/>
        <table tableName="city"/>

        <!--                ，    https://www.jianshu.com/p/e09d2370b796，    -->
        <!-- <table schema="DB2ADMIN" tableName="ALLTYPES" domainObjectName="Customer" >
          <property name="useActualColumnNames" value="true"/>
          <generatedKey column="ID" sqlStatement="DB2" identity="true" />
          <columnOverride column="DATE_FIELD" property="startDate" />
          <ignoreColumn column="FRED" />
          <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />
        </table> -->
    </context>
</generatorConfiguration>

3 Citpyエンティティの変更、Provinceの追加

package com.learn.zqw.association.domain;

import lombok.Data;

@Data
public class City {
     
    private Integer id;

    private String name;

    private Integer pid;

    private Province province;

}

4自動生成されたCityMapperに関連付けられたResultMapを追加し、associationラベル、com.learn.zqw.association.mapper.ProvinceMapper.selectByPrimaryKeyを使用してProvinceMapperの関数

  <resultMap id="ResultMapWithProvince" type="com.learn.zqw.association.domain.City">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="pid" jdbcType="INTEGER" property="pid" />
    <association property="province" column="pid" select="com.learn.zqw.association.mapper.ProvinceMapper.selectByPrimaryKey"/>
  </resultMap>

5自動生成CityMapperにクエリーselectを追加し、上で定義したResultMapWithProvinceを参照します.

  <select id="selectWithProvinceById" parameterType="java.lang.Integer" resultMap="ResultMapWithProvince">
    select id,name,pid from city where id = #{
     id, jdbcType=INTEGER}
  </select>

6 generatorConfig.xmlに2つのMapperを追加

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>

    <!-- context     -->
    <context id="MyGererator" targetRuntime="MyBatis3">

        <!--                  ，         -->
        <commentGenerator>
            <!--      -->
            <property name="suppressAllComments" value="true"/>
            <!--       -->
            <property name="suppressDate" value="true"/>
        </commentGenerator>

        <!--         -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mybatis_learn?useUnicode=true&characterEncoding=utf8"
                        userId="root"
                        password="root">
        </jdbcConnection>

        <!-- JAVA JDBC      ,         -->
        <javaTypeResolver >
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>

        <!--  javaModelGenerator javaBean  
        targetPackage          
        targetProject        -->
        <javaModelGenerator targetPackage="com.learn.zqw.association.domain" targetProject="src/main/java">
            <!-- enableSubPackages                   scheme   -->
            <property name="enableSubPackages" value="false" />
            <!--  Set     .trim -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!--     mapper.xml   -->
        <sqlMapGenerator targetPackage="com.learn.zqw.association.mapper"  targetProject="src/main/java">
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>

        <!--        ， mapper.xml        -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.learn.zqw.association.mapper"  targetProject="src/main/java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!--                  -->
        <!--<table tableName="student"/>-->
        <!--<table tableName="bird"/>-->
        <table tableName="province"/>
        <table tableName="city"/>

        <!--                ，    https://www.jianshu.com/p/e09d2370b796，    -->
        <!-- <table schema="DB2ADMIN" tableName="ALLTYPES" domainObjectName="Customer" >
          <property name="useActualColumnNames" value="true"/>
          <generatedKey column="ID" sqlStatement="DB2" identity="true" />
          <columnOverride column="DATE_FIELD" property="startDate" />
          <ignoreColumn column="FRED" />
          <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />
        </table> -->
    </context>
</generatorConfiguration>

7テスト

package com.learn.zqw.association;

import com.learn.zqw.association.domain.City;
import com.learn.zqw.association.mapper.CityMapper;
import lombok.Cleanup;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;

import java.io.IOException;
import java.io.InputStream;

/**
 * //TODO
 *
 * @author zhuquanwen
 * @vesion 1.0
 * @date 2020/9/4 20:58
 * @since jdk1.8
 */
@RunWith(JUnit4.class)
public class AssociationTests {
     

    @Test
    public void test() throws IOException {
     
        @Cleanup InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sessionFactory = sqlSessionFactoryBuilder.build(is);
        SqlSession session = sessionFactory.openSession();
        CityMapper mapper = session.getMapper(CityMapper.class);
        City city = mapper.selectWithProvinceById(1);
        System.out.println(city);
    }
}

テスト結果:

[2020/09/04 21:18:16,697] [DEBUG] [org.apache.ibatis.transaction.jdbc.JdbcTransaction:100] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@475e586c]
[2020/09/04 21:18:16,704] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectWithProvinceById:143] - ==>  Preparing: select id,name,pid from city where id = ? 
[2020/09/04 21:18:16,738] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectWithProvinceById:143] - ==> Parameters: 1(Integer)
[2020/09/04 21:18:16,762] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectByPrimaryKey:143] - ====>  Preparing: select id, name from province where id = ? 
[2020/09/04 21:18:16,762] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectByPrimaryKey:143] - ====> Parameters: 1(Integer)
[2020/09/04 21:18:16,769] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectByPrimaryKey:143] - <====      Total: 1
[2020/09/04 21:18:16,770] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectWithProvinceById:143] - <==      Total: 1
[2020/09/04 21:18:16,770] [DEBUG] [com.learn.zqw.plugin.TestPlugin:38] -   ：413ms
City(id=1, name=   , pid=1, province=Province(id=1, name=   ))

Process finished with exit code 0

二、クエリ結果のネスト方式
1 CityMapper.xml種にResultSet Mapを追加

  <resultMap id="ResultMapWithProvince2" type="com.learn.zqw.association.domain.City">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <association property="province" javaType="com.learn.zqw.association.domain.Province">
      <id column="id" javaType="INTEGER" property="id"/>
      <result column="name" jdbcType="VARCHAR" property="name" />
    </association>
  </resultMap>

2 CityMapper.xml種にselectを追加

  <select id="selectWithProvinceById2" parameterType="java.lang.Integer" resultMap="ResultMapWithProvince2">
    select c.id,c.name,c.pid,p.id, p.name from city c, province p where c.pid = p.id and c.id = #{
     id, jdbcType=INTEGER}
  </select>

3 CityMapper.xml種にインタフェースを追加

 City selectWithProvinceById2(Integer id);

4テスト

    @Test
    public void test2() throws IOException {
     
        @Cleanup InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sessionFactory = sqlSessionFactoryBuilder.build(is);
        SqlSession session = sessionFactory.openSession();
        CityMapper mapper = session.getMapper(CityMapper.class);
        City city = mapper.selectWithProvinceById2(1);
        System.out.println(city);
    }

テスト結果:

[2020/09/05 21:03:41,388] [DEBUG] [org.apache.ibatis.transaction.jdbc.JdbcTransaction:100] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@475e586c]
[2020/09/05 21:03:41,400] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectWithProvinceById2:143] - ==>  Preparing: select c.id,c.name,c.pid,p.id, p.name from city c, province p where c.pid = p.id and c.id = ? 
[2020/09/05 21:03:41,453] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectWithProvinceById2:143] - ==> Parameters: 1(Integer)
[2020/09/05 21:03:41,496] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectWithProvinceById2:143] - <==      Total: 1
[2020/09/05 21:03:41,497] [DEBUG] [com.learn.zqw.plugin.TestPlugin:38] -   ：668ms
City(id=1, name=   , pid=null, province=Province(id=1, name=   ))

Process finished with exit code 0

3、2つの方法で2つのテスト結果の出力を比較すると、1つ目の方法は2つのSQLを出力し、2つ目の方法は1つだけ出力します.