[SpringBoot] JDBC Template : SELECT [REST API 적용하여 데이터 조회하기]

---

[SpringBoot] Spring JDBC 사용하여 데이터베이스 연결하기 [JDBC Template, Groovy 활용]

---

- JDBC Template : SELECT

@PathVariable

호출 : localhost:8000/info/cityListByCode/KOR/3000000
→ countryCode = KOR, population = 3000000

@GetMapping("cityListByCode/{countryCode}/{population}")
public Object cityByCountryCode(@PathVariable("countryCode") String ctCode, @PathVariable("population") int population) {
	log.info("countryCode = {}, population {}", ctCode, population);
	List<City> cityList = infoService.findCityByCodeAndPopulation(ctCode, population);
	return cityList;
}

---

countryCode = KOR, population = 3000000

---

@RequestParam

호출 : localhost:8000/info/cityListByCode?countryCode=KOR
→ countryCode = KOR, population = 0

@GetMapping("cityListByCode")
public Object cityByCountryCode(@RequestParam("countryCode") String ctCode, @RequestParam(value="population", required = false, defaultValue = "0") int population) {
	log.info("countryCode = {}, population = {}", ctCode, population);
	List<City> cityList = infoService.findCityByCodeAndPopulation(ctCode, population);
	return cityList;
}

---

countryCode = KOR, population = 0

---

- CitySql.groovy

class CitySql {
	public static final String SELECT = """
		SELECT ID, Name, CountryCode, District, Population FROM city WHERE 1=1 
	""";
	
	public static final String COUNTRY_CODE_CONDITION = """
		AND countryCode = :countryCode
	""";
	
	public static final String POPULATION_CONDITION = """
		AND population >= :population
	""";
}

---

- CityRepository

@Slf4j
@Repository
public class CityRepository {
	
	private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private final CityRowMapper cityRowMapper;

	public CityRepository(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
		this.cityRowMapper = new CityRowMapper();
	}
	
        // 코드 생략
    
	public List<City> findByCountryCodeAndPopulation(String countryCode, int population) {
		String query = CitySql.SELECT + CitySql.COUNTRY_CODE_CONDITION + CitySql.POPULATION_CONDITION;
		SqlParameterSource param = new MapSqlParameterSource("countryCode", countryCode).addValue("population", population);
		return namedParameterJdbcTemplate.query(query, param, this.cityRowMapper);
	}

}

---

- InfoService

@Service
public class InfoService {
	
	private final CityRepository cityRepository;
    
	public InfoService(CityRepository cityRepository) {
		this.cityRepository = cityRepository;
	}

	public List<City> findCityByCodeAndPopulation(String countryCode, int population) {
		return this.cityRepository.findByCountryCodeAndPopulation(countryCode, population);
	}
	
}

---

- InfoController

@Slf4j
@RestController
@RequestMapping("info")
public class InfoController {
	
	private InfoService infoService;
	
	@Autowired
	public InfoController(InfoService infoService) {
		this.infoService = infoService;
	}
		
	@GetMapping("cityListByCode/{countryCode}/{population}")
	public List<City> cityByCountryCode(@PathVariable("countryCode") String ctCode, @PathVariable("population") int population) {
		log.info("countryCode = {}, population {}", ctCode, population);
		
		List<City> cityList = infoService.findCityByCodeAndPopulation(ctCode, population);
		return cityList;
	}
	
}

---

JDBC Template : SELECT 출력 결과

---

- 참고자료

[SpringBoot] RestApi 만들기 (5.3) jdbcTemplate - Select

---