This time I tried joining two tables.
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>
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>
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,
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>