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

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 / Repository classes which I don’t have.

Hence, after some googling, I’ve created a POJO and annotated it 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.

Technologies used

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

Let’s start creating table, stored procedure and classes

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 ;

UserInfo.java – The POJO to map the SP result

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

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 the 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 SqlResultSetMappingUserInfo” as a parameter to the method entityManager.createStoredProcedureQuery(String procedureName, String… resultSetMappings) along with the stored procedure name.

Creating the test class

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.

Run the 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 🙂

This Post Has 11 Comments

  1. Ahsan

    Thanks a lot for the tutorial. I am exactly looking for this solution. Able to implement this one without any error. Very clear and well described article.

    1. Chinna

      I’m glad that this tutorial helped you. This is what fuels me to write more. Thanks 🙂

  2. Ahsan

    Hello. Is it possible to make any field optional? like in UserInfo, some procedure return id,name and some return id,name,designation.

    1. Chinna

      No. It’s not possible. The procedure should return all the columns defined in the SqlResultSetMapping. Also, it can return additional fields that are not defined in the mapping. but not vice versa. You can define separate SqlResultSetMapping for procedures with different columns.

  3. Leonardo

    Can I use this annotation on a non entity class?

    1. Chinna

      Yes. You can use @SqlResultSetMapping with @MappedSuperclass annotation on a non entity class. If you don’t use @MappedSuperclass annotation then your @SqlResultSetMapping on a POJO will not be scanned by JPA

  4. DaithiG

    I tried exactly this solution as I didn’t want to pollute my @Entity class with annotations that are not directly related to the entity. But it did not work. JPA could not find the mapping using the name. I’m using Hibernate, might that have something to do with it?

    I got this error:
    unknownsqlresultsetmappingexception: The given SqlResultSetMapping name [Unknown SqlResultSetMapping is unknown.

    I had two other sets of eyes looking at it with this article to make sure I wasn’t missing something.

    Thanks though, I learned a lot about these JPA annotations.

    1. Chinna

      I’ve tested with Hibernate only. So It should work for you. Can you try annotating your POJO with @Entity (with some table for testing purpose) instead of @MappedSuperclass annotation? If it works, then we can assume this solution is not working for you. If it doesn’t work, then it means either your POJO is in a package that is not scanned by JPA at all or you are trying to look up SqlResultSetMapping with an incorrect name.

  5. Srijan

    Hi Chinna. First of all thanks for the above information, its very helpful.
    Lets say am using hibenate with spring but not JPA. In this scenario how can i call a PL/SQL stored procedure and map result list to POJO?

    1. Chinna

      Thank you 🙂 I’ll write a post shortly on how to map the result to POJO using pure hibernate without JPA.

Leave a Reply