I need to create an endpoint that returns the census data by state with the listing of cities, I currently get this using two endpoints.
Current response:
Custom query one: 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
}
]
Custom query two: 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
}
]
Expected response:
[
{
"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
}
]
}
]
Can you help me?
My classes:
MODEL
State
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;
}
City
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;
}
CensusPopulation
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;
}
INTERFACE
CensusStateStats
package com.example.demo.dto;
public interface CensusStateStats {
Long getId();
Integer getCode();
String getName();
Long getStatePopulation();
Long getCountCities();
}
CensusStateCitiesStats
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();
}
}
CensusStateCitiesStatsDto
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();
}
}
REPOSITORY
CensusPopulationRep
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);
}
SERVICE
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);
}
}
CONTROLLER
CensusPopulationController
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()));
}
}
CodePudding user response：
You can create a new class StateCityCensusDto then also return stateId in your query for censusCitiesByState
@Data
public class StateCityCensusDto {
private StateCensus stateCensus;
private List<CityCensus> cities;
}
Integer getCode();
String getName();
Long getCityPopulation();
//add stateId retrieve from query
Long getStateId();
}
Then return the states alongside it cities.
List<CensusStateStats> stateCensusList = //retrieve from the db;
List<CensusStateCitiesStats> //retrieve from the db;
List<StateCityCensusDto> stateCityCensusList = new ArrayList<>();
stateCensusList.forEach(stateCensus -> {
StateCityCensusDto stateCityCensus = new StateCityCensusDto();
stateCityCensus.setStateCensus(stateCensus);
stateCityCensus.setCities(cityCensusList.stream()
.filter(c -> c.getStateId() == stateCensus.getId())
.collect(Collectors.toList()));
stateCityCensusList.add(stateCityCensus);
});