import java.io.File; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.DateFormat; import java.text.SimpleDateFormat; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import org.w3c.dom.Attr; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.w3c.dom.Text; import org.xml.sax.SAXException; import com.sun.org.apache.xml.internal.serialize.OutputFormat; import com.sun.org.apache.xml.internal.serialize.XMLSerializer; /** * * Acord "Party Inquiry" example written with JDBC/DOM. * */ public class Acord204JDBC { // Change base directory to reference the folder where you have downloaded the XQuery files public static final String queryBaseDir = "C:/acord-xquery"; // Change server/account information to be consistent with your database settings public static final String connectionUrl = "jdbc:datadirect:sqlserver://localhost:1433;databaseName=pubs;user=user;password=pass"; /** The TXLife XML request */ public static final String inputDoc = "204-1.xml"; /** result codes */ public static final String RESULT_SUCCESS = "1"; public static final String RESULT_FAILURE = "5"; /** inquiry level */ public static final String OLI_INQUIRY_OBJREL = "2"; /** * */ public static void main(String[] args) throws Exception { // register JDBC driver Class.forName("com.ddtek.jdbc.sqlserver.SQLServerDriver"); new Acord204JDBC().run(); } public void run() throws IOException, ParserConfigurationException, SAXException { // read request document String uri = new File(Acord204JDBC.queryBaseDir + Acord204JDBC.inputDoc).toURI().toString(); Document request = Acord204JDBC.readDocument(uri); // create response document DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document response = builder.newDocument(); // create tx:TXLife and tx:TXLifeResponse nodes in response document Element root = response.createElementNS("http://ACORD.org/Standards/Life/2", "tx:TXLife"); response.appendChild(root); Attr attr = response.createAttributeNS("http://www.w3.org/2000/xmlns/", "xmlns:tx"); attr.setNodeValue("http://ACORD.org/Standards/Life/2"); root.setAttributeNodeNS(attr); Element txLifeResponse = response.createElement("tx:TXLifeResponse"); root.appendChild(txLifeResponse); // get tx:TXLifeRequest Element txLifeRequest = getElementByTagName(request.getDocumentElement(), "tx:TXLifeRequest"); if ( txLifeRequest != null ) { // and import the TransRefGUID and TransType from the request Node transRefGuid = getElementByTagName(txLifeRequest, "tx:TransRefGUID"); if ( transRefGuid != null ) { txLifeResponse.appendChild(response.importNode(transRefGuid, true)); } Node transType = getElementByTagName(txLifeRequest, "tx:TransType"); if ( transType != null ) { txLifeResponse.appendChild(response.importNode(transType, true)); } // set TransExeDate and TransExeTime java.util.Date now = new java.util.Date(); Node node = txLifeResponse.appendChild(response.createElement("tx:TransExeDate")); DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); addChildText(response, node, df.format(now)); node = txLifeResponse.appendChild(response.createElement("tx:TransExeTime")); df = new SimpleDateFormat("HH:mm:ss"); addChildText(response, node, df.format(now)); // create ResultCode node - will be filled after execution Element transResult = addChildElement(response, txLifeResponse, "tx:TransResult"); Element resultCode = addChildElement(response, transResult, "tx:ResultCode"); // create OLifE node Element olife = addChildElement(response, txLifeResponse, "tx:OLifE"); olife.setAttribute("Version", "2.12.00"); Connection conn = null; try { //connect conn = DriverManager.getConnection(connectionUrl); // process transaction process204(conn, request, response, olife); // Succeeded - set result code resultCode.setAttribute("tc", Acord204JDBC.RESULT_SUCCESS); addChildText(response, resultCode, "Success"); } catch ( SQLException ex) { // Failed - set result code resultCode.setAttribute("tc", Acord204JDBC.RESULT_FAILURE); addChildText(response, resultCode, "Failure"); Element child = addChildElement(response, transResult, "tx:ResultInfo"); child = addChildElement(response, child, "tx:ResultInfoDesc"); addChildText(response, child, ex.toString()); } finally { if (conn != null) { try { conn.close(); } catch ( SQLException ex ) { } } } } Acord204JDBC.dumpDocument(response); } /** * */ public void process204( Connection conn, Document request, Document response, Element olifeResponse) throws SQLException { // get tx:TXLifeRequest Element txLifeRequest = getElementByTagName(request.getDocumentElement(), "tx:TXLifeRequest"); // get OLifE object Element olifeRequest = getElementByTagName(txLifeRequest, "tx:OLifE"); // get inquiry level Element child = getElementByTagName(txLifeRequest, "tx:InquiryLevel"); String inqueryLevel = null; if ( child != null ) { inqueryLevel = child.getAttribute("tc"); } if ( olifeRequest != null ) { // get policyProduct list NodeList partyList = olifeRequest.getElementsByTagName("tx:Party"); // Prepare select statements for the 3 relations: // 1-1 relation {Party - Person} PreparedStatement stmt1 = conn.prepareStatement( "SELECT pa.PartyTypeCode, pa.PartyKey, pa.FullName, pe.FirstName, pe.LastName, pe.MarStat, pe.Gender, pe.BirthDate " + "FROM ACORD_PARTY pa, ACORD_PERSON pe " + "WHERE pa.GovtID = ? AND pa.PartyKey = pe.PartyKey" ); // 1->N relation {Party -> Address} PreparedStatement stmt2 = conn.prepareStatement( "SELECT a.AddressTypeCode, a.Line1, a.City, a.AddressState, a.Zip, a.AddressCountry " + "FROM ACORD_ADDRESS a " + "WHERE a.OwnerKey = ?"); // N<->N relation PreparedStatement stmt3 = conn.prepareStatement( "SELECT p.PhoneTypeCode, p.CountryCode, p.AreaCode, p.DialNumber " + "FROM ACORD_RELATION r, ACORD_PHONE p " + "WHERE r.OriginatingObjectKey = ? AND " + " r.OriginatingObjectType = 6 AND " + " r.RelatedObjectType = 3 AND " + " p.PhoneKey = r.RelatedObjectKey "); if ( partyList != null ) { for (int i = 0; i < partyList.getLength(); i++) { Element party = (Element) partyList.item(i); // get tx:Party child = getElementByTagName(party, "tx:GovtID"); String govtId = null; if ( child != null && child.getFirstChild() instanceof Text ) { govtId = child.getFirstChild().getNodeValue(); } // bind govtId to stmt1 stmt1.setString(1, govtId); if (stmt1.execute()) { ResultSet rs1 = stmt1.getResultSet(); if (rs1.next()) { String partyKey = rs1.getString(2); // create Party object Element resultParty = addChildElement(response, olifeResponse, "tx:Party"); addAttribute(response, resultParty, "id", "Party_" + Integer.toString(i + 1)); child = addChildElement(response, resultParty, "tx:PartyTypeCode"); addAttribute(response, child, "tc", rs1.getString(1)); child = addChildElement(response, resultParty, "tx:PartyKey"); addChildText(response, child, rs1.getString(2)); child = addChildElement(response, resultParty, "tx:FullName"); addChildText(response, child, rs1.getString(3)); child = addChildElement(response, resultParty, "tx:GovtID"); addChildText(response, child, govtId); // create Person object Element person = addChildElement(response, resultParty, "tx:Person"); child = addChildElement(response, person, "tx:FirstName"); addChildText(response, child, rs1.getString(4)); child = addChildElement(response, person, "tx:LastName"); addChildText(response, child, rs1.getString(5)); child = addChildElement(response, person, "tx:MarStat"); addAttribute(response, child, "tc", rs1.getString(6)); child = addChildElement(response, person, "tx:Gender"); addAttribute(response, child, "tc", rs1.getString(7)); child = addChildElement(response, person, "tx:BirthDate"); addChildText(response, child, rs1.getString(8)); // if the inquiry level is set to include related objects, // inlcude addresses and phones if ( OLI_INQUIRY_OBJREL.equals(inqueryLevel) ) { //bind partyKey and fetch addresses stmt2.setString(1, partyKey); if (stmt2.execute()) { ResultSet rs2 = stmt2.getResultSet(); while (rs2.next()) { // create Address object Element address = addChildElement(response, resultParty, "tx:Address"); child = addChildElement(response, address, "tx:AddressTypeCode"); addAttribute(response, child, "tc", rs2.getString(1)); child = addChildElement(response, address, "tx:Line1"); addChildText(response, child, rs2.getString(2)); child = addChildElement(response, address, "tx:City"); addChildText(response, child, rs2.getString(3)); child = addChildElement(response, address, "tx:AddressState"); addChildText(response, child, rs2.getString(4)); child = addChildElement(response, address, "tx:Zip"); addChildText(response, child, rs2.getString(5)); child = addChildElement(response, address, "tx:AddressCountry"); addChildText(response, child, rs2.getString(6)); } rs2.close(); } //bind partyKey and fetch phones stmt3.setString(1, partyKey); if (stmt3.execute()) { ResultSet rs3 = stmt3.getResultSet(); while (rs3.next()) { // create Phone object Element phone = addChildElement(response, resultParty, "tx:Phone"); child = addChildElement(response, phone, "tx:PhoneTypeCode"); addAttribute(response, child, "tc", rs3.getString(1)); child = addChildElement(response, phone, "tx:CountryCode"); addChildText(response, child, rs3.getString(2)); child = addChildElement(response, phone, "tx:AreaCode"); addChildText(response, child, rs3.getString(3)); child = addChildElement(response, phone, "tx:DialNumber"); addChildText(response, child, rs3.getString(4)); } rs3.close(); } } } rs1.close(); } } } } } /////////////////////////////////////////////////////////////////////////////// /** DOM utility to find a single child element by tag name */ public Element getElementByTagName( Element node, String name) { NodeList list = node.getElementsByTagName(name); if (list.getLength() == 1) { return (Element) list.item(0); } return null; } /** DOM utility to create a text node and append it as child node of a given parent. */ public Text addChildText( Document doc, Node parent, String content) { Text text = doc.createTextNode(content); parent.appendChild(text); return text; } /** DOM utility to create a attribute node and add it to an element. */ public Attr addAttribute( Document doc, Element parent, String name, String value) { Attr attr = doc.createAttribute(name); attr.setNodeValue(value); parent.setAttributeNodeNS(attr); return attr; } /** DOM utility to create a element node and append it as child node of a given parent. */ public Element addChildElement( Document doc, Node parent, String name) { Element element = doc.createElement(name); parent.appendChild(element); return element; } /** * Utility to read an XML document as DOM tree. */ public static Document readDocument(String uri) throws SAXException, ParserConfigurationException, IOException { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); return builder.parse(uri); } /** * Utility to dump a DOM Document to System.out . */ public static void dumpDocument(Document document) throws IOException { OutputFormat format = new OutputFormat(document); format.setIndent(2); XMLSerializer output = new XMLSerializer(System.out, format); output.serialize(document); } }