java 数据库连接方式（七）

import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

import oracle.sql.BLOB;
import oracle.sql.CLOB;

/**
 * 连接对象类
 * @author 
 */
public class ConnectionObject {
	/**
	 * Connection对象
	 */
	public Connection conn = null; 
	
	private Statement statement = null;
	private ResultSet resultSet = null;
	
	/**
	 * 是否被使用标志 
	 */
	public boolean isusing; 

	/**
	 * 最近一次开始使用时间
	 */
	public long lastAccess; 
	
	/**
	 * 创建时间
	 */
	public long createTime;

	/**
	 * 被使用次数
	 */
	public int useCount;

	public ConnectionObject(){
	}

	protected boolean isClosed() {
		if (this.conn == null)
			return true;
		
		try {
			if (this.conn.isClosed()) 
				return true;
		}catch(Exception ex){}
		
		Statement state = null;
		boolean clo = false;
		try {
			state = this.conn.createStatement();
		}catch(Exception ex){
			ex.printStackTrace();
			clo = true;
		}finally{
			try {
	            if (state != null) {
	            	state.close();
	            }
			}catch(Throwable e){}
		}
		if (clo)
			this.close();
		
		return clo;
	}
	
	/**
	 * 关闭整个连接
	 */
	public void close() {
		this.closeStatement();
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}catch(Throwable e){
			e.printStackTrace();
		}
	}

	/**
	 * 关闭整个连接
	 */
	public void closeResultSet() {
		try {
			if (this.resultSet != null) {
				this.resultSet.close();
				this.resultSet = null;
			}
		}catch(Throwable e){
			e.printStackTrace();
		}
	}

	/**
	 * 关闭语句
	 */
	public void closeStatement() {
		this.closeResultSet();
		try {
            if (statement != null) {
            	statement.close();
            	statement = null;
            }
		}catch(Throwable e){
			e.printStackTrace();
		}
	}
	
	void check() {
		if (this.conn == null)
			throw new DBException("连接已经关闭！");
		
		this.closeResultSet();
	}
	/**
	 * 根据sql查询
	 * @param sql 查询语句
	 * @return 查询结果集ResultSet对象
	 */
	public ResultSet executeQuery(String sql) {
		this.check();
		try {
        	lastAccess = System.currentTimeMillis();
        	if (statement == null)
        		statement = conn.createStatement();
			this.resultSet = statement.executeQuery(sql);
			return resultSet;
		}catch(Throwable e){
			throw new DBException(e);
		}
	}

	/**
	 * 查询单个对象
	 * @param sql 查询语句
	 * @return 查询结果（如果有多条记录，返回第一条）
	 */
	public Object getObject(String sql) {
		ResultSet set = executeQuery(sql);
		try {
			Object obj = null;
			if (set.next())
				obj = set.getObject(1);
			set.close();
			return obj;
		}catch(Throwable e){
			throw new DBException(e);
		}
	}

	/**
	 * 查询单个对象
	 * @param sql 查询语句
	 * @return 查询结果（如果有多条记录，返回第一条）
	 */
	public String getString(String sql) {
		ResultSet set = executeQuery(sql);
		try {
			String obj = null;
			if (set.next())
				obj = set.getString(1);
			set.close();
			return obj;
		}catch(Throwable e){
			throw new DBException(e);
		}
	}

	/**
	 * 查询单个对象
	 * @param sql 查询语句
	 * @return 查询结果（如果有多条记录，返回第一条）
	 */
	public int getInt(String sql) {
		ResultSet set = executeQuery(sql);
		try {
			int obj = -1;
			if (set.next())
				obj = set.getInt(1);
			set.close();
			return obj;
		}catch(Throwable e){
			throw new DBException(e);
		}
	}

    /**
     * 执行批sql语句
     * @param SqlArr 包含要操作的所有SQL语句
     * @return TRUE表示执行成功，FALSE表示执行失败，执行失败则回滚
     * @throws DBException
     */
    public boolean executeBatch(ArrayList SqlArr) {
		this.check();
        try {
        	lastAccess = System.currentTimeMillis();
        	if (statement == null)
        		statement = conn.createStatement();
            conn.setAutoCommit(false);
            for (int n = 0; n < SqlArr.size(); n++) {
            	String str = (String) SqlArr.get(n);
            	if (str != null && str.length()>0)
            		statement.addBatch(str);
            }
            statement.executeBatch();
            return true;
        }catch(Throwable e){
			throw new DBException(e);
		}
    }

    /**
     * 执行批sql语句，并提交事务
     * @param SqlArr 包含要操作的所有SQL语句
     * @return TRUE表示事务执行成功，FALSE表示执行失败，执行失败则回滚
     * @throws DBException
     */
    public boolean executeTransaction(ArrayList SqlArr) {
		this.check();
        try {
        	lastAccess = System.currentTimeMillis();
        	if (statement == null)
        		statement = conn.createStatement();
            conn.setAutoCommit(false);
            for (int n = 0; n < SqlArr.size(); n++) {
            	String str = (String) SqlArr.get(n);
            	if (str != null && str.length()>0)
            		statement.addBatch(str);
            }
            statement.executeBatch();
            conn.setAutoCommit(true);
            return true;
        }catch(Throwable e){
        	this.rollback();
			throw new DBException(e);
		}
    }

    /**
     * 执行更新、插入语句
     * @param sqlStr sql语句
     * @return 修改的记录条数
     * @throws Exception SQLException
     */
    public int executeUpdate(String sqlStr) {
		this.check();
        try {
        	lastAccess = System.currentTimeMillis();
        	if (statement == null)
        		statement = conn.createStatement();
            return statement.executeUpdate(sqlStr);
        }catch(Throwable e){
			throw new DBException(e);
		}
    }

    /**
     * 开始事务
     */
    public void startTransaction() {
		try {
			if (conn != null && conn.getAutoCommit()) {
				conn.setAutoCommit(false);
			}
		}catch(Throwable e){
			e.printStackTrace();
		}
    }

    /**
     * 结束事务
     */
    public void endTransaction() {
		try {
			if (conn != null && conn.getAutoCommit() == false) {
				conn.setAutoCommit(true);
			}
		}catch(Throwable e){
			e.printStackTrace();
		}
    }

    /**
     * 提交事务
     */
    public void commit() {
		try {
			if (conn != null) {
				conn.commit();
			}
		}catch(Throwable e){
			e.printStackTrace();
		}
    }

    /**
     * 回滚事务
     */
    public void rollback() {
		try {
			if (conn != null) {
				conn.rollback();
			}
		}catch(Throwable e){
			e.printStackTrace();
		}
    }

    /**
     * 从blob字段得到byte[]数据
     * @param rs 结果集
     * @param pos 字段位置
     * @return byte[]数组
     * @throws Exception
     */
    public byte[] getBlob(ResultSet rs, int pos) throws Exception {
    	if (ConnectionManager.getDBType() == ConnectionManager.ORACLE) {
    		BLOB blob = (BLOB) rs.getBlob(pos);
    		InputStream is = blob.getBinaryStream();
    		byte[] bt = new byte[(int)blob.length()];
    		is.read(bt);
    		is.close();
    		return bt;
    	}
    	return rs.getBytes(pos);
    }

    /**
     * 从blob字段得到byte[]数据
     * @param rs 结果集
     * @param colName 字段名
     * @return byte[]数组
     * @throws Exception
     */
    public byte[] getBlob(ResultSet rs, String colName) throws Exception {
    	if (ConnectionManager.getDBType() == ConnectionManager.ORACLE) {
    		BLOB blob = (BLOB) rs.getBlob(colName);
    		InputStream is = blob.getBinaryStream();
    		byte[] bt = new byte[(int)blob.length()];
    		is.read(bt);
    		is.close();
    		return bt;
    	}
    	return rs.getBytes(colName);
    }

    /**
     * 修改数据库blob字段的值
     * @param tableName blob所在的表名
     * @param condi 查询blob的条件(唯一标记)
     * @param fieldName blob字段名称
     * @param fieldValue 字段新值
     * @throws Exception
     */
    public void setBlob(String tableName, String condi, String fieldName, byte[] fieldValue) throws Exception {
        String sql = "select " + fieldName + " from " + tableName + " " + condi + " for update";
	    this.setBlob(sql, fieldValue);
    }

    /**
     * 修改数据库blob字段的值
     * @param tableName blob所在的表名
     * @param sql sql语句
     * @param fieldValue 字段新值
     * @throws Exception
     */
    public void setBlob(String sql, byte[] fieldValue) throws Exception {
        if (ConnectionManager.getDBType() == ConnectionManager.ORACLE) {
	    	ResultSet rset = null;
	        rset = executeQuery(sql);
	        BLOB blob = null;
	        if (rset.next())
	            blob = (BLOB) rset.getClob(1);
	
	        if (fieldValue == null)
	        	fieldValue = new byte[0];
	
	        OutputStream os = blob.getBinaryOutputStream();
	        os.write(fieldValue);
	        os.close();
	        rset.close();
        }
    }
    
    /**
     * 修改数据库clob字段的值
     * @param tableName clob所在的表名
     * @param condi 查询clob的条件(唯一标记)
     * @param fieldName clob字段名称
     * @param fieldValue 字段新值
     * @throws Exception
     */
    public void setClob(String tableName, String condi, String fieldName, String fieldValue) throws Exception {
        this.setClob(tableName, condi, fieldName, fieldValue, false);
    }

    /**
     * 修改数据库clob字段的值
     * @param tableName clob所在的表名
     * @param condi 查询clob的条件(唯一标记)
     * @param fieldName clob字段名称
     * @param fieldValue 字段新值
     * @param isclear 是否清除CLOB原有值
     * @throws Exception
     */
    public void setClob(String tableName, String condi, String fieldName, String fieldValue, boolean isclear) throws Exception {
        if (ConnectionManager.getDBType() == ConnectionManager.ORACLE) {
        	if (isclear) {
		    	String sql = "update " + tableName + " set " + fieldName + "=empty_clob() " + condi;
		    	this.executeUpdate(sql);
        	}
	    	
	        String sql = "select " + fieldName + " from " + tableName + " " + condi + " for update";
		    this.setClob(sql, fieldValue);
        }
    }

    /**
     * 修改数据库clob字段的值
     * @param sql sql语句
     * @param fieldValue 字段新值
     * @throws Exception
     */
    public void setClob(String sql, String fieldValue) throws Exception {
        if (fieldValue != null && fieldValue.length() > 0 
        		&& ConnectionManager.getDBType() == ConnectionManager.ORACLE) {
	    	ResultSet rset = null;
	        rset = executeQuery(sql);
	        CLOB clob = null;
	
	        if (rset.next()) {
	            clob = (CLOB) rset.getClob(1);
		        char[] valueChars = new char[fieldValue.length()];
		        fieldValue.getChars(0, fieldValue.length(), valueChars, 0);
		        java.io.BufferedWriter out = new java.io.BufferedWriter(clob.getCharacterOutputStream()); 
		        out.write(valueChars);
		        out.close(); 
	        }
	        rset.close();
        }
    }
    
    /**
     * 从clob字段得到值
     * @param rs 结果集
     * @param pos 字段位置
     * @return clob字段值
     * @throws Exception
     */
    public String getClob(ResultSet rs, int pos)  throws Exception {
    	if (ConnectionManager.getDBType() == ConnectionManager.ORACLE) {
	        CLOB clob = null;
	        clob = (CLOB) rs.getClob(pos);
	        
	        String value = "";
	        if (clob != null) {
	            int len = (int) clob.length();
	            char[] buffer = new char[len];
	            java.io.Reader reader = clob.getCharacterStream();
	            reader.read(buffer);
	            reader.close();
	            value = new String(buffer);
	        }
	        return value;
    	}
    	return rs.getString(pos);
    }

    /**
     * 从clob字段得到值
     * @param rs 结果集
     * @param fn 字段名
     * @return clob字段值
     * @throws Exception
     */
    public String getClob(ResultSet rs, String fn)  throws Exception {
    	if (ConnectionManager.getDBType() == ConnectionManager.ORACLE) {
	        CLOB clob = null;
	        clob = (CLOB) rs.getClob(fn);
	        
	        String value = "";
	        if (clob != null) {
	            int len = (int) clob.length();
	            char[] buffer = new char[len];
	            java.io.Reader reader = clob.getCharacterStream();
	            reader.read(buffer);
	            reader.close();
	            value = new String(buffer);
	        }
	        return value;
    	}
    	return rs.getString(fn);
    }
    
    /**
     * 调用存储过程
     * @param procedureName 存储过程名称
     * @param paraIn  传入参数
     * @param paraOut 返回的参数类型
     * @return 返回的值
     * @throws Exception
     */
    public Object[] callProcedure(String procedureName, Object[] paraIn,
                                 Class[] paraOut) {
        try {
            int inLen = paraIn.length;
            int outLen = paraOut.length;
            String callStr = "";
            for (int i = 0; i < inLen + outLen; i++) {
            	if (i > 0)
                    callStr += ",";
                callStr += "?";
            }
            callStr = "call " + procedureName + "(" + callStr + ")";
            CallableStatement callableStatement = conn.prepareCall(callStr);
            for (int i = 0; i < inLen; i++) {
                if (paraIn[i] == null) {
                    callableStatement.setNull(i + 1, Types.NULL);
                    continue;
                }
                
                if (paraIn[i] instanceof String){
                    callableStatement.setString(i + 1, paraIn[0].toString());
                    continue;
                }

                if (paraIn[i] instanceof Integer){
                    callableStatement.setInt(i + 1, ((Integer) paraIn[i]).intValue());
                    continue;
                }

                if (paraIn[i] instanceof Long){
                    callableStatement.setLong(i + 1, ((Long) paraIn[i]).longValue());
                    continue;
                }

                if (paraIn[i] instanceof Float){
                    callableStatement.setFloat(i + 1, ((Float)paraIn[i]).floatValue());
                    continue;
                }

                if (paraIn[i] instanceof Double){
                    callableStatement.setDouble(i + 1, ((Double)paraIn[i]).doubleValue());
                    continue;
                }

                if (paraIn[i] instanceof Boolean){
                    callableStatement.setBoolean(i + 1, ((Boolean) paraIn[i]).booleanValue());
                    continue;
                }
                
                if (paraIn[i] instanceof Array){
                	callableStatement.setArray(i + 1, (Array) paraIn[i]);
                    continue;
                }
                
                if (paraIn[i] instanceof byte[]){
                	callableStatement.setBytes(i + 1, (byte[]) paraIn[i]);
                    continue;
                }
                
                if (paraIn[i] instanceof Date){
                	callableStatement.setDate(i + 1, (Date) paraIn[i]);
                    continue;
                }
                callableStatement.setObject(i + 1, paraIn[i]);
            }

            for (int i = 0; i < outLen; i++) {
                if (paraOut[i] == null){
                    callableStatement.registerOutParameter(inLen + i + 1,
                        Types.NULL);
	                continue;
	            }                
                
                if (paraOut[i] == String.class) {
                    callableStatement.registerOutParameter(inLen + i + 1,
                        Types.VARCHAR);
	                continue;
	            } 

                if (paraOut[i] == Integer.class) {
                    callableStatement.registerOutParameter(inLen + i + 1,
                        Types.INTEGER);
	                continue;
	            } 

                if (paraOut[i] == Long.class) {
                    callableStatement.registerOutParameter(inLen + i + 1,
                        Types.INTEGER);
	                continue;
	            } 

                if (paraOut[i] == Float.class) {
                    callableStatement.registerOutParameter(inLen + i + 1,
                        Types.FLOAT);
	                continue;
	            } 

                if (paraOut[i] == Double.class) {
                    callableStatement.registerOutParameter(inLen + i + 1,
                        Types.DOUBLE);
	                continue;
	            } 

                if (paraOut[i] == Boolean.class) {
                    callableStatement.registerOutParameter(inLen + i + 1,
                        Types.REAL);
	                continue;
	            } 
                
                if (paraOut[i] == Array.class) {
                	callableStatement.registerOutParameter(inLen + i + 1, Types.ARRAY);
	                continue;
	            } 
                
                if (paraOut[i] == byte[].class) {
                	callableStatement.registerOutParameter(inLen + i + 1, Types.ARRAY);
	                continue;
	            } 
                
                if (paraOut[i] == Date.class) {
                	callableStatement.registerOutParameter(inLen + i + 1, Types.DATE);
	                continue;
	            } 
                
                callableStatement.registerOutParameter(inLen + i + 1, Types.OTHER);
            }

            callableStatement.execute();
            
            Object[] res = new Object[outLen];
            for (int i = 0; i < outLen; i++) {
                if (paraOut[i] == null){
	                res[i] = null;
	                continue;
	            }
                
                if (paraOut[i] == String.class){
                	res[i] = callableStatement.getString(inLen + i + 1);
	                continue;
	            }                

                if (paraOut[i] == Integer.class){
                	res[i] = new Integer(callableStatement.getInt(inLen + i + 1));
	                continue;
	            }                

                if (paraOut[i] == Long.class){
                	res[i] = new Long(callableStatement.getLong(inLen + i + 1));
	                continue;
	            }                

                if (paraOut[i] == Float.class){
                	res[i] = new Float(callableStatement.getFloat(inLen + i + 1));
	                continue;
	            }                

                if (paraOut[i] == Double.class){
                	res[i] = new Double(callableStatement.getDouble(inLen + i + 1));
	                continue;
	            }                

                if (paraOut[i] == Boolean.class){
                	res[i] = new Boolean(callableStatement.getBoolean(inLen + i + 1));
	                continue;
	            }                
                
                if (paraOut[i] == Array.class){
                	res[i] = callableStatement.getArray(inLen + i + 1);
	                continue;
	            }                
                
                if (paraOut[i] == byte[].class){
                	res[i] = callableStatement.getBytes(inLen + i + 1);
	                continue;
	            }                
                
                if (paraOut[i] == Date.class){
                	res[i] = callableStatement.getDate(inLen + i + 1);
	                continue;
	            }                
                
                res[i] = callableStatement.getObject(inLen + i + 1);
            }

            callableStatement.close();
            return res;
        }
        catch (Exception e) {
			throw new DBException(e);
        }
    }

    /**
     * 调用存储函数
     * @param functionName 函数名称值
     * @param paraIn 参数类型
     * @param retType 返回参数的类型
     * @return 返回执行结果
     * @throws Exception
     */
    public Object callFunction(String functionName, Object[] paraIn,
                               Class retType) throws Exception {
        try {
            int inLen = paraIn.length;
            String callStr = "";
            for (int i = 0; i < inLen; i++) {
                callStr += "?";
                if (i != inLen - 1)
                    callStr += ",";
            }
            callStr = "{? = call " + functionName + "(" + callStr + ")}";
            CallableStatement callableStatement = conn.prepareCall(callStr);

            for (int i = 0; i < inLen; i++) {
                if (paraIn[i] == null)
                    callableStatement.setNull(i + 2, Types.NULL);
                
                if (paraIn[i] instanceof String)
                    callableStatement.setString(i + 2, paraIn[0].toString());

                if (paraIn[i] instanceof Integer)
                    callableStatement.setInt(i + 2, ((Integer) paraIn[i]).intValue());

                if (paraIn[i] instanceof Long)
                    callableStatement.setLong(i + 2, ((Long) paraIn[i]).longValue());

                if (paraIn[i] instanceof Float)
                    callableStatement.setFloat(i + 2, ((Float)paraIn[i]).floatValue());

                if (paraIn[i] instanceof Double)
                    callableStatement.setDouble(i + 2, ((Double)paraIn[i]).doubleValue());

                if (paraIn[i] instanceof Boolean)
                    callableStatement.setBoolean(i + 2, ((Boolean) paraIn[i]).booleanValue());
                
                if (paraIn[i] instanceof Array)
                	callableStatement.setArray(i + 2, (Array) paraIn[i]);
                
                if (paraIn[i] instanceof byte[])
                	callableStatement.setBytes(i + 2, (byte[]) paraIn[i]);
                
                if (paraIn[i] instanceof Date)
                	callableStatement.setDate(i + 2, (Date) paraIn[i]);
            }

            if (retType == null)
                callableStatement.registerOutParameter(1, Types.NULL);

            else if (retType == String.class)
                callableStatement.registerOutParameter(1, Types.VARCHAR);

            else if (retType == Integer.class)
                callableStatement.registerOutParameter(1, Types.INTEGER);

            else if (retType == Long.class)
                callableStatement.registerOutParameter(1, Types.INTEGER);

            else if (retType == Float.class)
                callableStatement.registerOutParameter(1, Types.FLOAT);

            else if (retType == Double.class)
                callableStatement.registerOutParameter(1, Types.DOUBLE);

            else if (retType == Boolean.class)
                callableStatement.registerOutParameter(1, Types.REAL);
            
            else if (retType == Array.class)
            	callableStatement.registerOutParameter(1, Types.ARRAY);
            
            else if (retType == byte[].class)
            	callableStatement.registerOutParameter(1, Types.ARRAY);
            
            else if (retType == Date.class)
            	callableStatement.registerOutParameter(1, Types.DATE);
            
            else 
            	callableStatement.registerOutParameter(1, Types.OTHER);

            callableStatement.execute();
            Object obj = callableStatement.getObject(1);
            callableStatement.close();

            return obj;
        }
        catch (Exception e) {
			throw new DBException(e);
        }
    }

}