package com.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidUtils {
private static DruidDataSource dataSource;
//线程变量集合 使事务只是使用一个connection 可以事务管理
private static ThreadLocal<Connection> threadLocal;
static{
threadLocal=new ThreadLocal<Connection>();
//读取配置文件
Properties properties=new Properties();
InputStream inputStream=DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//加载集合
try {
properties.load(inputStream);
dataSource=(DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
//判断线程中是否存在
Connection connection=threadLocal.get();
if (connection==null) {
connection=dataSource.getConnection();
threadLocal.set(connection);//放入集合
}
return connection;
}
/**
* 开启事务
* @throws SQLException
*/
public static void beginTranscation() throws SQLException {
Connection connection=getConnection();
connection.setAutoCommit(false);
}
/**
* 提交事务
* @throws SQLException
*/
public static void commitTranscation() throws SQLException {
Connection connection=getConnection();
connection.commit();
}
/**
* 回滚事务
* @throws SQLException
*/
public static void rollbackTranscation() throws SQLException{
Connection connection=getConnection();
connection.rollback();
}
/**
* 关闭连接
* @param connection
* @throws SQLException
*/
public static void close() throws SQLException {
Connection connection=getConnection();
connection.close();
}
/**
* 获取数据源
* @return
*/
public static DataSource getDataSource() {
return dataSource;
}
/**
* 关闭所有数据库相关操作
* @param rs
* @param stat
* @param conn
*/
public static void closeAll(ResultSet rs,Statement stat,Connection connection) {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// /**
// * 执行命令 增删改
// * @param sql
// * @param params
// * @return
// */
// public static int exexuteUpdate(String sql,Object...params) {
// //1 读取连接
// Connection connection=null;
// //2 创建命令对象
// PreparedStatement pstat=null;
//
// try {
// connection=getConnection();
// pstat=connection.prepareStatement(sql);
//
// for (int i = 0; i < params.length; i++) {
// pstat.setObject(i+1, params[i]);
// }
//
// return pstat.executeUpdate();
// } catch (Exception e) {
// // TODO: handle exception
// }finally {
// closeAll(null, pstat, connection);
// }
// return 0;
// }
}
转账案例:
业务层:
package com.service.imp;
import java.sql.SQLException;
import com.dao.AccountDao;
import com.dao.impl.AccountDaoImpl;
import com.service.AccountService;
import com.utils.DruidUtils;
public class AccountServiceImp implements AccountService {
private AccountDao accountdao=new AccountDaoImpl();
@Override
public void transMoney(int from, int to, double money) {
//1 取钱
//2 加钱
//注意:存钱取钱需要事务进行处理 要么都处理要么都不处理 所以只能共用一个connnection
//事务管理 不能分别获取connection 采用线程ThreadLocal
try {
//事务开启:
DruidUtils.beginTranscation();
accountdao.subMoney(from, money);
accountdao.saveMoney(to, money);
//事务提交
DruidUtils.commitTranscation();
} catch (Exception e) {
try {
DruidUtils.rollbackTranscation();
DruidUtils.commitTranscation();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally {
try {
DruidUtils.close();
throw new RuntimeException("业务转账失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Dao层:
package com.dao.impl;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import com.dao.AccountDao;
import com.domain.Account;
import com.utils.DruidUtils;
//select * from account;
//select * from account where id=?;
//update account set name=?,money=? where id=?
//delete from account where id=?
//insert into account(name,money)
public class AccountDaoImpl implements AccountDao {
QueryRunner qr=new QueryRunner();
@Override
public List<Account> findAll() {
// TODO Auto-generated method stub
return null;
}
@Override
public Account findById(int id) {
// TODO Auto-generated method stub
return null;
}
@Override
public void update(Account account) {
}
@Override
public void delete(int account_id) {
// TODO Auto-generated method stub
}
@Override
public void add(Account account) {
// TODO Auto-generated method stub
}
@Override
public void saveMoney(int id, double money) {
try {
qr.update(DruidUtils.getConnection(), "update account set money=money+? where id=?", money,id);
} catch (SQLException e) {
throw new RuntimeException(id+"存钱失败!");
}
}
@Override
public void subMoney(int id, double money) {
try {
qr.update(DruidUtils.getConnection(),"update account set money=money-? where id=?", money,id);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(id+"取钱失败!");
}
}}