[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

    WeiyiGeek.项目BuildPath

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

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

    WeiyiGeek.JAVA_DOC

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

项目结构:

WeiyiGeek.

WeiyiGeek.


SQL语句:

1
2
3
4
5
6
7
8
9
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文件:

1
2
3
4
5
6
#/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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
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 信息查询结构类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 接口实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<%@ 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.实例效果

WeiyiGeek.实例效果