dbutil

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;

/**
 * 
 * 简单数据库访问接口
 * 
 * @author 阮智勇
 * @version 3.0
 */
public class DBUtil {
	private static DBUtil dbUtil = null;
	private List<Connection> idle = new ArrayList<Connection>(0);
	private Map<Thread, Connection> busy = new HashMap<Thread, Connection>(0);
	private Map<String, Connection> cache = new HashMap<String, Connection>(0);
	private String dialect = "ORACLE";
	private int maxsize = 3;

	/**
	 * 私有构造不让直接创建一个DBUtil实例 读取配置文件加载数据库配置信息,创建一定数量的连接到连接池
	 */
	private DBUtil() {
		InputStream in = DBUtil.class.getClassLoader().getResourceAsStream(
				"db.properties");
		Properties prop = new Properties();
		try {
			prop.load(in);
			String driver = (String) prop.get("db.driver");
			String url = (String) prop.get("db.url");
			String username = (String) prop.get("db.username");
			String password = (String) prop.get("db.password");
			if (prop.get("db.maxsize") != null) {
				maxsize = Integer.parseInt(prop.get("db.maxsize").toString());
			}
			if (prop.get("db.dialect") != null) {
				dialect = (String) prop.get("db.dialect");
			}
			Class.forName(driver);
			for (int k = 0; k < maxsize; k++) {
				Connection conn = DriverManager.getConnection(url, username,
						password);
				idle.add(conn);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获得数据库访问接口
	 * 
	 * @return 数据库访问接口DBUtil单例
	 */
	public static DBUtil getInstance() {
		if (null == dbUtil) {
			dbUtil = new DBUtil();
		}
		return dbUtil;
	}

	/**
	 * 获得数据库连接
	 * 
	 * @return 数据库连接
	 */
	private synchronized Connection getConn() {
		Connection conn = null;
		try {
			if (idle.size() == 0) {
				wait();
			}
			conn = idle.get(0);
			busy.put(Thread.currentThread(), conn);
			idle.remove(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 关闭所有对对象,归还链接到连接池
	 * 
	 * @param rs
	 *            数据集对象
	 * @param ps
	 *            Ԥ命令对象
	 */
	private synchronized void closeAll(ResultSet rs, PreparedStatement ps) {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (null != ps) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		Connection conn = busy.get(Thread.currentThread());
		if (null != conn) {
			idle.add(conn);
			busy.remove(Thread.currentThread());
			notify();
		}
	}

	/**
	 * 无事务更新(不带参数)
	 * 
	 * @param sql
	 *            SQL语句 ："update table set price=200.00"
	 * @return 受影响行数
	 * @throws SQLException
	 */
	public int update(String sql) throws SQLException {
		int result = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(null, ps);
		}
		return result;
	}

	/**
	 * 无事务更新(带参数)
	 * 
	 * @param sql
	 *            SQL语句 :"delete from table where id=?"
	 * @param params
	 *            参数对象数组 :new Object[]{参数1,参数2,...}
	 * @return 受影响行数
	 * @throws SQLException
	 */
	public int update(String sql, Object[] params) throws SQLException {
		int result = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			result = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(null, ps);
		}
		return result;
	}

	/**
	 * 在事务下更新(不带参数)
	 * 
	 * @param transId
	 *            事务ID
	 * @param sql
	 *            SQL语句 :"update table set price=200.00"
	 * @return 受影响行数
	 * @throws SQLException
	 */
	public int update(String transId, String sql) throws SQLException {
		int result = 0;
		Connection conn = cache.get(transId);
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		}
		return result;
	}

	/**
	 * 在事务下更新(带参数)
	 * 
	 * @param transId
	 *            事务ID
	 * @param sql
	 *            SQL语句 :"delete from table where id=?"
	 * @param params
	 *            参数对象数组 :new Object[]{参数1,参数2,...}
	 * @return 受影响行数
	 * @throws SQLException
	 */
	public int update(String transId, String sql, Object[] params)
			throws SQLException {
		int result = 0;
		Connection conn = cache.get(transId);
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			result = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		}
		return result;
	}

	/**
	 * 非事务下批量更新
	 * 
	 * @param sql
	 *            SQL语句 :"insert into table(p1,p2,p3) values(?,?,?)"
	 * @param params
	 *            参数集合List<Object[]> 其中new Object[]{参数1,参数2,...}
	 * @return 受影响行数
	 * @throws SQLException
	 */
	public int[] batchUpdate(String sql, List<Object[]> params)
			throws SQLException {
		int[] result = new int[params.size()];
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for (Object[] objects : params) {
				for (int i = 0; i < objects.length; i++) {
					ps.setObject(i + 1, objects[i]);
				}
				ps.addBatch();
			}
			result = ps.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(null, ps);
		}
		return result;
	}

	/**
	 * 在事务下批量更新
	 * 
	 * @param transId
	 *            事务ID
	 * @param sql
	 *            SQL语句 :"insert into table(p1,p2,p3) values(?,?,?)"
	 * @param params
	 *            参数集合List<Object[]> 其中new Object[]{参数1,参数2,...}
	 * @return 受影响行数
	 * @throws SQLException
	 */
	public int[] batchUpdate(String transId, String sql, List<Object[]> params)
			throws SQLException {
		int[] result = new int[params.size()];

		Connection conn = cache.get(transId);
		PreparedStatement ps = conn.prepareStatement(sql);
		for (Object[] objects : params) {
			for (int i = 0; i < objects.length; i++) {
				ps.setObject(i + 1, objects[i]);
			}
			ps.addBatch();
		}
		result = ps.executeBatch();
		return result;
	}

	/**
	 * 查询(不带参数)返回List<Map<k,v>>其中k:字段名小写,v:字段值
	 * 
	 * @param sql
	 *            SQL语句 :"select * from table"
	 * @return 集合List<Map<k,v>> 其中k:字段名小写,v:字段值
	 * @throws SQLException
	 */
	public List<Map<String, Object>> query(String sql) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < colCount; i++) {
					String key = rsmd.getColumnLabel(i + 1).toLowerCase();
					Object val = rs.getObject(i + 1) != null ? rs
							.getObject(i + 1) : "";
					map.put(key, val);
				}
				list.add(map);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(rs, ps);
		}
		return list;
	}

	/**
	 * 分页查询(不带参数)返回List<Map<k,v>>其中k:字段名小写,v:字段值
	 * 
	 * @param sql
	 *            SQL语句 :"select * from table"
	 * @param currPage
	 *            第几页
	 * @param pageSize
	 *            每页记录数
	 * @return 集合List<Map<k,v>> 其中k:字段名小写,v:字段值
	 * @throws SQLException
	 */
	public List<Map<String, Object>> queryPager(String sql, int currPage,
			int pageSize) throws SQLException {
		String pageSql = getPageSql(sql, currPage, pageSize);
		return query(pageSql);
	}

	/**
	 * 查询(带参数)返回List<Map<k,v>>其中k:字段名小写,v:字段值
	 * 
	 * @param sql
	 *            SQL语句 :"select * from table where id = ?"
	 * @param params
	 *            参数对象数组 :new Object[]{参数1,参数2,...}
	 * @return 集合List<Map<k,v>> 其中k:字段名小写,v:字段值
	 * @throws SQLException
	 */
	public List<Map<String, Object>> query(String sql, Object[] params)
			throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < colCount; i++) {
					String key = rsmd.getColumnLabel(i + 1).toLowerCase();
					Object val = rs.getObject(i + 1) != null ? rs
							.getObject(i + 1) : "";
					map.put(key, val);
				}
				list.add(map);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(rs, ps);
		}
		return list;
	}

	/**
	 * 分页查询(带参数)返回List<Map<k,v>>其中k:字段名小写,v:字段值
	 * 
	 * @param sql
	 *            SQL语句 :"select * from table where id = ?"
	 * @param params
	 *            参数对象数组 :new Object[]{参数1,参数2,...}
	 * @param currPage
	 *            第几页
	 * @param pageSize
	 *            每页记录数
	 * @return 集合List<Map<k,v>> 其中k:字段名小写,v:字段值
	 * @throws SQLException
	 */
	public List<Map<String, Object>> queryPager(String sql, Object[] params,
			int currPage, int pageSize) throws SQLException {
		String pageSql = getPageSql(sql, currPage, pageSize);
		return query(pageSql);
	}

	/**
	 * 单值查询(不带参数)
	 * 
	 * @param sql
	 *            SQL语句 :"select name from table"
	 * @return 单值
	 * @throws SQLException
	 */
	public String scalar(String sql) throws SQLException {
		String res = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				res = String.valueOf(rs.getObject(1));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(rs, ps);
		}
		return res;
	}

	/**
	 * 单值查询(带参)
	 * 
	 * @param sql
	 *            SQL语句 :"select name from table where id=?"
	 * @param params
	 *            参数对象数组 :new Object[]{参数1,参数2,...}
	 * @return 单值
	 * @throws SQLException
	 */
	public String scalar(String sql, Object[] params) throws SQLException {
		String res = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				res = String.valueOf(rs.getObject(1));
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(rs, ps);
		}
		return res;
	}

	/**
	 * 执行存储过程
	 * 
	 * @param procName
	 *            存储过程
	 * @param params
	 *            存储过程参数对象数组 :new Object[]{参数1,参数2,...}
	 * @param outParamNum
	 *            输出参数个数
	 * @return 输出参数
	 * @throws SQLException
	 */
	public String[] execProc(String procName, Object[] params, int outParamNum)
			throws SQLException {
		String[] ret = new String[outParamNum];
		Connection conn = null;
		CallableStatement cs = null;
		int inParamNum = (null != params) ? params.length : 0;
		String procSql = getProcSql(procName, inParamNum, outParamNum);
		try {
			conn = getConn();
			cs = conn.prepareCall(procSql);
			for (int i = 0; i < inParamNum; i++) {
				cs.setObject(i + 1, params[i]);
			}
			for (int k = 1; k <= outParamNum; k++) {
				cs.registerOutParameter(inParamNum + k, Types.VARCHAR);
			}
			cs.executeQuery();
			for (int k = 1; k <= outParamNum; k++) {
				ret[k - 1] = cs.getString(inParamNum + k);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			closeAll(null, cs);
		}
		return ret;
	}

	/**
	 * 开始事务
	 * 
	 * @return 事务ID
	 */
	public String beginTrans() {
		String transId = null;
		try {
			Connection conn = getConn();
			conn.setAutoCommit(false);
			transId = UUID.randomUUID().toString();
			cache.put(transId, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return transId;
	}

	/**
	 * 事务提交
	 * 
	 * @param transId
	 *            事务ID
	 */
	public void commitTrans(String transId) {
		Connection conn = null;
		try {
			conn = cache.get(transId);
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(null, null);
			cache.remove(conn);
		}
	}

	/**
	 * 事务回滚
	 * 
	 * @param transId
	 *            事务ID
	 */
	public void rollbackTrans(String transId) {
		Connection conn = null;
		try {
			conn = cache.get(transId);
			conn.rollback();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(null, null);
			cache.remove(conn);
		}
	}

	/**
	 * 得到分页SQL语句
	 * @param sql 基本SQL语句
	 * @param currPage 当前页
	 * @param pageSize 每页记录数
	 * @return 分页SQL语句
	 */
	private String getPageSql(String sql, int currPage, int pageSize) {
		StringBuffer pageSql = new StringBuffer(0);
		if ("oracle".equalsIgnoreCase(dialect)) {
			pageSql.append("SELECT * FROM(SELECT FA.*, ROWNUM RN FROM (");
			pageSql.append(sql).append(") FA WHERE ROWNUM <= ");
			pageSql.append(currPage * pageSize).append(")WHERE RN >= ").append(
					(currPage - 1) * pageSize + 1);
		}
		if ("mysql".equalsIgnoreCase(dialect)) {
			pageSql.append(sql).append(" limit ").append(
					(currPage - 1) * pageSize).append(",").append(pageSize);
		}
		return pageSql.toString();
	}

	/**
	 * 得到存储过程语句
	 * 
	 * @param procName
	 *            存储过程
	 * @param inParamNum
	 *            输入参数个数
	 * @param outParamNum
	 *            输出参数个数
	 * @return
	 * @throws SQLException
	 */
	private String getProcSql(String procName, int inParamNum, int outParamNum)
			throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("{call ").append(procName);
		int paramCount = inParamNum + outParamNum;
		if (paramCount > 0) {
			sb.append("(");
			for (int i = 0; i < paramCount; i++) {
				sb.append("?");
				if (i != paramCount - 1) {
					sb.append(",");
				}
			}
			sb.append(")");
		}
		sb.append("}");
		return sb.toString();
	}
	
	/**
	 * 测试DBUtil
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			DBUtil dao = DBUtil.getInstance();
			String sql0 = "select addd from operate";
			String sql = "select * from rolepower rp where rolecode = (select ur.rolecode from user u left join userrole ur on u.code = ur.usercode where u.code = 0001)";
			List<Map<String, Object>> list = dao.query(sql);
			sql = "select * from operate";
			String count = dao.scalar(sql0);
			// list = dao.queryPager(sql, 1, 10);
			System.out.println(count);
			System.out.println(list.size());
			// System.out.println(list.get(0).get("name"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}