经典的数据库访问接口

package org.lyq.dao;

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 2.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_cfg.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();  
        }  
    }  

}