Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Java 1.2 Unleashed

Java 1.2 Unleashed -- Ch 46 -- T


Java 1.2 Unleashed

Previous chapterNext chapterContents


- 46 -

Integrating Database Support into Web Applications




The Web is a global medium from which you can find information on any topic. Because of the Web's capability to distribute information to a worldwide audience, different approaches to integrating databases with the Web have been explored. Early Web pioneers relied on the Common Gateway Interface (CGI) as a way to marry Web and database technologies. The complexities, inefficiencies, and security problems associated with CGI programs resulted in second-generation approaches to database integration. Major Web software vendors, such as Netscape, have devised solutions to connecting databases to Web applications, such as LiveWire Pro.



In this chapter, you'll learn how to use the JDBC to connect applets with online databases. You'll learn what kind of JDBC drivers to use to support zero installation database connectivity. You'll develop an applet that inserts form data directly into an online database. You'll create a general SQL statement processing applet for searching online databases and for testing other database-enabled applets. You'll then explore approaches to integrating multimedia and databases and develop a multimedia photo album. When you finish this chapter, you'll find the JDBC to be a superior conduit for connecting the Web to online databases.

Using java.sql with Applets

Although applets can communicate with databases using any type of JDBC driver, the elegance or crudeness of the solution depends on which type of driver you use. The following is a list of the available drivers:


NOTE: Chapter 45 introduces and describes each of the preceding JDBC driver types.

As you can tell by now, I am sold on the JDBC-Net pure Java driver--that's why I use the IDS driver. The first two approaches are just too cumbersome for Web applications. Users should not have to install database drivers on their machines to use an applet.

Security and standardization are important considerations for Internet use. When I am not writing books, I am involved in Internet security, sometimes in the area of Internet firewalls. Because of the significant lack of firewall proxies for vendor-specific database protocols, any database-enhanced Web application should consider the availability of a standard protocol for database access (like HTTP and SHTTP). The JDBC-Net pure Java driver solution allows you to use a standard protocol. This is important so that both your firewall and the user's firewall can support applet-to-database communication.


NOTE: Proxies are programs that support secure use of a protocol through a firewall. In order for a firewall to support a protocol in a secure manner, it needs a proxy for that protocol.

The third and fourth types of drivers support zero installation applets. Because the JDBC drivers are written entirely in Java, they are downloaded from the Web server along with the applet. In order to support zero installation, the drivers must be placed on the Web server in the same directory as the applet. In this chapter, I'll be placing the applets in the \ch46 directory off of my Web server root. You should also create a \ch46 directory off of your Web server's root if you intend to run the examples in this chapter. Copy the C:\IDSServer\classes\ids directory and all of its subdirectories to your Web server so that it is accessible as /ch46/ids. By doing so, you're making the IDS JDBC driver accessible to applets so that zero installation can be accomplished. If you are not using the IDS driver, you will need to consult your driver's documentation to determine how to make that driver available for installation by applet users.

Trusted vs. Untrusted Applets

Another important consideration when you're setting up a database-enabled Web application using applets is the location of your database server and database access server. Untrusted applets can only communicate with the host from which they are served. This means that they can talk back only to the Web server from which they are loaded. The purpose of this restriction is to prevent applets from obtaining sensitive information (such as that entered into a form) and disclosing that information to other hosts on the Internet.

If you are using type 1, 2, or 4 JDBC drivers, you have to put your database server on the same host as your Web server. This can lead to a performance problem for a busy Web site that supports a high level of database accesses. Your alternative is to require users to trust your applets to talk to other Internet hosts in a secure manner. This is an imposition on the user for two reasons: 1) Technically, there is no reason why they should have to reconfigure their browser to run your applet, other than your inability to come up with a workable solution, and 2) Any trust that the user extends creates a potential security risk.

If you use a type 3 driver, you can place the database access server on the same host as your Web server (enabling the use of untrusted applets) and the actual database server on a separate machine. This can greatly reduce any potential performance problems with running the database server and the Web server on the same host. Several of the database access servers are integrated with a Web server and share the HTTP protocol port. This supports greater combined performance of the Web server and database access server. In addition, a separate database access server allows you to use multiple database servers that are located on independent hosts. Overall, type 3 drivers provide the most flexible and easy-to-use solution.

Form Handling and Database Access

Now that we've explored the virtues of different JDBC driver types as they relate to applets, we'll develop an applet that displays a survey form to a user and updates a database with the data entered by the user.

I'll be running this application under Windows 95. I've put together a small Microsoft Access 97 database (survey.mdb) that you can use to run the application on your computer. Use the 32-bit ODBC applet of Control Panel to set up the survey.mdb database as a System DSN. (DSN stands for data set name.) To do this, click the System DSN tab, click Survey as the Data Source Name, and use the Select button to select survey.mdb as the database. See Figure 46.1.

FIGURE 46.1. Setting up the Survey DSN.

I've installed the System DSN on a host named cx122974-a.cv1.sdca.home.com. I've also installed an evaluation copy of the IDS server (database access server) on this machine. The IDS server doubles as a Web server. Feel free to use your own Web server. However, if you use a type 3 JDBC driver and a database access server, you'll have a lot fewer installation hassles.

The Survey applet is shown in Listing 46.1. To run it, you'll have to tailor the first few lines of the updateDatabase() method for your JDBC driver and host name:

void updateDatabase(){

  try{

   Class.forName("ids.sql.IDSDriver");

   String url="jdbc:ids://cx122974-a.cv1.sdca.home.com:80/";

   url+="conn?dbtype=odbc&dsn='Survey'";

   Connection connection=DriverManager.getConnection(url);

As a minimum, you'll have to change cx122974-a.cv1.sdca.home.com to your database server's (or database access server's) host name. If you use a driver other than the IDS driver, you'll need to consult your driver's documentation for information on how to do this.

When you're finished with these changes, compile Survey.java and then copy the survey.htm (see Listing 46.2), Survey.class, and Survey$ButtonHandler.class files to the /ch46 directory off of your Web server's root.

Now you're ready for action. Use your browser to access the applet, as shown in Figure 46.2.


TIP: You can get a copy of the HotJava browser from http://java.sun.com/products/hotjava/.

FIGURE 46.2. The Survey applet opening display.

Use your Web server's host name instead of cx122974-a.cv1.sdca.home.com to access the applet. You can run your browser from the same host or a different host. If you've used a type 3 or 4 driver and copied the driver to the /ch46 directory, you'll be able to easily access the applet from any host. Otherwise, you'll have to install your JDBC drivers on the host from which you run your browser.

Fill out the Survey form as you want and click the Submit survey data button. Your survey results are added to your database. The applet then displays a Thank you! message, as shown in Figure 46.3.

To verify that the survey information was added to the database, start up Microsoft Access 97, open the survey.mdb database, and view the contents of the RawData table. The alternative is to wait until we create a general-purpose database search applet in the next section.

FIGURE 46.3. The applet completes the survey by thanking the user.

LISTING 46.1. THE Survey APPLET.

import java.applet.*;

import java.awt.*;

import java.awt.event.*;

import java.sql.*;

public class Survey extends Applet {

 Label blankLine = new Label(" ");

 TextField email = new TextField(50);

 TextField name = new TextField(50);

 Checkbox home = new Checkbox("I use Java at home.");

 Checkbox school = new Checkbox("I use Java at school.");

 Checkbox work = new Checkbox("I use Java at work.");

 Checkbox jdk12 = new Checkbox("I use the JDK 1.2 or later.");

 Checkbox standalone = new Checkbox("I develop standalone Java   Âapplications.");

 Checkbox applets = new Checkbox("I develop Java applets.");

 Checkbox database = new Checkbox("I develop database applications using   ÂJava.");

 Checkbox rmi = new Checkbox("I use remote method invocation in my   Âapplications/applets.");

 Checkbox intranet = new Checkbox("I use Java-based applications for the   Âintranet.");

 Checkbox internet = new Checkbox("I develop Java-based applications for   Âthe Internet.");

 Button submitButton = new Button("Submit survey data.");

 public void init() {

  setLayout(new GridLayout(21,1));

  Panel panels[]=new Panel[21];

  for(int i=0;i<panels.length;++i){

   panels[i]=new Panel();

   panels[i].setLayout(new FlowLayout(FlowLayout.LEFT));

  }

  submitButton.addActionListener(new ButtonHandler());

  panels[0].add(blankLine);

  panels[1].add(new Label("Please provide your e-mail address and name    Â(optional)."));

  panels[2].add(blankLine);

  panels[3].add(new Label("E-mail address:"));

  panels[3].add(email); 

  panels[4].add(new Label("Name:"));

  panels[4].add(name); 

  panels[5].add(blankLine);

  panels[6].add(new Label("Please check all that apply."));

  panels[7].add(blankLine);

  panels[8].add(home);

  panels[9].add(school);

  panels[10].add(work);

  panels[11].add(jdk12);

  panels[12].add(standalone);

  panels[13].add(applets);

  panels[14].add(database);

  panels[15].add(rmi);

  panels[16].add(intranet);

  panels[17].add(internet);

  panels[18].add(blankLine);

  panels[19].add(submitButton);

  panels[20].add(blankLine);

  for(int i=0;i<panels.length;++i) add(panels[i]);

 }

 void updateDatabase(){

  try{

   // Load IDS driver

   Class.forName("ids.sql.IDSDriver");

   String url="jdbc:ids://cx122974-a.cv1.sdca.home.com:80/";

   url+="conn?dbtype=odbc&dsn='Survey'";

   // Connect to database

   Connection connection=DriverManager.getConnection(url);

   Statement statement = connection.createStatement();

   String sql="INSERT INTO RawData VALUES (`"+email.getText()+"'";

   sql+=",'"+name.getText()+"'";

   sql+=toDigit(home);

   sql+=toDigit(school);

   sql+=toDigit(work);

   sql+=toDigit(jdk12);

   sql+=toDigit(standalone);

   sql+=toDigit(applets);

   sql+=toDigit(database);

   sql+=toDigit(rmi); 

   sql+=toDigit(intranet); 

   sql+=toDigit(internet);

   sql+=")";

   // Execute SQL

   statement.executeUpdate(sql);

   // Close database connection

   connection.close();

   displayPanel("Thank you!");

  }catch(Exception ex){

   displayPanel(ex.toString());

  }

 }

 String toDigit(Checkbox ch){

  boolean state = ch.getState();

  if(state) return ",'1'";

  else return ",'0'";

 }

 void displayPanel(String s){

  removeAll();

  setLayout(new BorderLayout());

  Font currentFont = getFont();

  setFont(new Font(currentFont.getName(),Font.ITALIC+Font.BOLD,18));

  add("Center",new Label(s,Label.CENTER));

  invalidate();

  doLayout();

 }

 class ButtonHandler implements ActionListener {

  public void actionPerformed(ActionEvent e){

   String s = e.getActionCommand();

   if("Submit survey data.".equals(s)){

    updateDatabase();

   }

  }

 }

}

LISTING 46.2. THE survey.htm FILE.

<HTML>

<HEAD>

<TITLE>Java Survey</TITLE>

</HEAD>

<BODY>

<APPLET CODE="Survey.class" WIDTH=550 HEIGHT=475>

[Survey applet]

</APPLET>

</BODY>

</HTML>

How the Survey Applet Works

Most of the code of the Survey applet is used to present the survey form to the user. This involves the creation and layout of the text fields and checkbox buttons that you saw in Figure 46.2.

The clicking of the Submit survey data button is handled by the ButtonHandler class. The updateDatabase() method is invoked to update the database with the information collected in the form.

The updateDatabase() method loads the JDBC driver and establishes a connection to the database specified in the database URL. It executes the "INSERT INTO RawData VALUE (...)" SQL statement with the values of the form fields inserted into the statement at the appropriate column positions. The toDigit() method is invoked to convert the values of the checkbox fields into strings of 1 or 0 so that they are in the correct format for Microsoft Access.


TIP: You can open the survey.mbd database with Microsoft Access 97 to see how the RawData table is structured.

Performing Database Searches

The Survey applet shows how form data can be collected and stored in a database. You typically would not let a user search a database that collected data from users via forms (mainly because of privacy considerations). In this case, you would use your database management system's reporting features to view the stored form data.

However, there are some cases where you'll want to provide users with the capability to search your databases. The SQL applet shown in Listing 46.3 provides the general capability to search (and update) selected databases. Although you most likely won't want to provide your users with this much flexibility, you can use the processing performed by this applet and tailor it to your specific search and update needs.

Because the SQL applet is a general-purpose applet, you'll only need to tailor the first couple of lines of processQuery() to work with your JDBC driver:

void processQuery(){

  try{

   Class.forName("ids.sql.IDSDriver");

   String url="jdbc:ids://"+hostPort.getText()+"/";

   url+="conn?dbtype=odbc&dsn='"+dsn.getText()+"'";

   Connection connection=DriverManager.getConnection(url);

Compile SQL.java and then copy SQL.class, SQL$ButtonHandler.class, and sql.htm (see Listing 46.4) to the /ch46 directory of your Web server.

Open sql.htm with your browser, as shown in Figure 46.4.

FIGURE 46.4. The SQL applet can query or update a variety of databases.

The SQL applet provides a great deal of flexibility for accessing different databases. You can use it to maintain your databases and test other database-enabled applets. It allows you to specify the host name and port of the database access server, the data set name, and an SQL statement. Click the Submit query button to send an SQL statement to the database server for execution. If the SQL statement returns a result, the results of the query are displayed in the text area.

Let's use the SQL applet to view the contents of the Survey data set. First, enter your database access server's host name and port number. Then enter the data set that you want to access. Finally, enter the SQL statement that you want to execute and click the Submit query button. The results of the query are displayed in the text area. Figure 46.5 shows a query that displays the contents of the RawData table of the Survey data set.

FIGURE 46.5. Using the SQL applet to view the Survey database.


LISTING 46.3. The SQL applet.

import java.applet.*;

import java.awt.*;

import java.awt.event.*;

import java.sql.*;

public class SQL extends Applet {

 Label blankLine = new Label(" ");

 TextField hostPort = new TextField(50);

TextField dsn = new TextField(25);
 TextField sqlStatement = new TextField(50);

 TextArea results = new TextArea(20,70);

 Button submitButton = new Button("Submit query.");

 public void init() {

  Panel topPanel = new Panel();

  Panel bottomPanel = new Panel();

  Panel topPanels[] = new Panel[4];

  for(int i=0;i<topPanels.length;++i){

   topPanels[i]=new Panel();

   topPanels[i].setLayout(new FlowLayout(FlowLayout.LEFT));

  }

  topPanel.setLayout(new GridLayout(4,1));

  topPanels[0].add(new Label("host:port "));

  topPanels[0].add(hostPort);

  topPanels[1].add(new Label("data set "));

  topPanels[1].add(dsn);

  topPanels[2].add(new Label("SQL statement "));

  topPanels[2].add(sqlStatement);

  submitButton.addActionListener(new ButtonHandler());

  topPanels[3].add(submitButton);

  for(int i=0;i<topPanels.length;++i)

   topPanel.add(topPanels[i]);

  bottomPanel.add(results);

  setLayout(new BorderLayout());

  add("North",topPanel);

  add("South",bottomPanel);

 }

 void processQuery(){

  try{

   // Load IDS driver

   Class.forName("ids.sql.IDSDriver");

   String url="jdbc:ids://"+hostPort.getText()+"/";

   url+="conn?dbtype=odbc&dsn='"+dsn.getText()+"'";

   // Connect to database

   Connection connection=DriverManager.getConnection(url);

   Statement statement = connection.createStatement();

   String sql=sqlStatement.getText();

   // Execute SQL and retrieve results

   boolean hasResults = statement.execute(sql);

   if(hasResults){

    // Retrieve result set

    ResultSet result = statement.getResultSet();

    if(result!=null) displayResults(result);

   }else results.setText("");

   // Close database connection

   connection.close();

  }catch(Exception ex){

   results.setText(ex.toString());

  }

 }

 void displayResults(ResultSet r) throws SQLException {

  // Get meta data from result set

  ResultSetMetaData rmeta = r.getMetaData();

  // Use the meta data to obtain information about the columns

  // of the result set

  int numColumns=rmeta.getColumnCount();

  String text="";

  for(int i=1;i<=numColumns;++i) {

   if(i<numColumns) 

    text+=rmeta.getColumnName(i)+" | ";

   else

    text+=rmeta.getColumnName(i);

  }

  text+="\n";

  while(r.next()){

   for(int i=1;i<=numColumns;++i) {

    if(i<numColumns)

     text+=r.getString(i)+" | ";

    else

     text+=r.getString(i).trim();

   }

   text+="\n";

  }

  results.setText(text);

 }

 class ButtonHandler implements ActionListener {

  public void actionPerformed(ActionEvent e){

   String s = e.getActionCommand();

   if("Submit query.".equals(s)){

    processQuery();

   }

  }

 }

}

LISTING 46.4. THE sql.htm FILE.

<HTML>

<HEAD>

<TITLE>SQL Interface</TITLE>

</HEAD>

<BODY>

<APPLET CODE="SQL.class" WIDTH=550 HEIGHT=475>

[SQL applet]

</APPLET>

</BODY>

</HTML>

How the SQL Applet Works

The main bulk of the SQL applet involves creating and laying out the form fields. The ButtonHandler class handles the clicking of the Submit query button by invoking the processQuery() method. The processQuery() method loads the JDBC driver and connects to the database access server specified in the hostPort text field and the data set specified in the dsn text field. The execute() method of the Statement class is invoked to execute the SQL statement specified in the sql text field.

If the execute() method returns a value of true, the getResultSet() method is invoked to retrieve a ResultSet object and the displayResults() method is invoked to display the ResultSet object in the text area.

The displayResults() method displays the column names separated by the vertical bar character (|). It then displays each row of the result table, also separating columns using the vertical bar.

Accessing Multimedia Databases

So far the database results that we've been displaying have consisted of boring text. Most databases allow you to store images, audio files, and other multimedia. The JDBC provides the capability to read arbitrary objects from the columns of a ResultSet object. However, in many cases, it is inconvenient to do so. To work with multimedia objects from a database, you must put the objects in the database in the first place. Your database server has to retrieve the objects and forward them to your applet. Then your applet has to convert the object to a format that is suitable to display.

Although each of these steps is technically feasible, there is an easier way. Instead of putting all of your multimedia files in your database, just put their names or URLs in the database. Move the actual multimedia files to your Web server. This will speed up your database access and let you use the applet methods for loading multimedia files.

The Multimedia applet in Listing 46.5 shows how easy it is to combine applets, databases, and multimedia. I've provided a multimed.mdb database with the descriptions of image and audio files and a \ch46\multimedia directory containing multimedia files. These multimedia files include circus photographs and audio files that comment on the photographs. The photographs were taken by a Kodak DC-20 digital camera and converted to JPEG format. The audio files were recorded by the Windows 95 sound recorder and converted to Sun audio (.au) format. Copy the \ch46\multimedia directory to your Web server so that it is \ch46\multimedia under your server's root. Use the Windows 95 Control Panel's 32-bit ODBC applet to set up the multimed.mdb file as a system data set, as shown in Figure 46.6.

FIGURE 46.6. Setting up the system data set for the Multimedia database.

In order to run the Multimedia applet, you need to tailor Multimedia.java to your host name and JDBC driver. First, modify the init() method to change cx122974-a. cv1.sdca.home.com to your host name:

public void init() {

  try{

   baseURL = new

    URL("http://cx122974-a.cv1.sdca.home.com/ch46/multimedia/");

  }catch(Exception ex){

  }

Second, change the first few lines of getQueryResults() to use your JDBC driver and substitute your host name for cx122974-a.cv1.sdca.home.com:

String[][] getQueryResults(String sql){

  try{

   Class.forName("ids.sql.IDSDriver");

   String url="jdbc:ids://cx122974-a.cv1.sdca.home.com:80/";

   url+="conn?dbtype=odbc&dsn='Multimedia'";

   Connection connection=DriverManager.getConnection(url);

Finally, compile Multimedia.java and copy the Multimedia.class, Multimedia$ChoiceHandler.class, Multimedia$MyCanvas.class, and multimedia.htm (see Listing 46.6) files to your Web server.

Open multimedia.htm with your browser, as shown in Figure 46.7. Be sure to substitute your Web server's host name for cx122974-a.cv1.sdca.home.com.

Select an item from the choice list. A photograph is displayed, and an audio file is played. You can browse through the circus photos for amusement.

This applet is very simple, and it was designed that way. However, it is a good example of how applets, databases, and multimedia files can be combined into an easy-to-use (and easy-to-develop) Web application.

FIGURE 46.7. The Multimedia applet's opening screen.


LISTING 46.5. The Multimedia applet.

import java.applet.*;

import java.awt.*;

import java.awt.event.*;

import java.sql.*;

import java.util.*;

import java.net.*;

public class Multimedia extends Applet {

 Choice choice = new Choice();

 MyCanvas canvas = new MyCanvas();

 Image photo = null;

 AudioClip audio = null;

 String imageName="";

 String audioName="";

 URL baseURL;

 public void init() {

  try{

   baseURL = new

    URL("http://cx122974-a.cv1.sdca.home.com/ch46/multimedia/");

  }catch(Exception ex){

  }

  String choices[]=getChoices();

  if(choices!=null){

   try{

    for(int i=0;i<choices.length;++i)

     choice.add(choices[i]);

   }catch(Exception ex){

    choice.add(ex.toString());

   }

  }

  setLayout(new BorderLayout());

  choice.addItemListener(new ChoiceHandler());

  add("North",choice);

  add("Center",canvas);

 }

 String[] getChoices(){

  String results[][] =

   getQueryResults("SELECT Description FROM Photographs");

  if(results == null){

   String err[]= {"No choices returned from database."};

   return err;

  }

  String column[] = new String[results.length];

  for(int i=0;i<results.length;++i)

   column[i]=results[i][0];

  return column;

 }

 String[][] getQueryResults(String sql){

  try{

   // Load IDS driver

   Class.forName("ids.sql.IDSDriver");

   String url="jdbc:ids://cx122974-a.cv1.sdca.home.com:80/";

   url+="conn?dbtype=odbc&dsn='Multimedia'";

   // Connect to database

   Connection connection=DriverManager.getConnection(url);

   Statement statement = connection.createStatement();

   // Execute SQL and obtain result set

   ResultSet results = statement.executeQuery(sql);

   // Obtain meta data about result set

   ResultSetMetaData rmeta = results.getMetaData();

   // Use meta data to get info about columns

   int numColumns=rmeta.getColumnCount();

   Vector v = new Vector();

   while(results.next()){

    String row[] = new String[numColumns];

    for(int i=0;i<numColumns;++i)

     // Retrieve results

     row[i]=results.getString(i+1);

    v.addElement(row);

   }

   int numRows=v.size();

   String resultTable[][] = new String[numRows][];

   for(int i=0;i<numRows;++i)

    resultTable[i]=(String[]) v.elementAt(i);

   // Close database connection

   connection.close();

   return resultTable;

  }catch(Exception ex){

   return null;

  }

 }

 void loadMedia(String s){

  String sql = "SELECT Image, Audio FROM Photographs";

  sql += " WHERE Description = `"+s+"'";

  String results[][] = getQueryResults(sql);

  if(results!=null){ 

   imageName = results[0][0];

   audioName = results[0][1];

   if(imageName != "TBD") photo=getImage(baseURL,imageName);

   if(audioName != "TBD") audio=getAudioClip(baseURL,audioName);

  }

  canvas.repaint();

 }

 class ChoiceHandler implements ItemListener {

  public void itemStateChanged(ItemEvent e){

   String s = choice.getSelectedItem();

   imageName="";

   audioName="";

   photo=null;

   if(audio!=null){

    audio.stop();

    audio=null;

   }

   loadMedia(s);

  }

 }

 class MyCanvas extends Canvas {

  public void paint(Graphics g){

   if(photo!=null) g.drawImage(photo,20,20,this);

   else g.drawString(imageName,10,20);

   if(audio!=null) audio.play();

   else g.drawString(audioName,10,40);

  }

 }

}

LISTING 46.6. THE multimedia.htm FILE.

<HTML>

<HEAD>

<TITLE>Multimedia Applet</TITLE>

</HEAD>

<BODY>

<APPLET CODE="Multimedia.class" WIDTH=540 HEIGHT=450>

[Multimedia applet]

</APPLET>

</BODY>

</HTML>

How the Multimedia Applet Works

The Multimedia applet displays a Choice object referenced by the choice variable and a MyCanvas object referenced by the canvas variable. The values of the Choice object are retrieved from the Photographs table of the Multimedia data set. The photo and audio variables are used to reference the Image object that is displayed on the MyCanvas object and the AudioClip object that is played. The baseURL variable is used to identify the URL of the directory where the image and audio files are located.

The init() method initializes the baseURL variable. (Be sure to substitute your server's URL.) It then invokes the getChoices() method to retrieve the list of photograph descriptions from the Multimedia data set. It then adds this list of descriptions to the Choice object referenced by the choice variable. The Choice object and MyCanvas objects are then laid out for display.

The getChoices() method invokes the getQueryResults() method with the "SELECT Description FROM Photographs" SQL statement. It then converts the results (if not null) from a two-dimensional String array to a one-dimensional String array. The String array is then returned to the caller.

The getQueryResults() method executes an SQL statement and returns the tabular results as a two-dimensional array. It begins by loading the JDBC driver and connecting to the Multimedia data set. It executes the SQL query and converts the result set to a Vector object containing a one-dimensional array for each row of the result set. A vector is used because there is no way to determine the size of the result set without stepping through it. The Vector object is then converted to a two-dimensional String array.

The ChoiceHandler class handles the ItemEvent associated with selecting an item from the choice list. It resets the values of the imageName, audioName, photo, and audio variables and then invokes loadMedia() with the value of the selected choice.

The loadMedia() method invokes getQueryResults() with the "SELECT Image, Audio FROM Photographs WHERE Description = `description'" SQL statement where the selected choice (as identified by the s variable) is substituted for description. This statement returns the values of the Image and Audio columns for the row with the Description column set to description. The imageName and audioName variables are updated based on the results returned. If the imageName is not "TBD", the getImage() method of the Applet class is used to load the image from the Web server and assign it to the photo variable. If the audioName is not "TBD", the getAudioClip() method of the Applet class is used to load the audio file from the Web server and assign it to the audio variable. Using getImage() and getAudioClip() is much easier than retrieving an image or audio file from the database. The repaint() method of the MyCanvas class is invoked to cause the image to be displayed and the audio file to be played.

The paint() method of the MyCanvas class checks to see if the photo variable references a valid image. If it does, the image then is drawn on the canvas. Otherwise, the image name is displayed. Similarly, the audio file is played if it is valid. Otherwise, its name is displayed.

JavaBlend and the Future of Java Database Programming

Having completed four chapters on Java database programming, you should be confident in your ability to develop your own Java database applications and applets. As you know by now, database programming is an involved process and requires expertise in working with database drivers, SQL, and the classes and interfaces of the java.sql package. The folks at JavaSoft realize the complex work that is involved in database programming, even with a language as easy to use as Java. They are in the process of developing a new product, JavaBlend, that will greatly simplify the process of building database applications using JDBC. JavaBlend will automatically map Java objects to information that is stored in databases. All operations on those objects will result in automatic querying and updating of the corresponding information stored in the databases. Once a Java object-to-database mapping is constructed, JavaBlend will free Java programmers from having to deal with the execution of SQL statements and the processing of result sets. JavaBlend will automatically generate, execute, and process the SQL statements corresponding to the methods that are invoked on Java objects. This will allow JDBC and SQL programming to be abstracted out of database application development. JavaBlend is not included in JDK 1.2.

Summary

In this chapter, you learned how to use the JDBC to connect applets with online databases. You learned what kind of JDBC drivers you should use to support zero installation database connectivity. You developed an applet that inserts form data directly into an online database. You then created a general SQL statement processing applet for searching databases and for testing other database-enabled applets. You also developed a multimedia photo album that explored approaches to integrating multimedia and databases. Finally, you learned about the future capabilities to be provided by JavaBlend.

This chapter concludes Part X, "Database Programming." In Part XI, "Server-Side Java," you'll learn how to develop server-side programs that implement CGI applications.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.