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.
Hi Sir,
great tutorial, I am trying to implement your solution, but now calling two stored procedures on different databases(a multisource configuration) whenever I try to call either I end up with error, but when I annotate one config as primary, I end up with results, the other one that is not marked as primary throws error, The given SqlResultSetMapping name [Unknown SqlResultSetMapping [Testing]] is unknown. Have asked on stackoverflow no answer link https://stackoverflow.com/questions/65137595/spring-data-jpa-map-the-stored-procedure-result-to-non-entity-pojo-from-multiple/65140656?noredirect=1#comment115171574_65140656
I will accept any help
I have answered your question there. Let me know if you need more help
Thank you for the quick reply and for the reply, I have been stuck for a month, trying multiple things dont work, can you checkout my code and advise what is wrong, I have setup as advised but still geting the error https://github.com/remymumoh/multidatasource, thank you
I had a look at your GitHub code and found that your BAISVReports controller was getting the primary entity manager only. So just autowired the entitymanager using @Qualifier and now the secondary entity manager is getting autowired. Now both controllers are able to find the SQLResultSetMapping. I have updated my answer in stackoverflow.
Hi Chinna,
I am not able to fetch the data from sql server, failing to fetch data in code…. Query. getResultList() method and getting error “Illegal State Exception.. Callable ou statement not called but getResultList was called. Please help me regarding this and I am using without entity pojo object and following the same code which you mentioned above.
Error is “java.lang.illegalstateexception:Current CallableStatement out was not a ResultSet, but getResultList was called”
This approach will work only for the Stored Procedure which queries the database with a select statement. Then only the columns in the select statement can be mapped to the POJO. It looks like you are trying to fetch the Stored Procedure OUT parameter value using this approach. That is why you are facing this issue.
You can use
getOutputParameterValue
methods to retrieve the values passed back from the procedure through INOUT and OUT parameters. For more information, you can refer thishow do we call the stored procedure by passing parameters