Querying a database

The Java sample plugin is a little more advanced. It provides two channels that can be subscribed to but instead of generating dummy data, it queries a database and returns a feed for the rows it finds. Compile the Java files and copy the .class, .plugin and .mdb files to $/AppPlugins and restart Awasu. You should see a new entry in the Control Center's Plugins window.

One of the tables contains hypothetical data on a company's sales and you can see it in action by subscribing to this channel and then adding a row to the database. The next time the channel updates, you will receive a notification of the new sale!

The subscription URL's on the main page are created so that they have a database table name embedded in them. This is so that we can determine which table to query when Awasu calls us to generate a channel feed:

StringBuffer buf = new StringBuffer() ;
buf.append( "<A href='" ) ;
buf.append( appServerUrl + "/subscribe" ) ;
buf.append( "?url=" + appPluginServerUrl + "/" + APP_PLUGIN_ID + "/" + tableName ) ;
buf.append( "'>" ) ;
buf.append( tableName ) ;
buf.append( "</A>" ) ;

When Awasu calls the script to generate a feed, it passes us whatever information we may have embedded in the URL. In our case, this will be the table name which we query and generate a feed for accordingly:

// prepare the SQL query 
String sql ;
if ( maxRows < 0 )
    sql = "SELECT * FROM tblCustomerSales" ; 
else 
    sql = "SELECT TOP " + Integer.toString(maxRows) + " * FROM tblCustomerSales" ; 
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( sql ) ;

// generate the RSS feed 
StringBuffer rssBuf = new StringBuffer() ; 
rssBuf.append( "<RSS>\n" ) ;
rssBuf.append( "<CHANNEL>\n" ) ;
rssBuf.append( "<LINK>http://intranet/sales</LINK>\n" ) ;
rssBuf.append( "<TITLE>Latest Customer Sales</TITLE>\n" ) ;
while( rs.next() ) 
{
    rssBuf.append( "<ITEM>\n" ) ;
    // NOTE: This is a dummy URL that demonstrates how one might be generated for a given sales item.
    String url = "http://intranet/sales/" + rs.getString("ID") ; 
    rssBuf.append( "<LINK>" + url + "</LINK>\n" ) ; 
    String title = rs.getString("CustomerName") + " ($" + rs.getInt("Amount") + ")" ;
    rssBuf.append( "<TITLE>" + title + "</TITLE>\n" ) ;
    StringBuffer buf = new StringBuffer() ;
    buf.append( rs.getDate("DateOfSale").toString() + ": " ) ;
    buf.append( rs.getString("ItemCount") + " x " + rs.getString("ItemName") ) ;
    String comments = rs.getString("Comments") ; 
    if ( comments != null )
        buf.append( "<br><i>" + comments + "</i>" ) ; 
    rssBuf.append( "<DESCRIPTION><![CDATA[" + buf.toString() + "]]></DESCRIPTION>\n" ) ; 
    rssBuf.append( "</ITEM>\n" ) ;
}
rssBuf.append( "</CHANNEL>\n" ) ;
rssBuf.append( "</RSS>\n" ) ;
System.out.println( rssBuf.toString() ) ;