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:
- 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 ";
- Search where clause:
private static final String SEARCH_WHERE_CLAUSE = " AND ( LOWER(c.name) "
+ " LIKE CONCAT('%', LOWER(:search), '%') ) ";
- Continent filter where clause:
private static final String CONTINENT_WHERE_CLAUSE =
" AND c.continent = :continent ";
- Region filter where clause:
private static final String REGION_WHERE_CLAUSE =
" AND c.region = :region ";
- 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.
