import java.sql.*; import java.io.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; /* ---------------------------------------------------------------------- SimpleJDBCTool.java Developed with GNU Emacs 20.2 and Sun JDK 1.1.5 on Solaris 2.6 Written by Jamie Flournoy Copyright (C) 1998 by WestLake Solutions This program is free software; you can redistribute it and/or modify it under the terms of the GNU 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 General Public License at for more details. The standard version of this program lives at ---------------------------------------------------------------------- This is a Java Servlet - see this document for more info about Servlets: This is a simple tool that lets you execute arbitrary SQL statements provided you have a suitable JDBC driver in this servlet's classpath AND provided you know the JDBC connect string that you want to use. It's a bit more than a hello world; if you've used the simple command line ISQL/DBAccess/SQL*Plus tools with major databases, or the GUI tools with their simple raw SQL tools, this gives similar functionality. However, this tool lets you open multiple browser windows to preserve the output of several queries, and query results are displayed in HTML table format for easier reading. This file is the whole tool, but you still DO need a separate JDBC driver for your database so that this thing will have a way to connect to it. ---------------------------------------------------------------------- To Do's for future versions: Use session management (or hidden fields) to remember the last few SQL statements, presenting them in a popup or something, for user convenience. Separate the userid and password from the rest of the JDBC connect string, and use a client cookie to store it and the driver name for user convenience. Then all the user has to do is remember the userid and password. Time the query, the way the Win32 Perl version does, so the user can see how long the query actually took vs. how long the browser took to render the response page. ---------------------------------------------------------------------- */ public class SimpleJDBCTool extends HttpServlet { private HttpSession userSession; private ServletOutputStream out; private HttpServletRequest req; private HttpServletResponse res; public void doGet (HttpServletRequest request, HttpServletResponse response) { try { out = response.getOutputStream(); } catch (IOException io_e) { // we're dead in the water if this happens // better servlets would do something but this // is a demo so we will silently fail :( } req = request; res = response; printHeader(); printStartHTML(); printStartForm(); printBannerAndButtons(); printStatement(); printConnectionInfo(); printEndForm(); if ( ! param("statement").equals("") ) { doQuery(); } printEndHTML(); } public void doPost (HttpServletRequest request, HttpServletResponse response) { doGet(request, response); } private void printHeader() { res.setContentType("text/html"); } private void printStartHTML() { print("\n" + "\n" + "Simple JDBC Tool\n" + "\n" + "\n"); } private void printStartForm() { // this could perhaps be more clever about asking for its current name print("
\n"); } private void printBannerAndButtons() { print("

SQL Tool

\n" + "
" + "
\n"); } private void printStatement() { print("

SQL Statement:

\n
\n"); String statement = param("statement"); print("\n"); } private void printConnectionInfo() { print("
\n

Connection Info:

\n
\n" + "\n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + "
JDBC Driver Class:
JDBC Connection String:
\n", true); } private void printEndForm() { print("
\n"); } private void print(String str) { print(str, false); } private void print(String str, boolean flush) { try { out.print(str); if (flush) { out.flush(); } } catch (IOException io_ex) { System.err.print(io_ex + getExceptionDetails(io_ex)); } } private String param(String parameter) { String str = ""; String[] values = req.getParameterValues(parameter); if (values != null) { if (values[0] != null) { str = values[0]; } } return str; } private void printEndHTML() { print("\n\n"); } private void doQuery() { try { print("

Query Response:

\n
\n" + "\n"); // try to load the JDBC driver specified by the user // if this fails it'll throw a ClassNotFoundException String driver = param("jdbc_driver"); Class.forName( driver ); // OK so it's a real class, let's try to open a DB // connection with the driver we just loaded. The connect // string is specified by the user. Connection jdbc_conn = DriverManager.getConnection( param("jdbc_connect_string") ); try { Statement stmt=jdbc_conn.createStatement(); String sql = param("statement"); // hack to decide if this is a select or insert/delete/update if ( sql.toLowerCase().startsWith("select") ) { ResultSet rs = stmt.executeQuery(sql); // display the result set printResults(rs); } else { int rows_affected = stmt.executeUpdate(sql); printRowCount(rows_affected); } } catch (SQLException stmt_ex) { print("\n" + "\n"); } // disconnect from the DB, we're all done. jdbc_conn.close(); } catch (Exception conn_ex) { print("\n" + "\n"); } print("
Statement Resulted in an Error
");
	      print( getExceptionDetails(stmt_ex) );
	      print("
Statement Resulted in an Error
");
	  print( getExceptionDetails(conn_ex) );
	  print("
\n
\n");
    }

  private void printRowCount(int rows)
    {
      print("
"
	    + String.valueOf(rows)
	    + " rows affected.
\n");
    }

  private void printResults(ResultSet rs) throws SQLException
    {
      // print header row first
      ResultSetMetaData rsmd = rs.getMetaData();
      int cols = rsmd.getColumnCount();
      int i;
      
      print("Row");

      for(i = 1; i <= cols; i++)
	{
	  print( rsmd.getColumnName(i) + "\n" );
	}
      print("\n");

      int rownum = 0;
      while ( rs.next() )
	{
	  rownum++;
	  print(""
		+ String.valueOf(rownum)
		+ "\n");
	  
	  for(i = 1; i <= cols; i++)
	    {
	      print(""
		    + rs.getString(i)
		    + "\n");
	    }

	  print("\n");
	}
      
    }

  private String getExceptionDetails(Exception e)
    {
      // there's probably a much cleaner way to do this
      // if you know it then by all means tell me: jamie@westlake.com

      ByteArrayOutputStream baos=new ByteArrayOutputStream();
      PrintWriter pw=new PrintWriter(baos);
      e.printStackTrace(pw);

      return e.toString() + baos.toString();
    }

}