# Designing a Database Control

This topic provides some tips for designing a Database control to efficiently perform the operations required of it, including designing for user convenience, designing for reuse, providing accurate access rights, compensating for large resultsets, and choosing between objects and primitives.

## Designing for User Convenience

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

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

## Designing for Reuse

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

For example, avoid hard-coding values that are likely to change into queries. 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. If you provide parameters for methods on your Database control instead of hard-coding them, your control will be more flexible and reusable.

## Partitioning the Interface for Access Rights

When designing a new Database control, you may wish to consider creating multiple Database controls for the same database. Interactions with a database typically fall into one of the following 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: these are 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 usually carried out as part of application deployment. In a production environment, there is not typically a requirement for an application interface to provide 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.

## Planning for Potentially Large Resultsets

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

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

Limit the number of columns returned from a query to only those required. Avoid "SELECT *" statements unless they 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 performs 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 @jc:sql annotation for the method. Set the value so 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 additional 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 the array-max-length attribute is 1024.

Use the max-rows attribute to limit the size of other data types. If you database control method does not return an array such as an Iterator, RowSet, or ResultSet, you can use the max-rows attribute to limit the size of the data set returned. For techniques for limiting the size of returned data at runtime, see Limiting the Size of Returned Data.

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. The Iterator and ResultSet objects transparently make occasional requests to the database to obtain more data as needed as you iterate through the resultset. Using this technique, you may enable processing of resultsets that are larger than the available memory.

However, note that ResultSets and Iterator types cannot be returned directly to classes in the web-tier (web services and page flows reside in the web-tier). This is because ResultsSet and Iterators will always be closed by the time they reach the web tier. As a workaround for this limitation, you should return custom array types to the web tier (such as Customer[]).

Make calls asynchronous. If a method of a web service initiates a long-running or resource-intensive SQL query, consider making that method asynchronous so that the client does not have to wait for it to return.

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

## Consciously Choosing 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. So if a database field with no value is converted to an int, its value is 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.

Related Topics

Creating a New Database Control