JasperReports

iReports

After installing add the JDBC DataSource jar in iReports Classpath through Tools->Options-Classpath (be sure to add the jar, not the folder); restart iReports
Create a DataSource (no menu option available, just click the plug-in socket icon); be sure you add the database name at the end of the URL
Now you can create a new report and modify the report query. You can define report default values for parameters as follows (please notice that parameters are strong typed so you must create an instance of the java class):

• String: enclose the desired value in double quotes
• Date:

1. new Date()
2. new Date(113,0,1)

The second expression will initialize the default value to 1st of January 2013 (!):year+1900,month+1

Formatting dates:

• add an expression in the report
• change the expression text with something like this:
• "From "+(new SimpleDateFormat("yyyy/MM/dd")).format($P{DateStart})+" to "+(new SimpleDateFormat("yyyy/MM/dd")).format($P{DateEnd})

JSF and JasperReports

Add the required jars (found in jasper reports distribution in dist and lib sub-directories) as project references. Minimal (depending on used functionality):

• jasperreports
• all commons
• iText
• groovy-all

Deploy compiled report in resources/jsrep (for instance) project sub-directory.

In managed bean you can use the following code to show the report in PDF format (the sample code uses PrimeFaces file download component, see the showcase):

      InputStream stream = ((ServletContext)FacesContext.getCurrentInstance().getExternalContext().getContext()).getResourceAsStream("/resources/jsrep/Report.jasper"); 

       JasperReport report = (JasperReport) JRLoader.loadObject(stream);

       Map<String, Object> params = new HashMap<String, Object>();

       params.put("Param", 494);

       JasperPrint jasperPrint = JasperFillManager.fillReport(report, params, ds.getConnection());

       ByteArrayOutputStream baos=new ByteArrayOutputStream();

       JasperExportManager.exportReportToPdfStream(jasperPrint,baos );

       ByteArrayInputStream bais=new ByteArrayInputStream(baos.toByteArray());

       return new DefaultStreamedContent(bais, "application/pdf", "report.pdf"); 

The data source can be obtained through injection, like

@Resource(name="jdbc/MyDataSource")

DataSource ds;

In order to export to XLS add poi jar to solution and use the following code:

        InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext()).getResourceAsStream("/resources/jsrep/Report.jasper");
        JasperReport report = (JasperReport) JRLoader.loadObject(stream);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("Param", 123);
        JasperPrint jasperPrint = JasperFillManager.fillReport(report, params, ds.getConnection());
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        JRXlsExporter exporterXLS = new JRXlsExporter();
        exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
        exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos);
        exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
        exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
        exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
        exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
        exporterXLS.setParameter(JRXlsExporterParameter.PASSWORD, null);
        exporterXLS.exportReport();
        ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
        return new DefaultStreamedContent(bais, "application/vnd.ms-excel", "report.xls");

Creating reports that will be exported to XLS can be tricky: be sure that fields are top aligned pixel-perfect otherwise they will not be shown or shown in an unexpected manner in resulting XLS

Configure MySQL jdbc resource

1. Copy the MySQL jdbc driver (jar file) in Glassfish lib directory and restart Glassfish
2. In the glassfish administration console (site) go to Resources\JDBC\JDBC ConnectionPools
3. Create a new pool of type javax.sql.DataSource and choose MySQL as the database vendor
4. Fill in: server, user, password, database and, very important, check the URL to contain the database name (for some reason or under some circumstances it is not correctly filled in)
5. Try Ping, should be successfull
6. Now create the jdbc Resource, etc