FileDocCategorySizeDatePackage
RawSQLServlet.javaAPI DocExample2924Sat Apr 28 20:56:06 BST 2001None

RawSQLServlet

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

Fields Summary
public static final String
PROPS_FILE
protected String
DRIVER
The name of the JDBC Driver
protected Connection
conn
The DB connection object
protected Statement
stmt
The JDBC statement object
Constructors Summary
Methods Summary
public voiddestroy()

		try {
			conn.close();	// All done with that DB connection
		} catch (SQLException ex) {
			log(getClass() + ": destroy: " + ex);
		}
public voiddoPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response)
Do the SQL query


		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!
		if (!query.toLowerCase().startsWith("select")) {
			throw new SecurityException("You can only select data");
		}

		try {	// SQL
			out.println("<br>Your query: <b>" + query + "</b>");
			ResultSet rs = stmt.executeQuery(query);

			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 voidinit()
Initialize the servlet.


	    
	     
		try {
			// Get a Properties to load from
			FileProperties fp = new FileProperties(PROPS_FILE);

			// Load the database driver
			DRIVER = fp.getProperty("driver");
			Class.forName(DRIVER);

			// Get the connection
			log(getClass() + ": Getting Connection");
			Connection conn = DriverManager.getConnection (
				fp.getProperty("dburl"),
				fp.getProperty("user"),
				fp.getProperty("password"));


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