这里只是对jdbc及进行简单的入门与使用,详细的教程戳这里,因为咱也是学人家的。OK,直接开整,项目代码地址:https://gitee.com/fluffycatkin/yyx-study-mybatis.git
一、JDBC事务
当使用jdbc同时执行多次新增或修改操作时候,就需要考虑到业务流程的完整性,这时候就需要对数据库事务进行控制。jdbc默认自动提交事务的,我们可将其设置为手动提交事务,对事务进行控制。
1、jdbc对事务的操作方法
- 将事务设置为手动提交:
void setAutoCommit(boolean autoCommit) throws SQLException;
- 事务提交:
void commit() throws SQLException;
- 事务回滚:
void rollback() throws SQLException;
- 设置事务保存点:
Savepoint setSavepoint() throws SQLException;
Savepoint setSavepoint(String name) throws SQLException;
- 删除事务保存点:
void releaseSavepoint(Savepoint savepoint) throws SQLException;
- 将事务回滚到保存点:
void rollback(Savepoint savepoint) throws SQLException;
2、事务自动提交
下面我们对以下两个sql语句进行操作:
insert into user (user_name,age) values ('元始天尊',9998);
insert into user (user_name,age) values ('无量天尊',9998);
首先,我们使用自动提交事务,在两个插入操作之间通过
【int num = 1/0】抛出异常;代码如下:
package cn.fpg.mybatis.jdbc;
import cn.fpg.mybatis.pojo.User;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @program: yyx-study-mybatis
* @description: jdbc事务测试
* @author: fluffycatkin
* @create: 2020-07-06 14:47
**/
@Slf4j
public class JdbcTrans {
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://192.168.170.129:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC";
private static final String USER_NAME = "root";
private static final String PASSWORD = "123456";
private Connection connection = null;
private Statement statement = null;
private ResultSet resultSet = null;
@Test
public void testJdbcTrans() {
List<User> users = new ArrayList<>();
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL,USER_NAME,PASSWORD);
//关闭自动提交
// connection.setAutoCommit(false);
String insertSql1 = "insert into user (user_name,age) values ('元始天尊',9998);";
String insertSql2 = "insert into user (user_name,age) values ('无量天尊',9998);";
statement = connection.createStatement();
//设置事务保存点
// Savepoint savepoint1 = connection.setSavepoint("savepoint1");
//执行插入操作1
statement.execute(insertSql1);
//报错
int num = 1/0;
//回滚事务保存点1
// connection.rollback(savepoint1);
// Savepoint savepoint2 = connection.setSavepoint("savepoint2");
//执行插入操作2
statement.execute(insertSql2);
//回滚事务保存点2
// connection.rollback(savepoint2);
//提交事务
// connection.commit();
String querySql = "select * from user";
resultSet = statement.executeQuery(querySql);
DefaultJdbc.handleRs(this.resultSet, users);
users.forEach(System.out::println);
this.resultSet.close();
statement.close();
connection.close();
} catch (Exception e ) {
if (connection!=null){
// try {
// connection.rollback();
// } catch (SQLException e1) {
// e1.printStackTrace();
// }
}
e.printStackTrace();
} finally {
System.out.println("释放资源...");
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
操作之前user表的数据:
在执行之后,user表数据如下,可见在抛出异常之前的insert操作成功的被执行,而异常之后的数据并没有插入成功。
3、设置事务手动控制
同样是对上面两个sql语句进行操作,然后将以下代码注释解开:
......
//将事务设置为手动提交
connection.setAutoCommit(false);
......
//提交事务
connection.commit();
......
try {
//事务回滚
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
在执行前,user表数据如下:
在执行之后如下,我们可以看到在第一个insert操作完成之后,如果抛出了异常,数据库数据并没有增加:
4、通过设置事务保存点控制事务
我们也可以使用事务保存点,将事务回滚到自己指定的业务逻辑的位置:
package cn.fpg.mybatis.jdbc;
import cn.fpg.mybatis.pojo.User;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @program: yyx-study-mybatis
* @description: jdbc事务测试
* @author: fluffycatkin
* @create: 2020-07-06 14:47
**/
@Slf4j
public class JdbcTrans {
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC";
private static final String USER_NAME = "root";
private static final String PASSWORD = "fluffycatkin";
private Connection connection = null;
private Statement statement = null;
private ResultSet resultSet = null;
@Test
public void testJdbcTrans() {
List<User> users = new ArrayList<>();
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL,USER_NAME,PASSWORD);
//关闭自动提交
connection.setAutoCommit(false);
String insertSql1 = "insert into user (user_name,age) values ('元始天尊',9998);";
String insertSql2 = "insert into user (user_name,age) values ('无量天尊',9998);";
statement = connection.createStatement();
//设置事务保存点
Savepoint savepoint1 = connection.setSavepoint("savepoint1");
//执行插入操作1
statement.execute(insertSql1);
//报错
// int num = 1/0;
Savepoint savepoint2 = connection.setSavepoint("savepoint2");
//执行插入操作2
statement.execute(insertSql2);
//回滚事务保存点2
connection.rollback(savepoint1);
//提交事务
connection.commit();
String querySql = "select * from user";
resultSet = statement.executeQuery(querySql);
DefaultJdbc.handleRs(this.resultSet, users);
users.forEach(System.out::println);
this.resultSet.close();
statement.close();
connection.close();
} catch (Exception e ) {
if (connection!=null){
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
} finally {
System.out.println("释放资源...");
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
以上代码,我在两个insert操作之前分别设置了事务保存点savepoint1和savepoint2,首先在异常处理里面,通过【connection.rollback(savepoint1)】将事务回滚到第一个事务保存点savepoint1,代码执行前:
代码执行后如下,发现两个操作都没有成功:
OK,接下来将异常处理中的回滚操作设置到第二个事务保存点【connection.rollback(savepoint2)】,执行之前:
执行之后如下,可见事务只回滚到第二个insert操作之前,而第一个insert操作并没有回滚,插入成功:
二、JDBC批处理
当需要同时插入大量数据的时候,批处理操作可以明显的减少性能开销,提升插入效率。
1、通过Statement进行批处理
- 使用createStatement()方法创建Statement对象。
- 使用setAutoCommit()将自动提交设置为false。
- 使用addBatch()方法在创建的Statement对象上添加SQL语句到批处理中。
- 在创建的Statement对象上使用executeBatch()方法执行所有SQL语句。
- 最后,使用commit()方法提交所有更改。
2、通过PrepareStatement进行批处理
- 使用占位符创建SQL语句。
- 使用prepareStatement()方法创建PrepareStatement对象。
- 使用setAutoCommit()将自动提交设置为false。
- 使用addBatch()方法在创建的Statement对象上添加SQL语句到批处理中。在创建的Statement对象上使用executeBatch()方法执行所有SQL语句。
- 最后,使用commit()方法提交所有更改。
代码如下:
package cn.fpg.mybatis.jdbc;
import cn.fpg.mybatis.pojo.User;
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @program: yyx-study-mybatis
* @description: jdbc批处理测试
* @author: fluffycatkin
* @create: 2020-07-06 16:03
**/
public class JdbcBatch {
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC";
private static final String USER_NAME = "root";
private static final String PASSWORD = "fluffycatkin";
private static Connection connection = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
static {
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL,USER_NAME,PASSWORD);
connection.setAutoCommit(false);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
@Test
public void testStatementBatch(){
try {
statement = connection.createStatement();
System.out.println("------------插入之前----------------");
printRows(statement);
System.out.println("------------插入之前----------------");
String insertSql1 = "insert into user (user_name,age) values ('晚年不详',3000)";
String insertSql2 = "insert into user (user_name,age) values ('无量tmd天尊',3000)";
statement.addBatch(insertSql1);
statement.addBatch(insertSql2);
System.out.println("执行批量插入....");
statement.executeBatch();
connection.commit();
System.out.println("------------插入之后----------------");
printRows(statement);
System.out.println("------------插入之后----------------");
close();
} catch (SQLException e) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
@Test
public void testPreparedStatementBatch(){
String insertSql = "insert into user (user_name,age) values (?,?)";
try {
preparedStatement = connection.prepareStatement(insertSql);
preparedStatement.setString(1,"大黑狗");
preparedStatement.setInt(2,1000);
preparedStatement.addBatch();
preparedStatement.setString(1,"老疯子");
preparedStatement.setInt(2,2000);
preparedStatement.addBatch();
preparedStatement.executeBatch();
connection.commit();
printRows(preparedStatement);
close();
} catch (SQLException e) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
public void printRows(Statement stmt) throws SQLException{
System.out.println("插入的结果为...");
String sql = "SELECT * FROM user";
resultSet = stmt.executeQuery(sql);
List<User> users = DefaultJdbc.handleRs(resultSet, new ArrayList<>());
users.forEach(System.out::println);
}
public void close(){
System.out.println("关闭资源...");
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
执行结果:
------------插入之前----------------
User(id=1, userName=张三_千百年后,风采依旧, age=1)
User(id=7, userName=李大白_千百年后,风采依旧, age=3)
User(id=10, userName=武大郎_千百年后,风采依旧, age=4)
User(id=11, userName=女娲_千百年后,风采依旧, age=5)
User(id=12, userName=王五_千百年后,风采依旧, age=6)
User(id=14, userName=盘古, age=9999)
User(id=15, userName=元始天尊, age=9998)
User(id=27, userName=元始天尊, age=9998)
------------插入之前----------------
执行批量插入....
------------插入之后----------------
插入的结果为...
User(id=1, userName=张三_千百年后,风采依旧, age=1)
User(id=7, userName=李大白_千百年后,风采依旧, age=3)
User(id=10, userName=武大郎_千百年后,风采依旧, age=4)
User(id=11, userName=女娲_千百年后,风采依旧, age=5)
User(id=12, userName=王五_千百年后,风采依旧, age=6)
User(id=14, userName=盘古, age=9999)
User(id=15, userName=元始天尊, age=9998)
User(id=27, userName=元始天尊, age=9998)
User(id=29, userName=晚年不详, age=3000)
User(id=30, userName=无量tmd天尊, age=3000)
------------插入之后----------------
关闭资源...
Process finished with exit code 0