package transaction;
import JDBC_Utils.JDBCUtils;
import org.junit.Test;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
/**
* 数据库事务:
* 事务:一组逻辑操作单元,使事务从一种状态转换为另一种状态
* 数据一旦提交,就无法回滚
* 关注的问题:哪些操作会自动提交?
* >DDL操作一旦执行,就会自动提交
* >DML操作默认情况下,一旦执行就会自动提交,但是可以修改set_autocommit=false取消DML的自动提交
* >默认在关闭连接的时候,会自动提交数据
*/
public class TransactionTest {
//考虑数据库事务的转账操作:
@Test
public void testUpdateWithTx() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
//取消数据的自动提交功能
conn.setAutoCommit(false);
String sql1="update user_table set balance=balance-100 where user = ?";
update(conn,sql1,"AA");
String sql2="update user_table set balance = balance+100 where user = ?";
update(conn,sql2,"BB");
System.out.println("转账成功!");
conn.commit();//再次提交数据
} catch (Exception e) {
e.printStackTrace();
//回滚数据
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
finally {
try {
//修改回,主要针对使用数据库连接池的时候
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
JDBCUtils.CloseResource(conn,null);
}
}
//通用的增删改操作(考虑事务)
public void update(Connection conn,String sql,Object ...args){//使用可变形参
PreparedStatement ps = null;
try {
//sql当中占位符的个数应该与可变形参的长度一致
//1.获取数据库的连接
ps = conn.prepareStatement(sql);
//2.填充占位符
for(int i=0;i< args.length;i++)
{
ps.setObject(i+1,args[i]);
}
//3.执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//4.资源关闭
JDBCUtils.CloseResource(null,ps);
}
}
@Test
public void testTransactionSelect() throws SQLException, IOException, ClassNotFoundException {
Connection conn = JDBCUtils.getConnection();
System.out.println(conn.getTransactionIsolation());
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
//设置数据库的隔离级别
String sql="select user,password,balance from user_table where user=?";
User user = getInstance(conn, User.class, sql, "CC");
JDBCUtils.CloseResource(conn,null);
System.out.println(user);
}
@Test
public void testTransactionUpdate() throws SQLException, IOException, ClassNotFoundException {
Connection conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);
String sql="update user_table set balance=? where user=?";
update(conn,sql,5000,"CC");
}
/*
针对不同的表格进行查询,返回表的一条记录
考虑了事务,version2.0
*/
public<T> T getInstance(Connection conn,Class<T> clazz,String sql,Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i++)
{
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
int columncount = rsmd.getColumnCount();
if(rs.next()){
T t = clazz.newInstance();
//要给customer赋值为取得的某个属性
//处理结果集一行数据中的每一个列
for(int i=0;i<columncount;i++){
Object value = rs.getObject(i + 1);
//获取列名
//String columnName = rsmd.getColumnName(i + 1);
//给customer列名为columnName的属性赋值为value:通过反射
String columnLabel=rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
//变为可以访问的属性
field.setAccessible(true);
field.set(t,value);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
package transaction;
public class User {
private String user;
private String password;
private int balance;
public User(String user, String password, int balance) {
this.user = user;
this.password = password;
this.balance = balance;
}
public User() {
}
public String getUser() {
return user;
}
public String getPassword() {
return password;
}
public int getBalance() {
return balance;
}
public void setUser(String user) {
this.user = user;
}
public void setPassword(String password) {
this.password = password;
}
public void setBalance(int balance) {
this.balance = balance;
}
@Override
public String toString() {
return "User{" +
"user='" + user + '\'' +
", password='" + password + '\'' +
", balance=" + balance +
'}';
}
}