【数据库 第四天】
一、JDBC 概述
之前我们学习了 JavaSE，编写了 Java 程序，数据保存在变量、数组、集合等中，无法持久化。
后来学习了 IO 流可以将数据写入文件，但不方便管理数据以及维护数据的关系；
后来我们学习了数据库管理软件 MySQL，可以方便的管理数据。
为了结合 Java 程序与 MySQL 数据库技术，实现数据的存储和处理，那么就可以使用 JDBC 技术。
1.为什么需要 JDBC
- 没有JDBC
- 有了JDBC后
2. JDBC 概述
JDBC：Java Database Connectivity，它是代表一组独立于任何数据库管理系统（DBMS）的 API，声明在 java.sql 与 javax.sql 包中，是 SUN (现在 Oracle )提供的一组接口规范。由各个数据库厂商来提供实现类，这些实现类的集合构成了数据库驱动 jar。
- 即 JDBC 技术包含两个部分：
- java.sql 包和 javax.sql 包中的 API
因为为了项目代码的可移植性，可维护性，SUN 公司从最初就制定了 Java 程序连接各种数据库的统一接口规范。这样的话，不管是连接哪一种 DBMS 软件，Java 代码可以保持一致性。
- 各个数据库厂商提供的 jar
因为各个数据库厂商的 DBMS 软件各有不同，那么内部如何通过 SQL 实现增、删、改、查等管理数据，只有这个数据库厂商自己更清楚，因此把接口规范的实现交给各个数据库厂商自己实现。
3. JDBC 使用步骤
(1)准备工作
create database day04;
use day04;
create table user(
id int primary key auto_increment,
username varchar(20),
password varchar(20),
nickname varchar(20)
);
INSERT INTO `USER` VALUES(null,'张三','123456','老张');
INSERT INTO `USER` VALUES(null,'李四','123456','老李');
INSERT INTO `USER` VALUES(null,'王五','123','东方不败');
(2)注册驱动
- 将 DBMS 数据库管理软件的驱动 jar 包拷贝到项目的 libs 目录中
例如：mysql-connector-java-5.1.36-bin.jar - 把驱动 jar 添加到项目的 build path 中
- 将驱动类加载到内存中
Class.forName("com.mysql.jdbc.Driver");
(3)获取 Connection 连接对象
Connection conn = DriverManager.getConnection(url,username,password);
// Connection conn = DriverManager.getConnection("数据库服务器路径","用户民","密码");
mysql 的 url：jdbc:mysql://localhost:3306/数据库名?参数名=参数值;
jdbc:mysql://服务器的主机地址:端口号/数据库名?characterEncoding=utf8;
jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8
（如果 JDBC 程序与服务器端的字符集不一致，会导致乱码，那么可以通过参数指定服务器端的字符集)
String url = "jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "123456";
//获得连接
Connection connection = DriverManager.getConnection(url, user, password);
(4)执行 SQL 语句并处理结果
- 编写 SQL 语句
String sql = "select *from user";
- 创建 Statement 对象
Statement statement = connection.createStatement();
- 使用 Statement 对象执行 SQL 语句
- 增删改：调用 executeUpate 方法
- 查询：调用 executeQuery 方法
ResultSet resultSet = statement.executeQuery(sql);
-
处理结果
- 增删改：返回的是整数值，表示受到影响的数据条数
- 查询：返回 ResultSet 结果
boolean next()：判断是否还有下一行getString(字段名或序号)getInt(字段名或序号)getObject(字段名或序号)
while (resultSet.next()) {
// 获取每一列的数据
System.out.println(resultSet.getObject(1));
System.out.println(resultSet.getObject(2));
System.out.println(resultSet.getObject(3));
System.out.println(resultSet.getObject(4));
}
（5）释放资源
原则是后创建的资源先关闭，我们会依次关闭 ResultSet、Statement、Connection 对象
// 关闭资源
if(resultSet != null){
resultSet.close();
}
if(statement != null){
statement .close();
}
if(connection != null){
connection.close();
}
4. JDBC 使用小结
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
/**
* JDBC 的使用
* 1. 注册驱动
* Class.forName("驱动的全限定名"); // MySQL 驱动的全限定名是 com.mysql.jdbc.Driver
* 如果使用 MySQL8.0 版本，那么使用 MySQL8 的驱动 jar 包，驱动的全限定名为 com.mysql.cj.jdbc.Driver
*
* 2. 获得连接（建立客户端与 MySQL 服务器的连接）
* Connection conn = DriverManager.getConnection(url,username,password);
*
* 3. 创建执行 SQL 语句的 Statement 对象
* Statement statement = conn.createStatement();
*
* 4. 使用 statement 执行 SQL 语句
* （1）执行增删改的 SQL 语句：int num = statement.executeUpdate; 返回值表示受到影响的函数
* （2）执行查询的 SQL 语句：ResultSet ret = statement.executeQuery(sql); 返回值是查询到的结果集
*
* 5. 如果第四步执行的 SQL 语句是查询，那么我们就要将查询到的结果集中数据遍历出来（难点）
*
* 6. 关闭资源：后创建的资源先关闭
* rst.close();
* statement.close();
* conn.close();
*/
public class TestJDBC {
private Statement statement;
private Connection conn;
@Before
public void init() throws Exception {
// 目标：使用 JDBC 执行查询所有用户的 SQL 语句
// 1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获得连接
// jdbc:mysql://服务器的主机地址:端口号/数据库名?characterEncoding=utf8
// 如果数据库主机地址是 localhost，并且端口号是 3306 ，那么就可以省略 localhost:3306
String url="jdbc:mysql://localhost:3306/day01_1?characterEncoding=utf8";
String user="root";
String password="root";
// 这种操作底层是使用反射创建对象
conn = DriverManager.getConnection(url,user,password);
// 3. 创建 statement 对象
statement= conn.createStatement();
}
@Test
public void testFindAll() throws ClassNotFoundException, SQLException {
// 4. 使用 statement 对象执行 SQL 语句
String sql="select * from user";
// 执行查询数据的 SQL 语句，获得查询到的结果集
ResultSet rst = statement.executeQuery(sql);
// 5. 遍历 rst，从中获取查询的数据
while(rst.next()){
// 每次调用 next() 就是将游标移动到结果集的下一行
// 获取当前行的每列数据，根据列名获取
int id=(int) rst.getObject("id");
String username=(String) rst.getObject("username");
String pwd=(String) rst.getObject("password");
String nickname=(String) rst.getObject("nickname");
System.out.println(id + ":" + username + ":" + pwd + ":" + nickname);
System.out.println("----------------");
}
// 6. 关闭资源，后创建的先关闭
rst.close();
}
@After
public void destroy() throws Exception {
statement.close();
conn.close();
}
}
二、JDBC 的增删改查练习
1.执行添加的 SQL 语句
public class TestJDBC {
private Statement statement;
private Connection conn;
@Before
public void init() throws Exception {
// 目标：使用 JDBC 执行查询所有用户的 SQL 语句
// 1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获得连接
// jdbc:mysql://服务器的主机地址:端口号/数据库名?characterEncoding=utf8
// 如果数据库主机地址是 localhost，并且端口号是 3306 ，那么就可以省略 localhost:3306
String url="jdbc:mysql://localhost:3306/day01_1?characterEncoding=utf8";
String user="root";
String password="root";
// 这种操作底层是使用反射创建对象
conn = DriverManager.getConnection(url,user,password);
// 3. 创建 statement 对象
statement= conn.createStatement();
}
@After
public void destroy() throws Exception {
statement.close();
conn.close();
}
@Test
public void testInsert() throws Exception {
// 测试执行添加数据的 SQL 语句
String sql="insert into user (username,password,nickname) values ('钱七','777777','老七')";
// 使用 statement 对象执行 SQL 语句
// i 表示受到影响的数据行数
int i=statement.executeUpdate(sql);
System.out.println(i);
}
}
2.执行删除的 SQL 语句
@Test
public void testDelete() throws SQLException {
// 删除 id 为4的用户
String sql="delete from user where id=4";
int i= statement.executeUpdate(sql);
}
3.执行修改的 SQL 语句
@Test
public void testUpdate() throws SQLException {
// 把 id 为3的用户的密码更改
String sql="update user set password='888888' where id=3";
statement.executeUpdate(sql);
}
4. 执行查询单行数据的 SQL 语句
- 要求：将查询到的结果封装到 User 对象中
User类
public class User {
private int id;
private String username;
private String password;
private String nickname;
public User() {
}
public User(int id, String username, String password, String nickname) {
this.id = id;
this.username = username;
this.password = password;
this.nickname = nickname;
}
public int getId() {
return id;
}
public void setId(int 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;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", nickname='" + nickname + '\'' +
'}';
}
}
JDBC 代码
@Test
public void testFindById() throws SQLException {
// 查询 id 为1的用户名的信息
String sql="select * from user where id=1";
ResultSet rst= statement.executeQuery(sql);
// 遍历出结果集
while(rst.next()){
// 获取当前行的每列数据
int id = rst.getInt("id");
String username = rst.getString("username");
String password = rst.getString("password");
String nickname = rst.getString("nickname");
// 从数据库中查询到的数据的目的是为了在 Java 代码的内存中操作查询出来的数据
// 我们要将查询出来的一行数据作为一个整体，就将查询出来的这行数据中各列数据存储到一个 Map 中或者 User 对象中
/*Map<String,Object> userMap = new HashMap<>();
userMap.put("id",id);
userMap.put("username",username);
userMap.put("password",password);
userMap.put("nickname",nickname);*/
// 优化：使用面向对象的思想
User user = new User(id, username, password, nickname);
System.out.println(user);
}
}
5.执行查询多行数据的 SQL 语句
- 要求：将查询到的多行数据封装到
List<User>中
@Test
public void testFindAll() throws ClassNotFoundException, SQLException {
// 4. 使用 statement 对象执行 SQL 语句
String sql="select * from user";
// 执行查询数据的 SQL 语句，获得查询到的结果集
ResultSet rst = statement.executeQuery(sql);
// 5. 遍历 rst，从中获取查询的数据
List<User> userList=new ArrayList<>(); // 每一个 User 对象都存入集合中
while(rst.next()){
// 每次调用 next() 就是将游标移动到结果集的下一行
// 获取当前行的每列数据，根据列名获取
int id=(int) rst.getObject("id");
String username=(String) rst.getObject("username");
String pwd=(String) rst.getObject("password");
String nickname=(String) rst.getObject("nickname");
// System.out.println(id + ":" + username + ":" + pwd + ":" + nickname);
// 优化：将查询到的数据封装到 User 对象
User user = new User(id, username, pwd, nickname);
userList.add(user); // 将对象存入集合中
System.out.println(user);
System.out.println("----------------");
}
// 6. 关闭资源，后创建的先关闭
rst.close();
}
三、 使用 PreparedStatement 处理 CRUD
1. Statement 存在的问题
（1）每次执行一个 SQL 语句都需要先编译
String sql1 = "insert into user values(null,'田七','77777','田七')";
String sql2 = "insert into user values(null,'赵六','666666','赵六')";
String sql3 = "insert into user values(null,'张三','333333','张三')";
// 如果使用 Statement 执行上述 SQL 语句需要编译三次
（2）SQL 语句拼接
String username = "王五";
String password= "555555";
String nickname = "王五";
String sql = "insert into user values(null,"+username+","+password+","+nickname+")";
（3）SQL 语句注入
String username = "hahahahha' or '1'='1"
String sql = "SELECT * FROM user where username='" + username + "'";
// 结果会把所有数据都查询出来
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
@Test
public void testError() throws SQLException {
// SQL 注入，由于 SQL 语句中的字符串拼接，导致 SQL 语句的格式发生了变化而引发的问题
// 演示 SQL 语句注入的问题
String username="张三丰' or '1'='1"; // 问题所在
String password="123456' or '1'='1"; // 问题所在
// 根据 username 和 password 查询用户，其实就是模拟登录
String sql="select * from user where username='"+username+"' and password='"+password+"'";
ResultSet rst = statement.executeQuery(sql);
System.out.println(sql);
// 我们只需要判断 rst 里面有没有数据
if (rst.next()) {
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
}
2. PreparedStatement 解决问题
（1）预编译
PreparedStatement 会先对参数化的 SQL 语句进行预编译，执行 SQL 语句的时候不会再进行编译
String sql = "insert into user values(null,?,?,?)";
// 预编译
PreparedStatement pstm = connection.prepareStatement(sql);
// 后续设置参数、执行添加多少条数据都不会再重新编译
（2）避免 SQL 拼接
String username = "王五";
String password= "555555";
String nickname = "王五";
String sql = "insert into user values(null,?,?,?)";
// 预编译
PreparedStatement pstm = connection.prepareStatement(sql);
// 设置参数
pstm.setObject(1, username);
pstm.setObject(2, password);
pstm.setObject(3, nickname);
int count = pstm.executeUpdate(); // 此处不能传 SQL
System.out.println(count);
（3）防止 SQL 语句注入
PreparedStatement 在进行预编译的时候，就已经确定好了 SQL 语句的格式，不会再因为 SQL 语句的拼接改变 SQL 语句的格式
String username = "hahahahha' or '1'='1"
String sql = "SELECT * FROM user where username=?";
//即使输入'张三' or '1'= '1'也没问题
PreparedStatement pst = conn.prepareStatement(sql);
//中间加入设置？的值
pst.setObject(1, username);
ResultSet rs = pst.executeQuery();
（4）示例
@Test
public void testLogin() throws ClassNotFoundException, SQLException {
// 1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql:///day01_1?characterEncoding=utf8", "root", "root");
// 3. 预编译参数化 SQL 语句
String username="张三";
String password="123456";
// （1）编写参数化的 SQL 语句，也就是说需要传入参数的地方使用?占位
String sql="select * from user where username=? and password=?";
// （2）预编译 SQL 语句：可以确定 SQL 语句的结构，那么预编译之后就无法通过 SQL 注入改变语句的结构了
PreparedStatement preparedStatement = conn.prepareStatement(sql);
// 4. 给?占位符传入对应参数
preparedStatement.setObject(1,username);
preparedStatement.setObject(2,password);
// 5. 执行 SQL 语句，不要再传入 SQL 语句，因为在编译的时候已经传过了
ResultSet rst = preparedStatement.executeQuery();
if (rst.next()) {
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
rst.close();
preparedStatement.close();
conn.close();
public class TestPreparedStatement {
private Connection conn;
@Before
public void init() throws ClassNotFoundException, SQLException {
// 1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接
conn = DriverManager.getConnection("jdbc:mysql:///day01_1?characterEncoding=utf8", "root", "root");
}
@After
public void destroy() throws Exception {
conn.close();
}
@Test
public void testInsert() throws Exception {
String sql="insert into user(username,password,nickname) values(?,?,?)";
// 预编译 SQL 语句
PreparedStatement preparedStatement = conn.prepareStatement(sql);
// 设置占位符的参数
preparedStatement.setObject(1,"赵六");
preparedStatement.setObject(2,"666666");
preparedStatement.setObject(3,"老赵");
// 执行 SQL 语句
int i=preparedStatement.executeUpdate();
System.out.println(i);
// 关闭资源
preparedStatement.close();
}
}
3.获取自增长键值
（1）获取自增长键值的应用场景
主要使用在一些复杂的业务中，在添加完主表的一条数据之后，要获取到这条数据的主键值，然后将该值添加进从表的外键字段
（2）获取自增长键值的步骤
- 在预编译的时候，指定要返回自增长的 key
PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
- 在执行完添加数据的 SQL 语句之后，通过 PreparedStatement 的对象调用
getGeneratedKeys()方法来获取自增长键值，遍历结果集
ResultSet rs = pst.getGeneratedKeys();
- 遍历获取自增长的键值
if(rs.next()){
Object key = rs.getObject(1);
System.out.println("自增的 key 值 did =" + key);
}
示例代码
@Test
public void testObtainPrimaryKeyAfterInsert() throws SQLException {
// 测试在添加数据之后获取数据
String sql="insert into user(username,password,nickname) values(?,?,?)";
// 预编译的时候，就要指定，不仅仅需要预编译，还需要获取自增长的主键值
PreparedStatement preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// 设置 SQL 语句的参数
preparedStatement.setObject(1,"武九");
preparedStatement.setObject(2,"123456");
preparedStatement.setObject(3,"老九");
// 执行 SQL 语句
// 返回值是受到影响的行数
int i= preparedStatement.executeUpdate();
System.out.println("添加了"+i+"行数据");
// 单独获取自增长的主键值，在一个结果集 ResultSet 里面
ResultSet rst = preparedStatement.getGeneratedKeys();
// 因为主键值只有一个，所以可以不使用遍历
if (rst.next()){
// 表示获取到了自增长的主键，就取出主键
// 底层获取自增长的 id 执行的 SQL 语句是 select last_insert_id();
int id = rst.getInt(1);
System.out.println("自增长的主键值为" + id);
}
// 关闭资源
preparedStatement.close();
}
4.批处理
（1）批处理优势和应用场景
批处理相比较单独一条条执行 SQL 语句来说，其效率高很多。
批处理一般会使用在批量添加多条数据和批量修改多条数据
（2）批处理的具体操作步骤
- 在 url 中要加一个参数
rewriteBatchedStatements=true，那么此时 url 就变成了
jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true
-
在完成所有参数设置之后，调用 PreparedStatement 的
addBatch()方法，添加到批处理中 -
最后执行 PreparedStatement 的
executeBatch()方法执行批处理语句
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestBatch {
@Test
public void testBatchInsert() throws ClassNotFoundException, SQLException {
// 测试批量处理
Class.forName("com.mysql.jdbc.Driver");
// 获得连接
Connection conn = DriverManager.getConnection("jdbc:mysql:///day01_1?characterEncoding=utf8&rewriteBatchedStatements=true", "root", "root");
// 预编译 SQL 语句
String sql="insert into user (username,password,nickname) values (?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
// 设置问号处的参数
for (int i = 1; i <= 100; i++) {
preparedStatement.setObject(1,"李十"+i);
preparedStatement.setObject(2,"123456"+i);
preparedStatement.setObject(3,"老李"+i);
// 添加到批量操作中
preparedStatement.addBatch();
}
// 执行批量操作
preparedStatement.executeBatch();
// 关闭资源
preparedStatement.close();
conn.close();
}
}
5.事务
（1）事务操作的步骤
- 执行逻辑单元之前先开启事务
- 逻辑单元执行完毕，没有出现异常则提交事务
- 逻辑单元执行过程中出现异常，则回滚事务
（2）事务相关 API
| Connection 中与事务有关的方法 | 说明 |
|---|---|
| setAutoCommit(boolean autoCommit) | 参数是 true 或 false 如果设置为 false，表示关闭自动提交，相当于开启事务; 类似 sql 里面的 start transaction; |
| void commit() | 提交事务; 类似 sql 里面的 commit; |
| void rollback() | 回滚事务; 类似 sql 里面的 rollback; |
事务结束之后，要将此连接的 autoCommit 还原成 true
（3）使用 JDBC 的事务完成转账案例
①准备数据
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
insert into account values (null,'张三',1000);
insert into account values (null,'李四',1000);
insert into account values (null,'王五',1000);
②代码实现
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 事务相关的API:
* 1. 开启事务 connection.setAutoCommit(false);
* 2. 提交事务 connection.commit();
* 3. 回滚事务 connection.rollback();
* 4. 事务结束之后，要将此此接的 autoCommit 还原成 true
*/
public class TestTransaction {
@Test
public void testTransfer() throws ClassNotFoundException, SQLException {
// 测试连接
// 1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获得链接
Connection conn = DriverManager.getConnection("jdbc:mysql:///day01_1?characterEncoding=utf8", "root", "root");
// 3. 预编译 SQL 语句
String sql="update account set money=money+? where name=?"; // 改变用户的金额
PreparedStatement preparedStatement = conn.prepareStatement(sql);
// 开启事务
conn.setAutoCommit(false);
try{
// 张三扣款500元
preparedStatement.setObject(1,-500);
preparedStatement.setObject(2,"张三");
// 执行张三的扣款 SQL 语句
preparedStatement.executeUpdate();
int num=10/0; // 故意引发异常
// 李四收款500元
preparedStatement.setObject(1,500);
preparedStatement.setObject(2,"李四");
// 执行李四的收款 SQL 语句
preparedStatement.executeUpdate();
// 提交事务
conn.commit();
}catch (Exception e){
e.printStackTrace();
conn.rollback();
}finally {
// 还原 connection 的 AutoCommit 为 true
conn.setAutoCommit(true);
}
// 关闭资源
preparedStatement.close();
conn.close();
}
}
四、数据库连接池
1.什么是数据库连池
连接池是 connection 对象的缓冲区，它里面会存放一些 connection，当 Java 程序需要使用 connection 的时候，如果连接池中有则直接从连接池获取，不需要去新创建 connection 。连接池让 Java 程序能够复用连接、管理连接。
2.为什么要使用连接池
- 因为每次创建和销毁连接都会带来较大的系统开销
- 每次创建和销毁连接都要消耗大概 0.05~1s 的时间。
- 可以防止大量用户并发访问数据库服务器。
3.连接池的优势
-
资源重用
- 由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。
- 在减少系统消耗的基础上，另一方面也增进了系统运行环境的平稳性（减少内存碎片以及数据库临时进程/线程的数量）。
-
更快的系统响应速度
- 数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。
- 对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。
-
新的资源分配手段
- 对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接的配置，实现数据库连接池技术。
- 某一应用最大可用数据库连接数的限制，避免某一应用独占所有数据库资源。
-
统一的连接管理，避免数据库连接泄漏
- 在较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄漏。
4.连接池的原理
- 连接池维护着两个容器空闲池和活动池
- 空闲池用于存放未使用的连接，活动池用于存放正在使用的连接，活动池中的连接使用完之后要归还回空闲池
- 当 Java 程序需要连接时，先判断空闲池中是否有连接，如果空闲池中有连接则取出一个连接放置到活动池供 Java 程序使用
- Java 程序需要连接时，如果空闲池中没有连接了，则先判断活动池的连接数是否已经达到了最大连接数，如果未达到最大连接数，则会新创建一个连接放置到活动池，供 Java 程序使用
- 如果空闲池中没有连接了，活动池中的连接也已经达到了最大连接数，则不能新创建连接了，那么此时会判断是否等待超时，如果没有等待超时则需要等待活动池中的连接归还回空闲池
- 如果等待超时了，则可以采取多种处理方式，例如：直接抛出超时异常，或者将活动池中使用最久的连接移除掉归还回空闲池以供 Java 程序使用
5.连接池的实现
（1）DataSource 接口
JDBC 的数据库连接池使用
javax.sql.DataSource来表示，DataSource只是一个接口（通常被称为数据源），所有的 Java 数据库连接池都需要实现该接口。该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现
（2）常见的数据库连接池
- DBCP 是 Apache （阿帕奇）提供的数据库连接池，速度相对 c3p0 较快，但因自身存在 BUG，Hibernate3 已不再提供支持
- C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以
- Proxool 是 sourceforge 下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较 c3p0 差一点
- HikariCP 俗称光连接池，是目前速度最快的连接池
- Druid 是阿里巴巴提供的数据库连接池，据说是集 DBCP 、C3P0 、Proxool 优点于一身的数据库连接池
（3）Druid 连接池的使用
- 加入 jar 包： 例如：druid-1.1.10.jar
- 代码步骤
- 第一步：创建 druid 连接池的配置文件 druid.properties 文件，放置到类路径下配置文件：src/jdbc.properties 或者 resources/jdbc.properties（建议）
键名不可以更改，键值更改为自己的需求
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day04?characterEncoding=utf8
username=root
password=root
# 初始化连接数
initialSize=5
# 最大活动连接数
maxActive=10
# 最大等待时间
maxWait=1000
- 第二步：使用工厂模式创建 DruidDataSource 对象
- 第三步：使用连接池对象获取连接
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
public class TestDataSource {
@Test
public void test01() throws Exception {
// 1. 创建一个 Properties 对象，让其读取 druid.properties 文件
Properties properties = new Properties();
// (1) 将 druid.properties 配置文件转成字节输入流
//FileInputStream fileInputStream = new FileInputStream("E:\\JetBrains中的Java练习\\Java练习一\\day01_JDBC_01\\resources\\druid.properties");
// 使用相对路径来将配置文件转成字节输入流，可以使用类加载器来读取文件，在类路径下读取配置文件
// TestDataSource.class.getClassLoader() 表示获取 ClassLoader 对象
// getResourceAsStream() 读取类路径下的配置文件
InputStream resourceAsStream = TestDataSource.class.getClassLoader().getResourceAsStream("druid.properties");
// (2) 使用 properties 对象加载流
// 绝对路径
//properties.load(fileInputStream);
// 相对路径
properties.load(resourceAsStream);
// 2. 使用 DruidDataSourceFactory 创建 Druid 连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
// 3. 从连接池中取出一个连接使用
Connection connection1 = dataSource.getConnection();
Connection connection2 = dataSource.getConnection();
Connection connection3 = dataSource.getConnection();
Connection connection4 = dataSource.getConnection();
Connection connection5 = dataSource.getConnection();
Connection connection6 = dataSource.getConnection();
Connection connection7 = dataSource.getConnection();
Connection connection8 = dataSource.getConnection();
Connection connection9 = dataSource.getConnection();
Connection connection10 = dataSource.getConnection();
// 归还一个连接：没有使用连接池的时候 connection.close() 就是销毁连接，如果是从连接池中取出的 connection 对象调用 close() 方法就是将连接归还回连接池
// 底层原理使用的是动态代理
connection1.close();
// 再创建一个连接
Connection connection11 = dataSource.getConnection();
}
}
（4）Druid 连接池的配置参数列表
| 配置 | 缺省 | 说明 |
|---|---|---|
| name | 配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this) | |
| url | 连接数据库的 url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto | |
| username | 连接数据库的用户名 | |
| password | 连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用 ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/使用ConfigFilter | |
| driverClassName | 根据 url 自动识别 这一项可配可不配，如果不配置 druid 会根据url自动识别 dbType，然后选择相应的 driverClassName (建议配置下) | |
| initialSize | 0 | 初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次 getConnection 时 |
| maxActive | 8 | 最大连接池数量 |
| maxIdle | 8 | 已经不再使用，配置了也没效果 |
| minIdle | 最小连接池数量 | |
| maxWait | 获取连接时最大等待时间，单位毫秒。配置了 maxWait 之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置 useUnfairLock 属性为 true 使用非公平锁。 | |
| poolPreparedStatements | false | 是否缓存 preparedStatement，也就是 PSCache。PSCache 对支持游标的数据库性能提升巨大，比如说 oracle。在 mysql 下建议关闭。 |
| maxOpenPreparedStatements | -1 | 要启用 PSCache，必须配置大于0，当大于0时，poolPreparedStatements 自动触发修改为 true。在 Druid 中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100 |
| validationQuery | 用来检测连接是否有效的 sql，要求是一个查询语句。如果 validationQuery 为 null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。 | |
| testOnBorrow | true | 申请连接时执行 validationQuery 检测连接是否有效，做了这个配置会降低性能。 |
| testOnReturn | false | 归还连接时执行 validationQuery 检测连接是否有效，做了这个配置会降低性能 |
| testWhileIdle | false | 建议配置为 true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于 timeBetweenEvictionRunsMillis，执行 validationQuery 检测连接是否有效。 |
| timeBetweenEvictionRunsMillis | 有两个含义：① Destroy 线程会检测连接的间隔时间 ② testWhileIdle 的判断依据，详细看 testWhileIdle 属性的说明 | |
| numTestsPerEvictionRun | 不再使用，一个 DruidDataSource 只支持一个 EvictionRun | |
| minEvictableIdleTimeMillis | 最小的间隔时间 | |
| connectionInitSqls | 物理连接初始化的时候执行的 sql 语句 | |
| exceptionSorter | 根据 dbType 自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接 | |
| filters | 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的 filter:stat 日志用的 filter:log4j 防御 sql 注入的 filter:wall | |
| proxyFilters | 类型是 List，如果同时配置了 filters 和 proxyFilters，是组合关系，并非替换关系 |
6.封装 JDBCTools 工具类
- 配置文件：src/jdbc.properties 或者 resources/jdbc.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day04?characterEncoding=utf8
username=root
password=root
initialSize=5
maxActive=10
maxWait=1000
- JDBCTools 工具类：
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* 工具类中提供三个方法
* 1. 获取连接池对象
* 2. 从连接池中获取连接
* 3. 将连接归还到连接池
*/
public class JDBCTools {
private static DataSource dataSource;
static { // 静态代码块只执行一次
try{
// 1. 使用类加载器读取配置文件，转成字节输入流
InputStream resourceAsStream = JDBCTools.class.getClassLoader().getResourceAsStream("druid.properties");
// 2. 使用 properties 类对象加载字节输入流
Properties properties = new Properties();
properties.load(resourceAsStream);
// 3. 使用 DruidDataSourceFactory 创建连接池对象
dataSource = DruidDataSourceFactory.createDataSource(properties);
}catch (Exception e){
e.printStackTrace();
}
}
/**
* 获取连接池对象
* @return
*/
public static DataSource getDataSource(){
return dataSource;
}
/**
* 获取连接
* @return
*/
public static Connection getConnection(){
// 这时是编译时异常，把它转换为运行时才报出异常
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage()); // 改为运行时异常
}
}
/**
* 归还连接
* @param connection
* @throws SQLException
*/
public static void releaseConnection(Connection connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage()); // 改为运行时异常
}
}
}
- 调用：
@Test
public void testDelete() throws Exception {
// 测试删除 id 为2的用户
// 1. 注册驱动：Druid 框架底层已经注册驱动了
// 2. 获得链接
Connection conn = JDBCTools.getConnection();
// 3. 预编译 SQL 语句
String sql = "delete from user where id=?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
// 4. 设置参数
preparedStatement.setObject(1,2);
// 5. 执行 SQL 语句
preparedStatement.executeUpdate();
// 6. 关闭资源
preparedStatement.close();
JDBCTools.releaseConnection(conn);
}
7.连接池使用的总结
- 拷贝加入 druid 的 jar 包
- 拷贝 druid 的配置文件到类路径，并修改，标记为资源根
- 拷贝 JDBCTools 工具类
- 在需要连接的地方编写
Connection conn = JDBCTools.getConnection();此时拿到的连接就是从连接池取出的 - 连接使用完毕之后，调用
JDBCTools.releaseConnection(conn);归还连接
五、Apache 的 DBUtils
1. DBUtils 的概述
commons-dbutils是 Apache （阿帕奇）组织提供的一个开源 JDBC工具类库，它是对 JDBC 的简单封装，学习成本极低，并且使用 DBUtils 能极大简化 JDBC 编码的工作量，同时也不会影响程序的性能。
其中 QueryRunner 类（核心部分）封装了 SQL 语句的执行，是线程安全的。
- 可以实现增、删、改、查、批处理
- 考虑了事务处理需要共用 Connection
- 该类最主要的就是简单化了 SQL 查询，它与 ResultSetHandler 组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。
2. DBUtils 执行增删改的 SQL 语句
（1）API 介绍
QueryRunner()，创建 QueryRunner 对象，用于执行 SQL 语句- QueryRunner 的
update(Connection conn, String sql, Object... params)方法，用于执行增删改的 SQL 语句
（2）代码实现
// 向 user 表添加一条数据
public void testInsert() throws SQLException{
// 创建 queryRunner 对象
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into user values(?,?,?,?)";
Object[] params ={null,"aaa","111","露西"};
queryRunner.update(JDBCTools.getConnection(),sql, params);
}
// 把用户名是李四的 user 密码改成 222
public void testUpdate() throws SQLException{
//创建 queryRunner 对象
QueryRunner queryRunner = new QueryRunner();
String sql = "update user set password = ? where username = ?";
Object[] params={"李四","aaa"};
queryRunner.update(JDBCTools.getConnection(),sql, params);
}
// 把用户名是李四的 user 给删除
public void testDelete() throws SQLException{
// 创建 queryRunner 对象
QueryRunner queryRunner = new QueryRunner();
String sql = "delete from user where username = ?";
Object[] params={"李四"};
queryRunner.update(JDBCTools.getConnection(),sql, params);
}
练习：
import com.atguigu.jdbc.utils.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Before;
import org.junit.Test;
import java.sql.SQLException;
public class TestDBUtils {
private QueryRunner queryRunner;
@Before
public void init(){
queryRunner = new QueryRunner(JDBCTools.getDataSource());
}
@Test
public void testInsert() throws SQLException {
// 第一种方式：需要自己在执行 SQL 语句的时候手动传入连接对象
// 往 user 表中添加一行数据
// 1. 创建 QueryRunner 对象
QueryRunner queryRunner01=new QueryRunner();
// 2. 调用 queryRunner 对象的方法执行 SQL 语句，如果是执行增删改的 SQL 语句则调用 update 方法
String sql01="insert into user (username,password,nickname) values (?,?,?)";
// queryRunner.update(连接对象,sql,问号处的参数)
queryRunner01.update(JDBCTools.getConnection(),sql01,"周八","888","老八");
// 第二种方式：直接将连接池对象交给 QueryRunner（如果有事务则不能使用这种方式）
QueryRunner queryRunner02=new QueryRunner(JDBCTools.getDataSource());
String sql02 = "insert into user (username,password,nickname) values (?,?,?)";
queryRunner02.update(sql02,"钱七","123456","老钱");
}
@Test
public void testDelete() throws SQLException {
String sql="delete from user where id=?";
queryRunner.update(sql,2);
// 不需要执行资源关闭，框架底层已经实现关闭
}
@Test
public void testUpdate() throws SQLException {
// 将 id 为 5 的用户昵称改为"老赵"
String sql="update user set nickname=? where id=?";
queryRunner.update(sql,"老赵",5);
}
3. DBUtils 执行批处理
（1）API 介绍
public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： 支持批处理 INSERT、UPDATE、或者 DELETE 语句public <T> T insertBatch(Connection conn,String sql,ResultSetHandler<T> rsh,Object[][] params)throws SQLException：只支持 INSERT 语句
（2）代码实现
public class TestDBUtils {
private QueryRunner queryRunner;
@Before
public void init(){
queryRunner = new QueryRunner(JDBCTools.getDataSource());
}
@Test
public void testBatchAdd() throws SQLException {
// 测试批量添加
// 编写 SQL 语句
String sql="insert into user (username,password,nickname) values (?,?,?)";
// 创建一个二维数组，用于存储批量的参数，二维数组的第一维表示批量操作多少条数据，第二维表示每条数据要设置多少个参数
Object[][] params=new Object[1000][3];
// 循环设置批量添加的数据
for (int i = 0; i < 1000; i++) {
params[i][0]="我是谁"+i;
params[i][1]="999"+i;
params[i][2]="我是"+i;
}
// 执行批量操作
queryRunner.batch(sql,params);
}
}
4.使用 QueryRunner 类实现查询
（1）API 介绍
query(String sql, ResultSetHandler<T> rsh, Object... params)，执行查询 SELECT- ResultSetHandler 结果集处理类
| Handler类型 | 说明 |
|---|---|
| ArrayHandler | 将结果集中的第一条记录封装到一个 Object[] 数组中，数组中的每一个元素就是这条记录中的每一个字段的值 |
| ArrayListHandler | 将结果集中的每一条记录都封装到一个 Object[] 数组中，将这些数组在封装到 List 集合中。 |
| BeanHandler | 将结果集中第一条记录封装到一个指定的 javaBean 中。 |
| BeanListHandler | 将结果集中每一条记录封装到指定的 javaBean 中，将这些 javaBean 在封装到 List 集合中 |
| ColumnListHandler | 将结果集中指定的列的字段值，封装到一个 List 集合中 |
| KeyedHandler | 将结果集中每一条记录封装到 Map<String,Object>，在将这个 Map 集合做为另一个 Map 的 value , 另一个 Map 集合的 key 是指定的字段的值。 |
| MapHandler | 将结果集中第一条记录封装到了 Map<String,Object> 集合中，key 就是字段名称，value 就是字段值 |
| MapListHandler | 将结果集中每一条记录封装到了 Map<String,Object> 集合中，key 就是字段名称，value 就是字段值，在将这些 Map 封装到 List 集合中。 |
| ScalarHandler | 它是用于单个数据。例如 select count(*) from 表。 |
（2）代码实现
// 查询 id 为1的用户信息
@Test
public void selectById() throws SQLException{
// 创建 queryRunner 对象
QueryRunner queryRunner = new QueryRunner();
String sql = "select * from user where id = ?";
Object[] params = {1};
User user = queryRunner.query(JDBCTools.getConnection(),sql, new BeanHandler<>(User.class), params);
System.out.println(user.toString());
}
// 查询所有的用户信息
@Test
public void selectAll() throws SQLException{
// 创建 queryRunner 对象
QueryRunner queryRunner = new QueryRunner();
String sql = "select *from user";
Object[] params = {};
List<User> list = queryRunner.query(JDBCTools.getConnection(),sql, new BeanListHandler<>(User.class), params);
System.out.println(list.toString());
}
// 统计用户的个数
@Test
public void getCount() throws SQLException{
// 创建 queryRunner 对象
QueryRunner queryRunner = new QueryRunner();
String sql = "select count(*) from user";
Long n = (Long) queryRunner.query(JDBCTools.getConnection(),sql, new ScalarHandler());
System.out.println(n.intValue());
}
六、经典错误
1、jar 包版本不兼容
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1023)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2576)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2309)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:419)
at com.mysql.jdbc.NonRegisteringDriver.connect(Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Exception in thread "main" java.sql.SQLException: The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
.java:344)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
看异常好像是无事务连接异常，无法创建连接。将 MySQL 驱动改为了最新的 8.0 版本的 MySQL 驱动。显示那个驱动类已经过时了，新的驱动类是 “com.mysql.cj.jdbc.Driver” ，而不是 “com.mysql.jdbc.Driver” 了，并且如果还说没有配置时区，从 JDBC6.0 开始驱动类使用了新的，并且 url 中必须要设置时区，否则会报错。
第一步：使用最新的 MySQL 驱动 jar 包。
第二步：把驱动的类名改为:
static String driver="com.mysql.cj.jdbc.Driver";
第三步：在访问 mysql 的 url 后加入时区设置：
static String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC"
127
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
