HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

It is not easy to query the contents of a database without proprietary front end tools or a database-aware IDE. A database-aware toolkit should be able to connect to and work with a variety of databases (local and remote, application and corporate) without a shift in how we view the contents of different databases.

Java and JDBC allow the builder to abstract the viewing of the data from the implementation of the database and database queries that yield the data for viewing. A number of IDEs allow the builder to query the contents of a database as part of the database component integration , but using an IDE is an expensive option for someone who would like a simple tool for viewing and maintaining data in different databases.

This article presents a simple database browser that allows access to any database that supplies either an ODBC or JDBC driver, and acts as a simple but useful interface to the JDBC API. The browser can list the various types of table as well as the tables and table columns, and maintain data in those tables via SQL, which is entered free-form.

Most of this article addresses the design and build of the browser. The aim is to produce a complete tool written in 100% Java 1.1 (AWT and JDBC) with no third party add-ons, and which is scalable enough to allow a new presentation layer to be bolted on. It is also reasonable to expect browser components to behave as middleware components on a multi-tier platform so that the browser can be imported as a Java Beanª into any Bean-complaint IDE. This will allow the 'Data Browser Bean' to interact with GUI and non-GUI components in a more ambitious distribution of functionality on a broader corporate platform. Although the browser does not implement the Bean interface, this is a small task for those interested in pursuing this extension.

Functionality
Essentially, the browser is a front end to the JDBC API. Based on practical requirements, the current version interfaces to a subset of the more important JDBC methods. The current user interface implements a set of three tabs using the Java CardLayout class:

  • Query capture and display
  • Table and column listing
  • Connection detail
The user clicks on a button and the relevant tab detail is displayed.

Query Capture and Display
The user enters an SQL query which is sent to the database for execution. The query can be any SQL which the JDBC/ODBC driver and database supports; for example, select, insert, update, delete. The user can limit the number of rows displayed from a query.

Figure 1 shows a query entered into the query window; when the 'Go' button is clicked, the query is executed and the results displayed in the Panel above. The user can limit the number of rows returned from the ResultSet by entering a value in the 'Row Count' text box.

Figure 1
Figure 1:

Note that the projection name (i.e., the name defined in the query), not the table column name, is displayed. If no projection name were nominated, the table column name would be displayed.

Queries that are not select statements (e.g., Update, Insert) will return an update count, and a confirmatory message is appended to the panel display. Figure 2 shows the update message when the update query is executed.

Figure 2
Figure 2:

Table and Column Listing
The user can select a qualified list of tables, including Catalogs, Schema and Table Types. From a list of tables, select the column detail for a specific table. Various types of tables can be displayed; the column detail is the complete column specification returned by the JDBC API.

Figure 3
Figure 3:  Column detail of the Products Table

Connection Detail
The user can select the driver and database URL to connect with. The default is the JDBC/ODBC bridge supplied in JDK 1.1, and the jdbc:odbc protocol. Any valid driver can be entered and the database URL can be remote.The user can display important meta data information about the database connection and driver information.

Figure 4
Figure 4:  Connection tab after the Details Button is clicked.

JDBC Drivers and the JDBC/ODBC Bridge
The browser will accept the class name of any JDBC driver you have, but defaults to sun.jdbc.odbc.JdbcOdbcDriver, which is the JDBC/ODBC bridge supplied with JDK 1.1.

JDBC/Net drivers enable a connection from the client, thru a JDBC driver, to a comms server which then connects to a database server on a remote host. This is required when the JDBC driver does not inherently support the protocol required between the client and the server directly. The local comms server will handle the protocol of database requests and, in some cases, will make transparent the different versions of products on multiple platforms. Generally, a name service is provided on all cooperating hosts to direct the clients to host ports where the service is listening. For example, you could use JDBC to connect to an Ingres/NET comms server, which then establishes a connection to a remote comms server, which in turn connects to the remote database server, and a virtual circuit is then established. In the process, the local and remote name servers are queried to find the host/port addresses of the cooperating services.

JDBC drivers can also handle the communication protocol directly. In this case, the JDBC driver connects directly to the database server, local or remote.

For those data sources that do not have a JDBC driver, the JDBC/ODBC bridge allows an ODBC data source to be interrogated by the JDBC API. The bridge software converts the JDBC calls into ODBC calls.

The Database class in the Database Browser handles the database connection. The User Interface passes an instruction to the database via the protocol handler. The connectDB method uses the instruction operands as arguments to the JDBC API.

To use the ODBC data sources, you need to create a new System DSN from the Windows Control Panel. The name you assign to the database is the name you enter into the Database URL field in the Connection Tab. For example, the screenshots use the NorthWestTrade name, which maps down to the nwind.mdb Access database provided by Microsoft.

The driver is loaded using the class.forname() method call. The getConnection() method call establishes a link to the database through the loaded driver. Once a link is created, a Statement object is created which is the handle for executing queries as in Listing 1.

Components (Interfaces and Classes)
The various components of the data browser are shown in Figure 5 and summarized in Tables 1 and 2.

Figure 5
Figure 5:

Table 1

Table 2

Design and Build
To support a component approach, the design of the browser has the following design constraints which are explained in more detail in the following section.

  • Separation of presentation layer from database layer
  • Use of interfaces to support component integration
  • Implementation of Observer/Observable from a model/view design pattern
  • Protocol handling with the use of a protocol client and server
  • Tight coupling of component to event using anonymous classes
  • Strict use of core Java 1.1 AWT classes, including a CardLayout for a Tab Panel
Builders unfamiliar with anonymous classes should find the examples here straightforward.

Separation of Presentation Layer from Database Layer
The user interface classes are separate from the database classes. The user interface communicates with the service classes (DataBase) via the protocol handler. The database performs the requested service and obtains a handle to two items: a results component to display JDBC API results and a log device to contain standard error output.

There are three ways to approach the handling of query results from the database:
a. the database returns query results as Java properties, e.g. Vectors and arrays
b. The database class writes the results to a device owned by the presentation layer.
c. The database class invokes a method provided by the presentation layer.

In the actual implementation:
a. is used to store meta values from the database in a class called ResultsInfo.
b. is used to write the API results. This includes the results of queries and metadata API calls.
c. is used for standard error output; since standard error is assumed to be a property of the user interface, the user interface must provide a class that implements the LogDevice interface.

Use of a log device that is visible to the database class yields a number of benefits. Exceptions can be handled and reported within the database service itself. If this were not the case, then either the protocol handler or the user interface would have to catch the exceptions thrown by the database. This reduces the granularity of error handling, leading to a lack of flexibility. Some of the SQL Exceptions thrown are not malevolent; a JDBC API method may not be supported by the ODBC driver (e.g., getSchemas()). In this case, we want to continue in the database server, not return to the client. We also want to report this fact to standard error.

Use of Interfaces to Support Component Integration
A small number of interfaces are used to ensure that a design thread is followed through in each relevant component. The interfaces perform two functions:

  • Specification of constant values so that interacting components can share them (interfaces DBConstants and DBProtocol)
  • Allow method signatures to be specified so that one component can invoke a method whose detail is completed by another component (interface LogComponent)

Implementation of Observer/Observable from a Model/View Design Pattern
The User Interface (DataBrowser class) is an observer of the Data (DataBase class). On a client/server platform with multiple client access to the data, if one client updates the data then the other clients may need to be informed and act accordingly. This means that the user interface is an observer of the data; the data is 'Observable'. Although the implementation described here does nothing with this paradigm, for extensibility the user interface has been defined as an observer and the database as observed. The Observer 'update()' method is a no-op method. The Observed 'notify()' method is similarly a no-op method. If the DataBase requires all registered observers to update their state based on a change to the database, then the DataBase should notify() all observers; the observers (the user interface) should then execute the update() method. Note that the data passed is still bound to the protocol, so a ProtocolData is used (see Listing 2).

Protocol Handling with the use of a Protocol Client and Server
Most client/server and n-tier architectures implement some form of protocol handling between the clients and the servers (either database servers or middle-tier application servers which communicate with the persistent storage servers). This allows the clients to abstract the operational functionality from the implementation of that functionality. On a distributed client/server platform, a communications server is usually required to take requests from the client and pass them on to the comms server on the server machine, which then passes the request on to a database server. The requests are formalized into a protocol which consists of an instruction with data or, more formally, an operator with operands. Client components which interact with a service on a remote host need to understand how to package up the instructions, or protocol data, into a suitable form.

The data browser uses a 'protocol client' class to package up a protocol instruction into a 'protocol data' class which then forwards this instruction to the 'protocol server' class to interpret, the interpretation being a call to a DataBase method to execute a JDBC method call.

What seems like overengineering a simple procedure yields dividends when a host implements a different service. The client will then require access to a different protocol client to connect to this service, the simple cost being the implementation of a new protocol interface to parameterize new service instructions instead of hardcoding the service calls manually.

The ProtocolClient class contains a method to package up operand values and send a protocol instruction to the protocol server. The example in Listing 3 takes the query entered by the user, and the row limit, as operands, and sends an execute query instruction to the ProtocolServer object.

The ProtocolServer class in Listing 4 contains a method that unpacks these operands and executes the instruction opcode. The object passed between the protocol client and server is an instance of ProtocolData class. Listing 5 shows the ProtocolData class handling all the operand/operator capture.

The ProtocolData object is a Singleton object; i.e., one instance of the class is created, regardless of how many times the constructor is called:

private static ProtocolData pd;
public ProtocolData() {
if (pd == null) {
pd = this;
}
}

The ProtocolClient and ProtocolServer classes are never instantiated; the relevant variables and methods are declared as static so they are referenced at class level. There is no need to instantiate them.

A distributed, multi-tier treatment of the protocol handling would use RMI, in which the call to the serviceDBRequest() methods is an RMI call. Without RMI, the ProtocolData object must be serializable, with the protocol client and server connected over sockets.

Tight Coupling of Component to Event using Anonymous Classes
Java 1.1 introduced both event delegation and anonymous classes. Event delegation allows events generated from one component - e.g. a button - to be handled by another component. Anonymous classes allow the event handling code to be specified when registering the event listener for that component without the need to define and instantiate another class, reducing design and build complexity. The 'Anonymous' tag describes a class that has no name; as far as the builder is concerned, it is in-line event handling code. When the code is compiled, a class file is generated which contains the event handling code and the class is named after the containing class with a '$' and a class number suffixed to it.

The user interface makes use of anonymous classes. Listing 6 shows how, when an entry in the tableList is clicked, a request is sent for the table columns to be displayed. Instead of adding a class which implements the ItemListener interface, Java will create a class anonymously at compilation and add it as a listener to the tableList component when the code above is executed.

Listing 7 shows how to display the Query Window when the Query button is pressed.

Strict Use of Core Java 1.1 AWT Classes
Although several widget libraries exist which support grid and tree controls, the browser does not use any and instead uses the core AWT components and layout managers. This reduces some of the complexity between the user interface and the protocol handling and simplifies the design, but makes for a less attractive interface and more coding.

In a prototyping context, the ideal would be to use a Bean-compliant IDE and Bean-compliant widgets, of which there are many. However, the design constraints dictate the core functional requirement that a user interface be placed on top of the JDBC API. Some would argue that the user interface is the most disposable part of an application; this is true if the parts are built quickly and cheaply.

Evaluation of GUI components is outside the scope of this exercise, so a decision was made to use the core AWT components, using a CardLayout to implement a form of tabbed panelling. Good use is made of the CardLayout component, which implements tab-like functionality. Here, the user clicks a button (Query, Tables, Connect) and a different card, or 'Tab', is displayed. The contents of the tab persist between displays so that, for example, the list of tables is still displayed when entering and returning from the Query tab.

When the user clicks one of the central buttons, the relevant tab is displayed as follows (using an anonymous class):

connectButton.addActionListener(
new ActionListener() {
public void actionPerformed( ActionEvent e){
tabOptions.show(
panel4,
connectButton.getLabel());
}});

Panel panel4 is a panel that has been added to the tabOptions CardLayout component and has a tag that is equivalent to the text of the button which fires the action event. The Connect tab is shown when the application starts up by setting the default display property as follows:

tabOptions.first(panel4);
tabOptions.show(panel4, connectButton.getLabel());

Extensions and Conclusion
The Data Browser allows us to view the JDBC API in a highly flexible way without compromising portability. With the source code provided, this article will enable you to use and extend the browser, learn how to work with layout managers to achieve a reasonably sophisticated effect without third party widget class calls and, centrally, learn how to connect to a database and use JDBC method calls.

Clearly, not all of the API has been implemented; interested readers can include extra method calls using popup menus against different menu options; e.g. metadata, statements, connection details, etc. It is a moderately simple task to hook another user interface to the protocol and database classes.

One enhancement would be to attach a grid component to the user interface. There are several excellent grid components available for 1.0 and 1.1 Java; Swing grids are available for 1.2.

The next enhancement would be to generate screens dynamically based on the columns of tables that we want to update. For example, we may select a database and a table, then nominate columns for a form that would be used to qualify queries for insert, update and delete queries. Remember that the browser is not tied to one database design so the forms must be created dynamically.

Network distribution of components can be achieved with little modification to the code through an RMI implementation, with the User Interface and Protocol Client on one host and the Protocol Server and DataBase on another host. All four components could be on four different hosts. One drawback in multi-tier hosting of service objects is the serialization and network transport of marshalled objects. Java 1.1 introduces object serialization; it is very easy to implement this feature without using RMI. Essentially, the ProtocolData instance is serialized and passed over a TCP/IP socket connection to a listening protocol server. Since both client and server are Java, you can use RMI, which implements serialization underneath the RMI protocol.

For an implementation where many clients require access to the database, the main requirement is that the Database class makes all methods 'synchronized' so that there is no contention when updating or inserting data. Where many clients require the protocol handler to perform a service, the ProtocolData object must not be a Singleton object, but be instantiated for every client that requires a protocol instruction to be formatted. The ProtocolClient and ProtocolServer classes would need some adjustment to enable the client/database handles to retain their identity for each client. For example, the ProtocolClient could be instantiated for every user interface connection, the variable data being a reference to the main UI object (the methods remain static); alternatively, a static array could contain a list of references to controlling user-interface objects.

In summary, when we relocate cooperating objects from the same virtual machine, we need to implement RMI or sockets while retaining the component functionality. Most functional requirements you explore can be implemented by extending the core classes in this way.

About the Author
Graham Harrison is a Senior Consultant with Informix Software and a Sun Certified Java Programmer. He can be contacted at [email protected]

	

Listing 1: Connect to Database using Protocol Operands
  
// Connect to database  
public void connectDB ( String []  operand )   
                   throws Exception {  
    if (operand[DBP_DRIVER_ARG] != null ) {  
      class.forName( operand[DBP_DRIVER_ARG]);  
    }  
    if (operand[DBP_USERID_ARG] ==  null) {  
       session =    
       DriverManager.getConnection(  
         operand[DBP_URL_ARG]);  
    }  
    else {  
        session =   
            DriverManager.getConnection(  
      operand[DBP_URL_ARG],  
      operand[DBP_USERID_ARG],  
      operand[DBP_PASSWORD_ARG]  );   
    }  
    query =  session.createStatement();  
}  

Listing 2: The User Interface is notified of Observable change with a ProtocolData Object.
  
class DataBrowser    
         extends Frame   
         implements DBProtocol,  
                            DBConstants,  
                            DBHandle,  
                            ItemListener,  
                            Observer {  
…  

        public void update(  Observable o,   
                                         Object p) {  
               if !(p instanceof ProtocolData)){  
                    System.out.println("Error in                
                                   Observers.");  
                    System.exit(1);  
               }  
              // do something as directed by   
              // observable object with detail   
              // in the protocol, e.g. requery  
      }}  

Listing 3: The Protocol Client creates a Protocol Instruction.
  
public static void  tellProtocolClient( byte opcode )   
                                   throws Exception {  
    ProtocolData protocolData =     
            ProtocolData.getProtocolData();  
    protocolData.setOpcode(opcode);  
    try {  
        switch (opcode) {  
            case DBP_EXECUTE_QUERY: {   
                   // the TextArea is the operand value  
                  protocolData.setOperand(  
                              ui.queryWindow.getText(),  
                              DBP_SQLTEXT_ARG);  
                  protocolData.setOperand(  
                             ui.rowCount.getText(),  
                             DBP_ROWCOUNT_ARG);  
                  ProtocolServer.serviceDBRequest(  
                              protocolData);  
                  break;  
 }  
…  
}  

Listing 4: Protocol Server Instruction Handling.
  
// protocol handler  
public static Object serviceDBRequest( ProtocolData p )  
                                throws Exception {  
    switch (p.getOpcode()) {  
        case DBP_EXECUTE_QUERY: {      
            db.execSQL(  
                 p.getOperand(DBP_SQLTEXT_ARG),     
                 p.getOperand(DBP_ROWCOUNT_ARG));  
 return db.getResults();  
        }  
…  
}    

Listing 5: ProtocolData Class Operand Capture
  
public void setOpcode( int opcode ) {  
    this.opcode = opcode;  
}  
public void setOperand( String operand, int index  ) {  
    this.operand[index] = operand;  
}  

Listing 6: Anonymous Class Example One
  
tableList.addItemListener(   
    new ItemListener() {  
        public void itemStateChanged(     
                              ItemEvent e ) {  
            Integer index =  (Integer)e.getItem();  
            tableName = tableList.getItem(index.intValue());  
            try {  
                ProtocolClient.tellProtocolClient(   
                                         DBP_COLUMNS );  
            } catch (Exception ea) {  
                 ea.printStackTrace();  
            }  
       } });  

Listing 7: Anonymous Class Example Two.
  
queryButton.addActionListener(   
        new ActionListener() {  
            public void actionPerformed(   
                               ActionEvent e ){  
                tabOptions.show(   
                          panel4,  
                          queryButton.getLabel());  
          } });
  
      
 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.