Oracle9iAS Portal Developer Kit
Installing the DBPersonalizationManager Sample

The DBPersonalizationManager sample provider illustrates the use of an alternative implementation of the PersonalizationManager interface, DBPersonalizationManager, to maintain portlet customization data in a relational database. In order for the DBPersonalizationManager to be able to service multiple requests in parallel without suffering from performance overheads, it makes use of a simple database connection pool, managed by the ConnectionManager singleton utility class. This article describes how to configure your environment in order to run the sample, and should also be useful to anyone who plans to use DBPersonalizationManager and / or ConnectionManager in portlets of their own.

### ASSUMPTIONS

1. You have already installed the PDK-Java and core samples and understand the steps required to display a Web portlet on an Oracle9iAS Portal page. For more information, please review the article Installing the PDK-Java Framework and Samples.
2. You have a relational database at your disposal, and have access to the client software required to connect to the database interactively (e.g. `sqlplus` on Oracle).
3. You have a compatible JDBC driver installed in your Webserver's classpath. The JDBC driver must support a mapping to the `java.sql.Types.LONGVARBINARY` type (e.g. the `LONG RAW` type in Oracle). See the documentation for your JDBC driver for more information. For best results, we recommend use of an Oracle database, version 8.1.6 or later, for which appropriate JDBC drivers can be downloaded from the 'Downloads' area of the Oracle Technology Network site. If you are using Oracle HTTP Server, then the drivers should come pre-installed (look for `classes111.zip` or `classes12.zip` in `jserv.properties`).

### CONFIGURING THE DATABASE

This section describes how to create the table required by the DBPersonalizationManager class.

1. Set the current directory to be the `providers/dbPersonalizationManager` directory under the JPDK root directory, e.g.:

``
`cd D:\jpdk\providers\dbPersonalizationManager`
``

2. Connect to your database as the user who you wish to own the DBPersonalizationManager table. Make sure this user has the necessary privileges to create tables and indexes. E.g.:

``
`sqlplus scott/tiger@mydb`
``

3. Create a table `JPDK_PREFERENCE_STORE` with the following design:

Column Name | XOPEN Datatype | Length | Mandatory | In Primary Key?
--- | --- | --- | --- | ---
`REFERENCE_PATH` | `VARCHAR` | 250 | Yes | Yes
`LOCALE` | `VARCHAR` | 5 | Yes | Yes
`PATH_TYPE` | `VARCHAR` | 60 | Yes | Yes
`SEARCH_PATH` | `VARCHAR` | 250 | Yes | No
`DEFAULT_FLAG` | `VARCHAR` | 1 | No | No
`DATA_CLASS` | `VARCHAR` | 250 | No | No
`LONG_VALUE` | `LONGVARBINARY` |  | No | No

For best performance, you should also create indexes on the `SEARCH_PATH` and `DEFAULT_FLAG` columns.

To quickly set up the `JPDK_PREFERENCE_STORE` table on an Oracle database, you simply need to run the `jpdk_preference_store.sql` script, e.g. by typing the following in `sqlplus`:

> `@jpdk_preference_store`

For other databases, the second column in the table above gives for each database column the XOPEN standard name used by JDBC (e.g. in java.sql.Types) to denote its datatype. Your database vendor may use different keywords to denote these types, e.g. the Oracle JDBC drivers map `VARCHAR` to the `VARCHAR2` Oracle type, and `LONGVARBINARY` to the `LONG RAW` Oracle type. Consult the documentation for your JDBC drivers to determine the correct type keywords to use.

### CONFIGURING CONNECTIONMANAGER

1. Set the current directory to be the `providers/dbPersonalizationManager` directory under the JPDK root directory, e.g.:

``
`cd D:\jpdk\providers\dbPersonalizationManager`
``

2. Use a text editor to edit the `connectionManager.xml` file in this directory so that there is a `<connection>` element which describes the attributes of the database account in which you set up the `JPDK_PREFERENCE_STORE` table. The element should contain:

- a uniquely identifying `<name>` element
- `<username>` and `<password>` elements describing the authentication details of the account in which you set up the `JPDK_PREFERENCE_STORE` table (if required)
- a `<dburl>` element stating the JDBC URL to your database. For example, to use the Oracle 'thin' JDBC driver (recommended), you would use a URL of the form `jdbc:oracle:thin:@host:port:sid`, and to use the Oracle OCI driver you would use a URL of the form `jdbc:oracle:oci8:@tnsname`. Consult the documentation for your JDBC drivers for more details of the appropriate URL to use.
- a `<driver>` element stating the full Java class name of your JDBC driver. If unspecified, the default value is `oracle.jdbc.driver.OracleDriver`.

You can also control further connection pool attributes with elements such as `<minCount>`, `<maxCount>`, `<maxWaitTime>` and `<maxIdleTime>`. See the ConnectionManager javadoc for more information.

`<?xml version="1.0"?>` `<connectionManager>` `<minCount>6</minCount>` `<maxCount>25</maxCount>` `<maxWaitTime>60</maxWaitTime>` `<maxIdleTime>3600</maxIdleTime>` `<connection>` `<name>myConnection</name>` `<username>scott</username>` `<password>tiger</password>` `<dburl>jdbc:oracle:thin:@my.host.com:1521:ORCL</dburl>` `<driver>oracle.jdbc.driver.OracleDriver</driver>` `</connection>` `</connectionManager>`

``

### CONFIGURING THE PROVIDER

1. Set the current directory to be the `providers/dbPersonalizationManager` directory under the JPDK root directory, e.g.:

``
`cd D:\jpdk\providers\dbPersonalizationManager`
``

2. Modify the `ENTITY` declarations at the top of the `provider.xml` provider registry file so that:

- `virtualRoot` is your Webserver's path to the JPDK "htdocs" directory (beginning and ending with a "/"). If you followed the PDK-Java installation instructions, this should be `/jpdk/`.
- `physicalRoot` should be the path to the same directory on the filesystem (ending with a separator character).
- `pooledConnection` is the identifying name you used for the `<connection>` element in `connectionManager.xml` describing the connection to the database account containing the `JPDK_PREFERENCE_STORE` table.

`<!DOCTYPE provider [` `<!ENTITY virtualRoot "/jpdk/">` `<!ENTITY physicalRoot "E:\9iAS\Apache\Apache\htdocs\jpdk\">` `<!ENTITY pooledConnection "myConnection">` `]>`

### CONFIGURING YOUR WEBSERVER

1. Modify your Webserver configuration so that the system property `ConnectionManager.config` is set to the full path of the `connectionManager.xml` file when your JVM is started. If your JVM runs under JServ (e.g. in Oracle HTTP Server) you can achieve this with the `wrapper.bin.parameters` option in `jserv.properties`, e.g.:

``
`wrapper.bin.parameters=-DConnectionManager.config=D:\jpdk\providers\dbPersonalization\connectionManager.xml`
``

2. Configure a new servlet alias for the sample provider. E.g., in `zone.properties` add:

``
`servlet.dbp.code=oracle.portal.provider.v1.http.HttpProvider`
``

3. Configure the initialization parameters for the sample provider. In particular, you need to set the `initArgs` parameter to the full path of the `dbPersonalization` directory. Another useful parameter is the `debuglevel` parameter, which when set to 1 allows you to view your provider's 'test page'. E.g., in `zone.properties` add:

``
`servlet.dbp.initArgs=provider_root=D:\jpdk\providers\dbPersonalization, sessiontimeout=1800000, debuglevel=1`
``

4. Test your provider and connection pool have been configured correctly by pointing a browser at the URL to your provider in order to display its test page, e.g.:

``
`http://my.host.com/servlets/dbp`
``

If you experience errors at this stage, then carefully review all the previous configuration steps.

You should now be able to register the DBPersonalization sample provider on a portal using a URL containing the servlet alias you set up for your provider. Once registered, try adding some of the provider's portlets to a page and then customizing those portlets, e.g. by editing the portlet's title. This time, any customizations you make will be stored in the table you configured in your database.

Revision History:

- April 26, 2001