人口普查1

package tool;
import java.sql.*;
public class linkMYSQL {
     // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";  
    //*************************************************服务器端口/数据库名称？useSS**********************
    static final String DataBasName="learnmysql";
    static final String URL_Fornt="jdbc:mysql://localhost:3307/";
    static final String URL_Tail="?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    static final String URL =URL_Fornt+DataBasName+URL_Tail;
    // 数据库的用户名与密码                               
    static final String USER = "root";
    static final String PASS = "123456";
    public static Connection link_mysql()  {
        Connection conn=null;
            try {
                Class.forName(JDBC_DRIVER);
                System.out.println("连接数据库...");
                conn = DriverManager.getConnection(URL,USER,PASS);
                System.out.println("已连接数据库");
                
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        return conn;
            
    }
    public static void stop_link_mysql(Connection conn) {
        if(conn!=null)
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
    public static void stop_link_mysql(PreparedStatement pre) {
        if(pre!=null)
            try {
                pre.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
    public static void stop_link_mysql(Statement pre) {
        if(pre!=null)
            try {
                pre.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
    public static void stop_link_mysql(ResultSet res) {
        if(res!=null)
            try {
                res.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

package tool;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class operationMYSQL {

    final static String table_name=" people";
    final static String table_key="(house,housesize,houseroom,name,id,sex,nation,edu)";
    final static String table_value="values (?,?,?,?,?,?,?,?)";
    
    /*******************************************************************/
    
    public void addData(userData data) {
        Connection connection=linkMYSQL.link_mysql();//链接数据库
        PreparedStatement ps=null;
        try {
            String input="insert into "+table_name+table_key+table_value;
            ps=connection.prepareStatement(input);
            System.out.println("test use 1");
            /********************************/
            
            ps.setString(1, data.getHouse());
            ps.setString(2, data.getHousesize());
            ps.setString(3, data.getHouseroom());
            ps.setString(4, data.getName());
            ps.setString(5, data.getId());
            System.out.println("test use 2");
            ps.setString(6, data.getSex());
            ps.setString(7, data.getNation());
            ps.setString(8, data.getEdu());
            /*****************************/
            ps.executeUpdate();
            System.out.println("数据导入成功");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            linkMYSQL.stop_link_mysql(connection);
            linkMYSQL.stop_link_mysql(ps);
        }
    }
    public void deleteData(String id){
        Connection connection=linkMYSQL.link_mysql();//链接数据库
        Statement ps=null;
        try {
            ps = connection.createStatement();
            String output="delete from "+table_name+" where house='"+id+"';";
            System.out.println("test delete 1");
            ps.executeUpdate(output);
            System.out.println("删除成功");
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        linkMYSQL.stop_link_mysql(connection);
        linkMYSQL.stop_link_mysql(ps);
    }
    public void update(userData data) {
        Connection connection=linkMYSQL.link_mysql();//链接数据库
        Statement ps=null;
        try {
            ps = connection.createStatement();
            String house= data.getHouse();
            String hs= data.getHousesize();
            String hr= data.getHouseroom();
            String name= data.getName();
            String id= data.getId();
            String sex= data.getSex();
            String nation= data.getNation();
            String edu= data.getEdu();        
             String output="update"+table_name+" set where house='"+house+"',housesize='"+hs+"',houseroom='"+hr+"',name='"+name+"',id='"+id+"',sex='"+sex+"',nation='"+nation+"',edu='"+edu+"';";
            ps.executeUpdate(output);
            System.out.println("更新成功");
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
        linkMYSQL.stop_link_mysql(connection);
        linkMYSQL.stop_link_mysql(ps);
        }
    }
    public List<userData> getAllData() {
        List<userData> date=new ArrayList<userData>();
    
        Connection connection=linkMYSQL.link_mysql();//链接数据库
        Statement ps=null;
        try {
            
            ps = connection.createStatement();
            String output="select * from "+table_name+";";
            System.out.println("test use 1");
            ResultSet rs = ps.executeQuery(output);
            System.out.println("test use 2");
            while(rs.next()){
                System.out.println("test use");
                userData temp=new userData(rs.getString("house"),rs.getString("housesize"),rs.getString("houseroom"),rs.getString("name"),rs.getString("id"),rs.getString("sex"),rs.getString("nation"),rs.getString("edu"));
                System.out.println("户别："+temp.getHouse()+"   现住房面积："+temp.getHousesize()+"   现住房房间数："+temp.getHouseroom()+"   姓名："+temp.getName()+"   身份证号："+temp.getId()+" 性别："+temp.getSex()+" 民族："+temp.getNation()+" 受教育程度："+temp.getEdu());    
                
                 date.add(temp);
             }
            System.out.println("获取成功");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            linkMYSQL.stop_link_mysql(connection);
            linkMYSQL.stop_link_mysql(ps);
            }
        return date;
    }
    public     userData getData(String a) {
        //List<String> date=new ArrayList<String>();
        userData save = null;
        Connection connection=linkMYSQL.link_mysql();//链接数据库
        Statement ps=null;
        try {
            ps = connection.createStatement();
            String output="select * from "+table_name+" where house='"+a+"';";
            System.out.println("test use 1");
            ResultSet rs = ps.executeQuery(output);
            System.out.println("test use 2");
            while(rs.next()){
                 save=new userData(rs.getString("house"),rs.getString("housesize"),rs.getString("houseroom"),rs.getString("name"),rs.getString("id"),rs.getString("sex"),rs.getString("nation"),rs.getString("edu"));
                    System.out.println("获取成功");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            linkMYSQL.stop_link_mysql(connection);
            linkMYSQL.stop_link_mysql(ps);
            }
        return save;
    }
    public     String get(String a) {
        //List<String> date=new ArrayList<String>();
        String save = null;
        Connection connection=linkMYSQL.link_mysql();//链接数据库
        Statement ps=null;
        try {
            ps = connection.createStatement();
            String output="select * from "+table_name+" where house like '"+a+"';";
            System.out.println("test use 1");
            ResultSet rs = ps.executeQuery(output);
            System.out.println("test use 2");
            while(rs.next()){
                 save="姓名："+rs.getString("house")+"性别："+rs.getString("housesize")+"民族："+rs.getString("edu")+"受教育程度"+rs.getString("nation")+"<br>";
                    System.out.println("获取成功");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            linkMYSQL.stop_link_mysql(connection);
            linkMYSQL.stop_link_mysql(ps);
            }
        return save;
    }
    

}

package tool;

public class userData {
private String house;
private String housesize;
private String houseroom;
private String name;
private String id;
private String sex;
private String nation;
private String edu;
public userData(){
    id="*";
    name="***";
    sex="male";
    house="0";
    houseroom="0";
    housesize="0";
    edu="*";
    nation="*";
}
public userData(String i,String na,String se,String ho,String hr,String hs,String ed,String n){
    id=i;
    name=na;
    sex=se;
    house=ho;
    houseroom=hr;
    housesize=hs;
    edu=ed;
    nation=n;
}
public String getEdu() {
    return edu;
}public String getHouse() {
    return house;
}public String getHouseroom() {
    return houseroom;
}public String getHousesize() {
    return housesize;
}public String getId() {
    return id;
}public String getName() {
    return name;
}public String getNation() {
    return nation;
}public String getSex() {
    return sex;
}public void setEdu(String edu) {
    this.edu = edu;
}public void setHouse(String house) {
    this.house = house;
}public void setHousesize(String housesize) {
    this.housesize = housesize;
}public void setHouseroom(String houseroom) {
    this.houseroom = houseroom;
}public void setId(String id) {
    this.id = id;
}public void setName(String name) {
    this.name = name;
}public void setNation(String nation) {
    this.nation = nation;
}public void setSex(String sex) {
    this.sex = sex;
}

}

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
     <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>信息录入</title>
</head>
<body>
<h3>请选择</h3>
<h4>1.查询信息</h4>
<script type="text/javascript">
function myfuc(){
    window.location.href='查询.jsp';
}
</script>

<button onclick="myfuc()">确定</button>
<h4>2.信息登记</h4>
<script type="text/javascript">
function myfuc1(){
    window.location.href='登记.jsp';
}
</script>

<button onclick="myfuc1()">确定</button>
<h4>3.信息修改</h4>
<script type="text/javascript">
function myfuc2(){
    window.location.href='修改.jsp';
}
</script>

<button onclick="myfuc2()">确定</button>
<h4>4.信息删除</h4>
<script type="text/javascript">
function myfuc3(){
    window.location.href='删除.jsp';
}
</script>

<button onclick="myfuc3()">确定</button>


</body>
</html>

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>删除</title>
</head>
<body>
<h3>输入要查询的关键词：</h3>
<form action="查询.jsp" method="get">
<input type="text" name="name">
<input type="submit" value="确定删除"><br>
</form>
<br>
<script type="text/javascript">
function myfuc3(){
    window.location.href='显示全部信息.jsp';
}
</script>

<button onclick="myfuc3()">显示全部信息</button>
<script type="text/javascript">
function myfuc3(){
    window.location.href='人口普查系统.jsp';
}
</script>

<button onclick="myfuc3()">返回总页面</button>
<%
    operationMYSQL get=new operationMYSQL();
    if(request.getParameter("name")!=null){
        String t=get.get(request.getParameter("name"));
        out.println(t);
    }
    
%>
</body>
</html>

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>录入</title>
</head>
<body>
<h3>输入要录入的学生信息；</h3>
<form action="登记.jsp" method="get">
    户别：<input type="radio" name="house" value="家庭户">家庭户
              <input type="radio" name="house" value="集体户">集体户<br>
    现住房面积：<input type="number" name="housesize" min="0"><br>
    现住房房间数：<input type="number" name="houseroom" min="0"><br>
    姓名：<input type="text" name="name" ><br>
    身份证：<input type="text" name="id"><br>
    
    性别：<input type="radio" name="sex" value="male">Male
          <input type="radio" name="sex" value="female">Female<br>
    民族：<input type="text" name="nation" ><br>
    受教育程度:<input list="edus" name="edu">
                <datalist id="edus">
                <option value="小学" >
                <option value="初中" >
                <option value="高中" >
                <option value="大专" >
                <option value="大学本科" >
                <option value="硕士" >
                <option value="博士" >
                </datalist><br>
            <input type="submit" value="确定">
            
</form>
<script type="text/javascript">
var cardId=/^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|31)|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}([0-9]|x|X)$/;
var aa = '<%=(String)request.getParameter("id")%>';
if(cardId.test(aa)){
    console.log("正确")
    </script>
    <%System.out.println(request.getParameter("house")+request.getParameter("housesize")+request.getParameter("houseroom")+request.getParameter("name")+request.getParameter("id")+request.getParameter("sex")+request.getParameter("nation")+request.getParameter("edu"));
        userData save=new userData(request.getParameter("house"),request.getParameter("housesize"),request.getParameter("houseroom"),request.getParameter("name"),request.getParameter("id"),request.getParameter("sex"),request.getParameter("nation"),request.getParameter("edu"));
        operationMYSQL add=new operationMYSQL();
        add.addData(save);
    %>
    <script type="text/javascript">
}else{
  console.log("格式不正确")     
}
</script>
<script type="text/javascript">
function myfuc3(){
    window.location.href='人口普查系统.jsp';
}
</script>

<button onclick="myfuc3()">点击返回主页面</button>
</body>
</html>

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>删除</title>
</head>
<body>
<h3>输入要删除的户主姓名：</h3>
<form action="删除.jsp" method="get">
<input type="text" name="name">
<input type="submit" value="确定删除"><br>
</form>
<%
    String name=null;
    userData pa=new userData();
    operationMYSQL get=new operationMYSQL();
    if(request.getParameter("name")!=null){
        name=request.getParameter("name");
        pa=get.getData(name);
        out.println("户别："+pa.getHouse()+"   现住房面积："+pa.getHousesize()+"   现住房房间数："+pa.getHouseroom()+"   姓名："+pa.getName()+"   身份证号："+pa.getId()+" 性别："+pa.getSex()+" 民族："+pa.getNation()+" 受教育程度："+pa.getEdu());                        
        %>
        <script type="text/javascript">
            window.alert(确定要删除);
        </script>
        <%
            get.deleteData(name);
    }
    else{
        %>
        <script type="text/javascript">
            window.alert(账号或密码为空);
        </script>
        <%
    }
    

%><br>
<script type="text/javascript">
function myfuc3(){
    window.location.href='人口普查系统.jsp';
}
</script>

<button onclick="myfuc3()">返回总页面</button>
</body>
</html>

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>all</title>
</head>
<body>
<%
    List<userData>date=new ArrayList<userData>();
    operationMYSQL get=new operationMYSQL();
    date=get.getAllData();
    userData pa=new userData();
    
    for(int i=0;i<=date.size();i++){
        pa=date.get(i);
        out.println("户别："+date.get(i).getHouse()+"   现住房面积："+date.get(i).getHousesize()+"   现住房房间数："+date.get(i).getHouseroom()+"   姓名："+date.get(i).getName()+"   身份证号："+date.get(i).getId()+" 性别："+date.get(i).getSex()+" 民族："+date.get(i).getNation()+" 受教育程度："+date.get(i).getEdu());    
        
    }
%>
<script type="text/javascript">
function myfuc3(){
    window.location.href='人口普查系统.jsp';
}
</script>

<button onclick="myfuc3()">点击返回主页面</button>
</body>
</html>

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>修改</title>
</head>
<body>
<h3>输入要修改的户主名：</h3>
<form action="执行修改.jsp" method="get">
<input type="text" name="name">
<input type="submit" value="确定删除"><br>
</form>
<%
    String name=null;
    userData pa=new userData();
    operationMYSQL get=new operationMYSQL();
    if(request.getParameter("name")!=null){
        name=request.getParameter("name");
        pa=get.getData(name);
        if(pa!=null){
            out.println("户别："+pa.getHouse()+"   现住房面积："+pa.getHousesize()+"   现住房房间数："+pa.getHouseroom()+"   姓名："+pa.getName()+"   身份证号："+pa.getId()+" 性别："+pa.getSex()+" 民族："+pa.getNation()+" 受教育程度："+pa.getEdu());    
            %><h3>输入要录入的学生信息；</h3>
            <form action="登记.jsp" method="get">
                户别：<input type="radio" name="house" value="家庭户">家庭户
                          <input type="radio" name="house" value="集体户">集体户<br>
                现住房面积：<input type="number" name="housesize" min="0"><br>
                现住房房间数：<input type="number" name="houseroom" min="0"><br>
                姓名：<input type="text" name="name" ><br>
                身份证：<input type="text" name="id"><br>
                
                性别：<input type="radio" name="sex" value="male">Male
                      <input type="radio" name="sex" value="female">Female<br>
                民族：<input type="text" name="nation" ><br>
                受教育程度:<input list="edus" name="edu">
                            <datalist id="edus">
                            <option value="小学" >
                            <option value="初中" >
                            <option value="高中" >
                            <option value="大专" >
                            <option value="大学本科" >
                            <option value="硕士" >
                            <option value="博士" >
                            </datalist><br>
                        <input type="submit" value="确定">
                        
            </form>
            <script type="text/javascript">
            var cardId=/^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|31)|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}([0-9]|x|X)$/;
            var aa = '<%=(String)request.getParameter("id")%>';
            if(cardId.test(aa)){
                console.log("正确")
                
            }</script>
            
            <%
            get.update(pa);
            } 
        }%>
    <br>
<script type="text/javascript">
function myfuc3(){
    window.location.href='人口普查系统.jsp';
}
</script>

<button onclick="myfuc3()">返回总页面</button>
</body>
</html>

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>查询</title>
</head>
<body>
<%
    String name=null;
    userData pa=new userData();
    operationMYSQL get=new operationMYSQL();
    if(request.getParameter("name")!=null){
        name=request.getParameter("name");
        pa=get.getData(name);
        out.println("户别："+pa.getHouse()+"   现住房面积："+pa.getHousesize()+"   现住房房间数："+pa.getHouseroom()+"   姓名："+pa.getName()+"   身份证号："+pa.getId()+" 性别："+pa.getSex()+" 民族："+pa.getNation()+" 受教育程度："+pa.getEdu());                        
        %>
        <script type="text/javascript">
            window.alert(确定要删除);
        </script>
        <%
            get.deleteData(name);
    }
    else{
        %>
        <script type="text/javascript">
            window.alert(账号或密码为空);
        </script>
        <%
    }
    

%><br>
<script type="text/javascript">
function myfuc3(){
    window.location.href='人口普查系统.jsp';
}
</script>

<button onclick="myfuc3()">返回总页面</button>
</body>
</html>

<!DOCTYPE html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.util.*"%>
      <%@page import="tool.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>录入</title>
</head>
<body>
<h3>输入要录入的学生信息；</h3>
<form action="执行修改.jsp" method="get">
    户别：<input type="radio" name="house" value="家庭户">家庭户
              <input type="radio" name="house" value="集体户">集体户<br>
    现住房面积：<input type="number" name="housesize" min="0"><br>
    现住房房间数：<input type="number" name="houseroom" min="0"><br>
    姓名：<input type="text" name="name" ><br>
    身份证：<input type="text" name="id"><br>
    
    性别：<input type="radio" name="sex" value="male">Male
          <input type="radio" name="sex" value="female">Female<br>
    民族：<input type="text" name="nation" ><br>
    受教育程度:<input list="edus" name="edu">
                <datalist id="edus">
                <option value="小学" >
                <option value="初中" >
                <option value="高中" >
                <option value="大专" >
                <option value="大学本科" >
                <option value="硕士" >
                <option value="博士" >
                </datalist><br>
            <input type="submit" value="确定">
            
</form>
<script type="text/javascript">
var cardId=/^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|31)|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}([0-9]|x|X)$/;
var aa = '<%=(String)request.getParameter("id")%>';
if(cardId.test(aa)){
    console.log("正确")
    </script>
    <%System.out.println(request.getParameter("house")+request.getParameter("housesize")+request.getParameter("houseroom")+request.getParameter("name")+request.getParameter("id")+request.getParameter("sex")+request.getParameter("nation")+request.getParameter("edu"));
        userData save=new userData(request.getParameter("house"),request.getParameter("housesize"),request.getParameter("houseroom"),request.getParameter("name"),request.getParameter("id"),request.getParameter("sex"),request.getParameter("nation"),request.getParameter("edu"));
        operationMYSQL add=new operationMYSQL();
        add.update(save);
    %>
    <script type="text/javascript">
}else{
  console.log("格式不正确")     
}
</script>
<script type="text/javascript">
function myfuc3(){
    window.location.href='人口普查系统.jsp';
}
</script>

<button onclick="myfuc3()">点击返回主页面</button>
</body>
</html>