"Welcome To Ashok IT" "Spring Boot and MicroServices" Topic : Spring Data JPA - StoredProcedures Date : 21/10/2024 (Session - 50) _____________________________________________________________________________________________________________________________ Last Session ============ * So far we have completed below things in Spring Data JPA 1) Crud Respoistory & PagingAndSortingRepoistory & JPARepoistory interfaces 2) Working with finder method provided by Spring Data JPA 3) Working with Custom Queries(Select,Non-Select) using @Query annotation Today Session ============= * Stored Procedure is the one of Database object in Relation Databases. * As we have already familiar writing the business logic at Java layer using service components. * Sometimes we need to place the business logic at Database side in such situations we need to go for Stored Procedure (or) Stored Functions at Database Side. * Basically Stored Procedure contains collection of DML Statements (insert,update,delete) (or) DRL Statements(select). * The advantage of working stored procedure is passing precompiled queries(Queries which are compiled at Database side and its ready for execution for many times based on Demand) Ex == Statement st = con.createStatement(); int rowCount = st.executeUpdate("insert into ashokit_customers(01,'Mahesh','Hyderabad',250000)"); Observation =========== 1) The above query will send to Database Software for as many as no of times i.e.,1000 2) The above query will be parsed(Spliting) & Compiling the query for as many as no of times i.e.,1000 3) The above query will be executed with Same values or different values for as many as no of times i.e.,1000 PreparedStatement pstmt = con.prepareStatement(); int rowCount = pstmt.executeUpdate("insert into ashokit_customers values(?,?,?,?);"); Observation =========== 1) The above query will send to Database Software for as only time. 2) The above query will be parsed(Spliting) & Compiling the query for as one time. 3) The above query will be executed with Same values or different values for as many as no of times i.e.,1000 * We can execute the Stored Procedure & Stored Function using below approaches 1) By using CallableStatement in Simple Jdbc Application. 2) By using SimpleJdbcCall Object in Spring Framework. 3) By using Spring Data JPA as well we can execute the Stored procedure. Syntax For Creating Stored Procedure ==================================== create or replace procedure (Parameters) is/as begin end; / * We have three types of parameters are available in stored procedure 1) IN ---> Default parameter mode type and which can be used to pass the input to stored procedure i.e.,Read Only 2) OUT ---> It can be used to write data to this parameter and returning some value from stored procedure. 3) INOUT --> It can be used to accept input and returning output of the Stored procedure. ************************* Example Stored Procedure ************************* --creating the stored procedure by taking customerId as input and stored procedure as to --return customername create or replace procedure GET_CUSTOMER_NAME_BY_ID(custId in number,custName out varchar) is begin select customer_name into custName from ashokit_customers where customer_id = custId; end; --------------------------------------------------------------------------------------------------------- --creating the stored procedure by taking customerId as input and stored procedure as to --return customername,customerLocation create or replace procedure GET_CUSTOMER_INFO(custId in number,custName out varchar,custLocation out varchar) is begin select customer_name,location into custName,custLocation from ashokit_customers where customer_id=custId; end; --------------------------------------------------------------------------------------------------------- --Getting Customer Details by CustomerId create or replace Procedure GET_CUSTOMER_BY_ID(custId in number,Details out SYS_REFCURSOR) as Begin Open Details for select customer_id,customer_name,location,bill_amount from ashokit_customers where customer_id=custId; End; --------------------------------------------------------------------------------------------------------- --Getting All customers information create or replace Procedure GET_ALL_CUSTOMERS(Details Out Sys_Refcursor)As Begin Open Details for select customer_id,customer_name,customer_location,bill_amount from ashokit_customers; End; ---------------------------------------------------------------------------------------------------------- --Updating the BillAmount For Given CustomerId create or replace procedure updateBillAmountByCustomerId(customerId in number, billAmount in number, newBillAmount out number) is begin update ashokit_customers set bill_amount=billAmount where customer_id=customerId; commit; select bill_amount into newBillAmount from ashokit_customers where customer_id=customerId; end; ----------------------------------------------------------------------------------------------------------- Creating the Stored Procedure In Oracle SQL Developer ===================================================== 1) We already having table i.e..,ashokit_customers and we are creating stored procedure to get customername based on customerid. 2) Open the SQL developer and write the below code snippet -- creating the procedure create or replace procedure fetchCustomerDetailsById(custId in number, custName out varchar) is begin select customer_name into custName from ashokit_customers where customer_id=custId; end; 3) Compiling the Stored procedure (select all statement in Procedure and click on "Green Arrow" Button in SQL Developer Tool bar) 4) Executing the Stored Procedure(Navigate to Procedures folder under database connection >>>> select required procedure >>>>> Right click on procedure and choose "Run" option and supply input value to procedure (7002)) >>>> click on Ok Button 5) Observation on status bar we have one tab i.e.,output variables select it and observe the output of values. Assignment ========== 1) How to Work with Pagination & Sorting when we are using custom Query by using @Query annotation Refrences URL : https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html 2) Stored Procedure References ::::: https://www.oracletutorial.com/plsql-tutorial/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++