ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Don Bales

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.

Diagram.
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.

Screen shot.
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.

Download example files for this article here.

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);
  }
}

A Dynamic SQL Applet

Related Reading

Java Programming with Oracle JDBCJava Programming with Oracle JDBC
By Donald Bales
Table of Contents
Index
Sample Chapter
Full Description
Read Online -- Safari

The second member of our dynamic duo is appropriately named SqlApplet (Example 2). SqlApplet has five public methods that can be executed by JavaScript when it is used as an applet on a Web page:

public boolean next()
Returns true if there is more data to read using getString().
public String getString(int col)
Returns a column value as a String.
public int getColumnCount()
Returns the number of columns in the result set.
public int getRowCount()
Returns the number of rows in the result set.
public int execute(String sql)
Used to execute a SQL statement. Returns an error code or zero if no error occurred.

You execute a SQL statement from JavaScript by calling SqlApplet's execute() method, passing it a SQL statement. When SqlApplet's execute() method is called, the program starts out by allocating four variables. The first is a BufferedReader, br, to buffer the data from the second, an InputStream, in, which will hold a reference to the input stream returned by the HTTP connection to SqlServlet. A reference to the connection itself is held by a URLConnection, conn. In order to open the connection, a URL, url, is created. Next, the program enters a try block where the URL is constructed. A connection is returned with a call to the URLConnection object's openConnection() method. Then the program turns off caching by calling its setUseCaches() method. Next, the URL is sent to the Web server and an input stream with the results is returned as an InputStream object, which the program wraps with a BufferedReader. Then the three custom headers, Sql-Stat, Sql-Rows, and Sql-Cols, are retrieved. The program then enters a while loop, where the tab-delimited data from SqlServlet is parsed into a String array, tokens. At this point, the entire result set from the SQL statement resides in SqlApplet's String array, tokens. The four other public methods can then be used by JavaScript to retrieve the values from SqlApplet into the client-side HTML document.

SqlApplet also builds a display in its init() method. It displays the number of rows and columns in the result set, along with any SQL error code on the screen. This is done using AWT objects for compatibility. The display helps you debug your application while you're developing it. When you no longer want to see the display, set the applet's height and width to zero.


Example 2: SqlApplet

import java.applet.*;
import java.awt.*;
import java.io.*;
import java.net.*;

public class SqlApplet extends Applet {
 int        cols      = 0;
 int        row       = 0;
 int        rows      = 0;
 int        stat      = 0;
 Label      colsLabel = new Label("Columns: 00000");
 Label      rowsLabel = new Label("Rows: 00000");
 Label      statLabel = new Label("Status: 00000");
 String[][] tokens    = new String[1][1];

 private String nvl(String value, String substitute) {
  return (value != null) ? value : substitute;
 }

 public void init() {
  setBackground(Color.white);
  Font  arialPlain11 = new Font("Arial", Font.PLAIN, 11);
  Font  arialBold11   = new Font("Arial", Font.BOLD, 11);
  Label appletLabel   = new Label("SqlApplet");
  appletLabel.setFont(arialBold11);
  colsLabel.setFont(arialPlain11);
  rowsLabel.setFont(arialPlain11);
  statLabel.setFont(arialPlain11);
  add(appletLabel);
  add(statLabel);
  add(rowsLabel);
  add(colsLabel);
 }

 public boolean next() {
  row++;
  return (row < rows) ? true : false;
 }

 public String getString(int col) {
  return (row < rows) ? tokens[row][col - 1] : "";
 }

 public int getColumnCount() {
  return cols;
 }

 public int getRowCount() {
  return rows;
 }

 public int execute(String sql) {
  BufferedReader br   = null;
  InputStream    in   = null;
  URLConnection  conn = null;
  URL            url  = null;

  try {
   String servlet = nvl(getParameter("servlet"),
    "http://localhost:9080/ghs/servlet/SqlServlet");
   url = new URL(servlet + "?sql=" + URLEncoder.encode(sql));

   conn = url.openConnection();
   conn.setUseCaches(false);

   in   = conn.getInputStream();
   stat = conn.getHeaderFieldInt("Sql-Stat", -1);
   rows = conn.getHeaderFieldInt("Sql-Rows", -1);
   cols = conn.getHeaderFieldInt("Sql-Cols", -1);

   statLabel.setText("Status: "  + Integer.toString(stat));
   rowsLabel.setText("Rows: "    + Integer.toString(rows));
   colsLabel.setText("Columns: " + Integer.toString(cols));

   br = new BufferedReader(new InputStreamReader(in));

   int    beginIndex = 0;
   int    index      = 0;
   int    col        = 0;
   String line       = null;
   tokens            = new String[rows][cols];
   row = 0;
   while ((line = br.readLine()) != null) {
    beginIndex = 0;
    col = 0;
    while ((index = line.indexOf('\t', beginIndex)) != -1) {
     tokens[row][col] = line.substring(beginIndex, index);
     beginIndex = index + 1;
     col++;
    }
    if (beginIndex < line.length()) {
     tokens[row][col] = line.substring(beginIndex);
    }
    row++;
   }
   row = -1;
   br.close();
   br = null;
   in.close();
   in = null;
  }
  catch (IOException e) {
   System.out.println("Can't execute servlet.");
   System.out.println(conn.getHeaderField(0));
   System.out.println(e.getMessage());
  }
  finally {
   if (br != null)
    try { br.close(); } catch (IOException ignore) {}
   if (in != null)
    try { in.close(); } catch (IOException ignore) {}
  }
  return stat;
 }

}

A Dynamic SQL Web Page

Now that we have our two infrastructure pieces, let's look at an example Web page that allows you to dynamically execute SQL statements from your browser. Our SqlApplet.html Web page (Example 3) consists of an embedded applet, a JavaScript script, and an HTML form. When you open the Web page from the same Web server where your servlet resides, you can enter a SQL statement and then click on the Execute button to execute it using the SqlApplet-SqlServlet peers. Figure 3 shows the results of such a query.

Screen shot.
Figure 3: SqlApplet.html shows the results of our previous query

In SqlApplet.html, you can see the <applet> tag where the SqlApplet applet is added to the Web page. It requires a single parameter, servlet, which tells the applet where its peer is located. This must be on the same host, otherwise you'll run into Java security exceptions. Next, the <script> tag denotes the start of the JavaScript that passes the runtime- specified SQL statement to SqlApplet for execution. It does so by getting the SQL statement from the HTML form's text field, and then calling the applet's public method execute(). When the script returns from its call to execute(), the contents of the result set from the database exist in the memory of the applet. The script proceeds by entering a while loop and within that, a for loop, where the values of the SQL statement's result set are retrieved one column at a time and added to the text area of the HTML form.


Example 3: SqlApplet.html

<html>
<head>
<applet
  code="SqlApplet.class"
  codebase="http://dssw2k01:8080/learn/applet/"
  height="25"
  name="sqlApplet"
  width="640" >
<param
<!-- Tell the applet where its peer is located -->
  name="servlet"
  value="http://dssw2k01:8080/learn/servlet/SqlServlet" />
</applet>
<script language="JavaScript">
function button1Clicked() {
 var sql    = document.form1.text1.value;
 var app    = document.sqlApplet;
 var result = "";
 app.execute(sql);
 while (app.next()) {
  var cols = app.getColumnCount();
  for (var i=1;i <= cols;i++) {
   var token = app.getString(i);
   if (i > 1)
    result += "\t";
   result += token;
  }
  result += "\n";
 }
 document.form1.textarea1.value = result;
}
</script>
</head>
<body>
<form action="" name="form1">
<input
  type="text"
  name="text1"
  size="106" >
<textarea
  cols="80"
  name="textarea1"
  rows="15"
  wrap="off" >
</textarea>
<input
  type="button"
  name="button1"
  onclick="button1Clicked();"
  value="Execute SQL" >
</form>
</body>
</html>

Conclusion

Using this architecture, you can add traditional client-server GUI functionality to your Web pages. I commonly use it for dynamically populating hierarchically related drop-down list boxes, instead of performing noticeable repeated calls to the Web server for the next page. For example, if I need to display a report criteria dialog screen for report by organization, I can display a Web page, as in Figure 4, where the values in the second and third levels change dynamically, based on the selection made in the previous level. I also use this architecture to dynamically validate values that may be duplicates in the database.

Screen shot.    Screen shot.

Figure 4. Second and then third levels selected.

This technique is no panacea, however; there are drawbacks. First, since the access to SqlServlet uses no security, it's wide open. You can only use it in its current invocation for data items that can be public information. You can work around this issue by encoding and passing a user ID and password from SqlApplet, and by modifying SqlServlet to require a password.

Second, browser compatibility still remains a constraint you'll have to work around. Both IE and Netscape work fine, but up-and-coming browsers like Opera do not. An alternative to using JavaScript is to rewrite SqlApplet and SqlServlet for subclassing, and then to use SqlApplet to build a lightweight applet with a rich-content user interface instead of using HTML and JavaScript. That works for a majority of browsers. Yet, using HTTP as the protocol for the applet keeps it to a reasonable size. We'll talk about this technique in my next article, "Lightweight Applets with Database Access using HTTP."

You can get a copy of the source code for this article at my Web site. For more information on applets, look at Learning Java by Patrick Niemeyer & Jonathan Knudsen (O'Reilly). For HTTP communications, read Java I/O by Elliotte Rusty Harold (O'Reilly). For servlets, check out the totally excellent Java Servlet Programming by Jason Hunter with William Crawford (O'Reilly). And for more information on Oracle's implementation of JDBC, check out my book, Java Programming with Oracle JDBC (O'Reilly).

Donald Bales is a Systems Architect, Computer Applications Consultant, and Business Analyst specializing in the analysis, design, and programming of web-based, client-server, and distributed applications, the internationalization of existing applications, systems integration, and data warehousing.


O'Reilly & Associates recently released (December 2001) Java Programming with Oracle JDBC.

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.