MacLochlainns Weblog

// --------------------------------------------------------------------
// 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); }}