Custom Queries with Spring Data JPA’s @Query Annotation

For Explanation Watch Video

Directory Structure ::

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.4</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.app</groupId>
<artifactId>SpringBootDataJPASelect</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>SpringBootDataJPASelect</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<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>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>

application.properties

#datasource
spring.datasource.url=jdbc:mysql://localhost:3306/new
spring.datasource.username=root
spring.datasource.password=root
#jpa
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

SpringBootDataJpaSelectApplication

package com.app;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringBootDataJpaSelectApplication {
public static void main(String[] args) {
  SpringApplication.run(SpringBootDataJpaSelectApplication.class, args);
 }
}

Employee

package com.app.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "emptab")
public class Employee {
 
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "eid")
 private Integer empId;
 
 @Column(name = "ename")
 private String empName;
 
 @Column(name = "dept")
 private String empDept;
 
 @Column(name = "esal")
 private Double empSal;
public Employee(String empName, String empDept, Double empSal) {
  super();
  this.empName = empName;
  this.empDept = empDept;
  this.empSal = empSal;
 }
 
 
}

EmployeeRepository

package com.app.repo;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.app.entity.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Integer>{
 
 //named parameteres
 //SELECT * FROM EMPLOYEE WHERE EID>=MIN AND EID<=MAX;
 @Query("From Employee WHERE empId>=:min and empId<=:max")
 List<Employee> fetchEmployeeByIdRange(int min,int max);
 
 //positonal param
 @Query("FROM Employee where empId>=?1 and empId<=?2")
 public List<Employee> searchByEmpIdRange(int min,int max);
 
 @Query(value = "SELECT * FROM emptab where dept=:dept",nativeQuery = true)
 public List<Employee> searchEmpByDept(String dept);
 
 @Query("FROM Employee where empName in(:name1,:name2,:name3) order by empName desc")
 public List<Employee> fetchEmpByName(String name1,String name2,String name3);
 
 //specific columns
 @Query("SELECT empId,empName,empSal from Employee where empSal>=:sal and empName in(:name1,:name2,:name3)")
 public List<Object[]> fetchEmpBySalAndName(Double sal,String name1,String name2,String name3);
 
 //for specific colum
 @Query("select empName from Employee where empId>=:min and empId<=:max")
 public List<String> fetchEmpNameById(int min,int max);
 
 //single row
 @Query("select e from Employee e where empName=:name")
 public Employee fetchSingleRow(String name);
 
 //single row specific col
 @Query("SELECT empId,empName,empSal from Employee where empName=:name")
 public Object fetchEmpPartialDataByName(String name);
 
 //max sal
 @Query("SELECT max(empSal) from Employee")
 public double fetchMaxSal();
 
 
 @Query("SELECT max(empSal),min(empSal),avg(empSal),count(*),sum(empSal) from Employee")
 public Object fetchAggregateData();
}

TestRunner

package com.app.runner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
import com.app.repo.EmployeeRepository;
@Component
public class TestRunner implements CommandLineRunner{
@Autowired
 private EmployeeRepository repo;
 
 @Override
 public void run(String... args) throws Exception {
  //positional param
  //repo.searchByEmpIdRange(5, 8).forEach(System.out::println);
  
  //emp acc to dept
  //repo.searchEmpByDept("QA").forEach(System.out::println);
  
  //fetch emp by names
  //repo.fetchEmpByName("A", "D", "G").forEach(System.out::println);
  
  //fetch the emp id,name,sal by sal and name
  //List<Object[]>
  /*repo.fetchEmpBySalAndName(1100.0, "H", "K", "A")
  .stream()
  .map(ob->ob[0]+","+ob[1]+","+ob[2])
  .forEach(System.out::println);*/
  
  //fetch empName col
  //repo.fetchEmpNameById(2, 8).forEach(System.out::println);
  
  //fetch only one row
  //System.out.println(repo.fetchSingleRow("B"));
  
  /*Object[] res = (Object[])repo.fetchEmpPartialDataByName("B");
  for(Object ob : res) {
   System.out.print(ob+" ");
  }
  System.out.println();*/
  
  //System.out.println(repo.fetchMaxSal());
  Object[] res = (Object[])repo.fetchAggregateData();
  System.out.println("Max sal "+res[0]);
  System.out.println("Min sal "+res[1]);
  System.out.println("Avg sal "+res[2]);
  System.out.println("Total rows "+res[3]);
  System.out.println("Sum of sal"+res[4]);
  
 }
}