Calling Stored Procedure and mapping the query result to non-entity POJO using Spring JPA SqlResultSetMapping

Post author:Chinna
Post published:October 26, 2019
Post category:Spring Boot
Post comments:62 Comments

1 Shares

Recently, I gotta chance to work with a microservice in which I had to call a Stored Procedure and map the results to a POJO. By default the StoredProcedureQuery returns result as List<Object[]> and this has to be mapped to the POJO based on the index manually as shown below in the code snippet

			query.execute();
			List<Object[]> objectList = query.getResultList();
			for (Object[] object : objectList) {
				list.add(new UserInfo(String.valueOf(object[0]), String.valueOf(object[1])));
			}

This looks good when the procedure returns just few columns. But the SP that I was calling queries mulitple tables and returns 40+ columns in the result. In that case, If I do the index based mapping, then I need to do the code changes whenever the SP is changed. i.e., when columns are added/removed in the select query of SP.

To overcome this issue, I thought of using SqlResultSetMapping which specifies the mapping of the result of a native SQL query or stored procedure. However, this annotation will work only on managed Entity classes which I don’t have.

Hence, after some googling, I came up with the following 2 solutions.

Table of Contents

Solution 1: Using @MappedSuperclass

Annotate the POJO with @MappedSuperclass since a mapped superclass has no separate table defined for it. At the same time, we can use the SqlResultSetMapping on this POJO to define our mapping. This solution will work only on Hibernate version < 5.4.30. Since 5.4.30, this behaviour was changed and the metadata like SqlResultSetMappings on a mapped super class are ignored.

Solution 2: Using @Entity

If you are using Hibernate version >= 5.4.30 or Solution 1 doesn’t work for you, then you can annotate the POJO with @Entity and mark an id field with the @Id annotation since an entity should have a primary key. No table will be created for this dummy entity as long as the JPA property spring.jpa.hibernate.ddl-auto is set to none or not set at all.

Technologies used

Spring Tool Suite 4
JDK 11
MySQL Server 8
Spring boot v2.1.8.RELEASE

Let’s Get Started

Creating User Table in MySQL Workbench

CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_date` datetime NOT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `enabled` bit(1) DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `provider` varchar(255) DEFAULT NULL,
  `provider_user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Inerting Into User Table for Testing

INSERT INTO `demo`.`user`
(`user_id`,
`created_date`,
`display_name`,
`email`,
`enabled`,
`modified_date`,
`password`,
`provider`,
`provider_user_id`)
VALUES
(<{user_id: }>,
<{created_date: }>,
<{display_name: }>,
<{email: }>,
<{enabled: }>,
<{modified_date: }>,
<{password: }>,
<{provider: }>,
<{provider_user_id: }>);

Creating Stored Procedure

DELIMITER //
Create procedure USER_INFO()
BEGIN
    SELECT USER_ID AS ID, DISPLAY_NAME AS NAME FROM USER;
END //
DELIMITER ;

Creating POJO

Let’s create a UserInfo POJO to map the Stored Procedure result.

Using @MappedSuperclass

@MappedSuperclass
@SqlResultSetMapping(name = "UserInfo", classes = @ConstructorResult(targetClass = UserInfo.class, columns = { @ColumnResult(name = "id", type = String.class),
		@ColumnResult(name = "name", type = String.class) }))
public class UserInfo {

	private String id;
	private String name;

	public UserInfo(String id, String name) {
		this.id = id;
		this.name = name;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

Using @Entity

@Entity
@SqlResultSetMapping(name = "UserInfo", classes = @ConstructorResult(targetClass = UserInfo.class, columns = { @ColumnResult(name = "id", type = String.class),
		@ColumnResult(name = "name", type = String.class) }))
public class UserInfo {
	@Id
	private String id;
	private String name;
	// Getters and Setters
}

In this class, I’ve created a mapping with the name “UserInfo” and specified the UserInfo.class class as the targetClass in the ConstructorResult annotation.

Note: The constructor in this class should match exactly with the order and datatype of the columns defined in the @SqlResultSetMapping

The @ConstructorResult annotation is used in conjunction with the @SqlResultSetMapping annotations to map columns of a given SELECT query to a certain object constructor.

The @ColumnResult annotation is used in conjunction with the @SqlResultSetMapping or @ConstructorResult annotations to map a SQL column for a given SELECT query.

Creating Spring Data Repository

UserInfoRepository.java

@Repository
@Transactional
public class UserInfoRepository {
	@PersistenceContext
	private EntityManager entityManager;

	@SuppressWarnings("unchecked")
	public List<UserInfo> getUerInfo() {
		List<UserInfo> list = new ArrayList<>();
		StoredProcedureQuery query = entityManager.createStoredProcedureQuery("USER_INFO", "UserInfo");
		try {
			// Execute query
			query.execute();
			list = query.getResultList();
		} finally {
			try {
				query.unwrap(ProcedureOutputs.class).release();
			} catch (Exception e) {
			}
		}
		return list;
	}
}

Here, I’ve passed the SqlResultSetMapping “UserInfo” as a parameter to the method entityManager.createStoredProcedureQuery(String procedureName, String… resultSetMappings) along with the stored procedure name.

Creating Junit Test

DemoApplicationTests.java

@RunWith(SpringRunner.class)
@SpringBootTest
public class DemoApplicationTests {

	@Autowired
	private UserInfoRepository userInfoRepository;

	@Test
	public void getUserInfoTest() {
		List<UserInfo> list = userInfoRepository.getUerInfo();
		assertThat(list).isNotEmpty();
	}
}

@SpringBootTest annotation can be used when we need to bootstrap the entire container. The annotation works by creating the ApplicationContext that will be utilized in our tests.

@RunWith(SpringRunner.class) is used to provide a bridge between Spring Boot test features and JUnit. Whenever we are using any Spring Boot testing features in our JUnit tests, this annotation will be required.

Running the Junit Test

Conclusion

In this article, we have looked on how to map the SP resultset to POJO without an entity class. I hope that it will help someone with a similar use case. Thanks for reading 🙂

Share1