JAVA-oracle数据库封装操作连接池

/**
 * Description:数据库封装操作，连接池
 * Author：方继祥
 * Date:2003-11-2
 * Version：1.0
 *确认已导入oracle相关数据包
 */
package com.fangjixiang.db;

import java.sql.*;
import java.io.*;
import java.io.Reader;
import javax.naming.*;
import java.util.Properties;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
import oracle.sql.BLOB;

public class DBExecute {
    private Connection conn = null;
    private Statement stmt = null; //有回滚的
    private PreparedStatement preStmt = null; //@param查询
    private String sTableName;
    private String sPK;
    private String sID;
    private int iID;
    private String sUF;
    private int iUF;
    private int iUFV;
    private String sUFV;
    private boolean isAutoCommit;

    /**
     * 数据库连接，通过JNDI查找数据源的方式连接数据库
     * 返回值小于0时，连接失败，等于0时，连接成功
     */

    public  DBExecute(){
        connectDB() ;
    }

    public int connectDB() {
        if (conn == null) {

            try {
                //System.setProperty(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
                //System.setProperty(Context.PROVIDER_URL, "t3://liuhl:7001");

                InputStream is = getClass().getResourceAsStream(
                    "/db.properties");
                Properties dbProps = new Properties();
                try {
                    dbProps.load(is);
                } catch (Exception e) {
                    System.err.println("不能读取属性文件。请确保db.properties在你的CLASSPATH中");
                    return -200;
                }

                InitialContext initCtx = new InitialContext();

                javax.sql.DataSource ds = (javax.sql.DataSource) initCtx.lookup(
                    dbProps.getProperty("dbJndi"));
                //javax.sql.DataSource ds = (javax.sql.DataSource) initCtx.lookup("java:comp/env/oraDB");

                if (ds != null) {
                    this.conn = ds.getConnection();
                } else {
                    return -300;
                }
                return 0;
            } catch (NamingException ex) {
                String strErrLog = "NamingException " + ex.getMessage();
                System.out.println(strErrLog);
                return -201;
            } catch (SQLException ex) {
                String strErrLog = "SQLException " + ex.getMessage();
                return -1 * Math.abs(ex.getErrorCode());
            } catch (Exception ex) {
                String strErrLog = ex.toString();
                return -202;
            }
        } else {
            return 0;
        }

    }

    /**
    /**
     * 过程开始
     * @throws SQLException 捕捉错误
     */
    public void beginTrans() throws SQLException {
        try {
            isAutoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.print("beginTrans Errors");
            throw ex;
        }
    }

    /**
     * 获取数据库连接
     */
    public Connection getConnection() {
        return conn;
    }


    /**
     * 数据事务提交
     * @throws SQLException 捕捉错误
     */
    public void commit() throws SQLException {
        try {
            conn.commit();
            conn.setAutoCommit(isAutoCommit);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.print("Commit Errors!");
            throw ex;
        }
    }

    /**
     * 数据事务回滚
     */
    public void rollback() {
        try {
            conn.rollback();
            conn.setAutoCommit(isAutoCommit);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.print("Roolback Error!");
        }
    }

    /**
     * 判断是否为自动加入数据模式
     * @return boolean值
     * @throws SQLException 捕捉错误
     */
    public boolean getAutoCommit() throws SQLException {
        boolean result = false;
        try {
            result = conn.getAutoCommit();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("getAutoCommit fail " + ex.getMessage());
            throw ex;
        }
        return result;
    }

    /**
     * 清空PrepareStatement中的参数。
     *
     * @throws SQLException SQL异常
     */
    public void clearParameters() throws SQLException {
        if (null != this.preStmt) {
            preStmt.clearParameters();
        }
    }

    public PreparedStatement pstmt(String str_sql) {
        this.preStmt = null;
        try {
            this.preStmt = conn.prepareStatement(str_sql);
        }
        catch (SQLException ex) {
            System.err.println("preparedStatement(): " + ex.getMessage());
        }
        return this.preStmt;
    }

    /**
     * 设置字符串值
     *
     * @param index 索引
     * @param value 字符串值
     * @throws SQLException SQL异常
     */
    public void setString(int index, String value) throws SQLException {
        preStmt.setString(index, value);
    }

    public void setInt(int index, int value) throws SQLException {
        preStmt.setInt(index, value);
    }

    public void setBoolean(int index, boolean value) throws SQLException {
        preStmt.setBoolean(index, value);
    }

    public void setDate(int index, Date value) throws SQLException {
        preStmt.setString(index, value.toString());
    }

    public void setLong(int index, long value) throws SQLException {
        preStmt.setLong(index, value);
    }

    public void setFloat(int index, float value) throws SQLException {
        preStmt.setFloat(index, value);
    }

    public void setBytes(int index, byte[] value) throws SQLException {
        preStmt.setBytes(index, value);
    }

    /**
     * 设置PrepareStatement，并同时其清空参数列表。
     *
     * @param sql SQL语句
     * @throws SQLException SQL异常
     */
    public void setPrepareStatement(String sql) throws SQLException {
        this.clearParameters();
        this.preStmt = this.conn.prepareStatement(sql);
    }

    /**
     * executeQuery操作，用于数据查询，主要是Select
     * @param sql 查询字段
     * @return 数据集
     * @throws SQLException 捕捉错误
     */
    public ResultSet executeQuery(String sql) throws SQLException {
        ResultSet rs = null;
        try {
            stmt =
                conn.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("dbTrans.executeQuery:" + ex.getMessage());
            throw ex;
        }
        return rs;
    }

    /**
     * executeUpdate操作，用于数据更新，主要是Update，Insert
     * @param sql 查询字段
     * @throws SQLException 捕捉错误
     */
    public int executeUpdate(String sql) throws SQLException {
        int i = 0;
        try {
            stmt =
                conn.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
            i = stmt.executeUpdate(sql);

        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("dbTrans.executeUpdate:" + ex.getMessage());
            throw ex;
        }
        return i;
    }

      public void close() throws Exception {
        if (stmt != null) {
            stmt.close();
            stmt = null;
        }
        if (preStmt != null) {
            preStmt.close();
            preStmt = null;
        }
        if (conn != null) {
            conn.close();
            conn = null;
        }
    }

    /*
     * clob Insert
     * inSql : Insert SQL语句或者UPDATE语句
     * querySql : select SQL语句
     */

    public void clobInsert(String upSql, String querySql, String s) throws
        Exception {
        try {
            this.isAutoCommit = false;
            this.beginTrans();
            this.executeUpdate(upSql);
            ResultSet rs = this.executeQuery(querySql);
            while (rs.next()) {
                oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);
                BufferedWriter out = new BufferedWriter(clob.
                    getCharacterOutputStream());
                //BufferedReader in = new BufferedReader(new FileReader(s));     //File
                Reader in = new StringReader(s); //Text
                int c;
                while ( (c = in.read()) != -1) {
                    out.write(c);
                }
                in.close();
                out.close();
            }
            this.commit();
        }
        catch (Exception ex) {
            this.rollback();
            throw ex;
        }
    }

    /*
     *  CLOB数据的读取，将clob字段的内容读出并插入到某个文件中
     *  querySql: select clobfiled from table where ....
     *  s:生成的文件
     *
     */
    public void clobRead(String querySql, String s) throws Exception {
        try {
            this.isAutoCommit = false;
            this.beginTrans();
            ResultSet rs = this.executeQuery(querySql);
            while (rs.next()) {
                oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);
                BufferedReader in = new BufferedReader(clob.getCharacterStream());
                BufferedWriter out = new BufferedWriter(new FileWriter(s));
                int c;
                while ( (c = in.read()) != -1) {
                    out.write(c);
                }
                out.close();
                in.close();
            }
            this.commit();
        }
        catch (Exception ex) {
            this.rollback();
            throw ex;
        }
    }

    /*
     * 读取clob字段中内容，返回String
     *
     */
    public String clobRead(String querySql) throws Exception {
        String s = "";
        try {
            String str;
            this.isAutoCommit = false;
            this.beginTrans();
            ResultSet rs = this.executeQuery(querySql);
            while (rs.next()) {
                oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(1);
                BufferedReader in = new BufferedReader(clob.getCharacterStream());
                StringBuffer buffer = new StringBuffer();
                while ( (str = in.readLine()) != null) {
                    buffer.append(str).append("\n");
                }
                s = buffer.toString();
                in.close();

            }
            this.commit();
        }
        catch (Exception ex) {
            this.rollback();
            throw ex;
        }
        return s;
    }

    /*
     * BLOB  Insert
     *
     *
     */
    public void blobReplace(String upSql, String querySql, String s) throws
        Exception {
        try {
            this.isAutoCommit = false;
            this.beginTrans();
            this.executeUpdate(upSql);
            ResultSet rs = this.executeQuery(querySql);
            while (rs.next()) {
                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1);
                BufferedOutputStream out = new BufferedOutputStream(blob.
                    getBinaryOutputStream());
                BufferedInputStream in = new BufferedInputStream(new
                    FileInputStream(s));
                int c;
                while ( (c = in.read()) != -1) {
                    out.write(c);
                }
                in.close();
                out.close();
            }
            this.commit();
        }
        catch (Exception ex) {
            this.rollback();
            throw ex;
        }
    }

    /*
     * BLOB Read
     *
     */
    public void blobRead(String querySql, String s) throws Exception {
        try {
            this.isAutoCommit = false;
            this.beginTrans();
            ResultSet rs = this.executeQuery(querySql);
            while (rs.next()) {
                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1);
                /* 以二进制形式输出 */
                BufferedOutputStream out = new BufferedOutputStream(new
                    FileOutputStream(s));
                BufferedInputStream in = new BufferedInputStream(blob.
                    getBinaryStream());
                int c;
                while ( (c = in.read()) != -1) {
                    out.write(c);
                }
                in.close();
                out.close();
            }
            this.commit();
        }
        catch (Exception ex) {
            this.rollback();
            throw ex;
        }
    }

    public static void main(String[] args) throws Exception {
        DBExecute dbc = new DBExecute();
        try {
            int indt = dbc.connectDB();
            System.out.print(indt);

            /* CLOB Insert */
            //String upSql = "INSERT FANG_TEST VALUES(2,EMPTY_CLOB())";
            //String querySql =
            //"SELECT CONTENT FROM FANG_TEST WHERE ID=2 FOR UPDATE";
            //String s = "c:/clobReplace2.txt";
            //dbc.clobInsert(upSql,querySql,s);

            /* CLOB Replace */
            //String upSql = "UPDATE FANG_TEST SET CONTENT = EMPTY_CLOB() WHERE ID=2";
            //String querySql = "SELECT CONTENT FROM FANG_TEST WHERE ID=2 FOR UPDATE";
            //String s = "c:/clobReplace2.txt";
            //dbc.clobInsert(upSql,querySql,s);

            /* CLOB Read */
            //String querySql = "SELECT CONTENT FROM FANG_TEST WHERE ID=2";
            //System.out.println(dbc.clobRead(querySql));

            /* BLOB Replace */
            //String upSql = "UPDATE FANG_TEST_BLOB SET CONTENT = EMPTY_BLOB() WHERE ID=2";
            //String querySql = "SELECT CONTENT FROM FANG_TEST_BLOB WHERE ID = 2 FOR UPDATE";
            //String s = "c:/755.jpg";
            //dbc.blobReplace(upSql,querySql,s);

            /* BLOB Read */
            //String querySql = "SELECT CONTENT FROM FANG_TEST_BLOB WHERE ID=2";
            //String s = "c:/756.jpg";
            //dbc.blobRead(querySql,s);

        }
        catch (Exception e) {
            throw e;
        }
        finally {
            dbc.close();
        }
    }

}