本教程适用于有一点 Java 基础和 mysql 基础的同学
本教程特点：从零讲起、问题驱动步骤、案例驱动理论、方法技巧大于记忆。
源码及笔记获取和配套视频：JDBC（Java连接MySQL数据库）详细案例教程：CRUD、注入安全、连接池、事务、dbutils等_哔哩哔哩_bilibili
零、内容大纲
- JDBC 简介
- 常用的 java.sql API 介绍
- 下载 jar、添加jar等
- 从零开始一步步实现基本操作：创建连接、增、删、改、查
- Statement 缺点：SQL注入安全案例、特殊字符处理案例
- 预处理 PreparedStatement 的应用及优点
- 自己封装底层工具类 BaseDao
- JDBC 的事务处理
- JDBC 的批处理
- JDBC 存储过程的调用
- 连接池：阿里的 Druid 连接池的使用（DBCP 、C3P0等）
- 开源工具 dbutils 组件的使用
一、JDBC是什么？有什么作用？
JDBC（ Java DataBase Connectivity, Java 数据库连接 ）是一种用于执行 SQL 语句的 Java API，是 Java 应用连接各种关系型数据库的基本的接口。可以为多种关系数据库提供统一访问的标准。
简单地说，JDBC 可做三件事：与数据库建立连接、发送操作数据库的语句、处理结果。再简单具体就是完成了对数据库数据的增删改查等操作。
二、本篇案例教程需要的准备工作
- 有一定的 Java 基础及SQL基础
- 安装 JDK8 或以上版本
- 集成开发环境 IDE , 本教程使用的为 Idea
- 安装 MySql5或8 数据库及客户端 ，本教程使用的 Sqlyog13
- 提前下载 mysql 的驱动 jar 包 本案例使用的是 mysql-connector-java-8.0.23.jar
- 注意: 如果使用的 mysql5 则需要单独下载 mysql5 的驱动 jar 包，另外在下面连接代码中也有细微差别。
三、上一步提到数据库驱动是什么？
数据库安装好之后，我们的应用程序也是不能直接使用数据库的，必须要通过相应的数据库驱动程序，通过驱动程序去和数据库打交道。这个驱动程序有数据库厂商来实现。也就是数据库厂商的 JDBC 接口实现，即对 Connection 等接口的实现类的 jar 文件。
而要连接不同数据库需要有不同的数据驱动 jar 包文件。作用关系图如下图：
四、用到的 API 接口
1.Driver接口
Driver接口由数据库厂家提供，作为java开发人员，只需要使用Driver接口就可以了。在编程中要连接数据库，必须先装载特定厂商的数据库驱动程序。如：
装载MySql驱动：Class.forName("com.mysql.jdbc.Driver");
装载Oracle驱动：Class.forName("oracle.jdbc.driver.OracleDriver");
其中的 com.mysql.cj.jdbc.Driver 其实就是上一步提到的驱动 jar 包中提供的完整类名。
2.Connection接口
Connection与特定数据库的连接（会话），在连接上下文中执行sql语句并返回结果。就像我们给朋友打电话一样，一定是先拨号并打通之后，然后才能相互通话发送数据。
DriverManager.getConnection(url, user, password)方法建立在JDBC URL中定义的数据库 Connection 连接上。不同数据库需要提供不同的 url 参数。
连接MySql数据库：Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
连接Oracle数据库：Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database", "user", "password");
连接SqlServer数据库：Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://host:port; DatabaseName=database", "user", "password");
- 常用方法：
- createStatement()：创建向数据库发送sql的statement对象。
- prepareStatement(sql) ：创建向数据库发送预编译sql的PrepareSatement对象。
- prepareCall(sql)：创建执行存储过程的callableStatement对象。
- setAutoCommit(boolean autoCommit)：设置事务是否自动提交。
- commit() ：在链接上提交事务。
- rollback() ：在此链接上回滚事务。
3.Statement接口
用于执行静态SQL语句并返回它所生成结果的对象。
-
三种Statement类：
- Statement：由createStatement创建，用于发送简单的SQL语句（不带参数）。
- PreparedStatement ：继承自Statement接口，由preparedStatement创建，用于发送含有一个或多个参数的SQL语句。PreparedStatement对象比Statement对象的效率更高，并且可以防止SQL注入，所以我们一般都使用PreparedStatement。
- CallableStatement：继承自PreparedStatement接口，由方法prepareCall创建，用于调用存储过程。
-
常用Statement方法：
- execute(String sql):运行语句，返回是否有结果集
- executeQuery(String sql)：运行select语句，返回ResultSet结果集。
- executeUpdate(String sql)：运行insert/update/delete 操作，返回更新的行数。
- addBatch(String sql) ：把多条 sql 语句放到一个批处理中。
- executeBatch()：向数据库发送一批sql语句执行。
4.ResultSet接口
ResultSet 提供检索不同类型字段的方法，常用的有：
-
- getString(int index)、getString(String columnName)：获得在数据库里是varchar、char等类型的数据对象。
- getFloat(int index)、getFloat(String columnName)：获得在数据库里是Float类型的数据对象。
- getDate(int index)、getDate(String columnName)：获得在数据库里是Date类型的数据。
- getBoolean(int index)、getBoolean(String columnName)：获得在数据库里是Boolean类型的数据。
- getObject(int index)、getObject(String columnName)：获取在数据库里任意类型的数据。
ResultSet还提供了对结果集进行滚动的方法：
-
- next()：移动到下一行
- previous()：移动到前一行
- absolute(int row)：移动到指定行
- beforeFirst()：移动resultSet的最前面。
- afterLast() ：移动到resultSet的最后面。
使用后依次关闭对象及连接：ResultSet → Statement → Connection
五、使用JDBC的步骤
下面开始通过实际案例来给大家演示及详细介绍使用 JDBC 连接数据库的各种操作，每段 SQL 脚本或代码都经过运行测试。可以直接复制在 IDE 中运行查看效果。
案例的顺序会从简单到复杂循序渐进的方式步步展开，如果没有 JDBC 基础的可以按顺序阅读及复制代码运行即可。如果有点基础的，想跳过某一部分的读者，可以先复制 SQL 脚本建库建表及插入测试数据后，然后跳到后面去阅读和测试。
1. MySQL 中建库和表。
表结构设计考虑便于理解 JDBC ,简化了表结构，保留了常用的字段。后面我会把一外包企业的真实表结构附上。
数据库脚本如下：
-- 创建数据库
CREATE DATABASE BANK;
-- 打开数据库
USE BANK;
-- 创建账户信息表
CREATE TABLE ACCOUNT(
ACCID INT AUTO_INCREMENT PRIMARY KEY, -- 账户主键 自动增长
ACCNAME VARCHAR(30) UNIQUE, -- 账户名 唯一约束
PASSWORD VARCHAR(30), -- 密码字段
BALANCE FLOAT, -- 账户余额
STATE INT(1), -- 状态： 0-正常 1-冻结 2-挂失 等等
ACCDATE DATETIME -- 账户开户时间
);
-- 插入测试数据
INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE,ACCDATE)VALUES('zhang','123456',1000,0,NOW());
INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE,ACCDATE)VALUES('wang', '666888',1000,0,NOW());
-- 查看已有数据
SELECT * FROM ACCOUNT;2. 创建项目及添加驱动
在 IDE 中创建项目及添加驱动
-
a. 创建文件夹 lib 用来存放驱动 jar 包：选中项目右键 -> New -> Directory-> New Directory输入框中输入 lib 然后确认即可。
-
b. 复制 驱动 jar 文件到文件夹 lib 中：选中 lib 文件夹后，Ctrl+V 即可。
-
c. 添加 jar 到类构建路径：鼠标选中驱动 jar 文件右键 -> Add As Libary -> 点击即可。
-
d.完成之后效果图如下：
3. 创建连接并测试
创建连接数据库的测试类，要使用 JDBC 首先要创建和数据库的连接，代码如下：
package jdbc.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* JDBC:Java DataBase Connection
* API :java.sql.*;
*
* 连接数据库必须的四个参数
* a.URL----------确定了连接数据库所在服务器的协议，IP,端口，数据库名等信息
* b.UserName-----登录数据库需要使用的账户名
* c.Password-----登录数据库需要使用的密码
* d.Driver-------连接数据库需要的驱动类 即上一步中添加的驱动包中包含的类名
*
* 总结的常见错误如下：
* Connection timed out: connect --- 连接超时-检查IP或端口（URL）
* Connection Refused----------------mysql服务没有启动
* Unknown database 'st'-------------URL中拼写的数据库名字有误
* Access denied for user 'root'@'localhost' ----用户名或密码错误
*/
public class TestConnection {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/bank";
String username = "rootx";
String password = "123456";
String driver = "com.mysql.jdbc.Driver";
try {
//加载驱动
Class.forName(driver);
//创建连接
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("连接成功！"+conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
运行上面代码如果能正常打印输出 “连接成功！...”，则连接数据库成功！
常见错误总结
Connection timed out: connect --- 连接超时-检查IP或端口（URL）
- Connection Refused--------------------mysql服务没有启动
- Unknown database 'st'----------------URL中拼写的数据库名字有误
- Access denied for user 'root'@'localhost' ----用户名或密码错误
4. 插入记录
上面连接成功后，就可以来通过 JDBC 插入数据了
package jdbc.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/**
* 本类用来向表中插入记录，
* 通过本例，了解使用 JDBC 典型的几个步骤：
* 1. 加载驱动 2. 创建连接 3.创建Statement对象 4.执行发送SQL 5.关闭释放资源
*/
public class TestInsert {
public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bank", "root", "root");
//3.创建Statement对象---发送执行SQL命令的
Statement st = conn.createStatement();
//编写 insert 语句，注意：字符串后不带分号
String sql = "INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE,ACCDATE)VALUES('zhaoliu','666666',0,0,NOW())";
//4.执行SQL
int rows = st.executeUpdate(sql);
//如果返回结果大于0，表明记录保存成功
if(rows>0) {
System.out.println("记录保存成功！");
}
//5.关闭连接 释放资源 这里也要注意关闭的顺序 不能颠倒
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
5. 修改记录
运行完代码后，思考一个问题：下面代码和上面代码区别？
package jdbc.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/**
* 本类用来单独演示修改操作
*/
public class TestUpdate {
public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bank", "root", "root");
//3.创建Statement对象---发送执行SQL命令的
Statement st = conn.createStatement();
//编写UPDATE修改语句，给刚插入的记录余额增加100元钱
String sql = "UPDATE ACCOUNT SET BALANCE=BALANCE+100 WHERE ACCID=3";
//4.执行SQL
int rows = st.executeUpdate(sql);
//如果返回结果大于0，表明记录保存成功
if(rows>0) {
System.out.println("记录修改成功！");
}
//5.关闭连接 释放资源
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
6. 删除记录
要注意思考 插入，修改，删除的代码是否有太多的相同？
package jdbc.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/**
* 本类用来单独演示删除操作
*/
public class TestDelete {
public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bank", "root", "root");
//3.创建Statement对象---发送执行SQL命令的
Statement st = conn.createStatement();
//编写UPDATE修改语句，给刚插入的记录余额增加100元钱
// String sql = "UPDATE ACCOUNT SET BALANCE=BALANCE+100 WHERE ACCID=3";
//如果要测试删除，只需把上面sql行注释掉，换为下面sql即可
String sql = "DELETE FROM ACCOUNT WHERE ACCID=3";
//4.执行SQL
int rows = st.executeUpdate(sql);
//如果返回结果大于0，表明记录保存成功
if(rows>0) {
System.out.println("记录删除成功！");
}
//5.关闭连接 释放资源
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
小提示： 通过 4,5,6 对记录插入，修改，删除的分别演示，我们会发现，这三种操作基本是相同的写法，仅仅的区别就是使用的 SQL 语句不同而已！所以换句话说，上面这么代码总结下来，就是一套掌握就够，就掌握了基本的增删改的操作了。那么查询呢？
7.查询操作
下面代码中遍历结果集时，使用了两种方式：列位置和列名。注意下面注释
package jdbc.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* 本类用来演示查询记录，并逐条读取记录的步骤
*
*/
public class TestSelect {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bank", "root","root");
Statement st = conn.createStatement();
String sql = "SELECT ACCID,ACCNAME,PASSWORD,BALANCE FROM ACCOUNT";
//执行查询语句 获取结果集
ResultSet rst = st.executeQuery(sql);
//使用游标来提取数据，
//默认游标指向结果集的第一条记录之前，所以需要先使用next()下移游标，
//指向记录或根据返回boolean结果来判定是否还有可读取的记录
while(rst.next()) {
//下面第一句使用的是 int 列位置来获取值。其他使用的是列名。
//两种方式可以互换。推荐:使用列名方式
int accid = rst.getInt(1); //用来读取查询出的第一列值
String accname = rst.getString("accname"); //通过列名的方式来读取账户名
String password = rst.getString("password");//通过列名的方式来获取密码值
float balance = rst.getFloat("balance"); //通过列名和getFloat()获取 float 类型的值
System.out.println(accid+"|"+accname+"|"+password+"|"+balance);
}
//关闭连接 释放资源
rst.close();
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
通过上面的4,5,6,7代码，分别独立的演示了对数据库的增删改查操作，可以发现增删改的代码基本相同，然而，这三种操作又和查询语句又是否存在相同的重复代码呢？
答案是肯定，上面所有的四种操作中，重复度最高的就是加载驱动，创建连接。因为任何的操作都需要依赖数据库连接对象 Connection。所以对 Connection的封装是很有必要的。
8.简单的数据库连接类
封装一个简单的数据库连接类，后面几个步骤，我们会暂时使用此类，方便获取连接。
package jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import com.mysql.jdbc.Statement;
/**
* 本类中完成了对创建数据库连接及关闭连接的封装
*/
public class ConnectionUtil {
private String url = "jdbc:mysql://127.0.0.1:3306/bank";
private String username = "root";
private String password = "root";
private String driver = "com.mysql.jdbc.Driver";
private Connection conn; // 连接对象
/**
* 创建连接并返回 这里做了初步封装，以便于后面测试使用
*/
public Connection getConnection() {
if (conn == null) {
try {
// 加载驱动
Class.forName(driver);
// 创建连接
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("连接成功！" + conn);
} catch (Exception e) {
e.printStackTrace();
}
}
return conn;
}
/**
* 关闭连接相关对象
*/
public void close(Connection conn, Statement st, ResultSet rst) {
try {
if (rst != null) {
rst.close();
rst = null;
}
if (st != null) {
st.close();
st = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
9. Statement 缺点
上面使用 Statement 是可以满足基本需求的，但是实际开发中使用的却比较少，因为它存在过多的缺点。下面我通过模拟运用场景，来分析总结下它的缺点。
package jdbc.demo1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import jdbc.pojo.Account;
import jdbc.util.ConnectionUtil;
/**
* 本类演示 Statement的缺点
* 1.注入安全问题
* 2.添加特殊字符问题
* 3.效率问题
*/
public class TestStatement {
public static void main(String[] args) {
/*
* //1.演示SQL注入安全：如果用户提交的用户名为SQL片段 String username =
* "\"' or 1=1 or ''='\"";//超级用户名 String password = "xxxxx";//无论什么密码都可登录成功
* boolean isok = login(username, password); if(isok) {
* System.out.println("登录成功！"); }else { System.out.println("用户名或密码有误！"); }
*/
//----------------------演示分割线---------------------
/*
* //2.演示添加特殊字符问题。注意：这里账户名故意多了一个单引号 Account account = new Account("xi'ao",
* "666666", 1000); add(account);
*/
//----------------------演示分割线---------------------
//这里同时添加多条记录
Account[] accounts = {new Account("lisi","111111",200),new Account("mingming","111111",300)};
saveMore(accounts);
}
/**
* 模拟登陆：演示SQL注入安全问题
* 这里我们通过拼接SQL及使用用户名和密码作为条件来进行查询，作为校验的方法。
* 当然这种方法开发中是推荐，但是可以演示和理解SQL注入安全的原因。
*/
public static boolean login(String accname,String password) {
//实例化自定义工具类
ConnectionUtil connUtil = new ConnectionUtil();
//获取连接
Connection conn = connUtil.getConnection();
Statement st = null;
ResultSet rst = null;
try {
st = conn.createStatement();
//拼写SQL 假设这样写了SQL，给注入留下了安全隐患
String sql = "SELECT * FROM ACCOUNT WHERE ACCNAME='"+accname+"' AND PASSWORD='"+password+"'";
//执行SQL 获取查询结果集
rst = st.executeQuery(sql);
//返回是否有记录
return rst.next();
} catch (Exception e) {
e.printStackTrace();
}finally {
connUtil.close(conn, st, rst);
}
return false;
}
/**
*
* 模拟了添加账户对象操作
* 特殊情况：如果account的属性值中包含了特殊字符“'”单引号，则会执行失败，
* 因为SQL语句中的单引号表示字符的标志，需要成对的出现。但是在实际场景中难免会有特殊字符的插入，比如一篇文章中，各种特殊符号都可能出现。
* 所以 这就是state的一个缺点。
*/
public static void add(Account account){
//实例化自定义工具类
ConnectionUtil connUtil = new ConnectionUtil();
//获取连接
Connection conn = connUtil.getConnection();
Statement st = null;
try {
//创建Statemnt
st = conn.createStatement();
//从account对象中获取属性，拼写SQL
String sql = "INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE,ACCDATE)"
+ "VALUES"
+ "('"+account.getAccname()+"','"+account.getPassword()+"',"+account.getBalance()+","+account.getState()+",NOW())";
//执行SQL
int rows = st.executeUpdate(sql);
if(rows>0) {
System.out.println("成功！");
}
}catch (Exception e) {
e.printStackTrace();
}finally {//关闭连接释放资源
connUtil.close(conn, st, null);
}
}
/**
* 本例演示 一次如果插入多条记录的效率问题
* 问题：每次循环中都会拼写一次SQL并别会重复的解析SQL的语法结构。
* 每次只是插入的值不同，但是SQL语句是相同的。但是还是要解析SQL合法性。
*/
public static void saveMore(Account[] accounts) {
//实例化自定义工具类
ConnectionUtil connUtil = new ConnectionUtil();
// 获取连接
Connection conn = connUtil.getConnection();
Statement st = null;
try {
// 创建Statemnt
st = conn.createStatement();
//一次性插入多条记录
for (Account account : accounts) {
// 从account对象中获取属性，拼写SQL
String sql = "INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE,ACCDATE)" + "VALUES" + "('"
+ account.getAccname() + "','" + account.getPassword() + "'," + account.getBalance() + ","
+ account.getState() + ",NOW())";
// 执行SQL
int rows = st.executeUpdate(sql);
if (rows > 0) {
System.out.println("成功！");
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {// 关闭连接释放资源
connUtil.close(conn, st, null);
}
}
}
从上面三个模拟场景中，可以发现三个存在的问题：a.SQL 注入安全问题 b.特殊字符处理问题 c.效率问题。
10.PreparedStatement 用法
针对 Statement 使用的问题，使用 PreparedStatement 即可自行解决了，下面我们来举例学习 PreparedStatement 用法。
package jdbc.demo1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import jdbc.util.ConnectionUtil;
/**
* 本类演示PreparedStatement用法
* 1.可以防止SQL注入安全问题
* 2.可以添加特殊字符
* 3.效率高于Statement
*/
public class TestPrepared {
/**
* 下面的sql赋值中 即使使用特殊字符，使用注入SQL是都没问题的
* @param args
*/
public static void main(String[] args) {
ConnectionUtil connUtil = new ConnectionUtil();
Connection conn = connUtil.getConnection();
//预处理的SQL语句写法，不需要拼接字符串，只需使用占位符？表明将来要赋值的位置和个数
String sql = "INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE)VALUES(?,?,?,?)";
try {
//创建PreparedStatement 注意：这里就需要传入SQL了，这里会提前语法解析
PreparedStatement pst = conn.prepareStatement(sql);
//提醒：如果一次要添加多条记录，只要循环这里即可，不需要多次编译SQL了，效率大大提高
//给sql中的问号占位符赋值 注意对应的位置及类型
pst.setString(1, "xiao'ming");//注意:从1开始 不是0 有特殊字符也可以了
pst.setString(2, "888777");
pst.setFloat(3, 3000);
pst.setInt(4, 1);
//执行 注意：这里是不需要传入sql参数的。因为前面已经预处理过了
int rows = pst.executeUpdate();
if(rows>0) {
System.out.println("成功！");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
注：使用 PreparedStatement 插入多条记录时，不再需要循环预处理 SQL 的代码，只需重复给占位符赋值及执行即可。从而减少了重复解析语法等步骤，从大大提高了速度性能。另外对于特殊字符可以处理，并且再使用注入 SQL 的方式来登录，已经行不通了。这些大家可以自行测试。
11. 读取属性文件创建连接
在上面的创建连接过程中，连接数据库的信息都是在程序中写死的，但是实际的使用过程中，连接数据库的信息有可能会经常变动，这样在项目发布后，没有源码的情况下是无法再次修改的，这样不利于后期维护。所以我们在实际的开发场景下，连接数据库的信息一般都是写在配置文件中的，这样方便后期修改。接下来我们来升级上面连接工具类。
为了后期方便修改连接数据库的信息，需要把连接数据库的四个参数专门提取放入属性文件中配置，这样后期在需要修改账号信息时，直接编辑配置文件即可。
六、JDBC 的事务处理及批处理
1. JDBC 中的事务控制
事务简单的说就是要么全部执行，要么全部都不执行！
数据库事务拥有以下四个特性，习惯上被称之为ACID特性。
- 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
- 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
- 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中
下面通过一个简单的银行转账场景模拟，来了解事务的作用和用法。
package jdbc.demo1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import jdbc.util.ConnectionUtil;
/**
* 事务 要么全部都执行 要么全部都不执行
* 原子性 持久性 一致性 隔离性
*
*/
public class TestTran {
public static void main(String[] args) {
// tran1();
trans();
}
/**
* 下面模拟如果没有事务控制，转账过程中的风险问题
* 假设zhang 原有余额 1000 wang 原有余额 0 那么：
* 如果 zhang成功转出500 则结果应该为 zhang余额500 wang 余额500
* 如果zhang转出500失败则结果应该为zhang余额1000 wang余额0 这样才对，
* 但是因为没有事务控制，所以在中间出现异常时，结果却是zhang余额变为500 wang还是0 ，造成了结果不一致。
*/
public static void tran1() {
ConnectionUtil connUtil = new ConnectionUtil();
Connection conn = new ConnectionUtil().getConnection();
PreparedStatement pst = null;
try {
//转账 修改语句
String sql = "UPDATE ACCOUNT SET BALANCE=BALANCE+? WHERE ACCNAME=?";
pst = conn.prepareStatement(sql);
//给 zhang 账户减去500
pst.setFloat(1, -500);
pst.setString(2, "zhang");
pst.executeUpdate();
"".substring(444);//引发异常
//给 wang 账户加上500
pst.setFloat(1, 500);
pst.setString(2, "wang");
pst.executeUpdate();
System.out.println("转账成功！");
} catch (Exception e) {
System.out.println("转账失败！");
e.printStackTrace();
}finally {
connUtil.close(conn, pst, null);
}
}
/**
* 下面案例使用了事务控制，定义了事务边界，把两个修改作为不可分割的单元，即原子性特点
* 两个修改如果都成功则提交事务。否则一个异常失败，则回滚事务，保证数据一致性
*/
public static void trans() {
ConnectionUtil connUtil = new ConnectionUtil();
Connection conn = new ConnectionUtil().getConnection();
PreparedStatement pst = null;
try {
//关闭自动提交 -----在执行更新前
conn.setAutoCommit(false);
//转账 修改语句
String sql = "UPDATE ACCOUNT SET BALANCE=BALANCE+? WHERE ACCNAME=?";
pst = conn.prepareStatement(sql);
//给 zhang 账户减去500
pst.setFloat(1, -500);
pst.setString(2, "zhang");
pst.executeUpdate();
"".substring(444);//引发异常
//给 wang 账户加上500
pst.setFloat(1, 500);
pst.setString(2, "wang");
pst.executeUpdate();
//提交事务---
conn.commit();
System.out.println("转账成功！");
} catch (Exception e) {
System.out.println("转账失败！");
//事务回滚
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
connUtil.close(conn, pst, null);
}
}
}
2. 批量处理
在实际应用中，有时可能有需要一次性处理多条数据的需求，比如一个 Excel 表格中万条数据导入数据库中。这个使用如果使用普通的方式来处理，会发现效率很低，速度很慢。所以就有必要来使用 JDBC 的批量处理功能。(关于Excel批量导入会在后期 Chat 讲述)
下面模拟一次性插入百万条记录的场景，看下批量处理是如何实现的：
package jdbc.demo1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import jdbc.pojo.Account;
import jdbc.util.ConnectionUtil;
public class TestBatch {
private static List<Account> list;
static {
//此处模拟多条记录的数据来源 千百万的记录数据
list = new ArrayList<Account>();
for (int i = 0; i < 1000000; i++) {
Account account = new Account("A"+i,"111111",i%10000);
list.add(account);
}
}
/**
* 要执行批量处理，注意：关闭自动提交功能 开启事务
* 一百万条总耗时：155034
*/
public static void batchAdd(List<Account> list) {
ConnectionUtil connUtil = new ConnectionUtil();
Connection conn = null;
PreparedStatement pst = null;
try {
conn = connUtil.getConnection();
String sql = "INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,ACCDATE)VALUES(?,?,?,NOW())";
//预处理-------------------
pst = conn.prepareStatement(sql);
//事务 默认自动提交事务
conn.setAutoCommit(false);// 自动提交关闭
int i = 0;
for (Account account:list) {
pst.setString(1, account.getAccname());
pst.setString(2, account.getPassword());
pst.setFloat(3,account.getBalance());
//添加到批处理队列 缓存
pst.addBatch();
if(i++%6000==0){
pst.executeBatch();//执行批处理
//提交事务
conn.commit();
pst.clearBatch();
}
}
pst.executeBatch();//执行批处理
//提交事务
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();//事务回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
connUtil.close(conn, pst, null);
}
}
/**
* 测试
*/
public static void main(String[] args) {
System.out.println(">>>==开始导入....");
long start = System.currentTimeMillis();
batchAdd(list);
long end = System.currentTimeMillis();
System.out.println("总耗时："+(end-start));
}
}
七、调用存储过程
存储过程(Stored Procedure) 是一组为了完成特定功能的SQL 语句集，经编译后存储在服务器端数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来执行它
在上面的转账业务中，我们至少需要执行两个修改语句才能完成转账业务，但其实是至少，因为在转账过程中除了两个修改，还需要记录转账日志、操作日志等功能，所以一个转账业务可能需要多条语句才能完成。
而如果过多的操作语句可能开发效率或执行效率会降低。这时，我们可以考虑使用存储过程
存储过程的优点
（1） 存储过程可以实现组件化管理 存储过程是实现特定功能的程序体，不同的应用程序都可以通过名称和参数调用存储过程，对存储过程的修改完善不会影响应用程序，提高系统的可移植性。 （2）存储过程能够实现较快的执行速度
因为存储过程是经过预编译和优化过的程序代码。 （3）存储过程能够减少网络流量
客户端程序通过名称和参数调用存储过程，而非传递整个TSQL代码来执行操作。 （4）存储过程可以实现数据的安全性
存储过程的调用需要权限，且对数据的操作是被封装的，只提供调用接口。
接下来我们来看下面四种情况的调用方式：
* 1.调用无参无输出的存储过程
* 2.有入参无出参存储过程
* 3.有入参和出参存储过程
* 4.查询结果的存储过程1. 无参存储过程
通过调用无参存储过程，新建 Account 表的备份表，并备份数据。
-- 创建无参的存储过程
DELIMITER $$
CREATE PROCEDURE create_back()
BEGIN
CREATE TABLE account_backup SELECT * FROM ACCOUNT;
END$$
DELIMITER ;
-- 调用存储过程
CALL create_back;
DROP TABLE account_backup;
SELECT * FROM account_backup;Java 代码：
/**
* 调用无参构造方法
*/
public static void callProcedure00(){
try ( Connection conn = ConnectionUtils.getConn();
CallableStatement callableStatement = conn.prepareCall("call create_back");
){
boolean is = callableStatement.execute();
int updateCount = callableStatement.getUpdateCount();
System.out.println(is+"===="+updateCount);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}2. 有入参无出参存储过程
先在数据库端创建存储过程
-- 修改表 添加检查约束 余额不能为负数 ALTER TABLE ACCOUNT ADD CONSTRAINT ck_balance CHECK(balance>=0); -- 插入测试数据 INSERT INTO ACCOUNT(accid,accname,PASSWORD,balance,state,accdate)VALUES (1,'zhang','123456',500,1,NOW()), (2,'wang','123456',1000,1,NOW()); -- 存储过程 转账多次，当余额小于0时，会触发检查约束，异常。 DROP PROCEDURE IF EXISTS transfer; DELIMITER $$ CREATE PROCEDURE transfer(money FLOAT,fromAcc INT,toAcc INT) -- 不支持 or replace BEGIN #declare exit handler for sqlstate '23000' #开启事务 START TRANSACTION; UPDATE ACCOUNT SET balance=balance+money WHERE accid=toAcc; UPDATE ACCOUNT SET balance=balance-money WHERE accid=fromAcc; #提交事务 COMMIT; # ROLLBACK; END$$ DELIMITER ; -- 调用过程 CALL transfer(500,1,2); -- ROLLBACK; SELECT * FROM ACCOUNT;
Java 代码，通过 jdbc 调用有参存储过程，注意传参，但是无法获取影响行数
/**
* 调用存储过程，传入参数
*/
public static void callProcedure01(){
try ( Connection conn = ConnectionUtils.getConn();
CallableStatement callableStatement = conn.prepareCall("CALL transfer(?,?,?)");
){
callableStatement.setFloat(1,500f);
callableStatement.setInt(2,2); // 转出的账户
callableStatement.setInt(3,1); // 转入的账户
// 注意：存储过程调用方法 返回的结果无法获取影响行数
int i = callableStatement.executeUpdate();
//获取存储过程执行影响行数
int updateCount = callableStatement.getUpdateCount();
System.out.println(i+"====="+updateCount); //默认获取不到的，因为mysql 存储过程执行过程中结果确实是0
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}3. 有入参和出参存储过程
通过调用存储过程实现转账，并通过 out 参数获取影响的行数
先新建存储过程：
#-------------out 参数存储过程---------------------
DROP PROCEDURE IF EXISTS transferOutParam;
DELIMITER $$
CREATE PROCEDURE transferOutParam(money FLOAT,fromAcc INT,toAcc INT,OUT res INT) -- 不支持 or replace
BEGIN
#declare exit handler for sqlstate '23000'
DECLARE affect_rows INT DEFAULT 0; -- 声明语句必须放在语句前面
SET res = 0;
#开启事务
START TRANSACTION;
UPDATE ACCOUNT SET balance=balance+500 WHERE accid=toAcc;
SELECT ROW_COUNT() INTO @affect_rows; -- 获取影响行数
SET res = res+@affect_rows; -- 累计影响的行数
UPDATE ACCOUNT SET balance=balance-500 WHERE accid=fromAcc;
SELECT ROW_COUNT() INTO @affect_rows;
SET res = res+@affect_rows; -- 累计最近更新影响的行数
#提交事务
COMMIT;
# ROLLBACK;
END$$
DELIMITER ;
-- 调用有out 参数的过程
CALL transferOutParam(500,2,1,@res);
SELECT @res;4. 查询结果的存储过程
实现根据id 查信息的存储过程
DROP PROCEDURE IF EXISTS getInfo;
DELIMITER $$
CREATE PROCEDURE getInfo(aid INT)
BEGIN
SELECT accid,accname,balance FROM ACCOUNT WHERE accid=aid;
END$$
DELIMITER ;
CALL getInfo(1);Java代码
/**
* 调用存储过程，获取查询结果
*/
public static void callProcedure03(){
try ( Connection conn = ConnectionUtils.getConn();
CallableStatement callableStatement = conn.prepareCall("CALL getInfo(?)");
){
callableStatement.setFloat(1,1);
ResultSet resultSet = callableStatement.executeQuery();
while (resultSet.next()){
int accid = resultSet.getInt("accid");
String accname = resultSet.getString("accname");
float money = resultSet.getFloat("balance");
System.out.println(accid+"==="+accname+"==="+money);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}八、自己封装底层 DAO 类。
下面封装一个方便后期使用的连接数据库操作的底层类，类中包括了连接的创建，关闭，执行更新，查询等，但是适用于单次操作，访问量不大的场景，否则还是推荐使用连接池。
package jdbc.demo1;
/**
* DAO:DataBase Access Object
* 数据库访问操作底层类
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
public class BaseDao {
private String url = "jdbc:mysql://127.0.0.1:3306/bank";
private String driver = "com.mysql.jdbc.Driver";
private String username = "root";
private String password = "root";
private Connection conn;
private PreparedStatement pst;
private ResultSet rst;
/**
* 获得连接
*/
protected Connection getConnection() {
if (conn == null) {
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
}
return conn;
}
/**
* 预编译更新方法
* insert into table()values(?,?,?)
*/
protected int update(String sql,Object... params) {
System.out.println(">>>>==="+sql);
System.out.println(">>>>=Param:="+Arrays.toString(params));
getConnection();
try {
pst = conn.prepareStatement(sql);
//给占位符赋值
if(params!=null) {
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1, params[i]);
}
}
int rows = pst.executeUpdate();
return rows;
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return -1;
}
/**
* 预编译的查询方法
*/
protected ResultSet query(String sql,Object... params) {
System.out.println(">>>>>=="+sql);
System.out.println(">>>>params:"+Arrays.toString(params));
getConnection();
try {
pst = conn.prepareStatement(sql);
if(params!=null&¶ms.length>0) {
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1, params[i]);
}
}
return pst.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}finally {
/注意 这里不能关闭 只有在使用完 ResultSet 后才能关闭
}
return null;
}
/**
* 关闭连接 释放资源
*/
protected void close() {
if (rst != null) {
try {
rst.close();
rst = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pst != null) {
try {
pst.close();
pst = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
八、开源组件dbutils
上面自己封装的类中，有一个缺点，就是查询时，获取的是一个结果集，在结果集没有被使用完之前，是不能关闭连接释放资源的。而且从结果集中提取记录并封装成实体对象是一件很繁琐且容易出错的事情。所以在开发项目中，为了提高开发效率及减少出错率，大家都会选择第三方的组件。比如：DbUtils就是一款优秀的开源组件。
DBUtils是apache下的一个小巧的JDBC轻量级封装的工具包，其最核心的特性是结果集的封装，可以直接将查询出来的结果集封装成JavaBean，这就为我们做了最枯燥乏味、最容易出错的一大部分工作。
下载地址：DbUtils – Download Apache Commons DbUtils
下载 commons-dbutils-1.6.jar，按上面第五步开头进行导入到项目中。
QueryRunner类提供了两个构造方法：
（1） QueryRunner()：默认的构造方法
（2）QueryRunner(DataSource ds)：需要一个 javax.sql.DataSource 来作参数的构造方法。
如果构造时，提供了数据源，则在调用方法执行时，不要再传入 Connection连接对象了
下面通过案例分别介绍下常用CRUD方法的实现及查询的自动封装使用。
package jdbc.util;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
/**
* 本类演示使用dbutils的数据插入，更新和删除
*
*/
public class TestDbUtils {
public static void main(String[] args) {
insert2();
// insert1();
}
/**
* 插入操作并返回记录主键
* 使用update方法，可以执行 insert，update，delete。返回的结果都为影响的行数
*/
public static void insert2() {
ConnectionUtil connUtil = new ConnectionUtil();
//获取连接
Connection conn = connUtil.getConnection();
//创建QueryRunner对象
QueryRunner qr = new QueryRunner();
//下面sql分别为插入，修改，删除，可以分别选一种演示
// String sql = "INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE)VALUES(?,?,?,?)";
// String sql = "UPDATE ACCOUNT SET PASSWORD=? WHERE ACCID IN(?,?)";
String sql = "DELETE FROM ACCOUNT WHERE ACCID=?";
//给占位符赋值的参数列表
// Object[] params = {"zhangsan","222222",2000,0};
// Object[] params = {"123456",1,2};//修改的参数
Object[] params = {2}; //删除的参数
try {
//执行插入并返回影响的行数
int rows = qr.update(conn, sql, params); // 如果在构造时，提供了数据源，则不需要传入conn
System.out.println(">>>>影响的行数=="+rows);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//connUtil.close(conn, null, null);
//也可以使用DbUtils提供的静态方法来进行关闭
try {
DbUtils.close(connection);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/**
* 插入操作并返回记录主键
* 如果插入记录时，还需要获取插入的记录的主键 ，可以使用下面的insert()方法
*/
public static void insert1() {
ConnectionUtil connUtil = new ConnectionUtil();
//获取连接
Connection conn = connUtil.getConnection();
//创建QueryRunner对象
QueryRunner qr = new QueryRunner();
String sql = "INSERT INTO ACCOUNT(ACCNAME,PASSWORD,BALANCE,STATE)VALUES(?,?,?,?)";
//给占位符赋值的参数列表
Object[] params = {"wang","123456",1000,1};
//Account表主键为自动增长，使用ScalarHandler可以在执行insert后获取生成主键
ScalarHandler<Long> handler = new ScalarHandler<Long>();
try {
//执行插入并返回主键
Long id = qr.insert(conn,sql, handler, params);
System.out.println(">>>>主键=="+id);
} catch (SQLException e) {
e.printStackTrace();
}finally {
connUtil.close(conn, null, null);
}
}
}
下面是查询单个对象 Bean 及 列表Bean 的案例
package jdbc.util;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import jdbc.pojo.Account;
/**
* 本类演示使用dbutils 查询操作，这是最常用和方便的方式
*
*/
public class TestDBUtilsQuery {
public static void main(String[] args) {
// queryOne();
queryAll();
}
/**
* 查询多个对象需要BeanHandler
*/
public static void queryAll() {
ConnectionUtil connUtil = new ConnectionUtil();
// 获取连接
Connection conn = connUtil.getConnection();
// 创建QueryRunner对象
QueryRunner qr = new QueryRunner();
//查询语句 注意，查询的字段名一定要属性名一致，否则属性值不能自动封装到对象中。如果不一致可以使用as 取别名
String sql = "select accid,accname,password,balance,accDate from account";
//实例化Handler,指定了封装的实体类
BeanListHandler<Account> rsh = new BeanListHandler<Account>(Account.class);
try {
List<Account> accounts = qr.query(conn, sql, rsh);
//循环查看结果
for (Account account : accounts) {
System.out.println(account.getAccid()+"=="+account.getAccname()+"=="+account.getBalance());
}
} catch (SQLException e) {
e.printStackTrace();
}finally {//这里就可以直接关闭了，因为已经不再使用ResultSet，不需要保持连接了
connUtil.close(conn, null, null);
}
}
/**
* 查询一个对象需要BeanHandler
*/
public static void queryOne() {
ConnectionUtil connUtil = new ConnectionUtil();
// 获取连接
Connection conn = connUtil.getConnection();
// 创建QueryRunner对象
QueryRunner qr = new QueryRunner();
//查询语句 注意，查询的字段名一定要属性名一致，否则属性值不能自动封装到对象中。如果不一致可以使用as 取别名
String sql = "select accid,accname,password,balance,accDate from account where accid=3";
//实例化Handler,指定了封装的实体类
BeanHandler<Account> rsh = new BeanHandler<Account>(Account.class);
try {
Account account = qr.query(conn, sql, rsh);
System.out.println(account.getAccname()+"===="+account.getPassword());
} catch (SQLException e) {
e.printStackTrace();
}finally {//这里就可以直接关闭了，因为已经不再使用ResultSet，不需要保持连接了
connUtil.close(conn, null, null);
}
}
}
DbUtils 中的各种 Handler
-
ArrayHandler：把结果集中的第一行数据转成对象数组。
-
ArrayListHandler：把结果集中的每一行数据都转成一个对象数组，再存放到List中。
-
BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
-
BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
-
ColumnListHandler：将结果集中某一列的数据存放到List中。
-
KeyedHandler：将结果集中的每一行数据都封装到一个Map里，然后再根据指定的key把每个Map再存放到一个Map里。
-
MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
-
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。
-
ScalarHandler：将结果集中某一条记录的其中某一列的数据存成Object。
扫一扫
566
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
