Custom Queries

<?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>3.3.5</version>
        <relativePath/>
    </parent>

    <groupId>com.example</groupId>
    <artifactId>student-customquery-demo</artifactId>
    <version>1.0.0</version>

    <properties>
        <java.version>17</java.version>
    </properties>

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

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

package com.example.custom.entity;

import jakarta.persistence.*;

@Entity
@Table(name = "students")
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @Column(unique = true)
    private String email;

    private String department;
    private int age;
    private int marks;      // out of 100

    public Student() {}

    public Student(String name, String email, String department, int age, int marks) {
        this.name = name;
        this.email = email;
        this.department = department;
        this.age = age;
        this.marks = marks;
    }

    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getDepartment() { return department; }
    public void setDepartment(String department) { this.department = department; }
    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
    public int getMarks() { return marks; }
    public void setMarks(int marks) { this.marks = marks; }
}

package com.example.custom.repository;

import com.example.custom.dto.DepartmentReport;
import com.example.custom.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface StudentRepository extends JpaRepository<Student, Long> {

    boolean existsByEmail(String email);          // a derived method still works alongside @Query

    // ===== TYPE 1: JPQL query =====
    // Written with the ENTITY name (Student) and FIELD names (department, marks), not table/column names.
    // :department and :minMarks are named parameters.
    @Query("SELECT s FROM Student s WHERE s.department = :department AND s.marks >= :minMarks ORDER BY s.marks DESC")
    List<Student> findTopPerformers(@Param("department") String department, @Param("minMarks") int minMarks);

    // JPQL with functions and OR: search in name or email, ignoring upper/lower case
    @Query("SELECT s FROM Student s WHERE LOWER(s.name) LIKE LOWER(CONCAT('%', :keyword, '%')) " +
           "OR LOWER(s.email) LIKE LOWER(CONCAT('%', :keyword, '%'))")
    List<Student> searchByKeyword(@Param("keyword") String keyword);

    // JPQL with positional parameters: ?1 is the first argument, ?2 the second (no @Param needed)
    @Query("SELECT s FROM Student s WHERE s.age BETWEEN ?1 AND ?2 ORDER BY s.age")
    List<Student> findByAgeRange(int minAge, int maxAge);

    // ===== TYPE 2: Native SQL query =====
    // Real SQL using the TABLE name (students) and COLUMN names. Runs exactly as written on MySQL.
    @Query(value = "SELECT * FROM students WHERE marks = (SELECT MAX(marks) FROM students)", nativeQuery = true)
    List<Student> findHighestScorers();

    // ===== TYPE 3: Reporting query (aggregate + custom result shape) =====
    // "SELECT new ..." builds a DepartmentReport object for each group instead of a Student.
    @Query("SELECT new com.example.custom.dto.DepartmentReport(s.department, COUNT(s), AVG(s.marks), MAX(s.marks)) " +
           "FROM Student s GROUP BY s.department ORDER BY s.department")
    List<DepartmentReport> departmentReport();

    // ===== TYPE 4: Modifying query (UPDATE / DELETE) =====
    // @Modifying is required for UPDATE and DELETE. The return value is the number of rows changed.
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query("UPDATE Student s SET s.marks = s.marks + :bonus WHERE s.department = :department AND s.marks + :bonus <= 100")
    int addBonusMarks(@Param("department") String department, @Param("bonus") int bonus);

    @Modifying(clearAutomatically = true)
    @Transactional
    @Query("DELETE FROM Student s WHERE s.marks < :minMarks")
    int deleteBelowMarks(@Param("minMarks") int minMarks);
}

package com.example.custom.dto;

// A record that holds one line of the report. The JPQL query fills it through its constructor.
public record DepartmentReport(String department, Long totalStudents, Double averageMarks, Integer highestMarks) {

    public DepartmentReport {
        if (averageMarks != null) {
            averageMarks = Math.round(averageMarks * 10) / 10.0;   // 73.33333 -> 73.3
        }
    }
}

package com.example.custom.controller;

import com.example.custom.dto.DepartmentReport;
import com.example.custom.entity.Student;
import com.example.custom.repository.StudentRepository;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping("/students")
public class StudentController {

    private final StudentRepository repository;

    public StudentController(StudentRepository repository) {
        this.repository = repository;
    }

    @PostMapping
    public ResponseEntity<Object> create(@RequestBody Student student) {
        if (repository.existsByEmail(student.getEmail())) {
            return ResponseEntity.status(HttpStatus.CONFLICT).body(Map.of("error", "Email already exists"));
        }
        student.setId(null);
        return ResponseEntity.status(HttpStatus.CREATED).body(repository.save(student));
    }

    @GetMapping
    public List<Student> getAll() {
        return repository.findAll();
    }

    // ---------- FILTERING (JPQL) ----------
    @GetMapping("/top-performers")
    public List<Student> topPerformers(@RequestParam String department, @RequestParam int minMarks) {
        return repository.findTopPerformers(department, minMarks);
    }

    @GetMapping("/search")
    public List<Student> search(@RequestParam String keyword) {
        return repository.searchByKeyword(keyword);
    }

    @GetMapping("/age-range")
    public List<Student> ageRange(@RequestParam int min, @RequestParam int max) {
        return repository.findByAgeRange(min, max);
    }

    // ---------- FILTERING (native SQL) ----------
    @GetMapping("/highest")
    public List<Student> highest() {
        return repository.findHighestScorers();
    }

    // ---------- REPORTING ----------
    @GetMapping("/report/departments")
    public List<DepartmentReport> departmentReport() {
        return repository.departmentReport();
    }

    // ---------- MODIFYING ----------
    // PUT /students/department/CSE/bonus?marks=5
    @PutMapping("/department/{department}/bonus")
    public Map<String, Integer> addBonus(@PathVariable String department, @RequestParam int marks) {
        return Map.of("updatedRows", repository.addBonusMarks(department, marks));
    }

    // DELETE /students/below/60
    @DeleteMapping("/below/{marks}")
    public Map<String, Integer> deleteBelow(@PathVariable int marks) {
        return Map.of("deletedRows", repository.deleteBelowMarks(marks));
    }
}

package com.example.custom;

import com.example.custom.entity.Student;
import com.example.custom.repository.StudentRepository;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import java.util.List;

@SpringBootApplication
public class CustomQueryApplication {

    public static void main(String[] args) {
        SpringApplication.run(CustomQueryApplication.class, args);
    }

    // Adds sample students once (only when the table is empty)
    @Bean
    CommandLineRunner seedData(StudentRepository repository) {
        return args -> {
            if (repository.count() == 0) {
                repository.saveAll(List.of(
                        new Student("Ravi Kumar",   "ravi@example.com",  "CSE", 21, 85),
                        new Student("Ravi Teja",    "teja@example.com",  "ECE", 22, 72),
                        new Student("Sneha Reddy",  "sneha@example.com", "ECE", 20, 91),
                        new Student("Anil Verma",   "anil@example.com",  "CSE", 23, 58),
                        new Student("Priya Nair",   "priya@example.com", "IT",  19, 91),
                        new Student("Rahul Sharma", "rahul@example.com", "CSE", 24, 77)));
            }
        };
    }
}