9 - Spring Data JPA | Spring Boot | Project Work - List Enquiry | Table Join Projection
SOURCE CODE (GitHub Link)
https://github.com/VikramThakur8/Enqu...
PURPOSE:
This video covers Listing Enquiry. The list display multiple fields from multiple table fetched using JPA Join Query. Spring Data JPA Interface Projection is used to map Tuples.
This report uses almost all tables.
MYSQL DIALECT CUSTOMIZATION:
By default MySQLDialect does not support GROUP_CONCAT() function. However there is scope to customize and add this feature. We have done this by creating child class of MySQL5Dialect and support using SQLFunctionTemplate class supplied in Hibernate API. Then we can register customize dialect in Spring Boot Application.
package net.ezeon.poc;
import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;
public class MyCustomDialect extends MySQL5Dialect {
public MyCustomDialect() {
super();
this.registerFunction("group_concat", new SQLFunctionTemplate(StandardBasicTypes.STRING, "group_concat(?1)"));
}
}
//Register in application.properties
spring.jpa.properties.hibernate.dialect=net.ezeon.poc.MyCustomDialect
//Add below method in Repository class to test:
@Query("SELECT group_concat(e.enquiryId) FROM Enquiry as e")
public String findAllIds();
SOURCE CODE REFERENCE:
1. Controller Method:
@GetMapping("/enq-list")
public String enquiryList(@RequestParam(required = false) Long instId, Model m) {
m.addAttribute("enquiryList", enquiryRepository.getEnquiryDTOList(instId));
return "/enq-list"; //html
}
2. Repository Method (Report Query):
//Reporting JPQL / Query
@Query("SELECT e.enquiryId AS enquiryId, e.doe AS doe, e.committedFees AS fees, c.name AS name, c.phone AS phone,"
" (SELECT count(followupId) FROM Followup f WHERE e=f.enquiry) AS followUpCount,"
" (SELECT group_concat(co.name) FROM Course co, EnquiryCourse ec WHERE e=ec.enquiry AND ec.course=co) AS courses "
" FROM Enquiry e, Contact c WHERE e.contact=c and e.institute.instituteId=?1")
public List of EnquiryDto getEnquiryDTOList(Long instituteId);
3. Interface Projection DTO:
public interface EnquiryDto {
Long getEnquiryId();
String getPhone();
String getName();
String getCourses(); //comma separated
Integer getFollowUpCount();
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "dd/MM/yyyy")
Date getDoe();
Float getFees();
}
TECHNOLOGY USED:
Java 8, Spring Boot, Spring Data JPA, MySQL Database, NetBeans IDE, Hibernate JPA Implementation, Maven, MySQL Workbench, JSON, MicroServices.
PREVIOUS VIDEO IN CASE YOU MISSED ( Spring Data JPA | Spring Boot | Project Work - Add Enquiry | Complex Form Using JPA Hibernate) :
• 8 - Spring Data JPA | Spring Boot | Projec...