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 the AppPlugins/ directory and restart Awasu. You should see a new entry in the Plugins window.

How the sample plugin works

One of the tables contains hypothetical data for a company's sales and you can see the plugin 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 feed:

StringBuffer buf = new StringBuffer() ;
buf.append( "<a href='" ) ;
buf.append( appServerUrl + "/channels/subscribe" ) ;
buf.append( "?token=" + apiToken ) ;
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() ) ;