社区人员登记管理系统

社区人员登记管理系统

模拟设计一个使用于社区管理来人员登记的系统，采取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.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;

    }
}

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);
    }
}

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

}

实现类

代码如下:

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


}

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

}

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

}

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>

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>

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>

整体编码到此完毕!

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 根据上面问号所设置的值来插入