Templates and Database Access

Templates and Database Access
Prev		Next

Dot-variables ending in .sql create DBCacheBean objects in the MRU cache. In English, this means the results of the SQL queries specified by these dot-variables will be periodically re-fetched so long as the web traffic asks for it; when the web traffic stops, SportPage will stop refreshing and remove the cached result set, rebuilding it the next time the query is requested.

To access any data table, you must first define SQL statements describing each unique table; this can be done using SQL “views” to distill complicated queries down to simple SELECT statements. These statements are bound to special dot-variables in the sportpage.conf which also means that you can have the same variable name being bound to different select statements in different topic areas.

The general form of the dot-variable definition is

topic.varname.sql=SELECT cols FROM table WHERE constraints

SportPage will detect the .sql and create a DBCacheBean for this request; the bean will periodically refresh the query every dbcache.period seconds, expiring the query completely if it is not accessed again within a preset time (set with mru.timeout).

For example

sports.hockey.standings.sql=SELECT * FROM hockey_standings

will define the variable $standings in any template within the topic /sports/hockey.

The $standings object has two access methods: $standings.columns and $standings.results; the first is a list of column names, the second a list of value lists.

Example 2. Rendering DBCacheBean

A generic template to render all SQL result objects would go something like this:

    <table>
    <tr>
        #foreach ($col in $standings.columns)
        <th>$col</th>
        #end
    </tr>
    #foreach ($row in $standings.results)
    <tr>
        #foreach ($val in $row)
        <td>$val</td>
        #end
    </tr>
    #end
    </table>

If you need to re-arrange or rename columns, it's best to do this in the SQL (so the database does all the work) but you can do a bit of processing on the columns or the results list; these are returned as Java ArrayLists, which provides methods to access specific items by number, so you could hunt for the column number of a particular column and then sift the results for that column number. It's much easier and less processing to simply define a new SQL object, but the downside is that the servlet container must be restarted to load any changes in the dot-variable definitions.

Prev	Up	Next
Defining PageHandlers	Home	Template XML Handling