Thursday, June 16, 2011

Create Java Scriptlets for Jasper Reports (part 2)

In part 1, we addressed the basics of creating Java scriptlets.  Now, we'll look at a specific example that updates a database based on report content.  In this example, we're using the underlying Jasperserver database and writing back to it.

Let's look at the Java code first:


import java.sql.*;
import net.sf.jasperreports.engine.JRDefaultScriptlet;
import net.sf.jasperreports.engine.JRScriptletException;

We're combining both the default Jasper scriptlet method and java.sql.* for the database update.  

public class UpdateQrtzTriggers extends JRDefaultScriptlet {}

This is the overall class.  

There are two objects within the overall class:

public void afterDetailEval() throws JRScriptletException

and

public String updateDB(Connection con, String trigger_names) throws SQLException

For the database update, we pass in the connection and a list of triggers to include in the update.  

After processing every report row (afterDetailEval), we collect the value of a field within the report and append it to a master list:

String trigger_name_list = (String)this.getVariableValue("TriggerNames");
String trigger_name_field = (String)this.getFieldValue("trigger_name");

if (trigger_name_list != null) //if trigger name list already contains values, treat as append
{
    if (trigger_name_list.indexOf(trigger_name_field) == -1) //only append value if trigger name is not already in list
    {
        this.setVariableValue("TriggerNames", trigger_name_list + ",'" + trigger_name_field +"'");
    }            
}
else 
{
    this.setVariableValue("TriggerNames", "'" + trigger_name_field +"'");
}

In the summary section of the report, we call the database update specifically as follows:

$P{REPORT_SCRIPTLET}.updateDB($P{REPORT_CONNECTION},$V{TriggerNames})

This gives updateDB its parameters.  It then does this with them:

try
{
    if (con.isReadOnly() == true) //make sure update can proceed
    {
        con.setReadOnly(false);
    }
    String sql_1 = "UPDATE qrtz_triggers q SET next_fire_time = next_fire_time-(next_fire_time-(UNIX_TIMESTAMP()*1000))+(DATE_FORMAT(FROM_UNIXTIME(next_fire_time/1000),'%i')*60000)+2000 WHERE q.trigger_name IN ("; 
    String sql_final = sql_1 + trigger_names + ");";  //include list of triggers to update and closing parenthesis   
    Statement s = con.createStatement();
    s.executeUpdate(sql_final);
    if (con.getAutoCommit() == false) //if autocommit is not set, commit manually
    {
        con.commit();
    }
    return "Update Succeeded";
}
catch (SQLException e)
{
    return e.toString();
}       

This sets the trigger for a scheduled report to a time in the immediate future - thus re-running a scheduled report whose execution time has already passed.  

Have fun!
Post a Comment