|
Database Access Using Lightweight Appletsby Donald Bales, author of Java Programming with Oracle JDBC02/20/2002 |
Using HTTP as the communication protocol and a servlet peer for database queries, you can write a rich-content user interface applet that can dynamically interact with a database, yet remain small enough in size to perform well. In last month's article, "Dynamic Database Access from Client-side JavaScript", we looked at an architecture for utilizing the services of an applet-servlet pair to provide dynamic database access for client-side JavaScript.
There were, however, some drawbacks to this approach, the most important being the fact that this architecture leaves access to the database wide open. You can work around this issue by restricting which database objects can be accessed from the user id used to log into the database from SqlServlet, or by restricting a sub-classed version of SqlServlet to the access of a particular table; however, both of these strategies fall short of allowing you to access confidential data. Another approach is needed. We'll discuss a transparent approach shortly.
A second drawback was browser compatibility. JavaScript compatibility varies wildly from browser to browser. To work around this problem, you can simply abandon HTML and JavaScript use for your client-side GUI, and instead create a rich-content GUI using a lightweight applet. A lightweight applet is an applet designed to display a single screen. It uses HTTP as a protocol to communicate with a database through SqlServlet.
Designing an applet this way greatly reduces the size of the applet, because almost all of the classes it needs to execute already exist on the client as part of a browser's installation. All that needs to be downloaded is your custom applet and the SqlApplet classes. The typical lightweight applet is around 8K in size. Not much bigger than an HTML page embedded with enough JavaScript to produce equivalent functionality. Accordingly, I'll show you how to rewrite SqlServlet and SqlApplet so they can be readily sub-classed to create a specialized SQL servlet or lightweight applet. We'll also cover some strategies for making SqlServlet and SqlApplet secure. Then, we'll finish up with a working example.
In contrast to my previous article, we're now using an applet-servlet-database architecture, minus the HTML and JavaScript, as shown in Figure 1. In this architecture, our dynamic SQL servlet, SqlServlet, receives a SQL statement as a URL parameter, sql, executes the SQL statement, and then returns its results as tab-delimited text. SqlServlet's peer, SqlApplet, parses the tab-delimited data, making it available as a series of rows and columns.
|
We have two modifications to make to SqlServlet. The first is to move the dynamic query capability of the servlet to a protected method.
This will allow you to sub-class the servlet in order to create a specialized query servlet. A second modification is to add security to the servlet so that only authorized use of the servlet is possible. Then we have two possible modifications for SqlApplet. The first is to remove its GUI console, and the second is to make its custom public methods protected, to prevent generic use through JavaScript. Let's start from the ground up by tackling SqlServlet first.
|
Dynamic Database Access from Client-Side JavaScript -- 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. This article describes the applet-servlet pair architecture and offers several sample applications. |
You can re-code SqlServlet so that it is suitable for sub-classing by moving the code that dynamically executes a SQL statement, and returns the results as tab-delimited data, into a protected method, execute(), as I have done in Example 1. In addition, if you wish to disable generic use of SqlServlet, you can code a call to the sendError() method in the beginning of its doGet() method.
|
|
Related Reading 
Java Programming with Oracle JDBC
Table of Contents
Index Sample Chapter Author's Article Read Online--Safari Search this book on Safari: |
There are several tactics you can use to add security to SqlServlet. For example, you can have it check headers for Basic authorization, like a Web server does when security is enabled. You would then have to add functionality to SqlApplet so that it would send a properly-formatted response to the authorization header from SqlServlet. There is a good example of this in Java Servlet Programming. The problem with this method is that a Base-64-encoded userid and password are passed clear over the wire, so it's fairly easy for a malicious user to discover what userid and password are being used by using a sniffer. You could always use your own custom encoding, but there's a better approach.
You can authenticate a user with a separate login screen, and store an authentication flag in their HttpSession object. You can then check for the authentication flag before executing a SQL statement in SqlServlet. That's what I have done in the beginning of the SqlServlet's execute() method. First, I get the current session object. Next, I attempt to retrieve a String by the name of authenticated from the session object. If the authentication flag authenticated does not exist, SqlServlet returns a 503 error (forbidden) to SqlApplet. This tactic requires you to add authentication to your Web application, but prevents unauthorized access to the database through SqlServlet. It works transparently for both the generic and sub-classed use of SqlServlet.
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 {
// Moved the code to execute a SQL statement
// to a private method: execute(...).
// You can disable its use without being
// sub-classed by sending a forbidden error.
/*
response.sendError(
HttpServletResponse.SC_FORBIDDEN);
return;
*/
// Process the SQL statement
String sql = request.getParameter("sql");
if (sql != null) {
execute(request, response, sql);
}
else {
response.sendError(
HttpServletResponse.SC_BAD_REQUEST);
}
}
public void doPost(
HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
protected void execute(
HttpServletRequest request,
HttpServletResponse response,
String sql)
throws IOException, ServletException {
// Check authentication. If the session variable
// "authenticated" does not exist, send a 401 error
HttpSession session = request.getSession();
String authenticated =
(String)session.getAttribute("authenticated");
if (authenticated == null) {
response.sendError(
HttpServletResponse.SC_FORBIDDEN);
return;
}
// 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;
}
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 Statemetn 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();
}
}Our dynamic query applet, SqlApplet, has two possible modifications. The first is to remove the console code from it. This is the code that existed in its init() method, which displayed the number of columns and rows, and the error code after each call to execute(). In Example 2, I've removed the code from init() and instead added a formatted call to System.out.println() to display that data in the Java console of the browser.
|
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;
}
}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);
}
}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);
}
}
|
Example 5 is an HTML file you can use to display OrganizationDDLBApplet.
Example 6, Login, is a servlet you can use to set the authenticated flag in the
current session to enable the use of SqlServlet.
Example 5. OrganizationDDLBApplet.html
<html>
<head>
<title>Organization</title>
</head>
<body BGCOLOR="#BFBFBF">
<applet
code="OrganizationDDLBApplet.class"
codebase="http://dssw2k01:8080/learn/applet/"
height="550"
width="750" >
<!-- Tell the applet where its peer is located -->
<param
name="servlet"
value="http://dssw2k01:8080/learn/servlet/OrganizationDDLBServlet" />
<param
name="BGCOLOR"
value="#BFBFBF" />
</applet>
</body>
</html>Example 6. Login
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class Login extends HttpServlet {
public void doGet(
HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
// Get the current session or create a new one
HttpSession session = request.getSession();
// Get the authentication flag
String authenticated =
(String)session.getAttribute("authenticated");
// If it's valid then remove it
if (authenticated != null) {
session.removeAttribute("authenticated");
}
// Get the userid and password
String userid =
request.getParameter("userid");
String password =
request.getParameter("password");
PrintWriter out =
response.getWriter();
response.setContentType("text/html");
// Display a login screen if needed
if (userid == null || password == null) {
out.println("<html>");
out.println("<head>");
out.println("<title>Login</title>");
out.println("<body>");
out.println("<form action\"../servlet/Login\"
method=\"post\">");
out.println("Please enter your useid and password
then submit to log-in:<br/>");
out.println("Userid: <input name=\"userid\"
size=\"30\" type=\"text\"><br/>");
out.println("Password: <input name=\"password\"
size=\"30\" type=\"password\"><br/>");
out.println("<input type=\"submit\"");
out.println("</form>");
out.println("</body>");
out.println("</html>");
}
else {
// Add authetication here!!!
session.setAttribute("authenticated", "");
out.println("<html>");
out.println("<head>");
out.println("<title>Login</title>");
out.println("<body>");
out.println("<h3>You have logged-in successfully.</h3>");
out.println("</body>");
out.println("</html>");
}
}
public void doPost(
HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
doGet(request, response);
}
}Using lightweight applet technology, you can bring the full weight of Java's
GUI capability, and the advantages of client-side dynamic database access, to
your Web applications. Because the technology employs HTTP to communicate with
a database via SqlServlet, lightweight applets perform well yet remain small
enough to have acceptable download times.
|
Related Reading Java Programming with Oracle JDBC
Table of Contents
Index Sample Chapter Author's Article Read Online--Safari Search this book on Safari: |
You can get a copy of the source code for this article here. For more information on applets, look at Learning Java. For HTTP communications read Java I/O. For servlets, check out the totally excellent Java Servlet Programming. And for more information on Oracle's implementation of JDBC, check out my book, Java Programming with Oracle JDBC.
Donald Bales is a Computer Applications Consultant specializing in the analysis, design, and programming of distributed systems; systems integration; and data warehousing.
Return to ONJava.com.
Copyright © 2007 O'Reilly Media, Inc.