13.9　練習問題　解答（2023年改訂版）

練習問題　解答

13.9　練習問題　解答

問1

➢　PracticeDao.java

1package ch13exercise;
2 
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.sql.Statement;
8import java.util.ArrayList;
9 
10public class PracticeDao {
11    //接続用の情報をフィールドに定数として定義
12 
13    private static String RDB_DRIVE = "org.mariadb.jdbc.Driver";
14    private static String URL = "jdbc:mariadb://localhost/practice_web_db";
15    private static String USER = "root";
16    private static String PASS = "root123";
17  
18    //データベース接続を行うメソッド
19    public static Connection getConnection(){
20        try{
21            Class.forName(RDB_DRIVE);
22            Connection con = DriverManager.getConnection(URL, USER, PASS);
23            return con;
24        }catch(Exception e){
25            throw new IllegalStateException(e);
26        }
27    }
28  
29    //データベースから全てのデータの検索を行うメソッド
30    public ArrayList<PracticeInfo> selectAll(){
31        //変数宣言
32        Connection con = null;
33        Statement  smt = null;
34  
35        //return用オブジェクトの生成
36        ArrayList<PracticeInfo> list = new ArrayList<PracticeInfo>();
37  
38        //SQL文
39        String sql = "SELECT * FROM practice_table";
40  
41        try{
42            con = getConnection();
43            smt = con.createStatement();
44  
45            //SQLをDBへ発行
46            ResultSet rs = smt.executeQuery(sql);
47  
48            //検索結果を配列に格納
49            while(rs.next()){
50                PracticeInfo info =new PracticeInfo();
51                info.setId(rs.getString("id"));
52                info.setName(rs.getString("name"));
53                info.setPrice(rs.getInt("price"));
54                info.setAuthor(rs.getString("author"));
55                info.setComment(rs.getString("comment"));
56                list.add(info);
57            }
58  
59        }catch(Exception e){
60            throw new IllegalStateException(e);
61        }finally{
62            //リソースの開放
63            if(smt != null){
64                try{smt.close();}catch(SQLException ignore){}
65            }
66            if(con != null){
67                try{con.close();}catch(SQLException ignore){}
68            }
69        }
70        return list;
71    }
72  
73    //データベースから指定された1件のデータの検索を行うメソッド
74    public PracticeInfo selectById(String id){
75        //変数宣言
76        Connection con = null;
77        Statement  smt = null;
78  
79        //return用オブジェクトを宣言
80        PracticeInfo info =new PracticeInfo();
81  
82        //SQL文
83        String sql = "SELECT * FROM practice_table WHERE id = '" + id + "'";
84  
85        try{
86            con = getConnection();
87            smt = con.createStatement();
88  
89            //SQLをDBへ発行
90            ResultSet rs = smt.executeQuery(sql);
91  
92            //取得した結果をreturn用オブジェクトに格納する
93            if(rs.next()){
94                info.setId(rs.getString("id"));
95                info.setName(rs.getString("name"));
96                info.setPrice(rs.getInt("price"));
97                info.setAuthor(rs.getString("author"));
98                info.setComment(rs.getString("comment"));
99            }
100        }catch(Exception e){
101            throw new IllegalStateException(e);
102        }finally{
103            //リソースの開放
104            if(smt != null){
105                try{smt.close();}catch(SQLException ignore){}
106            }
107            if(con != null){
108                try{con.close();}catch(SQLException ignore){}
109            }
110        }
111        return info;
112    }
113  }

➢　PracticeInfo.java

1package ch13exercise;
2 
3public class PracticeInfo {
4 
5    private String id;      //IDデータ格納用変数
6    private String name;    //名前データ格納用変数
7    private int price;      //価格データ格納用変数
8    private String author;  //著者データ格納用変数
9    private String comment; //コメントデータ格納用変数
10  
11  
12    public PracticeInfo() {
13        this.id = "";
14        this.name = "";
15        this.price = 0;
16        this.author = "";
17        this.comment = "";
18    }
19  
20    //変数idのアクセサメソッド
21    public String getId() {
22        return id;
23    }
24    public void setId(String id) {
25        this.id = id;
26    }
27  
28    //変数nameのアクセサメソッド
29    public String getName() {
30        return name;
31    }
32    public void setName(String name) {
33        this.name = name;
34    }
35  
36    //変数priceのアクセサメソッド
37    public int getPrice() {
38        return price;
39    }
40    public void setPrice(int price) {
41        this.price = price;
42    }
43  
44    //変数authorのアクセサメソッド
45    public String getAuthor() {
46        return author;
47    }
48    public void setAuthor(String author) {
49        this.author = author;
50    }
51  
52    //変数commentのアクセサメソッド
53    public String getComment() {
54        return comment;
55    }
56    public void setComment(String comment) {
57        this.comment = comment;
58    }
59 }

➢　Practice1301Servlet.java

1package ch13exercise;
2 
3import java.io.IOException;
4import java.util.ArrayList;
5 
6import jakarta.servlet.ServletException;
7import jakarta.servlet.annotation.WebServlet;
8import jakarta.servlet.http.HttpServlet;
9import jakarta.servlet.http.HttpServletRequest;
10import jakarta.servlet.http.HttpServletResponse;
11 
12@WebServlet("/Practice1301Servlet")
13public class Practice1301Servlet extends HttpServlet{
14    public void doGet(HttpServletRequest request ,HttpServletResponse response)
15            throws ServletException ,IOException{
16  
17        String error = "";
18  
19        try{
20            //配列宣言
21            ArrayList<PracticeInfo> list = new ArrayList<PracticeInfo>();
22  
23            //DAOオブジェクト宣言
24            PracticeDao objDao = new PracticeDao();
25  
26            //全検索メソッドを呼び出し
27            list = objDao.selectAll();
28  
29            //検索結果を持ってpractice1301.jspへフォワード
30            request.setAttribute("list", list);
31  
32        }catch (IllegalStateException e) {
33            error ="DB接続エラーの為、一覧表示はできませんでした。";
34  
35        }catch(Exception e){
36            error ="予期せぬエラーが発生しました。<br>"+e;
37  
38        }finally{
39            request.setAttribute("error", error);
40            request.getRequestDispatcher("/view/ch13exercise/practice1301.jsp").forward(request, response);
41        }
42    }
43 }

➢　practice1301.jsp

1<%@page contentType="text/html; charset=UTF-8"%>
2<%@page import="java.util.ArrayList,ch13exercise.PracticeInfo"%>
3<%
4ArrayList<PracticeInfo> list = (ArrayList<PracticeInfo>)request.getAttribute("list");
5String error = (String)request.getAttribute("error");
6%>
7<html>
8    <head>
9        <title>practice1301</title>
10    </head>
11    <body>
12        <div style="text-align:center">
13            <h2 style="text-align:center">一覧画面</h2>
14            <hr style="height:3; background-color:#0000ff" />
15            <br>
16            <%= error %>
17            <br>
18            <table style="border:1px solid; margin:0 auto">
19                <tr>
20                    <th style="background-color:#6666FF; width:100">ID</th>
21                    <th style="background-color:#6666FF; width:200">名前</th>
22                </tr>
23                <%
24                if(list != null){
25                    for(int i=0;i<list.size();i++){
26                %>
27                <tr>
28                    <td style="text-align:center; width:100">
29                        <A href="<%=request.getContextPath() %>/Practice1302Servlet?id=<%=list.get(i).getId()%>">
30                        <%= list.get(i).getId() %></A>
31                    </td>
32                    <td style="text-align:center; width:100"><%= list.get(i).getName() %></td>
33                </tr>
34                <%
35                    }
36                }
37                %>
38            </table>
39            <br>
40        </div>
41    </body>
42 </html>

➢　Practice1302Servlet.java

1package ch13exercise;
2 
3import java.io.IOException;
4 
5import jakarta.servlet.ServletException;
6import jakarta.servlet.annotation.WebServlet;
7import jakarta.servlet.http.HttpServlet;
8import jakarta.servlet.http.HttpServletRequest;
9import jakarta.servlet.http.HttpServletResponse;
10 
11@WebServlet("/Practice1302Servlet")
12public class Practice1302Servlet extends HttpServlet{
13    public void doGet(HttpServletRequest request ,HttpServletResponse response)
14            throws ServletException ,IOException{
15  
16        String error = "";
17  
18        try{
19            //パラメータの取得
20            String id = request.getParameter("id");
21  
22            //DTOオブジェクト宣言
23            PracticeInfo info = new PracticeInfo();
24  
25            //DAOオブジェクト宣言
26            PracticeDao objDao = new PracticeDao();
27  
28            //1件検索メソッドを呼び出し
29            info = objDao.selectById(id);
30  
31            //検索結果を持ってpractice1302.jspへフォワード
32            request.setAttribute("info", info);
33  
34        }catch (IllegalStateException e) {
35            error ="DB接続エラーの為、一覧表示はできませんでした。";
36  
37        }catch(Exception e){
38            error ="予期せぬエラーが発生しました。<br>"+e;
39  
40        }finally{
41            request.setAttribute("error", error);
42            request.getRequestDispatcher("/view/ch13exercise/practice1302.jsp").forward(request, response);
43        }
44    }
45 }

➢　practice1302.jsp

1<%@page contentType="text/html; charset=UTF-8"%>
2<%@page import="ch13exercise.PracticeInfo"%>
3<%
4PracticeInfo info = (PracticeInfo)request.getAttribute("info");
5String error = (String)request.getAttribute("error");
6%>
7<html>
8    <head>
9        <title>practice1302</title>
10    </head>
11    <body>
12        <div style="align:center">
13            <h2 style="align:center">詳細画面</h2>
14            <hr style="height:3; background-color:#0000ff" />
15            <br>
16            <%= error %>
17            <br>
18            <table style="border: 1px solid; margin:0 auto">
19            <%
20                if(info != null){
21            %>
22                <tr>
23                    <th style="background-color:#6666FF; width:100">ID</th>
24                    <td style="align:center; width:200"><%= info.getId() %></td>
25                </tr>
26                <tr>
27                    <th style="background-color:#6666FF; width:100">名前</th>
28                    <td style="align:center; width:200"><%= info.getName() %></td>
29                </tr>
30  
31                <tr>
32                    <th style="background-color:#6666FF; width:100">価格</th>
33                    <td style="align:center; width:200"><%= info.getPrice() %></td>
34                </tr>
35                <tr>
36                    <th style="background-color:#6666FF; width:100">著者</th>
37                    <td style="align:center; width:200"><%= info.getAuthor() %></td>
38                </tr>
39                <tr>
40                    <th style="background-color:#6666FF; width:100; height:50">コメント</th>
41                    <td style="align:center; width:200; height:50"><%= info.getComment() %></td>
42                </tr>
43            <%
44                }
45            %>
46            </table>
47            <br>
48        </div>
49    </body>
50 </html>

NEXT>> 第14章トラブルシューティング

練習問題 解答

13.9 練習問題 解答

問1

練習問題　解答

13.9　練習問題　解答