java web jsp页面链接mysql数据库的(增删改查)运用 注册验证

建表建库 JDBC等
同上
表 tbl_user_info 在数据库database2
表里有 username,number,password 三列 全为 string类型

连接数据库
DBUtil.java

package com.imooc.jdbc;

 

import java.sql.Connection;

import java.sql.DriverManager;

 

public class DBUtil {

 
	
	private static final String URI = "jdbc:mysql://localhost:3306/database2?"

			+ "user=root&password=123456&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";

	

	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

	

	public static Connection connectDB() throws Exception {

		//1、加载数据库驱动

		Class.forName(DRIVER);

		//2、获取数据库连接

		Connection conn = DriverManager.getConnection(URI);

		

		return conn;

	}

	

}

用户层
UserVo.java

package com.imooc.jdbc;

 

import java.util.Date;

 

public class UserVO {

	

	private String username;

	private String number;

	private String password;

 



 

	public String getUsername() {

		return username;

	}

 

	public void setUsername(String username) {

		this.username = username;

	}

 
	public String getNumber() {

		return number;

	}
	
	public void setNumber(String number) {

		this.number = number;

	}
	public String getPassword() {

		return password;

	}

	public void setPassword(String password) {

		this.password = password;

	}

	@Override

	public String toString() {

		return "UserVO [username=" + username + ", number=" + number

				+ ", password=" + password + "]";

	}

 

}

Dao层
UserDao.java

package com.imooc.jdbc;


import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mysql.cj.util.Util;


import java.sql.Statement;





public class UserDao {

	

	public void updateUserNumber(UserVO user) throws Exception {

		Connection conn = DBUtil.connectDB();

		String sql = "UPDATE tbl_user_info SET number=?"

				+ " WHERE username=?";

		

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setString(1, user.getNumber());
		pstmt.setString(2, user.getUsername());

		
		pstmt.executeUpdate();

	}

	public void updateUserPassword(UserVO user) throws Exception {

		Connection conn = DBUtil.connectDB();

		String sql = "UPDATE tbl_user_info SET password=?"

				+ " WHERE username=?";

		

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setString(1, user.getPassword());
		pstmt.setString(2, user.getUsername());

		
		pstmt.executeUpdate();

	}
	
	
	
/*
	 
	public boolean selectByNameAPwd(String userName,String userPwd ){
		//加载
		try {
			
			//连接
			Connection conn = DBUtil.connectDB();
			//预执行
			String sql="select * from tbl_user_info where username=? and password=?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1, userName);
			ps.setString(2, userPwd);
			//执行
			ResultSet rs = ps.executeQuery();
			boolean r;
			if(rs.next()){
				 r=true;//登录成功，查询到信息
			}else{
				 r=false;
			}
			//释放
			rs.close();
			ps.close();
			conn.close();
			return r;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	
	*/
	
	
		public boolean selectByNameAPwd(UserVO user ){
			//加载
			try {
				
				//连接
				Connection conn = DBUtil.connectDB();
				//预执行
				String sql="select * from tbl_user_info where username=? and password=?";
				PreparedStatement ps=conn.prepareStatement(sql);
				ps.setString(1, user.getUsername());
				ps.setString(2, user.getPassword());
				//执行
				ResultSet rs = ps.executeQuery();
				boolean r;
				if(rs.next()){
					 r=true;//登录成功，查询到信息
				}else{
					 r=false;
				}
				//释放
				rs.close();
				ps.close();
				conn.close();
				return r;
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
		}
		
		
		public void addUser(UserVO user) throws Exception {

			Connection conn = DBUtil.connectDB();

			String sql = "INSERT INTO tbl_user_info(username,number,password) "

					+ " VALUES(?, ?, ?)";

			

			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, user.getUsername());

			pstmt.setString(2, user.getNumber());

			pstmt.setString(3, user.getPassword());

			

			pstmt.execute();

		}
	
		public void deleteUser(String u) throws Exception {

			Connection conn = DBUtil.connectDB();

			String sql = "DELETE FROM tbl_user_info WHERE username = ?";

	
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, u);

			pstmt.execute();

		}

		public List<UserVO> queryByParams(List<Map<String, Object>> params) throws Exception {

			Connection conn = DBUtil.connectDB();

			StringBuilder sql = new StringBuilder("SELECT * FROM tbl_user_info WHERE 1=1 ");

			

			for(Map<String, Object> param : params) {

				sql.append(" and ");

				sql.append(" " + param.get("col") + " ");

				sql.append(" " + param.get("rel") + " ");

				sql.append(" " + param.get("value") + " ");

			}

		

			

			List<UserVO> userList = new ArrayList<UserVO>();

			

			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql.toString());

			while(rs.next()) {

				UserVO user = new UserVO();


				user.setUsername(rs.getString("username"));
				user.setNumber(rs.getString("number"));
				user.setPassword(rs.getString("password"));


				userList.add(user);

			}

			
			return userList;

		}

	 
	
	
	
}

注册页面（往数据库增加数据）
register.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>


<meta charset="UTF-8">
<title>用户注册</title>


<script type="text/javascript" language = "javascript">
 
    
     function check(){
    	    //验用户名
    	  
    	    	var username = document.getElementById("user").value;
    	        var pwd1 = document.getElementById("pwd1").value;
    	        var pwd2 = document.getElementById("pwd2").value;
    	           	     
    	    	if(username == ""){
    				alert("用户名不能为空");
    				return ;
    				}
    	    	if(pwd1 == ""){
    				alert("密码不能为空");
    				return ;
    				} 	
    	    	else if(pwd1!=pwd2){
    				alert("两次密码不一样");
    				return ;
    				} 
    			
    			document.getElementById("sub1").submit();//方法进行到这里将form的action提交
    	    
       } 
     
 </script>

<body>
<form id="sub1" action="checkregister" method="post">
<table align="center">
        <tr height="150dp">
            <td><h2>创建新账户</h2></td>
        </tr>

<tr>
<td>用户名：</td>
<td> <input type="text" id="user" name="user"></td>
</tr>
<tr>
<td>电话：</td>
<td> <input type="text" name="pnumber"></td>
</tr>
<tr>
<td>密码：</td>
<td> <input type="password" id="pwd1" name="pwd1"></td>
</tr>

<tr>
<td>密码确认：</td>
<td> <input type="password" id="pwd2" name="pwd2"></td>
</tr>
<tr>

 <tr align="center">
<td colspan="2"><input type="button" value="注册" onclick="check()"/>  </td>
</tr>
</table>
</form>


<form action="login.jsp">
<table align="center">
   
         
 <tr align="center">  
<td  ><input type = "submit" value = "登录" /></td>
</tr>

</table>
</form>


</body>
</head>
</html>

提交到servlet验证 调用Dao层函数
checkregister.java

package com.imooc.jdbc;


import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;


import com.imooc.jdbc.UserDao;

@WebServlet(name="checkregister",urlPatterns={"/checkregister"})   

public class checkregister extends HttpServlet{
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
 
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("UTF-8");	 
		PrintWriter out = response.getWriter();
        response.setContentType("text/html);charset=UTF-8");

    
        String user = request.getParameter("user");
        String pwd = request.getParameter("pwd1");
        String phone = request.getParameter("pnumber"); 
       
        UserDao dao = new UserDao();
        UserVO u=new UserVO();
       
        
        u.setUsername(user);
		u.setNumber(phone);
		u.setPassword(pwd);

		try {

			dao.addUser(u);
			response.getWriter().write("注册成功，1秒后回登录页");
			response.setHeader("refresh", "1;url="+request.getContextPath()+"/login.jsp");

		} catch (Exception e) {

			response.getWriter().write("注册失败，1秒后回登录页");
			response.setHeader("refresh", "1;url="+request.getContextPath()+"/login.jsp");
			e.printStackTrace();

		}
	
		
		
		
	}
		
 
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }

}

登陆页面（验证 数据库数据）
login.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
 <title>登录</title>
 
    </head>
   
<meta charset="UTF-8">
<title>登录</title>

 <script type="text/javascript" language = "javascript">
 
     function reloadCode(){
         var time = new Date().getTime();
         document.getElementById("imag").src="authImg?d="+time;
     }
     
     function check(){
    	    //验用户名
    	  
    	    	var username = document.getElementById("user").value;
    	        var pwd = document.getElementById("pwd").value;
    	        
    	  
    	     
    	    
    	     
    	    	if(username == ""){
    				alert("用户名不能为空");
    				return ;
    				}
    	    	if(pwd == ""){
    				alert("密码不能为空");
    				return ;
    				} 	 	    
    	  
    	    
    	    	
    			
    			document.getElementById("sub").submit();//方法进行到这里将form的action提交
    	    
       } 
     
 </script>
<body>
<form id="sub" action="loginServlet" method="post">
<table align="center">


<tr >
<td>用户名：</td>
<td align="left"> <input type="text" placeholder="输入用户名" name="user" id="user"></td>
</tr>


<tr >
<td>密码：</td>
<td align="left"> <input type="password" placeholder="输入密码" name="pwd" id="pwd"></td>
</tr>

<tr >
<td>验证码：</td>
<td align="left"><input type="text" placeholder="输入验证码" name="text" id="text" size="10"/> 
<img  src="authImg" id="imag" onclick="reloadCode()"/>
</td>
</tr>

<tr table align="center">
<td ><input type="button" value="提交" onclick="check()"/>  </td>
<td ><input type="reset"  value="重置"/>  </td>
<td ><input type="button" value="注册" onclick="window.location.href='register.jsp'"/></td>
</tr>

</table>
</form>





<center>
 <% 
        //读取session值
        String val= (String)session.getAttribute("name");
        //如果session不存在
        if(val==null){
            val ="不存在";
        }
        
        out.print("当前\""+val+"\"用户已登录");
        
     
     %>

<table align="center">
<tr>
<td><input type = "button" value = "主页" onclick="window.location.href='index.jsp'"/></td>
</tr>
 </center>
 </table>
</form>


</body>
</html>

登录成功后用户名保存在session里
验证码点击可刷新 servlet代码见 此页面

servlet调用Dao层函数验证数据库是否有用户名
LoginServlet.java

package com.imooc.jdbc;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;


import com.imooc.jdbc.UserDao;

@WebServlet(name="loginServlet",urlPatterns={"/loginServlet"})   //注解名和post的标签名相同

public class LoginServlet extends HttpServlet{
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
 
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("UTF-8");	 
		PrintWriter out = response.getWriter();
        response.setContentType("text/html);charset=UTF-8");

        String imageText = request.getParameter("text");
        String text = (String) request.getSession().getAttribute("image");      
        String user = request.getParameter("user");
        String pwd = request.getParameter("pwd"); 
       
        
        UserVO u=new UserVO();
        u.setUsername(user);
        u.setPassword(pwd);
        
		boolean r=new UserDao().selectByNameAPwd(u);//调用dao层
		
		
		
		
		//页面跳转
		if (!text.equalsIgnoreCase(imageText)){
        	response.getWriter().write("验证码错误,1秒后重新登录");
    		response.setHeader("refresh", "1;url="+request.getContextPath()+"/login.jsp");
        }
		else if(r){
			
			
            //把用户数据保存在session域对象中		
        		request.getSession().setAttribute( "user" , user);
        		
        		 HttpSession session = request.getSession(true);  
        	        session.setAttribute("name", user);
        	        
        		out.println("登陆成功，2秒后到主页");                       
        		response.setHeader("refresh", "1;url="+request.getContextPath()+"/index.jsp"); 
		
	       
			
		}else{
			response.getWriter().write("登陆失败,2秒后重新登录");
    		response.setHeader("refresh", "2;url="+request.getContextPath()+"/login.jsp");   
	       
		}
		
       
    }
 
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }

}

lisi 登陆成功后

登录页可跳转至主页
index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>主页</title>

<style>
body{
    margin:0px;padding:100px;
    text-align:center;
    
}
</style>
</head>
<body>


   <%
    String userna=(String)session.getAttribute("name");
   %>
   <% 
     if(userna==null||"".equals(userna)){
     %>
    
      <table align="center">
      <tr align="center">
      <td >    欢迎！
      <td ><a href="login.jsp">登录</a>
      </td>
      </tr>
     	    	
     <%
     	}else{	
      %>
      <table align="center">
      <tr align="center">
      <td >	欢迎回来 <%=userna %></td>
      	<td ><a href="logout.jsp">注销</a></td>
      	<td ><a href="login.jsp">登录页</a></td>
      <% 
        } 
      %>
  
    
    
     <table align="center">
     <tr align="center">
     <td ><a href="searchServlet?us=<%=userna%>">查询用户信息</a></td>
     </tr>
     </table>
   
</body>
</html>

查询用户信息
提交到servlet 调用Dao层函数
searchServlet.java

package com.imooc.jdbc;


import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;


import com.imooc.jdbc.UserDao;

@WebServlet(name="searchServlet",urlPatterns={"/searchServlet"})   

public class searchServlet extends HttpServlet{
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
 
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("UTF-8");	 
		PrintWriter out = response.getWriter();
        response.setContentType("text/html);charset=UTF-8");

     

        String na = request.getParameter("us"); 
        String name="'%"+na+"%'";
     
        
        UserDao dao = new UserDao();
        
 		List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();

 		Map<String, Object> param1 = new HashMap<String, Object>();

 		param1.put("col", "username");

 		param1.put("rel", "like");

 		param1.put("value", name);

 		params.add(param1);
 		try {

 			List<UserVO> userList = dao.queryByParams(params);
 			 HttpSession session = request.getSession();
 	        session.setAttribute("userList", userList);
 	        response.sendRedirect("show.jsp");
 	        
 			

 		} catch (Exception e) {

 			e.printStackTrace();

 		}
 		
       
        
        
 /*
        UserDao dao = new UserDao();
        
     		List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();

     		Map<String, Object> param1 = new HashMap<String, Object>();

     		param1.put("col", "username");

     		param1.put("rel", "like");

     		param1.put("value", name);

     		params.add(param1);
     		
     	

     		try {

     			List<UserVO> userList = dao.queryByParams(params);


     			for (UserVO user : userList) {
     				
     				out.println(user);
     			}
     			response.setHeader("refresh", "3;url="+request.getContextPath()+"/index.jsp");

     		} catch (Exception e) {

     			e.printStackTrace();

     		}
*/
     		
	}
		
 
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }

}

可跳转到 show.jsp 进行修改数据 删除数据等
show.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 
<%@ page import="com.imooc.jdbc.UserDao" %>    
<%@ page import="com.imooc.jdbc.UserVO" %>
<%@ page import="java.util.List" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>用户信息</title>
</head>
<body>
<%
   
    List<UserVO> users = (List) session.getAttribute("userList");
%>

    <table align="center">
     <tr align="center">
    <td><a href="index.jsp">返回主页</a></td>
    </tr>
    </table>
    
<table border="1" cellspacing="0" align="center" width="500dp"
       height="200dp">
       
    <thead>
   
    <tr>
   
        <td align="center">
            姓名
        </td>
        <td align="center">
            号码
        </td>
          <td align="center">
            密码
        </td>
        <td align="center">
            操作
        </td>
    </tr>
    </thead>
    <tbody>
    <tr>

        <%
            for (UserVO user : users) {
        %>
    
        <td><%=user.getUsername()%>
        </td>
        <td><%=user.getNumber()%>
        </td>
        <td><%=user.getPassword()%>
        </td>
        <td  align="center"> 
        <a href="updateshow.jsp?userN=<%=user.getUsername() %>">更改号码</a>  
        <a href="updatepassword.jsp?userNA=<%=user.getUsername() %>
                                    &&userPA=<%=user.getPassword() %>">更改密码</a>          
          <a href="deleteServlet?userName_1=<%=user.getUsername() %>">删除用户</a>            
                
        </td>
    </tr>
    <%
        }
    %>
   </tbody>
   </table>
   
</body>
</html>

删除用户
在servlet调用Dao层函数
DeleteServlet.java

package com.imooc.jdbc;


import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@WebServlet(name="deleteServlet",urlPatterns={"/deleteServlet"})

public class DeleteServlet extends HttpServlet{
	public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("UTF-8");	 
		PrintWriter out = response.getWriter();
        response.setContentType("text/html);charset=UTF-8");
        
        String u=request.getParameter("userName_1");
		

        UserDao dao = new UserDao();

		try {

			dao.deleteUser(u);

		} catch (Exception e) {

			e.printStackTrace();
		}	
		
		response.getWriter().write("删除成功并注销");
		response.setHeader("refresh", "2;url="+request.getContextPath()+"/logout.jsp");  
		
		
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }


}

跳转至logout.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title></title>
</head>
<body>

<% 
session.removeAttribute("user");
session.invalidate(); 
%> 


<table table align="center">
<tr table align="center">
<td><h3>注销成功</h3><td>
<td ><input type="button" value="登录" onclick="window.location.href='login.jsp'"/></td>
<td ><input type="button" value="主页" onclick="window.location.href='index.jsp'"/></td>
</tr>
</table>


</body>
</html>

更改号码
updateshow.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>更新</title>
</head>

<body>
<form action="updateServlet" method="post">

    <table align="center">
        <tr height="150dp">
            <td></td>
        </tr>
       
        <tr>
            <td>用户名:</td>
            <td><input type="text" name="userN" value="<%=request.getParameter("userN") %>"/></td>
        </tr>
        
        <tr>
            <td>输入新号码：</td>
            <td><input  type="text" name="pnumber"  /></td>
        </tr>
        
        <tr align="center">
            <td colspan="2"><input  type="submit" value="更新"/></td>
        </tr>
    </table>
</form>
</body>
</html>

同上在servlet调用Dao层函数

 UserDao dao= new UserDao();
	        UserVO USer = new UserVO();
	        USer.setUsername(userN);	        
			USer.setNumber(pnumber);

			try {

				dao.updateUserNumber(USer);
			

			} catch (Exception e) {

				e.printStackTrace();

			}

更改密码
updatepassword.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>更新</title>
</head>

<body>
<form action="updatePassword?userNA=<%=request.getParameter("userNA") %>" method="post">

    <table align="center">
        <tr height="150dp">
            <td></td>
        </tr>
       
        
         <tr>
            <td>原密码:</td>
            <td><input type="text"  value="<%=request.getParameter("userPA") %>"/></td>
        </tr>
        <tr>
            <td>输入新密码：</td>
            <td><input  type="text" name="passw"  /></td>
        </tr>
        
        <tr align="center">
            <td colspan="2"><input  type="submit" value="更新"/></td>
        </tr>
    </table>
</form>
</body>
</html>

同上在servlet调用Dao层函数

 String pass=request.getParameter("passw");
	        String userNA = request.getParameter("userNA");
	      
	        UserDao dao= new UserDao();
	        UserVO USer = new UserVO();
	        USer.setUsername(userNA);	        
			USer.setPassword(pass);

			try {

				dao.updateUserPassword(USer);
			

			} catch (Exception e) {

				e.printStackTrace();

			}