javaweb数据库增删改查——人口普查系统(第一步）
1、项目需求：
开展第七次全国人口普查，将为编制“十四五”规划提供重要信息支持；推动实现人口与经济社会、资源环境协调发展，为深化供给侧结构性改革，推动经济高质量发展，建设现代化经济体系提供强有力的支持；人口普查还将为完善人口发展战略和政策体系，促进人口长期均衡发展提供重要信息支持。
2.系统要求与功能设计
2.1 页面功能要求
（1）能够在Tomcat服务器中正确部署，并通过浏览器查看；（1分）
（2）网站页面整体风格统一；
图1 人口普查系统功能结构图
（3）主页面：要求显示人口登记、修改人口信息、删除人口信息、查询人口信息，人口信息浏览五个子菜单。（1分）
（4）人口登记页面：（6分）
①完成添加人口信息登记，基本信息和填报限制如下表所示
|
户 别 |
家庭户 集体户（单选框） |
|
住房类型 |
家庭住宅、集体住所、工作地住所、其他住宅、无住宅（单选框） |
|
本户现住房面积 |
XXXXX平方米（文本框，只能输入这个整数） |
|
本户现住房间数 |
XXXXX间（文本框，只能输入这个整数） |
|
户主姓名 |
XXXXX（文本框） |
|
身份证号码 |
XXXXXXXXXXXXXXXXXX（判断身份证位数13位并且只能录入数字、最后一位可以录入X） |
|
性别 |
男/女（单选框） |
|
民族 |
（文本框） |
|
受教育程度 |
研究生、大学本科、大学专科、高中、初中、小学、未上过学（下拉框实现） |
②点击“提交”按钮，保存成功则跳转到人口基本信息浏览界面，新录入的信息置顶显示。失败则提示错误信息，返回当前页面
评分标准：
①完成人口登记页面（未完成0分，完成1分）
②保存人口信息入库（未完成0分，完成1分）
③户别、住房类别、性别实现一个单选框录入0.5分，全部实现1分。（未完成0分，完成一个0.5，全部完成1分）
④本户现住房面积或本户现住房间数实现整数判断（未完成0分，完成0.5分）
⑤判断身份证按照要求录入，实现位数和数字录入判断0.5分，实现最后一位可以输入数字或X判断0.5分。（1分）
⑥受教育程度下拉框录入（未完成0分，完成0.5分）
⑦提交后页面跳转功能；（未完成0分，完成1分）
（5）修改人口信息页面：（3分）
输入户主姓名，显示其余信息，可对身份证号码、性别、民族、受教育程度进行修改。（身份证号码、性别、受教育程度必须符合录入要求）；如果该名户主信息数据库不存在，则提示“该户主信息不存在”。（3分）
评分标准：
①完成修改户主个人信息页面（未完成0分，完成0.5分）
② 实现数据库中信息更新（未完成0分，完成1分）
③修改信息判断是否符合要求。（未完成0分，完成0.5分）
④输入姓名，显示其余信息，若该信息不存在，提示错误信息；（未完成0分，完成1分）
（6）删除人口信息页面：录入户主姓名，显示详细信息后，点击“删除”按钮，弹出提示框，提示“是否确认删除该户主信息”，确认后删除该信息。（1分）
评分标准：
①输入户主姓名可显示其余信息。（未完成0分，完成0.5分）
②对应删除数据库中信息（未完成0分，完成0.5分）
（7）浏览人口信息页面：（2分）
以列表形式显示人口基本信息，结果列表中显示姓名、性别，民族、受教育程度基本信息，点击姓名，可以跳转到个人详细信息。
①实现以列表形式显示人口基本信息，结果列表中显示姓名、性别，民族、受教育程度基本信息（未完成0分，完成1分）
②实现跳转个人详细信息页面。（未完成0分，完成1分）
（8）查询人口信息页面：（3分）
要求可以根据人口姓名、性别、民族、受教育程度四种条件实现模糊查询，输出结果以列表形式显示，显示人口姓名、性别、民族、受教育程度基本信息，点击列表中的姓名，跳转到人口个人详细信息页面。
评分标准：
①缺少一种查询条件扣除1分。（未完成0分，完成3分）
2.2 功能要求
（1）设计出合理的数据库和数据表，要求使用mysql、sqlserver、oracle三种数据库中一种（1分）
（2）使用Serverlet实现页面交互（1分）。
（3）使用Java Bean封装数据库连接操作（1分。）
1.bean层
package bean; public class Bean { private int id; private String hubie; private String livetype; private int area; private int roomnum; private String name; private String idcard; private String sex; private String nation; private String education; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getHubie() { return hubie; } public void setHubie(String hubie) { this.hubie = hubie; } public String getLivetype() { return livetype; } public void setLivetype(String livetype) { this.livetype = livetype; } public int getArea() { return area; } public void setArea(int area) { this.area = area; } public int getRoomnum() { return roomnum; } public void setRoomnum(int roomnum) { this.roomnum = roomnum; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getIdcard() { return idcard; } public void setIdcard(String idcard) { this.idcard = idcard; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getNation() { return nation; } public void setNation(String nation) { this.nation = nation; } public String getEducation() { return education; } public void setEducation(String education) { this.education = education; } public Bean(int id, String hubie, String livetype, int area, int roomnum, String name, String idcard,String sex, String nation, String education) { this.id = id; this.hubie = hubie; this.livetype = livetype; this.area = area; this.roomnum = roomnum; this.name = name; this.idcard = idcard; this.sex = sex; this.nation = nation; this.education = education; } public String toString() { return "Census{" + "id=" + id + ", hubie='" + hubie + '\'' + ", livetype='" + livetype + '\'' + ", area=" + area + ", roomnum=" + roomnum + ", name='" + name + '\'' + ", idcard='" + idcard + '\'' + ", sex='" + sex + '\'' + ", nation='" + nation + '\'' + ", education='" + education + '\'' + '}'; } }
2.dao层
package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class Dao {//dao层 private DBUtil dbutil=new DBUtil(); public Dao() { // TODO Auto-generated constructor stub } public boolean insert(Bean bean) {//插入数据的方法 boolean f=false; String sql="insert into person(id,hubie,livetype,area,roomnum, name, idcard, sex,nation, education) values('"+bean.getId()+"','"+bean.getHubie()+"','"+bean.getLivetype()+"','"+bean.getArea()+"','"+bean.getRoomnum()+"','"+bean.getName()+"','"+bean.getIdcard()+"','"+bean.getSex()+"','"+bean.getNation()+"','"+bean.getEducation()+"')"; Connection conn=DBUtil.getConnection();//数据库连接，加载驱动 Statement state=null; try { state=conn.createStatement();//实例化Statement对象,方便对sql语句进行操作 System.out.println(conn); state.executeUpdate(sql); f=true; //执行数据库更新操作用于执行INSERT、UPDATE或DELETE语句以及SQLDDL（数据定义语言）语句， //例如CREATETABLE和DROPTABLE,（创建表和删除表） }catch(Exception e)//当try语句中s出现异常时，会执行catch中的语句 { e.printStackTrace();//捕获异常的语句 } finally //finally作为异常处理的一部分，它只能用在try/catch语句中，并且附带一个语句块，表示这段语句最终一定会被执行（不管有没有抛出异常），经常被用在需要释放资源的情况下。 { DBUtil.close(conn); } return f; } public boolean delete(int id ) {//删除方法 String sql="delete from person where id='"+id+"'"; boolean f=false; Connection conn =DBUtil.getConnection(); Statement st=null; try { st=conn.createStatement(); st.executeUpdate(sql); f=true; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ DBUtil.close(st, conn); } return f; } public boolean update(Bean bean) {//更新方法 String sql="update person set hubie='"+bean.getHubie()+"',livetype='"+bean.getLivetype()+"',area='"+bean.getArea()+"',roomnum='"+bean.getRoomnum()+"',name='"+bean.getName()+"',idcard='"+bean.getIdcard()+"',sex='"+bean.getSex()+"',nation='"+bean.getNation()+"',education='"+bean.getEducation()+"'where id='"+bean.getId()+"'"; Connection conn=DBUtil.getConnection(); boolean f=false; Statement st=null; try { st=conn.createStatement(); st.executeUpdate(sql); f=true; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return f; } public List<Bean> list(){//查询所有方法 String sql="select * from person order by id ASC"; Connection conn=DBUtil.getConnection(); Statement st=null; List<Bean> list=new ArrayList<>(); ResultSet rs=null; Bean bean=null; try { st=conn.createStatement(); st.executeQuery(sql); rs=st.executeQuery(sql); while(rs.next()) { int id=rs.getInt("id"); String hubie = rs.getString("hubie"); String livetype = rs.getString("livetype"); int area=rs.getInt("area"); int roomnum=rs.getInt("roomnum"); String name = rs.getString("name"); String idcard=rs.getString("idcard"); String sex = rs.getString("sex"); String nation = rs.getString("nation"); String education = rs.getString("education"); bean=new Bean(id,hubie,livetype,area,roomnum, name, idcard, sex,nation, education); list.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(rs, st, conn); } return list; } public List<Bean> searchByName(String str) throws SQLException{//查询条件方法 String sql="select * from person where(name like '%"+str+"%')"; Connection conn=DBUtil.getConnection(); Statement st=null; PreparedStatement pt = conn.prepareStatement(sql); List<Bean> search=new ArrayList<>(); ResultSet rs=null; Bean bean=null; try { pt=conn.prepareStatement(sql); rs=pt.executeQuery(); while(rs.next()) { int id=rs.getInt("id"); String hubie = rs.getString("hubie"); String livetype = rs.getString("livetype"); int area=rs.getInt("area"); int roomnum=rs.getInt("roomnum"); String name = rs.getString("name"); String idcard=rs.getString("idcard"); String sex = rs.getString("sex"); String nation = rs.getString("nation"); String education = rs.getString("education"); bean=new Bean(id,hubie,livetype,area,roomnum, name, idcard, sex,nation, education); search.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(rs, st, conn); } return search; } public List<Bean> searchBySex(String str) throws SQLException{//查询条件方法 String sql="select * from person where(sex like '%"+str+"%')"; Connection conn=DBUtil.getConnection(); Statement st=null; PreparedStatement pt = conn.prepareStatement(sql); List<Bean> search=new ArrayList<>(); ResultSet rs=null; Bean bean=null; try { pt=conn.prepareStatement(sql); rs=pt.executeQuery(); while(rs.next()) { int id=rs.getInt("id"); String hubie = rs.getString("hubie"); String livetype = rs.getString("livetype"); int area=rs.getInt("area"); int roomnum=rs.getInt("roomnum"); String name = rs.getString("name"); String idcard=rs.getString("idcard"); String sex = rs.getString("sex"); String nation = rs.getString("nation"); String education = rs.getString("education"); bean=new Bean(id,hubie,livetype,area,roomnum, name, idcard, sex,nation, education); search.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(rs, st, conn); } return search; } public List<Bean> searchByEducation(String str) throws SQLException{//查询条件方法 String sql="select * from person where(education like '%"+str+"%')"; Connection conn=DBUtil.getConnection(); Statement st=null; PreparedStatement pt = conn.prepareStatement(sql); List<Bean> search=new ArrayList<>(); ResultSet rs=null; Bean bean=null; try { pt=conn.prepareStatement(sql); rs=pt.executeQuery(); while(rs.next()) { int id=rs.getInt("id"); String hubie = rs.getString("hubie"); String livetype = rs.getString("livetype"); int area=rs.getInt("area"); int roomnum=rs.getInt("roomnum"); String name = rs.getString("name"); String idcard=rs.getString("idcard"); String sex = rs.getString("sex"); String nation = rs.getString("nation"); String education = rs.getString("education"); bean=new Bean(id,hubie,livetype,area,roomnum, name, idcard, sex,nation, education); search.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(rs, st, conn); } return search; } public List<Bean> searchByNation(String str) throws SQLException{//查询条件方法 String sql="select * from person where(nation like '%"+str+"%')"; Connection conn=DBUtil.getConnection(); Statement st=null; PreparedStatement pt = conn.prepareStatement(sql); List<Bean> search=new ArrayList<>(); ResultSet rs=null; Bean bean=null; try { pt=conn.prepareStatement(sql); rs=pt.executeQuery(); while(rs.next()) { int id=rs.getInt("id"); String hubie = rs.getString("hubie"); String livetype = rs.getString("livetype"); int area=rs.getInt("area"); int roomnum=rs.getInt("roomnum"); String name = rs.getString("name"); String idcard=rs.getString("idcard"); String sex = rs.getString("sex"); String nation = rs.getString("nation"); String education = rs.getString("education"); bean=new Bean(id,hubie,livetype,area,roomnum, name, idcard, sex,nation, education); search.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(rs, st, conn); } return search; } }
3.DBUtil
package DBUtil; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBUtil { private static String url = "jdbc:mysql://localhost:3306/course?useUnicode=true&characterEncoding=utf8"; private static String user = "root"; private static String password = "123"; private static String jdbcName="com.mysql.jdbc.Driver"; private Connection con=null; public static Connection getConnection() { Connection con=null; try { Class.forName(jdbcName); con=DriverManager.getConnection(url, user, password); //System.out.println("数据库连接成功"); } catch (Exception e) { // TODO Auto-generated catch block //System.out.println("数据库连接失败"); e.printStackTrace(); } try { con = DriverManager.getConnection(url,user,password); System.out.println("连接成功"); } catch (SQLException e) { // TODO: handle exception e.printStackTrace(); } return con; } public static void main(String[] args)throws SQLException { Connection conn = getConnection(); PreparedStatement pstmt = null; ResultSet rs = null; String sql ="select * from person"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); System.out.println(getConnection()); while(rs.next()){ System.out.println("成功"); } } // return con; public static void close(Connection con) { if(con!=null) try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void close(Statement state, Connection conn) { if(state!=null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(ResultSet rs, Statement state, Connection conn) { if(rs!=null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(state!=null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
4.searchServelet层
package searchServlet; import java.io.IOException; import java.sql.SQLException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * Servlet implementation class searchServlet */ @WebServlet("/searchServlet") public class searchServlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public searchServlet() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub //response.getWriter().append("Served at: ").append(request.getContextPath()); request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); String cxfs=request.getParameter("cxfs"); System.out.print(cxfs); String str=request.getParameter("value"); Dao dao=new Dao(); List<Bean> list = null; try { if("1".equals(cxfs)) { list=dao.searchByName(str); } if("2".equals(cxfs)) { list=dao.searchBySex(str); } if("3".equals(cxfs)) { list=dao.searchByEducation(str); } if("4".equals(cxfs)) { list=dao.searchByNation(str); } } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } request.setAttribute("list", list); request.getRequestDispatcher("list.jsp").forward(request,response); System.out.print(list.size()); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } }
5.servelet层
package Servelet; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * Servlet implementation class servlet */ @WebServlet("/servlet") public class servlet extends HttpServlet { Dao dao=new Dao(); private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public servlet() { super(); // TODO Auto-generated constructor stub } private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); int id = Integer.parseInt(request.getParameter("id")); String hubie = request.getParameter("hubie"); String livetype = request.getParameter("livetype"); int area= Integer.parseInt(request.getParameter("area")); int roomnum = Integer.parseInt(request.getParameter("roomnum")); String name = request.getParameter("name"); String idcard = request.getParameter("idcard"); String sex = request.getParameter("sex"); String nation = request.getParameter("nation"); String education= request.getParameter("education"); Bean bean=new Bean(id,hubie,livetype,area,roomnum,name,idcard,sex,nation,education); dao.update(bean); request.setAttribute("message", "修改成功"); request.getRequestDispatcher("servlet?method=list").forward(request, response); } private void list(HttpServletRequest request, HttpServletResponse response) throws Exception { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); List<Bean> list = dao.list(); request.setAttribute("list", list); request.getRequestDispatcher("list.jsp").forward(request,response); } private void delete(HttpServletRequest request, HttpServletResponse response) throws Exception, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("UTF-8"); int id=Integer.parseInt(request.getParameter("id")); dao.delete(id); //进行数据库的删除操作 request.setAttribute("message", "删除成功"); request.getRequestDispatcher("servlet?method=list").forward(request, response); } private void insert(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); int id = Integer.parseInt(request.getParameter("id")); String hubie = request.getParameter("hubie"); String livetype = request.getParameter("livetype"); int area= Integer.parseInt(request.getParameter("area")); int roomnum = Integer.parseInt(request.getParameter("roomnum")); String name = request.getParameter("name"); String idcard = request.getParameter("idcard"); String sex = request.getParameter("sex"); String nation = request.getParameter("nation"); String education= request.getParameter("education"); Bean bean=new Bean(id,hubie,livetype,area,roomnum, name, idcard, sex,nation, education); if(dao.insert(bean)) { request.setAttribute("message", "添加成功"); request.getRequestDispatcher("index.jsp").forward(request, response); } } private void search(HttpServletRequest request, HttpServletResponse response) throws Exception, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("UTF-8"); String cxfs = request.getParameter("cxfs"); System.out.print("cxfs"); request.setAttribute("search", "查询成功"); request.getRequestDispatcher("list.jsp").forward(request, response); } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); String method=request.getParameter("method"); if("insert".equals(method)) { insert(request,response); } else if("delete".equals(method)) { try { delete(request,response); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } else if("update".equals(method)) { update(request,response); } else if("list".equals(method)) { try { list(request,response); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }} else if("search".equals(method)) { try { search(request,response); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } }