新・Servlet/JSP入門編8:メッセージの一覧表示機能の実装

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

＃02:メッセージの一覧表示機能の実装

このチャプターでは、メッセージの一覧表示機能を実装します。

入力コード

【PostModel#findAll()のSQL】

select posts.id, posts.account_id, accounts.user_id, posts.content, posts.created_at, posts.updated_at from posts inner join accounts on posts.account_id = accounts.id order by posts.id desc

【PostModel#findAll()】

public List<PostBean> findAll() throws SQLException {
    String sql = "select posts.id, posts.account_id, accounts.user_id, posts.content, posts.created_at, posts.updated_at from posts inner join accounts on posts.account_id = accounts.id order by posts.id desc";
    ArrayList<PostBean> l = new ArrayList<>();
    try (PreparedStatement query = con.prepareStatement(sql)) {
        ResultSet rs = query.executeQuery();
        while (rs.next()) {
            PostBean post = new PostBean();
            post.setId(rs.getInt("id"));
            post.setAccountId(rs.getInt("account_id"));
            post.setUserId(rs.getString("user_id"));
            post.setContent(rs.getString("content"));
            post.setCreatedAt(rs.getTimestamp("created_at"));
            post.setUpdatedAt(rs.getTimestamp("updated_at"));
            l.add(post);
        }
    }
    return l;
}

【posts.jsp】

<%@ page contentType="text/html; charset=UTF-8" import="jp.paiza.bean.PostBean"%>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<html>
<head>
    <meta charset="UTF-8">
    <title>投稿一覧</title>
    <link rel="stylesheet" href="css/style.css" type="text/css">
</head>
<body>
<jsp:include page="header.jsp" />
<div class="page-background">
    <div class="content-container">
        <h1>投稿一覧</h1>
        <table>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>日時</th>
                    <th>ユーザーID</th>
                    <th>投稿内容</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
            <c:forEach var="post" items="${posts}">
                <tr>
                    <td><c:out value="${post.id}" /></td>
                    <td><c:out value="${post.createdAt}" /></td>
                    <td><c:out value="${post.userId}" /></td>
                    <td><c:out value="${post.content}" /></td>
                    <td>
                        <c:if test="${account.id == post.accountId}">
                            <a href="/learning/post-update?id=${post.id}">更新</a>
                            <br>
                            <a href="/learning/post-delete?id=${post.id}">削除</a>
                        </c:if>
                    </td>
                </tr>
            </c:forEach>
            </tbody>
        </table>
    </div>
</div>
</body>
</html>

【PostServlet】

package jp.paiza.servlet;

import java.io.*;
import jakarta.servlet.*;
import jakarta.servlet.http.*;
import jakarta.servlet.annotation.*;
import jp.paiza.model.PostModel;
import javax.sql.*;
import jakarta.annotation.*;
import java.sql.*;

@WebServlet("/posts")
public class PostServlet extends HttpServlet {

    @Resource(name = "jdbc/datasource")
    private DataSource ds;
    
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {
        
        doPost(req, res);
    }
    
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {

        try (Connection con = ds.getConnection()) {
            
            PostModel p = new PostModel(con);
            req.setAttribute("posts", p.findAll());
            
            RequestDispatcher d = 
                req.getRequestDispatcher("/WEB-INF/jsp/posts.jsp");
    
            d.forward(req, res);
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
}

【header.jsp】

<%@ page contentType="text/html; charset=UTF-8" %>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>

完成コード

【PostModel】

package jp.paiza.model;

import java.util.*;
import java.time.*;
import jp.paiza.bean.*;
import java.sql.*;
import jp.paiza.bean.AccountBean;

public class PostModel {

    private Connection con;

    public PostModel(Connection con) {
        this.con = con;
    }
    
    public List<PostBean> findAll() throws SQLException {
        String sql = "select posts.id, posts.account_id, accounts.user_id, posts.content, posts.created_at, posts.updated_at from posts inner join accounts on posts.account_id = accounts.id order by posts.id desc";

        ArrayList<PostBean> l = new ArrayList<>();
        try (PreparedStatement query = con.prepareStatement(sql)) {
            ResultSet rs = query.executeQuery();
            while (rs.next()) {
                PostBean post = new PostBean();
                post.setId(rs.getInt("id"));
                post.setAccountId(rs.getInt("account_id"));
                post.setUserId(rs.getString("user_id"));
                post.setContent(rs.getString("content"));
                post.setCreatedAt(rs.getTimestamp("created_at"));
                post.setUpdatedAt(rs.getTimestamp("updated_at"));
                l.add(post);
            }
        } 
        return l;
    }
}

【PostServlet】

package jp.paiza.servlet;

import java.io.*;
import javax.sql.*;
import jakarta.annotation.*;
import java.sql.*;
import jakarta.servlet.*;
import jakarta.servlet.http.*;
import jakarta.servlet.annotation.*;
import jp.paiza.model.PostModel;
import jp.paiza.bean.AccountBean;

@WebServlet("/posts")
public class PostServlet extends HttpServlet {

    @Resource(name = "jdbc/datasource")
    private DataSource ds;
    
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {
        
        doPost(req, res);
    }
    
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {

        try (Connection con = ds.getConnection()) {
            PostModel p = new PostModel(con);
            req.setAttribute("posts", p.findAll());
                
            RequestDispatcher d = 
                req.getRequestDispatcher("/WEB-INF/jsp/posts.jsp");
    
            d.forward(req, res);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServletException(e);
        }
    }
}

【posts.jsp】

<%@ page contentType="text/html; charset=UTF-8" import="jp.paiza.bean.PostBean"%>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<html>
<head>
    <meta charset="UTF-8">
    <title>投稿一覧</title>
    <link rel="stylesheet" href="css/style.css" type="text/css">
</head>
<body>
<jsp:include page="header.jsp" />
<div class="page-background">
    <div class="content-container">
        <h1>投稿一覧</h1>
        <table>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>日時</th>
                    <th>ユーザーID</th>
                    <th>投稿内容</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
            <c:forEach var="post" items="${posts}">
                <tr>
                    <td><c:out value="${post.id}" /></td>
                    <td><c:out value="${post.createdAt}" /></td>
                    <td><c:out value="${post.userId}" /></td>
                    <td><c:out value="${post.content}" /></td>
                    <td>
                        <c:if test="${account.id == post.accountId}">
                            <a href="">更新</a>
                            <br>
                            <a href="">削除</a>
                        </c:if>
                    </td>
                </tr>
            </c:forEach>
            </tbody>
        </table>
    </div>
</div>
</body>
</html>

【header.jsp】

<%@ page contentType="text/html; charset=UTF-8" %>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<header>
    <div>MySite</div>
    <c:if test="${not empty account}">
    <nav>
        <ul>
            <li><a href="/learning/posts">一覧</a></li>
            <li><a href="#">B</a></li>
            <li><a href="#">C</a></li>
        </ul>
    </nav>
    </c:if>
</header>

公式リファレンス

インタフェースConnection
https://docs.oracle.com/javase/jp/17/docs/api/java.sql/java/sql/Connection.html

インタフェースPreparedStatement
https://docs.oracle.com/javase/jp/17/docs/api/java.sql/java/sql/PreparedStatement.html

インタフェースResultSet
https://docs.oracle.com/javase/jp/17/docs/api/java.sql/java/sql/ResultSet.html

クラスSQLException
https://docs.oracle.com/javase/jp/17/docs/api/java.sql/java/sql/SQLException.html

Class HttpServlet
https://jakarta.ee/specifications/servlet/6.1/apidocs/jakarta.servlet/jakarta/servlet/http/httpservlet

Interface HttpServletRequest
https://jakarta.ee/specifications/servlet/6.1/apidocs/jakarta.servlet/jakarta/servlet/http/httpservletrequest

Interface HttpServletResponse
https://jakarta.ee/specifications/servlet/6.1/apidocs/jakarta.servlet/jakarta/servlet/http/httpservletresponse

Interface HttpSession
https://jakarta.ee/specifications/servlet/6.1/apidocs/jakarta.servlet/jakarta/servlet/http/httpsession

プログラミング学習 > 新・Servlet/JSP入門編 > 新・Servlet/JSP入門編8: CRUD機能を作成しよう > メッセージの一覧表示機能の実装

新・Servlet/JSP入門編8: CRUD機能を作成しよう

＃02:メッセージの一覧表示機能の実装

入力コード

完成コード

関連講座

公式リファレンス