JDBC
Introduction to JDBC
What is JDBC?
JDBC (Java Data Base Connectivity) 是 Java 访问数据库的标准规范。是一种用于执行 SQL 语句的 Java API,可以为多种关系数据库提供统一访问,它由一组用 Java 语言编写的类和接口组成。是 Java 访问数据库的标准规范。
想了解更多,欢迎关注我的微信公众号:Renda_Zhang
Mechanism of JDBC
JDBC 是接口,驱动是接口的实现,没有驱动将无法完成数据库连接,从而不能操作数据库。每个数据库厂商都需要提供自己的驱动,用来连接自己公司的数据库,也就是说驱动一般都由数据库生产厂商提供。
JDBC 就是由 sun 公司定义的一套操作所有关系型数据库的规则(接口),而数据库厂商需要实现这套接口,提供数据库驱动 jar 包,我们可以使用这套接口编程,真正执行的代码是对应驱动包中的实现类。
JDBC API
Data Preparation
在 MySQL 中准备好以下数据
CREATE DATABASE db4 CHARACTER SET utf8;
USE db4;
-- 创建 jdbc_user表
CREATE TABLE jdbc_user (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
PASSWORD VARCHAR(50),
birthday DATE
);
-- 添加数据
INSERT INTO jdbc_user (username, PASSWORD,birthday) VALUES('admin1', '123','1991/12/24'), ('admin2','123','1995/12/24'),('test1', '123','1998/12/24'), ('test2', '123','2000/12/24');
MySQL Driver Package
首先将 MySQL 驱动包添加到 jar 包库文件夹 myJar
中,它用于存放当前项目需要的所有 jar 包。然后在 IDEA 的项目中配置 jar 包库的位置。最后创建一个新的模块 jdbc_task01
并添加 jar 包库的依赖。
注册驱动
从 JDBC 3 开始,目前已经普遍使用的版本,可以不用注册驱动而直接使用。
使用反射方法 Class.forName
加载 Driver 类的时候会自动执行 Driver 类的静态代码块里面的注册驱动的代码。
public class JDBCDemo01 {
public static void main(String[] args) throws ClassNotFoundException {
// 注册驱动 forName 方法执行将类进行初始化
Class.forName("com.mysql.jdbc.Driver");
}
}
获得连接
解决插入中文乱码问题:characterEncoding=UTF-8
指定字符的编码、解码格式。
public class JDBCDemo02 {
public static void main(String[] args) throws Exception {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接 url, 用户名, 密码
String url = "jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8";
Connection con = DriverManager.getConnection(url, "root", "root");
}
}
获取语句执行平台
Statement createStatement();
– 创建 SQL 语句执行对象。
int executeUpdate(String sql);
– 执行增删改语句,返回 int
类型,代表受影响的行数。
public class JDBCDemo03 {
public static void main(String[] args) throws Exception {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接 url,用户名, 密码
String url = "jdbc:mysql://localhost:3306/db4";
Connection con = DriverManager.getConnection(url, "root", "root");
// 获取 Statement 对象
Statement statement = con.createStatement();
// 执行创建表操作
String sql = "create table test01(id int, name varchar(20), age int);";
// 增删改操作
int i = statement.executeUpdate(sql);
// 返回值是受影响的函数
System.out.println(i);
// 关闭流
statement.close();
con.close();
}
}
处理结果集
ResultSet executeQuery(String sql);
– 执行查询语句,返回 ResultSet
结果集对象。
只有在进行查询操作的时候,才会处理结果集。
ResultSet
接口作用:封装数据库查询的结果集,对结果集进行遍历,取出每一条记录。
public class JDBCDemo04 {
public static void main(String[] args) throws SQLException {
// 注册驱动可以省略
// 获取连接
String url = "jdbc:mysql://localhost:3306/db4";
Connection con = DriverManager.getConnection(url, "root", "root");
// 获取 Statement对象
Statement statement = con.createStatement();
String sql = "select * from jdbc_user";
// 执行查询操作, 返回的是一个 ResultSet 结果对象
ResultSet resultSet = statement.executeQuery(sql);
// 处理结果集 resultSet
while(resultSet.next()){
//获取id
int id = resultSet.getInt("id");
//获取姓名
String username = resultSet.getString("username");
//获取生日
Date birthday = resultSet.getDate("birthday");
System.out.println(id + " = " +username + " : " + birthday);
}
//关闭连接
resultSet.close();
statement.close();
con.close();
}
}
释放资源
释放原则:先开的后关,后开的先关。
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 注册驱动(省略)
// 获取连接
String url = "jdbc:mysql://localhost:3306/db4";
connection = DriverManager.getConnection(url, "root", "root");
// 获取 Statement对象
statement = connection.createStatement();
String sql = "select * from jdbc_user";
resultSet = statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
/**
* 关闭的顺序是先开后关, 先得到的后关闭,后得到的先关闭
*/
try {
connection.close();
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
步骤总结
- 获取驱动 (可以省略)
- 获取连接
- 获取 Statement 对象
- 处理结果集 (只在查询时处理)
- 释放资源
JDBC CRUD with Custom Utility Class
JDBC Utility Class
如果一个功能经常要用到,建议把这个功能做成一个工具类,可以在不同的地方重用。
“获得数据库连接”操作,将在以后的增删改查所有功能中都存在,可以封装工具类 JDBCUtils
:1. 把几个字符串定义成常量:用户名,密码,URL,驱动类;2. 得到数据库的连接:getConnection()
;3. 关闭所有打开的资源。
/**
* JDBC 工具类
*/
public class JDBCUtils {
// 定义字符串常量, 记录获取连接所需要的信息
public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8";
public static final String USER = "root";
public static final String PASSWORD = "root";
// 静态代码块, 随着类的加载而加载
static{
try {
//注册驱动
Class.forName(DRIVERNAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取连接的静态方法
public static Connection getConnection(){
try {
//获取连接对象
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
//返回连接对象
return connection;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
//关闭资源的方法
public static void close(Connection con, Statement st){
if(con != null && st != null){
try {
st.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection con, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(con,st);
}
}
插入记录
/**
* 插入数据
* @throws SQLException
*/
@Test
public void testInsert() throws SQLException {
// 通过工具类获取连接
Connection connection = JDBCUtils.getConnection();
// 获取 Statement
Statement statement = connection.createStatement();
// 编写 SQL
String sql = "insert into jdbc_user values(null,'布莱尔','123','2020/1/1')";
// 执行 SQL
int i = statement.executeUpdate(sql);
System.out.println(i);
// 关闭流
JDBCUtils.close(connection,statement);
}
更新记录
/**
* 修改 id 为 1 的用户名为张人大
*/
@Test
public void testUpdate() throws SQLException {
Connection connection = JDBCUtils.getConnection();
Statement statement = connection.createStatement();
String sql = "update jdbc_user set username = '张人大' where id = 1";
statement.executeUpdate(sql);
JDBCUtils.close(connection, statement);
}
删除记录
/**
* 删除 id 为 3 和 4 的记录
* @throws SQLException
*/
@Test
public void testDelete() throws SQLException {
Connection connection = JDBCUtils.getConnection();
Statement statement = connection.createStatement();
statement.executeUpdate("delete from jdbc_user where id in(3,4)");
JDBCUtils.close(connection,statement);
}
查询记录
/**
* 查询姓名为张人大的一条记录
* @throws SQLException
*/
@Test
public void testDelete() throws SQLException {
// 获取连接对象
Connection connection = JDBCUtils.getConnection();
// 获取Statement对象
Statement statement = connection.createStatement();
String sql = "SELECT * FROM jdbc_user WHERE username = '张人大';";
ResultSet resultSet = statement.executeQuery(sql);
// 处理结果集
while(resultSet.next()){
// 通过列名 获取字段信息
int id = resultSet.getInt("id");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String birthday = resultSet.getString("birthday");
System.out.println(id+" "+username+" " + password +" " + birthday);
}
// 释放资源
JDBCUtils.close(connection,statement,resultSet);
}
SQL Injection Problem
What is SQL Injection?
SQL 注入:用户输入的密码和 SQL 语句进行字符串拼接时,用户输入的内容作为了 SQL 语句语法的一部分,改变了原有 SQL 真正的意义。
Example
在语句 select * from jdbc_user where username = 'abc' and password = 'abc'
后面加上 or '1'='1'
,那么无论 username
和 password
的值时什么,都会查询了所有记录,然后就会成功登录。
public class TestLogin01 {
/**
* 用户登录案例
* 使用 Statement 字符串拼接的方式完成查询
* @param args
*/
public static void main(String[] args) throws SQLException {
// 获取连接
Connection connection = JDBCUtils.getConnection();
// 获取 Statement
Statement statement = connection.createStatement();
// 获取用户输入的用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名: ");
String name = sc.nextLine();
System.out.println("请输入密码: ");
String pass = sc.nextLine();
System.out.println(pass);
// 拼接 SQL,执行查询
String sql = "select * from jdbc_user " + "where username = " + " '" + name +"' " +" and password = " +" '" + pass +"'";
System.out.println(sql);
ResultSet resultSet = statement.executeQuery(sql);
// 处理结果集,判断结果集是否为空
if(resultSet.next()){
System.out.println("登录成功! 欢迎您: " + name);
}else {
System.out.println("登录失败!");
}
// 释放资源
JDBCUtils.close(connection, statement, resultSet);
}
}
How to Solve this Problem?
要解决 SQL 注入就不能让用户输入的密码和 SQL 语句进行简单的字符串拼接。
Prepared Statement
预处理对象:因为有预先编译的功能,可以提高 SQL 的执行效率,还可以有效的防止 SQL 注入的问题,安全性更高。
PreparedStatement
接口是 Statement 接口的子接口,继承于父接口中所有的方法。它是一个预编译的 SQL 语句对象。
预编译:是指 SQL 语句被预编译,并存储在 PreparedStatement
对象中。然后可以使用此对象多次高效地执行该语句。
public class TestLogin02 {
/**
* 使用预编译对象 PrepareStatement 完成登录案例
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
// 获取连接
Connection connection = JDBCUtils.getConnection();
// 获取用户输入的用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名: ");
String name = sc.nextLine();
System.out.println("请输入密码: ");
String pass = sc.nextLine();
System.out.println(pass);
// 编写 SQL 使用 ? 占位符方式
String sql = "select * from jdbc_user where username = ? and password = ?";
// 获取 PrepareStatement 预编译对象
PreparedStatement ps = connection.prepareStatement(sql);
// 设置占位符参数
ps.setString(1, name);
ps.setString(2, pass);
// 执行查询并处理结果集
ResultSet resultSet = ps.executeQuery();
if(resultSet.next()){
System.out.println("登录成功,欢迎您: " + name);
}else{
System.out.println("登录失败!");
}
// 释放资源
JDBCUtils.close(connection, ps, resultSet);
}
}
Difference between Statement
and PreparedStatement
Statement
用于执行静态 SQL 语句,在执行时,必须指定一个事先准备好的SQL语句。
PrepareStatement
是预编译的 SQL 语句对象,语句中可以包含动态参数 “?”,在执行时可以为 “?” 动态设置参数值。
PrepareStatement
可以减少编译次数提高数据库性能。
JDBC Transaction Control
Data Preparation
在 MySQL 中准备好以下数据
-- 创建账户表
CREATE TABLE account(
-- 主键
id INT PRIMARY KEY AUTO_INCREMENT,
-- 姓名
NAME VARCHAR(10),
-- 转账金额
money DOUBLE
);
-- 添加两个用户
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);
步骤
- 获取连接
- 开启事务
setAutoCommit(false)
- 获取到
PreparedStatement
,执行两次更新操作 - 正常情况下提交事务
commit()
- 出现异常回滚事务
rollback()
- 最后关闭资源
public class JDBCTransaction {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
try {
// 获取连接
con = JDBCUtils.getConnection();
// 开启事务
con.setAutoCommit(false);
// 获取到 PreparedStatement 执行两次更新操作
// tom 账户减去 500
ps = con.prepareStatement("update account set money = money - ? where name = ? ");
ps.setDouble(1,500.0);
ps.setString(2,"tom");
ps.executeUpdate();
// 模拟 tom 转账后出现异常
System.out.println(1 / 0);
// jack 账户增加 500
ps = con.prepareStatement("update account set money = money + ? where name = ? ");
ps.setDouble(1, 500.0);
ps.setString(2, "jack");
ps.executeUpdate();
// 正常情况下提交事务
con.commit();
System.out.println("转账成功");
} catch (SQLException e) {
e.printStackTrace();
try {
// 出现异常回滚事务
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
// 最后关闭资源
JDBCUtils.close(con,ps);
}
}
}