This discussion is archived
5 Replies Latest reply: Aug 26, 2006 3:16 PM by mdrake RSS

How do I access XMLType using JDBC ?

mdrake Expert
Currently Being Moderated
This thread will address a number of issues related to accessing XMLType tables and columns from Java via JDBC.
  • 1. Should I use OCI (Thick) or Thin Java to when working with XMLType.
    mdrake Expert
    Currently Being Moderated
    For non schema based XML Type it makes no difference.

    Schema Based XML Type is only supported with the OCI or thick JDBC Driver. The reason for this this is that the OCI 'C' Libraries contain a lot of code that allow some of the processing associated with the methods of an XMLType to be off-loaded to the client. The thin driver does not provide a rich enough infrastructure to allow this kind of optimization.

    For instance when the writeToOutputStream method is called the conversion from the object representation to the text representation of the XML is performed by the OCI client libraries not the database.
  • 2. How do I use the thin driver with Schema Based XMLType ?
    mdrake Expert
    Currently Being Moderated
    The only way to use the thin driver with Schema Based XMLType is to force a cast from Schema-Based to non-Schema-Based XMLType. This can be done by invoking the createNonSchemaBasedXMLType() method on the Schema-Based XMLType object.
  • 4. How do I access an XMLType from Java ?
    mdrake Expert
    Currently Being Moderated
    The following code shows examples of accessing XMLType using Java getting an instance of W3C DOM interface org.w3c.dom.Document. It also shows how which DOM implementation is returned by the getDOM() method is dependant on whether or not the OCI driver or Thin Dirver is in used.

    The code also show how to use the createNonSchemaBasedXMLType() method to access a schema based XMlLType via the thin driver...
    public class GetXMLTypeDOM extends BaseApplication
    {
       protected String driverType;
       
       protected String getDriver() {
         return this.driverType;
       }
    
       public void doSomething(String[] Args) throws Exception
       {
         OraclePreparedStatement  statement = null;
    
         this.driverType = this.OCI_DRIVER;
         this.initializeConnection();
    
         statement = (OraclePreparedStatement) getConnection().prepareStatement("select object_value from PURCHASEORDER_NSB_XMLTYPE where rownum < 2");
         getDocument(statement);
    
         statement = (OraclePreparedStatement) getConnection().prepareStatement("select object_value from PURCHASEORDER_SB_XMLTYPE where rownum < 2");
         getDocument(statement);
         
         getConnection().close();
    
         this.driverType = this.THIN_DRIVER;
         this.initializeConnection();
    
         statement = (OraclePreparedStatement) getConnection().prepareStatement("select object_value from PURCHASEORDER_NSB_XMLTYPE where rownum < 2");
         getDocument(statement);
    
         statement = (OraclePreparedStatement) getConnection().prepareStatement("select object_value from PURCHASEORDER_SB_XMLTYPE where rownum < 2");
         getDocument(statement);
          
         statement = (OraclePreparedStatement) getConnection().prepareStatement("select x.object_value.createNonSchemaBasedXML() from PURCHASEORDER_SB_XMLTYPE x where rownum < 2");
         getDocument(statement);
    
         getConnection().close();
    
       }
    
       private void getDocument(OraclePreparedStatement statement) 
       {
         OracleResultSet resultSet = null;
         org.w3c.dom.Document  doc = null;
         XMLType xml = null;
         
         try {
           resultSet = (OracleResultSet) statement.executeQuery();
      
           while (resultSet.next()) 
           { 
              xml = (XMLType) resultSet.getObject(1);
              doc = xml.getDOM();
              System.out.println("DOM Implementation is " + doc.getClass().getName());
              xml.close();
           }   
           resultSet.close();
           statement.close();
        } 
        catch (SQLException sqle) {
            System.out.println("Caught SQL Exception : " + sqle.getMessage());
        }
      }
           
      public static void main (String[] args)
      {
        try 
        {
          GetXMLTypeDOM example = new GetXMLTypeDOM();
          example.doSomething(args);
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }
    }
    Here's the output from running this class..
    C:\TEMP>
    C:\Oracle\JDeveloper\jdk\bin\javaw.exe -client -classpath C:\xdb\JDeveloper\Classes;C:\Oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar;C:\Oracle\product\10.2.0\db_1\LIB\xmlparserv2.jar;C:\Oracle\product\10.2.0\db_1\RDBMS\jlib\xdb.jar;C:\xdb\JDeveloper\jakarta-slide-webdavclient-bin-2.1\lib\jakarta-slide-webdavlib-2.1.jar;C:\xdb\JDeveloper\jakarta-slide-webdavclient-bin-2.1\lib\commons-httpclient.jar;C:\xdb\JDeveloper\jakarta-slide-webdavclient-bin-2.1\lib\commons-logging.jar;C:\xdb\JDeveloper\jakarta-slide-webdavclient-bin-2.1\lib\jdom-1.0.jar -Dcom.oracle.st.xmldb.pm.ConnectionParameters=C:\\xdb\\jdeveloper\\SimpleExamples\\LocalConnection.xml -Dhttp.proxyHost=www-proxy.us.oracle.com -Dhttp.proxyPort=80 -Dhttp.nonProxyHosts=192.168.0.77|localhost|192.168.1.1|*.oracle.com|*.us.oracle.com -Dhttps.proxyHost=www-proxy.us.oracle.com -Dhttps.proxyPort=80 -Dhttps.nonProxyHosts=192.168.0.77|localhost|192.168.1.1|*.oracle.com|*.us.oracle.com com.oracle.st.xmldb.pm.examples.GetXMLTypeDOM -mx2048M
    ConnectionProvider.establishConnection(): Connecting as SCOTT/TIGER@jdbc:oracle:oci8:@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(service_name=ORA10GR2.xp.mark.drake.oracle.com)(server=DEDICATED)))
    ConnectionProvider.establishConnection(): Database Connection Established
    DOM Implementation is oracle.xdb.dom.XDBDocument
    DOM Implementation is oracle.xdb.dom.XDBDocument
    ConnectionProvider.establishConnection(): Connecting as SCOTT/TIGER@jdbc:oracle:thin:@localhost:1521:ORA10GR2
    ConnectionProvider.establishConnection(): Database Connection Established
    DOM Implementation is oracle.xml.parser.v2.XMLDocument
    Caught SQL Exception : Only LOB or String Storage is supported in Thin XMLType
    DOM Implementation is oracle.xml.parser.v2.XMLDocument
    Process exited with exit code 0.
  • 5. Re: How do I access an XMLType from Java ?
    mdrake Expert
    Currently Being Moderated
    Here is an example of createing an XMLType from a Java program...
    public class InsertXMLType extends BaseApplication
    {
       
    
       public void doSomething(String[] Args) throws Exception
       {
         OracleCallableStatement  statement = null;
         String statementText;
    
         statementText = "insert into xmlTable values(:1)";
         System.out.println("GetXMLType.doSomething : Driver Type = " + this.getDriver() + ". Statement = " + statementText);
    
         OracleResultSet resultSet = null;
    
         XMLType xml;
         
         // In the real world we would get an InputStream from something more interesting that
         // an in memeory String
         
         String xmlText = "<Root><Element Attr=\"AttrValue\">A text Node</Element></Root>";
         byte[] byteBuffer = xmlText.getBytes();
         InputStream is = new ByteArrayInputStream(byteBuffer);
         
         xml = XMLType.createXML(this.getConnection(),is);
         
            
         statement = (OracleCallableStatement) getConnection().prepareCall(statementText);
         statement.setObject(1,xml);
         boolean result = statement.execute();
         statement.close();
         getConnection().commit();
         getConnection().close();
      }
           
      public static void main (String[] args)
      {
        try 
        {
          InsertXMLType example = new InsertXMLType();
          example.initializeConnection();
          example.doSomething(args);
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }
    }
    Another way of doing this is using a temporary CLOB
    public class InsertXMLTypeFromCLOB extends BaseApplication
    {
       
       public static String TABLE_NAME  = "Table";
       public static String SOURCE_FILE = "File";
       public static String ITERATIONS  = "Iterations";
       
       public void doSomething(String[] Args) throws Exception
       {
          OracleCallableStatement  statement = null;
          String statementText;
    
          CLOB    clob = CLOB.createTemporary( getConnection(), true, CLOB.DURATION_SESSION);
    
          statementText = "insert into " + this.getSetting(this.TABLE_NAME) + " values(xmltype(:1))";
          System.out.println("GetXMLType.doSomething : Driver Type = " + this.getDriver() + ". Statement = " + statementText);
          statement = (OracleCallableStatement) getConnection().prepareCall(statementText);
     
          InputStream is = new FileInputStream(getSetting(this.SOURCE_FILE)); 
              
          InputStreamReader reader = new InputStreamReader( is );
          Writer writer = clob.setCharacterStream(0);
                
          char [] buffer = new char [ clob.getChunkSize() ];
          for( int charsRead = reader.read( buffer );
          charsRead > - 1;
          charsRead = reader.read( buffer ) )
          {
             writer.write( buffer, 0, charsRead );
          }
          writer.close();
          reader.close();
                        
          statement.setCLOB(1,clob);
          boolean result = statement.execute();
          is.close();
            
          CLOB.freeTemporary(clob);
          statement.close();
          getConnection().commit();
          getConnection().close();
      }
           
      public static void main (String[] args)
      {
        try 
        {
          InsertXMLTypeFromCLOB example = new InsertXMLTypeFromCLOB();
          example.initializeConnection();
          example.doSomething(args);
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }
    }