Home>
Thing I want to do

I want to realize table join with Spring + MyBatis.

problem

The SQL SELECT result should be returned as null.

Source
DROP TABLE IF EXISTS detail CASCADE;
DROP TABLE IF EXISTS player CASCADE;

CREATE TABLE player
(
  id character NOT NULL,
  name character varying (256),
  age character varying (256),
  detail_id character varying (256),
  CONSTRAINT pkey PRIMARY KEY (id)
);

CREATE TABLE detail
(
  detail_id character NOT NULL,
  work character varying (256),
  CONSTRAINT ppkey PRIMARY KEY (detail_id)
);
<select resultMap = "PlayerDetailMap">
       SELECT
      p.id
      , p.name
      , d.work
       FROM FROM
        player p
       INNER JOIN
        detail d
       ON
        p.detail_id = d.detail_id
       WHERE
        p.id = # {id}
    </select>
  <!-Specify how to map the SELECT result of the table->
  <resultMap type = "com.mybatis.test.domain.PlayerEntity">
   <id property = "id" column = "id" />
   <result property = "name" column = "name" />
   <collection property = "detailEntity" ofType = "com.mybatis.test.domain.DetailEntity">
    <result property = "work" column = "work" />
   </collection>
  </resultMap>

The two underlying entity classes to combine are:

@Entity
public class PlayerEntity {
    @Id
    private String id;
    private String name;
    private String age;
    private String detail_id;
    @Transient
    private List<DetailEntity>detailEntity;
@Entity
public class DetailEntity {
    @Id
    private String detail_id;
    private String work;
@GetMapping ()
    public String index (Model model) {
        PlayerDetailEntity playerDetailEntity = new PlayerDetailEntity ();
        playerDetailEntity = myBatisService.selectItem ("001");
        model.addAttribute ("playerDetailEntity", playerDetailEntity);
        return "index";

        
          <label><b>name</b></label>
          <p th: text = "* {name}">
        
        
          <label><b>work</b></label>
          <p th: text = "* {detailEntity.work}">
        
  

The description in MyBatisMapper.xml seems to be a problem,
I don't know where it is.
Please teach me.

  • Answer # 1

    In the case of a 1: 1 parent-child relationship

    Since the parent-child relationship of the table is connected by detail_id, if the parent Entity is set to the following and the child of this is set to Detail, there are one or more child elements as shown below, so declare it as ListdetailIds. I will.

    import lombok.Data;
    @Data
    public class Player {
        private String id;
        private String name;
        private String age;
        private Detail detail;
    }
    import lombok.Data;
    @Data
    public class Detail {
        private String detailId;
        private String work;
    }

    The SQL (SQL mapping) to look for this looks like this:

    <? 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.github.apz.mapper.PlayerMapper">
      <select id = "findById" resultMap = "playerMap">
            SELECT
                player.id
                , player.name
                , player.age
                , player.detail_id
                , detail.work
            FROM FROM
                player
            JOIN DETAIL
                ON player.detail_id = detail.detail_id
            WHERE
                player.id = # {id}
      </select>
      <resultMap type = "com.github.apz.entity.Player" id = "playerMap">
          <id property = "id" column = "id" />
          <result property = "name" column = "name" />
          <result property = "age" column = "age" />
            
      </resultMap>
      <resultMap id = "detailResult" type = "com.github.apz.entity.Detail">
          <result property = "detailId" column = "detail_id" />
          <result property = "work" column = "work" />
      </resultMap>
    </mapper>

    It ’s a point,

    Be sure to include the condition to join the tables (detail_id in this case) in SQL.
    The search result is defined separately in the resultMap element.

    In, the result is mapped to the content (player this time) that you actually want to get in the search result. property is a property (variable) on the Java class side column is a SQL column.

    It is a description method of detail_id which is a condition of table join, but it describes the state where detail of the parent element player declares that it has a child element in the Player class.

    In property, the field name detail of the parent class that bundles the child classes, the content is the class declared in type, and the relationship between these two mappings is related by id = "detailResult" in resultMap.

    Please refer to https://qiita.com/alpha_pz/items/9c88acd814ffad2f1a36 for mapping of search results that have a 1: N relation.