Take bug tracking systems like JIRA and Redmine as examples. This system creates a project for each topic. Suppose a ticket is created for each project and users can add comments to the ticket. The ER diagram is as follows.
For the data, suppose it looks like this:
Now consider getting all the project and the ticket comments associated with it. Writing a query is easy, but what if I map the results to a nested Java bean called Project.java
? In other words, a query query against a table that has a one-to-many relationship. What if I want to map to a nested bean?
Project.java
@Data
@ToString(exclude = {"tickets"})
public class Project {
private int projectId;
private String name;
private List<Ticket> tickets;
}
Ticket.java
@Data
@ToString(exclude = {"comments"})
public class Ticket {
private int ticketId;
private String content;
private List<Comment> comments;
}
Comment.java
@Data
public class Comment {
private int commentId;
private String content;
}
Use the features resultMap
and collection
. In the above example, create the following xml.
ProjectMapper.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="com.example.demo.mapper.ProjectMapper">
<select id="findAllProject" resultMap="findAllProjectResultMap">
SELECT
project.project_id,
project.name as project_name,
ticket.ticket_id,
ticket.content as ticket_content,
comment.comment_id,
comment.content as comment_content
FROM project
JOIN ticket ON project.project_id = ticket.project_id
JOIN comment ON ticket.ticket_id = comment.ticket_id
</select>
<resultMap id="findAllProjectResultMap" type="com.example.demo.dto.Project">
<id property="projectId" column="project_id" />
<result property="name" column="project_name"/>
<collection property="tickets" ofType="com.example.demo.dto.Ticket">
<id property="ticketId" column="ticket_id" />
<result property="content" column="ticket_content"/>
<collection property="comments" ofType="com.example.demo.dto.Comment">
<id property="commentId" column="comment_id" />
<result property="content" column="comment_content"/>
</collection>
</collection>
</resultMap>
</mapper>
In the code below, you can check how the result of executing SQL is mapped to Java Bean by Pretty Print.
CommandlineappliApplication.java
@Component
public class CommandlineappliApplication implements CommandLineRunner{
@Autowired
private ProjectMapper projectMapper;
@Override
public void run(String... args) throws Exception {
for (Project project : projectMapper.findAllProject()) {
System.out.printf("%s%n", project);
for (Ticket ticket : project.getTickets()) {
System.out.printf(" └ %s%n", ticket);
for (Comment comment : ticket.getComments()) {
System.out.printf(" └ %s%n", comment);
}
}
}
}
}
The result of the actual operation is as follows. You can see that the SQL execution result is mapped to the nested bean as expected.
Project(projectId=1, name=Project 1)
└ Ticket(ticketId=1, content=Ticket 1-1)
└ Comment(commentId=1, content=Comment 1-1-1)
└ Comment(commentId=2, content=Comment 1-1-2)
└ Ticket(ticketId=2, content=Ticket 1-2)
└ Comment(commentId=3, content=Comment 1-2-1)
└ Comment(commentId=4, content=Comment 1-2-2)
Project(projectId=2, name=Project 2)
└ Ticket(ticketId=3, content=Ticket 2-1)
└ Comment(commentId=5, content=Comment 2-1-1)
└ Comment(commentId=6, content=Comment 2-1-2)
└ Ticket(ticketId=4, content=Ticket 2-2)
└ Comment(commentId=7, content=Comment 2-2-1)
└ Comment(commentId=8, content=Comment 2-2-2)
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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>mybatis-sample</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>mybatis-sample</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.batch</groupId>
<artifactId>spring-batch-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Recommended Posts