Rowset and Database Controls

Database and rowset controls enable you to access database data by using a simple relational object model. With database and rowset controls, you can access data from a single table, from multiple tables using joins, or directly using SQL statements. These controls simplify data access by using simple Java methods and value objects. Rowset and database controls provide similar capabilities: accessing a database. At their core , database and rowset controls are very similar; both use Java Database Connectivity (JDBC) to return content via a previously defined DataSource . The main difference between the two, as you saw in Chapter 3, is that rowset controls contain a significant amount of generated code for the purpose of accessing a database table with RowSet objects. Database controls contain only logic to manage a connection, and all database access methods must be written by a developer.

An important point to remember about database-type controls is that they support only synchronous method calls. They cannot generate or accept callbacks. At first glance, not being able to accept callbacks appears to be a limitation. However, it's not so much a limitation as simply a side effect of how database and rowset controls are created. Database controls, and most controls created by wizards, are more like interfaces than classes and can't contain user code.

The real power of these controls is in their capability to define custom methods and return values. Listing 6.1 shows a simple database control that returns scalar values, value objects, and arrays of value objects.

Listing 6.1. hotelTableCtrl.jcx

 package controls; import com.bea.control.*; import java.sql.SQLException; /**  *  * @jc:connection data-source-jndi-name="wonderlandDS"  */  public interface hotelTableCtrl extends DatabaseControl,   com.bea.control.ControlExtension {  static final long serialVersionUID = 1L;     static public class HotelVO {       public int id;       public String name;       public int rating;       public int capacity;     }     /**     * @jc:sql statement="SELECT CAPACITY FROM WONDERLAND.HOTEL where ID={id}"     */     int getCapacityById(int id) throws SQLException;     /**  * @jc:sql statement=   "SELECT ID, NAME,RATING,CAPACITY FROM WONDERLAND.HOTEL where ID={id}"  */     HotelVO getByID(int id) throws SQLException;     /**     * @jc:sql statement="SELECT ID, NAME,RATING,CAPACITY FROM WONDERLAND.HOTEL"     */     HotelVO[] getAll() throws SQLException; } 

Database Control Basics

Creating a database control is similar to creating a rowset control. To create a database control, follow these steps (shown in Figure 6.6):

1. Select a folder where the control will be created. Any subfolder in a project will do; however, common convention suggests that a subfolder of a controls folder is best.

2. Choose File, New, Java Control from the WebLogic Workshop menu.

3. Select the Database item in the New Java Control Extension dialog box. All database schemas are listed, such as CasinoDB, depending on what's installed on your machine.

4. Enter a name for the control. Browse to a directory to contain the control, if desired, and click Next. Note that controls must be placed in an existing folder.

5. Enter the name of the datasource, or browse the current list of datasources and select one. Click Create. All existing datasources are listed; for this book's examples, select the Casino datasource.

Figure 6.6. Adding a rowset or database control.

Methods are added to a rowset control by dragging a method to the control from the Operations section of the Palette and then entering an appropriate name for the method. Figure 6.7 shows the properties for a rowset control. All properties are optional, with the exception of the statement property.

Figure 6.7. Rowset control properties.

JDBC DATASOURCE

A JDBC datasource encapsulates access to a JDBC driver and a database and provides a transparent mechanism to handle connections and transaction support. WebLogic Server contains robust datasource support and administration functions. Those unfamiliar with managing datasources should see the WebLogic Server documentation.

You must also enter a SQL statement for each method. There are two ways to do this:

• The preferred method is to display a control in Design View, right-click a method, and choose Edit SQL. Figure 6.8 shows the Edit SQL and Interface dialog box, which can be used to enter the associated SQL statement and edit the method signature.

  Figure 6.8. The Edit SQL and Interface dialog box.

  

• In Design View, double-click a method, and Source View opens to the selected method. You can edit the SQL statement directly.

The first method is preferred over the second primarily because it shows the method signature and SQL statement together. However, there's no syntax checking on the SQL statement, so it's a matter of personal preference as to which works best for you.

Every method in a rowset or database control looks similar to this:

 /** * @jc:sql  optional attributes  statement="Parameterized SQL" */ returnType methodName(parameters) throws SQLException; 

Database controls, and their rowset cousins, define and expose methods by using the @jc:sql annotation. This annotation is a signal to the compiler that it should create a method implementation. Remember that .jcx files are actually annotated interfaces that return data based on the SQL provided in the statement property. There are a number of optional attributes as well. These optional attributes include number or results returned, for example, and are examined in more detail in "Selecting Data" later in this chapter. Statements can be simple, such as select count(*) from WONDERLAND.GUEST , or require parameter substitutions. Parameter substitution is the process by which a user variable is used in place of a variable in an SQL statement at runtime. Every method returns somethinga single simple value, an array of simple values, a single complex object, an array of complex objects, or a collection. Finally, all methods must throw a java.sql.SQLException .

Selecting Data

A powerful aspect of database and rowset controls is their capability to map data from a result set to Java variables and classes. One of the methods defined for the hotel table in the Casino example returns a single scalar integer value based on a supplied parameter value. The following code shows the connection between the defined parameter id and the select statement that uses it. At statement execution time, the where clause has all the values substituted and the results gathered for return. Table 6.2 shows a complete list of the SQLtoJava type mappings:

 /** * @jc:sql statement="SELECT CAPACITY FROM WONDERLAND.HOTEL where ID={id}" */ int getCapacityById(int id) throws SQLException; 

MULTILINE SQL STATEMENTS

The statement attribute of a jc:sql annotation can cover multiple lines for simple readability. SQL statements are delimited by pairs of double colons ( :: ), as shown in this example:

 /** * @jc:sql statement:: * select * from WONDERLAND.GUEST * where {prefix} ="mr" * :: */ 

This code example also shows a number of important features of database methods. Workshop assumes that because a simple defined scalar value is returned that the select statement will return only a single row. If the select statement returned multiple rows, only the first row would be returned to the caller. Workshop doesn't stop there, however; the name of the actual database column, the column type, and the Java equivalent are returned at runtime. This metadata is used to ensure that the SQL can be mapped to the return data. When executed, the return value is converted to the requested type, if this conversion is possible. You could just as easily have defined getCapacityById in this way:

 String getCapacityById(int id) throws SQLException; 

You can return the value as a string because integers can be converted to strings.

Table 6.2. SQLtoJava Type Mappings

SQL TYPE	JAVA TYPE
INTEGER, INT	Int
BIT, BOOLEAN	Boolean
CHAR, CHARACTER, VARCHAR, LONGVARCHAR	String
SMALLINT	Short
BIGINT	Long
FLOAT , DOUBLE	Double
NUMERIC , DECIMAL	java.math.BigDecimal
REAL	Float
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
TINYINT	Byte
BINARY , VARBINARY , LONGBINARY	byte[]
BLOB	java.sql.Blob
CLOB	java.sql.Clob

What about returning complex types? Listing 6.1 included HotelVO , an inner class used as a value object. This inner class is used as the return type of a select statement returning multiple columns of data. Assume for a moment that a where clause of a select statement returns a single complete row of data. You can return that data in its entirety by writing a method similar to the following:

 /**  * @jc:sql statement="SELECT ID, NAME,RATING,CAPACITY FROM WONDERLAND.HOTEL   where ID={id}"  */ HotelVO getByID(int id) throws SQLException; 

At runtime, the return types and names of the defined return classin this case, HotelVO and the columns of the result are examined, and an appropriately populated HotelVO object is returned.

Until now, you've examined select statements returning only a single row of data. However, more often than not, a select statement returns multiple rows of data. You can map this type of select statement to an array of value objects by simply specifying the return as a Java array, as shown here (note the brackets after HotelVO ):

 /** * @jc:sql statement="SELECT ID, NAME,RATING,CAPACITY FROM WONDERLAND.HOTEL" */ HotelVO  []  getAll() throws SQLException; 

The returned result is then mapped to an array of objects. Note that if an array is not defined, and the select statement returns multiple rows of data, only the first row is returned.

THE ARRAY-MAX-LENGTH PROPERTY

Setting the maximum number of array records to be returned can be done by using the array-max-length property. This property controls the number of records returned from a select statement. The property supports an integer value defining the maximum number of rows to return, or it supports the all variable and defaults to 1024. Be careful when using all , as you could exhaust memory on large selects.

In addition to arrays, there are two other mechanisms for returning data: iterators and hashmaps. Hashmaps are useful when the columns in a table might change, which would result in an exception if the return types were hard-coded.

To code a hashmap method, you define the return type as java.util.HashMap . You then access the data by using the hashmap's get() method, as shown in this example:

 java.util.HashMap result; result = dbcontrol.findbyName... (); If (result != null) {     String firstName = (String) result.get("FirstName"); } else { ... column name not found } 

CHOOSING BETWEEN ARRAYS AND ITERATORS

On the surface, Java arrays and iterators seem to be almost identical. In fact, iterators are more difficult codewise because they require slightly more work (at a minimum, a cast operation). Why would you choose one over the other? There are two points to consider with arrays:

• Arrays are read and captured into memory 100% at select time.

• Arrays are limited to 1,000 records.

With iterators, records are brought into memory only as required. When the result set is expected to be small, use an array. When the result is moderate in size or expected to be only partially consumed, use an iterator. When the result set is large, use a rowset.

Iterators are similar to returning arrays, in that multiple results are returned. You define a method that returns an iterator (return type java.util.Iterator ) as shown here:

 /**  * @jc:sql iterator-element-type="HotelVO"   statement="SELECT ID, NAME,RATING,CAPACITY FROM WONDERLAND.HOTEL"  */    java.util.Iterator getAll() throws SQLException; 

Callers then cast the iterator methods to type HotelVO as each result is processed .

Parameter Substitution

WebLogic Workshop generates a JDBC-prepared statement based on the statement attribute of the jc:sql annotation. The performance of a prepared statement comes from precompiling the statement as well as parameter substitution at execution time. WebLogic Workshop has rich support for parameter substitution in a where clause of an SQL statement. Parameter substitution in an SQL statement happens when a set of curly braces ( {} ) is encountered . Parameter substitution supports three styles of replacement:

• Simple substitution, in which a method parameter matches in name and case a parameter in a where clause

• Indirect substitution, in which a method parameter matches a variable within a class using dot (.) notation

• Wholesale substitution, in which a text replacement occurs without any error checking

Parameter substitution is based on a set of rules:

• Substitution is case sensitive. For example, Capacity is not equal to capacity .

• The variable and the where clause type must be type assignable. For example, it doesn't make sense to provide an integer variable when a Date is expected. Note that when using dates, you must provide a java.sql.Date if the column type is Date .

• Spaces short-circuit the substitutions. For example, {capacity} is treated as a JDBC-escaped variable and is not processed.

• Partial matches using the % operator must be specially handled.

These rules are best illustrated by example. You've already seen an example of simple substitution, so the following paragraphs concentrate on indirect and wholesale substitution.

Listing 6.1 defined an inner class, HotelVO , which included rating and capacity fields. You can use these fields as input, using indirect parameters via dot notation, as shown here:

 /**  * @jc:sql statement="SELECT * FROM WONDERLAND.HOTEL   where CAPACITY> {in.capacity} and rating={in.rating}"  */ HotelVO[] getAllByCapacityAndRating(HotelVO in) throws SQLException; 

In general, the fields on the class being used must be declared public , or a com.bea.control.ControlException is thrown. If a class has an inner subclass, it too can be accessed with dot notation. For example, assuming a class b with a variable c , and b is within a class a , you could reference field c by entering a.b.c .

In wholesale substitution, a substitution is made without error checking. For example, say you want to obtain the total number of records in a database table. The SQL statement could easily be written as select count(*) from someTable ; someTable is provided at runtime and the statement is executed directly rather than via a prepared statement. If a curly brace substitution encounters a :sql statement , the remainder of the SQL is added directly without error checking of any kind. You could achieve the required result by coding the method as follows :

 /** * @jc:sql statement="SELECT count(*) from {sql: tableName}" */ integer count(tableName) throws SQLException; 

HANDING PARTIAL MATCHES

Partial matches present a minor problem when used with parameter substitution. If you're searching by using the %% SQL operator, as in the following code, the {} in the where clause would be ignored:

 where lastname LIKE %{partialname}% 

To handle this special case, format the match string within the client and pass this formatted string into the method. Take a look at the following example:

• Method:

   /** @jc:sql statement:: select * from WONDERLAND.GUEST where lastname LIKE {partial} * :: */ public Guest[] findLastNameLike (partial}; 

• Usage:

   String match = "'%bink'%"; Guests[] result = dbcontrol. findLastNameLike(match); 

Updates, Inserts, and Deletes

Deletes and updates are, for all practical purposes, the same as select statements. That is, method definition, parameter substitution, and so forth work the same way. A simple way to code an insert is to define an SQL insert statement that uses dot notation to complete the values, as shown here:

 /**  * @jc:sql statement::  * INSERT into WONDERLAND.HOTEL (* "NAME","RATING","CAPACITY"  *  VALUES ({in.name}, {in.rating},{in.capacity})  * ::  */   void insertByHotelVO(HotelVO in); 

PROCESSING 0 RESULTS

Any select statement can return 0 results. How this case is handled depends on the return type. Primitive types, such as Int and Boolean , can return or false if no records are found. String , Integer , and other complex types return null . Be careful when creating your database tables to avoid ambiguous return values.

Testing a Database Control

You can test a database control by generating a test Web service. Don't worry that the test harness is actually a Web service; you'll be driving it by using a simple Web page. To generate a test Web service for a database control (or any control, for that matter), select the control, right-click on it, and choose Generate Test JWS file. A new file is generated with Test.jws appended to the original control name. Select the new test Web service and run it by choosing Debug, Start from the WebLogic Workshop menu. Figures 6.9 to 6.12 show the sequence of steps for testing a control. Figure 6.9 shows starting a conversation. All Web service controls operate in the context of a conversation, although for database and rowset controls, it's not important.

Figure 6.9. Starting a test conversation.

Figure 6.12. Examining the result.

Figure 6.10 shows the current conversation state. Click the Continue This Conversation link, and then select the method you'd like to test.

Figure 6.10. Continuing a conversation.

Figure 6.11 shows several methods, each of which can be tested . Enter a value for the method's variables and click the button labeled with the method name. You then see an error page (if, for example, you entered invalid SQL) or the result of the call as well as a fair amount of background information about the call.

Figure 6.11. Entering test values.

Figure 6.12 shows the result of testing the getCapacityById method. You might need to scroll down to find the returned result of your database call.

SHOP TALK : ROWSET/DATABASE CONTROLS AND VALUE OBJECTS

After a fair amount of time working with rowset and database controls, it became clear that I was often writing my own read, write, and update methods. Although rowset controls offer a useful mechanism for accessing database tables, I found myself coding the methods and select statements directly. Rowset controls are convenient , but mostly for generating Page Flows that perform create, read, update, and delete (CRUD) operations.

In fact, I began writing so many inner classes that I wrote a utility to generate a complete inner class JavaBean object and insert statements using database table metadata. The utility, along with a readme file to run it, can be found on the CD-ROM in the innerclass directory. Note that the genInnerClass utility assumes that the account weblogic/weblogic exists in WebLogic Server. Other user accounts can be used by altering the code.

To use the genInnerClass utility, follow these steps:

1. Edit the setenv .cmd file and replace e:\bea with the install path and directory where you installed WebLogic Platform. You need to replace this string in several places.

2. Edit prompt.cmd and update it similarly.

3. Click on prompt.cmd to open a command-prompt window with a prepared environment containing the necessary environment variables set up for you.

4. Run genInnerClass.java as follows:

   java genInner Class ds schema table [classname]

   The parameters in this command are defined as follows:

   • ds is a previously defined datasource, such as cgDataSource .

   • schema is any loaded database Schema, such as Wonderland.

   • table is a table within the Schema, such as hotel .

   • classname is an optional class name to be used with the generated result.

To capture the generated class to a file, pipe its output to a file, as shown in this example:

 java genInnerClass csDataSource Wonderland hotel  myHotelDBControl > hotel.innerclass.java 

Custom Java Controls

Custom Java controls provide an excellent mechanism for encapsulating business logic, aggregating functionality, or creating reuseable logic. Often multiple database and rowset controls, EJBs, Web services, logging, and other controls are combined intelligently to create a simplified facade. To create a Java control, select a folder for the new control and choose File, New, Custom Java Control from the Workshop menu.

Java controls are separated into an interface, which represents all the control's method signatures and is contained in a .java file, and its implementation, which is contained in a file ending in Impl with the extension jcs .

For all practical purposes, custom Java controls are simple Java classes that are exposed in the Workshop environment and can use other controls to perform work.

Adding methods to a custom Java control is as simple as dragging an operation from the Workshop Palette and entering the appropriate signature. After dragging a method to a control and setting its name, double-click to open the method in Workshop and update the method signature to match your specific needs. Adding a method to a custom Java control results in two changes to the source files. The first is a change in the Impl.jcs file. The following code shows a method that takes an integer and results in a string version:

 /** * @common:operation */ public String methodTakingInteger(int input) {         return new Integer(input).toString(); } 

The second change is made to the .java file, and an equivalent entry is made there. The .java file actually represents an interface to the control rather than its implementation and is required to support callbacks. Normally, this file is maintained by Workshop instead of the developer editing it directly.

Adding a callback is done the same way as adding a method, but it produces a different result. When a method is a definition in the .java file, an implementation skeleton is added in the Impl.jcs file. Callbacks result in a class definition being added to the .java file and a callback variable being added to the Impl.jcs . The following code represents a callback that passes a String :

 interface Callback {         void timerFired(String time);     } 

Remember that callbacks differ from other methods, in that they are actually implemented by the client, not the control developer. The callback interface informs Workshop that the client can implement this method. If a client wants to implement the callback, all you need to do is click on the method's definition, and a skeleton implementation is added to the client control.

Firing a callback (so that a client control gets the method invocation) requires calling the callback method from a method within the control. Remember that adding a callback method results in a callback variable being added to the control. You use the callback object to fire the callback method. Any client control that has implemented the callback then gets the callback notification.

VERSIONING IN CONTROLS

Have you wondered what static final long serialVersionUID = 1L; means in a database or rowset control? WebLogic Workshop uses a process known as hotfix versioning , which means you can change a JWS or control in a way that's compatible with Java serialization and redeploy the application. For example, you can change the order but not add any other variables, or add new method code that doesn't change the control's state. When conversational state is loaded from the database, the existing state is loaded into the updated class(es). The result is that at any point in time, there's just the "current" version of a control. Fixes must be serialization compatible and become the new "current" version. Whenever a change is made that's not compatible with serialization, such as changing or adding variable types, you should update the version ID to be one higher.

The following code shows a callback being fired :

 public void myTimer_onTimeout(long time) {  callback.timerFired(new Integer((int) time).toString());  System.out.println("MyCustomControl:: timer fired");     myTimer.restart(); } 

Custom Java controls, more often than not, actually use other controls as vehicles to gather data, implement business logic, or communicate in some way with other systems, often acting as a facade. You can add a control to a custom Java control by dragging it from a folder within the same project or by clicking Add in Workshop's Data Palette, selecting a control, and then following the wizard steps.

TIMER CONTROLS

Workshop comes with a control known as a Timer control, which provides a set of methods for defining timeout events. The Timer Control Wizard queries for a delay as well as whether to repeat the delay. Add a Timer control to your custom Java control by clicking Add in the Data Palette and choosing Timer. To use a Timer control, implement its onTimeout callback. In the preceding code examples, a Timer control was added and used to fire the timerFired callback on the custom Java control.

Figure 6.13 shows the result of adding a Timer control to the custom Java control. Any type of control can be added to a custom Java control using one of these two methods.

Figure 6.13. A custom control with a Timer control added.