Example: XML and XQuery support in Java (JDBC) procedure
Once the basics of Java™ procedures, programming in Java using the JDBC application programming interface (API), and XQuery are understood, you can create and call Java procedures that query XML data.
- the CREATE PROCEDURE statement for a parameter style JAVA procedure
- the source code for a parameter style JAVA procedure
- input and output parameters of data type XML
- use of an XML input parameter in a query
- assignment of the result of an XQuery, an XML value, to an output parameter
- assignment of the result of an SQL statement, an XML value, to an output parameter
- Prerequisites
- Before you work with the Java procedure example,
you might want to read the following topics:
The examples use a table that is named
xmlDataTable
, which is created and populated with the following statements:CREATE TABLE xmlDataTable ( num INTEGER, xdata XML )@ INSERT INTO xmlDataTable VALUES (1, XMLPARSE(DOCUMENT '<doc> <type>car</type> <make>Pontiac</make> <model>Sunfire</model> </doc>' PRESERVE WHITESPACE)), (2, XMLPARSE(DOCUMENT '<doc> <type>car</type> <make>Mazda</make> <model>Miata</model> </doc>' PRESERVE WHITESPACE)), (3, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Mary</name> <town>Vancouver</town> <street>Waterside</street> </doc>' PRESERVE WHITESPACE)), (4, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Mark</name> <town>Edmonton</town> <street>Oak</street> </doc>' PRESERVE WHITESPACE)), (5, XMLPARSE(DOCUMENT '<doc> <type>animal</type> <name>dog</name> </doc>' PRESERVE WHITESPACE)), (6, NULL), (7, XMLPARSE(DOCUMENT '<doc> <type>car</type> <make>Ford</make> <model>Taurus</model> </doc>' PRESERVE WHITESPACE)), (8, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Kim</name> <town>Toronto</town> <street>Elm</street> </doc>' PRESERVE WHITESPACE)), (9, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Bob</name> <town>Toronto</town> <street>Oak</street> </doc>' PRESERVE WHITESPACE)), (10, XMLPARSE(DOCUMENT '<doc> <type>animal</type> <name>bird</name> </doc>' PRESERVE WHITESPACE))@
- Procedure
- You can use the following example as references when you are making your own Java procedures:
The Java external code file
The example shows a Java procedure implementation. The example consists of two parts: the CREATE PROCEDURE statement and the external Java code implementation of the procedure from which the associated Java class can be built.
stpclass.java
included in a JAR
file named myJAR
. The file has the following format: using System;
import java.lang.*;
import java.io.*;
import java.sql.*;
import java.util.*;
import com.ibm.db2.jcc.DB2Xml;
public class stpclass
{ ...
// Java procedure implementations
...
}
The Java class file imports
are indicated at the top of the file. The com.ibm.db2.jcc.DB2Xml
import
is required if any of the procedures in the file contain parameters
or variables of type XML is used.
It is important to note the name of the class file and JAR name that contains the procedure implementation. These names are important because the EXTERNAL clause of the CREATE PROCEDURE statement for each procedure must specify this information so that the database manager can locate the class at run time.
Example: Parameter style JAVA procedure with XML parameters
xmlProc1
procedure
takes an input parameter, inXML
, inserts the inXML
value
into a table, queries XML data using both an SQL statement and an
XQuery expression, and sets two output parameters, outXML1
, and outXML2
. CREATE PROCEDURE xmlProc1 ( IN inNUM INTEGER,
IN inXML XML as CLOB (1K),
OUT out1XML XML as CLOB (1K),
OUT out2XML XML as CLOB (1K)
)
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
FENCED
THREADSAFE
DYNAMIC RESULT SETS 0
PROGRAM TYPE SUB
NO DBINFO
EXTERNAL NAME 'myJar:stpclass.xmlProc1'@
//*************************************************************************
// Stored Procedure: XMLPROC1
//
// Purpose: Inserts XML data into XML column; queries and returns XML data
//
// Parameters:
//
// IN: inNum -- the sequence of XML data to be insert in xmldata table
// inXML -- XML data to be inserted
// OUT: out1XML -- XML data to be returned
// out2XML -- XML data to be returned
//
//*************************************************************************
public void xmlProc1(int inNum,
DB2Xml inXML ,
DB2Xml[] out1XML,
DB2Xml[] out2XML
)
throws Exception
{
Connection con = DriverManager.getConnection("jdbc:default:connection");
// Insert data including the XML parameter value into a table
String query = "INSERT INTO xmlDataTable (num, inXML ) VALUES ( ?, ? )" ;
String xmlString = inXML.getDB2String() ;
stmt = con.prepareStatement(query);
stmt.setInt(1, inNum);
stmt.setString (2, xmlString );
stmt.executeUpdate();
stmt.close();
// Query and retrieve a single XML value from a table using SQL
query = "SELECT xdata from xmlDataTable WHERE num = ? " ;
stmt = con.prepareStatement(query);
stmt.setInt(1, inNum);
ResultSet rs = stmt.executeQuery();
if ( rs.next() )
{ out1Xml[0] = (DB2Xml) rs.getObject(1); }
rs.close() ;
stmt.close();
// Query and retrieve a single XML value from a table using XQuery
query = "XQUERY for $x in db2-fn:xmlcolumn(\"xmlDataTable.xdata\")/doc
where $x/make = \'Mazda\'
return <carInfo>{$x/make}{$x/model}</carInfo>";
stmt = con.createStatement();
rs = stmt.executeQuery( query );
if ( rs.next() )
{ out2Xml[0] = (DB2Xml) rs.getObject(1) ; }
rs.close();
stmt.close();
con.close();
return ;
}