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

2024-10-23 07:15:12

演示所需要的数据库表及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配置，这两项比较重要，且相对难以把握，要多加练习了解掌握。

码农公寓

相关文章