# Calling a Database Control From a Page Flow

This topic explains how a Page Flow can retrieve data from a database using a database control. This topic applies to custom database controls; RowSet controls (a specialized kind of database control) are discussed at RowSet Control.

## Declaring Controls and Calling Methods

To query a database from a Page Flow, (1) create a database control whose methods query the database, (2) declare the control within your Page Flow's Controller file (JPF file) and (3) call the methods on that control.

The following example shows a simple database control that queries the USERS table. Note that the control includes the data structure `User`. The `User` type is designed to model the data returned by the database. A single record on the USERS table is modeled by a single instance of the `User` type. Multiple rows are modeled by an array: `User[]`. For detailed information about modeling database data with Java types see Returning a Single Row from a Database Control Method and Returning Multiple Rows from a Database Control Method.

    public interface MyDatabaseControl extends com.bea.control.ControlExtension, DatabaseControl
    {

        /**
         * @jc:sql statement::
         * SELECT * FROM USERS
         * ::
         */
        public User[] getAllUsers();

        public static class User implements Serializable
        {
            public String username;
            public String password;

            public User()
            {
            }

            public User( String username, String password )
            {
                this.username = username;
                this.password = password;
            }
        }
    }

To declare a database control, create a member variable of the control class type, annotated with the `@common:control` annotation.

MyPageFlowController.jpf

        /**
         * @common:control
         */
        private controls.MyDatabaseControl control;

To call methods on the control, use the following syntax.

        User[] users = control.getAllUsers();

## Handling ResultSet, Iterator, and RowSet Types

If your database control contains methods that return `java.sql.ResultSet`, `java.util.Iterator`, or (uncached) `javax.sql.RowSet` types, you cannot call these methods directly from a Page Flow Controller file. (The reason you cannot call these methods directly from a Controller file is because these types depend on an open connection to the database, but the lifecycle of the database control ends, and cuts off the database connection, before the data can be returned to the Page Flow Controller.)

But you may call these methods indirectly by wrapping the database control in a custom Java control (JCS file).

Suppose you have a database control method that returns a `java.util.Iterator` type.

MyDatabaseControl.jcx

        /**
         * @jc:sql statement::
         * SELECT * FROM USERS
         * ::
         */
        public Iterator getAllUsers_Iterator();

You cannot call this method directly from a Page Flow Controller file. But you can call it indirectly through a Java control wrapper class, provided that you load the Iterator data into a data type that is not dependent on an active connection to the database, such as an ArrayList.

The following wrapper class shows how to call the database control method getAllUsers_Iterator() and load the Iterator data into an ArrayList. Note that the ArrayList contains instances of the User data type. An instance of User is created for each element in the Iterator.

WrapperImpl.jcs

    public class WrapperImpl implements Wrapper, ControlSource
    {
        /**
         * @common:control
         */
        private controls.MyDatabaseControl control;

        /**
         * Calls the database control method getAllUsers_Iterator()
         * and loads the Iterator data into an ArrayList (of User objects).

         *
         * @common:operation
         */
        public ArrayList getAllUsers_ArrayList() throws SQLException
        {
            Iterator it = control.getAllUsers_Iterator();
            ArrayList al = new ArrayList();
            while(it.hasNext())
            {
                User user = (User)it.next();
                al.add( user );
            }
            return al;
        }

        public static class User implements Serializable
        {
            public String username;
            public String password;

            public User()
            {
            }

            public User( String username, String password )
            {
                this.username = username;
                this.password = password;
            }
        }
    }

Now you can call the wrapper method getAllUsers_ArrayList() from a Controller file.

MyPageFlowController.jpf

    public class MyPageFlowController.jpf extends PageFlowController
    {
        /**
         * @common:control
         */
        private controls.Wrapper wrapper;

        public ArrayList getAllUsers() throws SQLException
        {
            return wrapper.getAllUsers_ArrayList();
        }

        ...

    }

## Design Advantages to Wrapping Database Controls

Even if your database control does not return the ResultSet, Iterator, or uncached RowSet types, in some situations there are advantages to wrapping your database control in a custom Java control.

By wrapping your database control in a custom Java control, the Controller file is abstracted from the implementation details of the database control. If the implementation of the database control should change (for example, if it is replaced by a web service) you will not have to change code in the Controller file.

Also, wrapping the DB control inside of a custom Java control provides transactional support when updating multiple databases, because a custom Java control method defines a single transaction that commits or rolls back atomically.

Related Topics

Returning a Single Row from a Database Control Method

Returning Multiple Rows from a Database Control Method