This post demonstrates how to build a widget that queries data from a relational database and returns the results as a static listing on a page. This is useful for content coming from relational datasources that does not need to be dynamically served by an application.
Step 1. Set up the Widget
Create a new Widget Builder widget named ExampleDatabaseList.
Step 2. Add Widget Field
For this Widget we’ll add a basic text field call maxresults to limit the results returned that we will use in a subsequent blog post.
Step 3. Setup the Display Template
The Widget’s Display Template is where we will execute the necessary Velocity code to run the database query and return the results.
The template source is included in text form here for ease of copy and paste:
#set($rows = $rx.db.get("RhythmyxData","SELECT TEMPLATE_ID, LABEL FROM PSX_TEMPLATE"))##
<h2>Available Templates</h2>
<p>
This widget is a simple example of querying a database from within a Widget Builder Display Template and publishing that content statically.
</p>
<table>
<tr>
<th>Template Id</th>
<th>Template Name</th>
#foreach($row in $rows)
<tr>
<td>$row.TEMPLATE_ID</td>
<td> $row.LABEL</td>
</tr>
#end
</table>
Step 4. Test The Widget
Deploy the Widget and then edit a Template, and add the Widget to the layout tab.
In order for the Widget to appear populated on a page you must edit the Content for the widget and set a value for Maximum Results. For now use -1 to specify no limit.
Once the Content is set. Preview a Page with the template. You should see a table that lists all of the available Templates on your system. While this isn’t a very practical example for most public web sites, it is a simple demonstration of using SQL within your Widget Builder Widgets to publish relational data.
How it Works
The $rx.db.get method takes 2 parameters. The first is JNDI datasource configured in the server’s rx-ds.xml file, located in /AppServer/server/rx/deploy/rx-ds.xml by default. The default name for the CM1 content repository data source is “RhythmyxData”.
The second parameter is the SQL SELECT statement to be passed to the relational database. When the template is assembled at Preview or Publish time, the query is executed and the results are returned to the Display Template. The Display Template then loops through the results and outputs the resulting data in tabular form.