本节将通过一个完整的微型系统来展示 JDBC 的使用,同时也会用到 Servlet 、 JSP 以及 MySQL 的部分知识,系统的功能包括:
- 用户注册
- 用户登录
- 用户信息展示
1. JDBC 简介
百度百科中对 JDBC 的定义如下:
Java数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。
JDBC API 允许用户访问任何形式的表格数据,尤其是存储在关系数据库中的数据。2. JDBC 的使用
2.1 下载驱动
- 要想使用 JDBC,首先需要下载 JDBC 驱动(jar包),链接为:
https://dev.mysql.com/downloads/connector/j/
选择“Platform Independent”
- 不用在意版本,选择“ZIP”下载即可。
- 可以选择登录,抑或是直接下载“No thanks, just start my download.”
- 打开下载的压缩包,将“mysql-connector-java-8.x.xx.jar”文件解压缩,留作备用。
2.2 创建项目
2.2.1 新建项目
创建一个项目名称为“HelloJDBC”的 Dynamic Web Project,
2.2.2 拷贝驱动
将之前解压缩的 jar 包 mysql-connector-java-8.0.25.jar 拷贝到项目中,详细路径如下:
(1)新版 Eclipse:项目/src/main/webapp/WEB-INF/lib。
(2)老版 Eclipse:项目/WebContent/WEB-INF/lib。
少一张截图
注意:Eclipse 会自动加载该路径下的 jar包。
2.3 创建数据库
2.3.1 新建数据库
在 MySQL 中新建与项目同名的数据库“hellojdbc”。
2.3.2 新建表
新建 user 表。
create table user
(
id int,
username varchar(20) null,
password varchar(20) null
)
comment '用户表';
create unique index user_id_uindex
on user (id);
alter table user
add constraint user_pk
primary key (id);
alter table user modify id int auto_increment;
2.4 创建实体类
根据 user 表,创建 User 实体类。
package cn.cyber.entity;
public class User {
private Integer id;
private String username;
private String password;
public User(String username, String password) {
super();
this.username = username;
this.password = password;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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;
}
}
2.5 创建 DBUtil
使用 JDBC 连接数据库,最重要的是要记住步骤(套路):
- 准备连接数据库的资源
- 加载驱动
- 获取连接
- 准备 SQL 语句
- 创建 Statement 对象
- 执行 SQL 语句,保存结果集 ResultSet
- 遍历结果集,输出结果
- 释放资源,关闭连接
万变不离其宗,只要记住了套路,CRUD 轻松拿下!
package cn.cyber.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
public static void main(String[] args) {
/**
* 1.准备连接数据库的资源
*/
// 用户名
String username = "root";
// 密码
String password = "root";
// 数据库地址
String url = "jdbc:mysql://localhost:3306/hellojdbc";
// 驱动名称
String driver = "com.mysql.cj.jdbc.Driver"; // 新版本驱动
// String driver = "com.mysql.jdbc.Driver"; // 老版本驱动
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.加载驱动
Class.forName(driver);
// 3.获取连接
conn = DriverManager.getConnection(url, username, password);
// 4.准备 SQL 语句
String sql = "select * from user";
// 5.创建 Statement 对象
st = conn.createStatement();
// 6.执行 SQL 语句,保存结果集 ResultSet
rs = st.executeQuery(sql);
// 7.遍历结果集,输出结果
while (rs.next()) {
System.out.println("ID= " + rs.getInt("id"));
System.out.println("Username= " + rs.getString("username"));
System.out.println("Password= " + rs.getString("password"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
// 8.释放资源,关闭连接
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2.6 运行测试
2.7 升级改造
2.7.1 在原 DBUtil 上进行升级
package cn.cyber.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtilTmp {
public static void main(String[] args) {
/**
* 1.准备连接数据库的资源
*/
// 用户名
String username = "root";
// 密码
String password = "root";
// 数据库地址
String url = "jdbc:mysql://localhost:3306/hellojdbc";
// 驱动名称
String driver = "com.mysql.cj.jdbc.Driver"; // 新版本驱动
// String driver = "com.mysql.jdbc.Driver"; // 老版本驱动
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
// 2.加载驱动
Class.forName(driver);
// 3.获取连接
conn = DriverManager.getConnection(url, username, password);
// 4.准备 SQL 语句
String sql = "select * from user";
// 5.创建 PreparedStatement 对象
pst = conn.prepareStatement(sql);
// 6.执行 SQL 语句,保存结果集 ResultSet
rs = pst.executeQuery(sql);
// 7.遍历结果集,输出结果
while (rs.next()) {
System.out.println("ID= " + rs.getInt("id"));
System.out.println("Username= " + rs.getString("username"));
System.out.println("Password= " + rs.getString("password"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
// 8.释放资源,关闭连接
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2.7.2 创建真正的 DBUtil
- 把 DBUitl 重命名为 DBUtilTmp。
- 新建 DBUtil。 ```java package cn.cyber.util;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;
public class DBUtil { private static String username = “root”; private static String password = “root”; private static String url = “jdbc:mysql://localhost:3306/hellojdbc”; private static String driver = “com.mysql.cj.jdbc.Driver”;
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void release(Connection conn, PreparedStatement pst, ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (conn != null) {
conn.close();
}
}
}
<a name="JI86g"></a>
## 2.8 运行测试
```java
package cn.cyber.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.cyber.util.DBUtil;
public class DBUtilTest {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from user";
pst = conn.prepareStatement(sql);
rs = pst.executeQuery(sql);
while (rs.next()) {
System.out.println("ID= " + rs.getInt("id"));
System.out.println("Username= " + rs.getString("username"));
System.out.println("Password= " + rs.getString("password"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
DBUtil.release(conn, pst, rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3. 实现登录功能
3.1 前端页面
login.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登录</title>
</head>
<body>
<form action="UserLoginController" method="post">
<div>
<label for="uname">Username: </label>
<input type="text" id="uname" name="user_name">
</div>
<div>
<label for="upwd">Password: </label>
<input type="password" id="upwd" name="pass_word">
</div>
<input type="submit" value="Submit">
</form>
</body>
</html>
3.2 DAO 层
UserDAO.java
package cn.cyber.dao;
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 cn.cyber.entity.User;
import cn.cyber.util.DBUtil;
public class UserDao {
// 增
// 删
// 改
// 查
/**
* 查询所有用户
* - 访问修饰符:public
* - 返回值类型:List<User>
* - 方法名称:getUsers
* - 方法参数:空
*/
public List<User> getUsers() {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
List<User> users = new ArrayList<User>();
try {
// 获取连接
conn = DBUtil.getConnection();
// 准备 SQL 语句
String sql = "select * from user";
// 创建 PreparedStatement 对象
pst = conn.prepareStatement(sql);
// 执行 SQL 语句
rs = pst.executeQuery();
// 遍历 ResultSet,封装对象
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
users.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
DBUtil.release(conn, pst, rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return users;
}
}
3.3 Service 层
UserService.java
package cn.cyber.service;
import java.util.List;
import cn.cyber.dao.UserDao;
import cn.cyber.entity.User;
public class UserService {
private UserDao userDao = new UserDao();
// 查询所有用户
public List<User> getUsers() {
return userDao.getUsers();
}
}
3.4 Controller 层
UserLoginController.java
package cn.cyber.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.cyber.entity.User;
import cn.cyber.service.UserService;
@WebServlet("/UserLoginController")
public class UserLoginController extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
String name = request.getParameter("user_name");
String password = request.getParameter("pass_word");
UserService userService = new UserService();
List<User> users = userService.getUsers();
String url = "fail.html";
for (User user : users) {
if (name.equals(user.getUsername())) {
if (password.equals(user.getPassword())) {
url = "success.html";
break;
}
}
}
request.getRequestDispatcher(url).forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
4. 实现注册功能
4.1 前端页面
register.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>注册</title>
</head>
<body>
<form action="UserRegisterController" method="post">
<div>
<label for="uname">Username: </label>
<input type="text" id="uname" name="user_name">
</div>
<div>
<label for="upwd">Password: </label>
<input type="password" id="upwd" name="pass_word">
</div>
<input type="submit" value="Submit">
</form>
</body>
</html>
4.2 DAO 层
UserDao.java
package cn.cyber.dao;
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 cn.cyber.entity.User;
import cn.cyber.util.DBUtil;
public class UserDao {
private Connection conn = null;
private PreparedStatement pst = null;
private ResultSet rs = null;
// 增
public int addUser(User user) {
int result = 0;
try {
// 获取连接
conn = DBUtil.getConnection();
// 准备 SQL 语句,“?”为占位符,表示此时该值尚不能确定
String sql = "insert into user(username,password) values(?,?)";
// 创建 PreparedStatement 对象
pst = conn.prepareStatement(sql);
// 在执行 SQL 语句前,需要为 SQL 语句中的占位符赋值
pst.setString(1, user.getUsername());
pst.setString(2, user.getPassword());
// 执行 SQL 语句
result = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
DBUtil.release(conn, pst, rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
// 删
// 改
// 查
/**
* 查询所有用户
* - 访问修饰符:public
* - 返回值类型:List<User>
* - 方法名称:getUsers
* - 方法参数:空
*/
public List<User> getUsers() {
List<User> users = new ArrayList<User>();
try {
// 获取连接
conn = DBUtil.getConnection();
// 准备 SQL 语句
String sql = "select * from user";
// 创建 PreparedStatement 对象
pst = conn.prepareStatement(sql);
// 执行 SQL 语句
rs = pst.executeQuery();
// 遍历 ResultSet,封装对象
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
users.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
DBUtil.release(conn, pst, rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return users;
}
}
4.3 Service 层
UserService.java
package cn.cyber.service;
import java.util.List;
import cn.cyber.dao.UserDao;
import cn.cyber.entity.User;
public class UserService {
private UserDao userDao = new UserDao();
// 添加用户
public int addUser(User user) {
return userDao.addUser(user);
}
// 查询所有用户
public List<User> getUsers() {
return userDao.getUsers();
}
}
4.4 Controller 层
UserRegisterController.java
package cn.cyber.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.cyber.entity.User;
import cn.cyber.service.UserService;
@WebServlet("/UserRegisterController")
public class UserRegisterController extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
String username = request.getParameter("user_name");
String password = request.getParameter("pass_word");
User user = new User(username, password);
UserService userService = new UserService();
int result = userService.register(user);
String url = "fail.html";
if (result > 0) {
url = "success.html";
}
request.getRequestDispatcher(url).forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}