Example of a database-lookup function

The following code exemplifies the use of database lookup as a user-defined XPath function:

package com.squarerootinc.wbimonitor;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.ibm.wbimonitor.xml.expression.udf.XPathFunction;

public class Examples {
public static final String NAMESPACE = "http://www.jimbo.org/beverages/functions";

@XPathFunction(
namespaceName = NAMESPACE,
localName = "getSuppliersCountInLocation",
description = "(driverName, protocol, dataSource, username, password, targetZipCode) This function returns the number of suppliers located in a given zip code stored in a database.",

isSelfContained = false,
isDeterministic = false
)
public static BigInteger getSuppliersCountInLocation(
final String JDBCDriver,
final String protocol,
final String dataSource,
final String username,
final String password,
final String targetZipCode
) {

String url = "jdbc:" + protocol + ":" + dataSource;
Connection con;
Statement stmt;
int count = 0;

try {
Class.forName(JDBCDriver + ".ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.println(e.getMessage());
}

try {
con = DriverManager.getConnection(url, username, password);
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT * FROM SUPPLIERS");

srs.beforeFirst();
while (srs.next()) {
String zipCode = srs.getString("ZIPCODE");
if (zipCode.equalsIgnoreCase (targetZipCode))
count++;

}
srs.close();
stmt.close();
con.close();

} catch (SQLException ex) {
count = 0;
}

return BigInteger.valueOf(count);
}
}