sqlutil.java

员工管理系统

package com.yiboit.hhs.db;

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

import javax.sql.DataSource;

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

/**
 * 数据库访问工具类
 * 
 * @author coombe
 */
public class SqlUtil {
	/** 数据库连接属性 */
	private Connection con = null;

	/**
	 * 构造器，取得数据库连接
	 */
	public SqlUtil(DataSource ds) throws SQLException {
		try {
			con = ds.getConnection();
		} catch (Exception ex) {
			con = null;
			System.err.println("取得数据库连接失败！" + ex.getMessage());
		}
	}

	/**
	 * 单行记录查询
	 * 
	 * 如果查询返回多行记录，则取第一行纪录的内容
	 * 
	 * @param query
	 *            查询字符串
	 * @param param
	 *            查询参数
	 * @param bean
	 *            返回值类型
	 * @return 容纳单行记录的JavaBean
	 * @throws SQLException
	 */
	public Object selectSingle(String query, Object[] param, Class bean)
			throws SQLException {
		Object result = null;
		QueryRunner qr = new QueryRunner();
		ResultSetHandler rsh = new BeanHandler(bean);

		try {
			if (param != null) {
				result = qr.query(con, query, param, rsh);
			} else {

				result = qr.query(con, query, rsh);
			}
		} catch (SQLException ex) {
			System.err.println("query:" + query);
			System.err.println("param:" + parseParams(param));
			this.rollbackAndClose();
			throw ex;
		}

		return result;
	}

	/**
	 * 单行记录查询
	 * 
	 * 如果查询返回多行记录，则抛出异常
	 * 
	 * @param query
	 *            查询字符串
	 * @param param
	 *            查询参数
	 * @param bean
	 *            返回值类型
	 * @return 容纳单行记录的JavaBean
	 * @throws SQLException
	 */
	public Object selectStrictSingle(String query, Object[] param, Class bean)
			throws SQLException {
		List result_list;
		Object result = null;
		QueryRunner qr = new QueryRunner();
		ResultSetHandler rsh = new BeanListHandler(bean);

		try {

			if (param != null) {
				result_list = (List) qr.query(con, query, param, rsh);
			} else {
				result_list = (List) qr.query(con, query, rsh);
			}

			if (result_list.size() > 1) {
				throw new SQLException("查询结果超过一行");
			} else if (result_list.size() == 1) {
				result = result_list.get(0);
			} else {
				result = null;
			}

			return result;
		} catch (SQLException ex) {
			System.err.println("query:" + query);
			System.err.println("param:" + parseParams(param));
			this.rollbackAndClose();
			throw ex;
		}
	}

	/**
	 * 多行数据查询
	 * 
	 * @param query
	 *            查询字符串
	 * @param param
	 *            查询参数
	 * @param bean
	 *            返回值类型
	 * @return List List对象，每个元素是一个JavaBean对象
	 * @throws SQLException
	 */
	public List selectMulti(String query, Object[] param, Class bean)
			throws SQLException {
		List result;
		QueryRunner qr = new QueryRunner();
		ResultSetHandler rsh = new BeanListHandler(bean);

		try {

			if (param != null) {

				result = (List) qr.query(con, query, param, rsh);
			} else {

				result = (List) qr.query(con, query, rsh);
			}

			// System.err.println("query:" + query);
			// System.err.println("param:" + parseParams(param));

			return result;
		} catch (SQLException ex) {
			System.err.println("query:" + query);
			System.err.println("param:" + parseParams(param));
			this.rollbackAndClose();
			throw ex;
		}
	}

	/**
	 * 查询单列内容
	 * 
	 * @param query
	 *            查询语句
	 * @param param
	 *            参数
	 * @param columnIndex
	 *            指定列索引
	 * @return Object对象 指定列内容
	 * @throws SQLException
	 */
	public Object selectScalar(String query, Object[] param, int columnIndex)
			throws SQLException {
		Object result;
		ResultSetHandler rsh;
		QueryRunner qr = new QueryRunner();
		if (columnIndex != 0) {
			rsh = new ScalarHandler(columnIndex);
		} else {
			rsh = new ScalarHandler();
		}

		try {

			if (param != null) {

				result = qr.query(con, query, param, rsh);
			} else {
				result = qr.query(con, query, rsh);
			}
			return result;
		} catch (SQLException ex) {
			System.err.println("query:" + query);
			System.err.println("param:" + parseParams(param));
			this.rollbackAndClose();
			throw ex;
		}
	}

	/**
	 * 更新查询(INSERT/UPDATE/DELETE)
	 * 
	 * @param query
	 *            查询语句
	 * @param param
	 *            参数
	 * @return int 更新影响的行数
	 * @throws SQLException
	 */
	public int update(String query, Object[] param) throws SQLException {
		int count = 0;
		QueryRunner qr = new QueryRunner();

		try {
			if (param != null) {

				count = qr.update(con, query, param);
			} else {

				count = qr.update(con, query);
			}
			return count;
		} catch (SQLException ex) {
			System.err.println("query:" + query);
			System.err.println("param:" + parseParams(param));
			this.rollbackAndClose();
			throw ex;
		}
	}

	/**
	 * 设置自动提交标记 通过把自动提交标记设为false开始一个事物
	 * 
	 * @param auto
	 *            是否自动提交(true/false)
	 */
	public void setAutoCommit(boolean auto) throws SQLException {
		try {
			if (con != null)
				con.setAutoCommit(auto);
		} catch (SQLException ex) {
			this.close();
			throw ex;
		}
	}

	/**
	 * 回滚事物
	 * 
	 * @throws SQLException
	 */
	public void rollback() throws SQLException {
		try {
			if (con != null) {
				con.rollback();
			}
		} catch (SQLException ex) {

			this.close();
			throw ex;
		}
	}

	/**
	 * 提交事物
	 * 
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		try {
			if (con != null) {
				con.commit();
			}
		} catch (SQLException ex) {

			this.close();
			throw ex;
		}
	}

	/*
	 * 关闭连接
	 */
	public void close() {
		try {
			if (con != null) {
				if (!con.isClosed()) {
					con.close();
				}
			}
			con = null;
		} catch (Exception ex) {
		}
	}

	/**
	 * 回滚并关闭连接
	 * 
	 */
	private void rollbackAndClose() {
		try {
			this.rollback();
			this.close();
		} catch (SQLException ex) {
		}
	}

	/**
	 * 将查询参数对象数组的内容转换成字符串，方便打印输出
	 * 
	 * @param params
	 *            参数对象数组
	 * @return 字符串形式
	 */
	protected static String parseParams(Object[] params) {
		StringBuilder tmpBuf = new StringBuilder();

		if (params != null) {
			tmpBuf.append("[ ");
			for (int i = 0; i < params.length; i++) {
				if (params[i] == null) {
					tmpBuf.append("null");
				} else {
					tmpBuf.append(params[i].toString());
				}
				if ((i + 1) != params.length) {
					tmpBuf.append(", ");
				}
			}
			tmpBuf.append(" ]");
		}
		return tmpBuf.toString();
	}
}