Database Control Design Issues

This topic describes design issues you should consider when creating new Database controls.

To learn about Database controls, see Database Control: Using a Database from Your Web Service.

# Partition the Interface

When designing a new Database control, you may with to consider creation multiple Database controls for the same database. Interactions with a database typically fall into two or three categories:

- Administration operations: these typically include CREATE and DROP operations on database tables.

- Privileged operations: these typically include INSERT and possibly UPDATE operations. Many applications restrict which users or applications may modify the contents of the database.

- Retrieval operations: typically SELECT operations. Operations that do not modify the contents of the database are typically available to a wider class of users.

If users in your application can be partitioned into these groups, you may wish to create separate Database controls for each class of user. In this way, you can expose only the less privileged operations to web service developers whose services do not require administrative access.

Note that database administration activities are typically carried out as part of application deployment. In a production environment, there is not typically a requirement for a web service interface to administrative activities such as table creation. The Database control samples provided with WebLogic Workshop include table creation methods for convenience as samples, not as examples of good design.

# Be Aware of Potentially Large Result Sets

When you design database operations in your Database control, be aware of the potential size of the result sets that might be generated by the operations. With large databases, it is easy to accidentally execute queries that return result sets that are larger than the available memory on the machine.

Here are some ways to protect against out-of-memory errors due to large result sets:

- Limit the number of columns returned from a query to only those required. Avoid "SELECT *" statements unless that are explicitly necessary.

- Perform filtering in the database. If you are only interested in a subset of the records that might be returned from a query, refine the query so that the database system will do the filtering. Database systems are optimized for this type of operation, and are also designed to perform filtering and sorting operations in a memory-efficient manner.

- Use the array-max-length attribute to limit array size. If your Database control method returns an array, you should explicitly provide the array-max-length attribute on the @jws:sql tag for the method. Set the value such that it is higher than the number of rows you expect to be returned, but not so large that you might run out of memory should that many rows be returned. Note that there is no way to subsequently return the "deleted" rows should the array size limit be reached by a query. There is also no way to set array-max-length using an API call.
  The default value of array-max-length is 1024.

- Use an Iterator. A Database control can return a java.util.Iterator instead of an array. The Iterator wraps a java.sql.ResultSet object that accesses the database efficiently. As the web service that obtained the Iterator (via a call to a Database control method) steps through the Iterator, the Iterator and ResultSet transparently make occasional requests to the database to obtain more data. Using this technique, you may enable processing of result sets that are larger than the available memory.

To learn more about writing Database control methods that return multiple rows, see Returning Multiple Rows from a Database Control Method.

# Design for User Convenience

When designing a Database control, as with designing any control or web service, think carefully about the information needs of the users of your control. Choose operations and data structures that are convenient for the web service developer.

One way to accomplish this is to provide Java classes that represent the records or partial rows your Database control methods operate on. Consistent use of these classes in the Database control's interface will simplify use of the Database control.

# Consciously Choose Between Objects and Primitives

As is noted in the topics describing how to return data from Database control methods, the value returned when a database field is NULL depends on the Java type to which the database field is converted. Java primitives such as int and float are not capable of reflecting a null value; they must always have a valid value. Thus, if a database field with no value is converted to an int, the value will be 0. If it is important to you or the clients of your Database control to differentiate between zero and null, you should use the Java wrapper classes for basic types. For example, use java.lang.Integer instead of int, and java.lang.Float instead of float.

# Design for Reuse

Wherever possible, try to avoid building rigidity into your Database controls.

For example, avoid hard-coding value into queries. Parameterize your database operations as much as possible while maintaining convenience for users of your control. It is typically desirable to hard-code the table name because operations are typically table specific. It is typically not desirable to hard-code absolute dates or times into queries.

Related Topics

Creating a New Database Control