"Welcome To Ashok IT"				
			                  "Spring Boot and MicroServices"
                                            Topic : Spring Data JPA - Finder Methods
                                             Date  : 19/10/2024
                                                (Session - 48)
_____________________________________________________________________________________________________________________________
Last Session
============
* We Completed working with JPA Repoistory interface methods 

* These are the interfaces we have completed so far

   1. CrudRepository

   2. PagingAndSortingRepository

   3. ListCrudRepository

   4. ListPagingAndSortingRepository

   5. JpaRepository   

Today Session: Finder Methods
=============================
* Earlier we worked with some finder methods from CrudRepoistory interface & JPARepoistory Interface

  CrudRepoistory Interface
  ========================
     1) findById(ID id)

     2) findAllById(Iterable<ID> ids)

     3) findAll()

  JPARepoistory Interface
  =======================
     1) getById(ID id)
   
     2) getOne(ID id)

     3) getReferencesById(ID id)

* If we observe above all methods are depending on primary key column values means based on primary key column value will get   the records from Database table  

* If we wanted to retreive the records from Database table based on non-primary key column definetly will go for Finder         methods in Spring Data JPA.

* Finder methods in Spring Data JPA are most powerful Methods which are used for selecting records from Database table based    on non-primary key column with out writing any SQL Queries explictly. 

* Inorder to create the finder methods in Spring Data JPA the programmer need to follow some naming convention to define the    finder method in user defined repoistory interface(CustomerDao).

* Every Finder method in user defined repoistory interface must an should start with "findBy" word and followed by fieldName    in the entity class.

     Example : findByName(string name), findByLocation(String location), findByBillAmount(float billAmount)

               findByNameAndLocation(String name,String location), findByNameOrLocation(String name,String location)

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

     @Id
     @GeneratedValue
     private Integer customerId;

     private String name;

     private String location;

     private float billAmount;

  }

*  To create a finder method for name,location fields in CustomerDao Repository Interface as below

   public interface CustomerDao extends CrudRepoistory/JPARepoistory<Customer,Integer> {
         
      public Iterable<Customer> findByName(String name);

      public Iterable<Customer> findByLocation(String location);

      public Page<Customer> findByLocation(String location,Pageable pageable);

      public List<Customer> findByLocation(String location, Sort sort);

      public Iterable<Customer> findByNameAndLocation(String name,String location);

      public Integer countByName(String name);

      public Integer countByNameAndLocation(String name,String location);

      public List<Customer> findByBillAmountLessThan(float billAmount);

      public List<Customer> findByBillAmountGreaterThanEqual(float billAmount);

      public List<Customer> findByBillAmountBetween(float startAmount,float endAmount);

   }

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 + "]";
	}

}


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

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

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.CrudRepository;

import com.ashokit.entity.Customer;

public interface CustomerDao extends  CrudRepository<Customer, Serializable>{

	public Iterable<Customer> findByName(String name);
	
	public Iterable<Customer> findByLocation(String location);
	
	public Page<Customer> findByLocation(String location,Pageable pageable);
	
	public Iterable<Customer> findByNameAndLocation(String name,String location);
	
	public List<Customer> findByNameAndLocation(String name,String location,Sort sort);
	
	public Integer countByName(String name);
	
	public Iterable<Customer> findByBillAmountLessThan(float billAmount);
	
	public Iterable<Customer> findByBillAmountGreaterThanEqual(float billAmount);
	
	public Iterable<Customer> findByBillAmountBetween(float startBillAmount,float endBillAmount);
	
}


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

public interface CustomerService {
	
	public void fetchCustomersByName(String name);
	
	public void fetchCustomersByNameAndLocation(String name ,String location);
	
	public void fetchCustomersByLocationWithPagination(String location,int pageSize,int currentPage);
	
	public void getCountOfCustomerName(String name);
	
	public void fetchCustomersBetweenBillAmount(float stBillAmount,float endBillAmount);

}


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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
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 fetchCustomersByName(String name) {
		Iterable<Customer> allCustomers = customerDao.findByName(name);
		allCustomers.forEach(System.out::println);
	}

	@Override
	public void fetchCustomersByNameAndLocation(String name, String location) {
		Iterable<Customer> allCustomers = customerDao.findByNameAndLocation(name, location);
		allCustomers.forEach(System.out::println);
	}

	@Override
	public void fetchCustomersByLocationWithPagination(String location, int pageSize, int currentPage) {

		PageRequest pageable = PageRequest.of(currentPage, pageSize);
		Page<Customer> allCustomers = customerDao.findByLocation(location, pageable);
		allCustomers.forEach(System.out::println);
	}

	@Override
	public void getCountOfCustomerName(String name) {
		System.out.println("Customer Count By Name ::::" + customerDao.countByName(name));
	}

	@Override
	public void fetchCustomersBetweenBillAmount(float stBillAmount, float endBillAmount) {
		Iterable<Customer> allCustomers = customerDao.findByBillAmountBetween(stBillAmount, endBillAmount);
		allCustomers.forEach(System.out::println);
	}

}


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

import java.util.List;

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.entity.Customer;
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 {
         //Creating the customers
	    //customerDao.saveAll(getCustomersData());
	    
	    //Fetching the customers
		System.out.println("1...... Finding the Customers Based on Name");
		customerService.fetchCustomersByName("Mahesh");
		System.out.println("2...... Finding the Customers Based on Location");
		customerService.fetchCustomersByNameAndLocation("Mahesh", "Hyderabad");
		System.out.println("3...... Finding the Customers Based on Location");
		customerService.fetchCustomersByLocationWithPagination("Hyderabad1",2,1);
		System.out.println("4...... Finding the Customers Count Based On CustomerName");
		customerService.getCountOfCustomerName("Mahesh");
		System.out.println("5...... Finding the Customers Based On BillAmount");
		customerService.fetchCustomersBetweenBillAmount(1000,5000);

		

	}

	public List<Customer> getCustomersData() {

		Customer customer1 = new Customer("Mahesh", "Hyderabad1", 25000);
		Customer customer2 = new Customer("Suresh", "Hyderabad2", 35000);
		Customer customer3 = new Customer("Rajesh", "Hyderabad3", 25000);
		Customer customer4 = new Customer("Ramesh", "Hyderabad4", 45000);
		Customer customer5 = new Customer("Nagesh", "Hyderabad4", 55000);
		Customer customer6 = new Customer("Yagnesh", "Hyderabad3", 65000);
		Customer customer7 = new Customer("Ganesh", "Hyderabad3", 6500);
		Customer customer8 = new Customer("Naresh", "Hyderabad2", 12000);
		Customer customer9 = new Customer("Rameshwar", "Hyderabad2", 9000);
		Customer customer10 = new Customer("Anil", "Hyderabad2", 2548);
		Customer customer11 = new Customer("Avinash", "Hyderabad1", 7895);
		Customer customer12 = new Customer("Smith", "Hyderabad1", 3215);
		Customer customer13 = new Customer("John", "Hyderabad1", 5462);
		Customer customer14 = new Customer("Mark", "Pune", 5000);
		Customer customer15 = new Customer("Bhoopal", "Pune", 2561);
		Customer customer16 = new Customer("Bhoopal", "Pune", 2561);
		Customer customer17 = new Customer("Mahesh", "Hyderabad1", 25000);

		// Java9 Style for Creating Immutable Collection
		List<Customer> customers = List.of(customer1, customer2, customer3, customer4, customer5, customer6, customer7,
				customer8, customer9, customer10, customer11, customer12, customer13, customer14, customer15,
				customer16,customer17);

		return customers;
	}

}


application.properties
======================
#configuring the Database properties
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=system
spring.datasource.password=manager

#Hibernate Configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

OUTPUT
======

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/

1...... Finding the Customers Based on Name
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.customer_name=?
Customer [customerId=1, name=Mahesh, location=Hyderabad1, billAmount=25000.0]
Customer [customerId=17, name=Mahesh, location=Hyderabad1, billAmount=25000.0]

2...... Finding the Customers Based on Location
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.customer_name=? and c1_0.location=?
Customer [customerId=1, name=Mahesh, location=Hyderabad1, billAmount=25000.0]
Customer [customerId=17, name=Mahesh, location=Hyderabad1, billAmount=25000.0]

3...... Finding the Customers Based on Location
Hibernate: select * from (select c1_0.customer_id c0,c1_0.bill_amount c1,c1_0.location c2,c1_0.customer_name c3,rownum rn from ashokit_customers c1_0 where c1_0.location=?) r_0_ where r_0_.rn<=?+? and r_0_.rn>? order by r_0_.rn
Hibernate: select count(c1_0.customer_id) from ashokit_customers c1_0 where c1_0.location=?
Customer [customerId=12, name=Smith, location=Hyderabad1, billAmount=3215.0]
Customer [customerId=13, name=John, location=Hyderabad1, billAmount=5462.0]

4...... Finding the Customers Count Based On CustomerName
Hibernate: select count(c1_0.customer_id) from ashokit_customers c1_0 where c1_0.customer_name=?
Customer Count By Name ::::2

5...... Finding the Customers Based On BillAmount
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.bill_amount between ? and ?
Customer [customerId=10, name=Anil, location=Hyderabad2, billAmount=2548.0]
Customer [customerId=12, name=Smith, location=Hyderabad1, billAmount=3215.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]


* If we observe carefully for the above interface Spring Data JPA will internally create proxy class (or) In-Memory Class and   also Spring Data JPA will provide the implmentation for the above finder methods of Repoistory Interface.

Assignment
==========
* Implement the Remaining finder method through Service Class and test it....
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/docs/current/reference/html/#repositories