oracleclob.java

CRM源码This file describes some issues that should be implemented in future and how it should be imple

/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package com.queplix.core.utils.sql.vendor.oracle;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.utils.StringHelper;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;

import java.io.CharArrayWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Serializable;
import java.io.Writer;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
 * This is a convenience class meant to generalize the action of updating an
 * CLOB column in an Oracle database.
 * This class MUST be thread-safe.
 *
 * @author [ALB] Baranov Andrey
 * @version $Revision: 1.1.1.1 $ $Date: 2005/09/12 15:31:23 $
 */
public class OracleClob
    implements Serializable {

    // =============================================================== Constants

    // Stored procedures call strings.
    protected final static String GET_EMPTY_CLOB_PROC = "{CALL GET_EMPTY_CLOB(?)}";
    protected final static String FREE_TMP_CLOB_PROC = "{CALL FREE_TMP_CLOB(?)}";

    // ========================================================== Public methods

    /**
     * Sets the CLOB into the PreparedStement object.
     *
     * @param stat PreparedStement object to set CLOB into
     * @param pos substitution position in statement
     * @param data CLOB to set
     */
    public void setObject( PreparedStatement stat, int pos, char[] data ) {

        CLOB clob = null;
        Connection conn = null;

        try {
            conn = stat.getConnection();
            if( data != null ) {
                clob = createCLOB( conn, data );
                ( ( OraclePreparedStatement ) stat ).setCLOB( pos, clob );
            } else {
                stat.setNull( pos, Types.CLOB );
            }

        } catch( SQLException ex ) {
            freeCLOB( conn, clob );
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );
        }

    }

    /**
     * Retrives the CLOB object from ResultSet object.
     *
     * @param rs ResultSet object to retrieve the CLOB from
     * @param pos substitution position in statement
     * @return CLOB data
     */
    public char[] getObject( ResultSet rs, int pos ) {

        try {
            CLOB clob = null;
            if(rs != null && rs instanceof OracleResultSet){
                Object obj = ( ( OracleResultSet ) rs ).getObject( pos );
                if( obj instanceof CLOB)
                    clob = (CLOB)obj; 
            }
            
            //CLOB clob = ( ( OracleResultSet ) rs ).getCLOB( pos );
            
            if( clob == null ) {
                return null;
            } else {
                return retriveObject( clob );
            }
        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );
        }
    }

    /**
     * Retrives the CLOB object from CallableStatement object.
     *
     * @param cs CallableStatement object to retrieve the CLOB from
     * @param pos substitution position in statement
     * @return CLOB data
     */
    public char[] getObject( CallableStatement cs, int pos ) {

        try {
            CLOB clob = ( ( OracleCallableStatement ) cs ).getCLOB( pos );
            if( clob == null ) {
                return null;
            } else {
                return retriveObject( clob );
            }
        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );
        }
    }

    // ========================================================= Protected methods

    // Retrives the CLOB object from ResultSet.
    protected char[] retriveObject( CLOB clob ) {

        Reader reader = null;
        CharArrayWriter caw = null;
        char[] ret = null;

        try {

            reader = clob.getCharacterStream();
            int len = clob.getBufferSize();
            char[] buffer = new char[len];
            caw = new CharArrayWriter();

            int i = 0;
            while( ( i = reader.read( buffer ) ) != -1 ) {
                caw.write( buffer, 0, i );
            }

            caw.flush();
            reader.close();
            ret = caw.toCharArray();

        } catch( SQLException sqlex ) {
            throw new GenericSystemException( "SQL exception: " + sqlex.getMessage(), sqlex );

        } catch( IOException ioex ) {
            throw new GenericSystemException( "I/O exception: " + ioex.getMessage(), ioex );

        } finally {
            if( caw != null ) {
                caw.close();
            }
            try {
                if( reader != null ) {
                    reader.close();
                }
            } catch( IOException ex ) {}
        }

        return ret;

    }

    // Creates or update a CLOB with data from the given object.
    protected CLOB createCLOB( Connection conn, char[] data ) {

        CLOB clob = null;
        CallableStatement cs = null;
        Writer writer = null;

        try {
            // Clear string
            String text = new String( data );
            text = StringHelper.clear( text );

            // Get an empty CLOB locator.
            cs = conn.prepareCall( GET_EMPTY_CLOB_PROC );
            cs.registerOutParameter( 1, Types.CLOB );
            cs.execute();

            clob = ( ( OracleCallableStatement ) cs ).getCLOB( 1 );

            writer = clob.getCharacterOutputStream();
            writer.write( text );
            writer.flush();
            writer.close();

        } catch( SQLException sqlex ) {
            freeCLOB( conn, clob );
            throw new GenericSystemException( "SQL exception: " + sqlex.getMessage(), sqlex );

        } catch( IOException ioex ) {
            freeCLOB( conn, clob );
            throw new GenericSystemException( "I/O exception: " + ioex.getMessage(), ioex );

        } finally {
            try {
                if( cs != null ) {
                    cs.close();
                }
            } catch( SQLException ex ) {}
            try {
                if( writer != null ) {
                    writer.close();
                }
            } catch( IOException ex ) {}
        }

        return clob;
    }

    // Frees the CLOB when an exception is raised.
    protected void freeCLOB( Connection conn, CLOB clob ) {

        PreparedStatement ps = null;
        try {
            if( conn != null && clob != null ) {
                ps = conn.prepareStatement( FREE_TMP_CLOB_PROC );
                ( ( OraclePreparedStatement ) ps ).setCLOB( 1, clob );
                ps.execute();
            }

        } catch( SQLException ex ) {
            // Do not throw an exception!

        } finally {
            try {
                if( ps != null ) {
                    ps.close();
                }
            } catch( SQLException ex ) {}
        }
    }

}