当前位置： 首页 > article >正文

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

article 2024/10/19 10:08:12

一、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/142846955icon-default.png?t=O83Ahttps://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 mapper
        PUBLIC "-//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);
    }

    @Test
    public void areaToCity1() {

        //多对一的情况：取出区域的同时取得城市数据
        List<Area> areaList = mapper.getArea();
        for (Area area : areaList) {
            System.out.println(area);
        }

        sqlSession.close();
    }

    @Test
    public 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 mapper
        PUBLIC "-//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);
    }

    @Test
    public void getCityIncludeArea1(){
        List<City> cityList = mapper.getCity1();
        for (City city : cityList) {
            System.out.println(city);
        }

        sqlSession.close();
    }

    @Test
    public 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配置，这两项比较重要，且相对难以把握，要多加练习了解掌握。 


http://www.kler.cn/news/355706.html

相关文章：

  • Android中的Room数据库框架
  • 如何使用代理来保护你的电子邮件？
  • 了解一些常用的Javascript对象方法
  • Python网络爬虫入门指南
  • 使用JavaScript开发扑克牌游戏：从零开始的前端之旅
  • Vscode+Pycharm+Vue.js+WEUI+django火锅(7） 傍着Copliot战WEUI Picker
  • React高级Hook
  • 遇到 msvcp120.dll 文件缺失的情况如何处理？全面分析msvcp120.dll
  • kubernets（二）
  • 【OpenGL】创建窗口/绘制图形
  • mailx邮件服务器的配置
  • 使用docker-compose搭建redis7集群-3主3从
  • Docker 教程十（Docker Compose）
  • 011_django基于大数据的足球数据分析与可视化系统2024_096va8x0
  • c++ 中虚函数和纯虚函数
  • Android Camera2在textureView中的预览和拍照
  • 【微信小程序_19_自定义组件（1）】
  • Leetcode 分割等和子集
  • 渗透实战 JS文件怎么利用
  • LabVIEW智能螺杆空压机测试系统