March 4, 2021
Hot Topics:

The Java Database Control in BEA Weblogic

  • By Sams Publishing
  • Send Email »
  • More Articles »

This is Chapter 6: The Database Control from the book BEA WebLogic Workshop Kick Start (ISBN:0-672-32417-2) written by Joseph Weber and Mark Wutka, published by Sams Publishing.

Chapter 6: The Database Control

In This Chapter

  • Creating a Database Control

  • Defining a Database Connection

  • Creating an SQL String

  • Including Variables

  • Getting a Result Set

  • Executing Multiple Statements

  • A Sample Application

Many Web services act as a front end to a database. That is, some Web services simply act as a means to store data in the database and to retrieve it. Other Web services use a database as part of the overall application functionality. In these cases, the database can contain vital application data, but the Web service provides data validation and additional business logic. Because database operations are very common and often tedious, Workshop provides a database control that handles the tedious parts.

Creating a Database Control

To add a database control to your application, select Service, Add Control, and Add Database Control. You will see the Add Database Control dialog box, as shown in Figure 6.1.

Figure 6.1
You can add a database control to handle database access.

As with other controls, you must give the control a variable name and then either specify an existing control, or create a new control. When you create a new control, you must also specify a data source. A data source is a factory for creating JDBC database connections, similar to the JDBC DriverManager class. One of the advantages of a data source is that you can locate it using JNDI, giving you a central place to keep your database URLs. Data sources were introduced as part of Java 2 Enterprise Edition and represent a cleaner way to access database connections.

A data source gets its connections from a JDBC connection pool. A connection pool can keep several database connections open at one time. Without a connection pool, you might encounter delays while the JDBC driver sets up a new connection each time you need one. Because the pool maintains a group of reusable connections, your program is more efficient when allocating a connection.

Defining a Database Connection

To use the database control, you must define a data source. To define a data source in WebLogic, you must first define a connection pool. The easiest way to define a connection pool is through the WebLogic server console. Figure 6.2 shows the page for creating a connection pool. Notice that you must supply the JDBC connection URL and the JDBC driver class.

Figure 6.2
You can create connection pools using the WebLogic console.

After you create a connection pool, you can create a data source that uses the connection pool. The WebLogic console makes it easy to create connection pools, as shown in Figure 6.3. Simply give the data source a name and enter the name of the connection pool that the data source should use to obtain database connections.

Figure 6.3
You can create data sources using the WebLogic console.

The WebLogic samples server includes a sample data source that you can use for test programs. The data source name is cgSampleDataSource. You can use this data source to create new database tables and then execute SQL statements to manipulate these tables.

Creating an SQL String

To execute database statements from a database control, you first create a method that accepts any parameters you want to pass to the database statement. For example, you might want to pass an order number to search for, or a customer's new address. Next, you use the @jws:sql JavaDoc tag to create the database statement.

You can find more information on SQL, including a tutorial and links to the SQL specification, on the support page for this book at http://www.samspublishing.com.

The only attribute in the @jws:sql tag is statement, which contains the database statement. For example:

 * @jws:sql statement="select * from accounts"

If a statement is long, you can split it across multiple lines. Instead of enclosing the statement in quotes, use the statement:: form of the attribute using :: to end the statement, like this:

 * @jws:sql statement:: update personnel set
 *   title='Manager', department='Toys'
 *   where id='123456'
 * ::

Selecting Values

The general format for the SQL SELECT statement is

SELECT fields FROM table

The fields can either be * to indicate all fields in the table, or a comma-separated list like first_name, last_name, city, state, zip.

One of the most common clauses in a SELECT statement is the WHERE clause, which narrows the selection. The WHERE clause contains a Boolean expression, which can contain comparison operators like =, <, and >. You can also combine multiple expressions with AND, OR, and NOT. To test whether a column has no value, use IS NULL. Here are some sample queries:

SELECT * FROM personnel WHERE department='123456'
SELECT * FROM personnel WHERE spouse IS NULL
SELECT * FROM orders WHERE status='SHIPPED' or status='BACKORDERED'

You can add an order by clause to sort the results. The order by clause takes a list of fields to sort by. For example, to sort first by last name and then by first name, use the following query:

SELECT * FROM personnel ORDER BY last_name, first_name

By default, ORDER BY sorts items in ascending order. You can use the DESC keyword after a field to sort that field in descending order. You can use the ASC keyword to explicitly specify ascending order for a field, which for complex ORDER BY clauses might improve readability. To sort by last name, then first name, then by age in descending order, use the following query:

SELECT * FROM personnel ORDER BY last_name, first_name, age DESC

Updating Values

The UPDATE statement updates values in a table. The general format is

UPDATE table SET column=value, column=value, ... WHERE where-clause

For example:

UPDATE personnel SET department='Marketing', manager_id='987654' WHERE id='123456'

Inserting Values

The INSERT statement inserts values into a table. The general format is

INSERT INTO table (columns) values (column-values)

For example:

INSERT INTO personnel (id, first_name, last_name)
  VALUES (1, 'Kaitlynn', 'Tippin')

Deleting Values

The DELETE statement deletes values from a table. The general format is

DELETE FROM table WHERE where-clause

For example:

DELETE FROM personnel WHERE id='321654'

Joining Tables

One of the most powerful aspects of SQL is the ability to coordinate data from multiple tables. This technique is called joining. You join tables by comparing values from two different tables in a WHERE clause. When the tables have duplicate column names, you can prefix the field name with the table name, using the form table.field, for example: personnel.first_name.

Because your SELECT statement might get cluttered with long table names, you can create aliases for tables in the FROM clause. Simply list the alias after the table name. For example, if the FROM clause is FROM personnel p, you can refer to fields in the personnel table with p.field in the FROM clause. You can also use the table.field form when you list the fields you want to select.

The following SELECT clause locates all items ordered by customers in Atlanta— locating customers in a particular city, orders whose customer ID matches the customer's ID, and the products whose order ID matches the order's ID:

SELECT p.* FROM customer c, products p, orders o
  WHERE p.order_id = o.id AND o.customer_id = c.id
    AND c.city = 'ATLANTA'

Including Variables

The database control uses the same variable substitution mechanism that you use to map incoming XML values to method parameters and vice versa. That is, you can include an incoming methods parameter in a database statement by surrounding it with {}s. You don't need to include the quotes for string fields, WebLogic Workshop handles that for you. For example, suppose you create a method that updates personnel information, like this:

public void updatePersonnel(String id, String firstName, String lastName);

You can substitute the parameters into an update statement like this:

 * @jws:sql statement::
 *  UPDATE personnel UPDATE first_name={firstName}, last_name={lastName}
 *    WHERE id={id}
public void updatePersonnel(String id, String firstName, String lastName);

Getting a Result Set

Although the database control has an easy way to map incoming parameters into an SQL statement, mapping SQL return values into method return values is more difficult. The problem is that a Java method can only return a single value—a primitive type or an object.

Returning a Variable

If a SELECT statement returns a single value, the database control can automatically return the result, like this:

 * @jws:sql statement="SELECT last_name FROM personnel WHERE id={id}
public String getLastName(String id);

The INSERT, UPDATE, and DELETE statements each return an integer value indicating the number of rows that have been inserted, updated, or deleted. You can return this value from a database control method:

 * @jws:sql statement="DELETE FROM personnel WHERE id={id}"
int delete(String id);

In this case, the return value of the delete method is the number of rows actually deleted.

Returning a Row of Results

If a statement returns multiple values for a single column (as opposed to multiple columns), you can return an array of all the values:

 * @jws:sql statement="SELECT last_name FROM personnel"
String[] getAllLastNames();

You can limit the number of items returned in an array with the array-max-length attribute:

 * @jws:sql statement="SELECT last_name FROM personnel"
 *   array-max-length="25"
String[] getFirst25LastNames();

By default, the database control limits the number of rows returned to 1,024. Use "all" with array-max-length to force the control to return all values no matter how many there are.

Returning a Class

Returning single values or an array of values from a single column isn't very useful in a large application. You usually need to retrieve many values. The database control can map column values to fields in a Java class, as long as the field names match the column names.

For example, to retrieve id, first_name, and last_name from the personnel table, you can use the following class:

public class Personnel
  public String id;
  public String first_name;
  public String last_name;

  public Personnel()

Now, to retrieve all the values from the personnel table, use the following declaration:

 * @jws:sql statement="select * from personnel"
public Personnel[] getAllPersonnel();

Returning a HashMap

If you don't want to write new classes for every variation of columns that you might retrieve, you can simply return a HashMap (a Java data structure that associates keys with values) or an array of HashMaps (if you want to return multiple database rows). The database control stores each column value in the HashMap using the column name as the key, for example:

 * @jws:sql statement="select * from personnel"
public HashMap[] getAllPersonnel();

To fetch the value of the "firstName" column for the first row returned by the getAllPersonnel method, you could use a statement like this:

HashMap[] personnel = getAllPersonnel();
String firstName = (String) personnel[0].get("firstName");

Returning a Multiple Row Result Set in a Container

For managing large data sets, you might want to use a Java iterator instead of returning an array of values. To use an iterator, you must use the iterator-element-type attribute to specify what kind of object the iterator should return.

To iterate through the personnel table, use the following declaration:

 * @jws:sql statement="select * from personnel"
 *   iterator-element-type="Personnel"
public java.util.Iterator getAllPersonnel();

The database control can also return an iterator that returns HashMaps. Simply specify java.util.HashMap as the iterator element type. To access each of these iterators, you could do something like this:

Iterator iter = getAllPersonnel();
while (iter.hasNext())
  HashMap row = (HashMap) iter.next();
  String lastName = (String) row.get("lastName");
  // do something with lastName

Page 1 of 3

This article was originally published on December 12, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date