RIFE logo
This page last changed on Dec 06, 2006 by gbevin.

Adding database support

So far, our Friends list is quite limited, since we don't have a place to store the list of friends yet. The last missing part is the database support, which we will add in this chapter. We assume that you are already somewhat familiar with SQL databases and in particular the Java framework for handling databases, JDBC.

Often when developing and deploying web applications, there's a need to handle some kind of dynamic data, like the list of friends in our example. Other examples might be news entries, articles, images, or why not all the contents of a web site? If a database is used as storage, it's easy to design very powerful and extensible applications that are easier to maintain than if the data would be scattered around in various files.

We'll show that incorporating a database into the design is a straightforward and easy task with RIFE.

The datasource participant

First of all, we need a datasources participant in the repository, and we'll also write a small configuration file for that participant. After editing rep/participants.xml, it looks like this:

Adding a data source participant
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE rep SYSTEM "/dtd/rep.dtd">

<rep>
  <participant param="rep/config.xml">ParticipantConfig</participant>

  <!-- Add a data sources participant: -->
  <participant param="rep/datasources.xml">ParticipantDatasources</participant>

  <participant param="sites/friends.xml">ParticipantSite</participant>
</rep>

Then we write the data sources definition file specified above, rep/datasources.xml, to set up two different data sources:

ata source definition
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE datasources SYSTEM "/dtd/datasources.dtd">

<datasources>

  <datasource name="postgresql">
    <driver>org.postgresql.Driver</driver>
    <url>jdbc:postgresql://localhost:5432/rife</url>
    <user>rife</user>
    <password>rife</password>
    <poolsize>5</poolsize>
  </datasource>

  <datasource name="mysql">
    <driver>com.mysql.jdbc.Driver</driver>
    <url>jdbc:mysql://localhost:3306/rife</url>
    <user>rife</user>
    <password>rife</password>
    <poolsize>5</poolsize>
  </datasource>

</datasources>

The reason we use two different data sources (one for PostgreSQL and one for MySQL) is to make the application more useful without the need to change the code just because one or the other is not available. A good example of this is when the configuration of the developer machine is different than the one running the application. The attentive reader probably recalls configuration selectors from the previous chapter, and points out that a host name selector would be excellent in that case!

Another benefit is that if the code works equally well with different databases, the chances of it being usable for other people or in other projects increase a lot. Code reuse is always a good thing, and can save time by not forcing people to re-invent the wheel time after time.

RIFE comes with built-in support for the PostgreSQL, MySQL and Oracle databases. Custom drivers can be written if another database is used, but that is outside the scope of this document.

Datasource parameters

There are a few parameters to set for each datasource:

Username and password

The user and password tags set the username and password to use for connecting to the database, and should hopefully be fairly self-explanatory.

Database driver

The driver tag specifies which database driver to use, and is one of org.postgresql.Driver, com.mysql.jdbc.Driver and oracle.jdbc.driver.OracleDriver, if one of the included drivers is used.

URL

The url tag specifies the location of the database. The first part, "jdbc" is the main protocol, "postgresql" or "mysql" is the sub-protocol, followed by the host name "localhost" and port number to connect to. The last part, "rife", is the name of the database. Of course, these are all just example values that you might have to modify according to your actual setup.

Connection pool

Since connecting to the database can be time consuming, RIFE can keep a pool of open database connections. This can increase the performance, especially for web sites that generate a lot of traffic. The poolsize tag controls the number of connections in the pool, where 0 means that no pool is used. We'll go for a moderate value of 5 here.

Selecting a data source

Since we have two different datasources, we need to be able to choose which one to actually use. The configuration system in RIFE makes this rather easy to control, we just add a configuration parameter that indicates which datasource has to be used. The database code can then read that parameter and do the right thing depending on its value. After adding the datasource parameter, our rep/config.xml file should look like this:

Selecting a data source through the configuration system
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE config SYSTEM "/dtd/config.dtd">

<config>
  <param name="DISPLAY_TITLE">Friends of Mikael</param>

  <!-- Add a DATASOURCE parameter: -->
  <param name="DATASOURCE">postgresql</param>

</config>

The new parameter is called DATASOURCE, and should be set to the name of the source to be used.

The Friend bean

We need a data structure to keep the name, description, and URL for each of our friends. A bean will be a perfect container for that information, because a lot of the RIFE interfaces can work with beans instead of handling a lot of properties individually. This will be more and more obvious as we learn more.

Friend bean class
package tutorial.friends.backend;

public class Friend
{
  private String  mFirstname = null;
  private String  mLastname = null;
  private String  mDescription = null;
  private String  mUrl = null;

  public Friend(String firstname, String lastname, String description, String url)
  {
    mFirstname = firstname;
    mLastname = lastname;
    mDescription = description;
    mUrl = url;
  }

  public void setFirstname(String firstname)
  {
    mFirstname = firstname;
  }

  public String getFirstname()
  {
    return mFirstname;
  }

  // ... and so on for lastName, description and url

}

The set and get methods for lastName, description and url are omitted from the snippet here to save some space, but they look just like firstName.

Managing the data

To actually access the data source, we will write a small class that handles creating the table and doing queries for adding and retrieving friends. RIFE makes it easy: we just extend com.uwyn.rife.database.DbQueryManager, which takes care of a great deal of work, leaving the essential bits for us to implement. The code can be found in * src/tutorial/friends/backend/FriendManager.java*, and should be pretty clear and comprehensible. There is a bit of code, so we'll walk through the most important bits.

Using the right data source

In this example, we added a configuration parameter to select which of the two data sources to use. We can use this parameter to initialize the manager with the right source:

Using the selected data source
public FriendManager()
{
  super(Datasources.getRepInstance().
    getDatasource(Config.getRepInstance().getString("DATASOURCE")));
}

Now the query manager will be initialized with the data source that corresponds to the name we entered in the configuration.

Displaying the friends

The template and element for displaying the list of friends are already written. All that needs to be done is to add the database functionality. We need to create a SQL query, execute it in the database and finally iterate over the resulting rows. Fortunately RIFE, again, provides a nice way to do all this. The easiest way to explain is by showing the code, the method display in FriendManager:

etrieving the friends list
public void display(DbRowProcessor processor)
{
  Select select = new Select(getDatasource());
  select
    .from("Friend")
    .fields(Friend.class)
    .orderBy("firstname");

  // fetch every row in the resultset and forward the processing
  // of the data to the DisplayProcessor
  executeFetchAll(select, processor);
}

This method, display, is called from the element to retrieve all the friends and insert them into the template. The query is constructed using the Friend bean class, which means that we don't have to specify all the fields, it just uses all the bean properties.

Displaying one friend at a time

After executing the query, we iterate over the result by calling the DbQueryManager method fetch until all the rows are processed. This is where the DbRowProcessor object, that is passed in as an argument, comes in handy.

The row processor is a way to keep the database code separated from the element and output generation code. Our FriendManager doesn't know anything about the elements that use it, and the elements don't know anything about the database code. This kind of design is almost always desirable since it makes the application a lot easier to maintain and develop.

Writing a row processor

Writing a row processor is simple. We just need to extend DbRowProcessor and implement the method processRow. Since this processor won't be needed anywhere outside the display element, it can be implemented as an inner class in tutorial/friends/Display.java:

Our row processor
private class DisplayProcessor extends DbRowProcessor
{
  private Template mTemplate = null;

  /**
   * The constructor requires a Template instance in which the retrieved
   * data will be filled in.
   *
   * @param template the Template in which the results will be display
   */
  DisplayProcessor(Template template)
  {
    mTemplate = template;
  }

  public boolean processRow(ResultSet resultSet)
  throws SQLException
  {
    mTemplate.setValue("firstname",
                       encodeHtml(resultSet.getString("firstname")));
    mTemplate.setValue("lastname",
                       encodeHtml(resultSet.getString("lastname")));
    mTemplate.setValue("description",
                       encodeHtml(resultSet.getString("description")));
    mTemplate.setValue("url",
                       encodeHtml(resultSet.getString("url")));

    mTemplate.appendBlock("rows", "row");

    return true;
 }
}

The separation between database and HTML generation is very clear here. The only thing the row processor does is getting the different values from the result set and using them to fill in the template.

The only thing left to do in the element's processElement method, is to create a manager and invoke display with our row processor:

FriendManager manager = new FriendManager();
DisplayProcessor processor = new DisplayProcessor(template);

manager.display(processor);

Adding friends

A database with a list of friends isn't that useful if new friends can't be added to it, is it? Now that the database framework is there, we can extend our application with an element, a template and the backing database code for adding friends.

Declaring the new element

The ADD element is pretty simple, it has no exits other than the global menu and no datalinks. The only thing we haven't talked about before is the use of a submission bean. Let's take a look at the site file and the element file, and then explain how submission beans work:

Updating the site file
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE site SYSTEM "/dtd/site.dtd">

<site>
  <arrival destid="DISPLAY"/>

  <globalexit name="menu" destid="MENU"/>

  <element id="DISPLAY" file="display.xml" url="/display"/>

  <element id="MENU" file="menu.xml" url="/menu">

    <!-- Add a flowlink to the new add element -->
    <flowlink srcexit="add" destid="ADD"/>

    <flowlink srcexit="display" destid="DISPLAY"/>
  </element>

  <!-- Add the new element -->
  <element id="ADD" file="add.xml" url="/add"/>

</site>

The comments in the site file points out the changes: a new flowlink from the menu to the add element, and a declaration of the new element itself.

Element for adding a friend
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE element SYSTEM "/dtd/element.dtd">

<element implementation="tutorial.friends.Add">
  <submission name="friend_data">
    <bean classname="tutorial.friends.backend.Friend"/>
  </submission>
</element>

Submission beans

A submission bean is, like many of RIFE's features, an abstraction of a common web application task, more specifically handling the input from a form.

Submission beans are set up similarly to regular submission parameters, except that instead of listing one or more parameters, a bean is used, as seen above.

classname points to the class of the bean, in this case our representation of a friend. After setting up the bean like this, getSubmissionBean can be used to get a bean with its properties set to the values entered by the user in the submission form:

Friend friend = (Friend)getSubmissionBean("friend_data", Friend.class);

This way, fewer lines of code are needed and there are less places to change if properties are added to or removed from our friend representation, compared to using submission parameters.

The add template

Finally, we only have the template left to write, which is more or less similar to the submission template for the numberguess game.

Template for adding a friend
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
                      "http://www.w3.org/TR/html4/strict.dtd">

<html>
  <head><title>Add a new friend to the database</title></head>
  <body>
    <h3>Add a friend</h3>

    <!--V 'content'/-->

    <!--BV 'content'-->
      <form name="friend_data" action="[!V 'SUBMISSION:FORM:friend_data'/]"
            method="post">

        <!--V 'SUBMISSION:PARAMS:friend_data'/-->
        Firstname<br/>
        <input type="text" name="firstname" length="51" maxlength="50"/><br/>
        Lastname<br/>
        <input type="text" name="lastname" length="51" maxlength="50"/><br/>
        Description<br/>
        <textarea name="description" cols="80" rows="6"></textarea><br/>
        Url<br/>
        <input type="text" name="url" length="80" maxlength="255"/><br/>
        <input type="submit" name="Add this friend."/>
      </form>
    <!--/BV-->

    <!--B 'content_added'-->
      <p>The friend has been added.</p>
    <!--/B-->

    <p><a href="[!V 'EXIT:QUERY:menu'/]">back to menu</a></p>
  </body>
</html>

Just like when using submission parameters, the values SUBMISSION:FORM: and SUBMISSION:PARAMS: are used to handle the submission bean.

Adding a friend to the database

Building and executing the insert query becomes particularly easy when we have all the data in a bean:

Adding a friend to the database
public void add(Friend friend)
throws DatabaseException
{
  Insert insert = new Insert(getDatasource());
  insert
    .into("Friend")
    .fieldsParameters(Friend.class);

  DbPreparedStatement insert_stmt = getConnection().getPreparedStatement(insert);
  insert_stmt.setBean(friend);
  insert_stmt.executeUpdate();
}

Finishing up the example

The friends database application is almost complete now, but before we can access any friends in the database, we need to create a table to keep the information in. We could leave this as an exercise for the admin that sets up the application, but we would like to make this a lot easier to use than that. A simple element that handles creating the table and likewise for removing it is a lot nicer, so let's do that.

Installing and removing the database table

As usual, there will be an element and a template for each new page we add, and the procedure should be fairly familiar by now. We add two elements to the repository:

<element id="INSTALL" file="install.xml" url="/install"/>
<element id="REMOVE" file="remove.xml" url="/remove"/>
They are simple elements with just a confirmation button to confirm the installation and removal, so there is no need to list them here. They are located in the files classes/elements/install.xml, classes/elements/remove.xml, classes/templates/install.html and classes/templates/remove.html for those who are interested.

Creating the table

The install element calls a method named install on the manager, which is where we will create the database table, by building the right query.

Method to install the Friends table
public void install()
throws DatabaseException
{
  CreateTable create = new CreateTable(getDatasource());
  create
    .table("Friend")
    .columns(Friend.class)
    .precision("firstname", 50)
    .precision("lastname", 50)
    .precision("url", 50)
    .nullable("firstname", CreateTable.NOTNULL)
    .nullable("lastname", CreateTable.NOTNULL)
    .nullable("description", CreateTable.NOTNULL)
    .nullable("url", CreateTable.NOTNULL);

  executeUpdate(create);

  // ...
}

Just like the other previous queries, the bean class is used to specify which columns to create. The gain is not as big in this case since we have to set more properties than just the name and type of the columns, such as the string length and the nullable property. Nonetheless, using the bean class makes it quite clear that the table is acting as a storage for the Friend bean, and code clarity is always a good cause.

Cleaning up

Removing the table is done in the same way as installing it, except that DropTable is used instead of CreateTable.

Method to remove the Friends table
public void remove()
throws DatabaseException
{
  DropTable drop = new DropTable(getDatasource());
  drop.table("Friend");
  executeUpdate(drop);
}

We're done!

Now we have a simple friends database application, that let's us keep track of friends. We've learned about things like global exits and beans, different kinds of participants and how to add configuration and a database backend with a RIFE application.

The friends database could still be more advanced, so there is plenty of room for the experimental mind to add features. Why not add the ability to edit the information about friends?

Document generated by Confluence on Oct 19, 2010 14:57