Announcement Announcement Module
Collapse
No announcement yet.
JDBC Connection pool issue Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC Connection pool issue

    Hi,

    I am integrate Jasper report in Spring frame work, everything works perfectly fine, except that after awhile of testing the app I am running out of the connection. I have WebLogic server manages connection pool and is currently by default set to 15.

    According to Spring framework and Jasper, I don't need to close the connection or statement.

    Does anyone run into this issue? and is there a way to fix this?

    Thanks in advance,
    Bob

  • #2
    Hi

    Of course you don't have to close connections unless you're leaking them

    You don't have to call Connection.close() if you're using JdbcTemplate or Spring AOP's TransactionInterceptor.

    When you're passing Connection objects to Jasper, you have to close them.

    org.springframework.jdbc.core.JdbcTemplate.execute (StatementCallback<T>) has the following flow:
    • Connection con = DataSourceUtils.getConnection(getDataSource());
    • create statement
    • use statement
    • JdbcUtils.closeStatement(stmt);
    • DataSourceUtils.releaseConnection(con, getDataSource());
    so the connections are properly closed.

    Check your code for connection close invocations.

    regards
    Grzegorz Grzybek

    Comment


    • #3
      Hi Grzegorz,

      Thanks for you response, I am a little bit confusing with JdbcTemplate, so if this is what I had:

      Connection conn;
      Statement stmt;
      ResultSet rs;

      conn = dataSource.getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery("select a b c from d");

      JRResultSetDataSource rsds = new JRResultSetDataSource (rs);

      ------------------
      Can I just simply using JdbcTemplate as:

      JdbcTemplate jt = new JdbcTemplate(getDataSource());
      rs = jt.execute("select a b c from d");

      THEN SHOULD i PASS JRResultSetDataSource OBJ TO JASPER REPORT OR JRBeanCollectionDataSource OR SOMETHING ELSE...

      Thanks again for your help.
      Bob

      Comment


      • #4
        When you use JRResultSetDataSource with ResultSet passed as an argument to its constructor, Jasper Reports is not able to close your statement and connection.

        So my info about Spring classes is (in your particular case) not relevant.

        You just have to be sure to call
        • rs.close()
        • stmt.close()
        • conn.close()

        after invoking Jasper report generation.

        You should consider using JRBeanCollectionDataSource instead of JRResultSetDataSource - it's almost always better to separate data retrieval logic from report generation.

        regards
        Grzegorz Grzybek

        Comment


        • #5
          Hi Grzegorz,

          Thanks for your response, I think I am OK now I see that I have the options and those are the area I am familiar with.

          But I have another question about Spring View technologies area, hope you can help. I have multiple reports and each has it own template. My question is should I create one controller for every report template OR it's better to have just one controller to pass the results set to the templates conditionally?

          On the views.properties file can I specified multiple entries such as:
          simple1.class=org.springframework.web.servlet.view .jasperreports.JasperReportsPdfView
          simple1.url=/WEB-INF/reports/Report1.jasper

          simpleReport2.class=org.springframework.web.servle t.view.jasperreports.JasperReportsPdfView
          simpleReport2.url=/WEB-INF/reports/Report2.jasper

          simpleReport3.class=org.springframework.web.servle t.view.jasperreports.JasperReportsPdfView
          simpleReport3.url=/WEB-INF/reports/Report3.jasper

          ETC...

          Thanks in advance
          Bob

          Comment


          • #6
            Hi

            I would create only one ReportsController. It's responsibility would be:
            • get a request parameter with the name/id of report
            • invoke busines layer service e.g. reportContentGenerator.generateReport(id) which returns List
            • return ModelAndView with view name corresponding to report and model returned from the service

            It's clear responsibility. Of course somewhere there has to be some conditional logic, but it's better if the ifs are in lower layers

            The service method could possibly have some map with report ids as keys and dao implementations as values - it could choose concrete DAO by report ID and the DAO would be responsible for generating report content (list) by some SQL or HQL.

            I've found that in web applications it's good to replace conditional logic with some properly configured (in Spring's XML) Maps.

            regards
            Grzegorz Grzybek

            Comment


            • #7
              Hi Grzegorz,

              Thanks again for your help. Could you put it into code example? I am still a little confuse.

              Thanks,
              Bob

              Comment


              • #8
                Hi

                Sorry for the delay...

                1. Web Layer

                In Spring's XML you put:

                Code:
                <context:component-scan base-package="com.abc.web.controllers" />
                In com.abc.web.controllers package you create class with Spring's @Controller annotation:

                Code:
                public class ReportsController {
                   @Autowired
                   private ReportService reportService;
                
                   @RequestMapping("/generateReport")
                   public ModelAndView generateReport(@RequestParam("report-id") long reportId) {
                      // this code is invoked with URL: http://host:port/contextPath/generateReport?report-id=X
                      List<?> report = this.reportService.generateReport(reportId);
                
                      return new ModelAndView(someReportViewName).addObject("report", report);
                   }
                }
                You also have to configure view resolving, so Spring would know what view to render (e.g. Jasper).

                2. Service and Data Layer

                You have to configure some ReportService implementation which will choose some DAO based on ID (or other criteria). The DAO will then choose proper SQL/HQL/... to generate report content.

                I hope this gives you some light on the implementation

                regards
                Grzegorz Grzybek

                Comment


                • #9
                  Hi Grzegorz,

                  What I am confuse is how to configure view resolver to handle multiple jasper templates.

                  Say, if I have this myapp-servlet.xml, and there is a view resolver bean using ResourceBundleViewResolver as

                  <bean id="viewResolver" class="org.springframework.web.servlet.view.Resour ceBundleViewResolver">
                  <property name="basename" value="views"/>
                  </bean>

                  Then I must have views.proprties file. Can I have things like these in this file to tell jasper which template to render...etc:

                  simpleReport1.class=org.springframework.web.servle t.view.jasperreports.JasperReportsPdfView
                  simpleReport1.url=/WEB-INF/reports/DataSourceReport1.jasper

                  simpleReport2.class=org.springframework.web.servle t.view.jasperreports.JasperReportsPdfView
                  simpleReport2.url=/WEB-INF/reports/DataSourceReport2.jasper

                  simpleReport3.class=org.springframework.web.servle t.view.jasperreports.JasperReportsPdfView
                  simpleReport3.url=/WEB-INF/reports/DataSourceReport3.jasper

                  Thanks again for your reply.
                  Bob

                  Comment


                  • #10
                    Hi

                    You could use JasperReportsViewResolver which changes viewName into an instance of configured view (e.g. org.springframework.web.servlet.view.jasperreports .JasperReportsPdfView)

                    In your case, the JasperReportsViewResolver will have the following properties:
                    • viewClass = JasperReportsPdfView
                    • prefix = /WEB-INF/reports/
                    • suffix = .jasper

                    Also the controller must return viewNames in the form of "DataSourceReportX"

                    regards
                    Grzegorz Grzybek

                    Comment

                    Working...
                    X