We will need to expose the following APIs to interact with the countrylanguage table:

• Get list of languages for a given country code
• Add a new language for a country by checking that the language doesn't already exist
• Delete a language for a country

For the sake of keeping it short, we will show the method implementations covering these three scenarios. The complete code can be found in the com.nilangpatel.worldgdp.dao.CountryLanguageDAO class available in the code downloaded for this book. The following is the code for these method implementations:

public List<CountryLanguage> getLanguages(String countryCode, Integer pageNo){
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("code", countryCode);
  
  Integer offset = (pageNo - 1) * PAGE_SIZE;
  params.put("offset", offset);
  params.put("size", PAGE_SIZE);
  
  return namedParamJdbcTemplate.query("SELECT * FROM countrylanguage"
      + " WHERE countrycode = :code"
      + " ORDER BY percentage DESC "
      + " LIMIT :size OFFSET :offset ", 
      params, new CountryLanguageRowMapper());
}

public void addLanguage(String countryCode, CountryLanguage cl) {
  namedParamJdbcTemplate.update("INSERT INTO countrylanguage ( "
      + " countrycode, language, isofficial, percentage ) "
      + " VALUES ( :country_code, :language, "
      + " :is_official, :percentage ) ", 
      getAsMap(countryCode, cl));
}

public boolean languageExists(String countryCode, String language) {
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("code", countryCode);
  params.put("lang", language);
  
  Integer langCount = namedParamJdbcTemplate.queryForObject(
    "SELECT COUNT(*) FROM countrylanguage"
    + " WHERE countrycode = :code "
    + " AND language = :lang", params, Integer.class);
  return langCount > 0;
}

public void deleteLanguage (String countryCode, String language ) {
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("code", countryCode);
  params.put("lang", language);
  namedParamJdbcTemplate.update("DELETE FROM countrylanguage "
      + " WHERE countrycode = :code AND "
      + " language = :lang ", params);
}

private Map<String, Object> getAsMap(String countryCode, CountryLanguage cl){
  Map<String, Object> map = new HashMap<String, Object>();
  map.put("country_code", countryCode);
  map.put("language", cl.getLanguage());
  map.put("is_official", cl.getIsOfficial());
  map.put("percentage", cl.getPercentage());
  return map;
}