[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.项目BuildPath
- 2.导入Maridba的JDBC jar包到工程中,注意如果只想当前工程使用则放入当前工程的WEB-INF,您也可以放在Tomcat中的lib中全部项目均可以使用;
- 3.设置并且导入JDBC_JAVADOC(
用jar库时,鼠标移到对应类、函数的上方,就可以显示对应注释了
),右键jar包Build Path->Configure Build Path
;
WeiyiGeek.JAVA_DOC
- 4.注册驱动必须写上否则找不到驱动
DriverManager.registerDriver(new org.mariadb.jdbc.Driver());
; - 5.连接测试数据库确定无误写用户登录代码示例代码,以及带有JSTL EL的JSP页面如下面所示;
- 6.补充:数据多了对象装,对象多了集合装;
项目结构:
WeiyiGeek.
SQL语句:
代码语言:javascript复制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文件:
代码语言:javascript复制#/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
代码语言:javascript复制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 信息查询结构类
代码语言:javascript复制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
代码语言:javascript复制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 接口实现
代码语言:javascript复制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
代码语言:javascript复制<%@ 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.实例效果