Example 2: Using Java External Functions and JDBC Prepared Statements

In contrast to our first example application, this application uses DataDirect XQuery Java external functions to pass the appropriate update action and related data extracted from the XML message from the XQuery query to the Java application. This approach requires more Java code, but it avoids the requirement to pass strings ('insert', 'update', and 'delete').

Although most of the code is straightforward, setting up the environment to execute the Java external functions is worth explaining further. Because DataDirect XQuery Java external functions are limited to static methods, the JDBC prepared statements that update the database also must be maintained in a static structure. This application places the JDBC prepared statements in a static HashMap named prepStats as shown in the following code:

static HashMap prepStats=new HashMap();

…

update=jdbcCon.prepareStatement(
 "update \"custinfo\" " +
 " set \"firstname\"=?," +
 " \"lastname\"=?," +
 " \"address\"=?" +
 "where \"custid\"=?");
updateStatIx=DDXQUpdate2.registerPrepStat(update);
insert=jdbcCon.prepareStatement(
 "insert into \"custinfo\" values(?,?,?,?)");
insertStatIx=DDXQUpdate2.registerPrepStat(insert);
delete=jdbcCon.prepareStatement(
 "delete from \"custinfo\" where \"custid\"=?");
deleteStatIx=DDXQUpdate2.registerPrepStat(delete);

…

public synchronized static int registerPrepStat(PreparedStatement ps) {
 prepStats.put(new Integer(prepIx),ps);
 return prepIx++;
 }

The updateStatIx integer and the equivalent insert and delete indices are passed into the XQuery query using an external variable, together with the XML message, to be processed as shown in the following code:

(: Declare namespace prefix for external Java functions :)
declare namespace p='ddtekjava:DDXQUpdate2'; 

(: Input XML message is passed using an external variable:)
declare variable 
 $inp as document-node(element(*,xdt:untyped)) external; 

(: References to JDBC prepared statements :)
declare variable $sqlUpd as xs:int external; 
declare variable $sqlIns as xs:int external; 
declare variable $sqlDel as xs:int external;
(: Declaration of Java external functions :)
declare function p:execInsert(
 $ix as xs:int, $cid as xs:int, $cfn as xs:string?, 
 $cfl as xs:string?, $cfa as xs:string?) as void() external; 
declare function p:execUpdate(
 $ix as xs:int, $cid as xs:int, $cfn as xs:string?, 
 $cfl as xs:string?, $cfa as xs:string?) as void() external; 
declare function p:execDelete(
 $ix as xs:int, $cid as xs:int) as void() external; 

(: Begin XQuery Query :)
(: Access custinfo with $custid and determine action :)
let $isUpdate := 
 exists(($firstname,$lastname,$address)) and
 exists(collection('custinfo')//custid[. eq $custid]) 
let $isDelete := 
 not($isUpdate) and 
 exists(collection('custinfo')//custid[. eq $custid]) 
let $isInsert := 
 not($isUpdate) and not($isDelete) 
return 
 if ($isUpdate) 
 then 
 p:execUpdate($sqlUpd,$custid,$firstname,$lastname,$address) 
 else 
 if($isInsert) 
 then
 p:execInsert($sqlIns,$custid,$firstname,$lastname,$address) 
 else 
 p:execDelete($sqlDel,$custid)

The Java external function execDelete, invoked in the previous code excerpt, is defined in the following code:

public static void execDelete(Integer ix,int custId) {
 PreparedStatement ps=(PreparedStatement)prepStats.get(ix);
 ps.setInt(1,custId);
 ps.addBatch();
 }