Accessing an XML document using XPATH

Requirement:
We have a completely organized XML file with SQL Queries. We need to parse the XML file and retrieve only the SQL Queries based on module code and sql query id.


XML File (ABCSQLQueries):

<SQL_ROOT>
<MODULE CODE="REQUEST">
<SQL_SEQMENT ID="PAYMENTTERMS_QUERY">
SELECT A.PAYMENTTERMSKEY, A.DISPLAY FROM OWN_ABC.PAYMENTTERMS A, OWN_ABC.REQUESTDETAILS B WHERE
B.REQUESTDETAILSKEY = ? AND A.PAYMENTTERMSKEY = B.PAYMENTTERMSKEY
</SQL_SEQMENT>
<SQL_SEQMENT ID="REQUEST_UPDATE">
update request query
</SQL_SEQMENT>
</MODULE>
<MODULE CODE="BANK">
<SQL_SEQMENT ID="BANK_SELECT">
Select bank query
</SQL_SEQMENT>
<SQL_SEQMENT ID="BANK_UPDATE">
update bank query
</SQL_SEQMENT>
</MODULE>
<MODULE CODE="COUNTRY">
<SQL_SEQMENT ID="COUNTRY_SELECT">
Select country query
</SQL_SEQMENT>
<SQL_SEQMENT ID="COUNTRY_UPDATE">
update country query
</SQL_SEQMENT>
</MODULE>
</SQL_ROOT>

Code:

import java.io.IOException;
import java.io.InputStream;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpression;
import javax.xml.xpath.XPathFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import org.xml.sax.SAXException;

public class ABCSQLLoader {

static Document doc;
static Element element;
// Declare it in constants file and use it instead of hard-coding like this
private static String fileName = "ABCSQLQueries.xml";

Uncomment only for execution through console mode.
/*
public static void main(String[] args) {
XMLParser xr = new XMLParser();
xr.getXmlParser();
}
*/

public void getXmlParser() {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

try {
DocumentBuilder builder = factory.newDocumentBuilder();
InputStream objInputStream = this.getClass().getResourceAsStream(fileName);
doc = builder.parse(objInputStream);
}catch(ParserConfigurationException parserException) {
}catch(SAXException saxException) {
}catch(IOException ioException) {
}catch(Exception exception){}

Uncomment only for execution through console mode.
//getSQLQuery("REQUEST", "PAYMENTTERMS_QUERY");
}

public static String getSQLQuery (String moduleName, String queryName) {
String sqlQuery = null;
try{
XPathFactory xpathFactory = XPathFactory.newInstance();
XPath xpath = xpathFactory.newXPath();
String expression ="SQL_ROOT/MODULE[@CODE='"+moduleName+"']/SQL_SEQMENT[@ID='"+queryName+"']/text()";
XPathExpression expr = xpath.compile(expression);
NodeList nodes = (NodeList) expr.evaluate(doc, XPathConstants.NODESET);
sqlQuery = nodes.item(0).getNodeValue();
System.out.println("Query: " + sqlQuery);
}
catch(Exception ex) {
System.out.println("......Error while parsing.....");
}
return sqlQuery;
}
}

How to apply this whole XML-SQL concept in a J2EE application?


Step-I: Load the XMLParser (ABCSQLLoader) while the J2EE web application starts in the server through a servlet’s init method.

public class PopulateApplicationDataServlet extends HttpServlet {

public void init(ServletConfig config) throws ServletException {
XMLParser parsing = new XMLParser();
parsing.getXmlParser();

}
}

Step-II:
Once the xml parser parse’s the XML file, give the module name and query name as parameters through normal java method call from the application DAO classes.

objResultSet = objStatement.executeQuery(ABCSQLLoader.getSQLQuery(
"REQUEST", " PAYMENTTERMS_QUERY "));