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!

Create Java Scriptlets for Jasper Reports (part 1)

This post is aimed at report developers who use iReport and/or Jasperserver and would like to create Java scriptlets to enhance report functionality.  Part 1 will discuss the basics of creating the Java code and .jar file.  Part 2 will discuss the specifics of creating a scriptlet that will write back to the database.

Step 1: Learn Java

If you already know Java, great!  If you're familiar with programming, something like this tutorial will probably be enough to get you started.

Step 2: Code

There are two different methods (not mutually exclusive) for creating scriptlets.  One is to piggyback on the default scriptlet class provided by Jaspersoft.  The methods within this class correspond to events within report processing, and they will run automatically with the report you attach the scriptlet to.

The second strategy is to create your own methods using any existing Java functionality (part 2 covers java.sql.*).  You have to call these specifically from the report if you want them to run.

Either way you go, you need to do the following:

• Create your .java code file.  The file name must match your class name (so FileName.java must contain public class FileName ...)
• Include the external classes you need in your file.  For example:

import net.sf.jasperreports.engine.JRDefaultScriptlet;
import net.sf.jasperreports.engine.JRScriptletException;

• Create your program.  

Step 3: Compile

Jasperserver comes with a compiler (located in <Jasper Home>\java\bin), so it's easiest just to use that.  It's even easier if you add this to your system path variable so you can call javac from anywhere.  If you want to use the standard JDK, that is fine, but there will be problems if you try to run the report on a server with a prior version of java from what you developed in.

To use Jasperserver-specific classes (as in the example above) you need to include the Jasper Reports library when you run javac.  For example:

javac -classpath "C:\Program Files\jasperserver-ce-3.7.1\apache-tomcat\webapps\jasperserver\WEB-INF\lib\jasperreports-3.7.1.1.jar" FileName.java

Step 4: Create the JAR

Once you have compiled your code successfully, you can create the jar file with a simple command:

jar cvf JarFileName.jar FileName.java FileName.class

Step 5: Add Scriptlet to Report

First, add your jar file to the iReport classpath (Tools>Options>Classpath>Add JAR).

Once you've done this, just go to your (iReport) report inspector, right-click 'Scriptlets' and 'Add Scriptlet'.  If you are only extending the default jasper scriptlet class, that's all you have to do - just compile your report and run it.

If you want to call your own method(s) within the report, you'll need to change the scriptlet class in your report properties to match your scriptlet rather than the default.  You can then call the method from a text field using something like this:

$P{REPORT_SCRIPTLET}.YourMethod()

Once you are ready to run this from Jasperserver, you'll need to upload the jar file as a resource when you create the report.

That's it!  In part 2, we'll look at a specific scriptlet that uses all of this to write back to the database based on information contained within the report.