调用对象列表的属性列表

Question

假设我有以下dto类：

class Item {
    int id;
    List<Detail> details;

    @Override
    public String toString() {
        return "{id: " + id + ", details: " + details + "}";
    }
}

class Detail {
    String name;
    String value;

    @Override
    public String toString() {
        return "{" + name + ": " + value + "}";
    }
}

是否可以编写映射器xml来检索具有适当填充详细信息的项列表，并且所有数据都将通过两个查询(第一个查询项，第二个查询详细信息)检索。在下面的示例中，将出现N+1查询(项目数N)。

完整的示例(用于示例模式、测试数据和用法)

Sandbox.java：

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;

import java.sql.*;
import java.sql.Statement;
import java.util.List;

public class Sandbox {
    public static void main(String... args) throws Throwable {
        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:sample.db")) {
            try (Statement statement = connection.createStatement()) {
                statement.executeUpdate("drop table if exists Item");
                statement.executeUpdate("create table Item (id integer)");


                statement.executeUpdate("insert into Item values(1)");
                statement.executeUpdate("insert into Item values(2)");

                statement.executeUpdate("drop table if exists Detail");
                statement.executeUpdate("create table Detail (id integer, name string, value string)");

                statement.executeUpdate("insert into Detail values(1, 'name', 'foo')");
                statement.executeUpdate("insert into Detail values(1, 'purpose', 'test')");
                statement.executeUpdate("insert into Detail values(2, 'name', 'bar')");
            }
        }

        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));

        try (SqlSession session = sqlSessionFactory.openSession()) {
            MyMapper mapper = session.getMapper(MyMapper.class);
            List<Item> items = mapper.selectItems();

            System.out.println("items = " + items);
        }
    }
}

MyMapper.java：

import java.util.List;

public interface MyMapper {
    List<Item> selectItems();
}

Mapper.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="MyMapper">
    <resultMap id="ItemMap" type="Item">
        <id column="id" property="id"/>
        <collection column="id" property="details" select="selectDetails"/>
    </resultMap>

    <select id="selectItems" resultMap="ItemMap">
        select * from Item
    </select>

    <select id="selectDetails" parameterType="int" resultType="Detail">
        select * from Detail WHERE id=#{id}
    </select>
</mapper>

Mybati-config.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="net.sf.log4jdbc.DriverSpy"/>
                <property name="url" value="jdbc:log4jdbc:sqlite:sample.db"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="Mapper.xml"/>
    </mappers>
</configuration>

pom.xml：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>ru.urururu</groupId>
    <artifactId>mybatis-batching</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.0</version>
        </dependency>

        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.15.1</version>
        </dependency>

        <dependency>
            <groupId>com.googlecode.log4jdbc</groupId>
            <artifactId>log4jdbc</artifactId>
            <version>1.2</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.21</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.6.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Answer 1

如果您查看参考文档中的多个关联ResultSets部分Mappers XML，将解释如下：

从3.2.3版本开始，MyBatis提供了另一种解决N+1问题的方法。 有些数据库允许存储过程一次返回多个结果集或执行多个语句，并且每个语句返回一个结果集。这可以用于只命中数据库一次，并返回相关数据而不使用联接。

这就是一个例子。因此，您需要一个具有查询的存储过程：

select * from Item
select * from Detail WHERE id=#{id}

然后，select将调用存储过程如下：

<select id="selectItems" resultSets="item,details" resultMap="ItemMap">
  {call getItemAndDetails(#{id,jdbcType=INTEGER,mode=IN})}
</select>

最后，结果图：

指定"details“集合将填写包含在名为"details”的结果集中的数据。

结果地图中的集合标记如下所示：

<collection property="details" ofType="Detail" resultSet="details" column="id" foreignColumn="foreign_id">