一,题目:

一、 考试要求:

1登录账号:要求由6到12位字母、数字、下划线组成,只有字母可以开头;(1分)

2登录密码:要求显示“• ”或“*”表示输入位数,密码要求八位以上字母、数字组成。(1分)

3性别:要求用单选框或下拉框实现,选项只有“男”或“女”;(1分)

4学号:要求八位数字组成,前四位为“2018”开头,输入自己学号;(1分)

5姓名:输入自己的姓名;

5电子邮箱:;(1分)

6点击“添加”按钮,将学生个人信息存储到数据库中。(3分)

7可以演示连接上数据库。(2分)

二思路

1,首先按要求编写出WEB网页。

2,在DBUtil.java文件编写中对数据库进行连接和关闭的操作。

3,在dao.java文件中编写对 sql 语句的拼接以及执行 sql 语句向数据库中添加东西的函数

DBUtil代码

package classtest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库连接工具
 * @author 李泽宇
 *
 */
public class DBUtil {
    //联结字符串                                              //数据库名test
    public static String db_url = "jdbc:mysql://localhost:3306/test1?&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    //数据库用户名
    public static String db_user = "root";
    //数据库密码名
    public static String db_pass = "root";

    public static Connection getConn () {

        //声明与数据库的连接并实例化为null
        Connection conn = null;

        try {
            //驱动程序名"
            Class.forName("com.mysql.cj.jdbc.Driver");//连接数据库
            //具体地连接到数据库——联接字符串(数据库名),联接用户名,联接密码名
            conn = DriverManager.getConnection(db_url, db_user, db_pass);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return conn;
    }

    /**
     * 关闭连接
     * @param state
     * @param conn
     */
    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();
            }
        }
    }

}

Grade代码

package classtest;

public class Grade {
    private String  username;
    private String password;
    private String sex;
    private String name;
    private String number;
    private String mail;
    private String xuexiao;
    private String xi;
    private String ban;
    public Grade(String username, String password, String sex, String name, String number, String mail, String xuexiao,
            String xi, String ban, String nian, String area, String base) {
        super();
        this.username = username;
        this.password = password;
        this.sex = sex;
        this.name = name;
        this.number = number;
        this.mail = mail;
        this.xuexiao = xuexiao;
        this.xi = xi;
        this.ban = ban;
        this.nian = nian;
        this.area = area;
        this.base = base;
    }
    private String nian;
    private String area;
    private String base;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
    public String getMail() {
        return mail;
    }
    public void setMail(String mail) {
        this.mail = mail;
    }
    public String getXuexiao() {
        return xuexiao;
    }
    public void setXuexiao(String xuexiao) {
        this.xuexiao = xuexiao;
    }
    public String getXi() {
        return xi;
    }
    public void setXi(String xi) {
        this.xi = xi;
    }
    public String getBan() {
        return ban;
    }
    public void setBan(String ban) {
        this.ban = ban;
    }
    public String getNian() {
        return nian;
    }
    public void setNian(String nian) {
        this.nian = nian;
    }
    public String getArea() {
        return area;
    }
    public void setArea(String area) {
        this.area = area;
    }
    public String getBase() {
        return base;
    }
    public void setBase(String base) {
        this.base = base;
    }

}

dao代码

package classtest;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import classtest.Grade;
import classtest.DBUtil;;

public class dao
{

    //添加
    //添加入Grade表里

    public boolean insert(Grade grade)
    {
        String sql="insert into test12(username,password,sex,name,number,mail,xuexiao,xi,ban,nian,area,base) values('"+grade.getUsername()+"','"+grade.getPassword()+"','"+grade.getSex()+"','"+grade.getName()+"','"+grade.getNumber()+"','"+grade.getMail()+"','"+grade.getXuexiao()+"','"+grade.getXi()+"','"+grade.getBan()+"','"+grade.getNian()+"','"+grade.getArea()+"','"+grade.getBase()+"')";
        //insert语句:
        //insert INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
        //注意:insert这里 values(100,'李泽宇','"+bianliang+"'),数字可以直接上,字符串的话用 '',变量的话要用 '"++"'

        Connection conn=DBUtil.getConn();//数据库连接,加载驱动
        Statement state=null;//数据库操作
        try
        {
            state=conn.createStatement();//实例化Statement对象,方便对sql语句进行操作
            state.executeUpdate(sql);
            //执行数据库更新操作用于执行INSERT、UPDATE或DELETE语句以及SQLDDL(数据定义语言)语句,
            //例如CREATETABLE和DROPTABLE,(创建表和删除表)
        }catch(Exception e)//当try语句中出现异常时,会执行catch中的语句
          {
            e.printStackTrace();//捕获异常的语句
          }
         finally //finally作为异常处理的一部分,它只能用在try/catch语句中,并且附带一个语句块,表示这段语句最终一定会被执行(不管有没有抛出异常),经常被用在需要释放资源的情况下。
         {
             DBUtil.close(state, conn); //close释放资源
         }
        return false;
    }

        //删除
        //根据ID删除



        public  List<Grade> list()
        {
            String sql="select * from test12 order by uesrname ASC";
            //select语句
            //select * from tabel_name   这是查询所有,若要查询某一列
            //select column1_name,column2_name,column3_name from table_name

            List<Grade>list=new ArrayList<>();
            //给集合list创造(new)一个存储空间用于存放数据

            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs=null;

            try
            {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                Grade grade=null;
                while(rs.next()) //这里表示,当rs.next()有值得时候,即收到查询结果
                {
                    String username=rs.getString("username"); //注意:这里用双引号,ID是表grade_student里的ID列
                    String password=rs.getString("password");

                    String sex=rs.getString("sex");
                    String name=rs.getString("name");
                    String number=rs.getString("number");
                    String mail=rs.getString("mail");
                    String xuexiao=rs.getString("xuexiao");
                    String xi=rs.getString("xi");
                    String ban=rs.getString("ban");
                    String nian=rs.getString("nian");

                    String area=rs.getString("area");
                    String base=rs.getString("base");

                    grade=new Grade(username,password,sex,name,number,mail,xuexiao,xi,ban,nian,area,base);
                    list.add(grade); //表示,把bean里的数据存入到list当中
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
            finally
            {
                DBUtil.close(state, conn);
            }
            return list;
        }

        //验证学生是否唯一
        public boolean id(String username) {

            boolean flag = false;

            String sql = "select username from test12 where username = '" + username + "'";
            Connection conn = DBUtil.getConn();
            Statement state = null;
            ResultSet rs = null;

            try {
                state = conn.createStatement();
                rs = state.executeQuery(sql);
                //要用statement类的executeQuery()方法来下达select指令以查询数据库,
                //executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用。
                while (rs.next()) {
                    flag = true;
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBUtil.close(rs, state, conn);
            }
            return flag;
        }



}

service代码

package classtest;

import java.util.List;
import classtest.dao;
import classtest.Grade;

/**
 * CourseService
 * 服务层
 * @author Hu
 *
 */

//service层相当于一个封装层,封装那些与业务相关的通用数据接口

public class service
{
    dao cdao=new dao();
    /**
     * 添加
     * @param course
     * @return
     */
    public boolean insert(Grade grade)
    {
        boolean f=false;
        if(!cdao.id(grade.getUsername()))
        {
            cdao.insert(grade);
            f=true;
        }
        return f;
    }

    //删除



    //全部数据遍历

    public List<Grade>list()
    {
        return cdao.list();
    }
}

servlet代码

package classtest;

import java.io.IOException;
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;

import classtest.Grade;
import classtest.service;

@WebServlet("/servlet")
public class servlet extends HttpServlet //继承的是 HttpServlet方法,和网页交互的方法
{
    private static final long serialVersionUID = 1L;

    service service=new service();

    //方法选择

    protected void service(HttpServletRequest req,HttpServletResponse resp)throws ServletException, IOException
    {
        //req是Request(请求)的简写,指的是从jsp/html界面请求回数据,即 jsp/html → 底层
        //resp是Response(回答)的简写,指的是发送给jsp/html的数据,,即 底层 → jsp/html

        req.setCharacterEncoding("utf-8");//设置从jsp或HTML中请求数据的值,也就是设置为中文,防止乱码
        String method=req.getParameter("method");//getParameter()获取的是客户端设置的数据。
        if ("insert".equals(method)) {
            insert(req, resp);
        }
        //
        else if ("list".equals(method)) {
            list(req, resp);
        }
        //
    }



    //添加

    private void insert(HttpServletRequest req,HttpServletResponse resp)throws IOException, ServletException
    {
        //首先读取前端网页数据并将数据存入我创建的grade存储空间里,即 jsp/html → servlet

        req.setCharacterEncoding("utf-8"); //从网页获得数据
        String username=req.getParameter("username");
        String password=req.getParameter("password");
        String sex=req.getParameter("sex");
        //因为req.getParameter只能获得String类型的数据
        //所以下面需要用Integer.parseInt(req.getParameter())来把String类型的数据转换成我们所需的int数据
        String name=req.getParameter("name");

        String number=req.getParameter("number");
        String mail=req.getParameter("mail");
        String xuexiao=req.getParameter("xuexiao");
        String xi=req.getParameter("xi");
        String ban=req.getParameter("ban");
        String nian=req.getParameter("nian");
        String area=req.getParameter("area");
        String base=req.getParameter("base");


        Grade login=new Grade(username,password,sex,name,number,mail,xuexiao,xi,ban,nian,area,base);


        //其次进行把grade里的数据与添加到数据库里,即 servlet → mysql数据库
        //并跳转,req.getRequestDispatcher("add.jsp").forward(req, resp);这一步是跳转到前端jsp/html界面

        if(service.insert(login)) //如果添加成功
        {
            req.setAttribute("message", "添加成功");
            //这里表示,在req的作用域中设置一个massage变量,变量内容是:添加成功
            //然后,当跳转到它下一个界面时,这个作用域里的值可以通过Object message =request.getAttribute("message")拿出来使用
            req.getRequestDispatcher("index.jsp").forward(req, resp);
            //处理完了,分发到下一个JSP页面或者下一个Action继续处理。
        }
        else
        {
            req.setAttribute("message", "添加重复,请重新输入");
            req.getRequestDispatcher("zhuce.jsp").forward(req, resp);
        }
    }


    //全部

    private void list(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
        req.setCharacterEncoding("utf-8");
        List<Grade> login_list = service.list();
        req.setAttribute("login_list", login_list);
        req.getRequestDispatcher("list.jsp").forward(req,resp);
    }





}

网页版zhuce界面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body >
    <%
         Object message = request.getAttribute("message");
         if(message!=null && !"".equals(message)){

    %>
         <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>");
         </script>
    <%} %>
    <font>当前位置:添加学生信息</font>
<form action="servlet?method=insert" method="post" onsubmit="return check()">
登录账号:<input type="text" id="username"  name="username"><br />
登录密码:<input type="password" id="password" name="password" ><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;性别:<input type="radio"  name="sex" value="男"><input type="radio" name="sex" value="女">女<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;姓名:<input type="text" id="name" name="name"><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;学号:<input type="text" id="number" name="number"><br />
电子邮件:<input type="text" id="mail" name="mail"> <br />
所在学院<input type="text" id="xuexiao" name="xuexiao"><br />
所在系:<input type="text" id="xi" name="xi"><br />
所在班级:<input type="text" id="ban" name="ban"><br />
入学年份:<select name="nian">
<option>请选择</option>
<option>2017</option>
<option>2018</option>
<option>2019</option>
</select>届<br />
生源地:<input type="text" id="area" name="area"><br />
备注:
<textarea name="base">

        </textarea><br />


<div>
<button type="submit" >添&nbsp;&nbsp;&nbsp;加</button>
</div>
</form>
    <script type="text/javascript">


        function check()
        {
            var username = document.getElementById("username");
            var password = document.getElementById("password");
                var mail=     document.getElementById("mail");
            var number=document.getElementById("number");
            //判断是否空
            var res=/^[0-9a-zA-Z]*$/g; //必须是数字和英文组合
            var length1=username.length;
            var myPattern = new RegExp("^[a-zA-Z]");//以英文字母开头
            var usernamelength=document.getElementById("username").value.length;

            if(!((res.test(username.value))&&(myPattern.test(username.value))&&usernamelength>5&&usernamelength<=13))
                {
                alert('用户名必须为字母数字为组成首字母大写');
                username.focus();
                return false;
                }
            var length=document.getElementById("password").value.length;



            if((res.test(password.value)&&length>=8))
            {
                alert('由八位以上英文和数字组成');
                password.focus();
                return false;
            }
            if((number.value[0]!=2||number.value[1]!=0||number.value[2]!=1||number.value[3]!=8)||number.value.length!=8){
                alert("学号格式不正确");
            number.focus();
                return false;
            }

            var kao=/^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(.[a-zA-Z0-9_-])+/;

            if(!(kao.test(mail.value)))
            {alert("邮箱格式不正确");
            mail.focus();
            return false;}

        }

    </script>
</body>
</html>
02-12 19:48