Process a raw SQL query; use ResultSetMetaData to format it
/*
* 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);
}
}
}
Related examples in the same category
