ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Database Access Using Lightweight Applets
Pages: 1, 2, 3, 4

Adding Security to SqlApplet?

Download example files for this article here.

In order to prevent JavaScript access to a database through SqlApplet, you can make its methods protected instead of public. If they are protected, they can still be called by a class that extends SqlApplet, but they will no longer be visible to client-side JavaScript. For the sake of compatibility with the previous use of SqlApplet, I have not made this change to Example 2.

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;
 String[][] tokens    = new String[1][1];
 private String nvl(String value, String substitute) {
  return (value != null) ? value : substitute;
 // You can disable SqlApplet's use without
 // being sub-classed by making the following
 // methods protected instead of public.
 public boolean next() {
  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"),
   url = new URL(servlet + "?sql=" + URLEncoder.encode(sql));
   conn = url.openConnection();
   in   = conn.getInputStream();
   stat = conn.getHeaderFieldInt("Sql-Stat", -1);
   rows = conn.getHeaderFieldInt("Sql-Rows", -1);
   cols = conn.getHeaderFieldInt("Sql-Cols", -1);
   // I've eliminated the console code in the
   // init() method, so I've added this code
   // to display the execute() method's status
   // in the Browser's Java console.
    "Columns: " + cols +
    ", Rows: " + rows +
    ", Status: " + stat);
   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;
    if (beginIndex < line.length()) {
     tokens[row][col] = line.substring(beginIndex);
   row = -1;
   br = null;
   in = null;
  catch (IOException e) {
   System.out.println("Can't execute servlet.");
  finally {
   if (br != null)
    try { br.close(); } catch (IOException ignore) {}
   if (in != null)
    try { in.close(); } catch (IOException ignore) {}
  return stat;

Creating a Specialized SQL Servlet

In order to demonstrate sub-classing of SqlServlet, I've created a specialized servlet, OrganizationDDLBServlet (Example 3), that expects part of a WHERE clause to be passed instead of an entire SQL statement. This prevents someone from using the servlet for a task other than that which it was intended.

OrganizationDDLBServlet extends SqlServlet, which gives it access to SqlServlet's protected method execute(). The servlet's doGet() method dynamically builds a SQL statement using the WHERE clause passed as a URL get parameter, and then calls its execute() method.

Example 3. OrganizationDDLBServlet

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class OrganizationDDLBServlet
 extends SqlServlet {
  public void doGet(
   HttpServletRequest request,
   HttpServletResponse response)
   throws IOException, ServletException {
    String sql = request.getParameter("sql");
    if (sql == null) {
    String select =
     "select organization_id, " +
     "       name " +
     "from   ORGANIZATION " +
     "where  " + sql + " " +
     "order by name";
    execute(request, response, select);

Creating a Lightweight Applet

Now that we've made the necessary modifications to SqlServlet and SqlApplet, we have a working foundation we can use to create a lightweight applet. Creating a lightweight applet is now a simple matter of sub-classing SqlApplet, adding the necessary GUI code, and deciding how we are going to initialize and save the applet's values.

There are two ways you can set the applet's initial field values. First, you can pass each of the values as an applet parameter, or you can pass a primary key value as a parameter and then query the database for the remaining values. In Example 4, OrganizationDDLBApplet, I use the second tactic. Here, I've have created a three-level, hierarchically-related drop-down list box dialog. For this applet, it's not necessary for me to pass a primary key as a parameter. Instead, the applet queries the database for all of the high-level organizations when its init() method is executed. Let's take a detailed look.

OrganizationDDLBApplet extends SqlApplet, which gives it access to SqlApplet's abilities to execute a SQL statement against a database. It also implements the ActionListener and ItemListener interfaces, so it can detect when the OK button is pressed, or when an item in one of the drop-down list boxes has been selected. At the top of the program I declare a handful of variables. The Button, ok, and Choice array, choice, contains GUI components for the OK button and three drop-down list boxes. The Vector array, id, keeps track of the primary keys for the values in each Choice. When an item is selected from a Choice, the same index value can be used to find the appropriate primary key for the selected organization. The Frame, frame, is used to set the cursor to an hourglass while the database is being queried. The int, levels, determines how many Choice lists the applet will display. The String, parent_organization_id, will hold the last organization ID selected from one of the drop-down list boxes (DDLB).

Briefly, in the init() method, the GUI is initialized. I call a private method, getFrame(), to walk the component hierarchy until I get the address of the browser's frame. I do this so I can later call the setCursor() method on the frame. Next, I get the background color for the applet from an applet tag parameter. You can make similar calls to get default or initial values for the applet. I do this sometimes when I generate the HTML that displays the applet dynamically from a JSP or servlet, to pass a primary key value for data to be retrieved from the database, or to set all initial values. Next, being lazy, I set the layout manager to null to enable fixed positioning of the applet's components. (Have you ever tried to get multiple drop down list boxes to line up with one of the standard layout managers?) Last, I add the components to the GUI.

Screen shot.
Figure 2. OrganizationDDLBApplet

In the start() method, I make a call to a specialized SqlServlet servlet, OrganizationDDLBServlet (Example 3), in order to initialize the top-level DDLB with all top-level organizations in the database. At this point, the applet displays a screen like the one shown in Figure 2. Each time an item is selected in one of the DDLBs, another call is made to the database via OrganizationDDLBServlet. When the OK button is clicked, the applet calls SnoopServlet, passing it the selected organization ID as a URL get parameter.

Once again, you have two ways you can save the applet's modified data. First, you can pass the items as URL get parameters, or you can execute INSERT, UPDATE, or DELETE statements against the database.

Example 4. OrganizationDDLBApplet

 by Donald Bales
 on 2/2/2002
import java.applet.*;
import java.awt.*;
import java.awt.event.*;
import java.net.*;
import java.util.*;
public class OrganizationDDLBApplet
 extends     SqlApplet
 implements  ActionListener,
             ItemListener {
  Button   ok;
  Choice[] choice;
  Frame    frame;
  int      levels = 3;
  String   parent_organization_id;
  Thread   loader;
  Vector[] id;
  private Frame getFrame(Component component) {
    Component frame = component;
    while (!(frame instanceof Frame)) {
      frame = frame.getParent();
    return (Frame)frame;
  public void init() {
    int col1Width  = 56;
    int col2Width  = col1Width * 8;
    int col1X      = 0;
    int col2X      = col1Width;
    int itemHeight = 21;
    int rowHeight  = 24;
    int y          = 0;
    choice         = new Choice[levels];
    id             = new Vector[levels];
    frame          = getFrame(this);
    Color  bgColor = Color.white;
    String BGCOLOR = getParameter("BGCOLOR");
    if (BGCOLOR != null) {
     try {
      bgColor = Color.decode(BGCOLOR);
     catch (NumberFormatException e) {
      bgColor = Color.white;
    Font arialBold11 =
     new Font("Arial", Font.BOLD, 11);
    Font courier11   =
     new Font("Courier", Font.PLAIN, 11);
    Label label = null;
    for (int i=0;i < levels;i++) {
      label = new Label("Level " +
       Integer.toString(i + 1) + ":", Label.RIGHT);
       new Dimension(col1Width, itemHeight));
      label.setLocation(col1X, y);
      id[i]     = new Vector();
      choice[i] = new Choice();
       new Dimension(col2Width, itemHeight));
      choice[i].setLocation(col2X, y);
      y += rowHeight;
    ok = new Button(" OK ");
     new Dimension(col1Width, itemHeight));
    ok.setLocation(col1X, y);
  public void start() {
    load(null, choice[0], id[0]);
  public void actionPerformed(ActionEvent ignore) {
    AppletContext ac = getAppletContext();
    String  codeBase = getCodeBase().toString();
    StringBuffer url = new StringBuffer();
    if (!parent_organization_id.equals("")) {
       codeBase.length() - "applet/".length()));
      try {
        System.out.println("url=" + url);
        ac.showDocument(new URL(url.toString()));
      catch (MalformedURLException e) {
    else {
       "Please select an organization");
  public void itemStateChanged(ItemEvent ie) {
    Object currentChoice   = ie.getSource();
    int currentIndex       = 9;
    parent_organization_id = null;
    if (ie.getStateChange() == ItemEvent.SELECTED) {
      for (int i=0;i < choice.length;i++) {
        if (choice[i] == currentChoice) {
          currentIndex = i;
          int selectedIndex =
          parent_organization_id =
        if (i > currentIndex) {
          empty(choice[i], id[i]);
          System.out.println("emptying " + i);
      if ((currentIndex + 1) < levels &&
          !(parent_organization_id.equals(""))) {
         choice[currentIndex + 1],
         id[currentIndex + 1]);
  public void empty(
   Choice emptyChoice,
   Vector emptyId) {
    //                   0        1         2         3
             4         5         6
    emptyId.addElement(new String(""));
  public void load(
   String parent_organization_id,
   Choice loadChoice,
   Vector loadId) {
    Cursor cursor = frame.getCursor();
     new Cursor(Cursor.WAIT_CURSOR));
    empty(loadChoice, loadId);
    String sql =
     (parent_organization_id != null) ?
    "parent_organization_id = " +
     parent_organization_id :
    "parent_organization_id is null";
    if (execute(sql) == 0) {
      while (next()) {
         "organization_id=" + getString(1));
        System.out.println("name=" + getString(2));

Pages: 1, 2, 3, 4

Next Pagearrow