5

I have a stored procedure that is returning results from multiple tables

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetProjectAndClientOverview`()
BEGIN
  SELECT ( SELECT count(*)  FROM projects_info) as "projects_done",
    (SELECT COUNT(*) FROM client_info) as "happy_clients",
    (SELECT count(*) FROM projects_info where category_id in(
select id from pme.projects_category
where category_name in 
("Commercial","Corporate","Hospitality")
))  as "real_professionals" ,
    (SELECT COUNT(*) FROM client_info) as "cups_of_coffee";
END

I have to call this procedure in SpringBoot using JPA, Please guide how to create Entity class, Repository, Service for this stored procedure.

I have tried to create Entity class but not sure if it is right

 package com.panchmeru_studio.entities;
    
    import lombok.Data;
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.NamedStoredProcedureQueries;
    import javax.persistence.NamedStoredProcedureQuery;
    
    @Data
    @Entity
   @NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(name = "getProjectAndClientOverview",
                                procedureName = "GetProjectAndClientOverview",
    resultClasses ={ ProjectsInfo.class ,ClientInfo.class})
})
    public class GetProjectAndClientOverview {
        
        @Column(name="projects_done")
            private int projects_done;
        @Column(name="happy_clients")
            private int happy_clients;
        @Column(name="real_professionals")
            private int real_professionals;
        @Column(name="cups_of_coffee")
            private int cups_of_coffee;
    
        }

Please correct this entity class and guide me further.

Thank you

VJain
  • 1,027
  • 7
  • 17
  • 37

0 Answers0