CRUD操作之项目练习——个人任务管理系统

大家好,我是一名入门的菜鸟,如果你不经意间翻开了我的文章,谢谢您,您的支持是我前进的动力,让我们一起加油!!
由于不是名牌大学,只是一个普普通通的专科生,所以,我想通过自己的努力来获得我想要的,我不会放弃我的梦想,我也曾幻想着我成功的时候在朋友边吹嘘,也曾想象到我失败时候潦倒的样子,幻想始终是幻想,我会努力的,加油,你一定能行

CRUD操作之项目练习——个人任务管理系统

项目需求

1. 题目描述：某公司为了对本公司的个人任务进行有效的跟踪管理，现需要开发一个个人任务管理系统。
2. 需要实现的整体功能：
   a) 查询任务
   b) 添加任务
   c) 删除任务
   d) 完成任务
   e) 整个项目以实现功能为主，不需要使用三层结构

JSP页面需求：
在网站里面,创建两个页面(TaskList.jsp、AddTask.jsp)
这是我的代码效果图：

数据库设计：

大家也不用这门麻烦我把建表语句和插入数据语句都写好了

CREATE TABLE Project
(
  --项目编号 主键,自动增长列
  ProjectID int PRIMARY KEY IDENTITY (1,1),
  --项目名称  不允许为空
  ProjectName varchar(50) NOT NULL,
)
INSERT INTO Project VALUES ('考勤管理平台')
INSERT INTO Project VALUES ('申请单管理系统')
INSERT INTO Project VALUES ('会员管理系统')

CREATE TABLE Task
(
--任务编号 主键,自动增长列
  TaskID int PRIMARY KEY IDENTITY (1,1),
--任务优先级 值为 1 或 2 或 3，分别表示：1:高 2:中 3:低
  TaskPriority int,
--任务内容 不允许为空
  Content varchar(200),
--添加时间 不允许为空
  AddTime datetime NOT NULL ,
--任务状态 1值为 1 或 2，分别表示:1:未完成 2:已完成
   TaskState int,
--完成时间 可以为空
  FinishedTime datetime null ,
--所属项目编号 外键，任务所属项目编号
  ProjectID int references Project(ProjectID),
)

INSERT INTO Task VALUES ('1','进行数据库设计','2019-11-08 00:22:43.640','2','2019-05-11 00:22:43.640','1')
INSERT INTO Task VALUES ('2','实现审批申请单','2019-11-08 00:22:43.640','1','','2')
INSERT INTO Task VALUES ('3','测试整个系统','2019-11-08 00:22:43.640','1','','3')
INSERT INTO Task VALUES ('4','实现登录','2019-11-08 00:22:43.640','2','2019-05-11 00:22:43.640','1')

下面我来简述一下这个项目的具体功能

• 查询
  *a) 当页面第一次加载时，通过 GridView 来显示所有的任务列表，通过DropDownList 来绑定所有的项目列表。
  *b) 在 GridView 中，需要显示列“所属项目”、“任务优先级”、“任务内容”、“任务添加时间”、“任务状态”、“任务完成时间”、“操作”。
  *c) 在 GridView 中，任务优先级要显示为“高”、“中”、“低”，不能显示为数字。
  *d)在 GridView 中，任务状态要显示为“已完成”、“未完成”，不能显示为数字。
  *e)当任务状态为“已完成”时，则“完成任务”按钮不可见，同时需要显示完成时间；当任务状态为“未完成”时，则“完成任务”按钮可见，同时完成时间为空(因为本任务还未完成，所以完成时间为空)。
  *f) 当单击“查询”按钮时，根据“所属项目”、“任务内容”、“任务状态”进行复合查询。
  *g) 当“所属项目”下拉列表选中项为“全部项目”、“任务内容”文本框为空、“任务状态”选中项为“全部”时，将查询所有任务列表；其它情况则按条件进行复合查询。

• 删除
  *点击删除按钮，跳转到DeleteServlet进行数据删除，然后跳转到TaskListServlet重新进行数据查询，就实现了刷新功能

• 添加
  *a) 添加时要进行验证，所属项目要选择项目，任务内容不能为空。
  *b) 当任务添加成功之后，跳转到 TaskList.aspx 页面。
  *c)当单击“返回到列表”按钮时，跳转到 TaskList.aspx 页面。

• 完成任务按钮
  *当单击“完成任务”按钮时，把该行对应任务的状态修改成“已完成”即修改为“2”，同时也需要修改任务完成时间为系统的当前时间。这需要用到更新

来看具体实现代码吧

JavaBean

• Project

package com.hnpi.domain;

public class Project {
    private Integer ProjectID;
    private String ProjectName;

    public Integer getProjectID() {
        return ProjectID;
    }

    public void setProjectID(Integer projectID) {
        ProjectID = projectID;
    }

    public String getProjectName() {
        return ProjectName;
    }

    public void setProjectName(String projectName) {
        ProjectName = projectName;
    }

    public Project(Integer projectID, String projectName) {
        ProjectID = projectID;
        ProjectName = projectName;
    }

    public Project() {
        super();
    }
}

• Task

package com.hnpi.domain;

import java.sql.Timestamp;

public class Task {
    private Integer TaskID;
    private Integer TaskPriority;
    private String Content;
    private Timestamp AddTime;
    private Integer TaskState;
    private Timestamp FinishedTime;
    private Integer ProjectID ;

    public Integer getTaskID() {
        return TaskID;
    }

    public void setTaskID(Integer taskID) {
        TaskID = taskID;
    }

    public Integer getTaskPriority() {
        return TaskPriority;
    }

    public void setTaskPriority(Integer taskPriority) {
        TaskPriority = taskPriority;
    }

    public String getContent() {
        return Content;
    }

    public void setContent(String content) {
        Content = content;
    }

    public Timestamp getAddTime() {
        return AddTime;
    }

    public void setAddTime(Timestamp addTime) {
        AddTime = addTime;
    }

    public Integer getTaskState() {
        return TaskState;
    }

    public void setTaskState(Integer taskState) {
        TaskState = taskState;
    }

    public Timestamp getFinishedTime() {
        return FinishedTime;
    }

    public void setFinishedTime(Timestamp finishedTime) {
        FinishedTime = finishedTime;
    }

    public Integer getProjectID() {
        return ProjectID;
    }

    public void setProjectID(Integer projectID) {
        ProjectID = projectID;
    }

    public Task(Integer taskID, Integer taskPriority, String content, Timestamp addTime, Integer taskState, Timestamp finishedTime, Integer projectID) {
        TaskID = taskID;
        TaskPriority = taskPriority;
        Content = content;
        AddTime = addTime;
        TaskState = taskState;
        FinishedTime = finishedTime;
        ProjectID = projectID;
    }

    public Task() {
        super();
    }
}

Servlet

• AddTaskServlet

package com.hnpi.servlet;

import com.hnpi.domain.Project;
import com.hnpi.util.DBUtil;

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 java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

@WebServlet(value = "/AddTaskServlet")
public class AddTaskServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");

        String project = request.getParameter("projectID");
        String taskPriority = request.getParameter("TaskPriority");
        String content = request.getParameter("content");

        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "insert into Task(TaskPriority,Content,AddTime,TaskState,FinishedTime,ProjectID)values(?,?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(taskPriority));
            ps.setString(2,content);
            ps.setTimestamp(3,new Timestamp(System.currentTimeMillis()));
            ps.setInt(4,1);
            ps.setString(5,null);
            ps.setInt(6,Integer.parseInt(project));
            ps.executeUpdate();
            response.sendRedirect(request.getContextPath()+"/TaskListServlet");
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.CloseConn(conn,ps,rs);
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request,response);
    }
}

• DeleteServlet

package com.hnpi.servlet;

import com.hnpi.util.DBUtil;

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 java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

@WebServlet(value = "/DeleteServlet")
public class DeleteServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            request.setCharacterEncoding("utf-8");
            String id = request.getParameter("id");
        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "delete from Task where TaskID = ?";
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
            response.sendRedirect(request.getContextPath()+"/TaskListServlet");
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.CloseConn(conn,ps,rs);
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            this.doPost(request,response);
    }
}

• TaskListServlet

package com.hnpi.servlet;

import com.hnpi.domain.Project;
import com.hnpi.domain.Task;
import com.hnpi.util.DBUtil;

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 javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@WebServlet(value = "/TaskListServlet")
public class TaskListServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            request.setCharacterEncoding("utf-8");

            String ProjectID = request.getParameter("ProjectID");
            String Content = request.getParameter("Content");
        String TaskState = request.getParameter("TaskState");

        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<Project>projectList = new ArrayList<Project>();
        List<Task>TaskList = new ArrayList<Task>();
        Map<Integer,String>projectMap = new HashMap<Integer,String>();
        List<Object>params = new ArrayList<Object>();
        String sql = "select * from Task where 1=1";

        boolean ProjectIDFlag = (ProjectID != null && !"".equals(ProjectID) && !"*".equals(ProjectID));
        boolean ContentFlag = (Content != null&& !"".equals(Content));
        boolean TaskStateFlag = (TaskState != null && !"".equals(TaskState) && !"*".equals(TaskState));
        if(ProjectIDFlag){
            sql += "and ProjectID = ?";
            params.add(Integer.parseInt(ProjectID));
        }
        if(ContentFlag){
            sql += "and Content like ?";
            params.add("%" + Content +"%");
        }
        if(TaskStateFlag){
            sql += "and TaskState = ?";
            params.add(Integer.parseInt(TaskState));
        }
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 1; i <= params.size();i++){
                Object obj = params.get(i-1);
                if(obj instanceof Integer){
                    ps.setInt(i,(Integer)obj);
                }
                if(obj instanceof String){
                    ps.setString(i,(String)obj);
                }
            }
            rs = ps.executeQuery();
            while(rs.next()){
                Task task = new Task(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getTimestamp(4),rs.getInt(5),rs.getTimestamp(6),rs.getInt(7));
                TaskList.add(task);
            }

            String sql1 = "select * from Project";
            ps = conn.prepareStatement(sql1);
            rs = ps.executeQuery();
            while(rs.next()){
                Project project = new Project(rs.getInt(1),rs.getString(2));
                projectList.add(project);
                projectMap.put(project.getProjectID(),project.getProjectName());
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.CloseConn(conn,ps,rs);
        }

        HttpSession session = request.getSession();
        session.setAttribute("TaskList",TaskList);
        session.setAttribute("projectList",projectList);
        session.setAttribute("projectMap",projectMap);
        response.sendRedirect(request.getContextPath()+"/TaskList.jsp");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request,response);
    }
}

• UpdateServlet

package com.hnpi.servlet;

import com.hnpi.util.DBUtil;

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 java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

@WebServlet(value = "/UpdateServlet")
public class UpdateServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            request.setCharacterEncoding("utf-8");
            String id = request.getParameter("id");

            Connection conn = DBUtil.getConn();
            PreparedStatement ps = null;
            ResultSet rs = null;
            String sql = "update Task set TaskState = ?,FinishedTime = ? where TaskID = ?";
            try {
                ps = conn.prepareStatement(sql);
                ps.setInt(1,2);
                ps.setTimestamp(2,new Timestamp(System.currentTimeMillis()));
                ps.setInt(3,Integer.parseInt(id));
                ps.executeUpdate();
                response.sendRedirect(request.getContextPath()+"/TaskListServlet");
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                DBUtil.CloseConn(conn,ps,rs);
            }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            this.doPost(request,response);
    }
}

JSP

• AddTask

<%@ page import="java.util.List" %>
<%@ page import="com.hnpi.domain.Project" %>
<%@ page import="java.util.ArrayList" %><%--
  Created by IntelliJ IDEA.
  User: Lenovo
  Date: 2019/11/9
  Time: 22:02
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加页面</title>
</head>
<body>
<center>
<form action="${pageContext.request.contextPath}/AddTaskServlet" method="post">
<table>
    <tr>
        <td>添加任务</td>
    </tr>
    <tr>
        <td>所属项目</td>
        <td>
            <select name="projectID">
                <%
                    List<Project> list = (ArrayList)session.getAttribute("projectList");
                    for(Project project:list){
                        %>
                <option value="<%=project.getProjectID()%>"><%=project.getProjectName()%></option>
                <%
                    }
                %>
            </select>
        </td>
    </tr>
    <tr>
        <td>任务优先级</td>
        <td>
            <input type="radio" name="TaskPriority" checked value="1">高
            <input type="radio" name="TaskPriority" value="2">中
            <input type="radio" name="TaskPriority" value="3">低
        </td>
    </tr>
    <tr>
        <td>任务内容</td>
        <td>
            <textarea rows="5" cols="10" name="content"></textarea>
        </td>
    </tr>
    <tr>
        <td>
            <input type="submit" value="添加">
            <button>
                <a href="TaskList.jsp">返回</a>
            </button>
        </td>
    </tr>
</table>
</form>
</center>
</body>
</html>

• index

<%--
  Created by IntelliJ IDEA.
  User: Lenovo
  Date: 2019/11/10
  Time: 11:07
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>跳转页面</title>
</head>
<body>
<center>
<h1>
    <a href="/TaskListServlet">查询所有用户信息</a>
</h1>

</center>
</body>
</html>

• TaskList

<%@ page import="java.util.List" %>
<%@ page import="com.hnpi.domain.Task" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.HashMap" %>
<%@ page import="com.hnpi.domain.Project" %><%--
  Created by IntelliJ IDEA.
  User: Lenovo
  Date: 2019/11/9
  Time: 21:45
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>显示页面</title>
    <style>
        table tr td{
            border-right: 1px solid black;
            font-family: 楷体;
        }
        thead{
            background-color: brown;
            color: white;
        }
        tbody{
            color: cornflowerblue;
        }
    </style>
</head>
<body>
<center>
    <form action="/TaskListServlet" method="post">
<div>
    所属项目:
    <select name="ProjectID">
        <option value="*" selected>全部项目</option>
        <%
            List<Project>list = (ArrayList)session.getAttribute("projectList");
            for (Project project :list){
                %>
        <option value="<%=project.getProjectID()%>"><%=project.getProjectName()%></option>
        <%
            }
        %>
    </select>
    任务内容:<input type="text" name="Content">
    任务状态:
    <input type="radio" name="TaskState" value="1">未完成
    <input type="radio" name="TaskState" value="2">已完成
    <input type="radio" name="TaskState" value="*" checked>全部
    <input type="submit" value="查询">
    <button>
        <a href="AddTask.jsp">添加</a>
    </button>
</div>
    </form>
    <br>
<table>
    <thead>
    <tr>
        <td>所属项目</td>
        <td>任务优先级</td>
        <td>任务内容</td>
        <td>任务添加时间</td>
        <td>任务状态</td>
        <td>任务完成时间</td>
        <td>操作</td>
    </tr>
    </thead>
    <tbody>

        <%
            List<Task>TaskList = (ArrayList)session.getAttribute("TaskList");
            Map<Integer,String>map = (HashMap)session.getAttribute("projectMap");
            for(Task task:TaskList){
                %>
        <tr>
            <td><%=map.get(task.getProjectID())%></td>
            <td><%
                if(task.getTaskPriority() == 1){
                    %>
                高
                <%
                }
                    if(task.getTaskPriority() == 2){
                %>
                中
                <%
                    }
                    if(task.getTaskPriority() == 3){
                %>
                低
                <%
                    }

            %></td>
            <td><%=task.getContent()%></td>
            <td><%=task.getAddTime()%>%</td>
            <td><%
                if(task.getTaskState() == 1){
                    %>
                未完成
                <%
                }else if(task.getTaskState() == 2){
                %>
                已完成
                <%
                    }
            %></td>
            <td><%=task.getFinishedTime()%></td>
            <td>
                <%
                    if(task.getTaskState() == 1){
                %>
                <button>
                    <a href="${pageContext.request.contextPath}/UpdateServlet?id=<%=task.getTaskID()%>">完成任务</a>
                </button>
                <button>
                    <a href="${pageContext.request.contextPath}/DeleteServlet?id=<%=task.getTaskID()%>">删除</a>
                </button>
                <%
                    }else{
                        %>
                <button>
                    <a href="${pageContext.request.contextPath}/DeleteServlet?id=<%=task.getTaskID()%>">删除</a>
                </button>
                <%
                    }
                %>
            </td>
        <%
            }
        %>

    </tr>
    </tbody>
</table>
</center>
</body>
</html>

非常感谢读者能认真看完我做的这一个项目，希望能给你带来些许灵感！！
我会继续加油！！