Call Stored Procedures using Spring Boot Hibernate | Code Factory


Donate : Link

Medium Blog : Link

Applications : 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=trace

SpringBootCallStoredProceduresHibernateApplication.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;
END
This image has an empty alt attribute; its file name is image-1.png

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]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s