十六、DBUtils事务使用

要保证QueryRunner操作事务时能够生效就必须要保存调用api时使用的Connection对象是同一个.为了减少代码的耦合性,这里可以使用ThreadLocal类来绑定当前线程的Connection对象.

关于事务的操作以及Connection对象的唯一性操作可以写到一个工具类中

package blog.youkuaiyun.com.mchenys.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceUtils {
	// c3p0连接池
	private static ComboPooledDataSource ds = new ComboPooledDataSource();
	// 当前线程关联的数据库连接对象
	private static ThreadLocal<Connection> tl = new ThreadLocal<>();

	/**
	 * 从线程中获取连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		// 从线程中获取conneciton
		Connection conn = tl.get();
		if (conn == null) {
			conn = ds.getConnection();
			// 和当前线程绑定
			tl.set(conn);
		}
		return conn;
	}

	/**
	 * 取数据源
	 * @return
	 */
	public static DataSource getDataSource() {
		return ds;
	}

	/**
	 * 释放资源
	 * @param st
	 * @param rs
	 */
	public static void closeResource(Statement st, ResultSet rs) {
		closeResultSet(rs);
		closeStatement(st);
	}

	/**
	 * 释放资源
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void closeResource(Connection conn, Statement st, ResultSet rs) {
		closeResource(st, rs);
		closeConn(conn);
	}

	/**
	 * 释放 connection
	 * @param conn
	 */
	public static void closeConn(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
				// 和线程解绑
				tl.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}

	/**
	 * 释放 statement 
	 * @param st
	 */
	public static void closeStatement(Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			st = null;
		}
	}

	/**
	 * 释放结果集
	 * @param rs
	 */
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
	}

	/**
	 * 开启事务
	 * @throws SQLException
	 */
	public static void startTransaction() throws SQLException {
		getConnection().setAutoCommit(false);
	}

	/**
	 * 事务提交且释放连接
	 */
	public static void commitAndClose() {
		Connection conn = null;
		try {
			conn = getConnection();
			// 事务提交
			conn.commit();
			// 关闭资源
			conn.close();
			// 解除绑定
			tl.remove();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 事务回滚且释放资源
	 */
	public static void rollbackAndClose() {
		Connection conn = null;
		try {
			conn = getConnection();
			// 事务回滚
			conn.rollback();
			// 关闭资源
			conn.close();
			// 解除版定
			tl.remove();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

下面以添加订单和订单项的操作为例演示下在dbutils下的事务事务使用步骤.

需求:提供一个方法生成一条订单,该订单可以包含有多个订单项,当订单或者订单项添加出错的时候回滚所有操作,必须要等这2个操作都完成才算生成订单成功.

Service层操作

package blog.youkuaiyun.com.mchenys.service.impl;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

import blog.youkuaiyun.com.mchenys.dao.OrderDao;
import blog.youkuaiyun.com.mchenys.dao.impl.OrderDaoImpl;
import blog.youkuaiyun.com.mchenys.domain.Order;
import blog.youkuaiyun.com.mchenys.domain.OrderItem;
import blog.youkuaiyun.com.mchenys.domain.PageBean;
import blog.youkuaiyun.com.mchenys.domain.Product;
import blog.youkuaiyun.com.mchenys.service.OrderService;
import blog.youkuaiyun.com.mchenys.utils.DataSourceUtils;

/**
 * 订单模块
 * 
 * @author mChenys
 *
 */
public class OrderServiceImpl implements OrderService {
        ....

	// 添加订单
	public void add(Order order) throws Exception {
		try {
			// 开启事务
			DataSourceUtils.startTransaction();

			OrderDao dao = new OrderDaoImpl();
			// 添加一条订单记录
			dao.add(order);

			// 添加多条订单项记录
			for (OrderItem item : order.getItems()) {
				dao.addItem(item);
			}
			// 完成事务
			DataSourceUtils.commitAndClose();
		} catch (SQLException e) {
			e.printStackTrace();
			// 回滚事务
			DataSourceUtils.rollbackAndClose();
			throw e;
		}
	}

	....

}

DAO层操作

package blog.youkuaiyun.com.mchenys.dao.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import blog.youkuaiyun.com.mchenys.dao.OrderDao;
import blog.youkuaiyun.com.mchenys.domain.Order;
import blog.youkuaiyun.com.mchenys.domain.OrderItem;
import blog.youkuaiyun.com.mchenys.utils.DataSourceUtils;

public class OrderDaoImpl implements OrderDao {

        ....

	@Override
	public void add(Order order) throws Exception {

		// 手动管理事务则不能通过构造方法传入connection
		QueryRunner qr = new QueryRunner();
		String sql = "insert into orders values(?,?,?,?,?,?,?,?)";
		// connection需要保持同一个.从DataSourceUtils中获取
		qr.update(DataSourceUtils.getConnection(), sql, order.getOid(), order.getOrdertime(), order.getTotal(),
				order.getState(), order.getAddress(), order.getName(), order.getTelephone(), order.getUser().getUid());
	}

	@Override
	public void addItem(OrderItem orderItem) throws Exception {

		// 手动管理事务,connection需要保持同一个.从DataSourceUtils中获取
		QueryRunner qr = new QueryRunner();
		String sql = "insert into orderitem values(?,?,?,?,?)";
		qr.update(DataSourceUtils.getConnection(), sql, orderItem.getItemid(), orderItem.getCount(),
				orderItem.getSubtotal(), orderItem.getProduct().getPid(), orderItem.getOrder().getOid());

	}

	....

}