okutane okutane - 6 days ago 5
Java Question

mybatis - fetching lists of properties for list of objects

Suppose I have following dto classes:

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 + "}";
}
}


Is it possible to write a mapper xml to retrieve list of Items with properly filled Details and all the data will be retrieved with two queries (1st for items, 2nd for details). In the example below there will be N+1 queries (N - number of items).

Complete example (for sample schema, test data and usage)

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>


mybatis-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>

Pau Pau
Answer

If you take a look the section Multiple ResultSets for Association in Mappers XML of the reference documentation it is explained:

Starting from version 3.2.3 MyBatis provides yet another way to solve the N+1 problem.

Some databases allow stored procedures to return more than one resultset or execute more than one statement at once and return a resultset per each one. This can be used to hit the database just once and return related data without using a join.

There is an example with this. So you would need a stored procedure with queries:

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

Then the select will call the stored procedure as next:

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

Finally the resultmap:

specify that the "details" collection will be filled out of data contained in the result set named "details"

Your collection tag in the result map would be something as next:

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