【Javaで作成するWebアプリケーション#3】データベースに登録された値をJSPで使用しブラウザ上に表示させる

$ mysql.server status
 ERROR! MySQL is not running
$ mysql.server start
Starting MySQL
.. SUCCESS! 
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36 Homebrew

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| information_schema     |
| customer_management_db |
| mysql                  |
| performance_schema     |
| sys                    |
+------------------------+
5 rows in set (0.01 sec)

mysql> use customer_management_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

mysql> create table customer_tb (customer_id int(11) not null auto_increment,
    -> admin_id int(11) not null,
    -> name varchar(20) not null,
    -> address varchar(20), 
    -> registered_time datetime DEFAULT CURRENT_TIMESTAMP,
    -> updated_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> primary key (customer_id),
    -> foreign key (admin_id)
    -> references admin_tb(admin_id) 
    -> on delete cascade on update cascade)
    -> ENGINE=InnoDB  DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.17 sec)

mysql>

mysql> desc customer_tb;
+-----------------+-------------+------+-----+-------------------+-----------------------------+
| Field           | Type        | Null | Key | Default           | Extra                       |
+-----------------+-------------+------+-----+-------------------+-----------------------------+
| customer_id     | int(11)     | NO   | PRI | NULL              | auto_increment              |
| admin_id        | int(11)     | NO   | MUL | NULL              |                             |
| name            | varchar(20) | NO   |     | NULL              |                             |
| address         | varchar(20) | YES  |     | NULL              |                             |
| registered_time | datetime    | YES  |     | CURRENT_TIMESTAMP |                             |
| updated_time    | datetime    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.03 sec)

mysql>

mysql> insert into customer_tb(admin_id, name, address) values(1, "松藤春治郎", "北海道札幌市");
Query OK, 1 row affected (0.05 sec)

mysql>

mysql> select * from customer_tb;
+-------------+----------+-----------------+--------------------+---------------------+---------------------+
| customer_id | admin_id | name            | address            | registered_time     | updated_time        |
+-------------+----------+-----------------+--------------------+---------------------+---------------------+
|           1 |        1 | 松藤春治郎      | 北海道札幌市       | 2022-12-09 01:47:03 | 2022-12-09 01:47:03 |
+-------------+----------+-----------------+--------------------+---------------------+---------------------+
1 row in set (0.01 sec)

mysql>

mysql> select * from customer_tb where admin_id = 1;
+-------------+----------+-----------------+--------------------+---------------------+---------------------+
| customer_id | admin_id | name            | address            | registered_time     | updated_time        |
+-------------+----------+-----------------+--------------------+---------------------+---------------------+
|           1 |        1 | 松藤春治郎      | 北海道札幌市       | 2022-12-09 01:47:03 | 2022-12-09 01:47:03 |
+-------------+----------+-----------------+--------------------+---------------------+---------------------+
1 row in set (0.09 sec)

mysql>

package object;

import java.util.Date;

public class Customer {

	private int customer_id;
	private int admin_id;
	private String name;
	private String address;
	private Date registered_time;
	private Date updated_time;


	public int getCustomer_id() {
		return customer_id;
	}
	public void setCustomer_id(int customer_id) {
		this.customer_id = customer_id;
	}
	public int getAdmin_id() {
		return admin_id;
	}
	public void setAdmin_id(int admin_id) {
		this.admin_id = admin_id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Date getRegistered_time() {
		return registered_time;
	}
	public void setRegistered_time(Date registered_time) {
		this.registered_time = registered_time;
	}
	public Date getUpdated_time() {
		return updated_time;
	}
	public void setUpdated_time(Date updated_time) {
		this.updated_time = updated_time;
	}
}

package sql;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import config.DBconfig;
import object.Admin;
import object.Customer;

public class Login {

	public Admin check(String admin_id, String password) throws FileNotFoundException {

		// データベースへの接続情報をプロパティファイルから取得
		DBconfig db_info = new DBconfig();
		String url = db_info.getDBinfo().get("url");
		String user = db_info.getDBinfo().get("user");
		String pass = db_info.getDBinfo().get("password");

		// 実行SQL
		String login_sql = "select * from admin_tb "
				+ "where admin_id = ? and password = ?;";
		// 管理者のオブジェクトを作成
		Admin admin = new Admin();

		// データベースへの接続
		// try〜catch〜resources構文を使用
		try(Connection conn = DriverManager.getConnection(url,user,pass)) {
			PreparedStatement stmt = conn.prepareStatement(login_sql);

			// 変数login_sqlの一番目の?に引数のuser_idをセット
			stmt.setString(1, admin_id);
			// 変数login_sqlの二番目の?に引数のpasswordをセット
			stmt.setString(2, password);

			// SQLを実行し、結果を取得
			ResultSet rs = stmt.executeQuery();

			// データベースから取得した値をAdminオブジェクトに格納
			// 値がなければ、login_flag（false）のみ格納
			if(rs.next()) {
				admin.setId(rs.getInt("admin_id"));
				admin.setName(rs.getString("name"));
				admin.setPassword(rs.getString("password"));
				admin.setLogin_flag(true);
			} else {
				admin.setLogin_flag(false);
			}
		} catch (SQLException e) {
			System.out.println("データベースとの接続を閉じます");
			e.printStackTrace();
		}
		// データベースから取得した値を返す
		return admin;
	}

	// ログイン成功後に管理者が管理する顧客情報の取得
	public List<Customer> getCustomerInfo(String admin_id) throws FileNotFoundException {

		// データベースへの接続情報をプロパティファイルから取得
		DBconfig db_info = new DBconfig();
		String url = db_info.getDBinfo().get("url");
		String user = db_info.getDBinfo().get("user");
		String pass = db_info.getDBinfo().get("password");

		// 実行SQL
		// admin_id(管理者ID)で該当する顧客情報を取得する
		String customer_sql = "select * from customer_tb "
				            + "where admin_id = ?;";

		// 顧客情報のデータを格納するListを作成
		List<Customer> cus_list = new ArrayList<Customer>();

		try(Connection conn = DriverManager.getConnection(url, user, pass)){
			PreparedStatement stmt = conn.prepareStatement(customer_sql);

			stmt.setString(1, admin_id);
			ResultSet rs = stmt.executeQuery();

			while(rs.next()) {
				// 顧客情報用のオブジェクトを作成
				Customer cus_info = new Customer();
				// オブジェクトにデータを一時格納
				cus_info.setCustomer_id(rs.getInt("customer_id"));
				cus_info.setAdmin_id(rs.getInt("admin_id"));
				cus_info.setName(rs.getString("name"));
				cus_info.setAddress(rs.getString("address"));
				cus_info.setRegistered_time(rs.getDate("registered_time"));
				cus_info.setUpdated_time(rs.getDate("updated_time"));

				// オブジェクトに格納された
				// 顧客情報用のデータをリストに加える
				cus_list.add(cus_info);
			}
		} catch (SQLException e) {
			System.out.println("データベースとの接続を閉じます");
			e.printStackTrace();
		}
		return cus_list;
	}
}

LoginServlet.java

package servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import object.Admin;
import object.Customer;
import sql.Login;

/**
 * Servlet implementation class LoginServlet
 */
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;


	//ログイン画面を表示させる
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		RequestDispatcher dispatcher =
				request.getRequestDispatcher("WEB-INF/jsp/login.jsp");
		dispatcher.forward(request, response);
	}

	// ログイン処理の実装
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// 文字コードの設定
		response.setContentType("text/html; charset=UTF-8");
		request.setCharacterEncoding("UTF-8");

		// ログイン画面で入力された値を取得
		String admin_id = request.getParameter("admin_id");
		String password = request.getParameter("password");

		// ログイン画面で入力された値をもとに
		// データベースに登録された管理者の値を取得
		// 入力された情報でデータベースから値が取得できない場合
		// ログイン失敗
		Login login = new Login();
		Admin admin = login.check(admin_id, password);

		if(admin.isLogin_flag()) {
			// ログイン成功 → 次の画面へ遷移
			System.out.println("ログイン成功");

			List<Customer> customer = null;
			// データベースから取得した顧客情報を格納
			customer = login.getCustomerInfo(admin_id);

			// 格納した顧客情報を遷移先の画面に渡す
			request.setAttribute("customer", customer);

			RequestDispatcher dispatcher =
					request.getRequestDispatcher("WEB-INF/jsp/customer_list.jsp");
			dispatcher.forward(request, response);
		} else {
			// ログイン失敗 → ログイン画面へ遷移
			System.out.println("ログイン失敗");
			RequestDispatcher dispatcher =
					request.getRequestDispatcher("WEB-INF/jsp/login.jsp");
			dispatcher.forward(request, response);
		}
	}
}

request.setAttribute(“データの名前”,登録するデータ)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="object.Customer" %>
<% List<Customer> customer_list = (List<Customer>)request.getAttribute("customer"); %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>顧客一覧画面</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
</head>
<body>
	<div class="mx-auto text-center" style="width: 70%;">
	<h2 class="text-center mb-3">顧客一覧</h2>
	<table class="table table-striped">
		<thead>
			 <tr>
			 	<th scope="col">顧客ID</th>
			 	<th scope="col">お客様名</th>
			 	<th scope="col">住所</th>
			 	<th scope="col">登録日</th>
			 	<th scope="col">更新日</th>
			 </tr>
		</thead>
		<tbody>
			 <% for(Customer cus : customer_list){ %>
			 <tr>
			 	<td><%= cus.getCustomer_id() %></td>
			 	<td><%= cus.getName() %></td>
			 	<td><%= cus.getAddress() %></td>
			 	<td><%= cus.getRegistered_time() %></td>
			 	<td><%= cus.getUpdated_time() %></td>
			 </tr>
			 <% } %>
		</tbody>
	</table>
</div>
</body>
</html>