"Welcome To Ashok IT"				
			                  "Spring Boot and MicroServices"
                                            Topic : Spring Data JPA - Paging & Sorting
                                             Date  : 19/04/2025
                                                (Session - 31)
_____________________________________________________________________________________________________________________________
JPARepoistory
=============
* As part of Spring Data JPA Module we have another important Repoistory interface i.e.,JPARepoistory.

* JPARepoistory Interface is a child interface of ListPagingAndSortingRepoistory & ListCrudRepoisotry &                  QueryByExampleExecutor Interfaces.
 
* JPARepoistory Interface contains 12 abstract Methods

* JPARepoistory  = CurdRepoistory Interface Methods + PagingAndSortingRepoistory Interface Methods  + QueryByExampleExecutor

* Most of the methods are available in JPA Repoistory are also available in CrudRepoistory interface and                    PagingAndSortingRepository Interface but they are work in Underlying JPA Implementation.

* For Example in our application DAO Interface extending services from JPARepoistory Interface it will supports the Database    Operations for only SQL Database.

Differences Between CrudRepoistory and JPARepoistory
====================================================
CrudRepoistory
==============
1) SaveAll(),findAll() Methods return type is Iterable object

2) CrudRepoistory Methods doesn't have support of Example Object.

3) findById() return type is optional Object i.e,Optional<T> and will throw Custom Exception if id is not available

4) CrudRepoistory deleteXXX() will not support Batch Deletion because when we working these methods for each id (or) entity      will have seperate delete statement.
   
Example
=======
Hibernate: delete from ashokit_customers where customer_id=?
Hibernate: delete from ashokit_customers where customer_id=?
Hibernate: delete from ashokit_customers where customer_id=?

5) These methods are common for both SQL Databases and NOSQL Databases.

JPARepoistory
=============
1) SaveAll(),findAll() methods return type is List Object

2) JPARepoistory Methods having support of Example Object

3) getById() return type is Object i.e.,<T> and it will throw EntityNotFoundException supplied id is not available

4) JPARepoistory deleteXXX() will support for Batch Deletion for all entities (or) given ids will be deleted with the help       single delete statement.

Example
=======
Hibernate: delete from ashokit_customers c1_0 where c1_0.customer_id in (?,?,?)

5) These methods are specific to SQL Database only.

JpaRepoistory Methods
=====================
1) void deleteAllByIdInBatch(Iterable ids) 
==========================================
* This method is used to perform bulk deletion (or) batch deletion by taking primary key column values of the Database table.

* This Methods won't throws an error If the given id record is not existed in the Database table.

* If we remember when we are working deleteAllById(Iterable ids) from CrudRepoistory Interfaces if the given id record is not   existed in the database table it will throw an error.

* This method can be used only for SQL Database Softwares.

2) List<T> findAll(Example<S> example)
======================================
* This Method is used to return List of object by given Example object based entity object.

* Example is an predefined Interfaces from Spring Data JPA Module.

* Example Object is JPA Supplied Object containing other object and which can works like Optional class from Java8 Version.

        Optional opt = Optional.of(customer);   >>>> opt.isPresent()   >>>>> opt.get()        
   
        Customer c = new Customer();
        c.setCustomerLocation(null);
       
        Example example = Example.of(c);

        customerDao.findAll(example); >>> Retreive the all records for table based on customerName is non null value and                                                simply example object is taking entity object and avoiding null values properties                                             in entity object.
 
* When we pass an entity object to the Example object then entity object allows only non null data will be supplied to          Example Object

3) <T> saveAndFlush(entity)
===========================
* This method is used to save the given entity into database.

* If we are working with CrudRepoistory we used save(entity) method for saving the given entity but where as in JPARepository   need to use this method only.

* Flushing is the process of synchronizing the state of the persistence context with the underlying database.

* When using saveAndFlush() data immediately flush to the database and to do it with the save method we need to call            flush() method explicitly.

4) T getReferenceById(Id)
=========================
* This Method is used to get the entity object based on given primary key.

* When we working with CrudRepoistory for this equivalent method we have Optional findById(ID id) here programmer can have      option to throw custom Exception because if given id is not availble in database table.

                Optional<Customer> cust =  customerDao.findById(123);

                if(cust.isPresent()){
                    Customer c = cust.get();     
                }else{
                    throw new IllegalArgumentException("Given Id is not existed");
                   // throw new ResourceNotFoundException("Given ID is not existed");
                }

* When we are working with this method we do not have option to throw userdefined exception because it already throwing         EntityNotFoundException if given id is not existed database.

* These are the interfaces we have completed so far

   1. CrudRepository

   2. PagingAndSortingRepository

   3. ListCrudRepository

   4. ListPagingAndSortingRepository

   5. JpaRepository

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.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++