mybatisの使用とソース分析(9)mybatis xmlの使用の2つの方法で複数の関連クエリーを1対1にする
78577 ワード
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スクリプトをインポートする
2 generatorプラグインを構成し、com.learn.zqw.generator.Generatorのメイン関数を実行してbirdテーブルからエンティティとmapperを自動的に生成します.
3 Citpyエンティティの変更、Provinceの追加
4自動生成されたCityMapperに関連付けられたResultMapを追加し、associationラベル、com.learn.zqw.association.mapper.ProvinceMapper.selectByPrimaryKeyを使用してProvinceMapperの関数
5自動生成CityMapperにクエリーselectを追加し、上で定義したResultMapWithProvinceを参照します.
6 generatorConfig.xmlに2つのMapperを追加
7テスト
テスト結果:
二、クエリ結果のネスト方式
1 CityMapper.xml種にResultSet Mapを追加
2 CityMapper.xml種にselectを追加
3 CityMapper.xml種にインタフェースを追加
4テスト
テスト結果:
3、2つの方法で2つのテスト結果の出力を比較すると、1つ目の方法は2つのSQLを出力し、2つ目の方法は1つだけ出力します.
本プロジェクトの構築ソース: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つだけ出力します.