Webアプリ開発入門 Servlet/JSP編3:具体例：特定のプレイヤーを表示する

※有料会員になるとこの動画をご利用いただけます

＃11:具体例：特定のプレイヤーを表示する

ここでは、ServletとJSP・データベースを使って、簡単なWebアプリケーションを作ります。RPGのプレイヤー一覧から、各プレイヤーの詳細情報を表示するアプリケーションを作ってみましょう。

プレイヤー一覧画面のServlet

(tomcat/webapps/mydatabase/PlayerListServlet.java)

// RPG Playerリスト
// Servlet-JSP間をArryaListで渡す

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class PlayerListServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {

        request.setAttribute("message", "Playerリスト");

        String url = "jdbc:mysql://localhost/mydb";
        String user = "root";
        String password = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement statment = connection.prepareStatement("SELECT * FROM players LEFT JOIN jobs ON jobs.id = players.jobId");
             ResultSet results = statment.executeQuery()) {

            ArrayList<HashMap<String, String>> rows = new ArrayList<HashMap<String, String>>();

            while (results.next()) {
                HashMap<String, String> columns = new HashMap<String, String>();

                String id = results.getString("id");
                columns.put("id", id);

                String name = results.getString("name");
                columns.put("name", name);

                String level = results.getString("level");
                columns.put("level", level);

                String jobId = results.getString("jobId");
                columns.put("jobId", jobId);

                String jobName = results.getString("jobName");
                columns.put("jobName", jobName);

                rows.add(columns);
            }

            request.setAttribute("rows", rows);

        } catch (Exception e) {
            request.setAttribute("message", "Exception:" + e.getMessage());
        }

        String view = "/WEB-INF/views/playerList.jsp";
        RequestDispatcher dispatcher = request.getRequestDispatcher(view);
        dispatcher.forward(request, response);
    }
}

プレイヤー一覧画面のJSP

(tomcat/webapps/mydatabase/WEB-INF/views/playerList.jsp)

<!-- ServletからArryaListでデータ渡し -->

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html" charset="UTF-8">
        <title>Java - paiza</title>
        <style>body {padding: 30px;}</style>
    </head>
    <body>
        <h1><%= request.getAttribute("message") %></h1>

        <%
        ArrayList<HashMap<String,String>> rows = (ArrayList<HashMap<String,String>>)request.getAttribute("rows");
        %>

        <table>
        <%
        for (HashMap<String,String> columns : rows) {
        %>
            <tr>
                <td><%= columns.get("id") %></td>
                <td><%= columns.get("name") %></td>
                <td><%= columns.get("level") %></td>
                <td><%= columns.get("jobName") %></td>
            </tr>
        <% } %>
        </table>

    </body>
</html>

プレイヤー詳細画面のServlet

(tomcat/webapps/mydatabase/PlayerProfileServlet.java)

// RPG Playerリスト
// Servlet-JSP間をArryaListで渡す

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class PlayerProfileServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {

        int playerId = Integer.parseInt(request.getParameter("id"));
        request.setAttribute("message", "Hello player " + playerId);

        String url = "jdbc:mysql://localhost/mydb";
        String user = "root";
        String password = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement statment = connection.prepareStatement("SELECT * FROM players LEFT JOIN jobs ON jobs.id = players.jobId WHERE players.id = ?")) {

             statment.setInt(1, playerId);
             ResultSet results = statment.executeQuery();

            while (results.next()) {

                String id = results.getString("id");
                request.setAttribute("id", id);

                String name = results.getString("name");
                request.setAttribute("name", name);

                String level = results.getString("level");
                request.setAttribute("level", level);

                String jobId = results.getString("jobId");
                request.setAttribute("jobId", jobId);

                String jobName = results.getString("jobName");
                request.setAttribute("jobName", jobName);
            }

        } catch (Exception e) {
            request.setAttribute("message", "Exception:" + e.getMessage());
        }

        String view = "/WEB-INF/views/playerProfile.jsp";
        RequestDispatcher dispatcher = request.getRequestDispatcher(view);
        dispatcher.forward(request, response);
    }
}

参考になるWebサイト

- Javaの道＞Servlet・JSP
https://www.javaroad.jp/servletjsp/index.html

- Servletアプリ開発：目次 - Web系開発メモ
http://web-dev.hatenablog.com/entry/java/servlet/dev-restful-app/table-of-contents

- JSP(formデータ)とJava Servletプログラムの連携 - はしくれエンジニアもどきのメモ
http://cartman0.hatenablog.com/entry/2015/12/08/181657

- データベース接続 - サーブレット入門
https://www.javadrive.jp/servlet/database/

プログラミング学習 > Webアプリ開発入門 Servlet/JSP編 > Webアプリ開発入門 Servlet/JSP編3:JavaとSQLでデータベースを操作しよう > 具体例：特定のプレイヤーを表示する