工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DbUtil {
/**
* 获取连接
* @return
*/
public static Connection getConnection()
{
Connection conn = null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/RollBackPro?useUnicode=true&characterEncoding=utf-8","root","123456");
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
/**
* 关闭连接
* @param conn
* @param ps
* @param rs
*/
public static void closeAll(Connection conn,PreparedStatement ps,ResultSet rs)
{
try{
rs.close();
ps.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 关闭连接(重载)
* @param conn
* @param ps
*/
public static void closeAll(Connection conn,PreparedStatement ps)
{
try{
ps.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
public static void closeAll(Connection con)
{
try{
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
DAO层数据库处理
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MoneyDAO {
/**
* 出账
* @param con
* @param name
* @param money
* @throws Exception
*/
public static void outMoney(Connection con,String name,int money) throws Exception{
judgeMoney(con,name,money); //转账之前先判断余额是否足够,若不够,此处会直接抛出异常,阻止之后代码的运行
String sql="update account set money=money-? where name=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, money);
ps.setString(2,name);
ps.execute();
}
/**
* 进账
* @param con
* @param name
* @param money
* @throws SQLException
*/
public static void intMoney(Connection con,String name,int money) throws SQLException{
String sql="update account set money=money+? where name = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, money);
ps.setString(2,name);
ps.execute();
}
/**
* 判断余额是否足够
* @param con
* @param name
* @param money
* @throws Exception
*/
public static void judgeMoney(Connection con,String name,int money) throws Exception{
ResultSet rs = null;
String sql="select money from account where name=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, name);
rs = ps.executeQuery();
if(rs.next()){
if(rs.getInt("money")<money){ //若不够,抛出异常
throw new Exception("余额不足!");
}
}
}
}
Test测试类
import java.sql.Connection;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
Connection con=null;
try {
con = DbUtil.getConnection(); // 开启连接
con.setAutoCommit(false); //自动提交关闭,带有的回滚的事务必须这样选择
MoneyDAO.outMoney(con,"张三", 500); //转账
MoneyDAO.intMoney(con,"李四",500); //进账
} catch (Exception e) {
// TODO: handle exception
try {
e.printStackTrace();
con.rollback(); //捕获异常并回滚
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
finally{
try {
con.commit(); //不管最终的结果的是什么,都要提交给数据库,保持数据库的一致性。
DbUtil.closeAll(con); //关闭此次连接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}