package com.example.repository;

import com.example.model.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentRepository extends JpaRepository<Student, Integer> {

    // 1. Find students by first name using JPQL
    @Query("SELECT s FROM Student s WHERE s.firstName = :firstName")
    List<Student> findByFirstName(@Param("firstName") String firstName);

    // 2. Find students by email using Native Query
    @Query(value = "SELECT * FROM students WHERE email = :email", nativeQuery = true)
    Student findByEmail(@Param("email") String email);

    // 3. Find students whose first name starts with a given prefix
    @Query("SELECT s FROM Student s WHERE s.firstName LIKE :prefix%")
    List<Student> findByFirstNameStartingWith(@Param("prefix") String prefix);

    // 4. Count students with a specific last name
    @Query("SELECT COUNT(s) FROM Student s WHERE s.lastName = :lastName")
    long countByLastName(@Param("lastName") String lastName);
}

package com.example.service;

import com.example.model.Student;
import com.example.repository.StudentRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;

    public List<Student> getStudentsByFirstName(String firstName) {
        return studentRepository.findByFirstName(firstName);
    }

    public Student getStudentByEmail(String email) {
        return studentRepository.findByEmail(email);
    }

    public List<Student> getStudentsByFirstNamePrefix(String prefix) {
        return studentRepository.findByFirstNameStartingWith(prefix);
    }

    public long countStudentsByLastName(String lastName) {
        return studentRepository.countByLastName(lastName);
    }
}

package com.example;

import com.example.model.Student;
import com.example.service.StudentService;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;

import java.util.List;

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

    @Bean
    public CommandLineRunner demo(StudentService studentService) {
        return args -> {
            studentService.saveStudent(new Student("Alice", "Brown", "alice@example.com"));
            studentService.saveStudent(new Student("Bob", "Smith", "bob@example.com"));
            studentService.saveStudent(new Student("Alice", "Taylor", "alice.taylor@example.com"));

            // Find by First Name
            List<Student> alices = studentService.getStudentsByFirstName("Alice");
            System.out.println("Students named Alice: " + alices.size());

            // Find by Email
            Student studentByEmail = studentService.getStudentByEmail("bob@example.com");
            System.out.println("Student found by email: " + studentByEmail.getFirstName());

            // Find by First Name Prefix
            List<Student> prefixResults = studentService.getStudentsByFirstNamePrefix("A");
            System.out.println("Students with names starting with A: " + prefixResults.size());

            // Count by Last Name
            long count = studentService.countStudentsByLastName("Brown");
            System.out.println("Number of students with last name 'Brown': " + count);
        };
    }
}