2.3 複数のテーブルを元にデータを取得しよう

第2章テーブル同士を連携しよう

2.3 複数のテーブルを元にデータを取得しよう

では実際にサンプルコードで動きを確認してみましょう。事前準備として、DBに以下の内容でbookinfoテーブルとorderinfoテーブルを作成してください。

CREATE TABLE bookinfo ( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(100), 
price INTEGER )ENGINE=InnoDB;

INSERT INTO bookinfo VALUES('0001','Java',1001); 
INSERT INTO bookinfo VALUES('0002','PHP',1002); 
INSERT INTO bookinfo VALUES('0003','Linux',1003);

CREATE TABLE orderinfo ( orderno INTEGER AUTO_INCREMENT PRIMARY KEY, 
order_isbn VARCHAR(20), date date, 
FOREIGN KEY (order_isbn) REFERENCES bookinfo(isbn) on update cascade on delete cascade )ENGINE=InnoDB; 

INSERT INTO orderinfo VALUES(null,'0001', CURDATE()); 
INSERT INTO orderinfo VALUES(null,'0002', CURDATE()); 
INSERT INTO orderinfo VALUES(null,'0001', CURDATE());

この2つのテーブル同士の関係性を図で表すと、以下のようになります。

また、ER図で表すとこのようになります。1件の書籍情報は、複数の売上情報に存在するため、orderinfoテーブルからみると、多対1の関係になります。

※ER図について

「テーブル同士の関連性を図式化したもの」です。システムに必要なデータの全体像を把握しやすくすることができます。

では、売上情報画面上に、売上番号、ISBN番号、書籍タイトル、購入日付が表示されるようなプログラムを作成してみましょう。

実行結果

フォルダ構造

DB接続設定

「src/main/resources」フォルダ内の「application.properties」に以下に示すソースコードを記述する

【ファイル名：application.properties】

spring.datasource.driverClassName=org.mariadb.jdbc.Driver
spring.datasource.url=jdbc:mariadb://localhost:3306/mybookdb
spring.datasource.username=root
spring.datasource.password=root123

また、プロジェクトフォルダ直下の「pom.xml」に以下に示すソースコードを記述する

【ファイル名：pom.xml】

<dependency>
  <groupId>org.mariadb.jdbc</groupId>
  <artifactId>mariadb-java-client</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

【ファイル名：Book.java】

package jp.co.f1.spring.entity;
	
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
	
@Entity
@Table(name = "bookinfo")
public class Book {
	// ISBN
	@Id
	@Column(length = 20)
	private String isbn;

	public String getIsbn() {
		return isbn;
	}

	public void setIsbn(String isbn) {
		this.isbn = isbn;
	}

	// タイトル
	@Column(length = 100, nullable = true)
	private String title;

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	// 価格
	@Column(length = 11, nullable = true)
	private String price;

	public String getPrice() {
		return price;
	}

	public void setPrice(String price) {
		this.price = price;
	}
}

【ファイル名：Order.java】

package jp.co.f1.spring.entity;
	
import java.time.LocalDate;

import com.fasterxml.jackson.annotation.JsonFormat;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import lombok.Data;

@Entity
@Table(name = "orderinfo")
public class Order {
	// 注文No
	@Id
	@Column(length = 20, nullable = true)
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int orderno;

	public int getOrderno() {
		return orderno;
	}

	public void setOrderno(int orderno) {
		this.orderno = orderno;
	}

	@Column(name = "order_isbn", length = 20)
	private String isbn;

	public String getIsbn() {
		return isbn;
	}

	public void setIsbn(String isbn) {
		this.isbn = isbn;
	}

	// 購入日付
	@Column(length = 20, nullable = true)
	@JsonFormat(pattern = "yyyy/MM/dd")
	private LocalDate date;

	public LocalDate getDate() {
		return date;
	}

	public void setDate(LocalDate date) {
		this.date = date;
	}

	// Web版のOrderedItemにあたるリレーション
	@ManyToOne(optional = false)
	@JoinColumn(name = "order_isbn", referencedColumnName = "isbn", insertable = false, updatable = false)
	private Book book;

	public Book getBook() {
		return book;
	}

	public void setBook(Book book) {
		this.book = book;
	}
}

【ファイル名：OrderRepository.java】

package jp.co.f1.spring.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import jp.co.f1.spring.entity.Order;

@Repository
public interface OrderRepository extends JpaRepository<Order, Integer> {

}

【ファイル名：BookRepository.java】

package jp.co.f1.spring.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import jp.co.f1.spring.entity.Book;

@Repository
public interface BookRepository extends JpaRepository<Book, String> {

}

【ファイル名：TableController.java】

package jp.co.f1.spring.table;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.servlet.ModelAndView;

import jp.co.f1.spring.entity.Order;
import jp.co.f1.spring.repository.OrderRepository;

@Controller
public class TableController {
	// Repositoryインターフェースを自動インスタンス化
	@Autowired
	private OrderRepository orderinfo;
	/**
	 * 「/showOrderedItem」へアクセスがあった場合
	 */
	@GetMapping("/showOrderedItem")
	public ModelAndView showOrderedItem(ModelAndView mav) {
		// 購入履歴情報の全件を取得（書籍情報を含む）
		Iterable < Order > orderedList = orderinfo.findAll();
		mav.addObject("orderedList", orderedList);
		mav.setViewName("orderList");
		return mav;
	}
}

【ファイル名：orderList.html】

<!DOCTYPE html>
<html>

<head>
  <title>複数のテーブルからデータを取得しよう</title>
</head>

<body>
  <table border="1" cellpadding="10" cellspacing="0">
    <thead>
      <tr>
        <th>注文番号</th>
        <th>ISBN</th>
        <th>タイトル</th>
        <th>時間</th>
      </tr>
    </thead>
    <tbody th:if="${orderedList.size() >= 1}">
      <tr th:each="Order : ${orderedList}" th:object="${Order}">
        <td>
          <a href="#" th:text="*{orderno}"></a>
        </td>
        <td th:text="*{isbn}">
        </td>
        <td th:text="*{book.title}">
        </td>
        <td th:text="*{date}">
        </td>
      </tr>
    </tbody>
  </table>
</body>

</html>

アプリケーションにアクセス

以下のアドレスからアプリケーションにアクセスします。
URL：http://localhost:8080/showOrderedItem

解説

・@ManyToOne

多対1の関係を表す場合、@ManyToOneアノテーションを付けます。先ほどER図で確認したように、orderinfoが多（Many）で、bookinfoが1（One）です。

・optional属性

optional属性を指定すると、結合方法を指定することができます。

optional属性のデフォルト値はtrue、すなわち外部結合です。

内部結合や外部結合については、SQL基礎6章の単元で学習します。

・@JoinColumn(name = “order_isbn”, referencedColumnName = “isbn”)

結合するカラムを指定する場合に、@JoinColumnアノテーションを使用します。
name属性には、結合元テーブルの外部キー列名を指定します。referencedColumnName属性には、結合先のカラム名を指定します。今回の場合はorderinfoテーブルの” order_isbn”に、bookinfoテーブルの”isbn”を結合してね、という指定をしています。

・insertable = false, updatable = false

insertable属性とupdatable属性は、フィールドをinsertとupdateの対象に含めるかどうかを指定します。サンプルコードではorder_isbnをinsert文、update文の対象から除外するための設定をしています。今回のorder_isbnはbookinfoテーブルのisbnフィールドを参照する外部キーです。システムでDB情報の挿入や更新を行う時に、JPAが意図しない操作をしないためにinsertable属性とupdatable属性を『このorder_isbnは読み取り専用のフィールドなので挿入/更新しないでね』という風に、falseで指定してあげる必要があるのです。デフォルトはtrueになっているので、参照のみで扱うカラムは意図しない操作を防ぐためにも、記述を忘れないようにしましょう。

NEXT>> 第3章メールを送信してみよう

第2章 テーブル同士を連携しよう