一.建立连接并开启事务与设置事务隔离级别:
SET AUTOCOMMIT = 0;
connection.setAutoCommit(false);
private String url = "jdbc:mysql://127.0.0.1:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
private String userName = "root";
private String password = "123456";
private String jdbcDriver = "com.mysql.jdbc.Driver"; //jdbc 驱动
private Connection connection;
public void createConnection() {
try {
Class.forName(jdbcDriver);
try {
connection = DriverManager.getConnection(url, userName, password);
//getConnection:就是jdbc提供给我们建立连接的方法 登录到mysql服务器的过程
if (!connection.isClosed()) {
System.out.println("连接建立成功");
connection.setAutoCommit(false); // SET AUTOCOMMIT = 0;
connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
} else {
System.out.println("连接建立失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
二.事务的基本操作:
(1)根据数据库中的表添加数据
public void insertTest(PreparedStatement pre,int id,String name) {
try {
pre.setInt(1, id);
pre.setString(2, name);
pre.executeUpdate(); //执行这条sql
} catch (SQLException e) {
e.printStackTrace();
}
}
(2)查询方法
public void selectTest(PreparedStatement sel,String sql){
try {
ResultSet resultSet = sel.executeQuery(sql);
while (resultSet.next()) {//resultSet.next() 判断集合是否有数据
System.out.println(
"id: " + resultSet.getInt(1) +
" name: " + resultSet.getString(2)
);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
(3)roolback回滚方法的测试
public void rollbackTest(){ //测试回滚方法正常
String insert = "insert test values(?,?)";
String select = "select * from test";
try {
PreparedStatement pre = connection.prepareStatement(insert);
PreparedStatement sel = connection.prepareStatement(select);
insertTest(pre,4,"李四");
selectTest(sel,select);
System.out.println("rollback:");
connection.rollback();
selectTest(sel,select);
} catch (SQLException e) {
e.printStackTrace();
}
}
(4)通过保存点进行rollback回滚方法的测试
public void rollbackBypoint(){
String insert = "insert test values(?,?)";
String select = "select * from test";
try {
PreparedStatement pre = connection.prepareStatement(insert);
PreparedStatement sel = connection.prepareStatement(select);
insertTest(pre,6,"王五");
Savepoint p1 = connection.setSavepoint();
insertTest(pre,7,"小芳");
selectTest(sel,select);
System.out.println("rollback to p1:");
connection.rollback(p1);
selectTest(sel,select);
} catch (SQLException e) {
e.printStackTrace();
}
}
(5)commit方法的测试,提交数据之后就对数据造成持久性修改,无法再进行回滚
public void commitTest(){
String insert = "insert test values(?,?)";
String select = "select * from test";
try {
PreparedStatement pre = connection.prepareStatement(insert);
PreparedStatement sel = connection.prepareStatement(select);
insertTest(pre,5,"小刘");
selectTest(sel,select);
connection.commit();
connection.rollback();
System.out.println("commit:");
selectTest(sel,select);
} catch (SQLException e) {
e.printStackTrace();
}
}
(6)关闭连接,避免造成资源浪费
public void closeConnection() {
try {
connection.close(); //当不在使用mysql时关闭mysql连接 方式资源浪费
} catch (SQLException e) {
e.printStackTrace();
}
}