dbaccess.java

oa办公系统

package com.wanczy.dbutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DBAccess {
	private static final Log log=LogFactory.getLog(DBAccess.class);
	private ConnectionManager connManager=ConnectionManager.getInstance();
	/**
	 * 执行更改操作,有JDBC级别的事务
	 * @param sql 执行更改的SQL语句
	 * @return
	 */
	public int executeUpdate(String sql){
		int ret=0;
		log.debug("begin executeUpdate");
		Connection conn=connManager.getConnection();
		try{
			conn.setAutoCommit(false);
			Statement stmt=conn.createStatement();
			try{
				ret=stmt.executeUpdate(sql);
				conn.commit();
				log.debug(sql+" committed");
			}finally{
				stmt.close();
			}
		}catch(SQLException e){
			log.error(sql+"\n",e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.fatal("回滚失败",e1);
				throw new RuntimeException("执行"+sql+"失败,而且回滚失败",e);
			}
			throw new RuntimeException("执行"+sql+"失败",e);
		}finally{
			try{
				if(conn != null) conn.setAutoCommit(true);
			}catch (Exception e) {
				// TODO: handle exception
			}
			connManager.closeConn(conn);
		}
		return ret;
	}
	/**
	 * 根据提供的sql和参数表,来执行修改数据库的操作,有JDBC级别的事务
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeUpdate(String sql,Object [] params){
		int ret=0;
		Connection conn=connManager.getConnection();
		try{
			conn.setAutoCommit(false);
			PreparedStatement pstmt=conn.prepareStatement(sql);
			try{
				for(int i=0;i<params.length;i++){
					pstmt.setObject(i+1, params[i]);
				}
				ret=pstmt.executeUpdate();
				conn.commit();
				log.debug(sql+" committed!");
			}finally{
				pstmt.close();
			}
		}catch(SQLException e){
			log.error(sql,e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.fatal("回滚失败",e1);
				throw new RuntimeException("执行"+sql+"失败,而且回滚失败",e);
			}
			throw new RuntimeException("执行"+sql+"失败",e);
		}finally{
			try{
				if(conn != null) conn.setAutoCommit(true);
			}catch (Exception e) {
				// TODO: handle exception
			}
			connManager.closeConn(conn);
		}
		return ret;
	}
	/**
	 * 执行查询的方法,需要提供SQL,和结果集的处理
	 * @param sql 查询的SQL语句
	 * @param handler 结果集的一行记录到POJO对象的一个转换代码
	 * @return
	 */
	public List executeQuery(String sql,IResultSetHandler handler){
		log.debug("查询:"+sql);
		List list=new ArrayList();
		Connection conn=connManager.getConnection();
		try{
			Statement stmt=conn.createStatement();
			try{
				ResultSet rs=stmt.executeQuery(sql);
				while(rs.next()){
					list.add(handler.handle(rs));
				}
				rs.close();
			}finally{
				stmt.close();
			}
		}catch(SQLException e){
			log.error("执行"+sql+"失败",e);
			throw new RuntimeException("执行"+sql+"失败",e);
		}finally{
			connManager.closeConn(conn);
		}
		return list;
	}
	/**
	 * 执行查询,要提供sql,参数表和结果集的对象映射方法.
	 * @param sql
	 * @param params
	 * @param handler
	 * @return
	 */
	public List executeQuery(String sql,Object[] params,IResultSetHandler handler){
		log.debug("查询:"+sql);
		List list=new ArrayList();
		Connection conn=connManager.getConnection();
		try{
			PreparedStatement pstmt=conn.prepareStatement(sql);
			try{
				for(int i=0;i<params.length;i++){
					pstmt.setObject(i+1, params[i]);
				}
				ResultSet rs=pstmt.executeQuery();
				while(rs.next()){
					list.add(handler.handle(rs));
				}
				rs.close();
			}finally{
				pstmt.close();
			}
		}catch(SQLException e){
			log.error("执行"+sql+"失败",e);
			throw new RuntimeException("执行"+sql+"失败",e);
		}finally{
			connManager.closeConn(conn);
		}
		return list;
	}
	/**
	 * 批量执行更新修改操作，
	 * @param sql 用于PreparedStatement的SQL语句
	 * @param params 二维对象数组，每行是一条SQL语句的参数。
	 * @return 执行完影响的记录数
	 */
	public int executeBatch(String sql,Object [][] params){
		int ret=0;
		Connection conn=connManager.getConnection();
		try{
			conn.setAutoCommit(false);
			PreparedStatement pstmt=conn.prepareStatement(sql);
			try{
				for(int j=0;j<params.length;j++){
					for(int i=0;i<params[j].length;i++){
						pstmt.setObject(i+1, params[j][i]);
					}
					pstmt.addBatch();
				}
				int[] rets=pstmt.executeBatch();
				conn.commit();
				for(int i:rets){
					ret+=i;
				}
				log.debug(sql+" 批量 committed!");
			}finally{
				pstmt.close();
			}
		}catch(SQLException e){
			log.error(sql,e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.fatal("回滚失败",e1);
				throw new RuntimeException("批量执行"+sql+"失败,而且回滚失败",e);
			}
			throw new RuntimeException("批量执行"+sql+"失败",e);
		}finally{
			try{
				if(conn != null) conn.setAutoCommit(true);
			}catch (Exception e) {
				// TODO: handle exception
			}
			connManager.closeConn(conn);
		}
		return ret;
	}
}