社区人员登记管理系统_社区信息登记系统

社区人员登记管理系统

模拟设计一个使用于社区管理来人员登记的系统，采取Browser/Client结构，基于MVC设计开发

1. 项目准备

1.1 环境搭建

• 开发工具使用Intellij IDEA 2021.3.1
• Java:version 17.0
• Mysql:Version 5.7
• Tomcat:9.0.58
• 使用JDBC驱动 mysql-connector-java.5.1.34.jar 和 javascript框架 Jquery包

1.2 数据库设计

使用Navicat 12 可视化编辑/新建数据库与表

新建一个数据库db_commnuicate 字符集采用utf-8

1.2.1 表设计

设计新建一张表tb_person里面表设计如下:

1.2.2 添加测试数据

向tb_person表中添加测试数据以备测试使用

测试sql语句代码如下:

INSERT INTO `tb_person` VALUES (1, 1, '喜羊羊', '男', '1992-05-06', '430405199205061234', '长沙市岳麓区街道123号', '1', '2022-03-20 22:09:07');
INSERT INTO `tb_person` VALUES (2, 2, '王喜顺', '男', '1998-07-18', '390304199807184321', '淄博市淄川区街道321号', '0', '2022-03-04 22:11:38');
INSERT INTO `tb_person` VALUES (3, 3, '刘二牛', '女', '1998-11-11', '43040519981111321X', '长沙市雨花区街道778号', '1', '2022-03-21 22:12:43');

1
2
3
4

1.3 IDEA项目建立

建立一个新的Javaproject项目—勾选WebApplication 4.0框架支持变成一个动态web项目

下一步

下一步 添加WebApplication框架支持

finshed! 项目结构如下

1.3.1 创建包

被Tomcat服务器所识别并且编译代码写在src文件里面，为了使用MVC方式开发时明确开发思路，必须将包与包之间的结构清清楚楚的分析到位,我们要创建以下包

• Controller (控制层)
• Dao (业务处理层)
• Model (数据模型层)
• Utils (工具类)
• View (视图层)

1.3.2 配置Tomcat服务器

点击

Add Configuration添加Tomcat服务器选择Local

并在Deployment里添加artifact 并且将Application context 虚拟资源目录修改

1.3.2.1 向项目依赖中添加Tomcat

在编写Serlvet类时不正确添加依赖会引起报错

2. 编码开发

2.1 Dao层与Utils工具类的实现

2.2.1 BaseDao类

完成Dao层里的BaseDao类，BaseDao类主要完成Dao类里基本的业务处理操作，其他Dao类通过继承来调用以下方法

代码如下:

package Dao;

import Utils.JDBCconnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    //初始化类时将数据库连接赋予变量conn
    static {
        if (conn != null ){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
       conn = JDBCconnection.getConnection();
    }

    //执行基础查询语句
    public static ResultSet executeQuery(String prepareSql , Object[]... param){
        try {
            ps = conn.prepareStatement(prepareSql);
            if (param != null){
                for (int i = 0 ; i < param.length ; i++){
                    ps.setObject(i+1,param[i]);
                }
            }
            rs = ps.executeQuery();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return rs;
    }

    //执行基础的增、删、改功能
    public static int executeSQL(String prepareSql, Object[]... param){
        int j = 0;
        try {
            ps = conn.prepareStatement(prepareSql);
            if (param != null){
                for (int i = 0 ; i < param.length ; i++){
                    ps.setObject(i+1,param[i]);
                }
            }
            j = ps.executeUpdate();
            return j;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return j;

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

2.2.2 JDBCconnection类

完成Utils工具类里的JDBC连接类，完成底层连接数据库的操作需要在依赖中添加mysql-connector-java 5.1.34.jar包 ，并且在WEB-INF-lib下添加jar包以便后续在服务器上运行能正确引用此jar包

代码如下:

package Utils;

import java.sql.*;

public class JDBCconnection {

    private static final String Driver = "com.mysql.jdbc.Driver";
    private static final String Url = "jdbc:mysql:///"; //  三个杠代表默认中间填写 localhost:3306 / 127.0.0.1:3306
    private static final String User = "root";
    private static final String Password = "123456";

    public static Connection getConnection(){
        try {
            Class.forName(Driver);
            return DriverManager.getConnection(Url,User,Password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //释放资源方法 先关闭Resultset 其次statement 最后Connection
    public static void Relesse(Connection conn , Statement statement) {
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            statement = null;
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
    public static void Relesse(Connection conn , Statement statement , ResultSet rs) {
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        Relesse(conn , statement);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

2.2.3 Dao层PersonDao类以及实现类

接口PersonDao类

代码如下:

package Dao;

import Model.Person;

import java.sql.SQLException;
import java.util.List;

public interface PersonDao {

    List<Person> getPersonList() throws SQLException;

    int addPerson(Person person);

    int delPerson(int personId);

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

实现类

代码如下:

package Dao;

import Model.Person;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class PersonDaoImpl extends BaseDao implements PersonDao{
    private static ResultSet rs = null;

    @Override
    public List<Person> getPersonList() throws SQLException {
        String sql = "select * from tb_person";
        List<Person> list = new ArrayList<>();
        rs = this.executeQuery(sql);
        while (rs.next()){
            Person p = new Person();
            p.setId(rs.getInt("id"));
            p.setNo(rs.getInt("no"));
            p.setSex(rs.getString("sex"));
            p.setName(rs.getString("name"));
            p.setBirth(rs.getString("birth"));
            p.setAddress(rs.getString("address"));
            p.setCardId(rs.getString("cardID"));
            p.setIsLocal(rs.getString("isLocal"));
            p.setWriteTime(rs.getTimestamp("writeTime"));

            list.add(p);
        }



        return list;
    }

    @Override
    public int addPerson(Person person) throws SQLException {
        Person p = new Person();
        int no = 0 ;
        String prepareSql = "INSERT INTO tb_person VALUES(null,?,?,?,?,?,?,?,?);";
        String sql = "select * from tb_person ;";


        rs = this.executeQuery(sql);
        rs.last();
        p.setNo(rs.getInt("id"));
        person.setNo( p.getNo() + 1 );
        person.setWriteTime(new Timestamp(System.currentTimeMillis()));
        this.executeSQL("alter table tb_person auto_increment= ? ;", person.getNo()); //保持主键id 字段之间的递增 可保持序号不会乱
        int i =  this.executeSQL(prepareSql,person.getNo(),person.getName(),person.getSex(),person.getBirth(),person.getCardId(),person.getAddress(),person.getIsLocal(),person.getWriteTime());


        return i;
    }

    @Override
    public int delPerson(int personId) {
        String prepareSql = "DELETE FROM tb_person where id = ?";
        int i = this.executeSQL(prepareSql,personId);
        return i;
    }


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

2.2 Model层

通过IDEA里的DATABASE功能—Generate pojs‘s–生成数据模型类

代码如下:

package Model;


import java.io.Serializable;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.Objects;

public class Person implements Serializable {

  private int id;
  private int no;
  private String name;
  private String sex;
  private String birth;
  private String cardId;
  private String address;
  private String isLocal;
  private java.sql.Timestamp writeTime;

  public Person() {
  }

  public Person(int id, int no, String name, String sex, String birth, String cardId, String address, String isLocal, Timestamp writeTime) {
    this.id = id;
    this.no = no;
    this.name = name;
    this.sex = sex;
    this.birth = birth;
    this.cardId = cardId;
    this.address = address;
    this.isLocal = isLocal;
    this.writeTime = writeTime;
  }

  @Override
  public String toString() {
    return "Person{" +
            "id=" + id +
            ", no=" + no +
            ", name='" + name + '\'' +
            ", sex='" + sex + '\'' +
            ", birth=" + birth +
            ", cardId='" + cardId + '\'' +
            ", address='" + address + '\'' +
            ", isLocal='" + isLocal + '\'' +
            ", writeTime=" + writeTime +
            '}';
  }

  @Override
  public boolean equals(Object o) {
    if (this == o) return true;
    if (!(o instanceof Person)) return false;
    Person person = (Person) o;
    return id == person.id && no == person.no && Objects.equals(name, person.name) && Objects.equals(sex, person.sex) && Objects.equals(birth, person.birth) && Objects.equals(cardId, person.cardId) && Objects.equals(address, person.address) && Objects.equals(isLocal, person.isLocal) && Objects.equals(writeTime, person.writeTime);
  }

  @Override
  public int hashCode() {
    return Objects.hash(id, no, name, sex, birth, cardId, address, isLocal, writeTime);
  }

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }


  public int getNo() {
    return no;
  }

  public void setNo(int no) {
    this.no = no;
  }


  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }


  public String getSex() {
    return sex;
  }

  public void setSex(String sex) {
    this.sex = sex;
  }


  public String getBirth() {
    return birth;
  }

  public void setBirth(String birth) {
    this.birth = birth;
  }


  public String getCardId() {
    return cardId;
  }

  public void setCardId(String cardId) {
    this.cardId = cardId;
  }


  public String getAddress() {
    return address;
  }

  public void setAddress(String address) {
    this.address = address;
  }


  public String getIsLocal() {
    return isLocal;
  }

  public void setIsLocal(String isLocal) {
    this.isLocal = isLocal;
  }


  public java.sql.Timestamp getWriteTime() {
    return writeTime;
  }

  public void setWriteTime(java.sql.Timestamp writeTime) {
    this.writeTime = writeTime;
  }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

2.3 Controller层

主要负责调用Dao层与Model层，控制View层输出模型

代码如下:

package Controller;

import Dao.PersonDao;
import Dao.PersonDaoImpl;
import Model.Person;

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 javax.servlet.jsp.PageContext;
import java.io.IOException;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/show")
public class personController extends HttpServlet {
    private static PersonDao personDao = new PersonDaoImpl();
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doGet(req,resp);
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=utf-8");
        HttpSession session = req.getSession();
        List<Person> personList = new ArrayList<>();
        String action = req.getParameter("action");
        switch (action){
            case "query":
                try {
                    personList = personDao.getPersonList();
                    session.setAttribute("personList",personList);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                   req.getRequestDispatcher("View/main.jsp").forward(req,resp);
                   break;
            case "del":
                   String id = req.getParameter("id");
                   personDao.delPerson(Integer.parseInt(id));
                   resp.sendRedirect(getServletContext().getContextPath()+"/show?action=query");
                   break;
            case  "insert":
                try {
                    Person person = new Person();
                    person.setName(req.getParameter("name"));
                    person.setSex(req.getParameter("sex"));
                    person.setBirth((req.getParameter("birth")));
                    person.setAddress(req.getParameter("address"));
                    person.setCardId(req.getParameter("cardId"));
                    person.setIsLocal(req.getParameter("isLocal"));
                    personDao.addPerson(person);
                    resp.sendRedirect(getServletContext().getContextPath()+"/show?action=query");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                break;
        }
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

2.4 View层

View文件下创建两个JSP文件 一个main.jsp展示数据 一个addPerson.jsp添加数据

index.jsp作为跳转页面

代码如下:

2.4.1 index.jsp

<%--
  Created by IntelliJ IDEA.
  User: 10501
  Date: 2022/3/20
  Time: 21:24
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
   <a href="${pageContext.servletContext.contextPath}/show?action=query">点击前往社区管理系统</a>
  </body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.4.2 main.jsp

<%@ page import="Model.Person" %>
<%@ page import="java.util.List" %><%--
  Created by IntelliJ IDEA.
  User: 10501
  Date: 2022/3/21
  Time: 12:52
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt"%>
<html>
<head>
    <title>Title</title>

</head>
<body>
<%--    <%--%>
<%--        List<Person> personList = (List) session.getAttribute("personList");--%>
<%--    %>--%>
    <div style="text-align: center;width: 1000px ; margin: 0 auto;" >
        <h1>社区人员登记系统</h1>
        <table border="1" style="text-align: center">
            <th>序号</th>
            <th>姓名</th>
            <th>性别</th>
            <th>出生日期</th>
            <th>身份证号</th>
            <th>现住地</th>
            <th>是否本地户口</th>
            <th>登记时间</th>
            <th>操作</th>
            <c:forEach var="person" items="${personList}">
                <tr>
                    <td><c:out value="${person.no}"></c:out></td>
                    <td><c:out value="${person.name}"></c:out></td>
                    <td><c:out value="${person.sex}"></c:out></td>
                    <td><c:out value="${person.birth}"></c:out></td>
                    <td><c:out value="${person.cardId}"></c:out></td>
                    <td><c:out value="${person.address}"></c:out></td>
                    <td><c:out value="${person.isLocal == 0?'否':'是'}"></c:out></td>
                    <td><c:out value="${person.writeTime}"></c:out></td>
                    <td><a href="${pageContext.servletContext.contextPath}/show?action=del&id=${person.id}" οnclick="Reconfirm()" >删除登记</a></td>
                </tr>
            </c:forEach>
        </table>
    </div>

    <div style="width: 1000px ; margin: 0 auto;" >
        <button οnclick=" location.href = '${pageContext.servletContext.contextPath}/View/addPerson.jsp'">新增登记人员</button>
    </div>

    <script>
        function Reconfirm(){
            if(window.confirm('是否删除')){
                alert('删除成功');
                return true;
            }else{
                return false;
            }


        }

    </script>

</body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

2.4.3 addPerson.jsp

<%--
  Created by IntelliJ IDEA.
  User: 10501
  Date: 2022/3/21
  Time: 22:13
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <h1>新增登记人员</h1>
    <form action="${pageContext.servletContext.contextPath}/show" method="post" οnsubmit=" return checkForm()">
        <input type="hidden"  name="action" value="insert">
        <p>姓  名：<input id="name" type="text" name="name"  >(必填)</p>
        <p>性  别:<input  type="radio" name="sex" value="男" checked>男 <input name="sex" type="radio" value="女">女</p>
        <p>生  日:<input type="date" name="birth" > </p>
        <p>身份证号:<input id="cardId" type="text" name="cardId" >(必填)</p>
        <p>现 住 地:<input id="address" type="text" name="address" >(必填)</p>
        <p>是否本地户口:<input type="radio" name="isLocal" value="1" checked>是 <input name="isLocal" type="radio" value="0">否</p>
        <input  type="submit" value="添加人员" style="margin-right: 25px;"><input type="reset" value="清空内容">
    </form>
    <script>
        function checkForm(){
            var name = document.getElementById('name');
            var cardId = document.getElementById('cardId');
            var address = document.getElementById('address');



            if (name.value.trim() == ''){
                alert('姓名不能为空');
                return false;
            }
            if (cardId.value.trim() == ''){
                alert('身份证号不能为空');
                return false;
            }

            if(cardId.value.trim().length != 18){
                alert('身份证号长度必须18位');
                return false;
            }
            if (address.value.trim() == ''){
                alert('地址不能为空');
                return false;
            }


        }

    </script>
</body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

整体编码到此完毕!

3. 项目展示

项目结构如下：

3.1 页面展示

3.1.1 main.jsp主要展示

3.1.2 addPerson.jsp主要展示

4. 总结

总得来说这套系统代码并不难 难在于花时间总结经验与改进代码 总结一下犯下的小错误

1. 本来想着将View层放在WEB-INF下通过Requset转发访问，但后面发现在删除完成处理页面刷新问题无法很好的重新加载数据，就推倒重来写成放在外面,结果上来说刷新页面重定向还是好用的 花了大量的时间研究 结论还是不要将请求处理的太麻烦

2. 开发过程中遇到了OUT文件中发现没有更新WEB-INF下的文件夹，处理方法重新导入artifact工件 ,解决完成

3. 表单上input标签Date类型从Request接收是String类型 所以在数据模型中 并不一定要Date类型的属性反而改成String类型的属性 会更好的处理保存数据

4. 数据库表中处理主键ID断层问题 通过如下代码解决 先运行该代码再插入会解决主键断层问题

   ALTER TABLE tb_person auto_increment = ?
   INSERT INTO tb_person VALUES(null); 插入的主键ID 根据上面问号所设置的值来插入
   1
   2