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

# #31 Spring Mybatisを利用したデータベース操作[5. テーブル結合 collection パターン2]
前回は``collectionタグ``を用いてテーブルの結合を行いましたが、今回も同様のことを行います。
前回と違う点として、結合するメインのテーブルを変更します。

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

# 前回まで

https://qiita.com/Jackoguro/items/d62cba12b5c4b2c35e49

# 構築環境
1. 各バージョン
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

2. 依存関係
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/58b53ccd-7cf2-2d82-4214-9f0c08fced2e.png)

# 成果物
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/60296e38-d6e4-6a77-bbac-39a34373d114.png)

# 使用するテーブル
今回は以下のテーブルを作成しました。
1. ユーザーテーブル(M_USER)
1. 商品テーブル(M_PRODUCT)
1. 店舗テーブル(M_DEPARTMENT)
1. 購入履歴テーブル(T_PURCHASE)
1. 商品在庫テーブル(T_STOCK)

## 1. ユーザーテーブル(M_USER)
BIRTHDAY1というカラムがありますが、今回は使用しません。
また、自己学習のためにNULLを所々許容しています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/b4b533eb-7509-c8f4-c314-064b1a6d6c32.png)

## 2. 商品テーブル(M_PRODUCT)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/d95abd9e-cde4-c89f-870c-02f140862fcc.png)


## 3. 店舗テーブル(M_DEPARTMENT)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/10d078e2-3d55-c2a3-9215-803366624e64.png)

## 4. 購入履歴テーブル(T_PURCHASE)
主キーをORDER_ID(注文番号)としています。
このカラムは``GENERATED ALWAYS AS IDENTITY``を用いて一意な連番を生成しています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/cdb85c81-69cb-fd99-cf13-c6fa507c3d48.png)

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

https://www.bnote.net/oracle/generated_identity.html

## 5. 商品在庫テーブル(T_STOCK)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/3fafc8ac-83a3-b8a3-c40f-f03458465b41.png)

# E-R図
E-R図は以下になります。
商品テーブル(M_PRODUCT)、商品在庫テーブル(T_STOCK)は今回は結合に使用しません。
今回はユーザーテーブル(M_USER)と購入履歴テーブル(T_PURCHASE)を結合します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/4da36894-5290-6b94-7cfa-55ab9936969a.png)

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

## 1. エンティティクラス(Department.java)の作成
### 1. MUser.java
このクラスにはユーザー情報が入ります。
```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; 
}
```
<br>

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

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

### 2. MProduct.java
このクラスには商品情報が入ります。
```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;
}
```
<br>

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

### 3. MDepartment.java
ここでは店舗情報が入ります。このクラスは今回はあまり関係ないです。
```java:MDepartment.java
package com.example.model;

import lombok.Data;

@Data
public class MDepartment {

	private Integer departmentId;
	private String departmentName;
}
```

### 4. TPurchase.java
ここではユーザーが商品を購入した履歴が入ります。
```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;
}
```
<br>

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

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

### 5. TStock.java
ここでは商品の在庫数が入ります。今回はあまり関係ないです。
```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" />
</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" />
</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
		FROM M_USER
			LEFT OUTER JOIN M_DEPARTMENT
				ON M_USER.DEPARTMENT_ID = M_DEPARTMENT.DEPARTMENT_ID
		WHERE M_USER.USER_ID = #{userId}
	</select> 
	
	<!-- ユーザーの購入履歴を取得 --> 
	<select id="findTPurchase" resultMap="purchase">
		SELECT 
			T_PURCHASE.COUNT,
			T_PURCHASE.PURCHASE_DATE,
			M_PRODUCT.PRODUCT_NAME,
			M_PRODUCT.PRODUCT_PRICE
		FROM T_PURCHASE
			LEFT OUTER JOIN M_PRODUCT
				ON T_PURCHASE.PRODUCT_ID = M_PRODUCT.PRODUCT_ID
			LEFT OUTER JOIN M_USER
				ON T_PURCHASE.USER_ID = M_USER.USER_ID
		WHERE M_USER.USER_ID = #{userId}
		ORDER BY T_PURCHASE.PURCHASE_DATE DESC
	</select>
</mapper>
```
<br>

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

```xml
	<association property="mdepartment" resultMap="department" />
	<collection property="purchaseList" resultMap="purchase" />
```

### 1. M_USER
```xml
<!-- マッピング定義(ユーザー) -->
<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" />
</resultMap>
```

### 2. M_PRODUCT
```xml
<!-- マッピング定義(商品) -->
<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
```xml
<!-- マッピング定義(店舗) -->
<resultMap type="com.example.model.MDepartment" id="department">
	<id column="department_id" property="departmentId" />
	<result column="department_name" property="departmentName" />
</resultMap>
```

### 4. T_PURCHASE
```xml
<!-- マッピング定義(購入履歴) -->
<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
```xml
<!-- マッピング定義(商品在庫) -->
<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``属性を使用しています。

```xml
	<!-- ユーザー情報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
		FROM M_USER
			LEFT OUTER JOIN M_DEPARTMENT
				ON M_USER.DEPARTMENT_ID = M_DEPARTMENT.DEPARTMENT_ID
		WHERE M_USER.USER_ID = #{userId}
	</select> 
	
	<!-- ユーザーの購入履歴を取得 --> 
	<select id="findTPurchase" resultMap="purchase">
		SELECT 
			T_PURCHASE.COUNT,
			T_PURCHASE.PURCHASE_DATE,
			M_PRODUCT.PRODUCT_NAME,
			M_PRODUCT.PRODUCT_PRICE
		FROM T_PURCHASE
			LEFT OUTER JOIN M_PRODUCT
				ON T_PURCHASE.PRODUCT_ID = M_PRODUCT.PRODUCT_ID
			LEFT OUTER JOIN M_USER
				ON T_PURCHASE.USER_ID = M_USER.USER_ID
		WHERE M_USER.USER_ID = #{userId}
		ORDER BY T_PURCHASE.PURCHASE_DATE DESC
	</select>
```

## 4. 処理の作成
### 1. UserService.java
```UserService.java
package com.example.service;

import java.util.List;

import com.example.model.MUser;
import com.example.model.TPurchase;

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

	/* 検索条件に一致するユーザーの購入履歴を取得 */
	public List<TPurchase> getTPurchase(String user);
}
```

### 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.model.TPurchase;
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);
	}

    /* 検索条件に一致するユーザーの購入履歴を取得 */
	@Override
	public List<TPurchase> getTPurchase(String user) {
		return mapper.findTPurchase(user);
	}
}
```

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

```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)の作成
```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.model.TPurchase;
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);

		// ユーザーの購入履歴を取得
		List<TPurchase> purchase = userservice.getTPurchase(UserId);

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

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

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

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

		// ユーザーの購入履歴を取得
		List<TPurchase> purchase = userservice.getTPurchase(UserId);

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

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

## 7. 画面(detail.html)の作成
```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>
							<th>購入日</th>
						</tr>
					</thead>
					<tbody>
						<tr th:each="item : *{purchaseList}">
							<td th:text="${item.product.productName}"></td>
							<td th:text="${item.count}"></td>
							<td th:text="${item.product.productPrice * item.count}"></td>
							<td th:text="${#dates.format(item.purchaseDate, 'YYYY/MM/dd')}"></td>
						</tr>
					</tbody>
				</table>
			</th:block>	
		</form>
	</div>
</body>
</html>
```
<br>

``th:if="*{purchaseList != null and purchaseList.size() > 0}"``を指定することで条件を満たした場合、``<th:block>``で囲まれた部分が表示されるようにします。
また、``${item.productId}``の値によって画面に表示する内容を分けます。
```html
			<!-- 購入情報 -->  
			<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>
							<th>購入日</th>
						</tr>
					</thead>
					<tbody>
						<tr th:each="item : *{purchaseList}">
							<td th:text="${item.product.productName}"></td>
							<td th:text="${item.count}"></td>
							<td th:text="${item.product.productPrice * item.count}"></td>
							<td th:text="${#dates.format(item.purchaseDate, 'YYYY/MM/dd')}"></td>
						</tr>
					</tbody>
				</table>
			</th:block>	
```

# 最後に
データベースから取得した購入履歴を個別のユーザー毎に取得することができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884987/bbcef64f-3c8b-854d-a4f6-6825d1a88916.png)