spring-boot mybatis collection 用

list 和 collection匹配

2022-05-19 接手项目以来遇到过许多问题，今天聊一下mybatis collection
collection 作为集合操作的一定要使用List来对应接收数据。以下是使用。

首先配置好mybatis的jar和数据库驱动，web等

 <dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>2.2.2</version>
 </dependency>
<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

接下来编写实体类 Company 和 WebData

@Data
public class Company {
    private String          companyId;
    private String          companyName;
    private List<WebData>   or;  // 这里一定要用List
}

@Data
public class WebData {
    private Integer id;
    private String  assc;
}

实体类关系or 用List去承接数据库返回的数据，一定要用List。set不可以，

编写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" >
<mapper namespace="org.danyuan.application.dao.WebDao">
   
    <resultMap id="baseCompanyMap" type="org.danyuan.application.po.Company">
    
        <id column="company_id" property="companyId"/>
        <result column="company_name" property="companyName"/>

        <collection property="or" ofType="org.danyuan.application.po.WebData">
            <id column="id" property="id"/>
            <result column="assc" property="assc"/>
            
        </collection>
    </resultMap>
   
   
    <select id="findAll" parameterType="java.lang.String" resultMap="baseCompanyMap">
        SELECT
        company_id ,
        company_name,
        id,
        assc
        FROM application.test_collection
        WHERE company_id IN
        <foreach collection="ids" item="idItem" open="(" close=")" separator="," index="idIndex">
            ${idItem}
        </foreach>
    </select>
</mapper>

这里我用一张表测试的，实际上使用collection 的情况绝大的概率是关联了子表的，这里面主要注意使用resultMap 的写法，返回结果会自动进行数据合并company_id相同的数据，id不同的添加到list中下面是我的测试数据

image.png

编写测试Service方法 然后提供一个web接口查看返回值

    public List<Company> test() {
        logger.info("开始测试");
        List<Company> list = webDao.findAll(new String[] { "123456", "444" });
        logger.info("测试 list size= {}", list.size());
        list.forEach(aa -> {
            logger.info("测试 web.size={} aa= {}", aa.getOr().size(), aa.toString());
        });
        
        logger.info("测试结束");
        return list;
    }

接口返回

image.png

控制台打印

2022-05-20 11:11:02.378  INFO 7176 --- [p-nio-80-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2022-05-20 11:11:02.524  INFO 7176 --- [p-nio-80-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1380765898 wrapping com.mysql.cj.jdbc.ConnectionImpl@22eec6e4] will not be managed by Spring
==>  Preparing: SELECT company_id , company_name, id, assc FROM application.test_collection WHERE company_id IN ( 123456 , 444 )
==> Parameters: 
<==    Columns: company_id, company_name, id, assc
<==        Row: 123456, aaa, 1, ssss
<==        Row: 123456, aaa, 2, 4444
<==        Row: 123456, aaa, 3, 456789
<==        Row: 123456, aaa, 4, 444445w
<==        Row: 444, 44, 5, 56
<==        Row: 444, 54, 6, 55
<==      Total: 6
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2cc4c8e4]
2022-05-20 11:11:02.579  INFO 7176 --- [p-nio-80-exec-1] o.d.application.service.CompanyService   : 测试 list size= 2
2022-05-20 11:11:02.580  INFO 7176 --- [p-nio-80-exec-1] o.d.application.service.CompanyService   : 测试 web.size=4 aa= Company(companyId=123456, companyName=aaa, or=[WebData(id=1, assc=ssss), WebData(id=2, assc=4444), WebData(id=3, assc=456789), WebData(id=4, assc=444445w)])
2022-05-20 11:11:02.580  INFO 7176 --- [p-nio-80-exec-1] o.d.application.service.CompanyService   : 测试 web.size=2 aa= Company(companyId=444, companyName=44, or=[WebData(id=5, assc=56), WebData(id=6, assc=55)])
2022-05-20 11:11:02.580  INFO 7176 --- [p-nio-80-exec-1] o.d.application.service.CompanyService   : 测试结束