How to pass a ResultSet to BIRT report

I tried to pass a JDBC ResultSet (java.sql.ResultSet) and could not find a straight forward way like passing the ResultSet as an argument to the report during generationd. However this is what I finally arrived at. This solution could be used to pass a collection of objects from a java class to the BIRT report.

Steps:-

1. In the java application where we are planning to generate the report using BIRT engine, execute the sql query and fetch the resultset. Add the resultset as a parameter to the report.

   ResultSet rs = getResultSet() // a function that executes the query and returns resultset

   HashMap params = new HashMap();

   params.put(myResultSet”, rs);

2. In the report, create a scripted data source. Create a scripted data set with the column names that you wish to use in the report. For e.g lets create a data set with one column “EMP_NAME”.

3. This step is to glue the columns in the resultset that came as parameter and the columns in the scripted data set. This can be achieved in two ways.

   a) Using javascript function

   1. In the scripted data set, implement the open(), fetch() methods. I'll explain in this another post.
      

   b) Using a java event handler that extends ScriptedDataSetEventAdapter class (org.eclipse.birt.report.engine.api.script.eventadapter.ScriptedDataSetEventAdapter) and implement the fetch(), open() and beforeOpen() methods. Lets handle one by one.

   1. Create a class called TestHandler (test.TestHandler)
      

   2. Declare a member variable for the ResultSet

      ResultSet resultSet;

   3. Retrieve the parameter myResultSet set from the java class in Step 1 and store it in the member variable.

      beforeOpen(IDataSetInstance dataSet, IReportContext reportContext) {

      super.beforeOpen(dataSet, reportContext);

      //retrieve the parameter myResultSet set from the java class in Step 1

      resultSet = (ResultSet)reportContext.getParameterValue(“myResultSet”);

      }

   4. Position to resultset to the first row in the open()

      public void open(IDataSetInstance dataSet) {

      super.open(dataSet);

      if(resultSet != null)

      resultSet.next();

      }

   5. Map the column of the resultset and the column of the scripted data set.

      public boolean fetch(IDataSetInstance datSet, IUPdatableDataSetRow row) {

      if(resultSet.isAfterLast())

      return false;// to indicate that there are no more rows left to process

      row.setColumnValue(“EMP_NAME”, resultSet.getString(1));

      resultSet.next();

      return true; // to indicate that more rows to follow

      }

4. Do the cleanup activities. Close the resultset, statement and release the connection.

5. Go to the report designer. Go to Outline window. Select the scripted data set created in Step 2. Go to the Property Editor and select "Event Handler" tab. In the Event Handler Class, add the fully qualified class name created in step 3 (test.TestHandler).
   

6. All set. Run the java program to initiate the engine and invoke the report and see the results.