|
Dynamic Database Access from Client-Side JavaScript
by Donald Bales, author of Java
Programming with Oracle JDBC
01/23/2002
|
Imagine the advantages of being able to dynamically access a database from
client-side JavaScript. You could dynamically query a database from JavaScript for client-side field validation, or dynamically populate a drop-down select list, to name just a few possibilities. In fact, you can do this, with the help of an applet-servlet pair (assuming you can constrain your users to compatible browsers, i.e., IE and Netscape.) This article describes the applet-servlet pair architecture and offers several sample applications.
A Lightweight SQL Applet Architecture
Figure 1 is a architecture diagram for using JavaScript, an applet, and a servlet
to query a database from a Web page. Starting on the far left, a
JavaScript in a Web page calls the applet's public method to send a SQL
statement to its servlet peer. The SQL applet uses HTTP to send the SQL
statement as a GET request. The servlet peer executes the SQL statement, using JDBC to
communicate with the database. Then, the servlet appropriately sends back either
a result set or the number of rows affected by the SQL statement as tab delimited
text. In turn, the SQL applet parses the returned data. The JavaScript then uses some of the SQL applet's other public methods to access the data from within the script.
Figure 1. JavaScript-Applet-Servlet-Database Architecture
|
Why an applet-servlet pair and not just an applet? You can write an
applet that can perform dynamic database queries, but then you'll have two problems to contend with. First, you'll have to add your JDBC driver's classes to your applet's archive. This will cause your applet's archive to grow from 4K to about 1.5M. That will be a major performance problem if your user base is not on a high-speed network. Second, you'll encounter socket security exceptions. These exceptions vary, depending on the version of JDK and browser you're using. To get around these two problems, we can utilize the services of a servlet that can perform dynamic SQL queries, while using an applet to exchange information with the servlet via HTTP. With a servlet performing the actual SQL statements, the database driver is not part of the applet's archive, so the size of the applet's archive can be kept to 4K. By using HTTP as the protocol, there are
typically no socket security issues. Assuming we have a database that's
accessible from our servlet container, let's start a detailed examination of this
architecture from the ground up by first looking at our SQL servlet.
A Dynamic SQL Servlet
Our dynamic SQL servlet, appropriately named SqlServlet (Example 1), leverages the truly dynamic
capabilities of JDBC to execute a SQL statement. It can execute not only a select
statement, but any kind of DML or DDL. Execute a SQL statement simply by sending it as the value of the sql. For example, if the
servlet is located in a context directory of "learn" on host "dssw2k01:8080", then
you can get a list of all the tables you can access at this URL: "http://dssw2k01:8080/learn/servlet/SqlServlet?sql=select * from all_tables".
Figure 2 shows typical results.
Figure 2. Sample results from a query using SqlServlet
|
When your browser sends the SQL statement to SqlServlet, the servlet's doGet()
method is executed. In doGet(), the method starts out by getting a connection.
As I have noted in the code, this is not the best way to get a connection, but it suffices
for a sample program. Next, SqlServlet gets a copy of the passed SQL statement by calling the
HttpServletRequest object's getParameter() method. Then,
it allocates several variables: three ints to keep track of the number of columns
in a result set, the number of rows in a result set, and an SQL error code if a
SQLException occurs; a Statement to dynamically execute
a passed SQL statement; a ResultSet to retrieve the results from a
SELECT statement; a ResultSetMetaData to dynamically determine
the number of columns in a returned result set; and finally a StringBuffer
used in the process of tab-delimiting data.
Next, the program enters a try
block where a Statement object is created and then used to execute the
SQL statement using its execute() method. execute() returns
true if a ResultSet is available, in which case the program retrieves the result set
using the Statement object's getResultSet() method.
Given a ResultSet object, the program then gets the result set's metadata object
by calling its getResultSetMetaData() method. The program then gets the
result set's column count by calling the ResultSetMetaData object's
getColumnCount() method. Next, the program loops through the result
set, tab-delimiting the data into the string buffer data.
If no result set is available, the program gets the number of rows affected
by the SQL statement by calling the Statement object's
getUpdateCount() method.
At this point, the program has determined the number of columns, rows, any error
code, and has tab-delimited any data. It proceeds by getting the servlet's PrintWriter
in order to the write the contents of the string buffer, data, to the user's
browser. and then sets the content type to text/plain. Next, three
custom headers are sent, Sql-Stat, Sql-Rows, and Sql-Cols, which are used to send
any error code, the number of rows in the result set or the number of rows affected by the SQL
statement, and the number of columns. The contents of the string buffer data is
sent, and the stream is flushed. At this point, the job of SqlServlet is done and it's time
for SqlApplet.
Example 1: SqlServlet
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class SqlServlet extends HttpServlet {
public void doGet(
HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
// Normally, I'd never get a connection
// for a servlet this way, but it's OK
// for an example.
// Load the JDBC driver
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException e) {
System.err.print(e.getMessage());
response.sendError(
HttpServletResponse.SC_INTERNAL_SERVER_ERROR,
"Unable to load class " +
"oracle.jdbc.driver.OracleDriver");
return;
}
// Get a database connection
Connection conn = null;
try {
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl",
"scott",
"tiger");
}
catch (SQLException e) {
System.err.print(e.getMessage());
response.sendError(
HttpServletResponse.SC_INTERNAL_SERVER_ERROR,
e.getMessage());
return;
}
// Get the SQL statement passed as a parameter
String sql = request.getParameter("sql");
int cols = 0;
int stat = 0;
int rows = 0;
ResultSet rset = null;
ResultSetMetaData rsmd = null;
Statement stmt = null;
// This StringBuffer will hold the output until
// we're ready to send it.
StringBuffer data = new StringBuffer(8192);
try {
// Create a Statement object from the
// Connection object
stmt = conn.createStatement();
// Execute the SQL statement.
// The execute() method will return
// a true if a result set is avaiable.
if (stmt.execute(sql)) {
// Get the result set
rset = stmt.getResultSet();
// Get meta data (data about the data)
// from the result set.
rsmd = rset.getMetaData();
// Get the number of columns
cols = rsmd.getColumnCount();
// Walk the result set
// tab delimiting the column
// data as you go into the
// StringBuffer, data.
while(rset.next()) {
rows++;
if (rows > 1) {
data.append("\n");
}
for(int col = 1;col <= cols;col++) {
if (col > 1) {
data.append("\t");
}
data.append(rset.getString(col));
}
}
// Let go of the meta data object
rsmd = null;
// Close and let go of the result set
rset.close();
rset = null;
}
else {
// If there's no result set
// then the execute() method
// returns the number of rows
// affected by the SQL statement.
rows = stmt.getUpdateCount();
}
// Close a let go of the statement
stmt.close();
stmt = null;
}
catch (SQLException e) {
System.out.println(
"Can't execute query: " + sql + ".");
System.out.println(e.getMessage());
stat = e.getErrorCode();
}
finally {
// Make sure the result set
// and statement objects
// are close if there is a
// SQLException.
if (rset != null) {
try {
rset.close();
}
catch (SQLException ignore) {
}
}
if (stmt != null) {
try {
stmt.close();
}
catch (SQLException ignore) {
}
}
}
// Close the connection
try {
conn.close();
}
catch (SQLException ignore) {
}
// Get the output stream
PrintWriter out = response.getWriter();
// Set the content type
response.setContentType("text/plain");
// Set the "custom" headers:
// Sql-Stat returns any SQLException
// error code.
response.setHeader(
"Sql-Stat",Integer.toString(stat));
// Sql-Rows returns the number of rows
response.setHeader(
"Sql-Rows",Integer.toString(rows));
// Sql-Cols returns the number of columns
response.setHeader(
"Sql-Cols",Integer.toString(cols));
// Send the data
out.print(data.toString());
out.flush();
}
public void doPost(
HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}