The data melting pot

/*-- 

 Copyright (C) 2000 Brett McLaughlin. All rights reserved.
 
 Redistribution and use in source and binary forms, with or without modifica-
 tion, are permitted provided that the following conditions are met:
 
 1. Redistributions of source code must retain the above copyright notice,
    this list of conditions, and the following disclaimer.
 
 2. Redistributions in binary form must reproduce the above copyright notice,
    this list of conditions, the disclaimer that follows these conditions,
    and/or other materials provided with the distribution.
 
 3. Products derived from this software may not be called "Java and XML", nor may
    "Java and XML" appear in their name, without prior written permission from
    Brett McLaughlin (brett@newInstance.com).
 
 THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES,
 INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
 FITNESS  FOR A PARTICULAR  PURPOSE ARE  DISCLAIMED.  IN NO  EVENT SHALL  THE
 JDOM PROJECT  OR ITS CONTRIBUTORS  BE LIABLE FOR  ANY DIRECT, INDIRECT, 
 INCIDENTAL, SPECIAL,  EXEMPLARY, OR CONSEQUENTIAL  DAMAGES (INCLUDING, BUT 
 NOT LIMITED TO, PROCUREMENT  OF SUBSTITUTE GOODS OR SERVICES; LOSS
 OF USE, DATA, OR  PROFITS; OR BUSINESS  INTERRUPTION)  HOWEVER CAUSED AND ON
 ANY  THEORY OF LIABILITY,  WHETHER  IN CONTRACT,  STRICT LIABILITY,  OR TORT
 (INCLUDING  NEGLIGENCE OR  OTHERWISE) ARISING IN  ANY WAY OUT OF THE  USE OF
 THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 
 This software was originally created by Brett McLaughlin <brett@newInstance.com>. 
 For more  information on "Java and XML", please see <http://www.oreilly.com/catalog/javaxml/>
 or <http://www.newInstance.com>.
 
 */
package com.oreilly.xml;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringBufferInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.*;
import javax.servlet.http.*;

/**
 * <p>
 *  <b><code>XmlSqlServlet</code></b> will take an XML request
 *    via HTTP, convert that request to a SQL JDBC call, and return
 *    the results of that SQL query in an XML format to the client.
 *    The servlet can transform or otherwise manipulate the results
 *    behind the scenes, as long as it returns valid XML to the
 *    client. It also is not guaranteed to connect to a database,
 *    but could also connect to, for example, an LDAP server.
 * </p>
 *
 * @author <a href="brett@newInstance.com">Brett McLaughlin</a>
 * @version 1.0
 */
public class XmlSqlServlet extends HttpServlet {

    /** Default JDBC Driver */
    private static final String JDBC_DRIVER =
        "oracle.jdbc.driver.OracleDriver";

    /** Default connect String */
    private static final String JDBC_CONNECT_STRING =
        "jdbc:oracle:thin:@hostname:1521:SID";

    /** Default username */
    private static final String JDBC_USERNAME =
        "username";

    /** Default password */
    private static final String JDBC_PASSWORD =
        "password";

    /** Database connection */
    private static Connection conn;

    /** Error message (if any) */
    private static String errorMessage;

    /** Shared Builder for XMl 
 SQL conversion */
    private XmlSqlBuilder builder;

    /**
     * <p>
     *  This will handle loading an initial database connection
     *    for all requests to use for database connectivity.
     *    In a production application, this might actually create
     *    a pool of connections instead.
     * </p>
     *
     * @param config <code>ServletConfig</code> with servlet information.
     * @throws <code>ServletException</code> - when a connection to the database
     *         cannot be opened.
     */
    public void init(ServletConfig config)
        throws ServletException {

        super.init(config);

        errorMessage = null;
        try {
            Class.forName(JDBC_DRIVER);

            // Load Database Connection
            conn = DriverManager.getConnection(
                JDBC_CONNECT_STRING, JDBC_USERNAME, JDBC_PASSWORD);
        } catch (Exception e) {
            errorMessage = e.getMessage();
        }
    }

    public void service(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {

        // Create a builder instance
        builder = new XmlSqlBuilder();

        PrintWriter out = res.getWriter();
        if (errorMessage != null) {
            res.setContentType("text/plain");
            out.println("Error in initialization occurred: " + errorMessage);
            return;
        }

        try {
            // Get the SQL Query from the XML input
            String sqlQuery = getQuery(req);

            // Output results as XML
            out.println(getXmlResultSet(sqlQuery));
        } catch (Exception e) {
            out.println("Error in execution occurred: " + errorMessage);
        } finally {
            out.close();
        }
    }

    /**
     * <p>
     *  This will extract the SQL query from the XML input to the
     *    servlet.
     * </p>
     *
     * @return <code>String</code> - SQL query.
     */
    private String getQuery(HttpServletRequest req) throws IOException {
        return builder.getQuery(
            new StringBufferInputStream(req.getParameter("QUERY")));
    }

    /**
     * <p>
     *  This will return the result set from JDBC in XML
     *    form given the SQL query. 
     * </p>
     *
     * @param query <code>String</code> query to execute.
     * @return <code>String</code> XML results in text format.
     */
    private String getXmlResultSet(String query) {        
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);

            return builder.buildXML(rs);

        } catch (SQLException e) {
            StringBuffer results = new StringBuffer();

            results.append("<?xml version=\"1.0\"?>\n\n")
                   .append("<resultSet>\n")
                   .append("  <error type=\"sql\">")
                   .append(e.getMessage())
                   .append("</error>\n")
                   .append("</resultSet>");

            return results.toString();
        }        
    }

    /**
     * <p>
     *  This will close the database connection, if it is viable.
     * </p>
     */
    public void destroy() {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception ignored) {
                // Do nothing
            }
        }
    }

}