MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Read a CLOB through JDBC

with 5 comments

There was a post on the Oracle Technical Network about connecting and reading a CLOB. I thought it might be a nice thing to add to my blog. To make my publisher happy, I borrowed it from my Oracle Database 11g PL/SQL Programming book. If you’re wondering why it’s in there, well there’s a chapter on writing Java libraries and PL/SQL wrappers to libraries.

This is based on Java 5 and a connection to an Oracle Database 11g instance. You should set your environment $PATH to the Java home bin directory, and $CLASSPATH to the location of the ojdbc5.jar file before compiling and running this program.

It depends on seeding the LOB first. You can find the code to do that in the presentation that I made at the Utah Oracle User Group Training Days 2008. The program will render a Java Swing JFrame like this:

Here’s the code below. Since I neglected to mention it when this was originally posted, you should ensure that you don’t forget to close the stream before you exit. Left open, the stream can cause a memory leak.

// --------------------------------------------------------------------
// ReadCLOB.java
// Appendix D, Oracle Database 11g PL/SQL Programming
// by Michael McLaughlin
//
// This code demonstrates reading an image file and displaying
// the image in a JLabel in a JFrame.
// --------------------------------------------------------------------
 
// Java Application class imports.
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import java.awt.Font;
import java.awt.GridLayout;
import java.io.Reader;
 
// Generic JDBC imports.
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
 
// Oracle JDBC import.
import oracle.jdbc.driver.OracleDriver;
 
// -------------------------------------------------------------------/
public class ReadCLOB extends JPanel {
  // Define database connections.
  private String host = "mclaughlin11g";
  private String port = "1521";
  private String dbname = "orcl";
  private String userid = "plsql";
  private String passwd = "plsql";
 
  // Define display variables.
  private String clobText;
  private JScrollPane scrollPane;
  private JTextArea textArea;
 
  // -----------------------------------------------------------------/
  public ReadCLOB () {
 
  // Set layout manager.
  super(new GridLayout(1,0));
 
  // Query the database.
  clobText = getQuery(host,port,dbname,userid,passwd);
 
  // Construct text area and format it.
  textArea = new JTextArea(clobText);
  textArea.setEditable(false);
  textArea.setFont(new Font("San Serif",Font.PLAIN,14));
  textArea.setLineWrap(true);
  textArea.setRows(10);
  textArea.setSize(400,100);
  textArea.setWrapStyleWord(true);
 
  // Put the image in container, and add label to panel.
  scrollPane = new JScrollPane(textArea);
  add(scrollPane); }
 
  // -----------------------------------------------------------------/
 
  private String getQuery(String host,String port,String dbname,String user,String pswd) {
 
    // Define method variables.
    char[] buffer;
    int count = 0;
    int length = 0;
    String data = null;
    String[] type;
    StringBuffer sb;
 
    try {
      // Load Oracle JDBC driver.
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 
      // Define and initialize a JDBC connection.
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" + host + ":" + port +
                                                    ":" + dbname, userid, passwd);
      // Define metadata object.
      DatabaseMetaData dmd = conn.getMetaData();
 
      // Create statement.
      Statement stmt = conn.createStatement();
 
      // Execute query.
      ResultSet rset =
        stmt.executeQuery("SELECT item_desc " +
                          "FROM   item " +
                          "WHERE  item_title = 'The Lord of the Rings - Fellowship of the Ring'" +
                          "AND    item_subtitle = 'Widescreen Edition'");
 
      // Get the query metadata, size array and assign column values.
      ResultSetMetaData rsmd = rset.getMetaData();
      type = new String[rsmd.getColumnCount()];
      for (int col = 0;col < rsmd.getColumnCount();col++)
        type[col] = rsmd.getColumnTypeName(col + 1);
 
      // Read rows and only CLOB data type columns.
      while (rset.next()) {
        for (int col = 0;col < rsmd.getColumnCount();col++) {
          if (type[col] == "CLOB") {
 
          // Assign result set to CLOB variable.
          Clob clob = rset.getClob(col + 1);
 
          // Check that it is not null and read the character stream.
          if (clob != null) {
            Reader is = clob.getCharacterStream();
 
            // Initialize local variables.
            sb = new StringBuffer();
            length = (int) clob.length();
 
            // Check CLOB is not empty.
            if (length > 0) {
              // Initialize control structures to read stream.
              buffer = new char[length];
              count = 0;
 
              // Read stream and append to StringBuffer.
              try {
                while ((count = is.read(buffer)) != -1)
                  sb.append(buffer);
 
                  // Assign StringBuffer to String.
                  data = new String(sb); }
              catch (Exception e) {} }
            else
              data = (String) null; }
          else
            data = (String) null; }
        else {
          data = (String) rset.getObject(col + 1); }}}
 
      // Close stream to prevent memory leaks.
      is. close();
 
      // Close resources.
      rset.close();
      stmt.close();
      conn.close();
 
      // Return CLOB as a String data type.
      return data; }
    catch (SQLException e) {
      if (e.getSQLState() == null) {
        System.out.println(
          new SQLException("Oracle Thin Client Net8 Connection Error.",
                           "ORA-" + e.getErrorCode() +
                           ": Incorrect Net8 thin client arguments:\n\n" +
                           "  host name     [" + host + "]\n" +
                           "  port number   [" + port + "]\n" +
                           "  database name [" + dbname + "]\n"
                           , e.getErrorCode()).getSQLState());
 
        // Return an empty String on error.
        return data; }
      else {
        System.out.println(e.getMessage());
        return data; }}
    finally {
      if (data == null) System.exit(1); }}
 
// -----------------------------------------------------------------/
 
  public static void main(String[] args) {
  // Define window.
  JFrame frame = new JFrame("Read CLOB Text");
 
  // Define and configure panel.
  ReadCLOB panel = new ReadCLOB();
  panel.setOpaque(true);
 
  // Configure window and enable default close operation.
  frame.setContentPane(panel);
  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  frame.setLocation(100,100);
  frame.pack();
  frame.setVisible(true); }}

Written by maclochlainn

September 7th, 2008 at 11:55 pm

Posted in Uncategorized

5 Responses to 'Read a CLOB through JDBC'

Subscribe to comments with RSS or TrackBack to 'Read a CLOB through JDBC'.

  1. May I ask why you do not close Reader is once reading from CLOB is finished?
    Do you expect it to be closed when rs.close is called?

    Thanks!

    Li

    27 Feb 09 at 10:38 am

  2. Useful information, but it would be more useful if you cover closing the clob objects that you opened in your java code. Otherwise, they may hit the issue for Memory leak by holding up the Temp space used by these CLOB objects.

    AK

    20 Mar 09 at 9:42 am

  3. Thanks for reminding me. The stream is now explicitly closed in the code example.

    maclochlainn

    20 Mar 09 at 10:52 pm

  4. Is this method (with the appropriate changes) possible through SQLJ? Admittedly, this is a homework assignment for me but I can see the value of knowing how to do it. My JAVA isn’t the best but I would appreciate your advice.

    Thanks

    Gcurrier

    30 Oct 10 at 12:21 pm

  5. There are some changes in syntax but if you’re working with SQLJ they should stand out.

    maclochlainn

    31 Oct 10 at 12:12 am

Leave a Reply