Call Stored Procedures using Spring Boot Hibernate | Code Factory

Reference Link : Link

Donate : Link

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.codeFactory</groupId>
<artifactId>spring-boot-call-stored-procedures-hibernate</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-boot-call-stored-procedures-hibernate</name>
<description>Demo project for Spring Boot Call Stored Procedure with Hibernate</description>

application.properties

logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=trace

SpringBootCallStoredProceduresHibernateApplication.java

package com.codeFactory;

DBConfiguration.java

package com.codeFactory.configuration;

Controller.java

package com.codeFactory.controller;

Employee.java

package com.codeFactory.model;

EmployeeService.java

package com.codeFactory.service;

Create Table 'employee' :

CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`salary` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
);

Procedure 'FIND_EMP_BY_ID' :

CREATE DEFINER=`root`@`localhost` PROCEDURE `FIND_EMP_BY_ID`(in p_id int)
BEGIN
SELECT id, name, salary
FROM employee
WHERE id = p_id;
END

Hit URL : http://localhost:8080/namedNativeQuery/1

Hit URL : http://localhost:8080/namedStoredProcedureQuery/2

Console :

2019-11-04 22:19:15.986 DEBUG 10416 --- [nio-8080-exec-1] org.hibernate.SQL                        : CALL FIND_EMP_BY_ID(?)
2019-11-04 22:19:15.996 TRACE 10416 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [INTEGER] - [1]
2019-11-04 22:19:16.024 TRACE 10416 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicExtractor : extracted value ([id] : [INTEGER]) - [1]
2019-11-04 22:19:16.035 TRACE 10416 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicExtractor : extracted value ([name] : [VARCHAR]) - [emp1]
2019-11-04 22:19:16.039 TRACE 10416 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicExtractor : extracted value ([salary] : [BIGINT]) - [1000]
2019-11-04 22:21:42.539 DEBUG 10416 --- [nio-8080-exec-4] org.hibernate.SQL : {call FIND_EMP_BY_ID(?)}
2019-11-04 22:21:42.622 TRACE 10416 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [INTEGER] - [2]
2019-11-04 22:21:42.637 TRACE 10416 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicExtractor : extracted value ([id] : [INTEGER]) - [2]
2019-11-04 22:21:42.637 TRACE 10416 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicExtractor : extracted value ([name] : [VARCHAR]) - [emp2]
2019-11-04 22:21:42.637 TRACE 10416 --- [nio-8080-exec-4] o.h.type.descriptor.sql.BasicExtractor : extracted value ([salary] : [BIGINT]) - [2000]