数据库转账事务处理
package com.sammery.transaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import com.sammery.util.JDBCUtils;
public class TransactionTest {
@Test
public void testUpdate(){
String sql1 = "UPDATE user_table SET balance = balance - 100 WHERE `user` = ?";
update(sql1,"AA");
System.out.println(10/0);
String sql2 = "UPDATE user_table SET balance = balance + 100 WHERE `user` = ?";
update(sql2,"BB");
}
public int update(String sql,Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
return preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.closeResource(connection, preparedStatement);
}
return 0;
}
@Test
public void testUpdate2() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
System.out.println(conn);
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 e1) {
e1.printStackTrace();
}
} finally {
try {
conn.setAutoCommit(true);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public int update(Connection conn,String sql,Object ...args){
PreparedStatement preparedStatement = null;
try {
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
return preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.closeResource(null, preparedStatement);
}
return 0;
}
}