"Welcome To Ashok IT"				
			                  "Spring Boot and MicroServices"
                                            Topic  : Spring Data JPA - MySQL StoredProcedure
                                             Date  : 24/10/2024
                                                (Ses/sion - 52)
_____________________________________________________________________________________________________________________________
Yesterday Session
=================
* How to execute the Stored Procedure in Spring data JPA

     @NamedStoredProcedureQuery----> Registering the Stored Procedure 


     @NamedStoredProcedureQueries ------> Grouping of several @NamedStoredProcedureQuery.


     @StoredProcedureParameter ------> Registering the in and out parameters of the stored Procedure


     @Procedure  ---------------------> Executing the StoredProcedure through the DAO Method of Dao Repoistory Interface

     @Transactional ------------------> Whenever we are executing stored procedure defineltly required transaction 					management.


Today Session 
=============
Creating ashokit_customers table in mysql
=========================================
create table ashokit_customers(customer_id bigint,customer_name varchar(40),
			       customer_location varchar(50), bill_amount bigint);
                               
Inserting some sample data into ashokit_customers table
========================================================
insert into ashokit_customers values(1001,'Mahesh','Hyderabad',5000);
insert into ashokit_customers values(1002,'Suresh','Hyderabad',15000);   
insert into ashokit_customers values(1003,'Ramesh','Hyderabad',25000);   
insert into ashokit_customers values(1004,'Rajesh','Hyderabad',35000);   
insert into ashokit_customers values(1005,'Naresh','Hyderabad',45000);  

select * from ashokit_customers

OUTPUT
======
1001	Mahesh	Hyderabad	5000
1002	Suresh	Hyderabad	15000
1003	Ramesh	Hyderabad	25000
1004	Rajesh	Hyderabad	35000
1005	Naresh	Hyderabad	45000

Creating Stored Procedure in MySQL
==================================

Stored Procedures in MYSQL
==========================
-- changing the delimiter from ; to $$ (or) //
delimiter $$
create procedure GET_CUSTOMER_BY_ID(in custId bigint,out custName varchar(30))
begin
   select customer_name into custName from ashokit_customers where customer_id = custId;
end$$

-- changing the dlimiter to default values
delimiter ;

-- Executing the stored procedure
-- @customer_name session variable in mysql
-- Storing result of out variable in stored procedure
call GET_CUSTOMER_BY_ID(1,@customer_name);

-- selecting the value from session variable
select @customer_name;

----------------------------------------------------------------------------------------------------
delimiter //
create procedure GET_ALL_CUSTOMERS()
begin
  select * from ashokit_customers;
end//
delimiter ;

call GET_ALL_CUSTOMERS();

----------------------------------------------------------------------------------------------------
delimiter //
create procedure GET_CUSTOMER_COUNT()
begin
 select count(*) from ashokit_customers;
 end//
 delimiter ;
 
call GET_CUSTOMER_COUNT();

NOTE
====
MySql Information :https://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx

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

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

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

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "customer_id")
	private Integer customerId;

	@Column(name = "customer_name", nullable=false)
	private String customerName;

	@Column(name="customer_location",nullable = false)
	private String customerLocation;

	@Column(name = "bill_amount", nullable = false)
	private Integer billAmount;
	
	public Customer() {
		
	}
	
	public Customer(Integer customerId, String customerName, String customerLocation, Integer billAmount) {
		super();
		this.customerId = customerId;
		this.customerName = customerName;
		this.customerLocation = customerLocation;
		this.billAmount = billAmount;
	}
	
	public Customer(String customerName, String customerLocation, Integer billAmount) {
		this.customerName = customerName;
		this.customerLocation = customerLocation;
		this.billAmount = billAmount;
	}

	public Integer getCustomerId() {
		return customerId;
	}

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

	public String getCustomerName() {
		return customerName;
	}

	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}

	public String getCustomerLocation() {
		return customerLocation;
	}

	public void setCustomerLocation(String customerLocation) {
		this.customerLocation = customerLocation;
	}

	public Integer getBillAmount() {
		return billAmount;
	}

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

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

}


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

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import com.ashokit.entity.Customer;

public interface CustomerDao extends CrudRepository<Customer, Integer>{
	
	@Query(value = "call GET_ALL_CUSTOMERS()",nativeQuery = true)
	public List<Customer> fetchAllCustomers();
	
	@Query(value="call GET_CUSTOMER_COUNT()",nativeQuery = true)
	public Integer getCountOfCustomers();
	
	@Procedure(value ="GET_CUSTOMER_BY_ID",outputParameterName = "custName")
	//@Procedure(procedureName  ="GET_CUSTOMER_BY_ID")
	public String fetchCustomerName(@Param("custId")Integer customerId);
	
}


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

import java.util.List;

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

import com.ashokit.entity.Customer;

import jakarta.persistence.EntityManager;
import jakarta.persistence.ParameterMode;
import jakarta.persistence.StoredProcedureQuery;

@Service
public class CustomerService {
		
	@Autowired 
	private EntityManager entityManager;
	
	public void getCustomerById(Integer customerId) {
		
		//creating the StoredProcedureQuery Object
		StoredProcedureQuery query = entityManager.createStoredProcedureQuery("GET_CUSTOMER_BY_ID");
		
		//setting the in parameters and out parameters of stored procedure
		query.registerStoredProcedureParameter(1,Integer.class,ParameterMode.IN);
		query.registerStoredProcedureParameter(2, String.class,ParameterMode.OUT);
		
		//setting value into Input variable
		query.setParameter(1, customerId);
		
		String customerName = (String)query.getOutputParameterValue(2);
		System.out.println("CustomerName:::::" + customerName);
	}
	
	
   public void getAllCustomers() {
	   
	   StoredProcedureQuery query = entityManager.createStoredProcedureQuery("GET_ALL_CUSTOMERS",Customer.class);
	   	   
	   List<Customer> customerList = query.getResultList();
	   customerList.stream().forEach(cust -> System.out.println(cust));
   }
}


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

import java.util.Arrays;
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 org.springframework.data.util.Streamable;

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

@SpringBootApplication
public class Application implements CommandLineRunner {
	
	@Autowired
	private CustomerDao customerDao;
	
	@Autowired 
	private CustomerService customerService;

	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}
	
	@Override
	public void run(String... args) throws Exception {
		
		System.out.println("1. DAO Interface Method Call......");
	  	String customerName =customerDao.fetchCustomerName(1002);
		System.out.println("Customer Name ::::" + customerName);
		
		System.out.println("2.DAO Interface Method Call for Getting All Customers");
		List<Customer> allCustomers = customerDao.fetchAllCustomers();
		allCustomers.forEach(System.out::println);
		
		System.out.println("3.DAO Interface Method Call For Getting Count of Customers");
		Integer customerCount = customerDao.getCountOfCustomers();
		System.out.println("Customers Count :::::" + customerCount);
		
		System.out.println("4.Service Method call.........");
		customerService.getCustomerById(1002);
		
		System.out.println("5.Streamable Method call.......");
		Streamable<Customer> allcusts = customerDao.findByCustomerLocation("Hyderabad");
		allcusts.forEach(System.out::println);	
		
	}

}



OUTPUT
======

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


1. DAO Interface Method Call......
Hibernate: {call GET_CUSTOMER_BY_ID(?,?)}
Customer Name ::::Suresh

2.DAO Interface Method Call for Getting All Customers
Hibernate: call GET_ALL_CUSTOMERS()
Customer [customerId=1001, customerName=Mahesh, cusotmerLocation=Hyderabad, billAmount=5000]
Customer [customerId=1002, customerName=Suresh, cusotmerLocation=Hyderabad, billAmount=15000]
Customer [customerId=1003, customerName=Ramesh, cusotmerLocation=Hyderabad, billAmount=25000]
Customer [customerId=1004, customerName=Rajesh, cusotmerLocation=Hyderabad, billAmount=35000]
Customer [customerId=1005, customerName=Naresh, cusotmerLocation=Hyderabad, billAmount=45000]

3.DAO Interface Method Call For Getting Count of Customers
Hibernate: call GET_CUSTOMER_COUNT()
Customers Count :::::5

4.Service Method call.........
Hibernate: {call GET_CUSTOMER_BY_ID(?,?)}
CustomerName:::::Suresh

5.Streamable Method call.......
Hibernate: select c1_0.customer_id,c1_0.bill_amount,c1_0.customer_location,c1_0.customer_name from ashokit_customers c1_0 where c1_0.customer_location=?
Customer [customerId=1001, customerName=Mahesh, cusotmerLocation=Hyderabad, billAmount=5000]
Customer [customerId=1002, customerName=Suresh, cusotmerLocation=Hyderabad, billAmount=15000]
Customer [customerId=1003, customerName=Ramesh, cusotmerLocation=Hyderabad, billAmount=25000]
Customer [customerId=1004, customerName=Rajesh, cusotmerLocation=Hyderabad, billAmount=35000]
Customer [customerId=1005, customerName=Naresh, cusotmerLocation=Hyderabad, billAmount=45000]