Call Stored Procedure and Map the Native Query Result to POJO with Hibernate

Post author:Chinna
Post published:September 22, 2020
Post category:Hibernate / Spring
Post comments:0 Comments

0 Shares

In the previous article, we have learned how to call the PL/SQL stored procedure and map the resultset to POJO without an entity class using JPA SqlResultSetMapping.

In this article, we are going to call a PL/SQL stored procedure and map result list to POJO using Hibernate ResultTransformer without using JPA.

We are going to use the SpringHibernateDemo application that we have created in this article.

Let’s Get Started

Create Stored Procedure

We are going to create a stored procedure to query the user table that we have created earlier.

DELIMITER $$
CREATE PROCEDURE `SP_USER_INFO`()
BEGIN
    SELECT USER_ID AS id, DISPLAY_NAME AS name, EMAIL as email FROM USER;
END$$
DELIMITER ;

Create POJO Class

Create a POJO with field names matching the alias names used in the native SQL query.

UserDTO.java

package com.javachinna.model;

import java.math.BigInteger;

import lombok.Data;

@Data
public class UserDTO {
	private BigInteger id;
	private String name;
	private String email;
}

Note: We have declared the id field as BigInteger since Hibernate returns integer value as BigInteger.

Create Repository

UserInfoRepository.java

package com.javachinna.repo;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.query.Query;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.javachinna.model.UserDTO;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Repository
@Transactional
public class UserInfoRepository {

	@Autowired
	private SessionFactory sessionFactory;

	@SuppressWarnings("unchecked")
	public List<UserDTO> getUerInfo() {
		Session session = null;
		Transaction transaction = null;
		List<UserDTO> list = null;
		try {
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
			Query query = session.createNativeQuery("{call SP_USER_INFO()}").setResultTransformer(Transformers.aliasToBean(UserDTO.class));
			list = query.getResultList();
			transaction.commit();
		} catch (Exception e) {
			log.error("Exception occurred", e);
			if (transaction != null) {
				transaction.rollback();
			}
		} finally {
			session.close();
		}
		return list;
	}
}

We have used Hibernate AliasToBeanResultTransformer that allows us to transform a result to a user-specified class which will be populated via setter methods or fields matching the alias names used in the native SQL query of Stored Procedure.

Note: setResultTransformer is deprecated in Hibernate 5.2 and there is no alternative to use instead. It was deprecated too soon in order to introduce @FunctionInterface as an alternative in Hibernate 6. Hence, we have to use the deprecated method till the migration to Hibernate 6 once it is released.

Create JUnit Test

Create Test Configuration

TestConfig.java

TestConfig is a Spring configuration class that specifies the base packages to scan for annotated components.

package com.javachinna.test;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@ComponentScan(basePackages = { "com.javachinna" })
public class TestConfig {

}

Create JUnit Test Class

DemoApplicationTests.java

@ExtendWith annotation is used to register SpringExtension for the annotated test class or test method.

SpringExtension integrates the Spring TestContext Framework into JUnit 5’s Jupiter programming model.

@ContextConfiguration defines class-level metadata that is used to determine how to load and configure an ApplicationContext for integration tests

@Test annotation signals that the annotated method is a test method.

package com.javachinna.test;

import java.util.List;

import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import com.javachinna.model.UserDTO;
import com.javachinna.repo.UserInfoRepository;

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = { TestConfig.class })
public class DemoApplicationTests {

	@Autowired
	private UserInfoRepository userInfoRepository;

	@Test
	public void getUserInfoTest() {
		List<UserDTO> list = userInfoRepository.getUerInfo();
		Assertions.assertNotNull(list);
	}
}