1、MySQL:每一条语句都属于独立事务,默认自动管理的。
2、开启事务:start transaction; 日后的语句都会处于同一个事务之中。
提交事务:commit;
回滚事务:rollback;
例子1(数据库中事务的操作命令):
create table account (
id int primary key auto_increment,
name varchar(40),
money float
)character set utf8 collate utf8_general_ci;
insert int account(name,money)values(‘aaa’,1000);
insert int account(name,money)values(‘bbb’,1000);
insert int account(name,money)values(‘ccc’,1000);
start transaction;
update account set money=money-100 where name=’aaa’;
update account set money=money+100 where name=’bbb’;
commit;
事务的常见的命令:
start transaction;
commit;
rollback;
3、JDBC如何控制事务
TxDemo.java文件:
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
//事务控制案例
/*
create table account(
id int primary key auto_increment,
name varchar(40),
money float
)character set utf8 collate utf8_general_ci;
insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);
*/
public class Txdemo {
@Test
public void test1(){
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = JdbcUtil.getConnection();
conn.setAutoCommit(false);//相当于start transaction
stmt = conn.prepareStatement("update account set money=money-100 where name='bbb'");
stmt.executeUpdate();
// int i=1/0;
stmt = conn.prepareStatement("update account set money=money+100 where name='aaa'");
stmt.executeUpdate();
conn.commit();// 提交事务
}catch(Exception e){
throw new RuntimeException(e);
}finally{
JdbcUtil.release(null, stmt, conn);
}
}
}
JdbcUtil.java文件:
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
//工具类
public class JdbcUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static{
try {
ClassLoader cl = JdbcUtil.class.getClassLoader();
InputStream in = cl.getResourceAsStream("dbcfg.properties");
Properties props = new Properties();
props.load(in);
driverClass = props.getProperty("driverClass");
url = props.getProperty("url");
user = props.getProperty("user");
password = props.getProperty("password");
Class.forName(driverClass);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws Exception{
Connection conn = DriverManager.getConnection(url,user, password);
return conn;
}
public static void release(ResultSet rs,Statement stmt,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
dbcnfg.properties文件:
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day16
user=root
password=admin