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

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

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 SqlResultSetMappingUserInfo” 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 🙂

This Post Has 58 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.

      1. Greg

        I might have missed something. This code use to work absolutely perfect in Java 8. I ended up having to use basically the same approach in 11 and I got the same error as DaithiG. When I saw this comment I decided to try removing @MappedSuperclass and adding @Entity in my POJO along with @Id and it worked. So I’m not sure what changed between versions but thought I would leave this here in case someone else has the same issue.

  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.

  6. remy

    I am getting endless errors can you help?

  7. remy

    The error i keep on getting is Could not locate appropriate constructor on class : com.ram.BAISV.model.Testing

    1. Chinna

      It looks like you figured it out. Let me know if you still need some help.

      1. remy

        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?

  8. Awanish

    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.

    1. Chinna

      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
      }

  9. Greg

    If my list = query.getResultList() is empty would you suspect I fat fingered the column result in userinfo?

    1. Chinna

      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.

      1. Greg

        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.

      2. Greg

        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.

        1. Chinna

          I’m glad to know that this tutorial helped you.

  10. ray

    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

    1. Chinna

      I have answered your question there. Let me know if you need more help

      1. ray

        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

        1. Chinna

          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.

  11. Arjun

    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.

  12. Arjun

    Error is “java.lang.illegalstateexception:Current CallableStatement out was not a ResultSet, but getResultList was called”

    1. Chinna

      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 this

  13. alekhya

    how do we call the stored procedure by passing parameters

    1. Chinna

      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();

  14. ywkpl

    Perfect solution without the use of the entity, call a stored procedure, thank you very much!

    1. Chinna

      Thank you

  15. archana

    Thank you so much..your blog helped me alot…:)

    1. Chinna

      Glad to know that my blog helped you.

  16. Tixi

    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?

    1. Chinna

      are you passing the resultSetMapping name to the createNativeQuery method?

      1. Tixi

        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.

        1. Chinna

          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.

  17. abhi

    Hi chinna,
    I am getting errors when i am calling the stored procedure for inserting record.

    1. Chinna

      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?

      1. abhi

        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

        1. Chinna

          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?

          1. abhi

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

            }

          2. Chinna

            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.

  18. abhi

    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

    1. Chinna

      Hi Abhi,

      The constructor in the POJO should match exactly with the order and datatype of the columns defined in the @SqlResultSetMapping

  19. Vino

    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

    1. Chinna

      Hi Vino,

      Can you share your code? So that I can take a look at it?

  20. mush

    Hi Chinna, how Can I use a similar implementation but now with input parameters? Will the POJO have @MappedSuperclass
    @SqlResultSetMapping or I will use

    @NamedStoredProcedureQuery(name = “monitor_summary”,
    procedureName = “get_summary”,
    resultClasses = { Monitorea.class },
    parameters = {
    @StoredProcedureParameter(name = “clusterNumber”, mode = ParameterMode.IN, type = String.class)})

    and then when I call the stored procedure

    public List getEmp(String clusterNumber) {
    List list;
    StoredProcedureQuery query = entityManager.createStoredProcedureQuery(“get_summary”, “monitor_summary”);
    query.registerStoredProcedureParameter(clusterNumber, String.class, ParameterMode.IN);

    query.execute();
    list = query.getResultList();

    return list;
    }

    I always end up with an error
    The given SqlResultSetMapping name [Unknown SqlResultSetMapping [monitor_summary]] is unknown”,

    AM i doing the right thing or I should use mapped class?

    1. Chinna

      Yes. Just follow the same implementation with input parameters as well. If you still get “Unknown SqlResultSetMapping” error, please share the code in StackOverflow and share the link with me. So that I can take a look at it.
      If you want to use NamedStoredProcedureQuery, then you can define the NamedStoredProcedureQuery with SqlResultSetMapping name as shown below:
      @NamedStoredProcedureQuery(name = "get_summary_query", procedureName = "get_summary", parameters =
      {@StoredProcedureParameter(mode = ParameterMode.IN, name = "clusterNumber", type = String.class)},
      resultSetMappings = { "get_summary_query_mapping" })

  21. mani

    Hi chinna.
    I am trying to map my native SQL result to my POJO, but it’s returning an error like this Could not locate appropriate constructor on class ; nested exception is java.lang.IllegalArgumentException: Could not locate appropriate constructor on class.Is @ConstructorResult supports timestamp class.

    1. Chinna

      Hi Mani,

      This exception happens because JPA doesn’t change column types returned from the database for native queries. Because of this, you have a type mismatch. You can map both datetime and timestamp columns to LocalDateTime type. If you still face the issue, You can add a breakpoint to ConstructorResultColumnProcessor.resolveConstructor(Class targetClass, List types) to find the exact type returned. After you find a mismatch, change the field type in your mapping class and SQLResultSetMapping.

      I hope it helps.

  22. mani

    Hi chinna,
    I am still facing issue.pls let me know how to call ConstructorResultColumnProcessor.resolveConstructor(Class targetClass, List types) .

    1. Chinna

      You don’t need to call that method. You just need to put a breakpoint in that method and debug it to find out the actual types returned from the database. if possible share your code or put it in StackOverflow and share it with me. I’ll have look at it.

Leave a Reply