Mybatis多对一查询的配置及两种方法的使用示例对比以及Mybatis一对多查询两种方法使用示例及对比

一、Mybatis多对一查询的配置及两种方法的使用示例对比

为了试验Mybatis多对一的查询，我们先在数据库中建两个表，一个城市表，一个市区表，一个城市有多个区是一个一对多的关系；多个区对应一个城市是一个多对一的关系。建表SQL及数据如下：

DROP TABLE IF EXISTS `citys`;
CREATE TABLE `citys` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `citys` VALUES ('1', '北京');
INSERT INTO `citys` VALUES ('2', '上海');DROP TABLE IF EXISTS `areas`;
CREATE TABLE `areas` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`cityid` int(20) unsigned NOT NULL,`area` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
INSERT INTO `areas` VALUES ('1', '1', '海淀区');
INSERT INTO `areas` VALUES ('35', '1', '东城区');
INSERT INTO `areas` VALUES ('18', '1', '西城区');
INSERT INTO `areas` VALUES ('37', '1', '朝阳区');
INSERT INTO `areas` VALUES ('39', '2', '黄浦区');
INSERT INTO `areas` VALUES ('40', '2', '闵行区');
INSERT INTO `areas` VALUES ('41', '2', '徐汇区');

接下来我们开始新建项目及module，相关的项目依赖及配置可参见文章：https://linge.blog.csdn.net/article/details/142846955https://linge.blog.csdn.net/article/details/142846955 我们建立两个pojo类及两个mapper类，此时的Area pojo类中的city不再是一个字符串，而是关联city的pojo类。代码如下：

//city pojo类
package com.kermit.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@NoArgsConstructor
@AllArgsConstructor
public class City {private int id;private String cityname;
}//area pojo类
package com.kermit.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@NoArgsConstructor
@AllArgsConstructor
public class Area {private int id;private City city;private String areaname;
}//AreaMapper类
package com.kermit.dao;import com.kermit.pojo.Area;
import java.util.List;public interface AreaMapper {//取得市区列表public List<Area> getArea();}

接下来我们要来编辑mybatis的mapper.xml配置文件，我们这里只是试验多对一，只需要填写AreaMapper.xml文件，这里我们使用了两种方式来实现多对一的查询，一是在association中用子查询去取得城市数据数据，另一种是是使用显示联表SQL查询后通过resultMap对应到city对象中。配置如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--绑定DAO接口-->
<mapper namespace="com.kermit.dao.AreaMapper"><!--使用association用子查询去取得城市数据数据--><select id="getArea" resultMap="AreaCity">select * from areas</select><resultMap id="AreaCity" type="com.kermit.pojo.Area"><association property="city" javaType="com.kermit.pojo.City" select="getCity" column="cityid"/></resultMap><select id="getCity" resultType="com.kermit.pojo.City">select * from citys where id = #{cid};</select><!--使用association:使用显示联表SQL查询后通过resultMap对应到city对象中--><select id="getArea2" resultMap="AreaCity2">select areas.cityid,cityname,areas.id,areas.areaname from areas,citys where areas.cityid = citys.id</select><resultMap id="AreaCity2" type="com.kermit.pojo.Area"><result property="id" column="id" /><result property="areaname" column="areaname" /><association property="city" javaType="com.kermit.pojo.City" ><result property="id" column="cityid" /><result property="cityname" column="cityname" /></association></resultMap></mapper>

接下来我们去编辑测试类TestMybatis代码如下：

import com.kermit.dao.AreaMapper;
import com.kermit.pojo.Area;
import com.kermit.utils.MybatisConn;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.io.IOException;
import java.util.List;public class TestMybatis {private static SqlSession sqlSession;private static AreaMapper mapper;static{try {sqlSession = MybatisConn.getSqlsession();} catch (IOException e) {e.printStackTrace();}mapper = sqlSession.getMapper(AreaMapper.class);}@Testpublic void areaToCity1() {//多对一的情况：取出区域的同时取得城市数据List<Area> areaList = mapper.getArea();for (Area area : areaList) {System.out.println(area);}sqlSession.close();}@Testpublic void areaToCity2(){//多对一的查询，通过sql查询再映射实现List<Area> areaList = mapper.getArea2();for (Area area : areaList) {System.out.println(area);}sqlSession.close();}
}

我们尝试运行两种查询，得到的结果分别如下：

#areaToCity1()结果如下：
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1293241549.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d154ccd]
==>  Preparing: select * from areas 
==> Parameters: 
<==    Columns: id, cityid, areaname
<==        Row: 1, 1, 海淀区
====>  Preparing: select * from citys where id = ?; 
====> Parameters: 1(Long)
<====    Columns: id, cityname
<====        Row: 1, 北京
<====      Total: 1
<==        Row: 35, 1, 东城区
<==        Row: 18, 1, 西城区
<==        Row: 37, 1, 朝阳区
<==        Row: 39, 2, 黄浦区
====>  Preparing: select * from citys where id = ?; 
====> Parameters: 2(Long)
<====    Columns: id, cityname
<====        Row: 2, 上海
<====      Total: 1
<==        Row: 40, 2, 闵行区
<==        Row: 41, 2, 徐汇区
<==      Total: 7
Area(id=1, city=City(id=1, cityname=北京), areaname=海淀区)
Area(id=35, city=City(id=1, cityname=北京), areaname=东城区)
Area(id=18, city=City(id=1, cityname=北京), areaname=西城区)
Area(id=37, city=City(id=1, cityname=北京), areaname=朝阳区)
Area(id=39, city=City(id=2, cityname=上海), areaname=黄浦区)
Area(id=40, city=City(id=2, cityname=上海), areaname=闵行区)
Area(id=41, city=City(id=2, cityname=上海), areaname=徐汇区)
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d154ccd]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d154ccd]
Returned connection 1293241549 to pool.
Process finished with exit code 0#areaToCity2()结果如下：
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1400856767.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@537f60bf]
==>  Preparing: select areas.cityid,cityname,areas.id,areas.areaname from areas,citys where areas.cityid = citys.id 
==> Parameters: 
<==    Columns: cityid, cityname, id, areaname
<==        Row: 1, 北京, 1, 海淀区
<==        Row: 1, 北京, 35, 东城区
<==        Row: 1, 北京, 18, 西城区
<==        Row: 1, 北京, 37, 朝阳区
<==        Row: 2, 上海, 39, 黄浦区
<==        Row: 2, 上海, 40, 闵行区
<==        Row: 2, 上海, 41, 徐汇区
<==      Total: 7
Area(id=1, city=City(id=1, cityname=北京), areaname=海淀区)
Area(id=35, city=City(id=1, cityname=北京), areaname=东城区)
Area(id=18, city=City(id=1, cityname=北京), areaname=西城区)
Area(id=37, city=City(id=1, cityname=北京), areaname=朝阳区)
Area(id=39, city=City(id=2, cityname=上海), areaname=黄浦区)
Area(id=40, city=City(id=2, cityname=上海), areaname=闵行区)
Area(id=41, city=City(id=2, cityname=上海), areaname=徐汇区)
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@537f60bf]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@537f60bf]
Returned connection 1400856767 to pool.
Process finished with exit code 0

可以看到两种方式均能实现我们要的结果，但也有所不同。通过日志记录我们可以发现第一种方式areaToCity1()执行后，实际执行了3次SQL查询。而第二种方式areaToCity2()只进行了一次SQL查询操作，从性能来讲肯定是第二种更优越。

二、Mybatis一对多查询两种方法使用示例及对比

演示所需要的数据库表及mybatis相关配置等见上篇文章及其关联的文章。此时我们已经有了城市与区域的一对多的关系，我们查询城市时，每个城市应该把它对应的多个区域数据取出来，这里我们要把pojo类进行一下修改，我们把Area类的city属性变成一个简单的cityid，而把City类的area变成一个List集合，每个值都是城市下面的一个区，即一对多的数据。代码如下：

//City的pojo类如下
package com.kermit.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@Data
@NoArgsConstructor
@AllArgsConstructor
public class City {private int id;private String cityname;private List<Area> areaList;
}//Area的pojo类如下
package com.kermit.pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data
@NoArgsConstructor
@AllArgsConstructor
public class Area {private int id;private int cityid;private String areaname;
}//CityMapper接口类如下：
package com.kermit.dao;import com.kermit.pojo.City;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;import java.util.List;public interface CityMapper {public List<City> getCity1();public List<City> getCity2();
}

接下来我们来编写CityMapper.xml配置文件，我们仍然是通过两种方式来实现，一个是通过子查询嵌套的方式，一个是通过联表查询后将字段映射到数据中，这里和多对一查询有个大的不一样的关键词即collection和association。在多对一查询时，每个结果只需要关联后面的这个“一”，是使用association；而在一对多查询时，前面的结果需要将后面的多数据放至一个集合中。即collection。当然我为了一次到位，实际我这篇文章和上一遍多对一查询都有多对多的关系。CityMapper.xml配置文件如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--绑定DAO接口-->
<mapper namespace="com.kermit.dao.CityMapper"><!--1通过子查询嵌套--><select id="getCity1" resultMap="cityAndArea1">select * from citys;</select><resultMap id="cityAndArea1" type="com.kermit.pojo.City"><collection property="areaList" column="id" select="getAreas" javaType="ArrayList" ofType="com.kermit.pojo.Area"/></resultMap><select id="getAreas" resultType="com.kermit.pojo.Area">select * from areas where cityid= #{id}</select><!--2通过联表查询后将字段映射到数据中--><select id="getCity2" resultMap="cityAndArea2" >select areas.cityid,cityname,areas.id,areas.areaname from areas,citys where areas.cityid = citys.id;</select><resultMap id="cityAndArea2" type="com.kermit.pojo.City"><result property="id" column="cityid" /><result property="cityname" column="cityname" /><collection property="areaList" ofType="com.kermit.pojo.Area"><result property="id" column="id"/><result property="areaname" column="areaname"/></collection></resultMap></mapper>

最后我们编写测试类，分别调用我们已经定义好的两个方法，测试类代码如下：

import com.kermit.dao.AreaMapper;
import com.kermit.dao.CityMapper;
import com.kermit.pojo.Area;
import com.kermit.pojo.City;
import com.kermit.utils.MybatisConn;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.io.IOException;
import java.util.List;public class TestMybatis {private static SqlSession sqlSession;private static CityMapper mapper;static{try {sqlSession = MybatisConn.getSqlsession();} catch (IOException e) {e.printStackTrace();}mapper = sqlSession.getMapper(CityMapper.class);}@Testpublic void getCityIncludeArea1(){List<City> cityList = mapper.getCity1();for (City city : cityList) {System.out.println(city);}sqlSession.close();}@Testpublic void getCityIncludeArea2(){List<City> cityList = mapper.getCity2();for (City city : cityList) {System.out.println(city);}sqlSession.close();}
}

运行结果如下：

//getCityIncludeArea1()方法运行结果如下
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 252277567.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@f09733f]
==>  Preparing: select * from citys; 
==> Parameters: 
<==    Columns: id, cityname
<==        Row: 1, 北京
====>  Preparing: select * from areas where cityid= ? 
====> Parameters: 1(Long)
<====    Columns: id, cityid, areaname
<====        Row: 1, 1, 海淀区
<====        Row: 35, 1, 东城区
<====        Row: 18, 1, 西城区
<====        Row: 37, 1, 朝阳区
<====      Total: 4
<==        Row: 2, 上海
====>  Preparing: select * from areas where cityid= ? 
====> Parameters: 2(Long)
<====    Columns: id, cityid, areaname
<====        Row: 39, 2, 黄浦区
<====        Row: 40, 2, 闵行区
<====        Row: 41, 2, 徐汇区
<====      Total: 3
<==      Total: 2
City(id=0, cityname=北京, areaList=[Area(id=1, cityid=1, areaname=海淀区), Area(id=35, cityid=1, areaname=东城区), Area(id=18, cityid=1, areaname=西城区), Area(id=37, cityid=1, areaname=朝阳区)])
City(id=0, cityname=上海, areaList=[Area(id=39, cityid=2, areaname=黄浦区), Area(id=40, cityid=2, areaname=闵行区), Area(id=41, cityid=2, areaname=徐汇区)])
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@f09733f]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@f09733f]
Returned connection 252277567 to pool.
Process finished with exit code 0//getCityIncludeArea2()方法运行结果如下：
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 580673921.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@229c6181]
==>  Preparing: select areas.cityid,cityname,areas.id,areas.areaname from areas,citys where areas.cityid = citys.id; 
==> Parameters: 
<==    Columns: cityid, cityname, id, areaname
<==        Row: 1, 北京, 1, 海淀区
<==        Row: 1, 北京, 35, 东城区
<==        Row: 1, 北京, 18, 西城区
<==        Row: 1, 北京, 37, 朝阳区
<==        Row: 2, 上海, 39, 黄浦区
<==        Row: 2, 上海, 40, 闵行区
<==        Row: 2, 上海, 41, 徐汇区
<==      Total: 7
City(id=1, cityname=北京, areaList=[Area(id=1, cityid=0, areaname=海淀区), Area(id=35, cityid=0, areaname=东城区), Area(id=18, cityid=0, areaname=西城区), Area(id=37, cityid=0, areaname=朝阳区)])
City(id=2, cityname=上海, areaList=[Area(id=39, cityid=0, areaname=黄浦区), Area(id=40, cityid=0, areaname=闵行区), Area(id=41, cityid=0, areaname=徐汇区)])
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@229c6181]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@229c6181]
Returned connection 580673921 to pool.
Process finished with exit code 0

同多对一的查询一样，mapper.xml配置中使用子查询嵌套的方法在SQL执行的时候会进行多次查询，最终查询次数和取得的行数成正比增加。而使用联表查询后将数据映射到属性中只执行一次SQL查询，所以一对多、多对一的查询推荐使用联表查询后映射数据的方式处理。还有一个需要注意的地方是xml配置文件中的 javaType和ofType配置，这两项比较重要，且相对难以把握，要多加练习了解掌握。