密保问题数据库设计思路和代码实现

最新推荐文章于 2023-03-24 21:17:25 发布

小秋蜀黍

最新推荐文章于 2023-03-24 21:17:25 发布

阅读量3.7k

点赞数 1

分类专栏： mysql java-web ajax

本文链接：https://blog.csdn.net/sinat_32133675/article/details/77363212

版权

java-web 同时被 3 个专栏收录

16 篇文章 0 订阅

订阅专栏

mysql

9 篇文章 0 订阅

订阅专栏

ajax

4 篇文章 0 订阅

订阅专栏

用户注册时设置三个密保问题方便用户找回。

数据库设计思路：

用户一张表，用户密保问题答案一张表，密保问题单独一张表。这样做的好处是满足三范式，条理清晰，跟重要的是方便后期更改维护密保问题，比如增加删除某些密保问题。

代码实现页面端的代码：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'x1.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  <script type="text/javascript" src="js/jq.js"></script></head>
  
  <body>
  
      <table>
          <tr>
             <td>手机或邮箱</td>
             <td><input type="text" name="teleoremail" id="teleoremail"/>
                 <span id="x1"></span>
             </td>
          </tr>
          <tr>
             <td>用户密码</td>
             <td><input type="text" name="userpwd" id="userpwd"/>
                 <span id="x2"></span></td>
          </tr>
          <tr>
             <td>密保问题1：</td>
             <td>
                <select name="one">
                <c:forEach items="${listquestion}" var="ques1">
                   <option value="${ques1.id}">${ques1.qusetions}</option>
                </c:forEach>   
                </select>
             </td>
          </tr>
          <tr>
              <td>答案</td>
              <td><input type="text" name="answer1" id="answer1"/>
              <span id="x3"></span></td>
          </tr>
          <tr>
             <td>密保问题2：</td>
             <td>
                <select name="two">
                <c:forEach items="${listquestion}" var="ques1">
                   <option value="${ques1.id}">${ques1.qusetions}</option>
                </c:forEach>   
                </select>
             </td>
          </tr>
          <tr>
              <td>答案</td>
              <td><input type="text" name="answer2" id="answer2"/>
              <span id="x4"></span></td>
          </tr>
          <tr>
             <td>密保问题3：</td>
             <td>
                 <select name="three">
                <c:forEach items="${listquestion}" var="ques1">
                   <option value="${ques1.id}">${ques1.qusetions}</option>
                </c:forEach>   
                </select>
             </td>
          </tr>
          <tr>
              <td>答案</td>
              <td><input type="text" name="answer3" id="answer3"/>
              <span id="x5"></span></td>
          </tr> 
      </table>
      <input type="button" value="提交"/>
  
  ========${listquestion}
  </body>
  <script type="text/javascript">
      /* $("#teleoremail").focus(function(){
          $("#x1").html("");
      }); */
      $("input[type='text']").click(function(){
         $(this).parent().find("span").html("");
      });
      $("input[type='text']").not("input:eq(0)").blur(function(){
         if($(this).val()==""){
            $(this).parent().find("span").html("不能为空！");
         }
      });
      $("#teleoremail").blur(function(){
             //alert("111");
             var te =$("#teleoremail").val();
             if(te.match(/^[\d]{11,20}$/)){
                 $.get("findtelephone.action",{"telephone":te},function(k){
                     if(k==1){
                        $("#x1").html("该手机已被注册！请重新输入！");
                     }
                 });
             }else if(te.match(/^[\w-]+(\.[\w-]+)*@[\w-]+(\.[\w-]+)+$/)){
                $.get("findtemail.action",{"email":te},function(k){
                     if(k==1){
                        $("#x1").html("该邮箱已被注册！请重新输入！");
                     }
                 });
             }else if(te==""){
                   $("#x1").html("注册信息不能为空！");
             }else{
                   $("#x1").html("格式不正确！");
             }
      });
      $("input[type='button']").click(function(){
             //alert($("select[name='one']").val());
             
           $.ajax({    
            url : "regin.action",  
            type : "post",  
            data : { q1: $("select[name='one']").val(),
                     q2: $("select[name='two']").val(),
                     q3: $("select[name='three']").val(),
                     ans1:$("#answer1").val(),
                     ans2:$("#answer2").val(),
                     ans3:$("#answer3").val(),
                     teleoremail:$("#teleoremail").val(),
                     userpwd:$("#userpwd").val()
                   }, 
             success:function(){window.open("x2.jsp","_parent")}    //枚有"_parent"则打开一个新网页，添加后实现跳转功能！     
        });
        
      });
  </script>
</html>

后台代码mapper接口

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
<mapper namespace="it.com.dao">  
   
   <select id="selectAllQuestion" resultType="Map">
      select qusetions,id from question
   </select>
   <select id="selectTelephone" resultType="Map" parameterType="String">
      select telephone from user where telephone=#{telephone}
   </select> 
   <select id="selectEmail" resultType="Map" parameterType="String">
      select email from user where email =#{email}
   </select>  
</mapper>

实现类

package it.com.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

public class UserDao {
    
	SqlSessionFactory sf;
	
	public List<Map> findAllQuestion(){
		SqlSession session = sf.openSession();
		List<Map> list =null;
		list=session.selectList("selectAllQuestion");
		session.commit();
		session.close();
		return list;
	}
	//验证电话号码是否存在
	public List<Map> findTelephone(String telephone){
		SqlSession session = sf.openSession();
		List<Map> list = session.selectList("selectTelephone",telephone);
		session.commit();
		session.close();
		return list;
	}
	//验证邮箱是否存在
	public List<Map> findEmail(String email){
		SqlSession session=sf.openSession();
		List<Map> list = session.selectList("selectEmail",email);
		session.commit();
		session.close();
		return list;
	}
	public SqlSessionFactory getSf() {
		return sf;
	}
	public void setSf(SqlSessionFactory sf) {
		this.sf = sf;
	}
	
}

action类

package it.com.action;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import java.util.Map;

import it.com.dao.UserDao;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class UserAction {
@Resource
UserDao user; 
@RequestMapping(value="frist.action")
public ModelAndView finduser(HttpSession session){
	List<Map> listquestion = user.findAllQuestion();
	//System.out.println(listquestion);
	session.setAttribute("listquestion", listquestion);
	return new ModelAndView("x1.jsp");
}
//验证电话号码是否存在
@RequestMapping(value="findtelephone.action")
public void findTelephone(HttpServletRequest request,HttpServletResponse response) throws IOException{
	String telephone = request.getParameter("telephone");
	//System.out.println("===="+telephone);
	List<Map> list = user.findTelephone(telephone);
	//System.out.println("xxxxx"+list);
	int n=0;
	if(list.size()!=0){
		n=1;
	}
	PrintWriter out = response.getWriter();
	out.print(n);
 }
//验证邮箱是否存在
@RequestMapping(value="findtemail.action")
public void findEmail(HttpServletRequest request,HttpServletResponse response) throws IOException{
	String email = request.getParameter("email");
	//System.out.println("===="+email);
	List<Map> list = user.findEmail(email);
	//System.out.println("xxxxx"+list);
	int n=0;
	if(list.size()!=0){
		n=1;
	}
	PrintWriter out = response.getWriter();
	out.print(n);
}
@RequestMapping(value="regin.action")
public ModelAndView regin(String teleoremail,String userpwd,HttpServletRequest request,HttpServletResponse response){
	System.out.println(teleoremail+" "+userpwd);
	
	
	String q1 =request.getParameter("q1");
	String q2 = request.getParameter("q2");
	String q3 = request.getParameter("q3");
	System.out.println(q1+" "+q2+" "+q3);
	return new ModelAndView("x2.jsp");
}
}

结果展示：

分析：逻辑验证的业务，好像可以用动态sql。我为了前端ajax验证数据类型的准确性，用js正则将类型划分，跳转不同的action。