JDBC
学习内容来自B站韩顺平老师的Java基础课
JDBC
概述
- JDBC 为访问不同的数据库提供了统一的接口,为使用者屏蔽了细节问题
- Java 可以通过 JDBC 连接任何提供了 JDBC 驱动程序的数据库系统,从而完成对数据库的各种操作
JDBC 的基本原理
JDBC API
JDBC API 是一系列的接口,它统一和规范了应用程序与数据库的连接、执行 SQL 语句,得到返回结果等各类操作。相关类和接口在 java.sql 与 javax.sql 包中
引入 mysql jar 包
jar 包可以在网上直接下载
JDBC 快速入门
public static void main(String[] args) throws SQLException {
// 1. 注册驱动
// com.mysql.jdbc.Driver 类
Driver driver = new Driver();
// 2. 获取连接
// jdbc:mysql:// 为规定好的协议,即通过 jdbc 连接 mysql
// localhost 为主机 ip,即目的主机 ip
// 3306 为端口号
// db01 为对应数据库
String url = "jdbc:mysql://localhost:3306/db01";
// 将用户名 user 和密码 password 放入 Properties
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "123456");
// 连接
Connection connect = driver.connect(url, properties);
// 3. 执行 sql
// sql 语句
String sql = "insert into actor values(null, 'jerry', '男', '2019-12-1', '123999')";
// String sql = "update actor set name='jerryDog' where id = 1";
// String sql = "delete from actor where id = 1";
// 创建 Statement,用于执行静态 sql 语句并返回结果的对象
Statement statement = connect.createStatement();
// 如果是 dml 语句,返回的是受影响的行数
int lines = statement.executeUpdate(sql);
System.out.println(lines > 0 ? "成功" : "失败");
// 4. 关闭资源
statement.close();
connect.close();
}
数据库连接方式
方式 1
获取 Driver 实现类的对象(即为上一部分的例子)
Driver driver = new com.mysql.jdbc.Driver();
String url = "jdbc:mysql://localhost:3306/db01";
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "123456");
// 连接
Connection connect = driver.connect(url, properties);
方式 2
使用反射动态加载,更加灵活
public void connect02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
// 使用反射加载 Driver 类
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/db01";
// 将用户名 user 和密码 password 放入 Properties
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "123456");
// 连接
Connection connect = driver.connect(url, properties);
System.out.println("connect = " + connect);
}
方式 3
使用 DriverManager 替代 Driver,进行统一管理
public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
// 使用反射加载 Driver 类
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
// 创建 url 和 user、password
String url = "jdbc:mysql://localhost:3306/db01";
String user = "root";
String password = "123456";
// 注册 DriverManager 驱动
DriverManager.registerDriver(driver);
// 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("connection = " + connection);
}
方式 4
使用 Class.forName 自动完成注册驱动,简化代码
public void connect04() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
// 使用反射加载 Driver 类
// 在加载 Driver 类时,其内部有静态代码块已经自动创建了一个对象
/*
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
*/
// 因而注册 Driver 的工作已经自动完成
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
// 创建 url 和 user、password
String url = "jdbc:mysql://localhost:3306/db01";
String user = "root";
String password = "123456";
// 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("connection = " + connection);
}
注意:
- mysql 5.1.6 之后可以无需 Class.forName(“com.mysql.jdbc.Driver”)
- 因为从 jdk 1.5 之后使用了 jdbc4,不再需要显式调用 Class.forName() 注册驱动,而是自动调用驱动程序 jar 包目录 META_INF\services\java.sql.Driver 文件中指定类名来注册
- 仍然建议写上 Class.forName
方式 5(建议使用)
方式 4 升级,使用配置文件,更加灵活的连接数据库
配置文件:
public void connect05() throws IOException, ClassNotFoundException, SQLException {
// 通过 Properties 获取配置文件信息
Properties properties = new Properties();
properties.load(new FileReader("src\\mysql.properties"));
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
// 加载类
Class.forName(driver);
// 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("connection = " + connection);
}
ResultSet
- 通过执行查询数据库的语句生成的数据表(结果集)
- 该数据表可能有多行,初始时指向第一行,可以通过 next 方法移动到下一行,如果没有下一行,再使用 next 会返回 false,因而可以使用 while 循环遍历结果集
例子:
public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
// 通过 Properties 获取配置文件信息
Properties properties = new Properties();
properties.load(new FileReader("src\\mysql.properties"));
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
// 加载类,注册驱动
Class.forName(driver);
// 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("connection = " + connection);
// 创建 Statement
Statement statement = connection.createStatement();
// sql
String sql = "select id, name, sex, birthday from actor";
// 执行查询,获取结果集
ResultSet resultSet = statement.executeQuery(sql);
// 取出结果
while (resultSet.next()) {
// 获取该行各个列数据
int id = resultSet.getInt(1); // 第一列
String name = resultSet.getString(2); // 第二列
String sex = resultSet.getString(3); // 第三列
Date date = resultSet.getDate(4); // 第四列
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date + "\t");
}
// 关闭连接
resultSet.close();
statement.close();
connection.close();
}
debug 可以查看 resultSet 的内容
Statement 与 SQL 注入
Statement 的简单介绍:
- Statement 对象用于执行静态 SQL 语句并返回其生成结果
- 在与数据库建立连接后,若要对数据库进行访问,执行命名或者 SQL 语句,可以通过:Statement、PreparedStatement、CallableStatement
- Statement 对象执行 SQL 语句时,会存在 SQL 注入风险
SQL 注入:
- 利用系统没有对用户输入数据进行检查的漏洞,在输入数据中注入非法的 SQL 语句段或者命令,恶意攻击数据库
- 若要防范 SQL 注入,使用 PreparedStatement 替代即可
比如现在有表 admin,里面有数据如下
使用 select * from admin where xxx
语句时,where 后需要跟上正确的用户密码才显示数据,如select * from admin where name = 'tom' and pwd = '123'
但是,如果使用 sql 注入也能找到该数据,如 where 后加上 NAME = '1' OR' and pwd = 'OR '1'= '1'
即可,这串字符也称为万能密码
这个例子可以用 java 代码来模拟实现:
public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
Scanner scanner = new Scanner(System.in);
// 让用户输入用户名和密码
System.out.println("请输入管理员名字:");
// next() 方法接收到空格或者 ’ 就表示输入结束
// 这里需要用 nextLine() 方法,接收到回车结束
String admin_name = scanner.nextLine();
System.out.println("请输入密码:");
String admin_pwd = scanner.nextLine();
// 通过 Properties 获取配置文件信息
Properties properties = new Properties();
properties.load(new FileReader("src\\mysql.properties"));
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
// 加载类,注册驱动
Class.forName(driver);
// 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("connection = " + connection);
// 创建 Statement
Statement statement = connection.createStatement();
// sql
String sql = "select name, pwd from admin where name = '"+ admin_name +"' and pwd = '" + admin_pwd + "'";
// 执行查询,获取结果集
ResultSet resultSet = statement.executeQuery(sql);
// 如果查到数据,则表示有该管理员
if (resultSet.next()) {
System.out.println("登录成功!");
} else {
System.out.println("登陆失败!");
}
// 关闭连接
resultSet.close();
statement.close();
connection.close();
}
命令行:
请输入管理员名字:
1' OR
请输入密码:
OR '1'= '1
connection = com.mysql.jdbc.JDBC4Connection@579bb367
登录成功!
PreparedStatement
- PreparedStatement 执行的 SQL 语句中的参数用 ?来表示,可以调用 PreparedStatement 的方法 setXXX() 来设置这些参数,其中 XXX 是要设置的参数的类型
- setXXX() 方法有两个参数,第一个是要设置的参数在 SQL 语句中的索引,第二个要设置的值
优点:
- 不需要使用 + 拼接 sql,减少语法错误
- 有效解决 sql 注入问题
- 减少编译次数,效率提高
使用 PreparedStatement 输入万能密码进行测试:
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
// 让用户输入用户名和密码
System.out.println("请输入管理员名字:");
// next() 方法接收到空格或者 ’ 就表示输入结束
// 这里需要用 nextLine() 方法,接收到回车结束
String admin_name = scanner.nextLine();
System.out.println("请输入密码:");
String admin_pwd = scanner.nextLine();
// 通过 Properties 获取配置文件信息
Properties properties = new Properties();
properties.load(new FileReader("src\\mysql.properties"));
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
// 加载类,注册驱动
Class.forName(driver);
// 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("connection = " + connection);
// sql,其中的 ? 就是占位符
String sql = "select name, pwd from admin where name = ? and pwd = ?";
// 得到 PreparedStatement
// 这里的 preparedStatement 是实现了 PreparedStatement 接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 给 ? 赋值
preparedStatement.setString(1, admin_name);
preparedStatement.setString(2, admin_pwd);
// 执行查询,获取结果集
ResultSet resultSet = preparedStatement.executeQuery();
// 如果查到数据,则表示有该管理员
if (resultSet.next()) {
System.out.println("登录成功!");
} else {
System.out.println("登陆失败!");
}
// 1' OR
// OR '1'= '1
// 关闭连接
resultSet.close();
preparedStatement.close();
connection.close();
}
命令行:
请输入管理员名字:
1' OR
请输入密码:
OR '1'= '1
connection = com.mysql.jdbc.JDBC4Connection@579bb367
登陆失败!
使用 PreparedStatement 执行 dml 语句同理:
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
// 让用户输入用户名和密码
System.out.println("请输入管理员名字:");
// next() 方法接收到空格或者 ’ 就表示输入结束
// 这里需要用 nextLine() 方法,接收到回车结束
String admin_name = scanner.nextLine();
System.out.println("请输入密码:");
String admin_pwd = scanner.nextLine();
// 通过 Properties 获取配置文件信息
Properties properties = new Properties();
properties.load(new FileReader("src\\mysql.properties"));
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
// 加载类,注册驱动
Class.forName(driver);
// 连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("connection = " + connection);
// sql,其中的 ? 就是占位符
// String sql = "insert into admin values (?, ?)";
String sql = "update admin set pwd = ? where name = ?";
// 得到 PreparedStatement
// 这里的 preparedStatement 是实现了 PreparedStatement 接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 给 ? 赋值
preparedStatement.setString(1, admin_pwd);
preparedStatement.setString(2, admin_name);
// 执行查询,获取结果集
int lines = preparedStatement.executeUpdate();
// 如果查到数据,则表示有该管理员
if (lines > 0) {
System.out.println("成功!");
} else {
System.out.println("失败!");
}
// 1' OR
// OR '1'= '1
// 关闭连接
preparedStatement.close();
connection.close();
}
命令行:
请输入管理员名字:
jerryDog
请输入密码:
123456
connection = com.mysql.jdbc.JDBC4Connection@579bb367
成功!
JDBC API
以及,
JDBC Utils 开发
可以发现,使用 JDBC 代码操作数据库时,可以分为三个阶段:
- 连接
- sql
- 关闭
而不同功能的代码,往往只有第二部分不同,而连接与关闭的部分大都相同
于是,可以选择开发一个工具类,用于完成连接和关闭,方便 JDBC 的使用
代码:
package com.jdbc.utils;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* @author jerry
* @version 1.0
* 用来完成连接与关闭的工具类
*/
public class JDBCUtils {
// 定义相关属性,因为只需要一份,所以将属性设置为 static
private static String user; // 用户名
private static String password; // 密码
private static String url; // url
private static String driver; // driver
// 在静态代码块中初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\mysql.properties"));
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
driver = properties.getProperty("driver");
} catch (IOException e) {
// 实际开发中,一般抛出运行时异常
// 即将编译异常转化为运行异常,这样方便调用者捕获该异常
// 如果不捕获也会默认处理
throw new RuntimeException(e);
}
}
// 连接数据库,返回 Connection
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// 将编译异常转化为运行异常,这样方便调用者捕获该异常
// 如果不捕获也会默认处理
throw new RuntimeException(e);
}
}
// 关闭资源
/*
待关闭的资源可能有:
1. ResultSet
2. Statement / PreparedStatement,后者继承自前者
3. Connection
*/
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
// 将编译异常转化为运行异常,这样方便调用者捕获该异常
// 如果不捕获也会默认处理
throw new RuntimeException(throwables);
}
}
}
写一个例子测试工具类:
public class JDBCUtilsTest {
@Test
public void testDML() {
Connection connection = null;
// sql
String sql = "update actor set name = ? where id = ?";
// 创建 PreparedStatement 对象
PreparedStatement preparedStatement = null;
try {
// 得到连接
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
// 给占位符赋值
preparedStatement.setString(1, "杰瑞狗");
preparedStatement.setInt(2, 4);
// 执行
int lines = preparedStatement.executeUpdate();
System.out.println(lines > 0 ? "成功!" : "失败!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtils.close(null, preparedStatement, connection);
}
}
@Test
public void testQuery() {
Connection connection = null;
// sql
String sql = "select * from actor";
// 创建 PreparedStatement 对象
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 得到连接
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
// 执行
resultSet = preparedStatement.executeQuery();
// 遍历结果集
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date birthday = resultSet.getDate("birthday");
String phone = resultSet.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + birthday + "\t" + phone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtils.close(resultSet, preparedStatement, connection);
}
}
}
事务
介绍
- JDBC 程序中当一个 Connection 对象创建时,默认情况下是自动提交事务:每次执行一个 SQL 语句,如果执行成功,就会向数据库自动提交,不能回滚
- JDBC 程序中为了让多个 SQL 语句作为一个整体执行,需要使用事务
- 调用 Connection 的 setAutoCommit(false) 方法,可以取消自动提交事务
- 在所有的 SQL 语句执行成功后,调用 commit() 方法提交事务
- 在其中某个操作失败或出现异常时,调用 rollback() 方法回滚事务
事务处理
假设现在需要完成两个账户之间的转账,那么需要完成两件事:
- 账户 A 扣钱 x
- 账户 B 加钱 x
但是,如果程序在 1 执行后,2 执行前,发生了异常,那么就不会执行 2,如:
public void noTransaction() {
Connection connection = null;
// sql
String sql = "update account set balance = balance - 100 where id = 1";
String sql2 = "update account set balance = balance + 100 where id = 2";
// 创建 PreparedStatement 对象
PreparedStatement preparedStatement = null;
try {
// 得到连接
// 默认情况下,每执行一次 sql 都会自动提交
connection = JDBCUtils.getConnection();
// 执行第一条
preparedStatement = connection.prepareStatement(sql);
int lines = preparedStatement.executeUpdate();
// 插入异常指令
int i = 1 / 0;
// 执行第二条
preparedStatement = connection.prepareStatement(sql2);
int lines2 = preparedStatement.executeUpdate();
System.out.println(lines > 0 ? "成功!" : "失败!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtils.close(null, preparedStatement, connection);
}
}
在上面代码中,只扣了 id 为 1 的账户的钱,但是没有给账户 2 加钱
因而需要使用事务来控制
使用事务的例子:
public void useTransaction() {
Connection connection = null;
// sql
String sql = "update account set balance = balance - 100 where id = 1";
String sql2 = "update account set balance = balance + 100 where id = 2";
// 创建 PreparedStatement 对象
PreparedStatement preparedStatement = null;
try {
// 得到连接
// 默认情况下,每执行一次 sql 都会自动提交
connection = JDBCUtils.getConnection();
// 取消自动提交
connection.setAutoCommit(false);
// 执行第一条
preparedStatement = connection.prepareStatement(sql);
int lines = preparedStatement.executeUpdate();
// 插入异常指令
int i = 1 / 0;
// 执行第二条
preparedStatement = connection.prepareStatement(sql2);
int lines2 = preparedStatement.executeUpdate();
// 执行完毕,提交事务
connection.commit();
System.out.println(lines > 0 ? "成功!" : "失败!");
} catch (Exception e) {
e.printStackTrace();
// 出现异常则回滚,撤销执行过的 sql
// 如果未设置回滚点,默认回滚到最初状态
try {
System.out.println("执行时发生异常,撤销已执行的 SQL");
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
// 关闭资源
JDBCUtils.close(null, preparedStatement, connection);
}
}
在上述代码中,使用事务并取消自动提交后,如果执行过程中出错,那么异常会被捕获,进入 catch 语句块,然后进行回滚操作
批处理
当需要成批插入或者更新记录时,就可以采用 Java 的批量更新机制,这个机制允许多条语句一次性提交给数据库批量处理,通常情况下,这样比单独提交更有效率
JDBC 的批处理语句有下列方法:
- addBatch():添加需要批量处理的 SQL 语句或参数
- executeBatch():执行要批量处理的语句
- clearBatch():清空批处理包的语句
注意:
- JDBC 连接 MYSQL 时,如果想要使用批处理功能,必须在 url 中加上参数
?rewriteBatchedStatements=true
- 批处理往往和 PreparedStatement 一起搭配使用,可以减少编译次数,又减少运行次数,提高效率
例子:
先看传统方法插入 5000 条数据
// 传统方法
@Test
public void noBacth() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values (null, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "jerry" + i);
preparedStatement.setString(2, "666");
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("传统方法共执行了:" + (end - start));
JDBCUtils.close(null, preparedStatement, connection);
}
开始执行
传统方法共执行了:206701
使用批处理方法,使用前要在 url 后加上参数
@Test
public static void batch() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values (null, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "jerry" + i);
preparedStatement.setString(2, "666");
// 将 sql 语句加入批处理包中
preparedStatement.addBatch();
// 当有 1000 条记录时,再批量执行
if ((i + 1) % 1000 == 0) {
preparedStatement.executeBatch();
// 清空
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("批处理方法共执行了:" + (end - start));
JDBCUtils.close(null, preparedStatement, connection);
}
开始执行
批处理方法共执行了:1037
可以看到比传统方法比批处理方法慢了将近200倍
查看批处理方法的 addBatch() 语句源码可以看到
- 批量处理底层使用 ArrayList 存储 sql,初始时 ArrayList 大小为 10,超过容量后按照扩容为原容量 1.5 倍
- 因为使用的是 PreparedStatement,所以实际存储的不是整个 sql 语句,而是 sql 语句中 ?对应的参数
- 这样批量处理可以减少网络开销和编译次数,因此效率提高