【JDBC中的事务管理】（掌握）
- JDBC的事务的管理的API:
方式一: 在service层获取connection, 以参数的方式把connection 传递给DAO层；
工具类DBUtils就是使用的这个方式
代码：
public void transaction(String from, String to, Double money) {
AccountDao accountDao = new AccountDao();
// 方式一:在业务层获取connection， 传递给DAO层；
Connection connection = null;
connection = JDBCUtils.getConnection();
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
accountDao.outMoney(connection,from,money);
int i = 10/0;
accountDao.inMoney(connection,to,money);
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
上面的第一种方法，就是代码有侵入性.
方式二：使用本地线程来传递connection
Hibernate框架用的就是这种方式；
/**
* 方式二：使用本地线程来传递connection service层
* @param from
* @param to
* @param money
*/
public void transaction(String from, String to, Double money) {
AccountDao accountDao = new AccountDao();
try {
JDBCUtils.beginTransaction();
accountDao.outMoney(from,money);
int i = 10/0;
accountDao.inMoney(to,money);
JDBCUtils.commitTransaction();
} catch (Exception e) {
// TODO Auto-generated catch block
try {
JDBCUtils.rollbackTransaction();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
}
DAO层：
public void outMoney(String from, Double money) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JDBCUtils.getConnection();
String sql = "update account set money=money-? where name = ?";
statement = connection.prepareStatement(sql);
statement.setDouble(1, money);
statement.setString(2, from);
statement.executeUpdate();
} catch(Exception e){
e.printStackTrace();
}finally {
try {
statement.close();
//connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void inMoney(String to, Double money) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JDBCUtils.getConnection();
String sql = "update account set money=money+? where name = ?";
statement = connection.prepareStatement(sql);
statement.setDouble(1, money);
statement.setString(2, to);
statement.executeUpdate();
} catch(Exception e){
e.printStackTrace();
}finally {
try {
statement.close();
//connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
JDBCUtils 工具类：
/**
* JDBC的工具类
* @author admin
*
*/
public class JDBCUtils {
private static final ComboPooledDataSource DATA_SOURCE =new ComboPooledDataSource();
private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
/**
* 获得连接的方法
*/
public static Connection getConnection(){
Connection conn = null;
try {
conn = tl.get();
if (null == conn) {
conn = DATA_SOURCE.getConnection();
tl.set(conn);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static DataSource getDataSource(){
return DATA_SOURCE;
}
public static void beginTransaction() throws Exception{
Connection connection = null;
connection = tl.get();
if (null == connection) {
connection = DATA_SOURCE.getConnection();
tl.set(connection);
}
connection.setAutoCommit(false);
}
public static void commitTransaction() throws Exception{
Connection connection = tl.get();
connection.commit();
}
public static void rollbackTransaction() throws Exception{
Connection connection = tl.get();
connection.rollback();
}
}
DBUtils实现事务管理:
没有事务管理: （创建QueryRunner时需要连接池）
- 有事务管理:（创建QueryRunner时不需要连接池）
service层：
/**
* 方式三：使用DbUtils
* @param from
* @param to
* @param money
*/
public void transaction(String from, String to, Double money) {
AccountDao accountDao = new AccountDao();
Connection connection = JDBCUtils.getConnection();
try {
connection.setAutoCommit(false);
accountDao.outMoneyByDBUtils(connection,from,money);
//int i = 10/0;
accountDao.inMoneyByDBUtils(connection,to,money);
DbUtils.commitAndCloseQuietly(connection);
} catch (Exception e) {
DbUtils.rollbackAndCloseQuietly(connection);
e.printStackTrace();
}
}
DAO层
public void outMoneyByDBUtils(Connection connection, String from, Double money) throws SQLException {
QueryRunner runner = new QueryRunner();
String sql = "update account set money = money - ? where name = ?";
runner.update(connection, sql, money,from);
}
public void inMoneyByDBUtils(Connection connection, String to, Double money) throws SQLException {
QueryRunner queryRunner = new QueryRunner();
String sql = "update account set money = money + ? where name = ?";
queryRunner.update(connection, sql, money,to);
}