基本介绍
1)JDBC为访问不同的数据库提供了统一的接口;
2)Java程序员使用JDBC,可以连接任何提供了JDBC驱动程序的数据库,从而完成对数据库的各种操作;
3)JDBC基本原理:Java程序提供接口规范,让不同数据库实现,在Java程序中统计调用接口的方法即可
4)模拟JDBC实现:创建数据库连接接口(Interface)→数据库实现接口创建操作方法→创建实现对象赋给接口引用,通过接口实现对象完成对数据库的操作。
JDBC带来的好处
1)如果Java直接访问数据库:(不便于维护,更新,拓展)
Java Application | |||
↓ | ↓ | ↓ | ↓ |
MySQL | Oracle | SQLSever | DB2 |
2)JDBC带来的好处:
Java Application | |||
调 ↓ 用 | |||
JDBC(一组规范:接口) | |||
实 ↑ 现 | 实 ↑ 现 | 实 ↑ 现 | 实 ↑ 现 |
JDBCMySQLImplement | JDBCOracleImplement | JDBCSQLSeverImplement | JDBCDB2Implement |
调 ↓ 用 | 调 ↓ 用 | 调 ↓ 用 | 调 ↓ 用 |
MySQL | Oracle | SQLSever | DB2 |
3)JDBC是Java提供一套用于数据库操作的接口API,Java程序员只需要面向这套接口编程即可。不同的数据库厂商,需要针对这套接口,提供不同实现;
JDBC API
JDBC API是一系列的接口,他统一和规范了应用程序与数据库的链接、执行SQL语句,并得到返回结果等各类操作,相关类和接口在java.sql与javax.sql包中。
JDBC程序编写步骤
1)注册驱动-加载Driver类;
2)获取链接-得到Connection(代表客户端到数据库之间的连接);
3)执行增删改查-发送相关的SQL命令给MySQL执行(MySQL返回结果集);
4)释放资源。
前置工作(添加mysql-connector-java(mysql.jar驱动)到项目新建文件夹中(libs)) | |||
↓ | |||
第一步,注册驱动 | |||
①将添加到项目中的mysql.jar文件点击 add to project...连接到项目中 | ②创建dirver对象: Driver driver = new Driver; | ||
第二步,获取链接 | |||
①创建链接: String url = "jdbc:mysql://localhost:3306/pero_db02"; -- url:连接数据库地址 -- jdbc:mysql:// 规定,表示协议:通过jdbc连接MySQL -- localhost: 表示数据库主机地址可以ip地址 -- 3306/ 端口号 -- pero_db02 数据库名称 | ②创建properties对象,将数据库登陆用户名和密码放入到properties中; Properties properties = new Properties(); //user 和 password 是规定好的,后面的值根据实际情况写 properties.setProperty("user","root"); properties.setProperty("password","pero"); | ||
③获取链接:调用driver对象的connect方法,将连接路径url和properties放入,返回链接; Connection connect = driver.connect(url, properties); | |||
第三步,执行相关SQL语句 | |||
①创建SQL语句赋值给String引用 String sql = "insert into actor values(null,'张华','男','1970-01-10','13335567799')"; | ②调用connect对象的createStatement方法,获取statement对象,用于执行静态SQL语句并返回其生成的结果的对象;Statement statement = connect.createStatement(); | ||
③调用statement对象的executeUpdate()方法 //如果是dml语句,返回受影响的行数(执行成功返回>0的数值,执行失败返回0) int rows = statement.executeUpdate(sql); System.out.println(rows > 0 ? "成功" : "失败"); | |||
第四步,关闭连接释放资源 | |||
①关闭statement: statement.close(); | ②关闭connect: connect.close(); |
获取数据库连接5种方式
package com.pero.jdbc;
import com.mysql.jdbc.Driver;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author Pero
* @version 1.0
*/
public class JdbcConn {
//方式一:
@Test
public void connect01() throws SQLException {
//获取加载对象
Driver driver = new com.mysql.jdbc.Driver();
//创建链接路径
String url = "jdbc:mysql://localhost:3306/pero_db02";
//获取properties对象
Properties properties = new Properties();
//将用户和密码存入properties对象中
properties.setProperty("user","root");
properties.setProperty("password","pero");
//建立连接
Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
//方式二:使用反射机制动态加载Driver类,更加灵活
@Test
public void conn02() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
//使用反射获取driver对象
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
//创建连接路径
String url = "jdbc:mysql://localhost:3306/pero_db02";
//获取properties对象并传入用户名和密码
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","pero");
//建立连接
Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
//方式三:使用DriverManager替换Driver进行统一管理,扩展性更好
@Test
public void conn03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
//创建连接路径、用户、密码
String url = "jdbc:mysql://localhost:3306/pero_db02";
String user = "root";
String password = "pero";
//注册Driver驱动
DriverManager.registerDriver(driver);
//建立连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
//第四种:在加载Driver类时,Driver类中有一段静态代码,会默认自动完成注册驱动
@Test
public void conn04() throws ClassNotFoundException, SQLException {
//Class.froName()自动完成注册驱动简化方法三的代码
/*源码分析:
static { //静态代码块,在类加载时会执行一次
try {
DriverManager.registerDriver(new Driver()); //注册Driver驱动
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
*/
//注意:①mysql驱动5.1.6可以无需Class.forName("com.mysql.jdbc.Driver");来加载驱动
// ②jdk1.5以后使用了jdbc4,不再需要显式调用Class.forName()注册驱动,而是自动调用驱动jar包
// 下的META-INF\services\java.sql.Driver文本中的类名称去注册
Class.forName("com.mysql.jdbc.Driver"); //在加载Driver类时自动完成注册
//创建连接路径、用户、密码
String url = "jdbc:mysql://localhost:3306/pero_db02";
String user = "root";
String password = "pero";
//建立连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
//第五种:使用配置文件,更灵活地连接数据库
@Test
public void conn05() throws IOException, ClassNotFoundException, SQLException {
//先创建properties文件,将user、password、url、driver信息存入
//通过properties对象获取配置文件信息
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("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
}
配置文件:
user=root
password=pero
url=jdbc:mysql://localhost:3306/pero_db02
driver=com.mysql.jdbc.Driver
ResultSet结果集
基本介绍
1)表示数据库结果集的数据表,通常通过执行查询数据库语句生成;
2)ResultSet对象保持一个光标指向其当前的数据行,初始,光标位于第一行之前;
3)next方法将光标移动到下一行,并且由于在ResultSet对象中没有更多行时返回false,因此可以在while循环中使用循环来遍历结果集。
package com.pero.jdbc.resultSet_;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* 演示查询语句返回ResultSet并取出结果
* @author Pero
* @version 1.0
*/
public class ResultSet_ {
public static void main(String[] args) 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 url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "select * from actor";
/*
* ResultSet底层源码
*
*
* */
ResultSet resultSet = statement.executeQuery(sql);
//使用while循环获取结果集
while(resultSet.next()){ //让光标后移,如果没有更多行返回false
int id = resultSet.getInt(1); //获取该行的第1列
String name = resultSet.getString(2); //获取该行的第2列
String sex = resultSet.getString(3); //获取该行的第3列
Date date = resultSet.getDate(4); //获取该行的第4列
System.out.println(id + "\t" + name + "\t" + sex + "\t" +date);
}
//关闭资源
resultSet.close();
statement.close();
connection.close();
}
}
Statement接口
基本介绍
1)用于执行静态SQL语句并返回其生成结果的对象;
2)在建立连接后,需要对数据库进行访问,执行命令或是SQL语句,可以通过Statement【SQL注入】、PreparedStatement【预处理】、CallableStatement【存储过程】;
3)Statement对象执行SQL语句,存在SQL注入风险;
4)SQL注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的SQL语句段或者命令,恶意攻击数据库;
5)要防范SQL注入,只要用PreparedStatement(从Statement扩展而来)取代Statement就可以。
mysql> select * from admin
-> where name = '1' or' and pwd = 'or '1' = '1';
package com.pero.jdbc.statement_;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
/**
* @author Pero
* @version 1.0
*/
public class Statement_ {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名:");
//用户输入管理员姓名和密码 scanner.next()遇到空格会认为命令结束
String admin_name = scanner.nextLine(); //只有回车才会认为命令结束
System.out.print("请输入用户密码:");
String admin_pwd = scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
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();
}
}
PreparedStatement(Statement子接口)
基本介绍
1)PreparedStatement执行的SQL语句中的参数用问号(?占位符)来表示,调用PreparedStatement对象的setXxx()方法来设置参数;setXxx()方法有两个参数,第一个参数是要设置的SQL语句中的参数的索引(从1开始),第二个参数时设置的SQL语句中的参数的值;
2)调用executeQuery(),返回ResultSet对象(查询);
3)调用executeUpdate(),执行更新(增、删、改),返回影响的行数int。
优点:
1)不再使用+拼接sql语句,避免了不必要的语法错误;
2)有效地解决了sql注入问题;
3)减少了编译次数,提高效率。
package com.pero.jdbc.preparedStatement;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
/**
* @author Pero
* @version 1.0
*/
public class PreparedStatement_ {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入删除的用户名:");
String admin_name = scanner.nextLine();
System.out.print("请输入用户密码:");
String admin_pwd = scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
//String sql = "select name, pwd from admin where name = ? and pwd = ?";
//添加
//String sql = "insert into admin values (?,?)";
//修改
//String sql = "update admin set pwd = ? where name = ?";
//删除
String sql = "delete from admin where name = ? and pwd = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,admin_name); //与sql语句中的?对应
preparedStatement.setString(2,admin_pwd); //与sql语句中的?对应
//查询调用executeQuery(),增删改调用executeUpdate();
// ResultSet resultSet = preparedStatement.executeQuery(); //此处不用再写sql语句,sql语句已经和preparedStatement同步
// if(resultSet.next()){
// System.out.println("登陆成功!");
// }else {
// System.out.println("登陆失败!");
// }
int i = preparedStatement.executeUpdate();
System.out.println(i > 0 ? "删除成功" : "删除失败");
//关闭资源
//resultSet.close();
preparedStatement.close();
connection.close();
}
}
课后作业
package com.pero.jdbc_;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* @title:PreparedStatementTest
* @Author Pero
* @Date 2022/10/22 19:42
* @Version 1.0
*/
public class PreparedStatementTest {
public static void main(String[] args) 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 url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
//添加数据
String[] names = {"tom","jack","lucy","pero","smith"};
int[] passwords = {321456,654321,789654,456357,487651};
String sql_insert = "insert into admin values (?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql_insert);
for (int i = 0; i < 5; i++) {
preparedStatement.setString(1,names[i]);
preparedStatement.setInt(2,passwords[i]);
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "添加成功" : "添加失败");
}
//修改数据
String sql_update = "update admin set name = ? where name = ? and password = ?";
PreparedStatement preparedStatement1 = connection.prepareStatement(sql_update);
preparedStatement1.setString(1,"king");
preparedStatement1.setString(2,"tom");
preparedStatement1.setInt(3,321456);
int update_rows = preparedStatement1.executeUpdate();
System.out.println(update_rows > 0 ? "修改成功" : "修改失败");
//删除数据
String sql_delete = "delete from admin where name = ?";
PreparedStatement preparedStatement2 = connection.prepareStatement(sql_delete);
preparedStatement2.setString(1,"pero");
int delete_rows = preparedStatement2.executeUpdate();
System.out.println(delete_rows > 0 ? "删除成功" : "删除失败");
//查询数据
String sql_select = "select * from admin";
PreparedStatement preparedStatement3 = connection.prepareStatement(sql_select);
ResultSet resultSet = preparedStatement3.executeQuery();
while (resultSet.next()){
String name = resultSet.getString(1);
int user_password = resultSet.getInt(2);
System.out.println(name + "\t" + user_password);
}
//关闭资源
resultSet.close();
preparedStatement3.close();
preparedStatement2.close();
preparedStatement1.close();
preparedStatement.close();
connection.close();
}
}
JDBC API常用类及其常用方法
JDBC API | 常用类 | 常用类的常用方法 |
DriverManager驱动管理类 | getConnection(url,user,pwd),获取链接 | |
Connection接口 | createStatement(),创建Statement对象 | |
preparedStatement(),生成预处理对象 | ||
Statement接口【有注入风险】 | executeUpdate(sql),执行DML语句返回影响的行数 | |
executeQuery(sql),执行查询返回ResultSet对象 | ||
execute(sql),执行任意地sql语句返回Boolean值 | ||
PreparedStatement接口 | executeUpdate(sql),执行DML语句返回影响行数 | |
executeQuery(),执行查询返回ResultSet | ||
execute(sql),执行任意地sql语句返回Boolean值 | ||
setXxx(占位符索引,占位符的值),给指定位置的赋值 | ||
setObject(占位符索引,占位符的值),按照对象赋值 | ||
ResultSet【结果集】 | next(),向下移动一行,如果没有下一行返回false | |
previous(),向上移动一行,如果没有上一行返回false | ||
getXxx(列的索引|列名),获取对应列的值,接收的类型是Xxx类型 | ||
getObject(列的索引|列名),获取对应列的值,接受类型是Object类型 |
封装JDBCUtils
在jdbc操作中,获取链接和释放资源是经常使用到的,可以将其封装到JDBC连接的工具类JDBCUtils中
package com.pero.utils;
import java.io.FileInputStream;
import java.io.FilterReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* @title:JDBCUtils
* @Author Pero
* @Date 2022/10/22 21:12
* @Version 1.0
*/
public class JDBCUtils {
//定义属性(user、password、url、driver),只使用一次定义为静态
private static String user;
private static String password;
private static String url;
private static String driver;
//在静态代码块中初始化
static {
try {
Properties properties = new Properties();
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);
}
}
//关闭资源
/**
* ResultSet、Statement或者PreparedStatement、Connection
* 如果需要关闭资源就传入对象,否则传入null
*/
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);
}
}
}
工具类JDBCUtils的使用
package com.pero.utils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @title:JDBCUtils_Use
* @author Pero
* @date 2022/10/22 21:57
* @version 1.2
*/
public class JDBCUtilsUse {
@Test
public void testInsert(){
Connection connection = null;
String sql_inset = "insert into admin values (?,?)";
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql_inset);
preparedStatement.setString(1,"tom");
preparedStatement.setInt(2,852456);
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
@Test
public void testUpdate(){
String sql_update =
"update admin set password = ? where name = ? and password = ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql_update);
preparedStatement.setInt(1,123456);
preparedStatement.setString(2,"tom");
preparedStatement.setInt(3,852456);
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
@Test
public void testDelete(){
String sql_delete = "delete from admin where name = ? and password = ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql_delete);
preparedStatement.setString(1,"tom");
preparedStatement.setInt(2,123456);
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
@Test
public void testSelect(){
String sql_select = "select * from admin";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql_select);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String name = resultSet.getString(1);
int password = resultSet.getInt(2);
System.out.println(name + "\t" + password);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(resultSet,preparedStatement,connection);
}
}
}
事务
1)JDBC程序中当一个Connection对象创建时,默认情况下是自动提交事务:每次执行一个SQL语句时,如果执行成功,就会向数据库自动提交事务,不能回滚;
2)JDBC程序中为了让多个SQL语句作为一个整体执行,会使用到事务;
3)调用Connection的setAutoCommite(false)可以取消自动提交事务;
4)在所有的SQL语句都成功执行后,调用Connection对象的commit()方法提交事务;
5)在其中某个操作失败或出现异常时,调用Connection对象的rollback()方法回滚事务。
测试代码
package com.pero.jdbc_.transcation_;
import com.pero.utils.JDBCUtils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
/**
* @author Pero
* @version 1.0
* @title: Transaction_
* @date 2022/10/26 17:01
*/
public class Transaction_ {
@Test
public void noTransaction(){
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql_update1 = "update account set balance = balance - 100 where id = 1";
String sql_update2 = "update account set balance = balance + 100 where id = 2";
try {
connection = JDBCUtils.getConnection(); //事务默认自动提交
preparedStatement = connection.prepareStatement(sql_update1);
preparedStatement.executeUpdate();
int i = 1 / 0 ; //抛出异常后面语句不再执行,事务执行到此提交
preparedStatement = connection.prepareStatement(sql_update2);
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
@Test
public void useTransaction(){
Connection connection = null;
PreparedStatement preparedStatement = null;
Savepoint point = null;
String sql_update01 = "update account set balance = balance - 100 where name = ?";
String sql_update02 = "update account set balance = balance + 100 where name = ?";
try {
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false); //将事务自动提交关闭
point = connection.setSavepoint("point");//设置保存点
preparedStatement = connection.prepareStatement(sql_update01);
preparedStatement.setString(1,"马云");
preparedStatement.executeUpdate();
int i = 1 / 0;
preparedStatement = connection.prepareStatement(sql_update02);
preparedStatement.setString(1,"马化腾");
preparedStatement.executeUpdate();
connection.commit();
} catch (SQLException e) {
//在此处回滚,撤销执行sql命令
try {
connection.rollback(point);
} catch (SQLException ex) {
ex.printStackTrace();
}
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
}
批处理
基本介绍
1)当需要成批插入或者更新数据时,可以采用java的批量处理机制,多条语句一次性提交给数据库来批量处理;
2)JDBC的批处理语句包括如下方法:①addBatch():添加需要批处理的SQL语句或参数;②executeBatch():执行批量处理语句;③clearBatch():清空批处理包的语句;
3)JDBC链接MySQL时,使用批处理功能则需要在url中添加参数:
?rewriteBatchedStatements=true;
4)批处理往往和preparedStatement一起搭配使用,可以减少编译次数、运行次数,使效率大大提高。
测试代码
package com.pero.batch_;
import com.pero.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* @author Pero
* @version 1.0
* @title: Batch_
* @date 2022/10/26 20:38
*/
public class Batch_ {
@Test
public void noBatch(){
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = "insert into admins values (null, ?, ?)";
System.out.println("开始执行");
long start = System.currentTimeMillis();
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 2000; i++) {
preparedStatement.setString(1, "jack" + i);
preparedStatement.setInt(2,123+i);
preparedStatement.executeUpdate();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
long end = System.currentTimeMillis();
System.out.println("执行结束,耗时:" + (end - start));
}
@Test
public void batch() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = "insert into admin values (null, ?, ?)";
System.out.println("开始执行批量处理");
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 2000; i++) {
preparedStatement.setString(1,"jack" + i);
preparedStatement.setInt(2,123);
preparedStatement.addBatch();
if ((i + 1) % 1000 == 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
JDBCUtils.close(null,preparedStatement,connection);
long end = System.currentTimeMillis();
System.out.println("执行完毕,耗时:" + (end - start));
}
}
源码分析
public void addBatch() throws SQLException {
synchronized(this.checkClosed().getConnectionMutex()) {
if (this.batchedArgs == null) { //第一次创建一个batchedArgs为ArrayList集合存放elementData对象Object数组(存放的是sql语句)的集合,存储数据满后按照1.5被扩容
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 BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
}
}