13.9　練習問題　解答

練習問題　解答

13.9　練習問題　解答

問1

➢　PracticeDao.java

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

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

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

➢　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　練習問題　解答