JSP & Servlet(MySQLとの連携Todoアプリ)

JSP&Servlet
2018.08.30

DAOを使って基本的なCRUDを実現しよう。今回はTodoアプリを作成する。(作成の流れはここを参照するとよい。）

［実行例]
●最初の画面

●新規を押した

●タイトルと重要度(1~5)を入力し登録ボタンを押すと一覧画面にTodoが表示される

●３件ほど登録した状態。重要度が高い順に表示される。

●水槽の掃除の隣の更新リンクをクリック

●重要度を4にして更新リンクを押した。

●新聞をかうの削除ボタンを押すとモーダルウィンドウで確認が開く

●ＯＫを押すと削除された

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

CREATE DATABASE todoapp
DEFAULT CHARACTER SET utf8;

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

CREATE TABLE todos(
 id INT PRIMARY KEY AUTO_INCREMENT,
 title VARCHAR(30),
 importance INT
 );

3.エクリプス。新規動的プロジェクトより「todoapp」を作成する。
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/todoapp"
      connectionProperties="autoReconnect=true;verifyServerCertificate=false;useSSL=false;requireSSL=false;useUnicode=true;characterEncoding=UTF-8;"
      username="root"
      password="root"
      validationQuery="select 1"/>
 </Context>

6.modelパッケージ
●model.Todo.java

package model;

import java.io.Serializable;

public class Todo implements Serializable{
	private int id;
	private String title;
	private int importance;
	public Todo() {}
	public Todo(String title,int importance) {
		this.title=title;
		this.importance=importance;
	}
	public Todo(int id, String title, int importance) {
		this(title,importance);
		this.id = id;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public int getImportance() {
		return importance;
	}
	public void setImportance(int importance) {
		this.importance = importance;
	}
	public String getStar() {
		String stars="";
		for(int i=0;i<this.importance;i++) {
			stars+="★";
		}
		return stars;
	}



}

7.daoパッケージ
●dao.TodoDAO.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.Todo;

public class TodoDAO {
	private Connection db; //todoappデータベースに接続されたコネクション
	private PreparedStatement ps; //sql文を保持する変数
	private ResultSet rs; //結果セット(SQL文を実行した結果の集合)を保持

	//接続処理
	private void connect() throws NamingException, SQLException {
		Context context = new InitialContext();
		DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/jsp");
		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<Todo> findAll(){
		List<Todo> list=new ArrayList<>();
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM todos ORDER BY importance DESC");
			rs=ps.executeQuery();
			while(rs.next()) {
				int id=rs.getInt("id");
				String title=rs.getString("title");
				int importance=rs.getInt("importance");
				list.add(new Todo(id,title,importance));
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally {
			this.disconnect();
		}
		return list;
	}
	public void insertOne(Todo todo) {
		try {
			this.connect();
			ps=db.prepareStatement("INSERT INTO todos(title,importance) VALUES(?,?)");
			ps.setString(1, todo.getTitle());
			ps.setInt(2, todo.getImportance());
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally {
			this.disconnect();
		}
	}
	public Todo findOne(int id) {
		Todo todo=null;
		try {
			this.connect();
			ps=db.prepareStatement("SELECT * FROM todos WHERE id=?");
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				String title=rs.getString("title");
				int importance=rs.getInt("importance");
				todo=new Todo(id,title,importance);
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally {
			this.disconnect();
		}
		return todo;
	}
	public void updateOne(Todo todo) {
		try {
			this.connect();
			ps=db.prepareStatement("UPDATE todos SET title=?,importance=? WHERE id=?");
			ps.setString(1, todo.getTitle());
			ps.setInt(2, todo.getImportance());
			ps.setInt(3, todo.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 todos WHERE id=?");
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}finally {
			this.disconnect();
		}
	}
}

8.controllerパッケージ
●controller.Read.java

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.TodoDAO;
import model.Todo;


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

	

}

●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;

import dao.TodoDAO;
import model.Todo;

/**
 * Servlet implementation class Create
 */
@WebServlet("/Create")
public class Create extends HttpServlet {
	private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Create() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/create.jsp");
		rd.forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		String title=request.getParameter("title");
		String importance=request.getParameter("importance");
		Todo todo=new Todo(title,Integer.parseInt(importance));
		TodoDAO dao=new TodoDAO();
		dao.insertOne(todo);

		response.sendRedirect("/todoapp/Read");
	}

}

●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.TodoDAO;
import model.Todo;

/**
 * Servlet implementation class Update
 */
@WebServlet("/Update")
public class Update extends HttpServlet {
	private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Update() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String s_id=request.getParameter("id");
		if(s_id == null) {
			response.sendRedirect("/todoapp/Read");
		}else {
			TodoDAO dao=new TodoDAO();
			Todo todo =dao.findOne(Integer.parseInt(s_id));
			request.setAttribute("todo", todo);
			RequestDispatcher rd=request.getRequestDispatcher("/WEB-INF/view/update.jsp");
			rd.forward(request, response);
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		String title=request.getParameter("title");
		String importance=request.getParameter("importance");
		String id=request.getParameter("id");
		Todo todo=new Todo(Integer.parseInt(id),title,Integer.parseInt(importance));
		TodoDAO dao=new TodoDAO();
		dao.updateOne(todo);

		response.sendRedirect("/todoapp/Read");

	}

}

●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.TodoDAO;

/**
 * Servlet implementation class Delete
 */
@WebServlet("/Delete")
public class Delete extends HttpServlet {
	private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Delete() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String s_id=request.getParameter("id");
		if(s_id !=null) {
			TodoDAO dao=new TodoDAO();
			dao.deleteOne(Integer.parseInt(s_id));
		}
		response.sendRedirect("/todoapp/Read");
	}



}

9.view(/WEB-INF/view/の中に以下のjspファイルを配置する)

●read.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="model.Todo,java.util.*"%>
 <%
 List<Todo> list=(List<Todo>)request.getAttribute("list");
 %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>TodoApp</title>
</head>
<body>
<a href="/todoapp/Create">新規</a>
<% if(list !=null && list.size() >0){ %>
<table>
<%for(Todo t:list){ %>
<tr>
<td>●<%=t.getTitle() %></td><td><%=t.getStar() %></td>
<td><a href="/todoapp/Update?id=<%=t.getId()%>">更新</a>
<a href="/todoapp/Delete?id=<%=t.getId()%>" onclick="return confirm('[<%=t.getTitle()%>]を削除してよろしいですか？');">削除</a>
</td>
</tr>
<%} %>
</table>
<%}else{ %>
<p>Todoはまだありません</p>
<%} %>
</body>
</html>

●create.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>TodoApp</title>
</head>
<body>
<form action="/todoapp/Create" method="post">
Title:<input type="text" name="title"><br>
重要度:<input type="number" name="importance" min="1" max="5" value="3"><br>
<button type="submit">登録</button>
</form>
</body>
</html>

●update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="model.Todo"%>
<%
Todo todo=(Todo)request.getAttribute("todo");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>TodoApp</title>
</head>
<body>
<form action="/todoapp/Update" method="post">
Title:<input type="text" name="title" value="<%=todo.getTitle() %>"><br>
重要度:<input type="number" name="importance" value="<%=todo.getImportance() %>"><br>
<input type="hidden" name="id" value="<%=todo.getId() %>"><br>
<button type="submit">更新</button>

</form>
</body>
</html>
スポンサーリンク

コメント

タイトルとURLをコピーしました