locomotive.org


Chapter V. Third Example: The Graffiti Wall:

Interacting with the Database

If you have ever lived near a large city, you've probably noticed that sometimes large walls have been "tagged" with graffiti. People come by and scrawl drawings, their signatures, and sometimes, even colorful remarks. But have you ever considered whether that might be an entirely new and distinct form of human communication? In other words, a wall of graffiti is a place where people come and express their opinion, on any subject whatsoever, in any blank space they can find. Their opinion will stay there until someone comes and erases it.

Think of a "graffiti wall" as a place where people jot down open-ended thoughts, until someone comes and erases those thoughts, possibly in order to make space to write new thoughts. Well, we at Leverage thought that this might be an interesting demo application to provide to our clients. In the Locomotive, the Graffiti Wall is a place where people visit a certain location (a HTML template) where they can view everything that other people have written. They can also add something new to that location; there is a text field on the Graffiti Wall page to allow them to do that. There are 10 spaces available for people to write their thoughts. Whenever someone submits a new thought, which we call a "Tag", that new Tag will overwrite the oldest Tag that is on the wall. Any user, including anonymous users, can submit new Tags, and any user can view the Graffiti Wall.

For our purposes here, the Graffiti Wall is also an excellent example of a simple application which interacts with the database. In this example, we will use some SQL statements and some JDBC method calls. If you don't know SQL, we suggest that you learn it before writing code for the Locomotive. (Don't worry, it's not very hard. You can probably learn something about it just from reading this example.) We'll be looking at the structure of the table in the database which stores Tags, as well as the Java code to store and read Tags from the database.

The Architecture of the Graffiti Wall

Before we dive into the code, let's talk about the architecture of the Graffiti Wall. The Graffiti Wall is a very simple module, with one GenericHandler subclass called GraffitiWallHandler. GraffitiWallHandler stores and reads Tags via the Tag class. The Tag class handles all the internal work of storing or reading Tags from the database. It also contains all the information about that Tag, as well as any methods dealing with Tags. In general, you'll find that it is common to store an entity as a table in the database, and then represent it within Java as a Java class. This approach satisfies the kind of object encapsulation that object-oriented programmers love.

When a user comes to the Graffiti Wall, the GraffitiWallHandler will ask the Tag class to load all the Tags from the database, via an SQL "select..." statement, and display them within an HTML Table. Each row of the HTML Table will display one Tag. When a user submits a new Tag, that Tag will be saved to the database using an SQL "update..." command. That command will simply replace the oldest Tag with the Tag that the user has just submitted.

And that's all there is to it. See, I told you. First, we're going to look at how Tags are stored in the database. Because we have already discussed how Handlers and GenericHandlers interact, we'll let you read the code for GraffitiWallHandler on your own.

The Structure of the Tag Table in the Database

First, let's discuss the structure of the Tag table in the database. In the Locomotive distribution, in the file "db/demos/graffiti.sql", you can find the SQL statements that create the Tag table in your database: 
create table loco_graffiti_tags (
    tag_id           number (20) PRIMARY KEY,
    tag_author       varchar2 (32),
    tag_text         varchar2 (2000),
    ins_time         date
) tablespace loco_app_ts;
In English, what this means is that we will create a new entity in the database, a new kind of object: a table called "loco_graffiti_tags", which has four fields, or "columns" in database lingo. The first column, tag_id, is the PRIMARY KEY of this table, which is used to uniquely identify each entry, or "row", in the loco_graffiti_tags table. Every row must have the PRIMARY KEY field filled in -- it cannot be empty. The PRIMARY KEY is always "indexed" for fast access. The second column, tag_author, stores the username of the user who submitted this Tag. The third column, tag_text, stores the text of the Tag. The fourth column, ins_time, stores the time and date when this Tag was written into the database. A field of type varchar2 (32) contains text that can have variable length, from being empty to 32 characters. varchar2 fields only take up as much space as they need, so they're efficient.

To learn about what tablespaces are, consult your database manual.

Loading Tags from the Database

Now that you understand the structure of the Tag table in the database, let's take a look at the method which loads all tags from the database, Tag.loadAllTags (conn). This is a static method of the Tag class. The Tag class has methods for dealing with individual Tags, but it also has methods for dealing with multiple Tags and for initializing the database table which stores Tags. Here's the code for Tag.loadAllTags (conn): 
/**
 * loads in all rows from the db, sorted by date in descending order.
 * if there are no rows in the table, an array of zero length
 * (Tag[0]) is returned.
 */
public static Tag[] loadAllTags (Connection conn)
    throws SQLException
{
    String statement =
        "select tag_author, ins_time, tag_text from " +
        "loco_graffiti_tags order by ins_time desc";
    Statement stmt = null;
    ResultSet rs = null;
    Vector v = new Vector();

    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery (statement);

        while (rs.next())
        {
            // uses private constructor
            v.addElement (new Tag (rs.getString (1),
                                   rs.getTimestamp (2),
                                   rs.getString (3)));
        }
    }
    finally
    {
        if (rs != null)  rs.close();
        if (stmt != null)  stmt.close();
    }

    Tag[] tags = new Tag[v.size()];
    v.copyInto (tags);
    
    return (tags);
}
The purpose of this method is to read every Tag into memory, and collect them all into an array of Tags. It will issue an SQL query to the database and then read the results, one row at a time, putting them into Tag objects.

First, let's look at the SQL query statement: 

String statement =
   "select tag_author, ins_time, tag_text from " + "loco_graffiti_tags
   order by ins_time desc";
When translated into English, the "select..." statement means: "for every row (or 'entry' or 'record') of the table called loco_graffiti_tags, give me the columns (or 'fields') named tag_author, ins_time, and tag_text. Also, return these results sorted by descending ins_time." Of course, SQL is much more concise. You send this SQL command to the database by using the following two methods: 
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery (statement);
Statement and ResultSet are classes in the java.SQL package. You can refer to the Java class documentation to read about them. Roughly, an instance of Statement class represents a single SQL statement to be sent to the database to be executed. An instance of ResultSet class represents the results, which are a list of rows, that are returned by a query to the database. After "rs = stmt.executeQuery (statement)" is called, the variable "rs" will be associated with a database "cursor" which points to a list of rows. Before you can access each row, you must call the method rs.next() (even before the first row) to make the database cursor point to that row: 
while (rs.next())
{
    // uses private constructor
    v.addElement (new Tag (rs.getString (1),
                           rs.getTimestamp (2),
                           rs.getString (3)));
}
And then, all you need to do to read each column of the current row is to call one of the get...() methods of ResultSet, e.g. getString(). The 1 in getTimestamp(1) means "the value in column 1, which must be a Timestamp", and refers to the first column specified in the "select..." statement, which was "time_submitted". Note that you should always use the class Timestamp from the package java.SQL to represent dates from the database. java.SQL.Date and java.util.Date are NOT compatible with JDBC dates! 
rs.close();
stmt.close();
Also, always remember to close ResultSets and Statements. It's better protocol. If you don't close them, it might affect your database Connections. Notice that the following code is enclosed within a finally { } clause: 
finally
{
    if (rs != null)  rs.close();
    if (stmt != null)  stmt.close();
}
This is because we want to close the ResultSet and Statement we used, no matter what, even if there were an SQLException or some other problem. If we forget to close our ResultSets, we may eventually run out of database cursors. Here's why. In the Locomotive, we don't create a new Connection for every Handler that is executed. To save overhead, we re-use Connections by placing them into something called a DBConnectionPool. We withdraw a Connection from this pool when a Handler starts, and after the Handler finishes, we return the Connection back to this pool.

Now, because Connections are never closed, any ResultSets associated with them which have not been closed will stay around -- and since the database associates a cursor with each ResultSet, those cursors stay around as well. Well, databases usually allocate a limited number of cursors, and, once all the cursors are used up, no more database clients (such as the Locomotive) can connect to the database. So, remember to close your ResultSets and Statements!!!

Okay, now back to our main program, the source code. Once we've read all the Tags from the database and stored them into the Vector "v", that we take all the information inside "v" and put it into an array of Tags: 

Tag[] tags = new Tag[v.size()];
v.copyInto (tags);
return (tags);
(You know, Vector should really have a method called toArray().)

Displaying Tags As Rows of an HTML table

Okay, now that we have an array of Tags, how do we display them? Let's look at the GraffitiWallHandler method which formats an array of Tags into HTML Rows: 
private String getTagsAsHTMLRows()
   throws SQLException
{
    StringBuffer html_rows = new StringBuffer();

    int index = 0;
    Tag[] tags = Tag.loadAllTags (conn);

    // the following will allow us to format a date to look like:
    //     Feb 14  at 8:45:00 PM
    SimpleDateFormat timeFmt = new SimpleDateFormat
        ("MMM d 'at' h:mm:ss a");

    for (int index = 0; index < tags.length; index++)
    {
        if (tags[index].getText() != null)
        {
            html_rows.append("<TR>");
            html_rows.append("<TD>");
            html_rows.append(timeFmt.format(tags[index].getInsTime()));
            html_rows.append("</TD>");

            html_rows.append("<TD>");
            html_rows.append(tags[index].getAuthor());
            html_rows.append("</TD>");

            html_rows.append("<TD>");
            html_rows.append(tags[index].getText());
            html_rows.append("</TD></TR> \n");
        }
    }

    return new html_rows.toString();
}
If you've ever written any code to display HTML Rows before, this method will seem familiar to you. <TR> means "start a row", and </TR> means "end this row". <TD> means "start a column in the current row", and </TD> ends that column. The code above reads all the Tags from the database, using the method "Tag.loadAllTags (conn)", and then, it formats each Tag as an HTML row. In this row, the first column displays the exact time and date that this Tag was submitted. The second column displays the username who submitted it, which might be "anonymous". The third column is the actual text submitted. All the rows are concatenated together into one big String, which is returned. This String will be put into a Steam variable called TAG_ROWS, which is inserted into a HTML template (in templates/examples/graffiti/graffiti.stm), where it will be enclosed by an HTML Table: 
<TABLE border="0">
[TAG_ROWS]
</TABLE>
The reason why we don't also include the <TABLE>...</TABLE> HTML tags in TAG_ROWS is because you may want to give your HTML Table special formatting, such as a special color, spacing between cells, or borders. So, returning the HTML rows gives you more flexibility.

The [table ...] Steam Command

By the way, if you think that writing a method to create rows for an HTML table is tedious, boy, do I have good news for you! We recently added a command to the Steam language called [table...] which allows you to iterate through the results of an SQL query using Steam. We are very excited about this new command. With this command, we can do all the work in both Tag.loadAllTags() and in GraffitiWallHandler.getTagsAsHTMLRows() in a few lines of Java and Steam: 
/**
 * Alternate, MUCH easier way to return an HTML Table of Tags.
 */
public static displayTags (Connection conn)
    throws SQLException
{
    String statement =
        "select ins_time, tag_author, tag_text from " +
        "loco_graffiti_tags order by ins_time desc";

    Statement stmt = null;
    ResultSet rs = null;

    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery (statement);

        // Create a SortableTable with the capacity for 
        // "maxRows" rows and 3 columns.
        SortableTable table = new SortableTable (maxRows, 3);

        // load all the results from the query, in ResultSet rs,
        // into the table.
        table.load (rs, true);

        // add the table as a Steam variable.
        steam_vars.put ("TAG_TABLE", table);
    }
    finally {
        if (rs != null)  rs.close();
        if (stmt != null)  stmt.close();
    }
}
Then, within your HTML template, you simply say: 
<TABLE border="0">
[table TAG_TABLE
<TR>
<TD>[colname INS_TIME]</TD>
<TD>[colname TAG_AUTHOR]</TD>
<TD>[colname TAG_TEXT]</TD>
</TR>
]
</TABLE>
The [table...] command iterates through every row in the SortableTable object named "TAG_TABLE", which is a Steam variable that was given a value in the Java code. For every row, it inserts all the text after TAG_TABLE until the end bracket, ']'. The columns INS_TIME, TAG_AUTHOR, and TAG_TEXT get their names from the names of the fields of your SQL query statement: 
"select ins_time, tag_author, tag_text from "...
Isn't the [table] command cool??!! It rocks!

Saving a Tag to the Database

Okay, so now you know how to read Tags from the database. Now, what if the user wants to submit a new Tag? Well, if you read the code in GraffitiWallHandler, you'll find that, after all the error checking is done, and we're ready to save a Tag into the database, we call the new Tag () constructor: 
new Tag(conn, authors_username, text);
And here's the code for this constructor method: 
public Tag (Connection conn, String author, String text)
    throws SQLException
{
    this.author = author;
    this.text = text;

    overwriteRow(conn);

    // might not be the exact same as the date from the DB, but
    // it will be close enough for government work.
    this.insTime = new Timestamp(System.currentTimeMillis());
}

private void overwriteRow(Connection conn)
    throws SQLException
{
    String statement = null;
    Statement stmt = null;

    switch (config.DB_TYPE)
    {
    case LOCOConfig.ORACLE:
        statement = "update loco_graffiti_tags " +
            "set tag_id = loco_tag_id_sq.nextval, " +
            " tag_author = " + SQL.quoteString (author) +
            ", tag_text = " + SQL.quoteString(text) +
            ", ins_time = SYSDATE where " +
            "tag_id = (select min(tag_id) from loco_graffiti_tags)";
        break;
    case LOCOConfig.INFORMIX:
        statement = "execute procedure overwrite_graf_tag ( " +
            SQL.quoteString (author) + ", " + SQL.quoteString(text) +
            " )";
        break;
    }

    try {
        stmt = conn.createStatement();
        stmt.executeUpdate(statement);
    }
    finally {
        if (stmt != null)  stmt.close();
    }
}
In the previous SQL statement, notice how we used SQL.quoteString() around both the "author" and "text" variables. (The SQL class is part of the org.locomotive.util package.) That is because those string values may contain characters which SQL databases would interpret in a special way, such as '%' or '&' or the single-quote character itself, '.
*** Remember: always use SQL.quoteString() around any of your Strings when saving them to the database, unless you are using the setString() method of PreparedStatements (see below).

Let's talk about the last line of the previous SQL statement: 

" where tag_id = " +
" (select min(tag_id) from loco_graffiti_tags)";
What this means exactly is, "find the Tag in the database with the smallest tag_id -- in other words, the oldest Tag." So, this "where..." subclause means that the "update..." statement will change only one row in the tags table, the row with the oldest Tag. Notice that tag_id is both a part of the "where..." clause, as well as one of the fields that will be saved in the "update..." clause. As a part of the "where..." clause, tag_id is referring to the tag_id field of a Tag to be found, while, as a part of the "update..." clause, tag_id refers to the new value to be saved.

Configuring the Locomotive to Use Different Types of Databases

Notice that in the code for overwriteRow(), we actually executed two different SQL statements, depending on which of the two types of databases we were using. This is an example of how the Locomotive can interact with different types of databases. In the code for overwriteRow(), we need two different SQL statements because Oracle and Informix databases differ in two important ways:
  1. In Oracle databases, the word that means "the current time and day" is SYSDATE, which can be used as a value for a date field. In Informix databases, that word is CURRENT.
  2. We need some way to ask the database to generate an ever-increasing sequence of integers. In Oracle, this can be accomplished with Sequences, but in Informix, we must use a stored procedure.
In order to know which database we're using, we get the value from the local variable, "config": 
    switch (config.DB_TYPE)
Now, where does the "config" variable get its DB_TYPE value? Actually, it is set in the Locomotive configuration file, loco.conf. For Oracle, we add the following line in loco.conf: 
    DB_TYPE                   oracle
For Informix, we add instead: 
    DB_TYPE                   informix
In order to use this information, first we need to access the Locomotive's global configuration file. Instead of accessing the file directly, we access the configuration variables through a LOCOConfig object: (this line comes from the variable declarations section at the top of the Tag class) 
private static LOCOConfig config = LOCO.getConfig();
Once we have this config object, then we can find out what kind of database we're using, by accessing config.DB_TYPE.

Accessing Configuration Variables

The previous example was an example of accessing the Locomotive's global configuration file. Actually, you would normally access configuration variables using the following two access methods of the MultiConfig class: 
getString (String config_variable_name);
getInt (String config_variable_name);
For example, LOCO.getConfig().getString ("LOCO_TEMPLATE_ROOT_PATH") will return the file path of the directory that contains all HTML templates for this Locomotive.

Creating Your Own Configuration File for Your Handler

Often, you'll want to have a configuration file for your own Handlers. This is a somewhat more advanced topic. You can create a new subclass of org.locomotive.server.MultiConfig, and then create a new configuration file for your own Handler. The access methods can be the same, getString() or getInt(). Or, you can write your own subclass of the java.util.Properties class or java.util. PropertyResourceBundle class.

Update vs. Insert And Delete

Now, if you're sharp (and I know you are), and you know how the SQL statement "update..." works, you'll realize that there's a potential problem in the "update" SQL statement in the overwrite() method. "update..." will overwrite a row ONLY if that row exists in the table. What if there are no rows in the loco_graffiti_tags table? Then, the "update..." statement will happily do nothing, and your Tag will not be written to the database. Yikes! Is this a bug?

Not exactly. You see, we designed the Graffiti Wall system so that it would make sure, at the beginning, that there would be exactly 10 rows in the loco_graffiti_tags table, before any GraffitiWallHandler was ever executed. In fact, it is in the init() method of GraffitiWallHandler that we do this: 

/**
 * The GraffitiWall architecture requires that a certain number of Tags
 * already exist in the database.  This is because
 * we are using "update" to overwrite Tags, rather than
 * an "insert" and then a "delete".
 */
public boolean init(Log server_log)
{
    Connection conn = RequestManager.getDBConnection();

    // if there are no tags, we should populate the tags table.
    Tag.prepopulate (conn);
}
Why do we use this approach in the first place, rather than inserting a Tag into the database each time, and deleting Tags only when there are more than 10 of them? The reason is, one "update..." SQL statement is faster, simpler, and less code than an "insert..." statement followed by a "delete..." statement. Plus, the "delete..." statement has to be "smart" enough to detect when there are 10 Tags in the database, which is a tricky issue.

Yet another issue is that if you must perform two SQL statements in order to accomplish one action, then that is more vulnerable to problems than using one SQL statement. For example, what if two different Java threads were trying to perform the same action at the same time? In this particular example, there may be no problems, but, since Java is a parallel programming environment, and since you can run multiple Locomotives on the same machine, you must always think about the pitfalls of parallel programming -- race conditions and deadlocking. You have to play out different scenarios; you have to "act like the computer". You need to think about synchronizing shared resources, both in your Java programs (using synchronized { }), and in your database transactions (by trying to accomplish single actions with single SQL statements, or, if you can't, by using locks). We can't give a whole parallel programming class here, but please learn about it so that you will understand the issues.

Now, let's take a look at the code in Tag.prepopulate(). This method first checks whether there are 10 Tags already in the database. Then, if there are not enough, it will create enough Tags to bring the number up to 10. 

public static prepopulate (Connection conn)
    throws SQLException
{
    String statement = 
        "select count(*), max(tag_id) from loco_graffiti_tags";
    Statement stmt = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;

    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery (statement);
    
        if (! rs.next())
        {
            throw new SQLException ("count(*) loco_graffiti_tags failed!");
        }
    
        int count = rs.getInt(1);
        int max_tag_id = rs.getInt(2);
        rs.close();
        stmt.close();
    
        if (count < maxRows)
        {
            statement = null;
            switch (config.DB_TYPE)
            {
            case config.ORACLE:
                statement = "insert into loco_graffiti_tags " +
                    " (tag_id, tag_author, tag_text, ins_time) " +
                    " values (loco_tag_id_sq.nextval, " +
                    " 'anon', 'This is an example tag', ?)";
                break;
            case config.INFORMIX:
                statement = "insert into loco_graffiti_tags " +
                    " (tag_id, tag_author, tag_text, ins_time) " +
                    " values (0, " +
                    " 'anon', 'This is an example tag', ?)";
                break;
            }
    
            pstmt = conn.prepareStatement(statement);

            Timestamp t = new Timestamp(System.currentTimeMillis());
    
            for (int i=0; i < maxRows - count; i++)
            {
                pstmt.setTimestamp(1, t);
                pstmt.executeUpdate();
            }
        }
    }
    finally {
        if (rs != null)  rs.close();
        if (stmt != null)  stmt.close();
        if (pstmt != null)  pstmt.close();
    }
}
In this code, we create enough new rows of Tags in the database to bring the total number of Tags to 10. For each new row, everything is the same; the only variation is a different tag_id. We set the value of the "tag_id" column to either "loco_tag_id_sq.nextval" (which retrieves the next value from a sequence) for Oracle, or the number "0" for Informix (Informix automatically returns an incremented ID when you specify a value of "0" for a Primary Key).

The PreparedStatement Class: Optimizing Repeated Queries

In the previous example, we also introduced the use of a class called PreparedStatement. This class is slightly different in operation from the Statement class we saw earlier. A PreparedStatement accomplishes exactly the same thing as a Statement, except that the query string itself may be a little bit different; and, under certain circumstances, a PreparedStatement may be more efficient. First, let's explain the differences in the query string. The query string for a PreparedStatement may contain one or more question marks, '?', which act as placeholders for real values. Before you execute a PreparedStatement, you must fill in values for each of these '?', by calling a method of the form "set...(column_number, value)". In the previous example, we called pstmt.setTimestamp (1, t), where 1 means we're setting the value for the first '?' placeholder in the statement, and "t" is a java.SQL.Timestamp we created to represent the current date and time as of this very moment.

Notice that we can execute a PreparedStatement multiple times. Each time we execute it, we are setting the value for the '?' placeholder to "t". This is another major difference between a Statement and a PreparedStatement. For a Statement, if you want to change the value of one of the fields in the query and then re-execute the query, you need to re-create the entire SQL statement string; for a PreparedStatement, you only need to use a set...() method to set the value for that column, and then you can re-execute that query.

You might remember from a previous discussion that there is another way to specify the current date and time. You could simply substitute the '?' with the word SYSDATE for Oracle or CURRENT for Informix. In fact, we recommend that you use this approach, because it's faster for the database to set its own timestamps, and slower for us to create a value in Java and pass that value to the database. The reason why we used setTimestamp() in this example was to introduce the concept of setting values in PreparedStatements. On the other hand, whenever you need to deal with timestamps other than the current date and time, then you will definitely need to use PreparedStatements and setTimestamp().

But there is another reason why we use a PreparedStatement in this case. If you are executing multiple SQL statements, using a PreparedStatement may be more efficient for the database server, because it will parse the SQL statement and do the setup for it only once, and reuse the setup every time the PreparedStatement is executed again. However, for Statements, the database server has to go through the same amount of work parsing the SQL statement every time any single Statement is executed.

For your reference, here's a summary of situations where you should consider using PreparedStatements instead of Statements:

  1. If you need to write timestamp values to the database, or use them in a query, use a PreparedStatement and setTimestamp(). By the way, do NOT use java.SQL.Date or even java.util.Date classes to represent any dates to be read or written to the database. They are not JDBC compatible!
  2. If any of your values for columns are floating point numbers, you must use PreparedStatements and setFloat(). In Java, the String.valueOf(float) method and the '+' String concatenation operator print out floats in a format that is incompatible with the way the database can read in floats.
  3. If a String value contains many characters that the database might interpret in a special way, for example, '&' or '\' or the single-quote character itself, ', consider using a PreparedStatement and setString(), which automatically "escapes" special characters.
  4. If you need to execute many SQL statements which are exactly the same, except for the values of some fields, you should consider using PreparedStatement for efficiency. Even if your statement doesn't have any fields, a PreparedStatement is more efficient.
SUMMARY.

In this example, we looked at all the different aspects involved in writing Java code to interact with the database. First, we looked at the SQL statement to create the database table; then, we looked at the JDBC method calls that loaded and saved Tags to the database. We also discussed how you might customize your code based on whether your database was Oracle or Informix. Then, we talked about the differences between "update" statements, "insert" statements, and "delete" statements. Finally, we introduced PreparedStatements and compared them to regular Statements.