JSP & Servlet-9日目(DAOを使ったCRUDシステム)

DAOを使って基本的なCRUDを実現しよう。今回は処理の一つ一つがわかりやすいようにファイルを分割し、装飾的要素もつけないで行う。

[作成]
１．MySQLでデータベース[personapp]を作成する。

CREATE DATABASE personapp
DEFAULT CHARACTER SET utf8;

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

CREATE TABLE persons(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30),
 age INT
 );

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

INSERT INTO persons(name,age)VALUES
 ("John",30),("Paul",28),("George",27),("Ringo",26);

4.エクリプス。新規動的プロジェクトより「joytas9」を作成する。
5.WEB-INF/lib/の中にJDBC,META-INFの直下にcontext.xmlを配置する。(詳しくは前回の記事参照)

<?xml version="1.0" encoding="UTF-8" ?>
<Context>
  <Resource
      name="jdbc/jsp"
      auth="Container"
      type="javax.sql.DataSource"
      driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/personapp"
      connectionProperties="autoReconnect=true;verifyServerCertificate=false;useSSL=false;requireSSL=false;useUnicode=true;characterEncoding=UTF-8;"
      username="root"
      password="root"
      validationQuery="select 1"/>
 </Context>

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

package model;

import java.io.Serializable;

public class Person implements Serializable{
	private int id;
	private String name;
	private int age;
	public Person(){}
	public Person(String name,int age){
		this.name=name;
		this.age=age;
	}
	public Person(int id,String name,int age){
		this(name,age);
		this.id=id;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	

}

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

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import model.Person;

public class PersonDAO {
	private Connection db;
	private PreparedStatement ps;
	private ResultSet rs;
	//接続共通処理
	private void connect() throws NamingException, SQLException {
		Context context = new InitialContext();
		DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/jsp");
		this.db = ds.getConnection();
	}
	//切断共通処理
	private void disconnect() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (db != null) {
				db.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List<Person> findAll(){
		List<Person> list=new ArrayList<>();
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons");
			rs=ps.executeQuery();
			while(rs.next()){
				int id=rs.getInt("id");
				String name=rs.getString("name");
				int age=rs.getInt("age");
				list.add(new Person(id,name,age));
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return list;
	}
}

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

package controller;

import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.PersonDAO;
import model.Person;

@WebServlet("/Read")
public class Read extends HttpServlet {
	private static final long serialVersionUID = 1L;
         
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PersonDAO dao=new PersonDAO();
		List<Person> list=dao.findAll();
		request.setAttribute("list", list);
		RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/read.jsp");
		rd.forward(request, response);
	}
}

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

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" import="model.*,java.util.*"%>
<%
List<Person> list=(List<Person>)request.getAttribute("list");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8"/>
<title>Joytas9</title>
</head>
<body>
<a href="/joytas9/Create">新規登録</a>
<%if(list != null && list.size() > 0){ %>

<table border="1">
<%for(Person p:list){ %>

<tr>

<td><%=p.getId() %></td>


<td><%=p.getName() %></td>


<td><%=p.getAge() %></td>


<td><a href="/joytas9/Update?id=<%=p.getId()%>">更新</a>
<a href="/joytas9/Delete?id=<%=p.getId()%>" onclick="return confirm('id=<%=p.getId()%>を削除してよろしいですか？');">削除</a></td>

</tr>

<%} %>
</table>

<%} %>
</body>
</html>

10.Read.javaをサーバーで実行する。(以下のようになることを確認)

「Create作成」
11.controllerパッケージ内にCreate.javaを作成。
●controller.Create.java

package controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/Create")
public class Create extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/create.jsp");
		rd.forward(request, response);
	}

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
	}

}

12.WEB−INF／viewフォルダ内にcreate.jspを作成
●create.jsp(WEB-INF/view/create.jsp)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8"/>
<title>Joytas9</title>
</head>
<body>

<form action="/joytas9/Create" method="post">
名前:<input type="text" name="name"><br>
年齢:<input type="number" name="age"><br>
<button type="submit">登録</button>
</form>

</body>
</html>

13.Create.javaを実行してみて以下のようになることを確認する。

14.フォームから入力されたデータをDBにインサートしよう。まずはPersonDAO.javaに追記する。
●dao.PersonDAO.java(62~74行追記)

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import model.Person;

public class PersonDAO {
	private Connection db;
	private PreparedStatement ps;
	private ResultSet rs;
	//接続共通処理
	private void connect() throws NamingException, SQLException {
		Context context = new InitialContext();
		DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/jsp");
		this.db = ds.getConnection();
	}
	//切断共通処理
	private void disconnect() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (db != null) {
				db.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List<Person> findAll(){
		List<Person> list=new ArrayList<>();
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons");
			rs=ps.executeQuery();
			while(rs.next()){
				int id=rs.getInt("id");
				String name=rs.getString("name");
				int age=rs.getInt("age");
				list.add(new Person(id,name,age));
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return list;
	}
	public void insertOne(Person person){
		try {
			this.connect();
			ps=db.prepareStatement("INSERT INTO persons(name,age) VALUES(?,?)");
			ps.setString(1, person.getName());
			ps.setInt(2, person.getAge());
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {	
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
	}
}

15.controller.Create.javaのdoPost内に追記する。

package controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.PersonDAO;
import model.Person;

@WebServlet("/Create")
public class Create extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/create.jsp");
		rd.forward(request, response);
	}

	//doPost内を追記
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		String name=request.getParameter("name");
		String age=request.getParameter("age");
		Person person=new Person(name,Integer.parseInt(age));
		PersonDAO dao=new PersonDAO();
		dao.insertOne(person);
		
		response.sendRedirect("/joytas9/Read");
	}

}

16.Create.javaから実行し、フォームにPeter,33と入れて登録ボタンを押してみよう。以下のように表示されれば成功だ。

「Update作成」
17.続いて更新処理を作っていこう。まずはPersonDAO.javaにidをもとに1件のデータを取得するfindOne（）を追記する。
●dao.PersonDAO.java

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import model.Person;

public class PersonDAO {
	private Connection db;
	private PreparedStatement ps;
	private ResultSet rs;
	//接続共通処理
	private void connect() throws NamingException, SQLException {
		Context context = new InitialContext();
		DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/jsp");
		this.db = ds.getConnection();
	}
	//切断共通処理
	private void disconnect() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (db != null) {
				db.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List<Person> findAll(){
		List<Person> list=new ArrayList<>();
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons");
			rs=ps.executeQuery();
			while(rs.next()){
				int id=rs.getInt("id");
				String name=rs.getString("name");
				int age=rs.getInt("age");
				list.add(new Person(id,name,age));
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return list;
	}
	public void insertOne(Person person){
		try {
			this.connect();
			ps=db.prepareStatement("INSERT INTO persons(name,age) VALUES(?,?)");
			ps.setString(1, person.getName());
			ps.setInt(2, person.getAge());
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {	
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
	}
//ここを追記
	public Person findOne(int id){
		Person person=null;
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons WHERE id=?");
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()){
				String name=rs.getString("name");
				int age=rs.getInt("age");
				person=new Person(id,name,age);
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return person;
	}
	
}

18.controller内にUpdate.javaを以下のように記述する。
●controller.Update.java

package controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.PersonDAO;
import model.Person;

@WebServlet("/Update")
public class Update extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String s_id=request.getParameter("id");
		if(s_id == null){
			response.sendRedirect("/joytas9/Read");
		}else{
			PersonDAO dao=new PersonDAO();
			Person person=dao.findOne(Integer.parseInt(s_id));
			request.setAttribute("person", person);
			RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/update.jsp");
			rd.forward(request, response);
		}
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
	}

}

19.WEB-INF/viewの中にupdate.jspを以下のように作成する。
●update.jsp(WEB-INF/view/update.jsp)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="model.*"%>
<%
Person person=(Person)request.getAttribute("person");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8"/>
<title>Joytas9</title>
</head>
<body>
<form action="/joytas9/Update" method="post">
名前:<input type="text" name="name" value="<%=person.getName() %>"><br>
年齢:<input type="number" name="age" value="<%=person.getAge() %>"><br>
<input type="hidden" name="id" value="<%=person.getId() %>"><br>
<button type="submit">更新</button>
</form>
</body>
</html>

20.Read.javaから実行し、任意のデータの更新リンクを押してみよう。以下のように情報がフォームに反映されれば成功だ。

21.更新ボタンを押したらDBの情報を更新する処理を作る。まずはPersonDAOに追記 (updateOne())
●controller.PersonDAO.java

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import model.Person;

public class PersonDAO {
	private Connection db;
	private PreparedStatement ps;
	private ResultSet rs;
	//接続共通処理
	private void connect() throws NamingException, SQLException {
		Context context = new InitialContext();
		DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/jsp");
		this.db = ds.getConnection();
	}
	//切断共通処理
	private void disconnect() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (db != null) {
				db.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List<Person> findAll(){
		List<Person> list=new ArrayList<>();
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons");
			rs=ps.executeQuery();
			while(rs.next()){
				int id=rs.getInt("id");
				String name=rs.getString("name");
				int age=rs.getInt("age");
				list.add(new Person(id,name,age));
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return list;
	}
	public void insertOne(Person person){
		try {
			this.connect();
			ps=db.prepareStatement("INSERT INTO persons(name,age) VALUES(?,?)");
			ps.setString(1, person.getName());
			ps.setInt(2, person.getAge());
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {	
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
	}
	public Person findOne(int id){
		Person person=null;
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons WHERE id=?");
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()){
				String name=rs.getString("name");
				int age=rs.getInt("age");
				person=new Person(id,name,age);
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return person;
	}
	//ここを追記
	public void updateOne(Person person){
		try {
			this.connect();
			ps=db.prepareStatement("UPDATE persons SET name=?,age=? WHERE id=?");
			ps.setString(1, person.getName());
			ps.setInt(2, person.getAge());
			ps.setInt(3, person.getId());
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
	}
}

22.controller.Update.javaにpostの処理を追記する。
●controller.UPdate.java

package controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.PersonDAO;
import model.Person;

@WebServlet("/Update")
public class Update extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String s_id=request.getParameter("id");
		if(s_id == null){
			response.sendRedirect("/joytas9/Read");
		}else{
			PersonDAO dao=new PersonDAO();
			Person person=dao.findOne(Integer.parseInt(s_id));
			request.setAttribute("person", person);
			RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/update.jsp");
			rd.forward(request, response);
		}
	}
	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//以下を追記
		request.setCharacterEncoding("UTF-8");
		String name=request.getParameter("name");
		String age=request.getParameter("age");
		String id=request.getParameter("id");
		Person person=new Person(Integer.parseInt(id),name,Integer.parseInt(age));
		PersonDAO dao=new PersonDAO();
		dao.updateOne(person);
		
		response.sendRedirect("/joytas9/Read");
	}

}

23.情報を更新してみよう。以下のように更新が反映されれば成功だ。（Peterの年齢を34に変更した)

「Delete作成」
24.最後に削除の処理を作る。まずはPersonDAOに追記。(deleteOne())
●dao.PersonDAO.java

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import model.Person;

public class PersonDAO {
	private Connection db;
	private PreparedStatement ps;
	private ResultSet rs;
	//接続共通処理
	private void connect() throws NamingException, SQLException {
		Context context = new InitialContext();
		DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/jsp");
		this.db = ds.getConnection();
	}
	//切断共通処理
	private void disconnect() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (db != null) {
				db.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List<Person> findAll(){
		List<Person> list=new ArrayList<>();
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons");
			rs=ps.executeQuery();
			while(rs.next()){
				int id=rs.getInt("id");
				String name=rs.getString("name");
				int age=rs.getInt("age");
				list.add(new Person(id,name,age));
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return list;
	}
	public void insertOne(Person person){
		try {
			this.connect();
			ps=db.prepareStatement("INSERT INTO persons(name,age) VALUES(?,?)");
			ps.setString(1, person.getName());
			ps.setInt(2, person.getAge());
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {	
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
	}
	public Person findOne(int id){
		Person person=null;
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM persons WHERE id=?");
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()){
				String name=rs.getString("name");
				int age=rs.getInt("age");
				person=new Person(id,name,age);
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
		return person;
	}
	
	public void updateOne(Person person){
		try {
			this.connect();
			ps=db.prepareStatement("UPDATE persons SET name=?,age=? WHERE id=?");
			ps.setString(1, person.getName());
			ps.setInt(2, person.getAge());
			ps.setInt(3, person.getId());
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
	}
	//ここを追記
	public void deleteOne(int id){
		try {
			this.connect();
			ps=db.prepareStatement("DELETE FROM persons WHERE id=?");
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally{
			this.disconnect();
		}
	}
}

25.controller内にDelete.java(Servlet)を作成。
●controller.Delete.java

package controller;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.PersonDAO;

@WebServlet("/Delete")
public class Delete extends HttpServlet {
	private static final long serialVersionUID = 1L;
 
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String s_id=request.getParameter("id");
		if(s_id != null){
			PersonDAO dao=new PersonDAO();
			dao.deleteOne(Integer.parseInt(s_id));
		}
		response.sendRedirect("/joytas9/Read");
	}
}

26.Peterを削除してみよう。ダイアログが表示された後、OKで削除されれば成功だ。