第25章 JDBC和数据库连接池(P821 - P857) 821. JDBC原理示意图韩顺平循序渐进学Java零基础第25章
-
JDBC 为访问不同的数据库提供了统一的接口，为使用者屏蔽了使用细节。Java 程序员使用 JDBC API 可以连接任何提供了 JDBC 驱动程序的数据库系统，从而完成对数据库的各种操作
-
Java 设计者定义了操作数据库的接口规范，由各自数据库厂商具体实现。Java 程序员只需要面向这套接口编程即可。
-
JDBC API 是一系列的接口，它统一和规范了应用程序与数据库的连接、执行 SQL 语句，并得到返回结果等各类操作，相关的接口和类在 java.sql 和 javax.sql 包中
-
JDBC 程序编写步骤：
- 注册驱动
- 获取连接
- 执行 SQL
- 释放资源
-
数据库连接方式1：
package p823;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class Jdbc01 {
public static void main(String[] args) throws SQLException {
// 连接到的数据库：jdbc:mysql://主机IP地址:端口/db_name
String url = "jdbc:mysql://localhost:3306/temp";
// 设置用户名与密码
Properties properties = new Properties();
properties.setProperty("user", "springbear");
properties.setProperty("password","123");
// 加载驱动: new com.mysql.jdbc.Driver()
Driver driver = new Driver();
// 获得连接
Connection connect = driver.connect(url, properties);
// SQL 语句
String insert = "INSERT INTO actor VALUES (NULL,'张三','男','1970-01-01','10086');";
// 得到一个执行静态 SQL 语句并返回其生成的结果的对象
Statement statement = connect.createStatement();
// 返回受影响的行数
int rows = statement.executeUpdate(insert);
System.out.println("返回受影响的行数 = " + rows);
statement.close();
connect.close();
}
}
824. 数据库连接方式2
public void connect02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
// 连接到的数据库：jdbc:mysql://主机IP地址:端口/db_name
String url = "jdbc:mysql://localhost:3306/temp";
// 设置用户名与密码
Properties properties = new Properties();
properties.setProperty("user", "springbear");
properties.setProperty("password","123");
// 加载类信息
Class aClass = Class.forName("com.mysql.jdbc.Driver");
// 获得类实例
Driver driver = (Driver)aClass.newInstance();
// 获得连接
Connection connect = driver.connect(url, properties);
}
825. 数据库连接方式3
public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
// 加载类信息
Class aClass = Class.forName("com.mysql.jdbc.Driver");
// 获得类实例
Driver driver = (Driver)aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/temp";
String user = "springbear";
String pwd = "123";
// 注册驱动
DriverManager.registerDriver(driver);
// 获得连接
Connection connection = DriverManager.getConnection(url, user, pwd);
}
826. 数据库连接方式4
public void connect04() throws ClassNotFoundException, SQLException {
// 加载类信息，在加载 Driver 的过程中自动完成注册
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/temp";
String user = "springbear";
String pwd = "123";
// 获得连接
Connection connection = DriverManager.getConnection(url, user, pwd);
}
827. 数据库连接方式5
- mysql-connector-java-5.1.37-bin.jar 驱动文件 5.1.6 之后无需 Class.forName(“com.mysql.jdbc.Driver”) 也可以直接获得连接。原因：从 jdk5 以后使用了 jdbc4，不再需要显式调用 Class.forName() 注册驱动，而是自动调用驱动 jar 包下的 meta-INFservicesjava.sql.Driver 文本中的类名称去注册
public void connect05() throws IOException, ClassNotFoundException, SQLException {
// 从配置文件中读取信息
Properties properties = new Properties();
properties.load(new FileInputStream("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);
}
828. ResultSet底层
- ResultSet：表示从数据库读取到的数据表的结果集。ResultSet 对象保持一个光标指向当前的数据行。最初，光标位于第一行之前。其有一个 next 方法将光标移动到下一行，并且由于在 ResultSet 对象中没有更多行时返回 false，因此常用 while 循环来遍历结果集
- com.mysql.jdbc.JDBC42 ResultSet 类下有一个 RowData(接口) 类型的字段，rowData 中有一个 ArrayList 类型的集合 rows，rows 中又有 byte[] 类型的 internalRowData，数据真正存储的位置
- Statement 对象，用于执行静态 SQL 语句并返回其生成结果的对象
- 在建立连接之后，想要对数据库进行操作，一般有以下三种方式：
- Statement：存在 SQL 注入
- PreparedStatement：预处理
- CallableStatement：用于执行数据库存储过程
- SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入的数据中注入非法的 SQL 语句段或命令，恶意攻击数据库
package p823;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
public class SqlInjection {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
// 获取用户想要查询的用户名和密码
// Input userName = 1' or
// Input pwd = or '1' = '1
Scanner scanner = new Scanner(System.in);
System.out.print("Input the name that you want to query:");
String userName = scanner.nextLine();
System.out.print("Input the password that you want to query:");
String pwd = scanner.nextLine();
// 加载配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("config\temp.properties"));
// 加载驱动类信息，自动注册驱动
Class.forName(properties.getProperty("driver"));
// 获得连接
Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties);
Statement statement = connection.createStatement();
String select = "SELECT * FROM admin WHERe name='" + userName + "' AND pwd= '" + pwd + "'";
ResultSet resultSet = statement.executeQuery(select);
while (resultSet.next()) {
userName = resultSet.getString(1);
pwd = resultSet.getString(2);
System.out.println(userName + "t" + pwd);
}
resultSet.close();
statement.close();
connection.close();
}
}
830. Statement
831. 预处理查询
- PreparedStatement 执行的 SQL 语句中的参数用问号（?）来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数。setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引，从 1 开始，第二个参数是设置 SQL 语句中的参数的值
- 预处理的好处：
- 不再使用 + 拼接 SQL 语句，减少语法错误
- 有效解决了 SQL 注入问题
- 大大减少了编译次数，提高了效率
String select = "SELECT * FROM admin WHERe name = ? AND pwd= ?"; // SQL 语句预处理 PreparedStatement preparedStatement = connection.prepareStatement(select); preparedStatement.setString(1, userName); preparedStatement.setString(2, pwd); // 执行 SQL 语句，得到结果集 ResultSet resultSet = preparedStatement.executeQuery();832. 预处理DML 833. JDBC API
| 接口名 | 方法名 | 功能 |
|---|---|---|
| Connection | createStatement() | 创建执行静态 SQL 语句的对象 |
| Connection | createPreparedStatement(sql) | 获得 SQL 语句预处理对象 |
| Statement | executeUpdate(sql) | 执行 DML 语句，返回受影响行数 |
| Statement | executeQuery(sql) | 执行 DQL 语句，返回结果集 |
| Statement | execute(sql) | 执行任意 SQL 语句，返回布尔值 |
| PreparedStatement | executeUpdate(sql) | 执行 DML 语句，返回受影响行数 |
| PreparedStatement | executeQuery(sql) | 执行 DQL 语句，返回结果集 |
| PreparedStatement | execute(sql) | 执行任意 SQL 语句，返回布尔值 |
| PreparedStatement | setXxx(index,value) | 设置 SQL 语句中的值 |
| PreparedStatement | setObject(index,value) | 设置 SQL 语句中的值 |
| ResultSet | next() | 向下移动一行，到表尾返回 false |
| ResultSet | previous() | 向上移动一行，到表头返回 false |
| ResultSet | getXxx(index || colLabel) | 获得指定列的值 |
| ResultSet | getObject(index || colLabel) | 获得指定列的值 |
package utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver;
private static String url;
private static String user;
private static String password;
private static String path = "config\temp.properties";
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream(path));
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (IOException e) {
// 实际开发中，会将此类编译异常转换成运行异常，由调用方自行处理，较为方便
throw new RuntimeException(e);
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// 实际开发中，会将此类编译异常转换成运行异常，由调用方自行处理，较为方便
throw new RuntimeException(e);
}
}
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 e) {
// 实际开发中，会将此类编译异常转换成运行异常，由调用方自行处理，较为方便
throw new RuntimeException(e);
}
}
}
835. JDBC Utils DML
836. JDBC Utils 查询
837. 事务介绍
- JDBC 程序中当一个 Connectioon 对象被创建时，默认情况下是自动提交事务：每次执行一条 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚
- 可以调用 Connection 接口的 setAutoCommit(false) 方法取消自动提交事务
- 在所有的 SQL 语句都执行成功后，调用 commit() 方法提交事务；在其中某个操作失败或出现异常时，调用 rollback() 方法回滚事务
public void transaction() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sub = "UPDATE account SET balance = balance - 100 WHERe id = 1";
String add = "UPDATE account SET balance = balance + 100 WHERe id = 2";
try {
// 获得连接
connection = JdbcUtils.getConnection();
// 关闭自动提交
connection.setAutoCommit(false);
// 执行 SQL
preparedStatement = connection.prepareStatement(add);
preparedStatement.executeUpdate();
int temp = 1 / 0;
preparedStatement = connection.prepareStatement(sub);
preparedStatement.executeUpdate();
// 提交事务
connection.commit();
System.out.println("所有 SQL 操作成功，提交事务！");
} catch (SQLException | ArithmeticException e) {
try {
// 发生异常，撤销操作，事务回滚
System.out.println("程序执行发生了异常，回滚所有操作！！！");
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JdbcUtils.close(null, preparedStatement, connection);
}
}
839. 批处理应用
- 当需要成批插入或者更新记录时，可以采用 Java 的批处理更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
// JDBC 的批处理语句包含下面的方法： // 添加需要批量处理的 SQL 语句或参数 addBatch() // 批量发送执行 executeBatch() // 清空批处理包 clearBatch()
- JDBC 连接 MySQL 时，如果需要使用批处理功能，需要在 url 中添加参数：
url = "jdbc:mysql://localhost:3306/temp?rewriteBatchedStatements=true“
- 批处理往往和 PreparedStatement 一起搭配使用，既可以减少编译次数，又可以减少运行次数，效率大大提高
preparedStatement.addBatch();
if (i % 1000 == 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
840. 批处理源码分析
public void addBatch() throws SQLException {
synchronized(this.checkClosed().getConnectionMutex()) {
if (this.batchedArgs == null) {
this.batchedArgs = new ArrayList();
}
for(int i = 0; i < this.parameterValues.length; ++i) {
this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
}
this.batchedArgs.add(new com.mysql.jdbc.PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
}
}
841. 传统链接弊端分析
- 传统的 JDBC 数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都需要将 Connection 加载到内存中，再验证 IP 地址、用户名、密码（耗时0.05s ~ 1s）是否正确。需要向数据库连接的时候，就向数据库请求一个连接，频繁的请求操作将占用过多的系统资源，容易造成服务器崩溃
- 每一次数据库连接，使用完后都得及时断开，如果程序出现异常而导致未能正常关闭，将导致数据库内存泄漏，最终导致数据库崩溃重启
- 传统获取连接的方式，不能控制创建连接的数量，如果连接过多，也可能导致内存泄漏，从而导致 MySQL 崩溃
- 预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕归还给缓冲池（并不断开与数据库的连接）
- 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个
- 当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列
- JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口具体实现留给第三方
| 连接池 | 特点 |
|---|---|
| C3P0 | 速度相对较慢，稳定性不错（hibernate、spring底层均采用） |
| Druid | 阿里巴巴提供的数据库连接池，集 DBCP、C3P0、Proxool 优点于一身 |
| Proxool | 有监控连接池状态的功能，稳定性较 C3P0 略差 |
| BoneCP | 速度快 |
| DBCP | 速度较 C3P0 快，但不稳定 |
// 创建数据源对象 ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(); // 设置相关信息 comboPooledDataSource.setDriverClass(driver); comboPooledDataSource.setJdbcUrl(url); comboPooledDataSource.setUser(user); comboPooledDataSource.setPassword(pwd); comboPooledDataSource.setInitialPoolSize(10); comboPooledDataSource.setMaxPoolSize(50); // 获得连接 Connection connection = comboPooledDataSource.getConnection(); connection.close();844. C3P0方式2
// 将 c3p0-config.xml 文件拷贝到工程 src 目录下
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("spring_bear");
Connection connection = comboPooledDataSource.getConnection();
connection.close();
845. 德鲁伊连接池
// 加载配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("config\druid.properties"));
// 创建一个的连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
// 获得连接
Connection connection = dataSource.getConnection();
connection.close();
846. 德鲁伊工具类
package utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtilsByDruid {
static DataSource dataSource;
static String path = "config\druid.properties";
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream(path));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
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 e) {
throw new RuntimeException(e);
}
}
}
847. ApDBUtils引出
- 关闭 connection 后，resultSet 结果集无法继续使用；然而很多时候我们希望关闭 connection 连接后仍然可以继续使用查询到的数据，resultSet 存储查询结果的方式不利于数据管理；从 resultSet 结果集中获取数据时操作方法不够明确，getXxx() 方法容易出错，含义模糊
- 定义一个类与数据库表的字段一一对应，这样的类一般称作 JavaBean 或 PoJo 或 Domain
- 将返回的结果集的字段值封装到自定义的类的对象中，将若干个这样的对象放进集合中，就可以直接访问集合从而获得数据库表的查询结果
- commons-dbutils 是 Apache 组织提供的一个开源 JDBC 工具类，它是对 JDBC 的封装，使用 dbutils 能极大简化 JDBC 编码量
- QueryRunner 类封装了 SQL 的执行，是线程安全的 ，可以实现增、删、改、查和批处理
- RessultSetHandler 接口用于处理 java.sql.ResultSet，将查询到的数据转换为另一种形式
| 接口名 | 功能 |
|---|---|
| ArrayHandler | 将结果集中的第一行数据转换成对象数组 |
| ArrayListHandler | 将结果集中的每一行都转换成一个数组，再存放到 List 中 |
| BeanHandler | 将结果集中的第一行数据封装到一个对应的 JavaBean 实例中 |
| BeanListHandler | 将结果集中的每一行数据封装到对应的 JavaBean 实例中，存放到 List |
| ColumnListHandler | 将结果集中的某一列数据存放到 List 中 |
| KeyedHandler(name) | 将每行数据封装到 Map 中，再将 map 存入另一个 map 中 |
| MapHandler | 将结果集的第一行数据封装到 Map 中，key 是列名，value 是对应值 |
| MapListHandler | 将结果集中的每一行数据封装到 Map 中，再存放到 List 里 |
public void testApache() throws SQLException {
// 获得连接
Connection connection = JdbcUtilsByDruid.getConnection();
// 获得 Apache 实现的查询对象
QueryRunner queryRunner = new QueryRunner();
String select = "SELECT * FROM cost WHERe id >= ? AND id <= ?";
List fishings = queryRunner.query(connection, select, new BeanListHandler<>(Fishing.class), 1, 10);
for (Fishing fishing : fishings) {
System.out.println(fishing);
}
JdbcUtilsByDruid.close(null, null, connection);
}
850. ApDBUtils源码分析
851. ApDBUtils查询2
852. ApDBUtilsDML
853. BasicDAO问题
- Apache-dbutils + Druid-connectionPoll 简化了 JDBC 开发，但仍有以下不足：
- SQL 语句是固定的，不能通过参数传入，通用性不好，需进行改进，以方便 CRUD 操作
- 对于查询 SELECT 操作，如果有返回值，返回类型不能确定，需要使用泛型解决
- 未来数据库中的表会有很多，业务需求复杂，不可能只靠一个 Java 类完成
- 解决方案：为每个表设计一个 JavaBean 类，同时为每一张数据库表设计一个专门操作它的类 Dao，将所有的具体 Dao 类中的共有部分抽象出父类 BasciDao，以更好地利用多态完成功能
- DAO（data access object）：访问数据库数据的对象
- 创建 JavaBeam 类的时候一定要给一个无参构造器，以方便反射机制获取该类信息
package dao.dao; import dao.utils.JdbcUtilsByDruid; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import java.sql.Connection; import java.sql.SQLException; import java.util.List; public class BasicDao856. BasicDAO实现2 857. JDBC连接池梳理{ QueryRunner queryRunner = new QueryRunner(); public int update(String sql, Object... params) { Connection connection = null; try { connection = JdbcUtilsByDruid.getConnection(); return queryRunner.update(connection, sql, params); } catch (SQLException e) { // 将编译异常转换为运行异常，方便调用者处理 throw new RuntimeException(e); } finally { JdbcUtilsByDruid.close(null, null, connection); } } public List getMultiRows(String sql, Class clazz, Object... params) { Connection connection = null; try { connection = JdbcUtilsByDruid.getConnection(); return queryRunner.query(connection, sql, new BeanListHandler (clazz), params); } catch (SQLException e) { // 将编译异常转换为运行异常，方便调用者处理 throw new RuntimeException(e); } finally { JdbcUtilsByDruid.close(null, null, connection); } } public T getOneRow(String sql, Class clazz, Object... params) { Connection connection = null; try { connection = JdbcUtilsByDruid.getConnection(); return queryRunner.query(connection, sql, new BeanHandler (clazz), params); } catch (SQLException e) { // 将编译异常转换为运行异常，方便调用者处理 throw new RuntimeException(e); } finally { JdbcUtilsByDruid.close(null, null, connection); } } public Object getOneObj(String sql, Object... params) { Connection connection = null; try { connection = JdbcUtilsByDruid.getConnection(); return queryRunner.query(connection, sql, new ScalarHandler(), params); } catch (SQLException e) { // 将编译异常转换为运行异常，方便调用者处理 throw new RuntimeException(e); } finally { JdbcUtilsByDruid.close(null, null, connection); } } }
