RawSQLServlet.java Source code

Introduction

Here is the source code for RawSQLServlet.java

Source

/*
 * Copyright (c) Ian F. Darwin, http://www.darwinsys.com/, 1996-2002.
 * All rights reserved. Software written by Ian F. Darwin and others.
 * $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, 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 and the following disclaimer in the
 *    documentation and/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS 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 AUTHOR OR 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.
 * 
 * Java, the Duke mascot, and all variants of Sun's Java "steaming coffee
 * cup" logo are trademarks of Sun Microsystems. Sun's, and James Gosling's,
 * pioneering role in inventing and promulgating (and standardizing) the Java 
 * language and environment is gratefully acknowledged.
 * 
 * The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
 * inventing predecessor languages C and C++ is also gratefully acknowledged.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/** Process a raw SQL query; use ResultSetMetaData to format it.
 */
public class RawSQLServlet extends HttpServlet {

    /** The application-wide servlet context */
    protected ServletContext application;

    /** The DB connection object */
    protected Connection conn;

    /** The JDBC statement object */
    protected Statement stmt;

    /** Initialize the servlet. */
    public void init() throws ServletException {
        application = getServletConfig().getServletContext();
        String driver = null;
        try {

            driver = application.getInitParameter("db.driver");
            Class.forName(driver);

            // Get the connection
            log(getClass() + ": Getting Connection");
            Connection conn = DriverManager.getConnection(application.getInitParameter("db.url"),
                    application.getInitParameter("db.user"), application.getInitParameter("db.password"));

            log(getClass() + ": Creating Statement");
            stmt = conn.createStatement();
        } catch (ClassNotFoundException ex) {
            log(getClass() + ": init: Could not load SQL driver " + driver);
        } catch (SQLException ex) {
            log(getClass() + ": init: SQL Error: " + ex);
        }
    }

    /** Do the SQL query */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        String query = request.getParameter("sql");

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        if (query == null) {
            out.println("<b>Error: malformed query, contact administrator</b>");
            return;
        }

        // NB MUST also check for admin privs before proceding!

        try { // SQL
            out.println("<p>Your query: <b>" + query + "</b></p>");
            stmt.execute(query);
            ResultSet rs = stmt.getResultSet();
            if (rs == null) {
                // print updatecount
                out.println("<p>Result: updateCount = <b>" + stmt.getUpdateCount() + "</p>");
            } else {
                // process resultset

                out.println("<br>Your response:");

                ResultSetMetaData md = rs.getMetaData();
                int count = md.getColumnCount();
                out.println("<table border=1>");
                out.print("<tr>");
                for (int i = 1; i <= count; i++) {
                    out.print("<th>");
                    out.print(md.getColumnName(i));
                }
                out.println("</tr>");
                while (rs.next()) {
                    out.print("<tr>");
                    for (int i = 1; i <= count; i++) {
                        out.print("<td>");
                        out.print(rs.getString(i));
                    }
                    out.println("</tr>");
                }
            }
            out.println("</table>");
            // rs.close();
        } catch (SQLException ex) {
            out.print("<B>" + getClass() + ": SQL Error:</B>\n" + ex);
            out.print("<pre>");
            ex.printStackTrace(out);
            out.print("</pre>");
        }
    }

    public void destroy() {
        try {
            conn.close(); // All done with that DB connection
        } catch (SQLException ex) {
            log(getClass() + ": destroy: " + ex);
        }
    }
}