All Packages  Class Hierarchy  This Package  Previous  Next  Index  

Class swosnick.database.servlets.SQLGatewayServlet

java.lang.Object
    |
    +----javax.servlet.GenericServlet
            |
            +----javax.servlet.http.HttpServlet
                    |
                    +----swosnick.database.servlets.SQLGatewayServlet

public class SQLGatewayServlet
extends HttpServlet

This is a DEMONSTRATION of a Java servlet which lets you view and manipulate a relational database using SQL via JDBC with any datasource configured on the Web server (including an ODBC datasource). One may use a JDBC driver such as those that ship with DB2 Universal Database 5.0 or the JDBC-ODBC Bridge driver. This servlet supports SQL queries, updates, deletes and inserts. It is not meant to imply that this is the ONLY way, or even the best way, to use servlets with JDBC. It merely attempts to demonstrate one possible way to use Java server-side programs with JDBC that execute on the Web server. It demonstrates the ability to easily connect to a legacy back-end database and move that information swifty over the internet or intranet. With very little modifications, this technology could be adapted to a a variety of applications where remote users send queries, modify and append records over the network to a legacy datasource through any number of intervening tiers or application servers. This is an important aspect of the IBM Network Computing Framework and the e-business strategy. Servlets, then, are an important server-side technology for any form of e-business.

The SQLGatewayServlet uses only dynamically created HTML. That is, even the first logon form is generated by the Webserver and additional forms could, of course, be custom-tailored to the particular requests that the server received. This has the potential to give the user a high degree of interactivity, yet preserve the logic on the server. All HTML forms displayed in the browser as a result of this servlet are generated by the servlet itself. This is possible, in a large part, and unlike traditional CGI programs, because the servlet is able to keep and maintain state information from request to request.

Because servlets are written in Java, they can be used on any Java-capable platform. Servlets also provides security reassurances of Java in a networked environment. This way, proprietary algorithms and other intellectual capital are built into a servlet and the code never passes beyond the boundaries of the server -- only the results that it produces does. Since the code is never passed to the client, intellectual capital cannot be simply saved and disassembled.

A feature of this servlet is that if the servlet throws an SQLException, information about that exception is HTTP-streamed to the browser for debugging purposes so the user can get meaningful feedback on the SQL-JDBC methods that are being executed improperly. For Java exceptions other than SQLException a Java console is required to accept calls to standard err.

All pure JDBC drivers should be supported including the JDBC-ODBC Bridge with the JavaSoft JDK (1.1x). The client HTML form has been constructed to allow for ODBC and DB2, but as new PURE JDBC drivers become available by various database vendors the code can be revised to include those new drivers.

PREREQUISITES:

Version:
1.0.0 01/15/98
Author:
Sheldon Bradley Wosnick IBM Toronto Lab Toronto Ontario, Canada February 1998 email: swosnick@ca.ibm.com

Variable Index

 o backgroundColor
Background color constant for control of background.
 o connection
The database connection.
 o driver
The JDBC Driver.
 o INSERT_UPDATE_DELETE
Constant representing an update, delete or insert operation and will use executeUpdate.
 o isDEBUGON
DEBUG flag - when true, tracing will be enabled, when false, tracing will be disabled and in addition, the compiler is smart enough to remove blocks it knows can never be reached, so the debugging code in effect is never compiled into the executable, with no overhead.
 o isExecuteUpdateSuccess
On an executeUpdate operation was it successful - returned rows? Used internally only.
 o isLoggedOn
If the user is logged on in appropriately, set to true, else remain false.
 o isSQLExceptionOccurred
Did an exception occur on an SQL action? Used internally only.
 o JDBCDrivers
The list of supported JDBC drivers which will appear in the logon form as a choice.
 o lastSQL
Track the last SQL query or command so that the user need not re-enter it.
 o logonFormHeading
The heading or description for the logon form.
 o logonFormTitle
The title which is sent to the browser for the initial logon form.
 o NO_OPERATION
Constant representing neither a query or an update, indicating an error retrieving the type of operation.
 o outstream
The output stream used for HTTP output to create dynamic HTML page.
 o password
The optional password.
 o properties
Properties object used to contain username and password to make the database connection.
 o request
The request sent to the servlet.
 o response
The response prepared by the servlet.
 o resultSet
Result set for a given query.
 o SELECT
Constant used to represent the SELECT operation which will use executeQuery.
 o servletName
The name of the servlet used in the logon form HTML to call back the servlet with the servlet tag.
 o sql
The SQL string to use for query or update.
 o statement
The Statement used for SQL query or update.
 o tableHeadingBackgroundColor
Table heading background color constant.
 o tableHeadingForeroundColor
Table heading background color constant.
 o url
The JDBC-style URL with protocol and datasource name.
 o userName
The user name or ID.

Constructor Index

 o SQLGatewayServlet()

Method Index

 o checkForWarning(SQLWarning)
This method was created by a SmartGuide.
 o connectionInfo()
This method was created by a SmartGuide.
 o connectToDatabase()
This method was created by a SmartGuide.
 o destroy()
This method was created by a SmartGuide.
 o executeTheQuery(String)
This method was created by a SmartGuide.
 o executeTheUpdate(String)
This method was created by a SmartGuide.
 o getRequestParameters()
This method was created by a SmartGuide.
 o getServletInfo()
This method was created by a SmartGuide.
 o getSQLOperation(String)
This method was created by a SmartGuide.
 o init(ServletConfig)
This method was created by a SmartGuide.
 o outResultSet()
This method was created by a SmartGuide.
 o outUpdateStatus(boolean)
This method was created by a SmartGuide.
 o processSQLException(SQLException)
This method was created by a SmartGuide.
 o sendHTMLLogonForm()
This method was created by a SmartGuide.
 o sendSQLForm()
This method was created by a SmartGuide.
 o service(HttpServletRequest, HttpServletResponse)
This method was created by a SmartGuide.
 o trace(String)
This method was created by a SmartGuide.

Variables

 o userName
private String userName
The user name or ID. Supplied by the user.

 o password
private String password
The optional password. Supplied by the user or set to an empty string.

 o url
private String url
The JDBC-style URL with protocol and datasource name. Supplied by the user.

 o driver
private String driver
The JDBC Driver. Supplied by the user from a choice of available drivers.

 o sql
private String sql
The SQL string to use for query or update. Supplied by the user.

 o lastSQL
private String lastSQL
Track the last SQL query or command so that the user need not re-enter it. Used internally only

 o connection
private Connection connection
The database connection. Used internally only.

 o resultSet
private ResultSet resultSet
Result set for a given query. Used internally only.

 o statement
private Statement statement
The Statement used for SQL query or update. Used internally only.

 o isSQLExceptionOccurred
private boolean isSQLExceptionOccurred
Did an exception occur on an SQL action? Used internally only.

 o isExecuteUpdateSuccess
private boolean isExecuteUpdateSuccess
On an executeUpdate operation was it successful - returned rows? Used internally only.

 o outstream
private ServletOutputStream outstream
The output stream used for HTTP output to create dynamic HTML page. Used internally only.

 o request
private HttpServletRequest request
The request sent to the servlet. Used internally only.

 o response
private HttpServletResponse response
The response prepared by the servlet. Used internally only.

 o logonFormTitle
private final String logonFormTitle
The title which is sent to the browser for the initial logon form.

 o logonFormHeading
private final String logonFormHeading
The heading or description for the logon form.

 o servletName
private final String servletName
The name of the servlet used in the logon form HTML to call back the servlet with the servlet tag.

 o JDBCDrivers
private final String[] JDBCDrivers
The list of supported JDBC drivers which will appear in the logon form as a choice.

 o properties
private Properties properties
Properties object used to contain username and password to make the database connection. Used internally only.

 o isDEBUGON
private boolean isDEBUGON
DEBUG flag - when true, tracing will be enabled, when false, tracing will be disabled and in addition, the compiler is smart enough to remove blocks it knows can never be reached, so the debugging code in effect is never compiled into the executable, with no overhead. Used internally only.

 o NO_OPERATION
private final int NO_OPERATION
Constant representing neither a query or an update, indicating an error retrieving the type of operation. Used internally only.

 o SELECT
private final int SELECT
Constant used to represent the SELECT operation which will use executeQuery. Used internally only.

 o INSERT_UPDATE_DELETE
private final int INSERT_UPDATE_DELETE
Constant representing an update, delete or insert operation and will use executeUpdate. Used internally only.

 o isLoggedOn
private boolean isLoggedOn
If the user is logged on in appropriately, set to true, else remain false. Used internally only.

 o backgroundColor
private final String backgroundColor
Background color constant for control of background. Change as desired.

 o tableHeadingBackgroundColor
private final String tableHeadingBackgroundColor
Table heading background color constant. Change as desired.

 o tableHeadingForeroundColor
private final String tableHeadingForeroundColor
Table heading background color constant. Change as desired.

Constructors

 o SQLGatewayServlet
public SQLGatewayServlet()

Methods

 o checkForWarning
private static boolean checkForWarning(SQLWarning anSQLwarning) throws SQLException
This method was created by a SmartGuide. Checks for and displays SQL warnings. Returns true if a warning existed.

Parameters:
anSQLwarning - SQLWarning
Returns:
boolean indicating existence of an SQL warning.
Throws: SQLException
 o connectionInfo
private void connectionInfo()
This method was created by a SmartGuide. This method will provide information about the driver and the connection useful for database administrators or for trouble shooting. It will publish a lot of the JDBC API methods.

 o connectToDatabase
private void connectToDatabase()
This method was created by a SmartGuide. Perform the physical connection to the database.

 o destroy
public void destroy()
This method was created by a SmartGuide. Destroys the servlet upon server shutdown, frees resources like database connections.

Overrides:
destroy in class GenericServlet
 o executeTheQuery
private void executeTheQuery(String anSQLQuery)
This method was created by a SmartGuide. Executes a user-defined SQL query on a table.

Parameters:
anSQLQuery - String
 o executeTheUpdate
private void executeTheUpdate(String anUpdateCommand)
This method was created by a SmartGuide. Executes a User-defined SQL update command on a table.

Parameters:
anUpdateCommand - String
 o getRequestParameters
private void getRequestParameters()
This method was created by a SmartGuide. This method will gather the properties passed into the servlet as the request.

 o getServletInfo
public String getServletInfo()
This method was created by a SmartGuide. Returns information about this servlet.

Returns:
String about the servlet.
Overrides:
getServletInfo in class GenericServlet
 o getSQLOperation
private int getSQLOperation(String anSQLString)
This method was created by a SmartGuide.

Parameters:
String - sql
Returns:
int
 o init
public void init(ServletConfig aServletConfig)
This method was created by a SmartGuide. Initialization of the servlet occurs here on an one-time basis.

Parameters:
aServletConfig - ServletConfig
Overrides:
init in class GenericServlet
 o outResultSet
private void outResultSet() throws SQLException
This method was created by a SmartGuide. Prepares http output to create new html page with results of query after a user-entered SQL query or update command.

 o outUpdateStatus
private void outUpdateStatus(boolean isSuccess)
This method was created by a SmartGuide. Provide the user with feedback that the operation was successful along with an SQL form.

Parameters:
success - boolean
 o processSQLException
private void processSQLException(SQLException anSQLException)
This method was created by a SmartGuide. Process the SQL exception by extracting relevant information from it. Display to the browser.

Parameters:
anSQLException - SQLException
 o sendHTMLLogonForm
private void sendHTMLLogonForm()
This method was created by a SmartGuide. This method sends the first logon form to the user to enter various logon information to connect.

 o sendSQLForm
private void sendSQLForm()
This method was created by a SmartGuide. This method will send an SQL editable area as HTML to the user to further submit SQL commands.

 o service
public void service(HttpServletRequest aRequest,
                    HttpServletResponse aResponse) throws ServletException, IOException
This method was created by a SmartGuide. Required method for a servlet, overridden to perform specialized JDBC work.

Parameters:
aRequest - HttpServletRequest, aResponse HttpServletResponse
Throws: ServletException,
IOException
Overrides:
service in class HttpServlet
 o trace
public void trace(String aTraceString)
This method was created by a SmartGuide. This method is used to output trace messages to standard output.

Parameters:
aTraceString - String

All Packages  Class Hierarchy  This Package  Previous  Next  Index