Necesito crear un punto final que devuelva los datos del censo por estado con la lista de ciudades, actualmente obtengo esto usando dos puntos finales.

Respuesta actual:

Consulta personalizada uno: censusByState

 [ { "id": 1, "code": 11, "name": "Rondônia", "statePopulation": 1815278, "countCities": 52 }, { "id": 2, "code": 12, "name": "Acre", "statePopulation": 906876, "countCities": 22 }, {... }, { "id": 27, "code": 53, "name": "Distrito Federal", "statePopulation": 3094325, "countCities": 1 } ]

Consulta personalizada dos: censusCitiesByState

 [ { "code": 1100015, "name": "Alta Floresta d Oeste", "cityPopulation": 22516 }, { "code": 1100023, "name": "Ariquemes", "cityPopulation": 111148 }, {... }, { "code": 1101807, "name": "Vale do Paraíso", "cityPopulation": 6490 } ]

Respuesta esperada:

 [ { "id": 1, "code": 11, "name": "Rondônia", "statePopulation": 1815278, "countCities": 52, "cities": [ { "code": 1100015, "name": "Alta Floresta d Oeste", "cityPopulation": 22516 }, {... }, { "code": 1101807, "name": "Vale do Paraíso", "cityPopulation": 6490 } ] }, {... }, { "id": 2, "code": 12, "name": "Acre", "statePopulation": 906876, "countCities": 22, "cities":[ { "code": 1200013, "name": "Acrelândia", "cityPopulation": 15721 }, {... }, { "code": 1200807, "name": "Porto Acre", "cityPopulation": 19141 } ] } ]

¿Me puedes ayudar?

Mis clases:

MODELO

Estado

 package com.example.demo.model; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import javax.persistence.*; @Entity @Data @Builder @AllArgsConstructor @NoArgsConstructor @Table(name = "states") @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"}) public class State { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private Integer code; private String name; @Column(length = 2) private String uf; }

Ciudad

 package com.example.demo.model; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import javax.persistence.*; @Entity @Data @Builder @AllArgsConstructor @NoArgsConstructor @Table(name = "cities") @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"}) public class City { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "state_id", nullable = false, foreignKey = @ForeignKey(name = "fk_cities_states1")) private State state; private String code; private String name; }

CensoPoblación

 package com.example.demo.model; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import javax.persistence.*; @Entity @Data @Builder @AllArgsConstructor @NoArgsConstructor @Table(name = "census_population") @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"}) public class CensusPopulation { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "census_id", nullable = false, foreignKey = @ForeignKey(name = "fk_census_population_census1")) private Census census; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "city_id", nullable = false, foreignKey = @ForeignKey(name = "fk_census_population_cities1")) private City city; private Long population; }

INTERFAZ

CensusStateStats

 package com.example.demo.dto; public interface CensusStateStats { Long getId(); Integer getCode(); String getName(); Long getStatePopulation(); Long getCountCities(); }

CensoEstadoCiudadesEstadísticas

 package com.example.demo.dto; public interface CensusStateCitiesStats { Integer getCode(); String getName(); Long getCityPopulation(); }

DTO

CensusStateStatsDto

 package com.example.demo.dto; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor public class CensusStateStatsDto { private Long id; private Integer code; private String name; private Long statePopulation; private long countCities; public CensusStateStatsDto(CensusStateStats censusStateStatsDto) { this.id = censusStateStatsDto.getId(); this.code = censusStateStatsDto.getCode(); this.name = censusStateStatsDto.getName(); this.statePopulation = censusStateStatsDto.getStatePopulation(); this.countCities = censusStateStatsDto.getCountCities(); } }

CensoEstadoCiudadesEstadísticasDto

 package com.example.demo.dto; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor public class CensusStateCitiesStatsDto { private Integer code; private String name; private Long cityPopulation; public CensusStateCitiesStatsDto(CensusStateCitiesStats censusStateCitiesStats) { this.code = censusStateCitiesStats.getCode(); this.name = censusStateCitiesStats.getName(); this.cityPopulation = censusStateCitiesStats.getCityPopulation(); } }

REPOSITORIO

CensoPoblaciónRep

 package com.example.demo.repository; import com.example.demo.dto.CensusStateCitiesStats; import com.example.demo.dto.CensusStateStats; import com.example.demo.model.CensusPopulation; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import java.util.List; public interface CensusPopulationRep extends JpaRepository<CensusPopulation, Long> { @Query(value = "SELECT s.id, s.code, s.name, s.uf, " + "SUM(cp.population) AS statePopulation, " + "COUNT(cp.id) AS countCities, " + "FROM census_population cp " + "INNER JOIN cities c ON c.id = cp.city_id " + "INNER JOIN states s ON s.id = c.state_id " + "GROUP BY s.code, s.name, s.uf" , nativeQuery = true) List<CensusStateStats> censusByState(); @Query(value = "SELECT c.code, c.name, " + "SUM(cp.population) AS cityPopulation, " + "FROM census_population cp " + "INNER JOIN cities c ON c.id = cp.city_id " + "WHERE c.state_id = :state " + "GROUP BY c.code, c.name " , nativeQuery = true) List<CensusStateCitiesStats> censusCitiesByState(@Param("state") Long state); }

SERVICIO

CensusPopulationService

 package com.example.demo.service; import com.example.demo.dto.CensusStateCitiesStats; import com.example.demo.dto.CensusStateStats; import com.example.demo.model.CensusPopulation; import com.example.demo.repository.CensusPopulationRep; import lombok.RequiredArgsConstructor; import org.springframework.stereotype.Service; import java.util.List; @Service @RequiredArgsConstructor public class CensusPopulationService { private final CensusPopulationRep censusPopulationRep; public List<CensusPopulation> findAll() { return censusPopulationRep.findAll(); } public List<CensusStateStats> censusByState() { return censusPopulationRep.censusByState(); } public List<CensusStateCitiesStats> censusCitiesByState(Long state) { return censusPopulationRep.censusCitiesByState(state); } }

CONTROLADOR

CensusPopulationControllerCensusPopulationController

 package com.example.demo.controller; import com.example.demo.dto.CensusStateCitiesStatsDto; import com.example.demo.dto.CensusStateStatsDto; import com.example.demo.model.CensusPopulation; import com.example.demo.service.CensusPopulationService; import lombok.RequiredArgsConstructor; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.*; import java.util.List; import java.util.stream.Collectors; @RestController @RequestMapping("api/v1/census-population") @RequiredArgsConstructor public class CensusPopulationController { private final CensusPopulationService censusPopulationService; @GetMapping public ResponseEntity<List<CensusPopulation>> findAll() { return ResponseEntity.ok(censusPopulationService.findAll()); } @GetMapping("/state-stats") public ResponseEntity<List<CensusStateStatsDto>> censusByState() { return ResponseEntity.ok(censusPopulationService.censusByState().stream() .map(CensusStateStatsDto::new) .collect(Collectors.toList())); } @GetMapping("/state-cities-stats") public ResponseEntity<List<CensusStateCitiesStatsDto>> censusCitiesByState(@RequestParam(required = false) Long state) { return ResponseEntity.ok(censusPopulationService.censusCitiesByState(state).stream() .map(CensusStateCitiesStatsDto::new) .collect(Collectors.toList())); } }