How to read and write CLOB data

The following example demonstrates the Oracle JDBC 10g enhanced features for inserting and retrieving CLOB data from the database. Using the new features, large  data of more than 32765 bytes can be inserted into the database using the  existing PreparedStatement.setString() and PreparedStatement.getString()  methods.

public static void main(String[] args) throws SQLException {

java.sql.Connection conn = null;

java.sql.ResultSet rSet = null;
java.sql.PreparedStatement pstmt = null;

  // create the driver manager

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

}

private void insertClob() throws SQLException {

String sql = "insert into MYTABLE (FILEVALUES) values(?)";

      // create the connection

            if ((conn == null) || conn.isClosed()) {
                conn = DriverManager.getConnection(url, "kesavanarayanan", "kesavanarayanan");
            }

       pstmt = conn.prepareStatement(sql);

       // Read a big file(larger than 32765 bytes)

      String str = this.readFile();

}

    private void selectClob() throws SQLException {

        // Create a PreparedStatement object
        PreparedStatement pstmt = null;

        // Create a ResultSet to hold the records retrieved.
        ResultSet rset = null;
        try {
            // Create the database connection, if it is closed.
            if ((conn == null) || conn.isClosed()) {
                // Connect to the database.
                conn = DriverManager.getConnection(this.url, this.props);
            }

            // Create SQL query statement to retrieve records having CLOB data
            // from
            // the database.
            // String sqlCall = "SELECT UIN,CLIP_NAME,FORM,
            // WRITEPLACER_PROMPTS_ID,USER_RESPONSE_TEXT,BATCH_ID FROM VUEBATCH
            // WHERE rownum<10";
            //String sqlCall = "SELECT IEC_FILE from CAT_EXPOSURE_CONTROL where test_detail_id=1";
            String sqlCall = "SELECT IEC_FILE from CAT_EXPOSURE_CONTROL where test_detail_id=2";
            pstmt = conn.prepareStatement(sqlCall);

            // Execute the PrepareStatement
            rset = pstmt.executeQuery();

            FileWriter writer = null;
            // Get the CLOB value from the resultset
            while (rset.next()) {
                Clob clobVal = rset.getClob(1);
                Reader clobStream = null;
                if (clobVal != null) {
                    /*
                     * clobStream = clobVal.getCharacterStream(); // Holds the
                     * Clob data when the Clob stream is being read StringBuffer
                     * suggestions = new StringBuffer(); int nchars = 0; //
                     * Number of characters read // Read from the Clob stream
                     * and write to the stringbuffer char[] buffer = new
                     * char[4096]; //Buffer holding characters being transferred
                     * while((nchars = clobStream.read(buffer)) != -1) {// Read
                     * from Clob suggestions.append(buffer, 0, nchars); // Write
                     * to StringBuffer }
                     * System.out.println(suggestions.toString()); writer = new
                     * FileWriter(new File("sample.txt"));
                     * writer.write(suggestions.toString());
                     */
                    int len = (int) clobVal.length();
                    String clobStr = clobVal.getSubString(1, len);
                    System.out.println(clobStr);
                }
                if (clobStream != null)
                    clobStream.close(); // Close the Clob input stream
            }
            if (writer != null) {
                writer.flush();
                writer.close();
            }
        } catch (SQLException sqlex) {
            // Catch Exceptions and display messages accordingly.
            System.out
                    .println("SQLException while connecting and querying the "
                            + "database table: " + sqlex.toString());
            sqlex.printStackTrace();
        } catch (Exception ex) {
            System.out.println("Exception while connecting and querying the "
                    + "database table: " + ex.toString());
            ex.printStackTrace();
        } finally {
            // Close the resultset, statement and the connection objects.
            if (rset != null)
                rset.close();
            if (pstmt != null)
                pstmt.close();
            if (conn != null)
                conn.close();
        }
    }