How to pass the db connection information and query parameters from controller to JasperReportsMultiFormatView

I am prototyping a web application using Spring MVC 3.0 with JasperReports. I have already done reporting applications using Spring + Jfreechart + iText + Apache POI and been able to use successfully the respective view classes provided by Spring to stream pdfs, xls and images.

This time I want to try to use JasperReports so that I can design my pdfs outside of the application and not have to worry about knowing the underlying api (be that jfreechart, itext, or poi).

Problem

I have a report1.jrxml file that contains a queryString tag with my query with two date parameters. When I test the report through iReport, it compiles and runs successfully. No problems here.

Now I am reading the JasperReports section from the following Spring documentation http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/htmlsingle/spring-framework-reference.html#view-jasper-reports and I am trying to get the JasperReportsMultiFormatView to work properly, but there is one piece that I am not understanding:

1. How does JasperReportMultiFormatView know the database to connect to (Recall that I have the query embedded in the report itself) ?

2. The documentation states to use the reportDataKey property in the view, but I do not see how this is the solution to my problem.

3. How do you pass parameters?

What can be done

JaperReports provides with a set of xxxManager objects that are responsible for compiling, fiiling, and exporting the report. You could create a custom class that implements the Spring View interface and do something like this:

Connection connection;
ServletOutputStream servletOutputStream = response .getOutputStream();
InputStream reportStream = getServlet().getServletConfig().getServletContext().getResourceAsStream("/reports/report1.jasper");
response.setContentType("application/pdf");
Class.forName("com.mysql.jdbc.Driver");
 connection = DriverManager.getConnection("jdbc:mysql://localhost: 
             3306/flightstats?user=user&password=secret");
JasperRunManager.runReportToPdfStream(reportStream,  
                 servletOutputStream, new HashMap(), connection);
connection.close();
servletOutputStream.flush();
servletOutputStream.close();

What I need

I need to accomplish what the code above those leveraging the Spring classes such as JasperReportsPdfView, JasperReportsXlsView, or even better JasperReportsMultiFormatView

So in summary I need to be able to pass the following from my controller to the jasper report:

1. Parameters
2. Db connection information so that the queryString inside the jasper knows who to run against

This is what I have and the output is a blank PDF document, I am assuming because it does not know how to run the query

@RequestMapping("/reports/**")
@Controller

public class ReportsController {

@RequestMapping(value ="/reports/usage/report", method = RequestMethod.GET)
public ModelAndView handleSimpleReportMulti(HttpServletRequest reques开发者_Go百科t, HttpServletResponse response) throws Exception {

    System.out.println("Made it here");

    Map model = new HashMap();
    //model.put("format", "pdf");
    model.put("START_DATE", new String("09-12-2011"));
    model.put("END_DATE", new String("09-17-2011"));

    return new ModelAndView("report1", model);
}
}

I found the answer to my question. I have changed my controller above to this:

@RequestMapping(value ="/reports/usage/report/{format}", method = RequestMethod.GET)
public ModelAndView handleSimpleReportMulti(ModelMap modelMap, @PathVariable("format") String format) throws Exception {

    //Map model = new HashMap();
    modelMap.put("format", format);
    modelMap.put("REPORT_CONNECTION", dataSource.getConnection());
    modelMap.put("START_DATE", new String("09-12-2011"));
    modelMap.put("END_DATE", new String("09-17-2011"));

    return new ModelAndView("report1", modelMap);       
}

I have changed my view.properties to this:

#report1(class)=org.springframework.web.servlet.view.jasperreports.JasperReportsPdfView
report1(class)=org.springframework.web.servlet.view.jasperreports.JasperReportsMultiFormatView
report1.url=/WEB-INF/reports/report1.jasper

I hope this helps.

Thanks

I found another way to pass the connection as a parameter, and then close it.

THE PROBLEM:
I implement the solution above an the problem was, that everytime I call a PDF, a new connection was created, so when the app gets to the max limit of open connections it crash.

    ReportesDAOJDBC reportes;

    public void setReportes(ReportesDAOJDBC reportes) {
            this.reportes = reportes;
    }

    public ModelAndView leoTest(HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    Map < String, Object > model = new HashMap < String, Object >();
    model.put("PARAMCONTRARECIBO", new Integer(1101));
    model.put("PARAMDOCTOS", new Integer(1101));
    model.put("REPORT_CONNECTION", reportes.getConexion());

    return new ModelAndView("leoTest",model);
    }

The parameter to pass a connection to a JasperReport is REPORT_CONNECTION, but as I said, doing this way, will cause a lot of trobubles.

MY SOLUTION:

    ReportesDAOJDBC reportes;

    public void setReportes(ReportesDAOJDBC reportes) {
            this.reportes = reportes;
    }

    public ModelAndView leoTest(HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    Map < String, Object > model = new HashMap < String, Object >();
    model.put("PARAMCONTRARECIBO", new Integer(1101));
    model.put("PARAMDOCTOS", new Integer(1101));
    model.put("OBJETO_CONEXION", reportes);

    return new ModelAndView(new PdfView("leoTest"),model);
    }

As you can see, I implement my own PdfView and I pass in the constructor the name of the key define in the view.properties file, and also I pass a reference to my DAO (reportes) as a parameter of the HashMap, the name of the parameter is "OBJETO_CONEXION". Here is the code for ReportesDAOJDBC:

    public class ReportesDAOJDBC extends JdbcDaoSupport {

      public Connection getConexion() {
         Connection  con ;
         try {
                con = getDataSource().getConnection();
         }
         catch (Exception e) {
           e.printStackTrace();
           return null;
         }
         return con;
      }

      public void closeConecction(Connection con) {
         try {
           if (con != null) {
              con.close();
           }
         }
         catch (Exception e) {
            e.printStackTrace();
         }
       }
      }

Then the next step is to show you the code of my own PdfView implementation.

    import java.io.File;
    import java.io.OutputStream;
    import java.sql.Connection;
    import java.util.Map;
    import java.util.ResourceBundle;

    import javax.servlet.ServletContext;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;

    import mx.com.mexican.leinksy.dao.jdbc.ReportesDAOJDBC;
    import mx.com.mexican.leinksy.utils.Utils;
    import net.sf.jasperreports.engine.JasperExportManager;
    import net.sf.jasperreports.engine.JasperFillManager;
    import net.sf.jasperreports.engine.JasperPrint;
    import net.sf.jasperreports.engine.JasperReport;
    import net.sf.jasperreports.engine.util.JRLoader;

    import org.springframework.web.servlet.View;
    import org.springframework.web.servlet.view.ResourceBundleViewResolver;

    public class PdfView implements View {

        private static final String CONTENT_TYPE = "application/pdf";
        private String JASPER_URL;


        public PdfView(String jasperUrl){
            this.JASPER_URL = jasperUrl+".url";
        }

        @Override
        public String getContentType() {
            return CONTENT_TYPE;
        }

        @Override
        public void render(Map model, HttpServletRequest request,
                HttpServletResponse response) throws Exception {

            System.out.println(Utils.getRealPath(request));


            ResourceBundle rb       = ResourceBundle.getBundle("view");/* Se lee el archivo view.properties*/
            ReportesDAOJDBC reporte = (ReportesDAOJDBC)model.get("OBJETO_CONEXION");/* Se obtiene el objeto de conexion */
            Connection con          = reporte.getConexion();/* Se genera la conexion a la base de datos*/


            String jasperFilePath     = Utils.getRealPath(request) + rb.getString( JASPER_URL );/* Se obtiene la ruta fisica del archivo .jasper a ejectuar*/
            JasperReport jasperReport = (JasperReport)JRLoader.loadObject(new File(jasperFilePath));/* Se carga el reporte ya compilado*/
            JasperPrint  jasperPrint  = JasperFillManager.fillReport(jasperReport, model, con);/* Se llena el reporte con datos del modelo y con la conexion a la BD*/


            try{

                OutputStream out = response.getOutputStream();

                JasperExportManager.exportReportToPdfStream(jasperPrint, out);/* Se manda el contenido a la salida estandar*/

                out.flush(); 
                out.close();

            }catch(Exception e){
                e.printStackTrace();
            }

            reporte.closeConecction(con);/* Cierro la conexion a la base de datos para liberar el pool*/

        }

    }

As you can see, I read the view.properties file using the ResourceBoundle class to get the path and name of the .jasper file to load, Also notice that I dont compile the .jrxml file I just load the compiled file, I compile the jrxml with IREPORTS. Also I hava a utility to get the path of my .jasper file, here is the code if you dont have idea of how to do it.

public static String getRealPath(HttpServletRequest req) {
    ServletContext context = req.getSession().getServletContext();
    String path = context.getRealPath("/");

    if (path != null) {
        if (!path.endsWith(File.separator)) {
            path += File.separator;
        }
    }
    return  path;
}

With this implementation I can control where to open an close connection, also I respect the MVC model of SPRING, and also I still using the view.properties.

And at this point, maybe you are asking WHAT CAN I DO IF I WANT AN EXCEL FILE, well, I also implement an XlsView, (Ajuuaaaa !!! ). Here is the code:

   import java.io.File;
   import java.io.OutputStream;
   import java.sql.Connection;
   import java.util.Map;
   import java.util.ResourceBundle;

   import javax.servlet.ServletContext;
   import javax.servlet.http.HttpServletRequest;
   import javax.servlet.http.HttpServletResponse;

   import mx.com.mexican.leinsky.dao.jdbc.ReportesDAOJDBC;
   import mx.com.mexican.leinksy.utils.Utils;
   import net.sf.jasperreports.engine.JasperExportManager;
   import net.sf.jasperreports.engine.JasperFillManager;
   import net.sf.jasperreports.engine.JasperPrint;
   import net.sf.jasperreports.engine.JasperReport;
   import net.sf.jasperreports.engine.export.JRXlsExporter;
   import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
   import net.sf.jasperreports.engine.util.JRLoader;

   import org.springframework.web.servlet.View;
   import org.springframework.web.servlet.view.ResourceBundleViewResolver;

   public class XlsView implements View {

    private static final String CONTENT_TYPE = "application/vnd.ms-excel";
    private String JASPER_URL;
    private String FILE_NAME = "XLSFile";


    public XlsView(String jasperUrl){
        this.JASPER_URL = jasperUrl+".url";
    }

    @Override
    public String getContentType() {
        return CONTENT_TYPE;
    }

    @Override
    public void render(Map model, HttpServletRequest request,
            HttpServletResponse response) throws Exception {

        if(model.get("FILE_NAME")!=null){
            this.FILE_NAME = model.get("FILE_NAME").toString();
        }


        ResourceBundle rb       = ResourceBundle.getBundle("view");/* Se lee el archivo view.properties*/
        ReportesDAOJDBC reporte = (ReportesDAOJDBC)model.get("OBJETO_CONEXION");/* Se obtiene el objeto de conexion */
        Connection con          = reporte.getConexion();/* Se genera la conexion a la base de datos*/


        String jasperFilePath     = Utils.getRealPath(request) + rb.getString( JASPER_URL );/* Se obtiene la ruta fisica del archivo .jasper a ejectuar*/
        JasperReport jasperReport = (JasperReport)JRLoader.loadObject(new File(jasperFilePath));/* Se carga el reporte ya compilado*/
        JasperPrint  jasperPrint  = JasperFillManager.fillReport(jasperReport, model, con);/* Se llena el reporte con datos del modelo y con la conexion a la BD*/

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition","attachment; filename=\""+FILE_NAME+".xls\"");
        response.setHeader("Pragma", "No-cache");
        response.setDateHeader("Expires", 1);

        try{

            OutputStream out = response.getOutputStream();

        JRXlsExporter exporterXLS = new JRXlsExporter();
        exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT,jasperPrint);
        exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM,out);
        exporterXLS.exportReport();

        out.flush(); 
        out.close();

    }catch(Exception e){
        e.printStackTrace();
    }

    reporte.closeConecction(con);/* Cierro la conexion a la base de datos para liberar el pool*/

}

}

So this is my solution, hope It helps !!!