8. Spring BootとMySQLの連携 〜 データを保存・取得しよう

		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<scope>runtime</scope>
		</dependency>

package com.example.demo.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * SuperDao - データベース接続の取得を担当する基底クラス
 */
public class SuperDao {

	// 接続情報は本来外部ファイル化すべきですが、
	// 教育上の簡略化のため直接記述します
	private static final String DB_URI = 
			"jdbc:mysql://localhost:3306/sip_a?"
			+ "characterEncoding=utf8&"
			+ "useSSL=false&serverTimezone=GMT%2B9&"
			+ "rewriteBatchedStatements=true";
	private static final String DB_USER = "newuser";
	private static final String DB_PASS = "0";

	/**
	 * データベース接続を取得する
	 * 呼び出し元で try-with-resources を使用して確実に close
	 * @return データベース接続オブジェクト
	 * @throws SQLException 接続失敗時にスロー
	 */
	protected Connection getConnection() throws SQLException {
		return DriverManager.getConnection(DB_URI, 
				DB_USER, DB_PASS);
	}
}

	public static void main(String[] args) {
        SuperDao dao = new SuperDao();
        
        System.out.println("テストを開始します...");

        // try-with-resources 文を使用して接続テストを実施
        try (Connection con = dao.getConnection()) {
            
            if (con != null && !con.isClosed()) {
                System.out.println("成功：正常に接続されました！");
                System.out.println("接続先: " + DB_URI);
            }

        } catch (SQLException e) {
            System.err.println("失敗：エラーが発生しました。");
            System.err.println("原因: " + e.getMessage());
            e.printStackTrace();
        }
        
        // tryブロックを抜けると自動的に con.close() が呼ばれる
        System.out.println("接続は自動的に切断されました");
    }

package com.example.demo.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class WithSuperClass extends SuperDao {

    public int countCars() {
        int ret = 0;
        String sql = "SELECT count(*) FROM cars";

        try (Connection conn = getConnection(); 
             PreparedStatement ps =
            		 conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            if (rs.next()) {
                ret = rs.getInt(1);
            }

        } catch (SQLException e) {
            System.err.println("データ取得中にエラーが発生。");
            e.printStackTrace();
        }

        return ret;
    }

    public static void main(String[] args) {
        WithSuperClass wsc = new WithSuperClass();
        System.out.println("登録台数: " + wsc.countCars());
    }
}

package com.example.demo.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.example.demo.model.dto.CarDto;

/**
 * CarsDaoクラス - carsテーブルに対するデータ操作を担当するクラス
 */
public class CarsDao extends SuperDao {

	// メソッドの開始：車の数をカウントするメソッド
	public int countCars() {

		// カウントした車の数を保持するための整数変数retを初期化
		int ret = 0;

		// 車の総数を取得するためのSQLクエリを定義
		String sql = "SELECT count(*) FROM cars";

		// try-with-resources文を使用する
		// これによりclose()の呼び出し忘れを防ぎ、確実にリソースを解放する
		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(sql);
				ResultSet rs = ps.executeQuery()) {

			// ResultSetの最初の行に移動し、値が存在すれば取得
			if (rs.next()) {
				// ResultSetから車の総数（列番号1）を取得してretに代入
				ret = rs.getInt(1);
			}

		} catch (SQLException e) {
			// SQL例外が発生した場合の処理
			// エラー内容を標準エラー出力に表示
			e.printStackTrace();
		}

		// 車の総数を返す
		return ret;
	}

	// 車の全件リストを取得するメソッド
	public List<CarDto> getCarList() {

		// CarDtoのリストを初期化
		List<CarDto> carList = new ArrayList<>();

		// 'cars'テーブルから全てのデータを選択するSQLクエリを定義
		String sql = "SELECT * FROM cars";

		// ConnectionとPreparedStatementを取得し、自動クローズを保証
		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(sql);
				ResultSet rs = ps.executeQuery()) {

			// ResultSetを繰り返し処理し、各車のデータを取得
			while (rs.next()) {
				// CarDtoオブジェクトを生成し、データをセット
				CarDto car = new CarDto();
				car.setCarId(rs.getInt("car_id"));
				car.setName(rs.getString("name"));
				car.setPrice(rs.getInt("price"));
				car.setDeletedAt(rs.getString("deleted_at"));

				// 取得した車のデータをリストに追加
				carList.add(car);
			}
		} catch (SQLException e) {
			// SQL例外が発生した場合のエラーハンドリング
			e.printStackTrace();
		}

		// 取得したリストを返す
		return carList;
	}

	// 特定のIDを持つ車の情報を取得するメソッド
	public CarDto getCar(int id) {

		// CarDtoのインスタンスを生成
		// 見つからない場合は中身が空のDtoを返す想定
		CarDto car = new CarDto();

		// 指定されたcar_idに基づいて車の情報を取得するSQL
		String sql = "SELECT * FROM cars WHERE car_id = ?";

		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(sql)) {

			// クエリのパラメータ（car_id）を設定
			ps.setInt(1, id);

			// クエリを実行し、結果をResultSetに格納
			try (ResultSet rs = ps.executeQuery()) {
				// データが存在する場合のみCarDtoにセット
				if (rs.next()) {
					car.setCarId(rs.getInt("car_id"));
					car.setName(rs.getString("name"));
					car.setPrice(rs.getInt("price"));
					car.setDeletedAt(rs.getString("deleted_at"));
				}
			}

		} catch (SQLException e) {
			// エラー情報を出力
			e.printStackTrace();
		}

		// 車の情報を返す
		return car;
	}

package com.example.demo.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class SQLInjection extends SuperDao {

    public void getACustomer() {
        String baseSql = "SELECT * FROM customers where customer_id = ";

        System.out.println("customer_idを整数値で指定してください");
        
        String inputId = "";
        try (Scanner sc = new Scanner(System.in)) {
            inputId = sc.nextLine();
        }

        // 脆弱性の再現：ユーザー入力をそのままSQL文字列に結合
        String dangerSql = baseSql + inputId;

        try (Connection con = getConnection();
             PreparedStatement ps = con.prepareStatement(dangerSql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                System.out.print(rs.getInt("customer_id") + ":");
                System.out.print(rs.getString("name") + ":");
                System.out.print(rs.getString("mail") + ":");
                System.out.print(rs.getString("mobile") + ":");
                System.out.println(rs.getString("pass"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        SQLInjection sqlInjection = new SQLInjection();
        sqlInjection.getACustomer();
    }
}

package com.example.demo.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Placeholder extends SuperDao {

    private static final String SQL =
    		"SELECT * FROM customers where customer_id = ?";

    public void getACustomer() {

        System.out.println("customer_idを整数値で指定してください");

        String customerId = "";
        try (Scanner sc = new Scanner(System.in)) {
            customerId = sc.nextLine();
        }

        // try-with-resourcesを使用してリソースを自動解放
        try (Connection con = getConnection();
             PreparedStatement ps = con.prepareStatement(SQL)) {

            // プレースホルダに値をセット
        	// 型安全かつエスケープ処理が行われる
            ps.setString(1, customerId);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.print(rs.getInt("customer_id") + ":");
                    System.out.print(rs.getString("name") + ":");
                    System.out.print(rs.getString("mail") + ":");
                    System.out.print(rs.getString("mobile") + ":");
                    System.out.println(rs.getString("pass"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Placeholder placeholder = new Placeholder();
        placeholder.getACustomer();
    }
}

	// 任意のフィールドで並べ替えたリストを取得するメソッド
	public List<CarDto> getSortedCarList(String sortField, boolean isAscending) {
		List<CarDto> carList = new ArrayList<>();

		// sortFieldが有効なフィールド名であるか確認（SQLインジェクション対策）
		if (!isValidSortField(sortField)) {
			throw new IllegalArgumentException("無効なソート項目です: " + sortField);
		}

		// 並び順を決定
		String sortOrder = isAscending ? "ASC" : "DESC";
		// ORDER BY句はプレースホルダ(?)が使えないため文字列結合を利用
		String sql = "SELECT * FROM cars ORDER BY " + sortField + " " + sortOrder;

		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(sql);
				ResultSet rs = ps.executeQuery()) {

			while (rs.next()) {
				CarDto car = new CarDto();
				car.setCarId(rs.getInt("car_id"));
				car.setName(rs.getString("name"));
				car.setPrice(rs.getInt("price"));
				car.setDeletedAt(rs.getString("deleted_at"));
				carList.add(car);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return carList;
	}

	// キーワードに基づいて車を検索するメソッド
	public List<CarDto> searchCarList(String keyword) {

		List<CarDto> carList = new ArrayList<>();

		// 名前（name）に対する部分一致検索のSQL
		String sql = "SELECT * FROM cars WHERE name LIKE ?";

		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(sql)) {

			// あいまい検索用のキーワードを設定
			ps.setString(1, "%" + keyword + "%");

			try (ResultSet rs = ps.executeQuery()) {
				while (rs.next()) {
					CarDto car = new CarDto();
					car.setCarId(rs.getInt("car_id"));
					car.setName(rs.getString("name"));
					car.setPrice(rs.getInt("price"));
					car.setDeletedAt(rs.getString("deleted_at"));
					carList.add(car);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return carList;
	}

	// 指定されたフィールド名が許可されているかチェックするメソッド
	private boolean isValidSortField(String field) {
		List<String> validFields = 
				Arrays.asList("car_id", "name", "price", "deleted_at");
		return validFields.contains(field);
	}

	// 新しい車のデータを追加するメソッド
	public int addCar(CarDto car) {

		// 影響を受けた行数を保持する変数
		int ret = 0;

		// nameとpriceを挿入するSQL
		String sql = "INSERT INTO cars(name, price) VALUES(?, ?)";

		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(sql)) {

			// 値をセット
			ps.setString(1, car.getName());
			ps.setInt(2, car.getPrice());

			// 更新系SQLを実行し、件数を取得
			ret = ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	// 車の情報を更新するメソッド
	public int updateCar(CarDto car) {
		int ret = 0;

		// 全項目をID指定で更新するSQL
		String sql =
				"UPDATE cars SET name = ?, price = ?, deleted_at = ? WHERE car_id = ?";

		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(sql)) {

			// パラメータの設定
			ps.setString(1, car.getName());
			ps.setInt(2, car.getPrice());
			ps.setString(3, car.getDeletedAt());
			ps.setInt(4, car.getCarId());

			// SQLを実行
			ret = ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

package com.example.demo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class LoginDao extends SuperDao {

	// ログインメソッド。ユーザーIDとパスワードを受け取り、ログイン成功かどうかを返す
	public boolean login(String id, String pass) {
		boolean isSuccess = false;

		// ユーザーIDとパスワードに一致するレコードが存在するかを確認するクエリ
		String query =
				"SELECT COUNT(*) FROM login_user WHERE login_id = ? AND password = ?";

		try (Connection con = getConnection();
				PreparedStatement ps = con.prepareStatement(query)) {

			// クエリのプレースホルダーにユーザーIDとパスワードをセット
			ps.setString(1, id);
			ps.setString(2, pass);

			// クエリを実行して結果セットを取得
			try (ResultSet rs = ps.executeQuery()) {
				// 結果セットが存在し、最初のカラムの値が0より大きければログイン成功
				if (rs.next()) {
					// 列名指定よりも列番号(1)での取得がタイポに強く推奨されます
					isSuccess = rs.getInt(1) > 0;
				}
			}

		} catch (SQLException e) {
			// 例外発生時のスタックトレースを出力
			e.printStackTrace();
		}

		// ログイン成功または失敗の結果を返す
		return isSuccess;
	}

	public static void main(String[] args) {
		LoginDao loginDao = new LoginDao();
		// テスト実行
		boolean success = loginDao.login("imai", "p");
		System.out.println("ログイン成功: " + success);
	}
}

package com.example.demo.controller;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

import com.example.demo.dao.LoginDao;

import jakarta.servlet.http.HttpSession;

@Controller
public class LoginController {

    @GetMapping("/")
    public String showLoginPage() {
        return "login"; // `login.html` を表示
    }
    
    @PostMapping("/login")
    public String login(
            String id,
            String pass,
            HttpSession session,
            Model model) {

        LoginDao loginDao = new LoginDao();

        // 入力チェック
        if (id.isEmpty() || pass.isEmpty()) {
            model.addAttribute("error", "ユーザーIDまたはパスワードを入力ください");
            return "login-error";
        }

        // 認証処理
        if (loginDao.login(id, pass)) {
            session.setAttribute("id", id); // セッションにユーザーIDを保存
            return "redirect:/member-only2";
        } else {
            model.addAttribute("error", "ユーザーIDまたはパスワードが間違っています");
            return "login-error";
        }
    }

    @GetMapping("/custom-logout")
    public String logout(HttpSession session) {
        if (session != null) {
            session.invalidate();
        }
        return "redirect:/re-login";
    }
    
    @GetMapping("/re-login")// 再度ログインする場合
    public String reLoginPage() {
    	return "login"; 
    }
}

package com.example.demo.controller;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

import jakarta.servlet.http.HttpSession;

@Controller
public class MemberController {

	@GetMapping("/member-only2")
	public String memberPage(HttpSession session, Model model) {
		// セッションがない場合、またはIDがない場合はログインページへリダイレクト
		if (session.getAttribute("id") == null) {
			return "redirect:/login";
		}

		return "member-only2"; // `member-only2.html` を表示
	}

	@GetMapping("/video")
	public String videoPage(HttpSession session, Model model) {
		// セッションがない場合、またはIDがない場合はログインページへリダイレクト
		if (session.getAttribute("id") == null) {
			return "redirect:/login";
		}
		
		return "video"; // `video.html` を表示
	}
}