DruidUtils包含线程安全以及事务管理

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+"取钱失败！");
		}
		
	}}