Procedure Call Using Jasper Reports ,Spring MVC

Hello friends.today i am going to demonstrate you how we can call procedure through Jasper reports.

The Project Structure is given below :

The Code is as given below.

MODEL CLASS 

package org.demo.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;


@Entity
@Table(name="student")
public class Student
{

@Id @Column(name="id")
private String studentid;
@Column
private String name;



public Student(){}
public String getStudentid() {
return studentid;
}


public void setStudentid(String studentid) {
this.studentid = studentid;
}


public String getName() {
return name;
}


public void setName(String name) {
this.name = name;
}





}

CONTROLLER:

package org.demo.controller;

import java.sql.SQLException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.validation.Validator;

import org.apache.commons.dbcp.BasicDataSource;

import org.dao.StudentDAO;

import org.demo.model.Student;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.stereotype.Controller;

import org.springframework.ui.Model;

import org.springframework.validation.BindingResult;

import org.springframework.validation.annotation.Validated;

import org.springframework.web.bind.annotation.ModelAttribute;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.ResponseBody;

import org.springframework.web.context.WebApplicationContext;

import org.springframework.web.context.support.WebApplicationContextUtils;

import org.springframework.web.servlet.ModelAndView;

import org.springframework.web.servlet.mvc.support.RedirectAttributes;

@Controller

public class StudentController

{

    

   @RequestMapping(value="/show_student", method=RequestMethod.GET)

   public String showStudent(@ModelAttribute("student1") Student student) {

      

       return "show_student";

   }

   

   @RequestMapping(method = RequestMethod.POST , value = "/GenerateReport")

   public @ResponseBody void generatePdfReport(ModelAndView modelAndView,HttpServletRequest request,HttpServletResponse response) throws SQLException{

 

WebApplicationContext context =WebApplicationContextUtils.getWebApplicationContext(request.getServletContext());

StudentDAO dao=(StudentDAO)context.getBean("studentDAO");

BasicDataSource dataSource = (BasicDataSource)context.getBean("myDataSource");

String contextPath = request.getServletContext().getRealPath("report/StudentReport.jrxml");

dao.generatePdfReport(dataSource,contextPath);

      

    }

   }

DAO LAYER:

package org.dao;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import net.sf.jasperreports.engine.JasperCompileManager;

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.design.JasperDesign;

import net.sf.jasperreports.engine.query.JRQueryExecuterFactory;

import net.sf.jasperreports.engine.util.JRProperties;

import net.sf.jasperreports.engine.xml.JRXmlLoader;

import org.apache.commons.dbcp.BasicDataSource;

import org.codehaus.jackson.map.ObjectMapper;

import org.demo.model.Student;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import org.springframework.orm.hibernate3.HibernateTemplate;

@SuppressWarnings("deprecation")

public class StudentDAO {

private HibernateTemplate template;

@Autowired

private BasicDataSource myDataSource;

public HibernateTemplate getTemplate() {

return template;

}

public void setTemplate(HibernateTemplate template) {

this.template = template;

}

public StudentDAO(HibernateTemplate template) {

this.template = template;

}

public void insert(Student student) {

template.save(student);

}

public List<Student> getStudents() {

return template.find("from student");

}

public void generatePdfReport(BasicDataSource dataSource, String contextPath)

throws SQLException {

Connection conn = dataSource.getConnection();

try {

InputStream input = new FileInputStream(new File(contextPath));

System.out.println("Context Path is  : =" + contextPath);

JasperDesign jasperDesign = JRXmlLoader.load(input);

System.out.println("Compiling Report Designs");

JasperReport jasperReport = JasperCompileManager

.compileReport(jasperDesign);

jasperReport.setProperty(

"net.sf.jasperreports.query.executer.factory.plsql",

"com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory");

JRProperties.setProperty(

JRQueryExecuterFactory.QUERY_EXECUTER_FACTORY_PREFIX

+ "plsql",

"com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory");

System.out.println("Creating JasperPrint Object");

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

parameters.put("studId", "1");

JasperPrint jasperPrint = JasperFillManager.fillReport(

jasperReport, parameters, conn);

int pages=jasperPrint.getPages().size();

if(pages != 0)

{

File f = new File("d:\\Studentnew.pdf");

f.createNewFile();

// Exporting the report

OutputStream output = new FileOutputStream(f);

JasperExportManager.exportReportToPdfStream(jasperPrint, output);

System.out.println("Report Generation Complete");

}

else

{

System.out.println(" NO DATA FOUND TO EXPORT .....");

}

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

}

public void callStoredProcedure(BasicDataSource datasource) {

ObjectMapper m = new ObjectMapper();

JdbcTemplate template = new JdbcTemplate(datasource);

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(template)

.withProcedureName("getStudent");

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

inParamMap.put("int_stockcode", 1);

SqlParameterSource in = new MapSqlParameterSource(inParamMap);

Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);

System.out.println(simpleJdbcCallResult);

System.out.println(simpleJdbcCallResult.get("name"));

// Student anotherBean = m.convertValue(simpleJdbcCallResult,

// Student.class);

// System.out.println(anotherBean.getStudentid());

// System.out.println(anotherBean.getName());

Iterator iterator = simpleJdbcCallResult.entrySet().iterator();

while (iterator.hasNext()) {

Student student = new Student();

/*

* 

* student.setStudentid((String)simpleJdbcCallResult.get("id"));

* student.setName((String)simpleJdbcCallResult.get("name"));

* System.out.println("st5udent : =>"+student.getName());

*/

/*

* System.out.println("The key is: " + mapEntry.getKey() +

* ",value is :" + mapEntry.getValue());

*/

}

}

public static void main(String[] args) {

ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext(

"/context-conf.xml");

BasicDataSource datasource = (BasicDataSource) context

.getBean("myDataSource");

StudentDAO dao = (StudentDAO) context.getBean("studentDAO");

dao.callStoredProcedure(datasource);

}

}

WEB.XML

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">

  <display-name>ProcCallJasperSpring</display-name>

  <welcome-file-list>

    <welcome-file>add_student.jsp</welcome-file>

  </welcome-file-list>

  <listener>

    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>

  </listener>

  <context-param>

    <param-name>contextConfigLocation</param-name>

    <param-value>WEB-INF/context-conf.xml</param-value>

  </context-param>

  <servlet>

    <servlet-name>dispatcher</servlet-name>

    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

    <load-on-startup>1</load-on-startup>

  </servlet>

  <servlet-mapping>

    <servlet-name>dispatcher</servlet-name>

    <url-pattern>*.html</url-pattern>

  </servlet-mapping>

</web-app>

context-conf.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xmlns:context="http://www.springframework.org/schema/context"

    xmlns:mvc="http://www.springframework.org/schema/mvc"

    xsi:schemaLocation="http://www.springframework.org/schema/beans 

    http://www.springframework.org/schema/beans/spring-beans.xsd 

    http://www.springframework.org/schema/context  

    http://www.springframework.org/schema/context/spring-context.xsd

     http://www.springframework.org/schema/mvc 

     http://www.springframework.org/schema/mvc/spring-mvc.xsd

      http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd"

        >

     

    

    

    

        <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

<property name="driverClassName" value="com.mysql.jdbc.Driver" />

<property name="url" value="jdbc:mysql://localhost:3306/studentdb" />

<property name="username" value="root" />

<property name="password" value="admin!@#"/>

</bean>

<bean id="mySessionFactory"

class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">

<property name="dataSource" ref="myDataSource" />

<property name="annotatedClasses">

<list>

<value>org.demo.model.Student</value>

</list>

</property>

<property name="hibernateProperties">

<props>

<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>

<prop key="hibernate.show_sql">true</prop>

<prop key="hibernate.hbm2ddl.auto">none</prop>

</props>

</property>

</bean>

<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">

<constructor-arg ref="mySessionFactory" />

</bean> 

<bean id="studentDAO" class="org.dao.StudentDAO">

   <constructor-arg ref="hibernateTemplate" />

</bean>

      

    

     

    <bean id="messageSource"

        class="org.springframework.context.support.ReloadableResourceBundleMessageSource">

        <property name="basename" value="classpath:message" />

        <property name="defaultEncoding" value="UTF-8" />

    </bean>

    

    

     </beans>

InitStudent.js

function InitStudent()

{

$('#AddStudent').click(function()

{   

 

 AddStudent();

 

});

 

$('#GenerateReport').click(function()

{   

 

GenerateReport();

 

});

 

}

StudentLogic.js

function  AddStudent()

{

$.ajax({

  url: "add_student.html",

  dataType:'json',

  type:'post',

  data:  $('#StudentDetail').serialize(),

beforeSend: function() {

$.blockUI();

},

  success: function (dataCheck)

  {

  },

  error: function (xhr, ajaxOptions, thrownError) {}

});  

}

function GenerateReport()

{

$.ajax({

  url: "GenerateReport.html",

  dataType:'json',

  type:'post',

  data:  {},

beforeSend: function() {

$.blockUI();

},

  success: function (dataCheck)

  {

  },

  error: function (xhr, ajaxOptions, thrownError) {}

});  

}

add_student.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<%@ taglib uri="http://www.springframework.org/tags/form"

    prefix="springForm"%>

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>ADD STUDENT FORM </title>

<style>

.error {

    color: #ff0000;

    font-style: italic;

    font-weight: bold;

}

</style>

</head>

<script type="text/javascript" src="script/jquery-1.11.0.min.js"></script>

<script type="text/javascript" src="script/bootstrap.js"></script>

<script type="text/javascript" src="script/jquery-ui.min.js"></script>

<script type="text/javascript" src="script/jquery.layout-latest.js"></script>

<script type="text/javascript" src="script/local/grid.locale-en.js" ></script>

<script type="text/javascript" src="script/jquery.blockUI.js"></script>

<script type="text/javascript" src="script/js/javascript.js"></script>

<script type="text/javascript" src="script/js/InitStudent.js"></script>

<script type="text/javascript" src="script/js/StudentLogic.js"></script>

<script type="text/javascript">

$(document).ready(function(){

InitStudent();

});

</script>

<body>

 

    <%-- <springForm:form method="POST" commandName="student"

        action="ValidateRequest"> --%>

        

        <form action="ValidateRequest" method="POST"></form>

        <table>

            <tr>

                <td>ID:</td>

                <td><input type="text" id="txtid" name="txtid">   </td>

               <%--  <td><springForm:errors path="txtid" cssClass="error" /></td> --%>

            </tr>

           

           <tr>

                <td>NAME:</td>

                <td><input type="text" id="txtname" name="txtname"></td>

                <%-- <td><springForm:errors path="txtname" cssClass="error" /></td> --%>

            </tr>

            

          

            <tr>

                <td colspan="3"><input type="submit"  id="AddStudent"  name="AddStudent" value="Submit"></td>

                <td colspan="3"><input type="submit"  id="GenerateReport"  name="GenerateReport" value="Generate Report"></td>

                

            </tr>

        </table>

 

  

 

</body>

</html>

 Procedure that i call from Java code is given below :

DELIMITER $$

DROP PROCEDURE IF EXISTS `getStudent` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudent`(studid VARCHAR(20))

BEGIN

SELECT id,name,college FROM student WHERE id = studid;

END $$

DELIMITER ;

Report will look like given below :

Happy Learning..!!

for any query ping me on pathak.nisarg@yahoo.com

Thanks for Reading..!