We will define the JDBC connection properties in an application.properties file and place it in src/main/resources. The properties we define are as follows:

dataSourceClassName=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/worldgdp
dataSource.user=root
dataSource.password=test

The preceding properties are with the assumptions that MySQL is running on port 3306 and the database username and password are root and test respectively. You can change these properties as per your local configuration. The next step is to define a properties resolver that will be able to resolve the properties when used from within the code. We will use the @PropertySource annotation, along with an instance of  PropertySourcesPlaceholderConfigurer, as shown in the following code:

@Configuration
@PropertySource("classpath:application.properties")
public class PropertiesWithJavaConfig {

   @Bean
   public static PropertySourcesPlaceholderConfigurer
     propertySourcesPlaceholderConfigurer() {
      return new PropertySourcesPlaceholderConfigurer();
   }
}

This class reads all the properties from the application.properties file stored in classpath (src/main/resources). Next up is to configure a javax.sql.DataSource object that will connect to the database using the properties defined in the application.properties file. We will use the HikariCP connection pooling library for creating our DataSource instance. This DataSource instance is then used to instantiate NamedParameterJdbcTemplate. We will use NamedParameterJdbcTemplate to execute all our SQL queries. At this point, we need to add a necessary dependency for the HikariCP library as follows:

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>${hikari.version}</version>
    </dependency>

The DBConfigurationdata source configuration class should look as follows:

@Configuration
public class DBConfiguration {
  @Value("${jdbcUrl}") String jdbcUrl;
  @Value("${dataSource.user}") String username;
  @Value("${dataSource.password}") String password;
  @Value("${dataSourceClassName}") String className;

  @Bean
  public DataSource getDataSource() {
    HikariDataSource ds = new HikariDataSource();
    ds.setJdbcUrl(jdbcUrl);
    ds.setUsername(username);
    ds.setPassword(password);
    ds.setDriverClassName(className);
    return ds;
  }

  @Bean
  public NamedParameterJdbcTemplate namedParamJdbcTemplate() {
    NamedParameterJdbcTemplate namedParamJdbcTemplate = 
        new NamedParameterJdbcTemplate(getDataSource());
    return namedParamJdbcTemplate;
  }
}

Let's have a quick introduction to a few new things used in this code:

It is always good practice to write unit test cases in JUnit. We will write test cases for our application. For that, we need to create the corresponding configuration classes for running our JUnit tests. In the next section, we will look at setting up the test environment.

Let's adopt a test first approach here. So, before going into writing the queries and DAO classes, let's set up the environment for our unit testing. If you don't find the src/test/java and src/test/resources folders, then please go ahead and create them either from your IDE or from your OS file explorer.

The src/test/java folder will contain all the Java code and src/test/resources will contain the required property files and other resources required for test cases. After creating the required folders, the project structure looks something like that shown in the following screenshot:

We will use the H2 database as a source of data for our testing environment. For that, we will update our Maven dependencies to add H2 and JUnit dependencies. H2 is one of the most popular embedded databases. The following is the dependency information that you need to add in your pom.xml:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>${spring.version}</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>${junit.version}</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.assertj</groupId>
    <artifactId>assertj-core</artifactId>
    <version>${assertj.version}</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>${h2.version}</version>
</dependency>

We already have a property for spring.version, but we need version properties for the other two, as given in the following code:

<junit.version>4.12</junit.version>
<assertj.version>3.12.0</assertj.version>
<h2.version>1.4.198</h2.version>

The World DB schema available in MySQL will not be compatible to run with H2, but don't worry. The compatible World DB schema for H2 is available in the source code of this chapter, you can download from GitHub (https://github.com/PacktPublishing/Spring-5.0-Projects/tree/master/chapter01). It is kept in the src/test/resources folder in the project. The file name is h2_world.sql. We will use this file to bootstrap our H2 database with the required tables and data that will then be available in our tests.

Next up is to configure H2 and one of the things we configure is the name of the SQL script file that contains the schema and data. This SQL script file should be available on the classpath. The following is the configuration class created in the com.nilangpatel.worldgdp.test.configpackage under src/test/java folder:

@Configuration
public class TestDBConfiguration {

  @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
            .generateUniqueName(true)
            .setType(EmbeddedDatabaseType.H2)
            .setScriptEncoding("UTF-8")
            .ignoreFailedDrops(true)
            .addScript("h2_world.sql")
            .build();
    }

  @Bean("testTemplate")
  public NamedParameterJdbcTemplate namedParamJdbcTemplate() {
    NamedParameterJdbcTemplate namedParamJdbcTemplate = 
        new NamedParameterJdbcTemplate(dataSource());
    return namedParamJdbcTemplate;
  }
}

Along with the H2 configuration, we are initializing NamedParameterJdbcTemplate by providing it with the H2 datasource built in the other method. 

As we are using the JDBC Template, we need a way to map the rows of data from a database to a Java object. This can be achieved by implementing a RowMapper interface. We will define mapper classes for all the three entities. For Country, the raw mapper class looks as follows:

public class CountryRowMapper implements RowMapper<Country>{

  public Country mapRow(ResultSet rs, int rowNum) 
                            throws SQLException {
    Country country = new Country();
    country.setCode(rs.getString("code"));
    country.setName(rs.getString("name"));
    country.setContinent(rs.getString("continent"));
    country.setRegion(rs.getString("region"));
    country.setSurfaceArea(rs.getDouble("surface_area"));
    country.setIndepYear(rs.getShort("indep_year"));
    country.setPopulation(rs.getLong("population"));
    country.setLifeExpectancy(rs.getDouble("life_expectancy"));
    country.setGnp(rs.getDouble("gnp"));
    country.setLocalName(rs.getString("local_name"));
    country.setGovernmentForm(rs.getString("government_form"));
    country.setHeadOfState(rs.getString("head_of_state"));
    country.setCode2(rs.getString("code2"));
    if ( Long.valueOf(rs.getLong("capital")) != null ) {
      City city = new City();
      city.setId(rs.getLong("capital"));
      city.setName(rs.getString("capital_name"));
      country.setCapital(city);
    }
    return country;
  }
}

Then we define the mapper class for City as follows:

public class CityRowMapper implements RowMapper<City>{
  public City mapRow(ResultSet rs, int rowNum) 
                    throws SQLException {
    City city = new City();
    city.setCountryCode(rs.getString("country_code"));
    city.setDistrict(rs.getString("district"));
    city.setId(rs.getLong("id"));
    city.setName(rs.getString("name"));
    city.setPopulation(rs.getLong("population"));
    return city;
  }
}

And finally, we define CountryLanguage as follows:

public class CountryLanguageRowMapper implements 
                       RowMapper<CountryLanguage> {
  public CountryLanguage mapRow(ResultSet rs, int rowNum) 
                                 throws SQLException {
    CountryLanguage countryLng = new CountryLanguage();
    countryLng.setCountryCode(rs.getString("countrycode"));
    countryLng.setIsOfficial(rs.getString("isofficial"));
    countryLng.setLanguage(rs.getString("language"));
    countryLng.setPercentage(rs.getDouble("percentage"));
    return countryLng;
  }
}

Let's go ahead and define the CountryDAO class in the com.nilangpatel.worldgdp.dao package along with the required methods, starting with the getCountries method. This method will fetch the details of countries to show them in the listing page. This method is also called while filtering the country list. Based on listing, filtering, and paginating, we have broken up the query used in this method into the following parts:

private static final String SELECT_CLAUSE = "SELECT "
      + " c.Code, "
      + " c.Name, "
      + " c.Continent, "
      + " c.region, "
      + " c.SurfaceArea surface_area, "
      + " c.IndepYear indep_year, "
      + " c.Population, "
      + " c.LifeExpectancy life_expectancy, "
      + " c.GNP, "
      + " c.LocalName local_name, "
      + " c.GovernmentForm government_form, "
      + " c.HeadOfState head_of_state, "
      + " c.code2 ,"
      + " c.capital ,"
      + " cy.name capital_name "
      + " FROM country c"
      + " LEFT OUTER JOIN city cy ON cy.id = c.capital ";

private static final String SEARCH_WHERE_CLAUSE = " AND ( LOWER(c.name) "
      + " LIKE CONCAT('%', LOWER(:search), '%') ) ";

private static final String CONTINENT_WHERE_CLAUSE = 
      " AND c.continent = :continent ";

private static final String REGION_WHERE_CLAUSE = 
      " AND c.region = :region ";

private static final String PAGINATION_CLAUSE = " ORDER BY c.code "
      + " LIMIT :size OFFSET :offset ";

The placeholders defined by :<<variableName>> are replaced by the values provided in the Map to the NamedParameterJdbcTemplate. This way we can avoid concatenating the values into the SQL query, thereby avoiding chances of SQL injection. The getCountries() definition would now be as follows:

public List<Country> getCountries(Map<String, Object> params){
    int pageNo = 1;
    if ( params.containsKey("pageNo") ) {
      pageNo = Integer.parseInt(params.get("pageNo").toString());
    }
    Integer offset = (pageNo - 1) * PAGE_SIZE;
    params.put("offset", offset);
    params.put("size", PAGE_SIZE);
    return namedParamJdbcTemplate.query(SELECT_CLAUSE
        + " WHERE 1 = 1 "
        + (!StringUtils.isEmpty((String)params.get("search")) 
            ? SEARCH_WHERE_CLAUSE : "")
        + (!StringUtils.isEmpty((String)params.get("continent")) 
            ? CONTINENT_WHERE_CLAUSE : "")
        + (!StringUtils.isEmpty((String)params.get("region")) 
            ? REGION_WHERE_CLAUSE : "")
        + PAGINATION_CLAUSE,
        params, new CountryRowMapper());
  }

Next is to implement the getCountriesCount method, which is similar to getCountries, except that it returns the count of entries matching the WHERE clause without the pagination applied. The implementation is as shown in the following code:

public int getCountriesCount(Map<String, Object> params) {
  return namedParamJdbcTemplate.queryForObject(
          "SELECT COUNT(*) FROM country c" 
        + " WHERE 1 = 1 "
        + (!StringUtils.isEmpty((String)params.get("search")) 
            ? SEARCH_WHERE_CLAUSE : "")
        + (!StringUtils.isEmpty((String)params.get("continent")) 
            ? CONTINENT_WHERE_CLAUSE : "")
        + (!StringUtils.isEmpty((String)params.get("region")) 
            ? REGION_WHERE_CLAUSE : ""),
        params, Integer.class);
  }

Then we implement the getCountryDetail method to get the detail of the country, given its code, as follows:

public Country getCountryDetail(String code) {
  Map<String, String> params = new HashMap<String, String>();
  params.put("code", code);

  return namedParamJdbcTemplate.queryForObject(SELECT_CLAUSE 
      +" WHERE c.code = :code", params, 
      new CountryRowMapper());
}

Finally, we define the method to allow editing the country information, as shown in the following code:

public void editCountryDetail(String code, Country country) {
    namedParamJdbcTemplate.update(" UPDATE country SET "
        + " name = :name, "
        + " localname = :localName, "
        + " capital = :capital, "
        + " continent = :continent, "
        + " region = :region, "
        + " HeadOfState = :headOfState, "
        + " GovernmentForm = :governmentForm, "
        + " IndepYear = :indepYear, "
        + " SurfaceArea = :surfaceArea, "
        + " population = :population, "
        + " LifeExpectancy = :lifeExpectancy "
        + "WHERE Code = :code ", 
        getCountryAsMap(code, country));
  }

The previous method uses a helper method that builds a Map object, by using the data present in the Country object. We need the map, as we'll be using it as a parameter source for our namedParamJdbcTemplate.

The helper method has a simple implementation, as shown in the following code:

private Map<String, Object> getCountryAsMap(String code, Country country){
    Map<String, Object> countryMap = new HashMap<String, Object>();
    countryMap.put("name", country.getName());
    countryMap.put("localName", country.getLocalName());
    countryMap.put("capital", country.getCapital().getId());
    countryMap.put("continent", country.getContinent());
    countryMap.put("region", country.getRegion());
    countryMap.put("headOfState", country.getHeadOfState());
    countryMap.put("governmentForm", country.getGovernmentForm());
    countryMap.put("indepYear", country.getIndepYear());
    countryMap.put("surfaceArea", country.getSurfaceArea());
    countryMap.put("population", country.getPopulation());
    countryMap.put("lifeExpectancy", country.getLifeExpectancy());
    countryMap.put("code", code);
    return countryMap;
}

Let's write our JUnit test for the CountryDAO class, which we haven't created yet. Create CountryDAOTestclass into the com.nilangpatel.worldgdp.test.dao package as follows: 

@RunWith(SpringRunner.class)
@SpringJUnitConfig( classes = {
      TestDBConfiguration.class, CountryDAO.class})
public class CountryDAOTest {

  @Autowired CountryDAO countryDao;

  @Autowired @Qualifier("testTemplate")
  NamedParameterJdbcTemplate namedParamJdbcTemplate;

  @Before
  public void setup() {
    countryDao.setNamedParamJdbcTemplate(namedParamJdbcTemplate);
  }

  @Test
  public void testGetCountries() {
    List<Country> countries = countryDao.getCountries(new HashMap<>());
    //AssertJ assertions
    //Paginated List, so should have 20 entries
    assertThat(countries).hasSize(20);
  }

  @Test
  public void testGetCountries_searchByName() {
    Map<String, Object> params = new HashMap<>();
    params.put("search", "Aruba");
    List<Country> countries = countryDao.getCountries(params);
    assertThat(countries).hasSize(1);
  }


  @Test
  public void testGetCountries_searchByContinent() {
    Map<String, Object> params = new HashMap<>();
    params.put("continent", "Asia");
    List<Country> countries = countryDao.getCountries(params);

    assertThat(countries).hasSize(20);
  }

  @Test
  public void testGetCountryDetail() {
    Country c = countryDao.getCountryDetail("IND");
    assertThat(c).isNotNull();
    assertThat(c.toString()).isEqualTo("Country(code=IND, name=India, "
        + "continent=Asia, region=Southern and Central Asia, "
        + "surfaceArea=3287263.0, indepYear=1947, population=1013662000, "
        + "lifeExpectancy=62.5, gnp=447114.0, localName=Bharat/India, "
        + "governmentForm=Federal Republic, headOfState=Kocheril Raman Narayanan, "
        + "capital=City(id=1109, name=New Delhi, countryCode=null, "
        + "country=null, district=null, population=null), code2=IN)");
  }

  @Test public void testEditCountryDetail() {
    Country c = countryDao.getCountryDetail("IND");
    c.setHeadOfState("Ram Nath Kovind");
    c.setPopulation(1324171354l);
    countryDao.editCountryDetail("IND", c);

    c = countryDao.getCountryDetail("IND");
    assertThat(c.getHeadOfState()).isEqualTo("Ram Nath Kovind");
    assertThat(c.getPopulation()).isEqualTo(1324171354l);
  }

  @Test public void testGetCountriesCount() {
    Integer count = countryDao.getCountriesCount(Collections.EMPTY_MAP);
    assertThat(count).isEqualTo(239);
  }
}

There are a few things to note about configuring JUnit tests using the Spring test framework from the following test, including the following:

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

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);
  }
}

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

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;
}

We need to fetch the GDP data from WorldBank API. As we discussed, it is REST end point, where we have to send few parameters and will get the response. For this, we will use RestTemplate to make REST call. The following is the definition for the com.packt.external.WorldBankApiClientclass, which is used to invoke the World Bank API and process its response to return List<CountryGDP>:

@Service
public class WorldBankApiClient {

  String GDP_URL = "http://api.worldbank.org/countries/%s/indicators/NY.GDP.MKTP.CD?"
      + "format=json&date=2008:2018";

  public List<CountryGDP> getGDP(String countryCode) throws ParseException {
    RestTemplate worldBankRestTmplt = new RestTemplate();
    ResponseEntity<String> response
      = worldBankRestTmplt.getForEntity(String.format(GDP_URL, countryCode), String.class);

    //the second element is the actual data and its an array of object
    JSONParser parser = new JSONParser();
    JSONArray responseData = (JSONArray) parser.parse(response.getBody()); 
    JSONArray countryDataArr = (JSONArray) responseData.get(1);

    List<CountryGDP> data = new ArrayList<CountryGDP>();
    JSONObject countryDataYearWise=null;
    for (int index=0; index < countryDataArr.size(); index++) {
      countryDataYearWise = (JSONObject) countryDataArr.get(index);

      String valueStr = "0";
      if(countryDataYearWise.get("value") !=null) {
        valueStr = countryDataYearWise.get("value").toString();
      }
      String yearStr = countryDataYearWise.get("date").toString();
      CountryGDP gdp = new CountryGDP();
      gdp.setValue(valueStr != null ? Double.valueOf(valueStr) : null);
      gdp.setYear(Short.valueOf(yearStr));
      data.add(gdp);
    }
    return data;
  }
}