dbmgr.java

大象购物系统

            if (null == param.elementAt(i)) {
                pstmt.setString(i + 1, "");
            } else if (param.elementAt(i) instanceof String) {
                pstmt.setString(i + 1, (String) param.elementAt(i));
            } else if (param.elementAt(i) instanceof Timestamp) {
                pstmt.setTimestamp(i + 1, (Timestamp) param.elementAt(i));
            } else if (param.elementAt(i) instanceof Date) {
                pstmt.setTimestamp(i + 1
                                   ,
                                   new Timestamp(((Date) param.elementAt(i)).
                                                 getTime()));
            } else if (param.elementAt(i) instanceof Integer) {
                pstmt.setInt(i + 1, ((Integer) param.elementAt(i)).intValue());
            } else if (param.elementAt(i) instanceof Double) {
                pstmt.setDouble(i + 1
                                , ((Double) param.elementAt(i)).doubleValue());
            } else {
                pstmt.setString(i + 1, (String) param.elementAt(i));
            }
        }
        return pstmt.executeQuery();
    }

    public int execute(String sql) throws SQLException {
        return execute(sql, new Vector());
    }

    public Vector getResult(String sql, Vector param) throws SQLException {
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = getConn();
            rs = getResultSet(sql, param, conn);
            return convertResultset(rs);

        } catch (SQLException ex) {
            LoggerUtil.error(" Exception query:" + sql);
            LoggerUtil.error(param);
            LoggerUtil.error(DBMgr.class, ex);
            throw ex;
        } catch (Exception ex) {
            LoggerUtil.error(DBMgr.class, ex);
            throw new SQLException(ex.getMessage());
        } finally {
            try {
                rs.close();
                conn.close();
                conn = null;
            } catch (SQLException ex1) {
                LoggerUtil.error(DBMgr.class, ex1);
            }
        }

    }

    public Vector getResult(String sql) throws SQLException {
        return getResult(sql, new Vector());

    }

    public ListVO getResult(String sql, Vector param
                            , ListVO lvo) throws SQLException {
        Vector v = new Vector();
        if (lvo != null) {
            int page = lvo.getCurrentPage();
            int pageSize = lvo.getItemsPerPage();
            if (lvo.getTotalItems() < 0) {
                lvo.setTotalItems(getRowCount(sql, param));
            }
            if (page > lvo.getMaxPage()) {
                page = lvo.getMaxPage();
                lvo.setPageInt(lvo.getMaxPage());
            }
//            LoggerUtil.debug("pages:" + lvo.toString());
            sql = getPaginationSql(sql, page, pageSize);

        }

        v = getResult(sql, param);

        if (lvo != null) {
            lvo.setData(v);
        } else {
            lvo = new ListVO();
            lvo.setData(v);
        }
        return lvo;
    }

    private String getPaginationSql(String sql, int pageInt, int pageSize) {
        int f = (pageInt - 1) * pageSize;
        if (f < 0) {
            f = 0;
        }
        StringBuffer query = new StringBuffer();
        query.append(sql + " LIMIT " + f + ", " +
                     pageSize);
        return query.toString();
    }

    public int getRowCount(String sqlStr) throws SQLException {
        int count = -1;
        try {
            Vector r = getResult(sqlStr, new Vector());
            count = r.size();
            return count;
        } catch (SQLException e) {
            LoggerUtil.error(DBMgr.class, e);
            throw e;
        }
    }

    public int getRowCount(String sqlStr, Vector param) throws SQLException {
        int count = -1;
        try {
            Vector r = getResult(sqlStr, param);
            count = r.size();
            return count;
        } catch (SQLException e) {
            LoggerUtil.error(DBMgr.class, e);
            throw e;
        }
    }

    public String toOracleDarkString(String s) {
        s = BaseUtil.toString(s);
        return (null == s || "".equals(s)) ? "%"
                : new StringBuffer("%").append(s).append("%").toString();
    }

    public String toOracleString(String number) {
        number = BaseUtil.toString(number);
        return (null == number || "".equals(number)) ? "%" : number;
    }

    public Object toOracleString(Object object) {
        return toOracleString(BaseUtil.toString(object));
    }

    public String toOracleDarkString(Object object) {
        return toOracleDarkString(BaseUtil.toString(object));
    }

    public String getSQLString(Vector v) {
        if (v == null || v.size() == 0) {
            return "''";
        }
        StringBuffer sb = new StringBuffer();
        sb.append("'").append(BaseUtil.toString(v.elementAt(0))).append("'");
        for (int i = 1; i < v.size(); i++) {
            if (!BaseUtil.toString(v.elementAt(i)).trim().equals("")) {
                sb.append(",").append("'").append(BaseUtil.toString(v.elementAt(
                        i))).append("'");

            }
        }
        return sb.toString();

    }

    public String getSQLInt(Vector v) {
        if (v == null || v.size() == 0) {
            return "''";
        }
        StringBuffer sb = new StringBuffer();
        sb.append(BaseUtil.toInt(v.elementAt(0)));
        for (int i = 1; i < v.size(); i++) {
            if (!BaseUtil.toString(v.elementAt(i)).trim().equals("")) {
                sb.append(",").append(BaseUtil.toString(v.elementAt(i)));
            }
        }
        return sb.toString();

    }

    public Vector convertResultset(ResultSet rs) throws
            SQLException {

        try {
            Vector retval = new Vector();
            ResultSetMetaData rsmd = rs.getMetaData();
            int length = rsmd.getColumnCount();
            while (rs.next()) {
                GenericVO gvo = new GenericVO();
                for (int i = 1; i < length + 1; i++) {
                    if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
                        gvo.add(rsmd.getColumnName(i).toUpperCase()
                                , BaseUtil.toShortDate(rs.getTimestamp(i)));
                    } else {
                        gvo.add(rsmd.getColumnName(i).toUpperCase()
                                , BaseUtil.toString(rs.getString(i)));
                    }
                }
                retval.add(gvo);
            }
//            LoggerUtil.debug("lenth:" + length);
            return retval;

        } catch (SQLException ex) {

            LoggerUtil.error(DBMgr.class, ex);
            throw ex;
        } catch (Exception ex) {
            LoggerUtil.error(DBMgr.class, ex);
            throw new SQLException(ex.getMessage());
        }

    }

    class ThreadConnectionMap {
        private Map threadConnectionMap = Collections.synchronizedMap(new
                WeakHashMap());

        public Connection getConnection() throws SQLException {
            Connection conn = (Connection) threadConnectionMap.get(Thread.
                    currentThread());
            if (conn != null && !conn.isClosed()) {
                conn = (Connection) threadConnectionMap.get(Thread.
                        currentThread());
            } else {
                conn = getConn();
                threadConnectionMap.put(Thread.currentThread(), conn);
            }
            return conn;
        }

        public void releaseConnection() throws SQLException {
            Connection conn = (Connection) threadConnectionMap.get(Thread.
                    currentThread());
            if (conn.getAutoCommit() == false) {
                return;
            }
            threadConnectionMap.remove(Thread.currentThread());
            conn.close();
            conn = null;

        }

        private void trimConn() {
            Iterator ite = threadConnectionMap.keySet().iterator();
            while (ite.hasNext()) {
                Thread r = (Thread) ite.next();
                if (r.isAlive()) {
                    threadConnectionMap.remove(r);
                }
            }
        }

        boolean inTransaction() {
            return threadConnectionMap.get(Thread.currentThread()) != null;
        }

    }
}