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:
- A web browser capable of displaying tables. Need not support JDK 1.1x.
This is a positive feature - since all logic is operating server-side,
the browser need to be capable of displaying HTML, that's all.
- A working to advanced understanding of the SQL language. Knowing how to
build a simple query, update, insert or delete command at a minimum.
- Either the ServletRunner which ships with the JavaSoft Java Servlet
Development Kit (JSDK 1.1x) and/or a Web server that supports servlets.
For example, Lotus Domino Go Webserver 4.6x.
- An understanding of how to deploy and call a servlet url directly. That is,
"http://:/servlet/swosnick.database.servlets.SQLGatewayServet",
where hostname and port are the server and port that host the servlet.
- Version:
- 1.0.0 01/15/98
- Author:
- Sheldon Bradley Wosnick
IBM Toronto Lab
Toronto Ontario, Canada
February 1998
email: swosnick@ca.ibm.com
backgroundColor- Background color constant for control of background.
connection- The database connection.
driver- The JDBC Driver.
INSERT_UPDATE_DELETE- Constant representing an update, delete or insert operation and will use executeUpdate.
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.
isExecuteUpdateSuccess- On an executeUpdate operation was it successful - returned rows? Used internally only.
isLoggedOn- If the user is logged on in appropriately, set to true, else remain false.
isSQLExceptionOccurred- Did an exception occur on an SQL action? Used internally only.
JDBCDrivers- The list of supported JDBC drivers which will appear in the logon form as a choice.
lastSQL- Track the last SQL query or command so that the user need not re-enter it.
logonFormHeading- The heading or description for the logon form.
logonFormTitle- The title which is sent to the browser for the initial logon form.
NO_OPERATION- Constant representing neither a query or an update, indicating an error retrieving the type of operation.
outstream- The output stream used for HTTP output to create dynamic HTML page.
password- The optional password.
properties- Properties object used to contain username and password to make the database connection.
request- The request sent to the servlet.
response- The response prepared by the servlet.
resultSet- Result set for a given query.
SELECT- Constant used to represent the SELECT operation which will use executeQuery.
servletName- The name of the servlet used in the logon form HTML to call back the servlet with the servlet tag.
sql- The SQL string to use for query or update.
statement- The Statement used for SQL query or update.
tableHeadingBackgroundColor- Table heading background color constant.
tableHeadingForeroundColor- Table heading background color constant.
url- The JDBC-style URL with protocol and datasource name.
userName- The user name or ID.
SQLGatewayServlet()
-
checkForWarning(SQLWarning)
- This method was created by a SmartGuide.
connectionInfo()
- This method was created by a SmartGuide.
connectToDatabase()
- This method was created by a SmartGuide.
destroy()
- This method was created by a SmartGuide.
executeTheQuery(String)
- This method was created by a SmartGuide.
executeTheUpdate(String)
- This method was created by a SmartGuide.
getRequestParameters()
- This method was created by a SmartGuide.
getServletInfo()
- This method was created by a SmartGuide.
getSQLOperation(String)
- This method was created by a SmartGuide.
init(ServletConfig)
- This method was created by a SmartGuide.
outResultSet()
- This method was created by a SmartGuide.
outUpdateStatus(boolean)
- This method was created by a SmartGuide.
processSQLException(SQLException)
- This method was created by a SmartGuide.
sendHTMLLogonForm()
- This method was created by a SmartGuide.
sendSQLForm()
- This method was created by a SmartGuide.
service(HttpServletRequest, HttpServletResponse)
- This method was created by a SmartGuide.
trace(String)
- This method was created by a SmartGuide.
userName
private String userName
- The user name or ID. Supplied by the user.
password
private String password
- The optional password. Supplied by the user or set to an empty string.
url
private String url
- The JDBC-style URL with protocol and datasource name. Supplied by the user.
driver
private String driver
- The JDBC Driver. Supplied by the user from a choice of available drivers.
sql
private String sql
- The SQL string to use for query or update. Supplied by the user.
lastSQL
private String lastSQL
- Track the last SQL query or command so that the user need not re-enter it. Used internally only
connection
private Connection connection
- The database connection. Used internally only.
resultSet
private ResultSet resultSet
- Result set for a given query. Used internally only.
statement
private Statement statement
- The Statement used for SQL query or update. Used internally only.
isSQLExceptionOccurred
private boolean isSQLExceptionOccurred
- Did an exception occur on an SQL action? Used internally only.
isExecuteUpdateSuccess
private boolean isExecuteUpdateSuccess
- On an executeUpdate operation was it successful - returned rows? Used internally only.
outstream
private ServletOutputStream outstream
- The output stream used for HTTP output to create dynamic HTML page. Used internally only.
request
private HttpServletRequest request
- The request sent to the servlet. Used internally only.
response
private HttpServletResponse response
- The response prepared by the servlet. Used internally only.
logonFormTitle
private final String logonFormTitle
- The title which is sent to the browser for the initial logon form.
logonFormHeading
private final String logonFormHeading
- The heading or description for the logon form.
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.
JDBCDrivers
private final String[] JDBCDrivers
- The list of supported JDBC drivers which will appear in the logon form as a choice.
properties
private Properties properties
- Properties object used to contain username and password to make the database connection.
Used internally only.
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.
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.
SELECT
private final int SELECT
- Constant used to represent the SELECT operation which will use executeQuery.
Used internally only.
INSERT_UPDATE_DELETE
private final int INSERT_UPDATE_DELETE
- Constant representing an update, delete or insert operation and will use executeUpdate.
Used internally only.
isLoggedOn
private boolean isLoggedOn
- If the user is logged on in appropriately, set to true, else remain false.
Used internally only.
backgroundColor
private final String backgroundColor
- Background color constant for control of background. Change as desired.
tableHeadingBackgroundColor
private final String tableHeadingBackgroundColor
- Table heading background color constant. Change as desired.
tableHeadingForeroundColor
private final String tableHeadingForeroundColor
- Table heading background color constant. Change as desired.
SQLGatewayServlet
public SQLGatewayServlet()
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
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.
connectToDatabase
private void connectToDatabase()
- This method was created by a SmartGuide.
Perform the physical connection to the database.
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
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
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
getRequestParameters
private void getRequestParameters()
- This method was created by a SmartGuide.
This method will gather the properties passed into the servlet as the request.
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
getSQLOperation
private int getSQLOperation(String anSQLString)
- This method was created by a SmartGuide.
- Parameters:
- String - sql
- Returns:
- int
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
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.
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
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
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.
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.
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
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