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