Java tutorial
/* * eXist SQL Module Extension ExecuteFunction * Copyright (C) 2006-10 Adam Retter <adam@exist-db.org> * www.adamretter.co.uk * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. * * $Id$ */ package org.exist.xquery.modules.sql; import org.apache.log4j.Logger; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.exist.Namespaces; import org.exist.dom.QName; import org.apache.commons.io.output.ByteArrayOutputStream; import org.exist.memtree.MemTreeBuilder; import org.exist.xquery.BasicFunction; import org.exist.xquery.Cardinality; import org.exist.xquery.FunctionSignature; import org.exist.xquery.XPathException; import org.exist.xquery.XQueryContext; import org.exist.xquery.value.BooleanValue; import org.exist.xquery.value.FunctionParameterSequenceType; import org.exist.xquery.value.FunctionReturnSequenceType; import org.exist.xquery.value.IntegerValue; import org.exist.xquery.value.NodeValue; import org.exist.xquery.value.Sequence; import org.exist.xquery.value.SequenceType; import org.exist.xquery.value.Type; import java.io.PrintStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.SQLRecoverableException; import java.sql.SQLXML; import java.sql.Statement; import java.sql.Timestamp; import java.sql.Types; import javax.xml.parsers.SAXParser; import javax.xml.parsers.SAXParserFactory; import org.exist.memtree.AppendingSAXAdapter; import org.exist.memtree.ReferenceNode; import org.exist.memtree.SAXAdapter; import org.exist.xquery.value.DateTimeValue; import org.xml.sax.InputSource; import org.xml.sax.XMLReader; /** * eXist SQL Module Extension ExecuteFunction. * * <p>Execute a SQL statement against a SQL capable Database</p> * * @author Adam Retter <adam@exist-db.org> * @version 1.13 * @see org.exist.xquery.BasicFunction#BasicFunction(org.exist.xquery.XQueryContext, org.exist.xquery.FunctionSignature) * @serial 2009-01-25 */ public class ExecuteFunction extends BasicFunction { private static final Logger LOG = Logger.getLogger(ExecuteFunction.class); public final static FunctionSignature[] signatures = { new FunctionSignature( new QName("execute", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "Executes a SQL statement against a SQL db using the connection indicated by the connection handle.", new SequenceType[] { new FunctionParameterSequenceType("connection-handle", Type.LONG, Cardinality.EXACTLY_ONE, "The connection handle"), new FunctionParameterSequenceType("sql-statement", Type.STRING, Cardinality.EXACTLY_ONE, "The SQL statement"), new FunctionParameterSequenceType("make-node-from-column-name", Type.BOOLEAN, Cardinality.EXACTLY_ONE, "The flag that indicates whether the xml nodes should be formed from the column names (in this mode a space in a Column Name will be replaced by an underscore!)") }, new FunctionReturnSequenceType(Type.NODE, Cardinality.ZERO_OR_ONE, "the results")), new FunctionSignature(new QName("execute", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "Executes a prepared SQL statement against a SQL db.", new SequenceType[] { new FunctionParameterSequenceType("connection-handle", Type.LONG, Cardinality.EXACTLY_ONE, "The connection handle"), new FunctionParameterSequenceType("statement-handle", Type.INTEGER, Cardinality.EXACTLY_ONE, "The prepared statement handle"), new FunctionParameterSequenceType("parameters", Type.ELEMENT, Cardinality.ZERO_OR_ONE, "Parameters for the prepared statement. e.g. <sql:parameters><sql:param sql:type=\"varchar\">value</sql:param></sql:parameters>"), new FunctionParameterSequenceType("make-node-from-column-name", Type.BOOLEAN, Cardinality.EXACTLY_ONE, "The flag that indicates whether the xml nodes should be formed from the column names (in this mode a space in a Column Name will be replaced by an underscore!)") }, new FunctionReturnSequenceType(Type.NODE, Cardinality.ZERO_OR_ONE, "the results")) }; private final static String PARAMETERS_ELEMENT_NAME = "parameters"; private final static String PARAM_ELEMENT_NAME = "param"; private final static String TYPE_ATTRIBUTE_NAME = "type"; /** * ExecuteFunction Constructor. * * @param context The Context of the calling XQuery * @param signature DOCUMENT ME! */ public ExecuteFunction(XQueryContext context, FunctionSignature signature) { super(context, signature); } /** * evaluate the call to the XQuery execute() function, it is really the main entry point of this class. * * @param args arguments from the execute() function call * @param contextSequence the Context Sequence to operate on (not used here internally!) * * @return A node representing the SQL result set * * @throws XPathException DOCUMENT ME! * * @see org.exist.xquery.BasicFunction#eval(org.exist.xquery.value.Sequence[], org.exist.xquery.value.Sequence) */ @Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { // was a connection and SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); } // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection con = SQLModule.retrieveConnection(context, connectionUID); if (con == null) { return (Sequence.EMPTY_SEQUENCE); } boolean preparedStmt = false; //setup the SQL statement String sql = null; Statement stmt = null; boolean executeResult = false; ResultSet rs = null; try { boolean makeNodeFromColumnName = false; MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; //SQL or PreparedStatement? if (args.length == 3) { // get the SQL statement sql = args[1].getStringValue(); stmt = con.createStatement(); makeNodeFromColumnName = ((BooleanValue) args[2].itemAt(0)).effectiveBooleanValue(); //execute the statement executeResult = stmt.execute(sql); } else if (args.length == 4) { preparedStmt = true; //get the prepared statement long statementUID = ((IntegerValue) args[1].itemAt(0)).getLong(); PreparedStatementWithSQL stmtWithSQL = SQLModule.retrievePreparedStatement(context, statementUID); sql = stmtWithSQL.getSql(); stmt = stmtWithSQL.getStmt(); makeNodeFromColumnName = ((BooleanValue) args[3].itemAt(0)).effectiveBooleanValue(); if (!args[2].isEmpty()) { setParametersOnPreparedStatement(stmt, (Element) args[2].itemAt(0)); } //execute the prepared statement executeResult = ((PreparedStatement) stmt).execute(); } else { //TODO throw exception } // DW: stmt can be null ? // execute the query statement if (executeResult) { /* SQL Query returned results */ // iterate through the result set building an XML document rs = stmt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); int iColumns = rsmd.getColumnCount(); builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); while (rs.next()) { builder.startElement(new QName("row", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(rs.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colElement = "field"; if (makeNodeFromColumnName && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement(new QName(colElement, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); if (!makeNodeFromColumnName || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName(TYPE_ATTRIBUTE_NAME, Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); //get the content if (rsmd.getColumnType(i + 1) == Types.SQLXML) { //parse sqlxml value try { final SQLXML sqlXml = rs.getSQLXML(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { SAXParserFactory factory = SAXParserFactory.newInstance(); factory.setNamespaceAware(true); InputSource src = new InputSource(sqlXml.getCharacterStream()); SAXParser parser = factory.newSAXParser(); XMLReader xr = parser.getXMLReader(); SAXAdapter adapter = new AppendingSAXAdapter(builder); xr.setContentHandler(adapter); xr.setProperty(Namespaces.SAX_LEXICAL_HANDLER, adapter); xr.parse(src); } } catch (Exception e) { throw new XPathException( "Could not parse column of type SQLXML: " + e.getMessage(), e); } } else { //otherwise assume string value final String colValue = rs.getString(i + 1); if (rs.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true"); } else { if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } } } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); } else { /* SQL Query performed updates */ builder.startDocument(); builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("updateCount", null, null), String.valueOf(stmt.getUpdateCount())); builder.endElement(); } // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } catch (SQLException sqle) { LOG.error("sql:execute() Caught SQLException \"" + sqle.getMessage() + "\" for SQL: \"" + sql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(sqle.getSQLState()); builder.endElement(); builder.startElement(new QName("message", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); String state = sqle.getMessage(); if (state != null) { builder.characters(state); } builder.endElement(); builder.startElement(new QName("stack-trace", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("sql", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(sql)); builder.endElement(); if (stmt instanceof PreparedStatement) { Element parametersElement = (Element) args[2].itemAt(0); if (parametersElement.getNamespaceURI().equals(SQLModule.NAMESPACE_URI) && parametersElement.getLocalName().equals(PARAMETERS_ELEMENT_NAME)) { NodeList paramElements = parametersElement.getElementsByTagNameNS(SQLModule.NAMESPACE_URI, PARAM_ELEMENT_NAME); builder.startElement( new QName(PARAMETERS_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); for (int i = 0; i < paramElements.getLength(); i++) { Element param = ((Element) paramElements.item(i)); String value = param.getFirstChild().getNodeValue(); String type = param.getAttributeNS(SQLModule.NAMESPACE_URI, TYPE_ATTRIBUTE_NAME); builder.startElement( new QName(PARAM_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute( new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), type); builder.characters(SQLUtils.escapeXmlText(value)); builder.endElement(); } builder.endElement(); } } builder.startElement(new QName("xquery", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(getLine())); builder.addAttribute(new QName("column", null, null), String.valueOf(getColumn())); builder.endElement(); builder.endElement(); builder.endDocument(); return ((NodeValue) builder.getDocument().getDocumentElement()); } finally { // close any record set or statement if (rs != null) { try { rs.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } rs = null; } if (!preparedStmt && stmt != null) { try { stmt.close(); } catch (SQLException se) { LOG.warn("Unable to cleanup JDBC results", se); } stmt = null; } } } private void setParametersOnPreparedStatement(Statement stmt, Element parametersElement) throws SQLException, XPathException { if (parametersElement.getNamespaceURI().equals(SQLModule.NAMESPACE_URI) && parametersElement.getLocalName().equals(PARAMETERS_ELEMENT_NAME)) { NodeList paramElements = parametersElement.getElementsByTagNameNS(SQLModule.NAMESPACE_URI, PARAM_ELEMENT_NAME); for (int i = 0; i < paramElements.getLength(); i++) { Element param = ((Element) paramElements.item(i)); Node child = param.getFirstChild(); // Prevent NPE if (child != null) { if (child instanceof ReferenceNode) { child = ((ReferenceNode) child).getReference().getNode(); } final String value = child.getNodeValue(); final String type = param.getAttributeNS(SQLModule.NAMESPACE_URI, TYPE_ATTRIBUTE_NAME); final int sqlType = SQLUtils.sqlTypeFromString(type); if (sqlType == Types.TIMESTAMP) { final DateTimeValue dv = new DateTimeValue(value); final Timestamp timestampValue = new Timestamp(dv.getDate().getTime()); ((PreparedStatement) stmt).setTimestamp(i + 1, timestampValue); } else { ((PreparedStatement) stmt).setObject(i + 1, value, sqlType); } } } } } }