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 SqlResultSetMapping “UserInfo” 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 🙂
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.
I’m glad that this tutorial helped you. This is what fuels me to write more. Thanks 🙂
Hello. Is it possible to make any field optional? like in UserInfo, some procedure return id,name and some return id,name,designation.
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.
Can I use this annotation on a non entity class?
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
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.
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.
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?
Thank you 🙂 I’ll write a post shortly on how to map the result to POJO using pure hibernate without JPA.
Here you go https://www.javachinna.com/2020/09/22/call-stored-procedure-and-map-the-native-query-result-to-pojo-with-hibernate/
I am getting endless errors can you help?
The error i keep on getting is Could not locate appropriate constructor on class : com.ram.BAISV.model.Testing
If you can go through my question on stack over flow
https://stackoverflow.com/questions/64025425/calling-stored-procedure-and-mapping-the-query-result-to-non-entity-pojo-using-s
It looks like you figured it out. Let me know if you still need some help.
I am trying to use a Multiple Data Sources, after annotating the first configuration as @Primary, and query the stored procedure i get results, when i try to map another data source i end up getting The given SqlResultSetMapping name [Unknown SqlResultSetMapping [UserInfo]], any tips on why it is throwing this error on the second mapping?
Thank you very much for this article. I have a similar requirement and I am trying to follow along.
I have a question:
The stored proc I am using is written by some other team and I do not have the convenience to change the store proc, it has OUT parameters like OUT_EMPLOYEE_NAME, OUT_EMPLOYEE_CODE and others
As per above code, I understand the ColumnResult mapping would be like below
@ColumnResult(name = “OUT_EMPLOYEE_NAME”, type = String.class)
this means I have to declare string as below in the class.
private String OUT_EMPLOYEE_NAME;
Is there a way we can map the name in ColumnResult to something simpler so that the Strings defined in the class would follow the camel case convention.
Please let me know if I am doing something wrong and if this can be made simpler.
Hi Awanish,
You cannot use SQLResultSetMapping for Procedure OUT parameters. You need to register the OUT parameters and get the values as follows:
query.registerStoredProcedureParameter("OUT_EMPLOYEE_NAME", String.class, ParameterMode.OUT);
query.registerStoredProcedureParameter("OUT_EMPLOYEE_CODE", String.class, ParameterMode.OUT);
query.execute();
String name = (String) query.getOutputParameterValue("OUT_EMPLOYEE_NAME");
String code = (String) query.getOutputParameterValue("OUT_EMPLOYEE_CODE");
SQLResultSetMapping can be used for a Procedure that executes a select query. The name attribute in @ColumnResult refers to the column name in the select statement. not the field name in the POJO. The POJO should have a constructor with all the columns as parameters in the same order and type as defined in @ColumnResult mappings. The column names and POJO field names can be different as in the example below.
@MappedSuperclass
@SqlResultSetMapping(name = "UserInfo", classes = @ConstructorResult(targetClass = UserInfo.class, columns = {@ColumnResult(name = "user_id", type = String.class),
@ColumnResult(name = "display_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;
}
// getters and setters
}
Thanks it works, tried approached mentioned in https://www.baeldung.com/spring-data-jpa-stored-procedures and https://medium.com/@kalpads/calling-stored-procedure-using-spring-jpa-ee37fa58ca2d but somehow couldn’t make it work. Not sure what is issue with approached mentioned in given links
If my list = query.getResultList() is empty would you suspect I fat fingered the column result in userinfo?
No. It will throw an error if the ColumnResult mapping is incorrect. I would suspect the select query in the Stored Procedure and check if it is returning any results.
While I do get a stack dump at the end I ran the debugger and it shows the list is size 0. The procedure/query does return records when executed in the db. I’m trying it on our codebase. May take a step back and just implement your actual example and see how that goes.
It was a type mismatch on my target code base – timestamp and date. And indeed your code sample does work flawlessly on it’s own. Thank you.
I’m glad to know that this tutorial helped you.