一、题目：WEB界面链接数据库
1.考试要求：
1登录账号：要求由6到12位字母、数字、下划线组成，只有字母可以开头；（1分）
2登录密码：要求显示“• ”或“*”表示输入位数，密码要求八位以上字母、数字组成。（1分）
3性别：要求用单选框或下拉框实现，选项只有“男”或“女”；（1分）
4学号：要求八位数字组成，前四位为“2018”开头，输入自己学号；（1分）
5姓名：输入自己的姓名；
5电子邮箱：要求判断正确格式xxxx@xxxx.xxxx；（1分）
6点击“添加”按钮，将学生个人信息存储到数据库中。（3分）
7可以演示连接上数据库。（2分）
二、设计思路：
1、首先在users.jsp代码中，用HTML写一个添加表单即可
2、在DBUtil.java文件编写中对数据库进行连接和关闭的操作
3、在Dao.java文件中编写向数据库中插入数据的SQL语句，以及判断Servelet.java中传递的数据是否为空。
4、在 users.jsp 文件中对表单用javascript进行form表单校验，将满足所有添加要求的数据传入Servelet.java中，用post方式传递。
5、在Selvlet.java 文件调用DBUtil.java文件和Dao.java文件，得到Dao.java中判断Servelet.java中传递的数据是否为空的函数，并进行二次表单校验。
6、Servelet.java中若数据合理，调用DBUtil.java的方法，连接数据库将数据通过参数的形式传递给在Dao.java文件中向数据库中插入数据的SQL语句，将数据添加到数据库。若添加成功，给出提示信息并返回users.jsp代码中action跳转的Servlet?method=add页面。若添加失败，给出提示信息并返回users.jsp代码中action跳转的Servlet?method=add页面。
三、源代码
1、Dao.java文件
1 package Dao;
2
3 import java.sql.Connection;
4 import java.sql.Statement;
5
6 import DBUtil.DBUtil;
7
8 import Entity.User;
9
10 public class Dao {
11
12 public boolean add(User user) {
13 //将数据插入数据库的SQL语句
14 String sql = "insert into uses1 (username,password,sex,name,stuname,email,xueyuan,xi,banji,year,address,addtext) values('"+ user.getUsername() + "','"+ user.getPassword() +"','"+ user.getSex() +"','"+user.getName() +"','"+ user.getStuname() +"','"+user.getEmail()+"','"+ user.getXueyuan() +"','"+user.getXi()+"','"+user.getBanji()+"','"+user.getYear()+"','"+user.getAddress()+"','"+user.getAddtext()+"')";
15 // 创建数据库链接
16 Connection conn = DBUtil.getConn();
17 Statement state = null;
18 boolean f = false;
19 int a = 0;
20
21 try {
22 state = conn.createStatement();
23 a=state.executeUpdate(sql);
24 } catch (Exception e) {
25 e.printStackTrace();
26 } finally {
27 // 关闭z 连接
28 DBUtil.close(state, conn);
29 }
30
31 if (a > 0) {
32 f = true;
33 }
34 return f;
35
36 }
37
38
39 }
2、DBUtil.java文件
1 package DBUtil;
2
3
4
5 import java.sql.Connection;
6 import java.sql.DriverManager;
7 import java.sql.PreparedStatement;
8 import java.sql.ResultSet;
9 import java.sql.SQLException;
10 import java.sql.Statement;
11
12 /**
13 * @author Hu
14 *
15 */
16 public class DBUtil {
17
18 public static String db_url = "jdbc:mysql://localhost:3306/user?serverTimezone=UTC";//登录数据库
19 public static String db_user = "root";
20 public static String db_pass = "123";
21
22 public static Connection getConn () {
23 Connection conn = null;
24
25 try {
26 Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动
27 conn = DriverManager.getConnection(db_url, db_user, db_pass);
28 } catch (Exception e) {
29 e.printStackTrace();
30 }
31
32 return conn;
33 }
34 /**
35 * 关闭与数据库的连接
36 * @param state
37 * @param conn
38 */
39 public static void close (Statement state, Connection conn) {
40 if (state != null) {
41 try {
42 state.close();
43 } catch (SQLException e) {
44 e.printStackTrace();
45 }
46 }
47
48 if (conn != null) {
49 try {
50 conn.close();
51 } catch (SQLException e) {
52 e.printStackTrace();
53 }
54 }
55 }
56
57 public static void close (ResultSet rs, Statement state, Connection conn) {
58 if (rs != null) {
59 try {
60 rs.close();
61 } catch (SQLException e) {
62 e.printStackTrace();
63 }
64 }
65
66 if (state != null) {
67 try {
68 state.close();
69 } catch (SQLException e) {
70 e.printStackTrace();
71 }
72 }
73
74 if (conn != null) {
75 try {
76 conn.close();
77 } catch (SQLException e) {
78 e.printStackTrace();
79 }
80 }
81 }
82 public static void main(String[] args) throws SQLException {
83 Connection conn = getConn();
84 PreparedStatement pstmt = null;
85 ResultSet rs = null;
86 String sql ="select * from users";
87 pstmt = conn.prepareStatement(sql);
88 rs = pstmt.executeQuery();
89 if(rs.next()){
90 System.out.println("空");
91 }else{
92 System.out.println("不空");
93 }
94 }
95 }
3、User.java(用户属性类)文件
1 package Entity;
2
3 public class User {
4
5 private String username;
6 private String password;
7 private String sex;
8 private String name;
9 private String stuname;
10 private String email;
11 private String xueyuan;
12 private String xi;
13 private String banji;
14 private String year;
15 private String address;
16 private String addtext;
17
18 public String getUsername() {
19 return username;
20 }
21
22 public void setUsername(String username) {
23 this.username = username;
24 }
25
26 public String getPassword() {
27 return password;
28 }
29
30 public void setPassword(String password) {
31 this.password = password;
32 }
33
34 public String getSex() {
35 return sex;
36 }
37
38 public void setSex(String sex) {
39 this.sex = sex;
40 }
41
42 public String getName() {
43 return name;
44 }
45
46 public void setName(String name) {
47 this.name = name;
48 }
49
50 public String getStuname() {
51 return stuname;
52 }
53
54 public void setStuname(String stuname) {
55 this.stuname = stuname;
56 }
57
58 public String getEmail() {
59 return email;
60 }
61
62 public void setEmail(String email) {
63 this.email = email;
64 }
65
66 public String getXueyuan() {
67 return xueyuan;
68 }
69
70 public void setXueyuan(String xueyuan) {
71 this.xueyuan = xueyuan;
72 }
73
74 public String getXi() {
75 return xi;
76 }
77
78 public void setXi(String xi) {
79 this.xi = xi;
80 }
81
82 public String getBanji() {
83 return banji;
84 }
85
86 public void setBanji(String banji) {
87 this.banji = banji;
88 }
89
90 public String getYear() {
91 return year;
92 }
93
94 public void setYear(String year) {
95 this.year = year;
96 }
97
98 public String getAddress() {
99 return address;
100 }
101
102 public void setAddress(String address) {
103 this.address = address;
104 }
105
106 public String getAddtext() {
107 return addtext;
108 }
109
110 public void setAddtext(String addtext) {
111 this.addtext = addtext;
112 }
113
114 public User() {}
115
116 public User(String username,String password,String sex,String name,String stuname,String email,String xueyuan, String xi,String banji,String year,String address,String addtext) {
117
118
119 this.username=username;
120 this.password=password;
121 this.sex=sex;
122 this.name=name;
123 this.stuname=stuname;
124 this.email=email;
125 this.xueyuan=xueyuan;
126 this.xi=xi;
127 this.banji=banji;
128 this.year=year;
129 this.address=address;
130 this.addtext=addtext;
131
132 }
133
134
135 }
4、Servlet.java文件
1 package Servlet;
2
3 import java.io.IOException;
4
5 import javax.servlet.ServletException;
6 import javax.servlet.annotation.WebServlet;
7 import javax.servlet.http.HttpServlet;
8 import javax.servlet.http.HttpServletRequest;
9 import javax.servlet.http.HttpServletResponse;
10
11 import Dao.Dao;
12
13 import Entity.User;
14
15
16
17
18 @WebServlet("/Servlet")
19 public class Servlet extends HttpServlet {
20 private static final long serialVersionUID = 1L;
21
22
23 public Servlet() {
24 super();
25
26 }
27 protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
28 req.setCharacterEncoding("utf-8");
29 String method = req.getParameter("method");
30 if ("add".equals(method)) {
31 add(req, resp);
32 }
33 }
34
35
36 public void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
37 req.setCharacterEncoding("utf-8");
38 String username = req.getParameter("username");
39 String password = req.getParameter("password");
40 String sex = req.getParameter("sex");
41 String name = req.getParameter("name");
42 String stuname = req.getParameter("stuname");
43 String email = req.getParameter("email");
44 String xueyuan = req.getParameter("xueyuan");
45 String xi= req.getParameter("xi");
46 String banji= req.getParameter("banji");
47 String year= req.getParameter("year");
48 String address= req.getParameter("address");
49 String addtext= req.getParameter("addtext");
50 //调用用户属性类
51 User user = new User(username,password,sex,name,stuname,email,xueyuan,xi,banji,year,address,addtext);
52 //接受判断函数返回值
53 Dao dao =new Dao();
54 boolean f=dao.add(user);
55 //提示信息
56 if(f) {
57 req.setAttribute("message", "注册成功！");
58 req.getRequestDispatcher("users.jsp").forward(req,resp);
59 }
60 else {
61 req.setAttribute("message", "已有账号，重复登录！");
62 req.getRequestDispatcher("users.jsp").forward(req,resp);
63 }
64 }
65 }
5、users.jsp文件
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3 <!DOCTYPE html>
4 <html>
5 <head>
6 <meta charset="UTF-8">
7 <title>Insert title here</title>
8 <script>
9 //表单校验
10 function check() {
11 flag=0;
12 var rep = /^[wu4e00-u9fa5]{6,8}$/; //判断字符串是否为数字和字母组合
13 var myPattern = new RegExp("^[a-zA-Z]"); // 以英文字母开头
14 var username = document.getElementById("username").value;
15 //alert(username.length);
16 if(!(username.length>5&&username.length<13)){
17 alert("用户名长度错误！");return false;
18 }
19 else if(!(rep.test(username))){
20 alert("用户名组成内容错误！");return false;
21 }else if(!(myPattern.exec(username))){
22 alert("用户名开头必须是字母！");return false;
23 }
24 var password = document.getElementById("password").value;
25 if(password.length<8){
26 alert("密码长度错误");
27 return false;
28 }
29
30 var stuname = document.getElementById("stuname").value;
31 if(stuname.length!=8){
32 alert("学号长度错误！");
33 return false;
34 }
35 if(stuname [0]=='2'&&stuname[1]=='0'&&stuname[2]=='1'&&stuname[3]=='8'){
36 flag++;
37 }
38 else{
39 alert("学号格式错误！");
40 return false;
41 }
42
43 var email = document.getElementById("email").value;
44 if(/^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(.[a-zA-Z0-9_-])+/.test(email)){
45 flag++;
46 }
47 else{
48 alert("邮箱格式错误！");
49 return false;
50 }
51 //标志法判断是否添加成功
52 if(flag>1){
53 alert("添加成功");
54 return true;
55 }
56 else{
57 alert("添加失败");
58 return false;
59 }
60 }
61 </script>
62 </head>
63
64 <body>
65
66
67
68 <table border="0px" cellpadding="10px" cellspacing="0px"
69 style=" 50%;margin:auto;background:rgb(195,195,195)" bordercolor="red" >
70 <form action="Servlet?method=add" method="post"
71 onsubmit="return check()">
72 <caption>当前位置：添加学生信息</caption>
73
74
75 <tr>
76 <td>登录账号:</td>
77 <td><input type="text" name="username" id="username"></td>
78 </tr>
79 <tr>
80 <td>登录密码:</td>
81 <td><input type="password" name="password" id="password" ></td>
82 </tr>
83 <tr>
84 <td>性别:</td>
85 <td>
86 <select name="sex">
87 <option value="男">男</option>
88 <option value="女">女</option>
89 </select>
90 </td>
91 </tr>
92 <tr>
93 <td>姓名:</td>
94 <td><input type="text" name="name"></td>
95 </tr>
96 <tr>
97 <td>学号:</td>
98 <td><input type="text" name="stuname" id="stuname"></td>
99 </tr>
100 <tr>
101 <td>电子邮箱:</td>
102 <td><input type="text" name="email" id="email"></td>
103 </tr>
104 <tr>
105 <td>所在学院:</td>
106 <td><input type="text" name="xueyuan"></td>
107 </tr>
108 <tr>
109 <td>所在系:</td>
110 <td><input type="text" name="xi"></td>
111 </tr>
112 <tr>
113 <td>所在班级:</td>
114 <td><input type="text" name="banji"></td>
115 </tr>
116 <tr>
117 <td>入学年份(届):</td>
118 <td>
119 <select name="year">
120 <option value="1998">1998</option>
121 <option value="1999">1999</option>
122 <option value="2000">2000</option>
123 <option value="2001">2001</option>
124 <option value="2002">2002</option>
125 <option value="2003">2003</option>
126 <option value="2004">2004</option>
127 <option value="2005">2005</option>
128 <option value="2006">2006</option>
129 <option value="2007">2007</option>
130 <option value="2008">2008</option>
131 <option value="2009">2009</option>
132 <option value="2010">2010</option>
133 <option value="2011">2011</option>
134 <option value="2012">2012</option>
135 <option value="2013">2013</option>
136 <option value="2014">2014</option>
137 <option value="2015">2015</option>
138 <option value="2016">2016</option>
139 <option value="2017">2017</option>
140 <option value="2018">2018</option>
141 <option value="2019">2019</option>
142 </select>
143 </td>
144 </tr>
145 <tr>
146 <td>生源地:</td>
147 <td><input type="text" name="address"></td>
148 </tr>
149 <tr>
150 <td>
151 备注:
152 </td>
153 <td>
154 <input type="text" name="addtext" >
155 </td>
156 </tr>
157 <tr>
158 <th colspan="2">
159 <input type="submit" value="添加">
160 </th>
161 </tr>
162 </form>
163 </table>
164 </body>
165 </html>
四、程序测试
