[JAVA] Spring + MyBatis connection setting method <table join>

This time I tried joining two tables.

In the case of one-to-one

Entity

Player.java


package com.mybatis.test.domain;

import java.util.List;

import lombok.Data;

@Data
public class Player {

	private String id;
    private String name;
    private String age;
    //Define the elements of the child class(1 to 1)
    private Detail detailIds;
}

Detail.java


package com.mybatis.test.domain;

import lombok.Data;

@Data
public class Detail {

	private String detailId;
    private String work;
}

Since the parent-child relationship of the table is connected by detail_id, prepare the variable of the child element in Player.java.

XML

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.mybatis.test.domain.MyBatisMapper">
  	<select id="selectItem" resultMap="playerMap">
  	 SELECT
	  player.id
	  ,player.name
	  ,player.detail_id
	  ,detail.work
  	 FROM
  	  player
  	 JOIN
  	  detail
  	 ON
  	  player.detail_id=detail.detail_id
  	 WHERE
  	  player.id=#{id}
  	</select>

	<!--Specify how to map the SELECT result of the table-->
    <resultMap id="playerMap" type="com.mybatis.test.domain.Player">
	  <id property="id" column="id" />
      <result property="name" column="name" />
      <association property="detailIds" resultMap="detailResult"/>
	</resultMap>

      <resultMap id="detailResult" type="com.mybatis.test.domain.Detail">
        <result property="detailId" column="detail_id" />
        <result property="work" column="work" />
      </resultMap>

</mapper>
Be sure to include the table join condition (detail_id) in SQL.

The search result is defined separately in the resultMap element.

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

Also, since this time we have a 1: 1 relationship, we will use association. Associate with child elements in the resultMap of association.

Interface

MyBatisMapper.java


package com.mybatis.test.domain;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface MyBatisMapper {

	//For JOIN testing
	public Player selectItem(String id);

Service

MyBatisService.java


package com.mybatis.test.domain;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class MyBatisService {

	@Autowired
	MyBatisMapper mapper;
	//JOIN
	@Transactional
	public Player selectItem(String id) {
		return mapper.selectItem(id);
	}

Controller

Controller


@Controller
public class MybatisController {
	@Autowired
	MyBatisService myBatisService;
		Player player = new Player();
		player = myBatisService.selectItem("002");
		model.addAttribute("playerDetail", player);
		return "index";
}

Jsp

	<div th:object="${playerDetail}">
		<div>
			<label><b>name</b></label>
			<p th:text="${playerDetail.name}">
		</div>
		<div>
			<label><b>jobs</b></label>
			<p th:text="${playerDetail.detailIds.work}">
		</div>
	</div>

In the case of one-to-many

Entity

Player.java


package com.mybatis.test.domain;

import java.util.List;

import lombok.Data;

@Data
public class Player {

	private String id;
    private String name;
    private String age;
    private Detail detailIds;
    //Element definition of child class(One-to-many)
    private List<Item> items;
}

Since it is one-to-many, I defined a List type in the field.

Item.java


package com.mybatis.test.domain;

import lombok.Data;

@Data
public class Item {

	private String id;
	private String item;
}

XML

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.mybatis.test.domain.MyBatisMapper">
      <select id="selectChoice" resultMap="itemMap">
      select
       player.id
       ,player.name
       ,item.items
      from
       player
      join
       item
      on
       player.id = item.id
      where
       player.id=#{id}
      </select>

      <!--Specify how to map the SELECT result of the table-->
      <resultMap id="itemMap" type="com.mybatis.test.domain.Player">
       <id property="id" column="id" />
       <result property="name" column="name" />
       <collection property="items" ofType="com.mybatis.test.domain.Item">
       	<result property="id" column="id" />
       	<result property="item" column="items" />
       </collection>
      </resultMap>
</mapper>

For one-to-many, use the collection tag. (Association tag for one-to-one) Set the field (items) of the parent class (Player.java) that bundles the child class (Item.java) in the property of the collection tag. In ofType, specify the child class.

even here,

Be sure to include the table join condition (detail_id) in SQL.

Interface and service are omitted.

Controller

MybatisController.java


@Controller
public class MybatisController {
	@Autowired
	MyBatisService myBatisService;
	@GetMapping()
	public String index(Model model) {
		Player playerChoise = myBatisService.selectChoice("002");
		//Repack to Item type list
		List<Item> itemsList = playerChoise.getItems();
		model.addAttribute("itemsList", itemsList);

Jsp

	<!-- th:Get one element at a time with each-->
	<div th:each="item : ${itemsList}">
	<p th:text="${item.item}">
	</div>