"Welcome To Ashok IT" "Spring Boot and MicroServices" Topic : Spring Data JPA - Stored Procedures Date : 23/04/2025 (Session - 33) _____________________________________________________________________________________________________________________________ Stored Procedure ================ * 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) insert into ashokit_customers values(?1,?2,?3); 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 one 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 * 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. 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.NamedStoredProcedureQueries; import jakarta.persistence.NamedStoredProcedureQuery; import jakarta.persistence.ParameterMode; import jakarta.persistence.StoredProcedureParameter; import jakarta.persistence.Table; @Entity @Table(name = "ashokit_customers") @NamedStoredProcedureQueries({ @NamedStoredProcedureQuery(name="fetchCustomerName", procedureName="GET_CUSTOMER_NAME_BY_ID", parameters = { @StoredProcedureParameter(name="custId",mode = ParameterMode.IN,type = Integer.class), @StoredProcedureParameter(name="custName",mode = ParameterMode.OUT,type=String.class) }), @NamedStoredProcedureQuery(name = "customerInfoDetails", procedureName ="GET_CUSTOMER_INFO", parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "custId", type = Integer.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "custName", type = String.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "custLocation", type = String.class) }), @NamedStoredProcedureQuery(name="fetchCustomerDetails", procedureName="GET_CUSTOMER_BY_ID", resultClasses = Customer.class, parameters = { @StoredProcedureParameter(mode = ParameterMode.IN,name = "custId",type = Integer.class), @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR,name = "Details",type=void.class) }), @NamedStoredProcedureQuery(name="fetchAllCustomerDetails", procedureName="GET_ALL_CUSTOMERS", resultClasses = Customer.class, parameters = { @StoredProcedureParameter(name="Details",type=void.class,mode=ParameterMode.REF_CURSOR) }) }) public class Customer { @Id @Column(name = "customer_id") @GeneratedValue(strategy = GenerationType.AUTO) //ORM will use hibernate_sequence private Integer customerId; @Column(name = "customer_name") private String customerName; @Column(name = "location") private String customerLocation; @Column(name="bill_amount") private Integer 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 void setBillAmount(Integer billAmount) { this.billAmount = billAmount; } public Integer getBillAmount() { return billAmount; } @Override public String toString() { return "Customer [customerId=" + customerId + ", customerName=" + customerName + ", customerLocation=" + customerLocation + ", billAmount=" + billAmount + "]"; } } CustomerDao.java ================ package com.ashokit.dao; import java.util.List; import java.util.Map; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.query.Procedure; import com.ashokit.entity.Customer; public interface CustomerDao extends JpaRepository{ @Procedure(name = "fetchCustomerName") public String fetchCustomerNameById(Integer custId); @Procedure(name="customerInfoDetails") public Map fetchCustomerDetailsById(Integer custId); @Procedure(name="fetchCustomerDetails") public Customer fetchCustomerDetailsByCustomerId(Integer custId); @Procedure(name="fetchAllCustomerDetails") public List getAllCustomers(); } CustomerService.java ==================== package com.ashokit.service; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.ashokit.dao.CustomerDao; import com.ashokit.entity.Customer; @Service public class CustomerService { @Autowired private CustomerDao customerDao; @Transactional public void getCustomerNameByCustomerId(int customerId) { String customerName = customerDao.fetchCustomerNameById(customerId); System.out.println("CustomerName ::::" + customerName); } @Transactional public void getCustomerDetailsInfo(int customerId) { Map customerDetails = customerDao.fetchCustomerDetailsById(customerId); System.out.println("CustomerDetails Info ::::" + customerDetails); } @Transactional public void getCustomerDetailsById(int customerId) { Customer customerDetails = customerDao.fetchCustomerDetailsByCustomerId(customerId); System.out.println("Customer Details:::::"+customerDetails); } @Transactional public void getAllCustomers() { List customerDetails = customerDao.getAllCustomers(); customerDetails.stream().forEach(cust -> System.out.println(cust)); } } Application ============ 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.service.CustomerService; @SpringBootApplication public class Application implements CommandLineRunner{ @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. Executing Stored Procedure with one input and one output...."); customerService.getCustomerNameByCustomerId(1358); System.out.println(); System.out.println("2. Executing Stored Procedure with one input and output as Map Object...."); customerService.getCustomerDetailsInfo(1358); System.out.println(); System.out.println("3. Executing Stored Procedure with one input and object as output...."); customerService.getCustomerDetailsById(1358); System.out.println(); System.out.println("4. Executing Stored Procedure with object as output List Of Customers...."); customerService.getAllCustomers(); } } application.yml =============== spring: datasource: driver-class-name: oracle.jdbc.OracleDriver password: manager url: jdbc:oracle:thin:@localhost:1521:xe username: system jpa: hibernate: ddl-auto: update show-sql: true Output ====== 1. Executing Stored Procedure with one input and one output.... Hibernate: {call GET_CUSTOMER_NAME_BY_ID(?,?)} CustomerName ::::Ganesh 2. Executing Stored Procedure with one input and output as Map Object.... Hibernate: {call GET_CUSTOMER_INFO(?,?,?)} CustomerDetails Info ::::{custName=Ganesh, custLocation=Hyderabad3} 3. Executing Stored Procedure with one input and object as output.... Hibernate: {call GET_CUSTOMER_BY_ID(?,?)} Customer Details:::::Customer [customerId=1358, customerName=Ganesh, customerLocation=Hyderabad3, billAmount=5000] 4. Executing Stored Procedure with object as output List Of Customers.... Hibernate: {call GET_ALL_CUSTOMERS(?)} Customer [customerId=1352, customerName=Mahesh, customerLocation=Hyderabad1, billAmount=5000] Customer [customerId=1353, customerName=Suresh, customerLocation=Hyderabad2, billAmount=5000] Customer [customerId=1354, customerName=Rajesh, customerLocation=Orissa, billAmount=5000] Customer [customerId=1355, customerName=Ramesh, customerLocation=Hyderabad4, billAmount=5000] Customer [customerId=1356, customerName=Nagesh, customerLocation=Hyderabad4, billAmount=5000] Customer [customerId=1357, customerName=Yagnesh, customerLocation=Hyderabad3, billAmount=5000] Customer [customerId=1358, customerName=Ganesh, customerLocation=Hyderabad3, billAmount=5000] Customer [customerId=1359, customerName=Naresh, customerLocation=Hyderabad2, billAmount=5000] Customer [customerId=1360, customerName=Rameshwar, customerLocation=Hyderabad2, billAmount=5000] Customer [customerId=1361, customerName=Anil, customerLocation=Hyderabad2, billAmount=5000] Customer [customerId=1362, customerName=Avinash, customerLocation=Hyderabad1, billAmount=5000] Customer [customerId=1363, customerName=Smith, customerLocation=Hyderabad1, billAmount=5000] Customer [customerId=1364, customerName=John, customerLocation=Hyderabad1, billAmount=5000] Customer [customerId=1368, customerName=Mahesh, customerLocation=Hyderabad1, billAmount=5000] MySQL Stored procedures ======================== 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 delimiter 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(); ------------------------------------------------------------------------------------------------------- Quick References - https://www.mysqltutorial.org/mysql-stored-procedure/mysql-delimiter/ Quick References - https://www.oracletutorial.com/plsql-tutorial/plsql-procedure/ Working with No SQL Database ============================ * By using Spring Data Module we can communicate with any Database software. * By using Spring Data JPA module we can communicate with any RDBMS Database Software(Oracle,MySQL,Postgresql,SQLServer etc.,) * Inorder to communicate with No SQL Database's in Spring Data Module has given seperate modules for each NO SQL Database. Mongo DB >>>>>>>>>>>> Spring Data MongoDB Cassendar >>>>>>>>>>>> Spring Data for Apache Cassendar Neo4J >>>>>>>>>>>> Spring Data for Neo4j Redis >>>>>>>>>>>> Spring Data for Redis * In RDBMS Database softwares always data will be stored in the form of "Tables(Rows & Columns)". In No-SQL Database softwares the data will be stored in the form of Key-value based,document based,graph based. * If data having fixed format and schema then we need go for SQL Databases. If data is dynamically growing and no fixed format then we need go for No-SQL Databases. Mongo DB ======== * Mongo DB is an open source and document orientied no-sql database software. * Mongo DB Internally stores the data in the form of JSON i.e.,"documents". * JSON >>>>> Javascript Object Notation * JSON is format defining text-data as key-value pairs. * JSON is an alternative for XML. * XML based approach required more decoration for elements when compared to JSON data. Example ======= Customer c = new Customer(); c.setCustomerId(123); c.setCustomerName("Mahesh"); c.setCustomerLocation("Hyderabad"); XML Format ========== 123 Mahesh Hyderabad JSON Format =========== { "customerId" : 123, "customerName" : "mahesh", "customerLocation" : "Hyderabad" } Multiple Customers ================== 123 Mahesh Hyderabad 456 Ashok Hyderabad Multiple Customers ================== [ { "customerId" : 123, "customerName" : "mahesh", "customerLocation" : "Hyderabad" }, { "customerId" : 456, "customerName" : "Ashok", "customerLocation" : "Hyderabad" } ]