首次尝试连接mysql数据库，并将信息存入，收获颇多。
设计思路：使用 Javascript书写前端网页，servlet项目中写后台,通过正则表达式对输入格式进行判断，完成题目要求
1.网页
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 { public static String db_url = "jdbc:mysql://localhost:3306/shuju"; public static String db_user = "root"; public static String db_pass = "root"; public static Connection getConn () { Connection conn = null; try { Class.forName("com.mysql.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(); } } } public static void main(String[] args) throws SQLException { Connection conn = getConn(); PreparedStatement pstmt = null; ResultSet rs = null; String sql ="select * from user"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ System.out.println("空"); }else{ System.out.println("非空"); } } }
2.格式设计
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> </head> <body> <% Object message = request.getAttribute("message"); if (message != null && !"".equals(message)) { %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <% } %> <table border="1px" cellpadding="10px" cellspacing="0px" style=" 30%;margin:auto;background:rgb(195,195,195)" bordercolor="blue" > <form action="servlet?method=add" method="post" onsubmit="return check()"> <caption>添加学生信息</caption> <tr> <th>登录账号:</th> <td><input type="text" name="username"></th> </tr> <tr> <th>登录密码:</th> <td><input type="password" name="password"></td> </tr> <tr> <th>性别:</th> <td> <select name="sex"> <option value="男">男</option> <option value="女">女</option> </select> </td> </tr> <tr> <th>姓名:</th> <td><input type="text" name="name"></td> </tr> <tr> <th>学号:</th> <td><input type="text" name="num"></td> </tr> <tr> <th>电子邮件:</th> <td><input type="text" name="email"></td> </tr> <tr> <th>所在学院:</th> <td><input type="text" name="xueyuan"></td> </tr> <tr> <th>所在系:</th> <td><input type="text" name="xi"></td> </tr> <tr> <th>所在班级:</th> <td><input type="text" name="ban"></td> </tr> <tr> <th>入学年份:</th> <td> <select name="year"> <option value="1998">1998</option> <option value="1999">1999</option> <option value="2000">2000</option> </select> </td> </tr> <tr> <th>生源地:</th> <td><input type="text" name="address"></td> </tr> <tr> <th>备注:</th> <td><input type="text" name="addment"></td> </tr> <tr> <th colspan="2"> <input type="submit" value="添加"> </th> </tr> </form> </table> </body> </html>
3.设置所需成员
package Entity; public class User { private String tel; private String username; private String password; private String sex; private String name; private String num; private String email; private String xueyuan; private String xi; private String ban; private String year; private String address; private String addment; public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getNum() { return num; } public void setNum(String num) { this.num=num; } public String getBan() { return ban; } public void setBan(String ban) { this.ban=ban; } public String getXueyuan() { return xueyuan; } public void setXueyuan(String xueyuan) { this.xueyuan=xueyuan; } public String getXi() { return xi; } public void setXi(String xi) { this.xi=xi; } public String getAddment() { return addment; } public void setAddment(String addment) { this.addment=addment; } public String getYear() { return year; } public void setYear(String year) { this.year=year; } public String getPassword() { return password; } public void setPassssword(String password) { this.password = password; } 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 getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getTel() { return tel; } public void setTel(String tel) { this.tel = tel; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public User() {} public User(String username,String password,String sex,String name,String num,String email,String xueyuan,String xi,String ban,String year,String address,String addment) { this.username=username; this.password=password; this.sex=sex; this.name=name; this.num=num; this.email=email; this.xueyuan=xueyuan; this.xi=xi; this.ban=ban; this.year=year; this.address=address; this.addment=addment; } }
4.后台运行
package Servlet; import java.io.IOException; 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 Dao.Dao; import Entity.User; import java.io.PrintWriter; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.util.regex.Matcher; import java.util.regex.Pattern; @WebServlet("/servlet") public class servlet extends HttpServlet { private static final long serialVersionUID = 1L; public servlet() { super(); } protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("add".equals(method)) { add(req, resp); } } private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String username = req.getParameter("username"); String password = req.getParameter("password"); String sex = req.getParameter("sex"); String name = req.getParameter("name"); String num = req.getParameter("num"); String email = req.getParameter("email"); String xueyuan = req.getParameter("xueyuan"); String xi = req.getParameter("xi"); String ban = req.getParameter("ban"); String year = req.getParameter("year"); String address = req.getParameter("address"); String addment = req.getParameter("addment"); Pattern p = null; Pattern s = null; Pattern t = null; Matcher m = null; Matcher n = null; Matcher y = null; boolean b = false; boolean c = false; boolean u = false; p = Pattern.compile("2018[0-9]{4}"); m = p.matcher(num); b = m.matches(); s= Pattern.compile("^[A-Za-z][A-Za-z0-9]{5,11}$") ; n = s.matcher(username); c =n.matches(); t= Pattern.compile("^[A-Za-z0-9\\u4e00-\\u9fa5]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$") ; y = t.matcher(email); u =y.matches(); if(!c){ PrintWriter out = resp.getWriter(); out.print("<script>alert('Please enter the user name and start with a letter');window.history.go(-1)</script>"); } else if(!b) { PrintWriter out = resp.getWriter(); out.print("<script>alert('Please start with 2018 and enter 4 digits');window.history.go(-1)</script>"); }else if(!u){ PrintWriter out = resp.getWriter(); out.print("<script>alert('Please enter the correct email');window.history.go(-1)</script>"); } else { User user = new User(username,password,sex,name,num,email,xueyuan,xi,ban,year,address,addment); Dao dao =new Dao(); boolean f=dao.add(user); if(f) { req.setAttribute("message", "注册成功！"); req.getRequestDispatcher("user.jsp").forward(req,resp); } else { req.setAttribute("message", "注册失败！"); req.getRequestDispatcher("user.jsp").forward(req,resp); } } } }
5.连接到数据库
package Dao; import java.sql.Connection; import java.sql.Statement; import DBUtil.DBUtil; import Entity.User; public class Dao { public boolean add(User user) { // TODO Auto-generated method stub String sql = "insert into user1(username,password,sex,name,num,email,xueyuan,xi,ban,year,address,addment) values('"+ user.getUsername() + "','"+ user.getPassword() +"','"+ user.getSex() +"','" + user.getName() +"','"+ user.getNum() +"','"+user.getEmail() +"','"+ user.getXueyuan()+"','"+ user.getXi()+"','"+ user.getBan()+"','"+ user.getYear()+"','"+ user.getAddress()+"','"+ user.getAddment()+"')"; Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a=state.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(state, conn); } if (a>0) { f = true; } return f; } }