The following are some of the important operations to be supported by com.nilangpatel.worldgdp.dao.CityDAO class:

• Get cities for a country
• Get city details for given ID
• Add a new city to a country
• Delete the given city from the country

Let's go ahead and implement each one of these functionalities starting with the getCities, as follows:

public List<City> getCities(String countryCode, Integer pageNo){
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("code", countryCode);
  if ( pageNo != null ) {
    Integer offset = (pageNo - 1) * PAGE_SIZE;
    params.put("offset", offset);
    params.put("size", PAGE_SIZE);
  }
  
  return namedParamJdbcTemplate.query("SELECT "
      + " id, name, countrycode country_code, district, population "
      + " FROM city WHERE countrycode = :code"
      + " ORDER BY Population DESC"
      + ((pageNo != null) ? " LIMIT :offset , :size " : ""),
      params, new CityRowMapper());
}

We are using a paginated query to get a list of cities for a country. We will also need another overloaded version of this method where we return all the cities of a country and we will use this query to fetch all the cities while editing the country to select its capital. The overloaded version is as follows:

public List<City> getCities(String countryCode){
  return getCities(countryCode, null);
}

Next is to implement the method to get the city details, as shown in the following code:

public City getCityDetail(Long cityId) {
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("id", cityId);
  return namedParamJdbcTemplate.queryForObject("SELECT id, "
      + " name, countrycode country_code, "
      + " district, population "
      + " FROM city WHERE id = :id", 
      params, new CityRowMapper());
}

Then we implement the method to add a city as follows:

public Long addCity(String countryCode, City city) {
  
  SqlParameterSource paramSource = new MapSqlParameterSource(
      getMapForCity(countryCode, city));
  KeyHolder keyHolder = new GeneratedKeyHolder();
  namedParamJdbcTemplate.update("INSERT INTO city("
      + " name, countrycode, "
      + " district, population) "
      + " VALUES (:name, :country_code, "
      + " :district, :population )", 
      paramSource, keyHolder);
  return keyHolder.getKey().longValue();
}

As we saw with adding a country, this will also make use of a helper method to return a Map from the City data, as follows:

private Map<String, Object> getMapForCity(String countryCode, City city){
  Map<String, Object> map = new HashMap<String, Object>();
  map.put("name", city.getName());
  map.put("country_code", countryCode);
  map.put("district", city.getDistrict());
  map.put("population", city.getPopulation());
  return map;
}

An important thing to notice in addCity is the use of KeyHolder and GeneratedKeyHolder to return the generated (due to auto increment) primary key that is the cityId, as follows:

 KeyHolder keyHolder = new GeneratedKeyHolder();
 //other code
 return keyHolder.getKey().longValue();

And finally, we implement the method to delete a city from the country as shown in the following code:

public void deleteCity(Long cityId) {
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("id", cityId);
  namedParamJdbcTemplate.update("DELETE FROM city WHERE id = :id", params);
}

Now let's add a test for CityDAO. Add the CityDAOTest class in com.nilangpatel.worldgdp.test.dao package under src/test/java folder as follows:

@RunWith(SpringRunner.class)
@SpringJUnitConfig( classes = {
  TestDBConfiguration.class, CityDAO.class})
public class CityDAOTest {

  @Autowired CityDAO cityDao;
  
  @Autowired @Qualifier("testTemplate")
  NamedParameterJdbcTemplate namedParamJdbcTemplate;
  
  @Before
  public void setup() {
    cityDao.setNamedParamJdbcTemplate(namedParamJdbcTemplate);
  }
  
  @Test public void testGetCities() {
    List<City> cities = cityDao.getCities("IND", 1);
    assertThat(cities).hasSize(10);
  }
  
  @Test public void testGetCityDetail() {
    Long cityId = 1024l;
    City city = cityDao.getCityDetail(cityId);
    assertThat(city.toString()).isEqualTo("City(id=1024, name=Mumbai (Bombay), "
      + "countryCode=IND, country=null, district=Maharashtra, population=10500000)");
  }
  
  @Test public void testAddCity() {
    String countryCode = "IND";
    City city = new City();
    city.setCountryCode(countryCode);
    city.setDistrict("District");
    city.setName("City Name");
    city.setPopulation(101010l);
    
    long cityId = cityDao.addCity(countryCode, city);
    assertThat(cityId).isNotNull();
    City cityFromDb = cityDao.getCityDetail(cityId);
    assertThat(cityFromDb).isNotNull();
    assertThat(cityFromDb.getName()).isEqualTo("City Name");
  }
  
  @Test (expected = EmptyResultDataAccessException.class)
  public void testDeleteCity() {
    Long cityId = addCity();
    cityDao.deleteCity(cityId);
    City cityFromDb = cityDao.getCityDetail(cityId);
    assertThat(cityFromDb).isNull();
  }
  
  private Long addCity() {
    String countryCode = "IND";
    City city = new City();
    city.setCountryCode(countryCode);
    city.setDistrict("District");
    city.setName("City Name");
    city.setPopulation(101010l);
    
    return cityDao.addCity(countryCode, city);
  }
}