Aggregate function with spring data

I was working on a small project. Requirement was different in term of object mapping. There is simple way to map  java object with database table. Aggregate function in query is not mapped with entity class. We don't have direct mapping attribute in JPA annotation to map aggregate function with entity class.

Spring data support this using interface. In ORM query we use aggregate function e.g. count , sum etc. the output of these aggregate function cannot directly map with the Entity class. To collect the outcome of aggregate methods. We need to map using interface in java and provide the Interface class with mapping method.

    public List<ITeacherReport> getTeacherReport(String teacherName);

Our requirement is to count solved question per month by teacher from answer table. We have below environment configuration.

• Thymeleaf (for view,  this is template engine)
• JDK 1.8
• Maven
• Springboot framework 
• MySql  for database operation

Open the start.spring.io and create a maven project. you are free to choose the option of jar or war.  now we are living in this world where the weapon of mass destruction are stored in  many countries. So i request you to choose option jar .  ( joking). 

It is always better to choose jar with springboot application.   

Dependency in pom.xml . 

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

First  we require an interface (ITeacherReport). This interface will link to the result of the repository query.  So first need to create an interface to collect the data. We just need to write a class  with getter   method. Just need a plain interface. Datatype of return getter method declaration in interface  map with sql query alias field datatype I have mark that with green colour.

@Query(value = "SELECT MONTH(answer_date) AS monthData, "
+ " COUNT(answer_date) AS answerData, "

                + " YEAR(answer_date) AS sessionYear "
+ " FROM (SELECT answer_date FROM answer WHERE teacher_name=?1) AS answer  "
+ " GROUP BY MONTH(answer_date) ",nativeQuery = true)

package com.school.question.model;

public interface ITeacherReport {
     Integer getMonthData();
     Long    getAnswerData();
     Integer getSessionYear();
}

We require a controller class to entertain the request received from client.  

1. Controller class. 

@Autowired
private ReportServiceImpl  reportService;


@GetMapping("/performance")
    public String getTotalAndMonthCount(@RequestParam String teacherName,Model model) {
        Optional<User>  teacherInfo = reportService.getTeacherRecord(teacherName);
        List<ITeacherReport> totalAnser = reportService.getTeacherReport(teacherName);
        model.addAttribute("totalAnswer",totalAnser);
        model.addAttribute("teacherInfo",teacherInfo.get());
        return "report/teacherReport";
    }

2. Service class

ReportService is an interface. public List<ITeacherReport> getTeacherReport(String userName) is declared in report service class. 

@Service
public class ReportServiceImpl implements ReportService {

@Override
	public List<ITeacherReport> getTeacherReport(String userName) {
		return userRepository.getTeacherReport(userName);
	}
}

3. Repository Class

Sql query attached with getTeacherReport( ) method in Repository class configured/declared as native query in JPA. 

public interface AnswerRepository extends JpaRepository<Answer, Long> {

@Query(value = "SELECT MONTH(answer_date) AS monthData, "
   + " COUNT(answer_date) AS answerData, YEAR(answer_date) AS sessionYear "
   + " FROM (SELECT answer_date FROM answer WHERE teacher_name=?1) AS answer  "
   + " GROUP BY MONTH(answer_date) ",nativeQuery = true)

    public List<ITeacherReport> getTeacherReport(String teacherName);

}

CREATE TABLE `answer` (
	`answer_id` BIGINT(20) NOT NULL,
	`answer` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`answer_date` DATE NULL DEFAULT NULL,
	`data` LONGBLOB NULL DEFAULT NULL,
	`file_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`file_type` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`question_id` BIGINT(20) NULL DEFAULT NULL,
	`student_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`teacher_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	PRIMARY KEY (`answer_id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

We have @GetMapping in controller.  to test it just run code and type the URL on browser.  (this is according to my configuration . it depends on your requirement to pass any variable or not. You need get method or post method.

.  

http://localhost:8080/performance?teacherName=try_try@8.gmail.com

You can get this code from github https://github.com/PersonSimple/aggregatefunction.git

Thanks