[TOC]

0x00 用户登录

描述:采用Tomcat 7 + JSP + Dao模式数据库(10.1.37-MariaDB) + JSTL(EL) 实现一个简单的登陆验证和显示基础信息;

MariaDB JDBC下载:https://downloads.mariadb.com/Connectors/java/
操作流程:

• 1.新建立动态脚本项目Package包Dao / Servlet 以及工具类Unti；

  

  weiyigeek.top-项目BuildPath

• 2.导入Maridba的JDBC jar包到工程中,注意如果只想当前工程使用则放入当前工程的WEB-INF，您也可以放在Tomcat中的lib中全部项目均可以使用;

• 3.设置并且导入JDBC_JAVADOC(用jar库时，鼠标移到对应类、函数的上方，就可以显示对应注释了)，右键jar包Build Path->Configure Build Path;

  

  weiyigeek.top-JAVA_DOC

• 4.注册驱动必须写上否则找不到驱动DriverManager.registerDriver(new org.mariadb.jdbc.Driver());;
• 5.连接测试数据库确定无误写用户登录代码示例代码,以及带有JSTL+EL的JSP页面如下面所示;
• 6.补充:数据多了对象装，对象多了集合装;

项目结构:

weiyigeek.top-

SQL语句:

CREATE TABLE manager_user (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(32) NOT NULL PRIMARY KEY,
  `password` VARCHAR(32) NOT NULL,
  `age` SMALLINT DEFAULT 0,
  `address` VARCHAR(255) DEFAULT "NULL"
);
INSERT INTO `student`.`manger_user` (`username`, `password`, `age`, `address`) VALUES ('admin', MD5(123456), '19',"中国北京朝阳区");
INSERT INTO `student`.`manger_user` (`username`, `password`, `age`, `address`) VALUES ('guest', MD5(123456), '19',"中国北京朝阳区");

数据库连接字符串以及properties文件:

#/User/src/jdbc.properties
#注意Mariadb驱动注册名称不同于MySQL
mysql.driverClass=org.mariadb.jdbc.Driver
mysql.url=jdbc:mariadb://127.0.0.1:3306/student
mysql.user=root
mysql.pass=

(1) /User/src/top/weiyigeek/Util/db.java

package top.weiyigeek.Util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/***
 * 
 * @Desc: 数据库连接工具类
 * @author WeiyiGeek
 * @CreatTime 下午1:28:57
 */
public class db {
  /**
	 * 预读取Properties设置JDBC连接（注意这里采用Mariadb做为测试）
	 */
  static String driverClass = null;
  static String url = null;
  static String user = null;
  static String pass = null;
  static {

    try {
      Properties prop = new Properties();
      InputStream is = db.class.getClassLoader().getResourceAsStream("jdbc.properties");
      prop.load(is);
      driverClass=prop.getProperty("mysql.driverClass");
      url=prop.getProperty("mysql.url");
      user=prop.getProperty("mysql.user");
      pass=prop.getProperty("mysql.pass");
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      System.out.println("Msg#Properties 文件预读取成功!");
    }
  }
  
  /**
	 * Fun:驱动注册和建立链接
	 * @return Connection
	 */
  public static Connection getConn() throws SQLException {
    Connection conn = null;
    System.out.print("Msg#驱动注册和建立链接:"+url);
    //坑啊:驱动注册必须写上
    DriverManager.registerDriver(new org.mariadb.jdbc.Driver());
    try {
      //防止驱动二次注册自动注册驱动与建立连接		
      conn = DriverManager.getConnection(url, user, pass);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return conn;
  }
  
  /**
	 * Fun:关闭数据库连接并释放资源 （注意点:关闭的顺序）
	 * @param conn
	 * @param st
	 * @param rs
	 */
  public static void release(Connection conn,Statement st, ResultSet rs) {
    closeRs(rs);
    closeSt(st);
    closeConn(conn);
    System.out.println("关闭数据库连接并释放资源");
  }
  
  //私有静态方法-释放查询结果集
  private static void closeRs(ResultSet rs) {
      try {
        if(rs != null)
          rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        rs = null;
      }
    }
    
  //私有静态方法-释放statement对象
  private static void closeSt(Statement st) {
      try {
        if(st != null)
          st.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        st = null;
      }
    }
    
  //私有静态方法-关闭数据库连接
  private static void closeConn(Connection conn) {
    try {
      if(conn != null)
        conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      conn = null;
    }
  }
}

(2) /User/src/top/weiyigeek/entity/UserInfo.java 信息查询结构类

package top.weiyigeek.entity;
public class UserInfo {
  private int id;
  private int age;
  private String username;
  private String password;
  private String address;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public int getAge() {
    return age;
  }
  public void setAge(int age) {
    this.age = age;
  }
  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 getAddress() {
    return address;
  }
  public void setAddress(String address) {
    this.address = address;
  }
}

(3) /User/src/top/weiyigeek/Dao/UserDao.java

package top.weiyigeek.Dao;

import java.sql.SQLException;
import java.util.List;

import top.weiyigeek.entity.UserInfo;

/**
 * 描述:Dao 定义对用户相关请求的数据查询验证
 * 注意:需要集成JDBC
 * @author WeiyiGeek
 *
 */
public interface UserDao {
  /**
	 * Desc:用户登录验证
	 * @return true or False
	 * @throws SQLException 
	 */

  boolean loginVerity(String name, String pass) throws Exception;
  List<UserInfo> infoQuery();
}

(4) /User/src/top/weiyigeek/Dao/impl/User.java 接口实现

package top.weiyigeek.Dao.impl;

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

import top.weiyigeek.Dao.UserDao;
import top.weiyigeek.Util.db;
import top.weiyigeek.entity.UserInfo;

/**
 * @Desc:用户登录验证实现
 * @author WeiyiGeek
 * @CreatTime 下午12:46:54
 */
public class User implements UserDao {

  @Override
  public boolean loginVerity(String name, String pass) throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    try {
      //数据库连接对象
      conn = db.getConn();
      String sql = "SELECT * FROM manager_user WHERE username = ? and password = MD5(?)";
      
      //创建预处理对象
      ps = conn.prepareStatement(sql);
      System.out.println("\n账号:" + name  + ",密码:" + pass);
      ps.setString(1, name);
      ps.setString(2, pass);

      //执行查询返回结果集
      rs = ps.executeQuery();
      
      //判断是否移动下一条判断用户登录信息是否正确
      return rs.next();
    } finally {
      //执行结束释放
      db.release(conn, ps, rs);
    }
  }

  @Override
  public List<UserInfo> infoQuery() {
    ArrayList<UserInfo> info = new ArrayList<UserInfo>();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    try {
      //数据库连接对象
      conn = db.getConn();
      String sql = "SELECT * FROM manager_user";
      
      //创建预处理对象
      ps = conn.prepareStatement(sql);
    
      //执行查询返回结果集
      rs = ps.executeQuery();
      
      //迭代数据
      while(rs.next()) {
        UserInfo ui = new UserInfo();
        ui.setId(rs.getInt("id"));
        ui.setUsername(rs.getString("username"));
        ui.setPassword(rs.getString("password"));
        ui.setAge(rs.getInt("age"));
        ui.setAddress(rs.getString("address"));
        
        info.add(ui);
      }
      
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      //执行结束释放
      db.release(conn, ps, rs);
    }
    
    // TODO Auto-generated method stub
    return info;
  }
}

(5) /User/WebContent/listinfo.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"><title>用户信息</title></head>
<body>
<% 
  //利用Session判断用户是否成功登陆登录则显示，否则不显示
  if(!"ok".equals(session.getAttribute("login"))){
%>
  <script type="text/javascript">alert('您还未登录，请重新登录!');window.location="./Login.jsp";</script>
<% 
  response.sendRedirect("Login.jsp");
  }
%>

<B>管理员信息列表:</B>
<table border="1" width="700">
  <tr>
  <th>序号</th><th>姓名</th><th>密码</th><th>年龄</th><th>地址</th>
  </tr>
  <c:forEach items="${list}" var="user">
  <tr>
    <td>${user.id}</td>
    <td>${user.username}</td>
    <td>${user.password}</td>
    <td>${user.age}</td>
    <td>${user.address}</td>
  </tr>
  </c:forEach>
</table>
</body>
</html>

最终效果:

weiyigeek.top-实例效果