JSP & Servlet-(DB接続の基礎DAO) | ジョイタスネット

サーバーサイドにJavaを利用しMySQLからデータを取得し一覧表示する処理のサンプル
[作成]
１．MySQLでデータベース[lunchapp]を作成する。

`1`	`CREATE DATABASE lunchapp`

`2`	`DEFAULT CHARACTER SET utf8;`

2．データベースlunchappに[lunches]テーブルを作成する。

1CREATE TABLE lunches(
2 id INT PRIMARY KEY AUTO_INCREMENT,
3 name VARCHAR(30),
4 menu VARCHAR(30)
5 );

3.初期データを挿入しておく

`1`	`INSERT INTO lunches(name,menu)VALUES`

`2`	`("subway","チリチキン"),("はなまる","かけうどん(小)"),("吉野家","牛丼(並)")`

;

4.エクリプス。新規動的プロジェクトより「lunchapp」を作成する。
4_1.以下のリンクからjdbcをダウンロード(5.1.47)
https://downloads.mysql.com/archives/c-j/

5.WEB-INF/lib/の中にJDBC,META-INFの直下にcontext.xmlを配置する。(詳しくは前回の記事参照)

01<?xml version="1.0" encoding="UTF-8" ?>
02<Context>
03  <Resource
04      name="jdbc/jsp"
05      auth="Container"
06      type="javax.sql.DataSource"
07      driverClassName="com.mysql.jdbc.Driver"
08      url="jdbc:mysql://localhost:3306/lunchapp"
09      connectionProperties="autoReconnect=true;verifyServerCertificate=false;useSSL=false;requireSSL=false;useUnicode=true;characterEncoding=UTF-8;"
10      username="root"
11      password="root"
12      validationQuery="select 1"/>
13 </Context>

6.modelパッケージにLunch.javaを作成する。
●model.Lunch.java

01package model;
02 
03import java.io.Serializable;
04 
05public class Lunch  implements Serializable{
06  private int id;
07  private String name;
08  private String menu;
09  public Lunch() {}
10  public Lunch(String name,String menu) {
11    this.name=name;
12    this.menu=menu;
13  }
14  public Lunch(int id,String name,String menu) {
15    this(name,menu);
16    this.id=id;
17  }
18  public int getId() {
19    return id;
20  }
21  public void setId(int id) {
22    this.id = id;
23  }
24  public String getName() {
25    return name;
26  }
27  public void setName(String name) {
28    this.name = name;
29  }
30  public String getMenu() {
31    return menu;
32  }
33  public void setMenu(String menu) {
34    this.menu = menu;
35  }
36 
37}

「dao作成」
7.daoパッケージ内にLunchDAO.javaを作成する。
●dao.LunchDAO.java

001package dao;
002 
003import java.sql.Connection;
004import java.sql.PreparedStatement;
005import java.sql.ResultSet;
006import java.sql.SQLException;
007import java.util.ArrayList;
008import java.util.List;
009 
010import javax.naming.Context;
011import javax.naming.InitialContext;
012import javax.naming.NamingException;
013import javax.sql.DataSource;
014 
015import model.Lunch;
016 
017public class LunchDAO {
018  private Connection db;
019  private PreparedStatement ps;
020  private ResultSet rs;
021 
022  //接続共通処理
023  private void connect() throws NamingException, SQLException {
024    Context context = new InitialContext();
025    DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/jsp");
026    this.db = ds.getConnection();
027  }
028 
029  //切断共通処理
030  private void disconnect() {
031    try {
032      if (rs != null) {
033        rs.close();
034      }
035      if (ps != null) {
036        ps.close();
037      }
038      if (db != null) {
039        db.close();
040      }
041    } catch (SQLException e) {
042      e.printStackTrace();
043    }
044  }
045 
046  public List<Lunch> findAll() {
047    List<Lunch> list = new ArrayList<>();
048    try {
049      this.connect();
050      ps = db.prepareStatement("SELECT * FROM lunches");
051      rs = ps.executeQuery();
052      while (rs.next()) {
053        int id = rs.getInt("id");
054        String name = rs.getString("name");
055        String menu = rs.getString("menu");
056        Lunch l = new Lunch(id, name, menu);
057        list.add(l);
058      }
059    } catch (NamingException | SQLException e) {
060 
061      e.printStackTrace();
062    }finally {
063      this.disconnect();
064    }
065 
066    return list;
067  }
068  public void insertOne(Lunch lunch) {
069    try {
070      this.connect();
071      ps=db.prepareStatement("INSERT INTO lunches(name,menu) VALUES(?,?)");
072      ps.setString(1,lunch.getName());
073      ps.setString(2,lunch.getMenu());
074      ps.executeUpdate();
075    } catch (NamingException | SQLException e) {
076      e.printStackTrace();
077    }finally {
078      this.disconnect();
079    }
080  }
081  public Lunch findOne(int id) {
082    Lunch lunch=null;
083    try {
084      this.connect();
085      ps=db.prepareStatement("SELECT * FROM lunches WHERE id=?");
086      ps.setInt(1, id);
087      rs=ps.executeQuery();
088      if(rs.next()) {
089        String name=rs.getString("name");
090        String menu=rs.getString("menu");
091        lunch=new Lunch(id,name,menu);
092      }
093    } catch (NamingException | SQLException e) {
094      e.printStackTrace();
095    }finally {
096      this.disconnect();
097    }
098 
099    return lunch;
100  }
101  public void updateOne(Lunch lunch) {
102    try {
103      this.connect();
104      ps=db.prepareStatement("UPDATE lunches SET name=?,menu=? WHERE id=?");
105      ps.setString(1, lunch.getName());
106      ps.setString(2, lunch.getMenu());
107      ps.setInt(3, lunch.getId());
108      ps.executeUpdate();
109    } catch (NamingException | SQLException e) {
110      e.printStackTrace();
111    }finally {
112      this.disconnect();
113    }
114  }
115  public void deleteOne(int id) {
116    try {
117      this.connect();
118      ps=db.prepareStatement("DELETE FROM lunches WHERE id=?");
119      ps.setInt(1, id);
120      ps.executeUpdate();
121    } catch (NamingException | SQLException e) {
122      e.printStackTrace();
123    }finally {
124      this.disconnect();
125    }
126  }
127 
128}

8.controllerパッケージ内にRead.java(Servlet)を作成

01package controller;
02 
03import java.io.IOException;
04import java.util.List;
05 
06import javax.servlet.RequestDispatcher;
07import javax.servlet.ServletException;
08import javax.servlet.annotation.WebServlet;
09import javax.servlet.http.HttpServlet;
10import javax.servlet.http.HttpServletRequest;
11import javax.servlet.http.HttpServletResponse;
12 
13import dao.LunchDAO;
14import model.Lunch;
15 
16@WebServlet("/Read")
17public class Read extends HttpServlet {
18  private static final long serialVersionUID = 1L;
19 
20  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
21    LunchDAO dao=new LunchDAO();
22    List<Lunch> list=dao.findAll();
23    request.setAttribute("list", list);
24    RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/read.jsp");
25    rd.forward(request, response);
26  }
27 
28}

9./WEB-INF/viewフォルダにread.jspを作成
●read.jsp(/WEB-INF/view/read.jsp)

01<%@ page language="java" contentType="text/html; charset=UTF-8"
02    pageEncoding="UTF-8" import="model.*,java.util.*"%>
03<%
04List<Lunch> list=(List<Lunch>)request.getAttribute("list");
05%>
06<!DOCTYPE html>
07<html>
08<head>
09<meta charset="UTF-8">
10<title>Insert title here</title>
11</head>
12<body>
13<a href="/lunchapp/Create">新規追加</a>
14<%if(list !=null && list.size()>0){ %>
15<table border="1">
16<tr><th>id</th><th>name</th><th>menu</th><th></th></tr>
17<%for(Lunch lunch:list){ %>
18<tr>
19<td><%=lunch.getId() %></td>
20<td><%=lunch.getName() %></td>
21<td><%=lunch.getMenu() %></td>
22<td>
23<a href="/lunchapp/Update?id=<%=lunch.getId() %>">更新</a>
24<a href="/lunchapp/Delete?id=<%=lunch.getId() %>" onclick="return confirm('id=<%=lunch.getId()%>を削除してよろしいですか？');">削除</a>
25</td>
26</tr>
27<%} %>
28</table>
29<%} %>
30</body>
31</html>

[実行]
サーブレット（Read)から実行してみよう。以下のように表示されれば成功だ。

インサート

10.controllerパッケージ内にCreate.java(Servlet)を作成

01package controller;
02 
03import java.io.IOException;
04 
05import javax.servlet.RequestDispatcher;
06import javax.servlet.ServletException;
07import javax.servlet.annotation.WebServlet;
08import javax.servlet.http.HttpServlet;
09import javax.servlet.http.HttpServletRequest;
10import javax.servlet.http.HttpServletResponse;
11 
12import dao.LunchDAO;
13import model.Lunch;
14 
15/**
16 * Servlet implementation class Create
17 */
18@WebServlet("/Create")
19public class Create extends HttpServlet {
20  private static final long serialVersionUID = 1L;
21 
22   
23  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
24    RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/create.jsp");
25    rd.forward(request, response);
26  }
27 
28 
29  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
30    request.setCharacterEncoding("UTF-8");
31    String name=request.getParameter("name");
32    String menu=request.getParameter("menu");
33    Lunch lunch=new Lunch(name,menu);
34    LunchDAO ld=new LunchDAO();
35    ld.insertOne(lunch);
36 
37    response.sendRedirect("/lunchapp/Read");
38  }
39 
40}

11./WEB-INF/viewフォルダにcreate.jspを作成
●create.jsp(/WEB-INF/view/create.jsp)

01<%@ page language="java" contentType="text/html; charset=UTF-8"
02    pageEncoding="UTF-8"%>
03<!DOCTYPE html>
04<html>
05<head>
06<meta charset="UTF-8">
07<title>Insert title here</title>
08</head>
09<body>
10<form action="/lunchapp/Create" method="post">
11なまえ:<input type="text" name="name"><br>
12メニュー:<input type="text" name="menu"><br>
13<button type="submit">追加</button>
14</form>
15</body>
16</html>

アップデート

12.controllerパッケージ内にUpdate.java(Servlet)を作成

01package controller;
02 
03import java.io.IOException;
04 
05import javax.servlet.ServletException;
06import javax.servlet.annotation.WebServlet;
07import javax.servlet.http.HttpServlet;
08import javax.servlet.http.HttpServletRequest;
09import javax.servlet.http.HttpServletResponse;
10 
11import dao.LunchDAO;
12import model.Lunch;
13 
14/**
15 * Servlet implementation class Update
16 */
17@WebServlet("/Update")
18public class Update extends HttpServlet {
19  private static final long serialVersionUID = 1L;
20 
21 
22  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
23    String s_id=request.getParameter("id");
24    if(s_id==null) {
25      response.sendRedirect("/lunchapp/Read");
26    }else {
27      LunchDAO dao=new LunchDAO();
28      Lunch lunch=dao.findOne(Integer.parseInt(s_id));
29      request.setAttribute("lunch", lunch);
30      request.getRequestDispatcher("/WEB-INF/view/update.jsp").forward(request, response);
31    }
32  }
33 
34  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
35    request.setCharacterEncoding("UTF-8");
36    String id=request.getParameter("id");
37    String name=request.getParameter("name");
38    String menu=request.getParameter("menu");
39    Lunch lunch=new Lunch(Integer.parseInt(id),name,menu);
40    LunchDAO dao=new LunchDAO();
41    dao.updateOne(lunch);
42    response.sendRedirect("/lunchapp/Read");
43  }
44 
45}

13./WEB-INF/viewフォルダにupdate.jspを作成
●update.jsp(/WEB-INF/view/update.jsp)

01<%@ page language="java" contentType="text/html; charset=UTF-8"
02    pageEncoding="UTF-8" import="model.*,java.util.*"%>
03<%
04Lunch lunch=(Lunch)request.getAttribute("lunch");
05%>
06<!DOCTYPE html>
07<html>
08<head>
09<meta charset="UTF-8">
10<title>Insert title here</title>
11</head>
12<body>
13<form action="/lunchapp/Update" method="Post">
14なまえ:<input type="text" name="name" value="<%=lunch.getName()%>"><br>
15メニュー:<input type="text" name="menu" value="<%=lunch.getMenu() %>"><br>
16<input type="hidden" name="id" value="<%=lunch.getId() %>">
17<button type="submit">更新</button>
18</form>
19</body>
20</html>

DELETE

14.controllerパッケージ内にDelete.java(Servlet)を作成

01package controller;
02 
03import java.io.IOException;
04 
05import javax.servlet.ServletException;
06import javax.servlet.annotation.WebServlet;
07import javax.servlet.http.HttpServlet;
08import javax.servlet.http.HttpServletRequest;
09import javax.servlet.http.HttpServletResponse;
10 
11import dao.LunchDAO;
12 
13/**
14 * Servlet implementation class Delete
15 */
16@WebServlet("/Delete")
17public class Delete extends HttpServlet {
18  private static final long serialVersionUID = 1L;
19 
20 
21  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
22    String id=request.getParameter("id");
23    if(id !=null) {
24      LunchDAO dao=new LunchDAO();
25      dao.deleteOne(Integer.parseInt(id));
26    }
27    response.sendRedirect("/lunchapp/Read");
28  }
29 
30 
31 
32}