Call Stored Procedures using Spring Boot Hibernate | Code Factory
4 min readApr 25, 2020
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><properties>
<java.version>1.8</java.version>
</properties><dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency><dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency><dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-dbcp</artifactId>
<version>9.0.1</version>
</dependency><dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies><build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build></project>
application.properties
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=traceSpringBootCallStoredProceduresHibernateApplication.java
package com.codeFactory;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;/**
* @author code.factory
*
*/
@SpringBootApplication
public class SpringBootCallStoredProceduresHibernateApplication {public static void main(String[] args) {
SpringApplication.run(SpringBootCallStoredProceduresHibernateApplication.class, args);
}}
DBConfiguration.java
package com.codeFactory.configuration;import java.util.Properties;import javax.sql.DataSource;import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.hibernate5.HibernateTransactionManager;
import org.springframework.orm.hibernate5.LocalSessionFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;/**
* @author code.factory
*
*/
@Configuration
@EnableTransactionManagement
public class DBConfiguration {@Bean
public LocalSessionFactoryBean sessionFactory() {
LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
sessionFactory.setDataSource(dataSource());
sessionFactory.setPackagesToScan("com.codeFactory");
sessionFactory.setHibernateProperties(hibernateProperties());
return sessionFactory;
}@Bean
public DataSource dataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}@Bean
public PlatformTransactionManager hibernateTransactionManager() {
HibernateTransactionManager transactionManager = new HibernateTransactionManager();
transactionManager.setSessionFactory(sessionFactory().getObject());
return transactionManager;
}private final Properties hibernateProperties() {
Properties hibernateProperties = new Properties();
hibernateProperties.setProperty("hibernate.hbm2ddl.auto", "update");
hibernateProperties.setProperty("hibernate.dialect",
"org.hibernate.dialect.MySQLDialect");
return hibernateProperties;
}
}
Controller.java
package com.codeFactory.controller;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;import com.codeFactory.model.Employee;
import com.codeFactory.service.EmployeeService;/**
* @author code.factory
*
*/
@RestController
public class Controller {@Autowired
EmployeeService employeeService;@GetMapping("namedNativeQuery/{id}")
public Employee namedNativeQuery(@PathVariable("id") Integer id) {
return employeeService.namedNativeQuery(id);
}@GetMapping("namedStoredProcedureQuery/{id}")
public Employee namedStoredProcedureQuery(@PathVariable("id") Integer id) {
return employeeService.namedStoredProcedureQuery(id);
}
}
Employee.java
package com.codeFactory.model;import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.Table;/**
* @author code.factory
*
*/
@Entity
@Table(name = "employee")
@NamedNativeQueries({
@NamedNativeQuery(
name = "findEmpById",
query = "CALL FIND_EMP_BY_ID(:p_id)",
resultClass = Employee.class
)
})
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(
name = "findEmpById",
procedureName = "FIND_EMP_BY_ID",
resultClasses = {Employee.class},
parameters = {
@StoredProcedureParameter(
name = "p_id",
type = Integer.class,
mode = ParameterMode.IN
)
}
)
})
public class Employee {@Id
@GeneratedValue(strategy = GenerationType.AUTO)
int id;
String name;
long salary;public Employee() {
}
public Employee(int id, String name, long salary) {
this.id = id;
this.name = name;
this.salary = salary;
}
public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public String getName() {
return name;
}public void setName(String name) {
this.name = name;
}public long getSalary() {
return salary;
}public void setSalary(long salary) {
this.salary = salary;
}@Override
public String toString() {
return "id=" + id + ", name=" + name + ", salary=" + salary;
}
}
EmployeeService.java
package com.codeFactory.service;import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import com.codeFactory.model.Employee;/**
* @author code.factory
*
*/
@Service
public class EmployeeService {@Autowired
SessionFactory sessionFactory;
@PersistenceContext
EntityManager em;
public Employee namedNativeQuery(int id) {
Session session = sessionFactory.openSession();
Query query = session.getNamedQuery("findEmpById").setParameter("p_id", id);
return (Employee) query.list().get(0);
}public Employee namedStoredProcedureQuery(int id) {
StoredProcedureQuery query = em.createNamedStoredProcedureQuery("findEmpById").setParameter("p_id", id);
return (Employee) query.getResultList().get(0);
}
}
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;
ENDHit 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]