Read XMLType as CLOB Java JDBC Example

In this Java example, we will discuss how to read XMLType data in an Oracle table as clob value (oracle.sql.CLOB) and parse the data using SAX parser, and print the contents of the XML to the output. The tutorial will teach you how to read XMLType data through Java JDBC code, by converting it into Clob. Note that we described an approach earlier to query XMLType data directly as oracle.xdb.XMLType object. This example presents a different approach to handle XMLType data.

The high level steps involved reading XMLType data as CLOB in Java is shown below:

Read XMLType as Clob and Print - Java JDBC Example - Steps 

We will walk through each of these steps as we move on.

Read XMLType Using getCLOBVal() in Java

This is one of the key step in this code. Here, you use the method getCLOBVal() on an XMLType column in your JDBC SQL Query. This will fetch the XMLType data as CLOB inside your Java program, which you can use for further manipulation. A code segment (full code provided at the end) that does this is shown below:

        OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement("SELECT A.TESTFIELD.getCLOBVal() myClob FROM JDBC_XML A");

OracleResultSet – getCLOB() – Java

When you loop through the rows from the SQL output, you have to use getCLOB() method against the result set. This method returns an object of type oracle.sql.CLOB. In our Java program, we will be using this method and map it to getCLOBVal(), to fetch the XMLType data as CLOB.  The signature of this method is shown below:

getCLOB() method - XMLType to Clob- signature - Java example

Note that you can also pass the column name directly to this method instead of the number. The code segment is provided below:

        oracle.sql.CLOB clb= null;
        while (orset.next()) {
            clb = orset.getCLOB(1);;
        }

Convert XMLType Clob to java.io.Reader

Well, we want to parse the XMLType data and print the XML. For this we will use SAX parser and it requires the input to be supplied as an object of type org.xml.sax.InputSource. You can create an InputSource from java.io.Reader. This means, you have to find a way to convert your XML (aka clob) to java.io.Reader. You can do this by invoking the getCharacterStream() method in oracle.sql.CLOB class. The signature of this method is shown below:

Convert XMLType - Clob - to java.io.Reader - Java example

The Java code segment is shown below:

        /* Convert CLOB to java.io.Reader */
        Reader my_clob_xml= clb.getCharacterStream();

        /* Create InputSource from Reader */
        InputSource myInputSource=new InputSource(my_clob_xml);

Note that we have also created an InputSource object required for our parser to read the XML data.

Parse XMLType Data and Print Output

Finally, you can invoke the SAXParser to parse the XMLType data and print the contents of the table as plain text output. There are multiple tutorials available on the web that explains how to use SAXParser to parse XML data. I will not be covering the parser in detail as we are focussed on printing the contents of the XML.  You can see the parser and the handler in action in the complete Java code that we will provide shortly.

Java JDBC Program – XMLType As Clob

The complete Java JDBC program that you can use, to query a XMLType data as Clob in Oracle and print the XML contents back is shown below:

import java.io.*;
import java.sql.*;
import oracle.xdb.XMLType;
import oracle.jdbc.*;
import oracle.sql.CLOB;
import java.io.Reader;
import org.xml.sax.InputSource;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;
public class XmltypeAsClob {
    public static void main(String[] args) throws Exception {
        Class.forName ("oracle.jdbc.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/orcl", "hr", "hr");
        OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement("SELECT A.TESTFIELD.getCLOBVal() myClob FROM JDBC_XML A");
        ResultSet rset = stmt.executeQuery();
        OracleResultSet orset = (OracleResultSet) rset;
        oracle.sql.CLOB clb= null;
        while (orset.next()) {
            clb = orset.getCLOB(1);;
        }

        /* Convert CLOB to java.io.Reader */
        Reader my_clob_xml= clb.getCharacterStream();

        /* Create InputSource from Reader */
        InputSource myInputSource=new InputSource(my_clob_xml);

        /* We will now use SAX Parser to parse this XML */
        SAXParserFactory factory = SAXParserFactory.newInstance();
        SAXParser saxParser = factory.newSAXParser();

        /* Define a handler for the parser. The handler would just dump the value of tags
        as fetched from the oracle table */
        DefaultHandler handler = new DefaultHandler() {
            boolean bid = false;
            boolean bnames = false;
            public void startElement(String uri, String localName,String qName, Attributes attributes)
            throws SAXException {
                    if (qName.equalsIgnoreCase("ID")) {
                    bid = true;
                }

                if (qName.equalsIgnoreCase("NAMES")) {
                    bnames = true;
                }
            }
            public void characters(char ch[], int start, int length)
            throws SAXException {

                if (bid) {
                    System.out.println("Value of ID : " + new String(ch, start, length));
                    bid = false;
                }

                if (bnames) {
                    System.out.println("Value of Name : "+ new String(ch, start, length));
                    bnames = false;
                }
            }
        };
        /* Call parse method to parse the input */
        saxParser.parse(myInputSource, handler);
        /* Close all DB related objects */
        orset.close();
        stmt.close();
        conn.close();
    }
}

To test this program you need to have the ojdbc6.JAR files loaded in your classpath. This may be different depending on your database version. Also, note that we don’t need xdb.jar and xmlparserv2.jar unlike our previous tutorial.  You will also require a test table with XMLType data pre loaded. Here is a script that you can use for testing this code:

CREATE TABLE JDBC_XML
(
TESTFIELD XMLTYPE
)
INSERT INTO JDBC_XML(TESTFIELD) VALUES('<test><set><id>123</id><names>emp</names></set></test>');
COMMIT;

The output of the program is shown below:

java -classpath .;ojdbc6.jar XmltypeAsClob
Value of ID : 123
Value of Name : emp

That completes our tutorial to read XMLType data from Oracle table as CLOB, using a Java JDBC Program. You can try this Java code and post a comment back to us. See you in a different example next time.