"Welcome To Ashok IT"				
			                  "Spring Boot and MicroServices"
                                            Topic : Spring Data JPA - Custom Queries
                                             Date  : 20/10/2024
                                                (Session - 49)
_____________________________________________________________________________________________________________________________
Last Session: Finder Methods
============================
* With the help of finder method of Spring Data JPA will have some problems

   1) We don't have any finder method to select limited columns on Entity class.

   2) We don't have any finder method to perform aggregate functions on Entity class.

   3) We don't have any finder method to get the results as grouping.

Today Session: Custom Queries
=============================
* Spring Data JPA Also provided other mechanism to execute custom queries on database tables.

* We can write our custom Queries in ORM Frameworks (or) Spring Data JPA using two techniques
      
    1) SQL Queries  ------------> Database Dependent Queries ---------------> Native Queries

    2) QL Queries   ------------> Database Independent Queries --------------> ORM Queries

    QL  -> Query Language and every ORM Framework will have their own Query Language Suppport

        JPA        -> JPQL   -> Java Persistence Query Language.

    Hibernate      -> HQL    -> Hibernate Query Language

    Eclipse Link   -> ELQL  -> Eclipse Link Query Language 

   Example
   =======
    select at.customer_id,at.customer_name from ashokit_customers at;  ----------> Native Query / SQL Query

    select c.customerId,c.customerName from Customer c;  ------------------------> ORM Query

                column names in query  <--------------> FieldNames from Entity Class

                table name in Query   <---------------> Entity Class Name

* Inorder to execute the Custom Queries either Native Queries/ORM Queries as part of an Spring Data JPA provided one           annotation i.e.,@Query and this annotation is used to execute only for select statements.

    @Query(value="HQL Query")
  
    @Query(value="SQL Query", nativeQuery=true)

* As programmer we need to add new custom method in our DAO Repoistory Interface on top of that method we need to add @Query    annotation

     public interface CustomerDao extends JPARepoistory<Customer,Integer>{

      @Query(value="select c.* from Customer c") //HQL Query
      //@Query(value="from Customer c")//HQL Query
      public Iterable<Customer> getAllCustomersByCustomQuery();

      @Query(value="select * from ashokit_customers", nativeQuery=true) //SQL Query
      public Iterable<Customer> getAllCustomersByNativeQuery();

      @Query(value="select customer_name,customer_location from ashokit_customers", nativeQuery=true);
      public List<Object[]> getCustomerNameAndCustomerLocationByCustomQuery();

      @Query(value="select c.customerName,c.customerLocation from Customer c")
      public List<Object[]> getCustomerNameAndCustomerLocationByHQLQuery();
 
      //NamedParameters
      @Query(value="from Customer c where c.location=:custLocation and c.gender=:custGender")
      public List<Customer> getAllCustomersByCity(String custLocation,String custGender);
   	  
      //IndexedParameters
      @Query(value="from Customer c where c.location=?1")
      public List<Customer> getAllCustomersByLocation(String custLocation);
   
      //IndexedParameters
      @Query(value="from Customer c where c.location=?1 and c.billAmount > ?2")
      public List<Customer> getAllCustomersByLocation(String custLocation, float custbillAmount);

    } 

Example
=======
Customer.java
=============
package com.ashokit.entity;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "ashokit_customers")
public class Customer {

	@Id
	@GeneratedValue
	@Column(name = "customer_id")
	private Integer customerId;

	@Column(name = "customer_name")
	private String name;

	@Column(name = "location")
	private String location;

	@Column(name = "bill_amount")
	private float billAmount;
	
	public Customer() {
		
	}
	
	public Customer(Integer customerId, String name, String location, float billAmount) {
		super();
		this.customerId = customerId;
		this.name = name;
		this.location = location;
		this.billAmount = billAmount;
	}
	
	public Customer(String name, String location, float billAmount) {
		this.name = name;
		this.location = location;
		this.billAmount = billAmount;
	}



	public Integer getCustomerId() {
		return customerId;
	}

	public void setCustomerId(Integer customerId) {
		this.customerId = customerId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getLocation() {
		return location;
	}

	public void setLocation(String location) {
		this.location = location;
	}

	public float getBillAmount() {
		return billAmount;
	}

	public void setBillAmount(float billAmount) {
		this.billAmount = billAmount;
	}

	@Override
	public String toString() {
		return "Customer [customerId=" + customerId + ", name=" + name + ", location=" + location + ", billAmount="
				+ billAmount + "]";
	}

}


CustomerService.java
====================
package com.ashokit.services;

public interface CustomerService {
	
	public void fetchCustomerDetailsUsingNativeQuery();
	
	public void fetchCustomerDetailsUsingHqlQuery();
	
	public void fetchSelectedColumnsUsingNativeQuery();
	
	public void fetchSelectedColumnsUsingHqlQuery();
	
	public void fetchCustomerDetailsByCity(String location);
	
	public void fetchCustomerDetailsByLocation(String location);
}



CustomerServiceImpl.java
========================
package com.ashokit.services;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.ashokit.dao.CustomerDao;
import com.ashokit.entity.Customer;

@Service
public class CustomerServiceImpl implements CustomerService {

	@Autowired
	private CustomerDao customerDao;

	@Override
	public void fetchCustomerDetailsUsingNativeQuery() {
		List<Customer> allCustomers = customerDao.getAllCustomersByNativeQuery();
		allCustomers.forEach(System.out::println);
	}

	@Override
	public void fetchCustomerDetailsUsingHqlQuery() {
        List<Customer> allCustomers = customerDao.getAllCustomers();
        allCustomers.forEach(System.out::println);
	}

	@Override
	public void fetchSelectedColumnsUsingNativeQuery() {
		 List<Object[]> allCustomers = customerDao.getAllCustomersInfo();
		 allCustomers.forEach(eachObject -> {
			 System.out.println(eachObject[0] + " ---" + eachObject[1]);
		 });
	}

	@Override
	public void fetchSelectedColumnsUsingHqlQuery() {
		 List<Object[]> allCustomers = customerDao.getAllCustomersDetails();
		 allCustomers.forEach(eachObject -> {
			 System.out.println(eachObject[0] + " ---" + eachObject[1]);
		 });
	}
	
	@Override
	public void fetchCustomerDetailsByCity(String location){
		List<Customer> allCustomers = customerDao.getAllCustomersByCity(location);
		allCustomers.forEach(System.out::println);
	}
	
	@Override
	public void fetchCustomerDetailsByLocation(String location){
		List<Customer> allCustomers = customerDao.getAllCustomersByLocation(location);
		allCustomers.forEach(System.out::println);
	}

}

CustomerDao.java
================
package com.ashokit.dao;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.ashokit.entity.Customer;

import jakarta.transaction.Transactional;

public interface CustomerDao extends  CrudRepository<Customer, Serializable>{

   	  @Query(value="from Customer c", nativeQuery = false)
	  public List<Customer> getAllCustomers();
   	  
   	  @Query(value="select * from ashokit_customers",nativeQuery = true)
   	  public List<Customer> getAllCustomersByNativeQuery();
   	  
   	  @Query(value="select customer_name,location from ashokit_customers",nativeQuery = true)
   	  public List<Object[]> getAllCustomersInfo();
   	  
   	  @Query(value="select at.name,at.location from Customer at",nativeQuery = false)
   	  public List<Object[]> getAllCustomersDetails();
   	  
   	  //NamedParameters
   	  @Query(value="from Customer c where c.location=:custLocation", nativeQuery = false)
	  public List<Customer> getAllCustomersByCity(String custLocation);
   	  
   	  //IndexedParameters
   	  @Query(value="from Customer c where c.location=?1", nativeQuery = false)
	  public List<Customer> getAllCustomersByLocation(String custLocation);
   
   	   //IndexedParameters
   	  @Query(value="from Customer c where c.location=?1 and c.billAmount > ?2", nativeQuery = false)
	  public List<Customer> getAllCustomersByLocation(String custLocation, float custbillAmount);
   	  
	  @Query(value="update ashokit_customers set location=:custLocation where customer_id=:custId",nativeQuery=true)
	  @Modifying
	  @Transactional
	  public int updateCustomerLocation(String custLocation,Integer custId);
	
}


Application.java
================
package com.ashokit;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.ashokit.dao.CustomerDao;
import com.ashokit.services.CustomerService;

@SpringBootApplication
public class Application implements CommandLineRunner {

	@Autowired
	public CustomerService customerService;
	
	@Autowired
	public CustomerDao customerDao;

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

	@Override
	public void run(String... args) throws Exception {
       
		System.out.println("1.....Selecting Data using Native Query....");
		customerService.fetchCustomerDetailsUsingNativeQuery();
		
		System.out.println();
		System.out.println("2.....Selecting Data Using HQL.......");
		customerService.fetchCustomerDetailsUsingHqlQuery();
		
		System.out.println();
		System.out.println("3.....Selecting Columns Data Using Native Query.......");
		customerService.fetchSelectedColumnsUsingNativeQuery();
		
		System.out.println();
		System.out.println("4.....Selecting Columns Data Using HQL Query.......");
		customerService.fetchSelectedColumnsUsingHqlQuery();
		
		System.out.println();
		System.out.println("5.....Selecting Data Using HQL Query Based on Location with NamedParameters.......");
		customerService.fetchCustomerDetailsByCity("Hyderabad1");
		
		System.out.println();
		System.out.println("6.....Selecting Data Using HQL Query Based on Location with Index.......");
		customerService.fetchCustomerDetailsByCity("Hyderabad2");
		
		System.out.println();
		System.out.println("7.....Updating the Customer Details based on CustomerId using @Query.......");
		customerDao.updateCustomerLocation("Hyderabad", 1);
	}
}


OUTPUT
======
  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v3.1.1)


1.....Selecting Data using Native Query....
Hibernate: select * from ashokit_customers
Customer [customerId=1, name=Mahesh, location=Hyderabad, billAmount=25000.0]
Customer [customerId=2, name=Suresh, location=Hyderabad2, billAmount=35000.0]
Customer [customerId=3, name=Rajesh, location=Hyderabad3, billAmount=25000.0]
Customer [customerId=4, name=Ramesh, location=Hyderabad4, billAmount=45000.0]
Customer [customerId=5, name=Nagesh, location=Hyderabad4, billAmount=55000.0]
Customer [customerId=6, name=Yagnesh, location=Hyderabad3, billAmount=65000.0]
Customer [customerId=7, name=Ganesh, location=Hyderabad3, billAmount=6500.0]
Customer [customerId=8, name=Naresh, location=Hyderabad2, billAmount=12000.0]
Customer [customerId=9, name=Rameshwar, location=Hyderabad2, billAmount=9000.0]
Customer [customerId=10, name=Anil, location=Hyderabad2, billAmount=2548.0]
Customer [customerId=11, name=Avinash, location=Hyderabad1, billAmount=7895.0]
Customer [customerId=12, name=Smith, location=Hyderabad1, billAmount=3215.0]
Customer [customerId=13, name=John, location=Hyderabad1, billAmount=5462.0]
Customer [customerId=14, name=Mark, location=Pune, billAmount=5000.0]
Customer [customerId=15, name=Bhoopal, location=Pune, billAmount=2561.0]
Customer [customerId=16, name=Bhoopal, location=Pune, billAmount=2561.0]
Customer [customerId=17, name=Mahesh, location=Hyderabad1, billAmount=25000.0]

2.....Selecting Data Using HQL.......
Hibernate: select c1_0.customer_id,c1_0.bill_amount,c1_0.location,c1_0.customer_name from ashokit_customers c1_0
Customer [customerId=1, name=Mahesh, location=Hyderabad, billAmount=25000.0]
Customer [customerId=2, name=Suresh, location=Hyderabad2, billAmount=35000.0]
Customer [customerId=3, name=Rajesh, location=Hyderabad3, billAmount=25000.0]
Customer [customerId=4, name=Ramesh, location=Hyderabad4, billAmount=45000.0]
Customer [customerId=5, name=Nagesh, location=Hyderabad4, billAmount=55000.0]
Customer [customerId=6, name=Yagnesh, location=Hyderabad3, billAmount=65000.0]
Customer [customerId=7, name=Ganesh, location=Hyderabad3, billAmount=6500.0]
Customer [customerId=8, name=Naresh, location=Hyderabad2, billAmount=12000.0]
Customer [customerId=9, name=Rameshwar, location=Hyderabad2, billAmount=9000.0]
Customer [customerId=10, name=Anil, location=Hyderabad2, billAmount=2548.0]
Customer [customerId=11, name=Avinash, location=Hyderabad1, billAmount=7895.0]
Customer [customerId=12, name=Smith, location=Hyderabad1, billAmount=3215.0]
Customer [customerId=13, name=John, location=Hyderabad1, billAmount=5462.0]
Customer [customerId=14, name=Mark, location=Pune, billAmount=5000.0]
Customer [customerId=15, name=Bhoopal, location=Pune, billAmount=2561.0]
Customer [customerId=16, name=Bhoopal, location=Pune, billAmount=2561.0]
Customer [customerId=17, name=Mahesh, location=Hyderabad1, billAmount=25000.0]

3.....Selecting Columns Data Using Native Query.......
Hibernate: select customer_name,location from ashokit_customers
Mahesh ---Hyderabad
Suresh ---Hyderabad2
Rajesh ---Hyderabad3
Ramesh ---Hyderabad4
Nagesh ---Hyderabad4
Yagnesh ---Hyderabad3
Ganesh ---Hyderabad3
Naresh ---Hyderabad2
Rameshwar ---Hyderabad2
Anil ---Hyderabad2
Avinash ---Hyderabad1
Smith ---Hyderabad1
John ---Hyderabad1
Mark ---Pune
Bhoopal ---Pune
Bhoopal ---Pune
Mahesh ---Hyderabad1

4.....Selecting Columns Data Using HQL Query.......
Hibernate: select c1_0.customer_name,c1_0.location from ashokit_customers c1_0
Mahesh ---Hyderabad
Suresh ---Hyderabad2
Rajesh ---Hyderabad3
Ramesh ---Hyderabad4
Nagesh ---Hyderabad4
Yagnesh ---Hyderabad3
Ganesh ---Hyderabad3
Naresh ---Hyderabad2
Rameshwar ---Hyderabad2
Anil ---Hyderabad2
Avinash ---Hyderabad1
Smith ---Hyderabad1
John ---Hyderabad1
Mark ---Pune
Bhoopal ---Pune
Bhoopal ---Pune
Mahesh ---Hyderabad1

5.....Selecting Data Using HQL Query Based on Location with NamedParameters.......
Hibernate: select c1_0.customer_id,c1_0.bill_amount,c1_0.location,c1_0.customer_name from ashokit_customers c1_0 where c1_0.location=?
Customer [customerId=11, name=Avinash, location=Hyderabad1, billAmount=7895.0]
Customer [customerId=12, name=Smith, location=Hyderabad1, billAmount=3215.0]
Customer [customerId=13, name=John, location=Hyderabad1, billAmount=5462.0]
Customer [customerId=17, name=Mahesh, location=Hyderabad1, billAmount=25000.0]

6.....Selecting Data Using HQL Query Based on Location with Index.......
Hibernate: select c1_0.customer_id,c1_0.bill_amount,c1_0.location,c1_0.customer_name from ashokit_customers c1_0 where c1_0.location=?
Customer [customerId=2, name=Suresh, location=Hyderabad2, billAmount=35000.0]
Customer [customerId=8, name=Naresh, location=Hyderabad2, billAmount=12000.0]
Customer [customerId=9, name=Rameshwar, location=Hyderabad2, billAmount=9000.0]
Customer [customerId=10, name=Anil, location=Hyderabad2, billAmount=2548.0]


* As of now we had seen executing the select queries using HQL & Native Queries and Spring Data JPA also provided the           flexibility to execute non-select queries(update,delete)

* Inorder to perform the non-select Operations in spring data JPA we need use to two Annotation i.e.,@Query and @Modifying

   public interface CustomerDao extends JPARepoistory{

      @Query(value="update ashokit_customers set customer_location=:custLocation where customer_id=:custId",                               nativeQuery=true)
      @Modifying
      @Transactional
      public int updateCustomerLocation(String custLocation,Integer custId);

      @Query("delete from Customer c where c.customerLocation=?1")
      @Modifying
      @Transactional
      public int deleteCustomersByLocation(String customerLocation);
 
   } 

NOTE
===
https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html