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
getOutputParameterValuemethods 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
Hi Alekhya,
Sorry for the late response. I was very busy recently. I hope you would have already found a solution. Anyhow you can register IN parameter as shown below to pass parameters to the SP and use that in the select query.
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("USER_INFO", "UserInfo");
query.registerStoredProcedureParameter("x", Double.class, ParameterMode.IN);
// Execute query
query.execute();
list = query.getResultList();
Perfect solution without the use of the entity, call a stored procedure, thank you very much!
Thank you
Thank you so much..your blog helped me alot…:)
Glad to know that my blog helped you.
Using your example but not for fetching data using stored procedure but SQL query with createNativeQuery method. I don’t get any errors but getResultList method still returns List of Objects instead of List of POJOs for which we did mapping. Any idea why’s that happening?
are you passing the resultSetMapping name to the createNativeQuery method?
Hi Chinna,
I figured out combination that was producing desired result. I had to mark POJO with @Entity and one of its properties with @Id (the one property that made sense to be marked with) instead of @MappedSuperclass. Thank you for your insight on all this.
I do know that using @Entity and @Id combination on a POJO would work. Using @MappedSuperClass also should work too. But I’m not sure why it didn’t work for you.
Hi chinna,
I am getting errors when i am calling the stored procedure for inserting record.
Hi Abhi,
what are all the errors are you getting? Can you paste your code snippet here? So that I can take a look at it?
Hi chinna,
This is my stored procedure.for this i need a service.can u please help me for this.
i am getting issue is column in not in the resultset.
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_employee`(
IN employeeID integer,
IN firstName varchar(50),
IN lastName varchar(50),
IN address varchar(100) )
BEGIN
INSERT INTO alekhya.employees
(
employeeID,
firstName,
lastName,
address
)
values
(
employeeID,
firstName,
lastName,
address
);
SELECT LAST_INSERT_ID() as id;
END
Hi Abhi,
Based on the error, it looks like you are trying to get a column value that is not selected in the SP select query. How you are calling this stored procedure? Can you post that code as well here?
This is my POJO class:
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@MappedSuperclass
@SqlResultSetMapping(name = “EmployeeInfo”, classes = @ConstructorResult(targetClass = EmployeeInfo.class,
columns = { @ColumnResult(name = “employeeID”, type = Long.class),
@ColumnResult(name = “firstName”, type = String.class),
@ColumnResult(name = “lastName”, type = String.class),
@ColumnResult(name = “address”, type = String.class)
}))
public class EmployeeInfo {
private Long employeeID;
private String firstName;
private String lastName;
private String address;
}
Repository Class:
@Repository
@Transactional
public class EmployeeInfoRepository {
@PersistenceContext
private EntityManager entityManager;
@SuppressWarnings(“unchecked”)
public EmployeeInfo addEmployeeInfo(@Param(“employeeID”) Long employeeID, @Param(“firstName”) String firstName, @Param(“lastName”) String lastName,
@Param(“address”) String address)
{
EmployeeInfo list;
StoredProcedureQuery query = entityManager.createStoredProcedureQuery(“add_employee”, “EmployeeInfo”);
try {
query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(4, String.class, ParameterMode.IN);
query.setParameter(1, employeeID);
query.setParameter(2, firstName);
query.setParameter(3, lastName);
query.setParameter(4, address);
query.execute();
System.out.println(“execute:” +query.execute());
list = (EmployeeInfo) query.getResultList();
System.out.println(list);
} finally {
try {
query.unwrap(ProcedureOutputs.class).release();
} catch (Exception e) {
}
}
return list;
}
}
Hi Abhi,
In the stored procedure, you have only the following select statement
SELECT LAST_INSERT_ID() as id;
This will return only the id column in the resultSet. But you are trying to map this resultSet to EmployeeInfo resultSet mapping which has fields like employeeID, first name, last name and address.
That is why it is throwing column not found error.
Either include all these fields in the stored procedure select statement.
Or change your resultSet mapping to have only the id field.
Hi chinna,
now i am getting this erro:
Could not locate appropriate constructor on class ; nested exception is java.lang.IllegalArgumentException: Could not locate appropriate constructor on class
Hi Abhi,
The constructor in the POJO should match exactly with the order and datatype of the columns defined in the @SqlResultSetMapping
Hello sir,
I’m getting the below error when I use @MappedSuperclass. If I change it to @Entity and mark one of the column as @Id, things are working fine. Can you help on this? I’m using spring data JPA starter kit for this example.
org.hibernate.MappingException: Unknown entity
Hi Vino,
Can you share your code? So that I can take a look at it?