Dynamic Database Access from Client-Side JavaScript
Pages: 1, 2
A Dynamic SQL Applet
|
Related Reading
|
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.
|
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.
 |
 |
|
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.
Sample Chapter 19, "Performance," is available free online.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
For more information, or to order the book, click here.
Return to ONJava.com.
