Database Access Using Lightweight Applets
Pages: 1, 2, 3, 4
Adding Security to SqlApplet?
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() {
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:8080/root/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);
// 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.
System.out.println(
"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;
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;
}
}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) {
response.sendError(
HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
}
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.
|
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
/*
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;
}
}
setBackground(bgColor);
Font arialBold11 =
new Font("Arial", Font.BOLD, 11);
Font courier11 =
new Font("Courier", Font.PLAIN, 11);
setLayout(null);
Label label = null;
for (int i=0;i < levels;i++) {
label = new Label("Level " +
Integer.toString(i + 1) + ":", Label.RIGHT);
label.setFont(arialBold11);
label.setSize(
new Dimension(col1Width, itemHeight));
label.setLocation(col1X, y);
add(label);
id[i] = new Vector();
choice[i] = new Choice();
choice[i].setFont(courier11);
choice[i].setSize(
new Dimension(col2Width, itemHeight));
choice[i].setLocation(col2X, y);
add(choice[i]);
empty(choice[i],id[i]);
choice[i].addItemListener(this);
y += rowHeight;
}
ok = new Button(" OK ");
ok.setFont(arialBold11);
ok.setSize(
new Dimension(col1Width, itemHeight));
ok.setLocation(col1X, y);
ok.addActionListener(this);
add(ok);
}
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("")) {
url.append(codeBase.substring(0,
codeBase.length() - "applet/".length()));
url.append("servlet/SnoopServlet");
url.append("?parent_organization_id=");
url.append(parent_organization_id);
try {
System.out.println("url=" + url);
ac.showDocument(new URL(url.toString()));
}
catch (MalformedURLException e) {
}
}
else {
ac.showStatus(
"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 =
choice[i].getSelectedIndex();
parent_organization_id =
(String)(id[i].elementAt(selectedIndex));
System.out.println(parent_organization_id);
}
if (i > currentIndex) {
empty(choice[i], id[i]);
System.out.println("emptying " + i);
}
}
if ((currentIndex + 1) < levels &&
!(parent_organization_id.equals(""))) {
load(
parent_organization_id,
choice[currentIndex + 1],
id[currentIndex + 1]);
}
}
}
public void empty(
Choice emptyChoice,
Vector emptyId) {
emptyChoice.removeAll();
// 0 1 2 3
4 5 6
//
123456789012345678901234567890123456789012345678901234567890
emptyChoice.addItem("
");
emptyId.removeAllElements();
emptyId.addElement(new String(""));
}
public void load(
String parent_organization_id,
Choice loadChoice,
Vector loadId) {
Cursor cursor = frame.getCursor();
((Component)frame).setCursor(
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()) {
System.out.println(
"organization_id=" + getString(1));
loadId.addElement(getString(1));
System.out.println("name=" + getString(2));
loadChoice.addItem(getString(2));
}
}
((Component)frame).setCursor(cursor);
}
}
