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!