#30 Spring Mybatisを利用したデータベース操作[5. テーブル結合 collection パターン1]

今回はMybatisを用いてテーブル結合を行います。
前回との違いとして、前回は<association>タグを用いてテーブルの結合を行いましたが、今回は<collection>タグを用いてテーブルの結合を行います。両者の違いに関しては後でまとめます。

前提条件

この記事はSpringの最低限の知識が必要になります。
また、なるべく分かりやすく書くつもりですが、この記事の目的は自分の勉強のアウトプットであるため所々説明は省略します。

前回まで

構築環境

各バージョン
Spring Boot ver 2.7.5
mybatis-spring-boot-starter ver 2.2.2
Model Mapper ver 3.1.0
jquery ver 3.6.1
bootstrap ver 5.2.2
webjars-locator ver 0.46
thymeleaf-layout-dialect ver 3.0.0
依存関係

使用するテーブル

今回は以下のテーブルを作成しました。

ユーザーテーブル(M_USER)
商品テーブル(M_PRODUCT)
店舗テーブル(M_DEPARTMENT)
購入履歴テーブル(T_PURCHASE)
商品在庫テーブル(T_STOCK)

1. ユーザーテーブル(M_USER)

BIRTHDAY1というカラムがありますが、今回は使用しません。
また、自己学習のためにNULLを所々許容しています。

2. 商品テーブル(M_PRODUCT)

3. 店舗テーブル(M_DEPARTMENT)

4. 購入履歴テーブル(T_PURCHASE)

主キーをORDER_ID(注文番号)としています。
このカラムはGENERATED ALWAYS AS IDENTITYを用いて一意な連番を生成しています。

参考文献(OracleDBでの連番の生成方法)

5. 商品在庫テーブル(T_STOCK)

E-R図

E-R図は以下になります。
商品テーブル(M_PRODUCT)、商品在庫テーブル(T_STOCK)は今回は結合に使用しません。
今回はユーザーテーブル(M_USER)と購入履歴テーブル(T_PURCHASE)を結合します。

今回行うこと

エンティティクラス(Department.java)の作成
　1. MUser.java
　2. MProduct.java
　3. MDepartment.java
　4. TPurchase.java
　5. TStock.java
マッピング定義の追加
　1. M_USER
　2. M_PRODUCT
　3. M_DEPARTMENT
　4. T_PURCHASE
　5. T_STOCK
SQLの作成
処理の作成
　1. UserService.java
　2. UserServiceImpl.java
Formクラスの作成
コントローラー(UserDetailController.java)の作成
画面(detail.html)の作成

1. エンティティクラス(Department.java)の作成

1. MUser.java

このクラスにはユーザー情報が入ります。

MUser.java

package com.example.model;

import java.util.Date;
import java.util.List;

import lombok.Data;

@Data
public class MUser {

	private String userId; 
	private String phoneNumber; 
	private Integer postalNumber1;
	private Integer postalNumber2;
	private String address;
	private String userName;
	private String password;
	private Date birthday2;
	private Integer age;
	private byte[] accountIcon;
	private Integer gender;
	private MDepartment mdepartment;
	private List<TPurchase> purchaseList; 
}

店舗テーブル(M_DEPARTMENT)からは必ず1つのデータしか返ってこないので以下のように定義します。

private MDepartment mdepartment;

購入履歴テーブル(T_PURCHASE)からは複数のデータが返ってくる可能性があるため以下のようにList型で定義します。

private List<TPurchase> purchaseList;

2. MProduct.java

このクラスには商品情報が入ります。

MProduct.java

package com.example.model;

import java.util.List;

import lombok.Data;

@Data
public class MProduct {

	private Integer productId;
	private String productName;
	private Integer productPrice;
	private List<TPurchase> purchaseList;
}

購入履歴テーブル(T_PURCHASE)からは複数のデータが返ってくる可能性があるため以下のようにList型で定義します。

private List<TPurchase> purchaseList;

3. MDepartment.java

ここでは店舗情報が入ります。このクラスは今回はあまり関係ないです。

MDepartment.java

package com.example.model;

import lombok.Data;

@Data
public class MDepartment {

	private Integer departmentId;
	private String departmentName;
}

4. TPurchase.java

ここではユーザーが商品を購入した履歴が入ります。

TPurchase.java

package com.example.model;

import java.util.Date;

import lombok.Data;

@Data
public class TPurchase {

	private Integer orderId;
	private String userId;
	private Integer productId;
	private Integer count;
	private Date purchaseDate;
	private MProduct product; // 定義してありますが今回は使用しません。
	private MUser user;
}

商品テーブル(M_PRODUCT)からは必ず1つのデータしか返ってこないので以下のように定義します。

private MProduct product;

購入履歴テーブル(T_PURCHASE)からは複数のデータが返ってくる可能性があるため以下のようにList型で定義します。

private List<TPurchase> purchaseList;

5. TStock.java

ここでは商品の在庫数が入ります。今回はあまり関係ないです。

TStock.java

package com.example.model;

import lombok.Data;

@Data
public class TStock {

	private Integer productId;
	private Integer productStock;
}

2. マッピング定義の追加

UserMapper.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とxmlのマッピング -->
<mapper namespace="com.example.repository.UserMapper">

<!-- マッピング定義(ユーザー) -->
<resultMap type="com.example.model.MUser" id="muser">
	<id column="user_id" property="userId"/>
	<result column="phone_number" property="phoneNumber"/>
	<result column="postal_number1" property="postalNumber1"/>
	<result column="postal_number2" property="postalNumber2"/>
	<result column="address" property="address"/>
	<result column="user_name" property="userName"/>
	<result column="birthday1" property="birthday1"/>
	<result column="birthday2" property="birthday2"/>
	<result column="age" property="age"/>
	<result column="account_icon" property="accountIcon"/>
	<association property="mdepartment" resultMap="department" />
	<collection property="purchaseList" resultMap="purchase" columnPrefix="purchase_" />
</resultMap>

<!-- マッピング定義(店舗) -->
<resultMap type="com.example.model.MDepartment" id="department">
	<id column="department_id" property="departmentId" />
	<result column="department_name" property="departmentName" />
</resultMap>

<!-- マッピング定義(商品) -->
<resultMap type="com.example.model.MProduct" id="product">
	<id column="product_id" property="productId" />
	<result column="product_name" property="productName" />
	<result column="product_price" property="productPrice" />
	<collection property="purchaseList" resultMap="purchase" columnPrefix="product_" />
</resultMap>

<!-- マッピング定義(購入履歴) -->
<resultMap type="com.example.model.TPurchase" id="purchase">
	<id column="order_id" property="orderId"/>
	<result column="user_id" property="userId" />
	<result column="product_id" property="productId" />
	<result column="count" property="count" />
	<result column="purchase_date" property="purchaseDate" />
	<association property="user" resultMap="muser" />
	<association property="product" resultMap="product" />
</resultMap>

<!-- マッピング定義(商品在庫) -->
<resultMap type="com.example.model.TStock" id="stock">
	<id column="product_id" property="productId" />
	<result column="product_stock" property="productStock" />
</resultMap>

	<!-- ユーザー情報1件取得 -->
	<select id="findOneMUser" resultMap="muser">
		SELECT  
			M_USER.USER_ID,
			M_USER.PHONE_NUMBER,
			M_USER.POSTAL_NUMBER1,
			M_USER.POSTAL_NUMBER2,
			M_USER.ADDRESS,
			M_USER.USER_NAME,
			M_USER.PASSWORD,
			M_USER.BIRTHDAY2,
			M_USER.AGE,
			M_USER.GENDER,
			M_DEPARTMENT.DEPARTMENT_NAME,
			T_PURCHASE.PRODUCT_ID AS PURCHASE_PRODUCT_ID,
			T_PURCHASE.COUNT AS PURCHASE_COUNT,
			T_PURCHASE.PURCHASE_DATE AS PURCHASE_PURCHASE_DATE
		FROM M_USER
			LEFT OUTER JOIN M_DEPARTMENT
				ON M_USER.DEPARTMENT_ID = M_DEPARTMENT.DEPARTMENT_ID
			LEFT OUTER JOIN T_PURCHASE
				ON M_USER.USER_ID = T_PURCHASE.USER_ID
		WHERE M_USER.USER_ID = #{userId}
	</select> 
</mapper>

複数のテーブルを結合する場合、SELECT結果のカラム名が重複することがあります。カラム名の重複を避けるためにSELECT結果に別名を付けることがあります。ただし、別名を付けるとマッピング定義のカラム名と一致しなくなってしまいます。その問題を解決するために使用するのがcolumnPrefix属性です。

<!-- マッピング定義(ユーザー)の一部抜粋 -->
<collection property="purchaseList" resultMap="purchase" columnPrefix="purchase_" />

<!-- ユーザー情報1件取得の一部抜粋 -->
<select id="findOneMUser" resultMap="muser">
	SELECT  
        <!-- 省略 -->
		T_PURCHASE.PRODUCT_ID AS PURCHASE_PRODUCT_ID,
		T_PURCHASE.COUNT AS PURCHASE_COUNT,
		T_PURCHASE.PURCHASE_DATE AS PURCHASE_PURCHASE_DATE
        <!-- 省略 -->
</select>

ここで<association>タグと<collection>タグの違いをまとめます。

<association>タグ：1つのデータが返って来る場合、基本的にトランザクションテーブルからマスタテーブルを結合する際に使用する
<collection>タグ：複数のデータが返ってくる場合、基本的にマスタテーブルからトランザクションテーブルを結合する際に使用する

	<association property="mdepartment" resultMap="department" />
	<collection property="purchaseList" resultMap="purchase" columnPrefix="purchase_" />

1. M_USER

<!-- マッピング定義(ユーザー) -->
<resultMap type="com.example.model.MUser" id="muser">
	<id column="user_id" property="userId"/>
	<result column="phone_number" property="phoneNumber"/>
	<result column="postal_number1" property="postalNumber1"/>
	<result column="postal_number2" property="postalNumber2"/>
	<result column="address" property="address"/>
	<result column="user_name" property="userName"/>
	<result column="birthday1" property="birthday1"/>
	<result column="birthday2" property="birthday2"/>
	<result column="age" property="age"/>
	<result column="account_icon" property="accountIcon"/>
	<association property="mdepartment" resultMap="department" />
	<collection property="purchaseList" resultMap="purchase" columnPrefix="purchase_" />
</resultMap>

2. M_PRODUCT

<!-- マッピング定義(商品) -->
<resultMap type="com.example.model.MProduct" id="product">
	<id column="product_id" property="productId" />
	<result column="product_name" property="productName" />
	<result column="product_price" property="productPrice" />
	<collection property="purchaseList" resultMap="purchase" />
</resultMap>

3. M_DEPARTMENT

<!-- マッピング定義(店舗) -->
<resultMap type="com.example.model.MDepartment" id="department">
	<id column="department_id" property="departmentId" />
	<result column="department_name" property="departmentName" />
</resultMap>

4. T_PURCHASE

<!-- マッピング定義(購入履歴) -->
<resultMap type="com.example.model.TPurchase" id="purchase">
	<id column="order_id" property="orderId"/>
	<result column="user_id" property="userId" />
	<result column="product_id" property="productId" />
	<result column="count" property="count" />
	<result column="purchase_date" property="purchaseDate" />
	<association property="user" resultMap="muser" />
	<association property="product" resultMap="product" />
</resultMap>

5. T_STOCK

<!-- マッピング定義(商品在庫) -->
<resultMap type="com.example.model.TStock" id="stock">
	<id column="product_id" property="productId" />
	<result column="product_stock" property="productStock" />
</resultMap>

3. SQLの作成

全体は2. マッピング定義の追加に載せてあるためSQLを記述している部分を抜粋します。
2. マッピング定義の追加で説明したcolumnPrefix属性を使用しています。

    <!-- ユーザー情報1件取得 -->
	<select id="findOneMUser" resultMap="muser">
		SELECT  
			M_USER.USER_ID,
			M_USER.PHONE_NUMBER,
			M_USER.POSTAL_NUMBER1,
			M_USER.POSTAL_NUMBER2,
			M_USER.ADDRESS,
			M_USER.USER_NAME,
			M_USER.PASSWORD,
			M_USER.BIRTHDAY2,
			M_USER.AGE,
			M_USER.GENDER,
			M_DEPARTMENT.DEPARTMENT_NAME,
			T_PURCHASE.PRODUCT_ID AS PURCHASE_PRODUCT_ID,
			T_PURCHASE.COUNT AS PURCHASE_COUNT,
			T_PURCHASE.PURCHASE_DATE AS PURCHASE_PURCHASE_DATE
		FROM M_USER
			LEFT OUTER JOIN M_DEPARTMENT
				ON M_USER.DEPARTMENT_ID = M_DEPARTMENT.DEPARTMENT_ID
			LEFT OUTER JOIN T_PURCHASE
				ON M_USER.USER_ID = T_PURCHASE.USER_ID
		WHERE M_USER.USER_ID = #{userId}
	</select>

4. 処理の作成

1. UserService.java

UserService.java

package com.example.service;

import java.util.List;

import com.example.model.MUser;

public interface UserService {
			
	/* ユーザー取得(1件) */
	public MUser getOneMUser(String UserId);
}

2. UserServiceImpl.java

UserServiceImpl.java

package com.example.service.impl;

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

import com.example.model.MUser;
import com.example.repository.UserMapper;
import com.example.service.UserService;

@Service
public class UserServiceImpl implements UserService {
	
	/* レポジトリー(UserMapper.java)のDIを注入 */
	@Autowired
	private UserMapper mapper;
		
	/* ユーザー情報取得(1件) */
	@Override
	public MUser getOneMUser(String UserId) {
		return mapper.findOneMUser(UserId);
	}
}

5. Formクラスの作成

次にテーブルから受け取った値を画面に表示するためにFormクラス(UserDetailForm.java)を作成します。

UserDetailForm.java

package com.example.form;

import java.util.Date;
import java.util.List;

import org.springframework.web.multipart.MultipartFile;

import com.example.model.MDepartment;
import com.example.model.TPurchase;

import lombok.Data;

@Data
public class UserDetailForm {
	
	private String userId;
	private String phoneNumber;
	private Integer postalNumber1;
	private Integer postalNumber2;
	private String address;
	private String userName;
	private String password;
	private Date birthday1;
	private Date birthday2;
	private Integer age;
 	private MultipartFile acocountIcon;
	private Integer gender;
	private MDepartment mdepartment;
	private List<TPurchase> purchaseList;
}

6. コントローラー(UserDetailController.java)の作成

UserDetailController.java

package com.example.controller;

import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import com.example.form.UserDetailForm;
import com.example.model.MUser;
import com.example.service.UserService;

@Controller
@RequestMapping("/user")
@Slf4j
public class UserDetailController {

	@Autowired
	private UserService userservice;
	
	@Autowired
	private ModelMapper modelMapper;
		
	/* ユーザーの詳細情報を表示 */
	@GetMapping("/detail/{userId:.+}")
	public String getUser(UserDetailForm form, Model model, @PathVariable("userId") String UserId) throws Exception {
		
		// ユーザーを1件取得
		MUser user = userservice.getOneMUser(UserId);

        // MUserをuseDetailFormに変換
		form = modelMapper.map(user, UserDetailForm.class);

        // List<TPurchase>は個別に取得
		form.setPurchaseList(user.getPurchaseList());
		
		// Modelに登録
		model.addAttribute("userDetailForm", form);
		
		// ユーザー詳細情報を表示
		return "user/detail";
	}
}

ユーザー情報を1件取得した後、user内にあるデータをUserDetailFormにコピーします。しかし、List<TPurchase>はコピーして値を取得することができないため、個別に取得する必要があります。

		// ユーザーを1件取得
		MUser user = userservice.getOneMUser(UserId);

        // MUserをuseDetailFormに変換
		form = modelMapper.map(user, UserDetailForm.class);

        // List<TPurchase>は個別に取得
		form.setPurchaseList(user.getPurchaseList());

7. 画面(detail.html)の作成

detail.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout"
layout:decorate="~{layout/layout}">
<head>
<title>ユーザー詳細</title>
<!-- CSS読込 -->
<link rel="stylesheet" th:href="@{/css/user/list.css}">
</head>
<body>
	<div layout:fragment="content">
		<div class="header border-bottom">
			<h1 class="h2">ユーザー詳細</h1>
		</div>
		<form id="user-detail-form" method="post" th:action="@{/user/detail}" class="form-signup" th:object="${userDetailForm}">
        <!-- 省略 -->
			<!-- 購入情報 -->  
			<th:block th:if="*{purchaseList != null and purchaseList.size() > 0}">
				<div class="header border-bottom">
					<h1 class="h2">購入履歴</h1>
				</div>
				<table class="table table-striped table-bordered table-hover">
					<thead>
						<tr>
							<th class="w-25">購入商品</th>
							<th>購入数</th>
							<th>購入日</th>
						</tr>
					</thead>
					<tbody>
						<tr th:each="item : *{purchaseList}">
						<div th:switch="${item.productId}">
							<td th:case= 1 th:text="シャツ"></td>
							<td th:case= 2 th:text="スカート"></td>
							<td th:case= 3 th:text="ジャケット"></td>
							<td th:case= 4 th:text="ズボン"></td>
							<td th:case= 5 th:text="ワンピーズ"></td>
						</div>
							<td th:text="${item.count}"></td>
							<td th:text="${#dates.format(item.purchaseDate, 'YYYY/MM/dd')}"></td>
						</tr>
					</tbody>
				</table>
			</th:block>	
		</form>
	</div>
</body>
</html>

th:if="*{purchaseList != null and purchaseList.size() > 0}"を指定することで条件を満たした場合、<th:block>で囲まれた部分が表示されるようにします。
また、${item.productId}の値によって画面に表示する内容を分けます。

			<!-- 購入情報 -->  
			<th:block th:if="*{purchaseList != null and purchaseList.size() > 0}">
				<div class="header border-bottom">
					<h1 class="h2">購入履歴</h1>
				</div>
				<table class="table table-striped table-bordered table-hover">
					<thead>
						<tr>
							<th class="w-25">購入商品</th>
							<th>購入数</th>
							<th>購入日</th>
						</tr>
					</thead>
					<tbody>
						<tr th:each="item : *{purchaseList}">
						<div th:switch="${item.productId}">
							<td th:case= 1 th:text="シャツ"></td>
							<td th:case= 2 th:text="スカート"></td>
							<td th:case= 3 th:text="ジャケット"></td>
							<td th:case= 4 th:text="ズボン"></td>
							<td th:case= 5 th:text="ワンピーズ"></td>
						</div>
							<td th:text="${item.count}"></td>
							<td th:text="${#dates.format(item.purchaseDate, 'YYYY/MM/dd')}"></td>
						</tr>
					</tbody>
				</table>
			</th:block>

最後に

今回は、collectionタグを用いてテーブルの結合を行いました。
しかし、今回の処理はユーザーテーブル(M_USER)をメインのテーブルとしてSQLを記述したため、ユーザーテーブル(M_USER)と直接的に結合していない商品テーブル(M_PRODUCT)の値は取得することができません(商品名、商品金額を直接データベースから取得することができない)。
よって、次回以降は購入履歴テーブル(T_PURCHASE)をメインのテーブルとして記述します。