Defining the data access layer – Spring JDBC Template

We have the model classes that reflect the structure of the data in the database that we obtained from the World Bank API. Now we need to develop a data access layer that interacts with our MySQL and populates the data stored in the database into instances of the model classes. We will use the Spring JDBC Template to achieve the required interaction with the database.

First, we need the JDBC driver to connect any Java application with MySQL. This can be obtained by adding the following dependency and version property to our pom.xml:

    <properties>
        <java.version>1.8</java.version>
        <lombok.version>1.16.18</lombok.version>
        <hibernate.validator.version>6.0.2.Final</hibernate.validator.version>    
        <mysql.jdbc.driver.version>5.1.44</mysql.jdbc.driver.version>
    </properties> 
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql.jdbc.driver.version}</version>
    </dependency>

Wherever you see <something.version>1.5.6</something.version>, it should go within the <properties></properties> tag. Will not mention this repeatedly. This is for keeping the versions of libraries used in one place, making it easy to maintain and look up.

Anything that comes as <dependency></dependency> goes within the <dependencies></dependencies> list.

Now we need to add a dependency to the Spring core APIs, as well as the Spring JDBC APIs (which contain the JDBC Template) to our pom.xml. A brief intro about these two dependencies is as follows:

1. Spring core APIs: It provides us with core Spring features such as dependency injection and configuration model
2. Spring JDBC APIs: It provides us with the APIs required to create the DataSource instance and interact with the database

Since this is a sample application, we aren't using Hibernate or other ORM libraries because they provide lots of functionalities apart from basic CRUD operations. Instead, we will write SQL queries and use them with JDBC Template to make things simpler. 

 The following code shows the dependency information for the two libraries:

<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-core</artifactId>
   <version>${spring.version}</version>
</dependency>
<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>${spring.version}</version>
</dependency>

Along with the preceding two dependencies, we need to add a few more Spring dependencies to assist us in setting up Java-based configurations using annotations (such as @bean,  @Service, @Configuration, @ComponentScan, and so on) and dependency injection using annotations (@Autowired). For this, we will be adding further dependencies as follows:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-beans</artifactId>
    <version>${spring.version}</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>${spring.version}</version>
</dependency>

Defining the JDBC connection properties

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

We will follow the convention of placing all our configuration classes in com.nilangpatel.worldgdp.config and any root configuration will go in the com.nilangpatel.worldgdp package. 

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 DBConfiguration data 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:

• @Configuration: This is to indicate to Spring Framework that this class creates Java objects that contain some configuration
• @Bean: This is method-level annotation, used to indicate to Spring Framework that the method returns Java objects whose life cycle is managed by Spring Framework and injected into places where its dependency is declared
• @Value: This is used to refer to the properties defined in the application.properties, which are resolved by the PropertySourcesPlaceholderConfigurer bean defined in the PropertiesWithJavaConfig class

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.

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.config package 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. 

We have added few other dependencies specific to JUnit. You can refer to them by downloading the source code.

Defining the RowMapper

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

Designing the CountryDAO 

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:

1. Select clause:

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

2. Search where clause:

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

3. Continent filter where clause:

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

4. Region filter where clause:

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

5. Pagination clause:

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

In all of the previous DAO method implementations, we have made use of the CountryRowMapper we defined in the Defining the RowMapper section.

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 CountryDAOTest class 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:

• @RunWith is used to replace the JUnit's test runner with a custom test runner, which in this case, is Spring's SpringRunner. Spring's test runner helps in integrating JUnit with the Spring test framework.
• @SpringJUnitConfig is used to provide the list of classes that contain the required configuration to satisfy the dependencies for running the test.

Many people who choose ORM frameworks may feel that writing complicated SQL queries like this is awkward. However, from the next chapter onward, we'll start using the Spring Data framework to make an interaction with various data sources; the database is one of those accessed with the Spring Data JPA. Here, we wanted to show how the Spring JDBC offering interacts with the database.

Designing the CityDAO

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

Designing the CountryLanguageDAO

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

Designing the client for World Bank API

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.WorldBankApiClient class, 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;
  }
}