Chapter 15

Developing Database Applications and Applets with the JDBC

CONTENTS

• Why the Java Database Connectivity (JDBC) Specification?
• Storing Data for the Web
• Providing Access to Data
• The JDBC API
  • Database Requirements
  • The JDBC Interfaces
• Simple Database Access Using the JDBC Interfaces
  • The Counter Applet Example
• Result Sets and the Meta-data Interfaces
• Other JDBC Functionality
  • Transaction Management
  • Cursor Support
  • Stored Procedures
  • Multiple Result Sets
• Building a JDBC Implementation
  • Implementing the Interfaces
• Extending JDBC
• Designing a Database Application
  • Security Issues
  • Constructing a Three-tier Application
  • A Three-tier Bug Tracking System
  • Implementing a Three-tier Application with Java
• Summary

Perhaps the most exciting and powerful use of computers involve heavy duty storage of data and organized access to that data. For example, business users have long benefited from the ability to centrally store data for many different users to access. By connecting the millions of once-isolated home computers on a common network, the Internet has empowered developers to bring the advantages of client/server computing into the home. Until Java, such Web applications have primarily used CGI for connecting to databases.

For a few years now, the business solution to client/server development has been rapid application development tools such as Borland Delphi, Sybase PowerBuilder, and Microsoft Visual Basic. These applications provide both rapid GUI development through the use of drag-and-drop screen painting as well as a library of tools for accessing data housed in relational databases. Unfortunately, these applications are not well suited to Internet development, where platform independence, security, and distribution are all imperative.

A major strength of rapid application development products is their database connectivity. Ideally, you would have wanted the Order Entry System to access a real database and function off of that data. For example, the applet could report to a customer whether a certain item was available when the order was sent in. Or, even better, the applet could check and feature only those items that were in stock. But, as you saw, the Order Entry System by the time you finished with it was already becoming unwieldy. This chapter will provide you with the basis to include these database access and manipulation features into your own applets. I will also create a number of coded examples that will demonstrate these features and techniques that you can apply to your own Web programming.

Why the Java Database Connectivity (JDBC) Specification?

The Java specification, as well as the original release of the Java Developer's Kit (JDK), made no provisions for Java database access. To create access to a database, a programmer had to create an intermediary program between the database manager and the Java program that would access the data. To force a multitude of programmers all to write code that does essentially the same thing is truly a waste. Of course, each different programmer would do it his/her own way and suddenly you'd have a large mess on your hands. It is entirely inefficient not to have a standardized access format between Java code and database management systems.

Another reason that a standardized database interface proves necessary stems from security issues. By making a standard JDBC specification, database manufacturers can produce interfaces for their database, regardless of the internal storage format, so that the standardized interface could work with any Java program. This is a large bonus: Any Java program that implements database features can manipulate and access any database that has a JDBC-compliant interface. Also, the standardized classes and interfaces of the database interface classes can join the ranks of "trusted" classes that Web browsers can then safely use. Of course, home-brewed database interfaces would not be trusted or put into wide use and applets would not be able to use databases at all.

For these reasons, some kind of standardized database capability is necessary. In March 1996, Sun Microsystems, Inc. addressed this need with the draft release of the Java Database Connectivity specification, JDBC. As of this writing, the JDBC specification is still in a request-for-comments phase and is scheduled to be available soon in a full release. This chapter addresses the problem of database access in Java and demonstrates how to write code that conforms to and enhances the JDBC interface.

A hidden plus of the JDBC standard is that implementing database features in a Java program should make it easy to use that same implementation on another database. Of course, standardizing all of these functions comes at a price. It is less efficient to include the JDBC interface as another layer between your program and the database itself, but you should be accustomed to the tradeoff between standardization and simplicity and efficiency.

Currently, a number of database system developers have committed to developing JDBC-specification-compliant interfaces for their database systems. As of July 1996, database system producers ranging from Borland to tiny companies have stated their intent to develop JDBC interfaces immediately. As always, the latest information on Java database developments can be found at Java's home page:

http://java.sun.com/

Storing Data for the Web

Simple applets rarely need to perform database access. They are generally executed as on-off programs without the need to save any state information across executions. As Java developers move their work out of the realm of the simple applet, they will find the need to access some sort of data store. A popular yet simple example is the ubiquitous page counter. Of course, a page counter is simply an applet or CGI script that keeps track of how many times a particular page has been hit and displays that number on the page (see Figure 15.1).

Figure 15.1 : A Web page running a counter applet.

At the other extreme in complexity are the search engines with which you can perform keyword searches to find the most trivially related pages of information existing on the Internet. No matter how complex the application, the basic data management needs are the same. Many users need to gain access to the same piece of information and require an application built in such a way that it can access and/or modify centrally stored data. The developer must then take the following steps to provide users with access to data:

• Select and install a database management system (DBMS)
• Build data processing logic
• Build a user interface

Providing Access to Data

Whether Java or some other language is used to build these pieces, the DBMS used will have a direct impact on the implementation. A detailed discussion of database management systems is well beyond the scope of this book. When you choose among the various technologies, however, keep your needs (and your wallet) in mind and resist the dazzle of technology. Three basic data storage technologies that serve various needs follow:

• Object-oriented database (OODBMS)
• Relational database (RDBMS)
• Object-relational database (OORDBMS)

With the advent of the high multimedia content data storage needs of the Internet, developers have been more open to the idea of using object databases. In addition to being better suited to the unusual demands of storing multimedia data, object databases also help provide a true object paradigm from data store to client application.

What does this mean to you? You'll be concerned with a couple of issues, the first of which are the time and storage space requirements necessary for each different type of database manager. If you're trying to store a large amount of customer orders, for example, you should choose the appropriate database management system, such as a simple relational database. Or, if you're going heavy into multimedia, you'll want to use an object-based database manager.

The second issue is how the choice will affect how easily you can access the database. As you probably know, accessing a pure object database with any front-end tool is a challenge. Because the JDBC specification revolves around ANSI SQL-2 compliance and few object databases have SQL support, accessing an object database through Java will prove to be doubly challenging.

For developers not faced with the need to store complex data, any traditional relational databases should do exactly what you need. The grand trick to programming in Java with a relational database, or doing any object programming against a relational database, is mapping between the dynamic realm of objects and the static realm of pure data.

Paving the road between these two seemingly disparate technologies are the object-relational databases. For developers with complex data modeling needs, an object-relational database can provide the object modeling power of an object database while maintaining the ease of data access afforded by traditional relational systems.

The JDBC API

To provide a common base API for accessing data, Sun Microsystems, Inc., with support from a number of independent software vendors, developed JDBC. JDBC defines a number of Java interfaces to enable developers to access data independent of the actual database product being used to store the data. In theory, an application written against the basic JDBC API using only SQL-2 can function against any database technology that supports SQL-2. Of course, the key words are "in theory." The idea is that the interaction between your Java program and any database is standardized through the JDBC specification standard.

Database Requirements

You may store data in a wide variety of formats using various technologies. In addition to the three major modern database management systems, you will want to consider other systems, such as hierarchical databases and file systems. Any low-level API trying to find some common ground between all of these systems would be unsuccessful. JDBC mandates no specific requirements on the underlying DBMS, however. In other words, the JDBC doesn't care what's going on underneath the interface as long as it meets the ANSI SQL-2 standards. Rather than dictating what sort of DBMS an application must have to support JDBC, the specification places all of its requirements on the JDBC implementation.

Each platform and database-specific implementation of the JDBC by a software developer will provide a standardized environment in which your Java programs can operate. The JDBC specification primarily mandates that a JDBC implementation supports at least ANSI SQL-2 Entry Level. Because most common relational database systems and object-relational database systems support SQL-2, this requirement provides a reasonable baseline from which software developers can build Java database access. In addition, because SQL-2 is required only at the JDBC implementation level, that implementation can provide its own SQL-2 wrapper around non-SQL data stores.

The most important thing for you to remember when developing in Java is that your applications and applets are limited to accessing databases that support the ANSI SQL-2 standard. These databases naturally follow this standard or have had a specialized JDBC-compliant interface written for them.

The JDBC Interfaces

The JDBC defines eight interfaces that must be implemented to be JDBC-compliant:

• java.sql.Driver
• java.sql.Connection
• java.sql.Statement
• java.sql.PreparedStatement
• java.sql.CallableStatement
• java.sql.ResultSet
• java.sql.ResultSetMetaData
• java.sql.DatabaseMetaData

Figure 15.2 shows these interfaces and how they interact in the full JDBC object model.

The central object around which the whole concept revolves is the java.sql.DriverManager object. This object is responsible for keeping track of the various JDBC implementations that may exist for an application. If, for example, a system were aware of Sybase and Oracle JDBC implementations, the DriverManager would be responsible for tracking those implementations. Any time an application desires to connect to a database, it asks the DriverManager to give it a database connection using a database URL through the DriverManager.getConnection() method. Based on this URL, the DriverManager searches for a Driver implementation that accepts the URL. It then gets a Connection implementation from that Driver and returns it to the application.

Figure 15.2 : The JDBC object model.

The DriverManager is the only instantiated class provided by JDBC other than exception objects and a few specialized subclasses of java.util.Date. Additional calls made by an application are written against the JDBC interfaces that are implemented for specific DBMSs.

java.sql.Driver

A Driver is essentially a Connection factory. The DriverManager uses the Driver to determine whether it, the DriverManager, can handle a given URL. If it can handle the URL, it should create a connection object and return it to the DriverManager. Because an application only indirectly references a Driver through the DriverManager, applications are rarely concerned with this class.

java.sql.Connection

A Connection is a single database session. As such, it stores state information about the database session it manages and provides the application with Statement, PreparedStatement, or CallableStatement objects to make calls during the session.

java.sql.Statement

A Statement is an unbound SQL call to the database. It is generally a simple UPDATE, DELETE, INSERT, or SELECT for which no columns need to bind to Java data. It provides methods for making such calls and returns to the application the results of any SELECT statements, or the number of rows affected by an UPDATE, DELETE, or INSERT.

Statement has the subclass PreparedStatement, which is in turn subclassed by CallableStatement. A PreparedStatement is a precompiled database call that requires binding parameters. An example of a PreparedStatement might be a stored procedure call that has no OUT or INOUT parameters. For stored procedures with OUT or INOUT parameters, an application should use the CallableStatement interface.

java.sql.ResultSet

A SELECT query returns data to an application by implementing this interface. Specifically, the ResultSet object enables an application to retrieve sequential rows of data returned from a previous SELECT call. It provides a multitude of methods that enable you to retrieve a given row as any data type to which it makes sense to convert it. For example, if you have a date stored in the database as a datetime, you can retrieve it through the getString() method to use it as a string.

The Meta-data Interfaces

Meta-data is data about data. Specifically, it is a set of data that gives you information about the database and data retrieved from the database. Java provides two meta-data interfaces: java.sql.ResultSetMetaData and java.sql.DatabaseMetaData. The ResultSetMetaData provides a means for getting information about a particular ResultSet. For example, among other things, it provides information on the number of columns in the result set, the name of a column, and its type. The DatabaseMetaData interface, on the other hand, gives the application information on the database in general, such as what levels of support it has, its name, version, and other features.

Simple Database Access Using the JDBC Interfaces

An application for which database independence is paramount, in other words, one in which you want to write a program that can use different databases using a JDBC interface, should be written to the JDBC specification without using database-specific calls and without making use of SQL that is not part of the ANSI SQL-2 standard. In such code, no reference should be made to a specific implementation of JDBC. Writing a simple database application using only JDBC calls involves the following steps:

1. Ask the DriverManager for a Connection implementation.
2. Ask the Connection for a Statement or subclass of Statement to execute your SQL.
3. For subclasses of Statement, bind any parameters to be passed to the prepared statement.
4. Execute the Statement.
5. For queries, process the ResultSet returned from the query. Do this for each result set (if you have multiple result sets) until none are left.
6. For other statements, check the return value for the number of rows affected.
7. Close the Statement.
8. Process any number of such statements and then close the connection.

The Counter Applet Example

The counter applet discussed earlier in this chapter provides a simple example of JDBC programming. Using the JDBC interfaces, this applet connects to a database, determines how many times the page on which it appears has been hit, updates the page to reflect the new hit, and displays the number of hits. To use this example, you need a database engine to run your database and a JDBC driver to access that database engine. If you do not have a database engine, download mSQL and JDBC, which are both free for noncommercial use. Links to mSQL and the JDBC class may be found through

http://www.imaginary.com/Java/.

In addition, you need to create a table called t_counter with the fields counter_file (chAR(100), PRIMARY KEY) and counter_num (INT, NOT NULL). The following mSQL script creates the table:

DROP TABLE t_counterpg


CREATE TABLE t_counter(

        counter_file    chAR(100)    PRIMARY KEY,

        counter_num     INT          NOT NULL
)pg

The applet consists of two classes, Counter and Database. The Counter class is the subclass of applet that provides the user interface to the applet. It contains two instance variables: count, which is the number this applet is supposed to display, the number of page hits, and database, which is an instance of the Database class that provides wrappers for the JDBC access needed by the applet.

Counter does not define any new methods; rather, it simply overrides the java.applet.Applet.init() and java.applet.Applet.paint() methods. The init() method is used to create a Database instance and find out from it what the page hit count is for display. The paint() method displays the page hit count.

This interesting JDBC-related work is all encapsulated inside the Database class. This class has a single instance variable, connection, which is an instance of a JDBC Connection implementation. The connection variable is initialized in the Database class constructor:

public Database(String url, String user, String pass)
 throws java.sql.SQLException  {

     connection = DriverManager.getConnection(url, user, pass);

}

By getting an instantiated Connection object, the applet is ready to access whatever database it needs.

The applet uses the getCount() method to calculate how many page hits this particular access to the Web page represents. That seemingly benign query actually represents several steps:

1. Create a Statement object.
2. Formulate and execute the SELECT query.
3. Process the result.
4. Increment the hit count.
5. Format and execute an UPDATE or INSERT statement.
6. Close the Statement and Connection objects.

The Statement is created through the JDBC call:

java.sql.Statement statement = connection.createStatement();

You want the number of hits for this page from the t_counter table:

sql = "SELECT counter_num FROM t_counter " +

     "WHERE counter_file = '" + page + "'";

result_set = statement.executeQuery(sql);

The result_set variable now holds the results of the query. For queries that return multiple rows, an application loops through the next() method in the result set until no more rows exist. This query should only return one row with one column, unless the page has never been hit. If the page has never been hit, the query will not find any rows and the count variable should be set to 0:

if( !result_set.next() ) count = 0;

Otherwise, you need to retrieve that row into the count variable as an integer:

else count = result_set.getInt(1);

After incrementing the count to reflect this new hit, close out the Statement object and get a new one to prepare for the UPDATE:

count++;

statement.close();

statement = connection.create Statement();

If this is the first time the page is being hit, the applet needs to INSERT a new row into the database. Otherwise, it should UPDATE the existing row:

if( count == 1 ) {
    sql = "INSERT INTO t_counter " +

           "(counter_file, counter_num) " +

            "VALUES ('" + file + "', " + count + ")";
}

else {
    sql = "UPDATE t_counter " +

          "SET counter_num = " + count + " " +

           "WHERE counter_file = '" + file + "'";
}

statement.executeUpdate(sql);

The method then cleans up and returns the hit count.

Listing 15.1 puts the whole applet together.

Listing 15.1. The Counter applet.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.awt.Graphics;

public class Counter extends java.applet.Applet {

    Database db;

    String count;

    public void init() {

        String driver = getParameter("driver");

        String url = getParameter("url");
        String user = getParameter("user");

        String pass = getParameter("password");
        String page = getParameter("page");

        try {

            Class.forName(driver).newInstance();

            db = new Database(url, user, pass);

            count = db.getCount(page);

        }
   catch( java.sql.SQLException e ) {

            e.printStackTrace();
            count = "Database exception";

        }
   catch( Exception e ) {

            e.printStackTrace();

            count = "Unable to load driver";

        }

    }



    public void paint(Graphics g) {


        g.setFont(new java.awt.Font(getParameter("font"),
                                          &nbs p;     java.awt.Font.BOLD, 14));

        g.drawString(count, 5, 15);

}

}



class Database {

    private Connection connection;


    public Database(String url, String user, String pass)
 throws java.sql.SQLException {

    connection = DriverManager.getConnection(url, user, pass);

    }



public String getCount(String page) {

        int count = 0;


        try {
            java.sql.Statement statement =
                 connection.createStatement();

            java.sql.ResultSet result_set;

                   String sql;

           sql = "SELECT counter_num FROM t_counter " +
                                "WHERE counter_file = '" +
                page + "'";


          result_set = statement.executeQuery(sql);

            if( !result_set.next() ) count = 0;

                           else count = result_set.getInt(1);


            count++;

           statement.close();


            statement = connection.createStatement();

            if( count == 1 ) {

                           sql = "INSERT INTO t_counter " +
                                 "(counter_file, counter_num) " +
                                 "VALUES ('" + page + "', " +count+ ")";

            }  else {

                          sql = "UPDATE t_counter " +
                                "SET counter_num = " + count + " " +
                                "WHERE counter_file = '" + page + "'";

            }


            statement.executeUpdate(sql);
                   statement.close();
                   connection.close();

           }
  catch( java.sql.SQLException e ) {

            e.printStackTrace();

        }


    return ("" + count);

    }

}

Result Sets and the Meta-data Interfaces

In simple applications such as the counter applet, there is no need to perform any tricks with the results from a query-the data is simply retrieved sequentially and processed. More commonly, however, an application will need to process the data in a more complex fashion. For example, a set of classes might want to deal with data on a more abstract level than the Database class from the counter example. Instead, such classes might not know exactly what data is being retrieved. They can query the meta-data interfaces to process intelligently such data that they would otherwise not know. Listing 15.2 shows a generic database view class that is populated with database objects based on a result set.

Listing 15.2. A generic database view class.

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Hashtable;
import java.util.Vector;


public class View {


    private Vector objects;
    public void populate(ResultSet result_set, String cl) {


        ResultSetMetaData meta_data;

        int i, maxi;



        try {

            objects = new Vector();

            meta_data = result_set.getMetaData();

            maxi = meta_data.getColumnCount();

            while( result_set.next() ) {


                Hashtable row = new Hashtable();
                DataObject obj;
                for(i=1; i<=maxi; i++) {

                    String key;

                    Object value;

                    int t;


                           key = meta_data.getColumnLabel(i);

                    t = meta_data.getColumnType(i);

                    value = result_set.getObject(i, t);
                                        row.put(key, value);
                        }

                          obj = (DataObject)Class.forName(cl);
                                     obj.restore(row);

                         objects.addElement(obj);

            }

         }
  catch ( java.sql.SQLException e ) {

                e.printStackTrace();

                objects = new Vector();

                return;

        }

    }

}

In the View class, reference is made to a DataObject class that implements a restore(java.util.Hashtable) method not listed.

Because many applications will use this generic class, the class knows nothing about the queries it is executing. Instead, it takes any random result set and assumes that each row corresponds to an instance of the class named by the second parameter to populate().

To get the information it needs for performing the data retrievals, the populate() method first obtains the meta-data object for this result set. This method is specifically interested in knowing how many columns, as well as the names of the columns, are in the result set.

To store the columns in a Hashtable object that the DataObject object can use for restoring itself, all data must be in the form of objects. Thus, for each column in the result set, the DataObject finds its data type from the meta-data and retrieves the column as an object. The final step is to store it in the Hashtable.

Other JDBC Functionality

The JDBC provides functionality beyond the commonly used methods already discussed in terms of the following features:

• Transaction management
• Cursor support
• Stored procedure support
• Multiple result set processing

Transaction Management

JDBC implementations should default automatically to committing transactions unless the application otherwise requests that transactions require an explicit commitment. An application may toggle the automatic commit of the JDBC implementation it is using through the Connection.setAutoCommit() method. An example follows:

connection.setAutoCommit(false);

Of course, by not setting the AutoCommit attribute or by setting it to true, the JDBC implementation will make certain that the DBMS commits after each statement you send to the database. When set to false, however, the JDBC implementation requires specific commits from the application before a transaction is committed to the database. A series of statements executed as a single transaction would look like this:

public void add_comment(String comment) {


    try {

        Statement s;

        ResultSet r;

        int comment_id;


        connection.setAutoCommit(false);

        s = connection.createStatement();

        r = s.executeQuery("SELECT next_id " +

                    "FROM t_id " +

                           "WHERE id_name = 'comment_id'");


        if( !r.next() ) {

            throw new SQLException("No comment id exists " +
                                          &nbs p;          "in t_id table.");

        }


        comment_id = r.getInt(1) + 1;

               s.close();

                      s = connection.createStatement();

        s.executeUpdate("UPDATE t_id " +

            "SET comment_id = "
            + comment_id + " " +

                        "WHERE next_id = 'comment_id'");

        s.close();

        s = connection.createStatement();


        s.executeUpdate("INSERT INTO t_comment " +
                                         "(com ment_id, comment_text) " +

            "VALUES(" + comment_id + ", '" +

            comment + "')");
        connection.commit();

        }
    catch( SQLException e ) {

            e.printStackTrace();

            try {
            connection.rollback();

            } catch( SQLException e2 )  System.exit(-1);

        }

}

This method adds a comment to a comment table for some applications. To insert the new comment, the method needs to generate a new comment_id and then update the table for generating IDs so that the next one will be one greater than this one. Once the program has an ID for this comment, it then inserts the comment into the database and commits the entire transaction. If an error occurs at any time, the entire transaction is rolled back.

JDBC currently has no support for a two-phase commit. Applications written against distributed databases require extra support to allow for a two-phase commit.

Cursor Support

JDBC provides limited cursor support. It enables an application to associate a cursor with a result set through the ResultSet.getCursorName() method. The application can then use the cursor name to perform positioned UPDATE or DELETE statements.

Stored Procedures

Stored procedures are precompiled SQL statements stored in the database that enable faster execution of SQL. JDBC supports stored procedures through the CallableStatement class. In the counter applet, you could have used a stored procedure to update the page hit count in the following way:

CallableStatement s = connection.prepareCall(
            "{call sp_upd_hit_count[?, ?]}");

s.setStringParameter(1, "file");

s.setIntParameter(2, count);

s.executeUpdate();

Multiple Result Sets

In some cases, especially with stored procedures, an application can find a statement by returning multiple result sets. JDBC handles this through the method Statement.getMoreResults(). Although result sets are left to be processed, this method returns true. The application can then obtain the next ResultSet object by calling Statement.getResultSet(). Processing multiple result sets simply involves looping through as long as Statement.getMoreResults() returns a value of true.

Building a JDBC Implementation

Building a JDBC implementation requires a lot more in-depth knowledge of both your DBMS and the JDBC specification than does simply coding to it. Most people will never encounter the need to roll their own implementation because database vendors logically want to make them available for their product. Understanding the inner workings of JDBC can help advance your application programming, however.

JDBC is a low-level interface that provides direct SQL-level access to the database. Most business applications and class libraries abstract from that SQL-level access to provide such features as object persistence and business-aware database access. A narrow example of such an abstraction is the Database class from the counter example.

The ideal object method of accomplishing these goals is to reuse existing JDBC implementations for the DBMS in question and to add custom interfaces on top of those implementations. If the DBMS is an oddball DBMS, or perhaps if you are concerned about the available implementations that exist, writing one from scratch makes sense.

Implementing the Interfaces

The first concern of any JDBC implementation is how it will talk to the database. Figure 15.3 illustrates the architecture of three possible JDBC implementations. Depending on the design goals in question, one of these methods will suit any JDBC implementation:

Figure 15.3 : Possible JDBC implementation architectures.

• A native C library
• A socket interface
• Extending a vendor JDBC implementation

Of course, extending a vendor JDBC implementation is not really the same as building a JDBC implementation. Because a key to any object-oriented project is reusing code instead of building from scratch it is listed here.

With all three architectures, the application is apparently isolated from the actual communication mechanism. In truth, however, the native C library method places severe restrictions on any application using a JDBC implementation built on top of it. Because it uses native calls, it is naturally not portable across operating systems. In addition, due to virtual machine restrictions on most browsers, native calls are either severely limited or fully restricted.

To use one of these mechanisms for database communication, you need to construct the four basic interfaces: java.sql.Driver, java.sql.Connection, java.sql.Statement, and java.sql.ResultSet. These interfaces will provide minimum functionality so that you can test against simple queries and updates. Once these interfaces are functional, the implementation needs the meta-data interfaces as well as the Statement subclasses to be complete and JDBC-compliant.

Extending JDBC

Nothing requires an application to use the JDBC interface to access a database. In fact, before JDBC, developers programmed to Java classes written specifically to go against several major database engines. JDBC isolates the database access behind a single interface. This isolation provided developers with the ability to write database access in Java without having to know which database engine their application is actually hitting. With a single prevalent database API, finding people with experience programming against it proves much simpler than finding people to program against a proprietary API. JDBC is, however, a low-level specification that requires developers to write both SQL code as well as Java code.

Both examples in this chapter demonstrate two different ways in which you can extend JDBC. In the counter applet, a database class was created as a wrapper around the JDBC implementation. The applet itself was divided into a representational portion, the Counter class, and a functional portion, the Database class. If you make changes to the visual representation, such as making the hit count appear through an odometer graphic, you won't have to make changes to the functional logic because it is isolated in a separate class. In fact, if the applet were more complex, requiring multiple developers, all the SQL would still be isolated in a class specifically interested in the functional behavior of the application. This reduces the number of people who need to write SQL code.

The View class example was a more abstract way of extending JDBC. The View class assumes that rows in result sets translate into business objects. In an application using this class, View objects are created to make JDBC calls and populate the applications with meaningful objects.

Another manner in which you can extend JDBC is to take advantage of database-specific features. Although it is prudent to question the need to make use of any proprietary features of a given DBMS, it is equally important that you do not ignore the extra power a specific DBMS gives you. It is, after all, very rare that an application actually needs to switch database engines.

Designing a Database Application

Knowing the JDBC API and coding cute applets is naturally just the start to database programming in Java. To harness the advantages of Java, application designers need to be able to address the design issues Java raises. The entire Java paradigm empowers developers to write database applications and applets using architectures that before were either very complex or simply not supported by other tools. Two such buzzwords that have been flying around the client/server world for a while are distributed objects and three-tier client/server.

Security Issues

Before going off the edge and into the deep end, Java does put some restrictions on applets for security reasons that can appear to be particularly limiting to the database developer. The following two particular applet restrictions affect database programmers:

• Limited access to native libraries
• Limited network access

The native call limitation affects programmers who need to use some sort of C- or operating system-level library to design an applet. This is especially troublesome to applet writers who take advantage of a database-specific feature not supported outside of native calls.

To veteran client/server developers, however, the most troubling idea is likely that your Web server must be on the same machine to which your applet connects for database access. Specifically, most Java virtual machines restrict applets from connecting to any machine except the host that served the applet. The applet cannot connect directly to any local or third-machine databases. As limiting as this particular restriction seems, a three-tier architecture provides a liberating solution.

Constructing a Three-tier Application

Two-tier applications tend to push a lot of processing onto the client machines. This architecture poses several problems:

• Client-side resource requirements balloon with the extra processing needs. It is not uncommon to find business applications requiring Pentiums with 32M of RAM.
• User interface and business processing tend to get rolled together, especially with the rapid application development tools on the market. With the user interface so closely tied to business processing, changes to one end up having a direct impact on the other, making maintenance a headache.
• With all this redundant processing occurring on many client machines rather than in a central location, new applications are forced to reinvent the wheel when dealing with the same business processing.

With the guaranteed execution environment of the Java virtual machine and an easy-to-use Internet socket interface, Java is actually well suited to implementing three-tier systems. A three-tier application is one in which a third application layer exists between the client and server layers of traditional two-tier client/server development. This middle layer has a wide variety of uses depending on the application.

In the three-tier architecture, the middle layer separates business processing from the visual representation of data. This layer, called the application server, is responsible for knowing how to find and manipulate business data. The client evolves into a much leaner application, responsible only for retrieving information from the application server and displaying it on the screen.

In addition to removing a huge processing burden from client machines, this application server can be used to consolidate enterprise-wide business rules.

Where business rules had to be rewritten for each two-tier application thrust on the desktop, application servers process business rules in a single place for multiple applications to use. When the business rules change, a change to the application server takes care of that change for all the applications being run by the business.

Of specific interest to Java developers is the ability to hide any knowledge of the database server from the client. Because Internet clients view the applet or application as interfacing with a single application server, you can use that application server to determine such issues as where the data really exists. Additionally, this back-end independence enables applications to scale much easier across CPUs. Figure 15.4 shows a three-tier architecture.

Figure 15.4 : A three-tier Java applet or application.

A Three-tier Bug Tracking System

The application server forms the core of a three-tier architecture. In it, the business rules are defined and processed. Implementing the counter using a three-tier architecture would naturally be overkill. Instead, the ideal application for a three-tier design is one in which some manipulation of data occurs or where the data can be viewed in multiple fashions (or even better, by multiple applications). The first step in building an application server would thus be to identify the data processing needs of the application.

Implementing a Three-tier Application with Java

Figure 15.5 shows a bug tracking application implemented as a three-tier Java application.

Figure 15.5 : A bug tracking system using a three-tier architecture.

The only processing done on the client is the painting of GUI widgets and user data entry. On the other end, the database server runs on a machine otherwise inaccessible to the client applet. The application server bridges this gap by finding desired data, mapping it from its relational state into objects, and performing operations on those objects.

With any three-tier architecture, the greatest programming challenge is getting the three layers to communicate with one another. JDBC or some similar set of database access classes should handle the application server-to-database server communication in a manner transparent to the application developer. The client-to-application server solution is still left wanting.

The two best methods for providing such communication in Java are Java sockets or distributed objects.

Compared to sockets from other languages, Java sockets are quite simple to use. Sockets, however, force the developer to make esoteric decisions about exactly what is being communicated between client and application server because method calls and object passing are better handled by the distributed objects solution. A socket solution generally best fits an application when the scope of communication is limited and well-defined. The bug tracking system would be best implemented in this manner.

Distributed objects provide the more elegant solution. From the developer's point of view, the application server objects appear to be part of the same application as the client, just residing on a central server and available to other applications simultaneously. The developer handles communication simply through method calls.

Summary

Although the original Java release did not address the issue of database access, the JDBC specification attempts to address this issue by defining a set of interfaces that can give applications access to data independent of the DBMS being used to store that data. Though this back-end independence can be very liberating, it is important to balance it with the advantages of the DBMS being used.

Many books cover only the subjects of database application design and programming. This chapter does not attempt to delve into those matters; instead, it focuses on the application of Java to database programming.

Programmers interested in using Java to write database applications should become familiar with the general subject matter.

In spite of the vastness of the subject matter, this chapter should whet your appetite for database programming and prepare you at least enough to write simple applets and applications. Much of the Java experience you already have translates into many of the issues specific to Java database programming. For example, applets written to use a database must work around the strict security limitations of Java virtual machines. Using the basics of a three-tier architecture can help an applet get around this limitation while giving it greater functionality. It is, however, important not to overdesign a simple applet just for the sake of doing a three-tier design.