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

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.


Create POJO Class

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

package com.javachinna.model;

import java.math.BigInteger;

import lombok.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

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;

public class UserInfoRepository {

	private SessionFactory sessionFactory;

	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();
		} catch (Exception e) {
			log.error("Exception occurred", e);
			if (transaction != null) {
		} finally {
		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 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;

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


Create JUnit Test Class

@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;

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

	private UserInfoRepository userInfoRepository;

	public void getUserInfoTest() {
		List<UserDTO> list = userInfoRepository.getUerInfo();

Run the test

Junit 5 test execution result

Source Code


That’s all folks. In this article, we have looked at how to map the SP resultset to POJO without an entity class using Hibernate.

Please share the article with your friends if you like it. Thank you for reading.

Leave a Reply