[JAVA] [MyBatis] I want to map a query query to a table that has a one-to-many relationship to a nested bean


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?


@ToString(exclude = {"tickets"})
public class Project {
    private int projectId;
    private String name;
    private List<Ticket> tickets;


@ToString(exclude = {"comments"})
public class Ticket {
    private int ticketId;
    private String content;
    private List<Comment> comments;


public class Comment {
    private int commentId;
    private String content;


Use the features resultMap and collection. In the above example, create the following xml.


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
<mapper namespace="com.example.demo.mapper.ProjectMapper">
  <select id="findAllProject" resultMap="findAllProjectResultMap">
      project.name as project_name,
      ticket.content as ticket_content,
      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

  <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"/>

In the code below, you can check how the result of executing SQL is mapped to Java Bean by Pretty Print.


public class CommandlineappliApplication implements CommandLineRunner{

    private ProjectMapper projectMapper;

    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)

Environmental information


<?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">
		<relativePath/> <!-- lookup parent from repository -->
	<description>Demo project for Spring Boot</description>






Recommended Posts

[MyBatis] I want to map a query query to a table that has a one-to-many relationship to a nested bean
I want to use swipeback on a screen that uses XLPagerTabStrip
I want to develop a web application!
I want to write a nice build.gradle
I want to write a unit test!
[Android] I want to create a ViewPager that can be used for tutorials
[Ruby] I want to do a method jump!
I want to simply write a repeating string
I want to design a structured exception handling
I want to issue a connection when a database is created using Spring and MyBatis
I want to use PowerMock in a class that combines parameterized tests and ordinary tests
7 things I want you to keep so that it doesn't become a fucking code
A story I was addicted to when getting a key that was automatically tried on MyBatis
I want to call a method of another class
I learned stream (I want to convert List to Map <Integer, List>)
I want to use a little icon in Rails
I want to monitor a specific file with WatchService
I want to define a function in Rails Console
I want to stop snake case in table definition
I want to add a reference type column later
I want to click a GoogleMap pin in RSpec
I want to get along with Map [Java beginner]
I want to connect to Heroku MySQL from a client
I want to create a generic annotation for a type
I want to add a delete function to the comment function
I want to write a loop that references an index with Java 8's Stream API