Group By in Jasper Report in Spring MVC Framework and Spring Data JPA

Create Spring MVC Project

On the Eclipse, create a Spring MVC project in Spring Boot

Enter Project Information:

• Name: LearnSpringMVCWithRealApps
• Group: com.demo
• Artifact: LearnSpringMVCWithRealApps
• Description: Learn Spring MVC with Real Apps
• Package: com.demo

Select the technologies and libraries to be used:

• Web
• JPA
• MySQL

Click Next button to show Site Information for project

Click Finish button to finish create Spring MVC project

Configure pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
						http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.demo</groupId>
	<artifactId>LearnSpringMVCWithRealApps</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>LearnSpringMVCWithRealApps</name>
	<description>Learn Spring MVC with Real Apps</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.5.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<!-- JSTL tag lib -->
		<dependency>
			<groupId>javax.servlet.jsp.jstl</groupId>
			<artifactId>javax.servlet.jsp.jstl-api</artifactId>
			<version>1.2.1</version>
		</dependency>

		<dependency>
			<groupId>taglibs</groupId>
			<artifactId>standard</artifactId>
			<version>1.1.2</version>
		</dependency>

		<!-- Tomcat for JSP rendering -->
		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>net.sf.jasperreports</groupId>
			<artifactId>jasperreports</artifactId>
			<version>6.7.0</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
		</dependency>

		<dependency>
			<groupId>org.scala-lang</groupId>
			<artifactId>scala-library</artifactId>
			<version>2.11.0</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-test</artifactId>
			<scope>test</scope>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Create Database

Create a database with the name is learn_spring_mvc_with_real_apps. This database have 1 table: Product table

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `price` decimal(10,1) NOT NULL,
  `quantity` int(11) NOT NULL,
  `category_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `name`, `price`, `quantity`, `category_name`) VALUES
(40, 'fashion 4', '1.00', 3, 'Category 1'),
(39, 'fashion 3', '1.00', 3, 'Category 1'),
(38, 'fashion 2', '1.00', 3, 'Category 1'),
(37, 'fashion 1', '1.00', 3, 'Category 2'),
(36, 'computer 3', '1.00', 3, 'Category 2'),
(35, 'computer 2', '1.00', 3, 'Category 3'),
(34, 'computer 1', '1.00', 3, 'Category 3'),
(33, 'mobile 1', '20.00', 7, 'Category 3');

Structure of Product Table

Data of Product Table

Configure application.properties

spring.mvc.view.prefix = /WEB-INF/views/
spring.mvc.view.suffix = .jsp
spring.mvc.static-path-pattern=/resources/**

spring.datasource.url= jdbc:mysql://localhost:3306/learn_spring_mvc_with_real_apps
spring.datasource.username=root
spring.datasource.password=123456

server.port=9596

Report

In src/main/resources folder, create new folder named reports. In this folder, create new Jasper Report named report.jrxml as below:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report1" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="84feeac3-d1f7-4503-84bf-fc3fa153df55">
	<queryString>
		<![CDATA[]]>
	</queryString>
	<field name="id" class="java.lang.Integer"/>
	<field name="price" class="java.math.BigDecimal"/>
	<field name="quantity" class="java.lang.Integer"/>
	<field name="categoryName" class="java.lang.String"/>
	<field name="name" class="java.lang.String"/>
	<group name="Group1">
		<groupExpression><![CDATA[$F{categoryName}]]></groupExpression>
		<groupHeader>
			<band height="72">
				<textField>
					<reportElement x="27" y="10" width="100" height="20" uuid="8992d0ad-1f5e-4210-b50b-1e29dc66f119"/>
					<textElement>
						<font isBold="true"/>
					</textElement>
					<textFieldExpression><![CDATA[$F{categoryName}]]></textFieldExpression>
				</textField>
				<staticText>
					<reportElement x="30" y="38" width="50" height="22" uuid="bcaf85c0-57ff-4a34-af27-cbf1330125ae"/>
					<textElement>
						<font isBold="true"/>
					</textElement>
					<text><![CDATA[Id]]></text>
				</staticText>
				<staticText>
					<reportElement x="135" y="38" width="70" height="22" uuid="faf9b9ee-7cf2-4ea5-80b8-d371c3c753b9"/>
					<textElement>
						<font size="11" isBold="true"/>
					</textElement>
					<text><![CDATA[Name]]></text>
				</staticText>
				<staticText>
					<reportElement x="280" y="38" width="70" height="22" uuid="93727672-9f69-4856-aa26-33837e8dc417"/>
					<textElement>
						<font fontName="SansSerif" isBold="true"/>
					</textElement>
					<text><![CDATA[Price]]></text>
				</staticText>
				<staticText>
					<reportElement x="380" y="38" width="70" height="22" uuid="09b47abe-09b2-479a-bb76-9770e361a23c"/>
					<textElement>
						<font isBold="true"/>
					</textElement>
					<text><![CDATA[Quantity]]></text>
				</staticText>
			</band>
		</groupHeader>
		<groupFooter>
			<band height="15"/>
		</groupFooter>
	</group>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="45" splitType="Stretch">
			<staticText>
				<reportElement x="180" y="3" width="230" height="30" uuid="04d35299-5eb0-4a34-b340-3ca5cf8290fc"/>
				<textElement>
					<font size="20" isBold="true"/>
				</textElement>
				<text><![CDATA[Product List]]></text>
			</staticText>
		</band>
	</title>
	<pageHeader>
		<band height="1" splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band splitType="Stretch"/>
	</columnHeader>
	<detail>
		<band height="48" splitType="Stretch">
			<textField>
				<reportElement x="30" y="10" width="50" height="20" uuid="99fa4c16-cfe2-4482-828e-4439abb9e8f0"/>
				<textFieldExpression><![CDATA[$F{id}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="280" y="11" width="70" height="19" uuid="ba6e9cfa-6d4c-470b-8bc4-eb2e3411c21e"/>
				<textFieldExpression><![CDATA[$F{price}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="380" y="11" width="70" height="19" uuid="447253d3-d268-4873-b0b7-9c174c6a7c9f"/>
				<textFieldExpression><![CDATA[$F{quantity}]]></textFieldExpression>
			</textField>
			<line>
				<reportElement x="20" y="36" width="471" height="1" uuid="cf1ee61b-8c60-406d-8195-bff8c93b2423"/>
			</line>
			<textField>
				<reportElement x="130" y="10" width="100" height="20" uuid="0a0a83d1-9e11-48c9-ab2c-62d42a9363e3">
					<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="03b19b38-6d1b-4187-b530-7cfa0e8fbaf4"/>
				</reportElement>
				<textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band height="4" splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band height="54" splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band height="42" splitType="Stretch"/>
	</summary>
</jasperReport>

Entities Class

Create new package, named com.demo.entities. In this package, create entities class as below:

Product Entity

Create new java class, named Product.java

package com.demo.entities;

import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "product")
public class Product {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;
	private String name;
	private BigDecimal price;
	private int quantity;
	@Column(name = "category_name")
	private String categoryName;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

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

	public BigDecimal getPrice() {
		return price;
	}

	public void setPrice(BigDecimal price) {
		this.price = price;
	}

	public int getQuantity() {
		return quantity;
	}

	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}

	public String getCategoryName() {
		return categoryName;
	}

	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}

}

Create ProductRepository Interface

Create new package named com.demo.repositories. In this package create the ProductRepository interface implements from CrudRepository interface of Spring Data JPA that provides CRUD Operations for an entity.

package com.demo.repositories;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.demo.entities.Product;

@Repository("productRepository")
public interface ProductRepository extends CrudRepository<Product, Integer> {
}

Create ProductService Interface

Create new package named com.demo.services. In this package create the ProductService interface as below:

package com.demo.services;

import java.util.List;
import java.util.Map;

public interface ProductService {

	public List<Map<String, Object>> report();

}

Create ProductServiceImpl class

In com.demo.services package, create ProductServiceImpl class implements method from ProductService interfaces

package com.demo.services;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.demo.entities.Product;
import com.demo.repositories.ProductRepository;

@Service("productService")
public class ProductServiceImpl implements ProductService {

	@Autowired
	private ProductRepository productRepository;

	@Override
	public List<Map<String, Object>> report() {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		for (Product product : productRepository.findAll()) {
			Map<String, Object> item = new HashMap<String, Object>();
			item.put("id", product.getId());
			item.put("name", product.getName());
			item.put("price", product.getPrice());
			item.put("quantity", product.getQuantity());
			item.put("categoryName", product.getCategoryName());
			result.add(item);
		}
		return result;
	}

}

Create Controllers

Create new package named com.demo.controllers. In this package, create new java class named ProductController.java

package com.demo.controllers;

import java.io.InputStream;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import com.demo.services.ProductService;
import net.sf.jasperreports.engine.DefaultJasperReportsContext;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.engine.export.HtmlExporter;
import net.sf.jasperreports.export.SimpleExporterInput;
import net.sf.jasperreports.export.SimpleHtmlExporterOutput;

@Controller
@RequestMapping("product")
public class ProductController {

	@Autowired
	private ProductService productService;

	@RequestMapping(method = RequestMethod.GET)
	public String index(ModelMap modelMap) {
		return "product/index";
	}

	@RequestMapping(value = "report", method = RequestMethod.GET)
	public void report2(HttpServletResponse response) throws Exception {
		response.setContentType("text/html");
		JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(productService.report());
		InputStream inputStream = this.getClass().getResourceAsStream("/reports/report.jrxml");
		JasperReport jasperReport = JasperCompileManager.compileReport(inputStream);
		JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null, dataSource);
		HtmlExporter exporter = new HtmlExporter(DefaultJasperReportsContext.getInstance());
		exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
		exporter.setExporterOutput(new SimpleHtmlExporterOutput(response.getWriter()));
		exporter.exportReport();
	}

}

Create Views

Create new folders with path webapp\WEB-INF\views in src\main. In views folder, create new folder named product. In product folder, create new jsp named index.jsp as below:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Report</title>
</head>
<body>

	<h3>Reports</h3>
	<a href="${pageContext.request.contextPath }/product/report" target="_blank">Generate Report</a>

</body>
</html>

Structure of Spring MVC Project

Run Application

Select LearnSpringMVCWithRealAppsApplication.java file in com.demo package, right click and select Run As/Spring Boot App menu

Access index method in product controller with following url: http://localhost:9596/product

Output

Click Generate Report link to open report

Output

References

I recommend you refer to the books below to learn more about the knowledge in this article:

• Spring MVC Beginners Guide – Second Edition
• Spring Boot in Action
• Pro Spring Boot
• Spring Data