How to transfer db connection information and request parameters from the controller to JasperReportsMultiFormatView

Question

I am prototyping a web application using Spring MVC 3.0 with JasperReports. I already made reporting applications using Spring + Jfreechart + iText + Apache POI and was able to successfully use the corresponding class classes provided by Spring to stream pdf files, xls and images.

This time I want to try using JasperReports so that I can create my pdf files outside the application and not have to worry about knowing the basic api (be it jfreechart, itext or poi).

Problem

I have a report1.jrxml file containing a queryString tag with my query with two date parameters. When I test a report through iReport, it compiles and runs successfully. There is no problem.

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 I am trying to get JasperReportsMultiFormatView to work correctly, but there is one part that I do not understand:

• How does JasperReportMultiFormatView know the database to connect to (remember that I have a query built into the report itself)?

• The documentation states the use of the reportDataKey property in the view, but I don't see how this is the solution to my problem.

• How do you pass the parameters?

What can be done

JaperReports provides a set of xxxManager objects that are responsible for compiling, processing, and exporting the report. You can create a custom class that implements the Spring View interface and does 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 execute the code above those using Spring classes like JasperReportsPdfView, JasperReportsXlsView or even better JasperReportsMultiFormatView

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

• Parameters
• Db connection info so queryString inside jasper knows who will work against

This is what I have, and the output is an empty PDF document, I guess, because it does not know how to execute the request

 @RequestMapping("/reports/**") @Controller public class ReportsController { @RequestMapping(value ="/reports/usage/report", method = RequestMethod.GET) public ModelAndView handleSimpleReportMulti(HttpServletRequest request, 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); } } 

Answer 1

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

 @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 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 

Hope this helps.

thanks

Answer 2

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

PROBLEM:
I am implementing a solution to the above problem, since every time I call the PDF file, a new connection was created, so when the application reaches the maximum limit of open connections, it crashes.

  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 for transferring a connection to JasperReport is REPORT_CONNECTION, but, as I said, by doing this, you will get many trolleybuses.

MY DECISION:

  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 the constructor the name of the define key in the view.properties file, and also pass the link to my DAOs (reports) as a HashMap parameter, the parameter name 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 for my own implementation of PdfView.

  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 note that I am not compiling a .jrxml file. I just upload the compiled file, I compile jrxml using IREPORTS. I also have a utility to get the path to my .jasper file, here is the code if you have no idea how to do this.

 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 a closed connection, I also respect the MVC SPRING model, and I still use view.properties.

And at the moment, maybe you are asking WHAT CAN DO IF I WANT THE EXCEL FILE, well, I also implement 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 this helps !!!