Custom Queries with Spring Data JPA’s @Query Annotation

 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]);

}

}