数据库操作类

package test;

import java.sql.*;
import java.util.*;

import javax.sql.rowset.CachedRowSet;
import oracle.jdbc.rowset.OracleCachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

/**
 * Title: 数据库操作类，不带显示事物的方法
 * Description: 以一种新的方式来操作数据库使用SingletonDataSource单态类来取得连接
 * Copyright: Copyright (c) 2007
 */
public class DBManager {
    public DBManager() {
    }
    /**
     * 执行任意SQL语句SELECT,INSERT,UPDATE or DELETE，不要求返回任何信息的SQL语句 隐式事物处理
     * @param sql    String 要执行的任何SQL语句
     * @return boolean 成功执行返回true,否则flase
     * @throws Exception
     */
    public boolean execute(String sql) throws Exception {
        System.out.println("execute：" + sql);
        boolean re = false;
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            pstmt =con.prepareStatement(sql);
            re = pstmt.execute();
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return re;
    }

    /**
     *
     * @param sql
     * @param value
     * @param type
     * @return
     * @throws Exception
     */
    public boolean execute(String sql, String[] value, int[] type)
    throws Exception {
        System.out.println("execute：" + sql);
        boolean re = false;
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            this.setParameters(pstmt,value,type);  //调用私有方法设置参数
            re = pstmt.execute();
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return re;
    }    

    /**
     * 执行查询，返回结果集 隐式事物处理
     * @param sql String 要执行的SELECT语句
     * @return CachedRowSet 未查到记录则返回null
     * @throws Exception
     */
    public CachedRowSet executeQueryRowSet(String sql) throws Exception {
        System.out.println("executeQueryRowSet：" + sql);
        CachedRowSet crs = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            /*stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,    ResultSet.CONCUR_UPDATABLE);
             * java.sql.SQLException: 不允许的操作: Unsupported syntax for refreshRow()
             * Oracle: Unsupported syntax for refreshRow()报此错使用以下方式解决
             * 1、con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
             * 2、con.createStatement();
            */
            pstmt=con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (!(rs.isBeforeFirst() == rs.isAfterLast())) {
                crs = new CachedRowSetImpl();
                crs.populate(rs);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return crs;
    }

    /**
     * 执行高效率的SELECT语句，使用ORACLE的SQL特点
     * @param sql    String 带?号的SELECT语句
     * @param value    String[] ?对应的值
     * @param type    int[] ?对应的值的数据库表中的类型
     * @return CachedRowSet 未查到记录则返回null
     * @throws Exception
     */
    public CachedRowSet executeQueryRowSet(String sql, String[] value,
            int[] type) throws Exception {
        System.out.println("executeQueryRowSet：" + sql);
        CachedRowSet crs = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            this.setParameters(pstmt,value,type);  //调用私有方法设置参数
            rs = pstmt.executeQuery();
            if (!(rs.isBeforeFirst() == rs.isAfterLast())) {
                crs = new CachedRowSetImpl();
                crs.populate(rs);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }

        }
        return crs;
    }


    /**
     * 执行查询，返回Map单条记录，如查执行结果有多条记录的情况下也只返回第一条记录 隐式事物处理
     * @param sql    String 要执行的SELECT语句
     * @return Map 未查到记录则返回null，使用列名的大家做为Map的Key，列值对象为Map的value
     * @throws Exception
     */
    public Map executeQueryMap(String sql) throws Exception {
        System.out.println("executeQueryMap：" + sql);
        Map map = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            pstmt =con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) { // 有记录的情况
                map = new HashMap();
                ResultSetMetaData rsm = rs.getMetaData();
                int colnum = rsm.getColumnCount(); // 得到记录的列数
                for (int i = 1; i <= colnum; i++) {
                    // rsm.getColumnName(i).toUpperCase()统一列名大写
                    // rs.getObject(i)更的值为对象类型
                    map
                            .put(rsm.getColumnName(i).toUpperCase(), rs
                                    .getObject(i));
                }
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return map;
    }


    /**
     * 执行高效率的SELECT语句，使用ORACLE的SQL特点
     * @param sql  String 带？号的SELECT语句
     * @param value String[] ？对应的值
     * @param type int[] ？对应的值的数据库表中的类型
     * @return Map 未查到记录则返回null，使用列名的大家做为Map的Key，列值对象为Map的value
     * @throws Exception
     */
    public Map executeQueryMap(String sql, String[] value, int[] type)
            throws Exception {
        System.out.println("executeQueryMap：" + sql);
        Map map = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            this.setParameters(pstmt,value,type);  //调用私有方法设置参数
            rs = pstmt.executeQuery();
            if (rs.next()) { // 有记录的情况
                map = new HashMap();
                ResultSetMetaData rsm = rs.getMetaData();
                int colnum = rsm.getColumnCount(); // 得到记录的列数
                for (int i = 1; i <= colnum; i++) {
                    // rsm.getColumnName(i).toUpperCase()统一列名大写
                    // rs.getObject(i)更的值为对象类型
                    map
                            .put(rsm.getColumnName(i).toUpperCase(), rs
                                    .getObject(i));
                }
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return map;
    }


    /**
     * 执行更新语句INSERT,UPDATE or DELETE
     * @param sql String 要执行的SQL语句
     * @return int 如果执行INSERT,UPDATE or DELETE则返回影响的记录行数,如果执行没有返回值的语句则返回为0
     * @throws Exception
     */
    public int executeUpdate(String sql) throws Exception {
        System.out.println("executeUpdate：" + sql);
        int re = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            pstmt = con.prepareStatement(sql);
            re = pstmt.executeUpdate();
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return re;
    }


    /**
     * 执行更新语句INSERT,UPDATE or DELETE
     * @param sql String 要执行的带有？号的INSERT,UPDATE or DELETE语句
     * @param value String[] ？对应的值
     * @param type int[] ？对应的值的数据库表中的类型
     * @return int 如果执行INSERT,UPDATE or DELETE则返回影响的记录行数,如果执行没有返回值的语句则返回为0
     * @throws Exception
     */
    public int executeUpdate(String sql, String[] value, int[] type)
            throws Exception {
        System.out.println("executeUpdate：" + sql);
        int re = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            this.setParameters(pstmt,value,type);  //调用私有方法设置参数
            re = pstmt.executeUpdate(); // 影响的记录行数
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return re;
    }

    /**
     私有方法用来设置
     * @param pstmt PreparedStatement对象的参数
     * @param value  参数值
     * @param type      参数类型
     * @throws Exception
     */
    private void setParameters(PreparedStatement pstmt,String[] value,int[] type)throws Exception{
        int j = 1;
        for (int i = 0; i < value.length; i++) {
            switch (type[i]) {
            case Types.INTEGER:
                pstmt.setInt(j, Integer.parseInt(value[i]));
                System.out.println("Parameter("+i+")    INTEGER      "+value[i]);
                break;
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.DECIMAL:
            case Types.NUMERIC:
                pstmt.setDouble(j, Double.parseDouble(value[i]));
                System.out.println("Parameter("+i+")    FLOAT、DOUBLE、DECIMAL、NUMERIC      "+value[i]);
                break;
            case Types.CHAR:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                pstmt.setString(j, value[i]);
                System.out.println("Parameter("+i+")    CHAR、VARCHAR、LONGVARCHAR      "+value[i]);
                break;
            case Types.DATE:
            case Types.TIMESTAMP:
                pstmt.setString(j, value[i]);
                System.out.println("Parameter("+i+")    DATE、TIMESTAMP      "+value[i]);
                break;
            case Types.BOOLEAN:
                // Boolean.parseBoolean(value[i]) JDK5API
                pstmt.setBoolean(j, Boolean.getBoolean(value[i]));
                System.out.println("Parameter("+i+")    BOOLEAN      "+value[i]);
                break;
            }
            j = j + 1;
        }
    }
    

    /**
     * 执行记录的查询(调用存储过程)
     * @param procName：调用存储过程的名称；
     * @param returnType:游标返回类型
     * 0为CachedRowSet以及各种类型
     * @param outParameter：传入输出参数的类型，例如：new int[]{Types.REF,Types.REF,Types.VARCHAR},
     * 表示所调用的存储过程前两个输出结果的类型为游标类型，第三个输出结果的类型为VARCHAR2类型。
     * @param args       所调用存储过程的输入参数
     * @return List<Object> 存储过程的输出结果
     *
     */
    public List<Object> executeProc(String procName,int returnType,int[] outParameter,Object... args) throws Exception {
        List<Object> list = null;
        Connection con = null;
        CallableStatement cstmt = null;
        try {
            con = SingletonDataSource.getInstance().getConnection();
            int iInStart = 1;// 存储过程输入参数的起始位
            // 统计问号的个数
            int parameterCount = args.length+outParameter.length; //调用的存储过程的总参数个数
            // 组装带问号的SQL
            String sql = "{call " + procName + "(";
            for (int i = 1; i <= parameterCount; i++) {
                if (i == parameterCount){
                    sql += "?";
                }else{
                    sql += "?,";
                }
            }
            sql += ")}";
            System.out.println("executeProc："+sql);
            cstmt = con.prepareCall(sql);
            for (int i = 0; i < args.length; i++) {// 循环设定存储过程的输入参数
                cstmt.setObject(iInStart + i, args[i]);
                System.out.println("设置输入参数:"+iInStart+"  "+args[i]);
            }
            int iOutStart = args.length + iInStart;// 存储过程输出参数的起始位
            for (int i = 0; i < outParameter.length; i++) {// 循环设定存储过程的输出参数
                if (outParameter[i]==Types.REF){ //代表游标
                    cstmt.registerOutParameter(i + iOutStart, oracle.jdbc.driver.OracleTypes.CURSOR);
                    System.out.println("设置输出参数:"+(i + iOutStart)+" 游标");
                }else if((outParameter[i]==Types.INTEGER)){
                    cstmt.registerOutParameter(i + iOutStart, Types.INTEGER);
                    System.out.println("设置输出参数:"+(i + iOutStart)+" 数值型");
                }else{
                    cstmt.registerOutParameter(i + iOutStart, Types.VARCHAR);
                    System.out.println("设置输出参数:"+(i + iOutStart)+" 字符型");
                }
            }
            cstmt.execute();  //执行
            list = new ArrayList<Object>();
            switch (returnType) {
            case 0:// 把存储过程中返回的游标转换为CachedRowSet类型输出
                for (int i = 0; i < outParameter.length; i++) {// 循环获取存储过程的输出结果，包括游标、字符串
                    if (outParameter[i]==Types.REF) { //处理游标
                        //CachedRowSet crs=new CachedRowSetImpl();
                        OracleCachedRowSet crs=new OracleCachedRowSet();  //使用Oracle10g的数据则必须要用10g的驱动,Oracle 10g驱动解决了“ORA-01002: 读取违反顺序”这个问题
                        if (cstmt.getObject(i + iOutStart) != null){
                            crs.populate((ResultSet)cstmt.getObject(i + iOutStart));
                        }
                        list.add(crs);
                    } else{ //非游标
                        list.add(cstmt.getObject(i + iOutStart));
                    }
                }
                break;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            cstmt.close();
            //con.close();
        }
        return list;
    }
    
    /**
     * 主函数做测试用
     *
     * @param args
     *            String[]
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        DBManager dbm = new DBManager();
        //p_report_bzyb.p_bzyb(reportdate in varchar2,limited out integer,result1 out sys_refcursor,result2 out sys_refcursor,result3  out sys_refcursor)
        List list=dbm.executeProc("p_report_bzyb.p_bzyb",0,new int[]{Types.INTEGER,Types.REF,Types.REF,Types.REF},"2007-06-01");
        //p_report_4_realtime.p_report_4_rt(in_xz in varchar2,in_tjqsrq in varchar2,in_tjjzrq in varchar2,c1 OUT sys_refcursor)
        //List list=dbm.executeProc("p_report_4_realtime.p_report_4_rt",0,new int[]{Types.REF},"43048101","2007-04-10","2007-09-10");
        //dbm.execute("update dept set dname=? where deptno=? ",new String[]{"sdfsdfsad","51"},new int[]{Types.VARCHAR,Types.NUMERIC});
        // Connection con = SingletonDataSource.getInstance().getConnection();
        // CachedRowSet crs = dbm.executeQueryRowSet(
        // "SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP WHERE
        // EMPNO >7654",
        // con);
        // while (crs.next()) {
        // System.out.println(crs.getString("EMPNO") + " " +
        // crs.getString("ENAME"));
        // }
        // System.out.println("########################");
        // Map map = dbm.executeQueryMap(
        // "SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP WHERE
        // EMPNO=7654",
        // con);
        // System.out.println(map.get("EMPNO") + " " + map.get("ENAME") + " " +
        // map.get("COMM"));
        // con.close();
        /*
         * String sql = "select * from emp where comm=?"; String[] value = new
         * String[] {"500"}; int[] type = {Types.DOUBLE}; Map map =
         * dbm.executeQueryMap(sql, value, type, null); //Map map =
         * dbm.executeQueryMap("select * from emp where empno=? and ename=?",new
         * String[] {"7654","MARTIN"},new int[]{Types.INTEGER,Types.VARCHAR},
         * null); if (map != null) { System.out.println(map.get("EMPNO") + " " +
         * map.get("ENAME")); }
         */
        /*
         * String sql = "select * from emp where empno>?"; String[] value = new
         * String[] {"7693"}; int[] type = {Types.INTEGER}; CachedRowSet crs =
         * dbm.executeQueryRowSet(sql, value, type, null); boolean isprint =
         * true; while (crs.next()) { ResultSetMetaData rsmd =
         * crs.getMetaData(); int colcount = rsmd.getColumnCount(); if (isprint) {
         * isprint=false; for (int i = 0; i < colcount; i++) {
         * System.out.print(rsmd.getColumnName(i + 1) + "\t\t"); }
         * System.out.println(); } for (int i = 0; i < colcount; i++) {
         * System.out.print(crs.getString(i + 1)+"\t\t"); }
         * System.out.println(); }
         */
        /*
         * String sql = "update emp set comm=? where comm=?"; String[] value =
         * new String[] {"56.22", "0"}; int[] type = {Types.DOUBLE,
         * Types.INTEGER}; System.out.println(dbm.executeUpdate(sql, value,
         * type, null));
         */
        // System.out.println(dbm.selectList("test", "empno", "ename", true,
        // "","select * from emp", null, null));
    }
}