Up to this point, we have not had a database in our overall architecture. This is unrealistic, but has allowed us to focus on the front-end and the service layer.
Spring Boot provides many options for persistence. It provides different abstractions for relational databases as well as NoSQL options (such as MongoDB, Cassandra, etc).
As well an understanding the mechanics of the code, remember to keep a focus on the design of the application and to keep evaluating the design choices made by the framework designers. If you don't like their approach, do ask yourselves why you think they did it that way. Don't dismiss an idea without seeking to understand it.
The first thing we need is to include Spring's support for relational databases using JDBC. JDBC stands for Java Database Connectivity and is a standard API for all relational database interaction. It supports the sending of querys as statements (which have a SQL injection risk) and prepared statements (using parameters). It can call stored procedures. It provides a way of accessing the results. More fundamentally, it provides a way of making the initial connection to the database (typically over a network) and has expectations on how the developer manages that connection.
Spring provides a JDBC template that allows the developer to focus on the query to send in and the mapping of the returned data, removing the repetitive, connection-handling code from the developer's load.
To add support for JDBC, we need to add a line to build.gradle.
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
runtimeOnly 'com.h2database:h2'
The first line adds the Spring JDBC support to the project. This gives us access to Spring's JdbcTemplate class as a bean (which means we can inject it).
The second line draws in the support for a specific database. In this case, we will draw in support for a H2 in-memory database. There are separate dependencies for each database (MySQL/Maria, Oracle, DB2, etc).
We need to configure H2 so that it can be auto-started by the application. Since it is in-memory, it will be started when the application starts, and all data will be lost when the application ends. This makes it very useful for testing as we'll have a consistent dataset each time.
Let's add to application.properties.
#turn on the console for development purposes
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
#set credentials explicitly
spring.datasource.username=sa
spring.datasource.password=
#make clear which data to load into h2. the value matches the suffix on the data.sql and schema.sql files
spring.datasource.platform=h2
It is useful to be able to look at the database during development. If using MySQL/MariaDB, then MySQL Connector or IntelliJ's built-in support are useful. With H2, we can start a browser-based console by including spring.h2.console.enabled=true in the properties. The console can be accessed using (http://localhost:8080/h2-console) (adjust accordingly if you've changed server.port in the properties).
We also want to set the credentials and url as these will be asked for when the console opens.
Finally, we will want to initialise the database with specific data and the data may be different for our development H2 database than it might be for a production MySQL database. So we will set the datasource platform to "h2". We will use this setting when naming our initialisation files.
To initialise the database, we need to both create the schema and then populate the tables. For this we requires two files; schema-?.sql and data-?.sql. The ‘-<?>' can be left out or can be replaced with the datasource platform identifier.
In our case, we are going to use the datasource platform provider identifier which we set to "h2". Hence our files are called schema-h2.sql and data-h2.sql.
The schema file defines the "shape" of the database. For now, we only create if not already present. We may wish to consider explicitly deleting the tables first to ensure the database is exactly as required.
In schema-h2.sql (apologies for the weird formatting in Gitlab - that's IntelliJ's choice.)
SET MODE MySQL;
SET IGNORECASE=TRUE;
-- -----------------------------------------------------
-- Table `charity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `charity`
(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` VARCHAR( 45) NOT NULL,
`name` VARCHAR( 100) NOT NULL,
`mission` VARCHAR( 500) NOT NULL,
`logo_file_name` VARCHAR( 500) NULL,
`acronym` VARCHAR( 30) NULL,
is_active VARCHAR( 1) NULL,
PRIMARY KEY( `id`))
ENGINE = InnoDB;
For now, we'll just look at charities. Trustees will come later. Notice the underscore naming convention. Underscores replace camel case conventions in the database. This is just a choice, but it will be important later on.
The schema will be empty, so for testing purposes, we'll introduce some test data.
In test-h2.sql*.
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('1', '12345678', 'National Society for Preventation of Cruelty to Children', 'Child protection', 'nspcc');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('2', '12312433', 'Cancer Research UK', 'Cancer Research and prevention', 'cruk');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('3', '12279188', 'Cancer Research Wales', 'Cancer Research and prevention', 'crw');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('4', '12245943', 'Amnesty International', 'Human Rights Upkeep', 'amnesty');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('5', 'S12212698', 'Cancer Scotland', 'Cancer Research and prevention', 'cancer_scotland');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('6', '12179453', 'British Heart Foundation', 'Heart Disease Research and prevention', 'bhf');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('7', '12146208', 'Kidney Research UK', 'Kidney Disease Research and preventation', 'kruf');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('8', '12112963', 'Oxfam', 'Worldwide aid', 'oxfam');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('9', '12079718', 'Greenpeace', 'Environmental protection and upkeep', 'greenpeace');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('20', '88888888', 'Cats Protection League (Cardiff)', 'Helping cats', 'cpl_cardiff');
INSERT INTO `charity` (`id`, `registration_id`, `name`, `mission`, `acronym`)
VALUES ('21', '88888888', 'Cats Protection League (Swansea)', 'Helping cats', 'cpl_swansea');
update charity
set logo_file_name = acronym
;
update charity
set is_active='Y'
;
Again, we could (maybe should) delete all data first before adding them. We will adopt this practice later (when we start thinking about automated tests).
For now, we insert some data, then set the logo_file_name to be the same as the acronym and make all of the charities active.
Our previous repository implementation was a complete mock using an internal map to hold charities. We want remove this completely at first, but we will change one method to use the database. Hence the code will look like a mixture of two approaches. Focus on the findAll() method and don't try testing other scenarios. The changes are not integrated.
Add a field to the repository to refer to the template. We type this field as JdbcOperations which is an interface provided by Spring. It is implemented by JdbcTemplate. Later on, we will change the type to JdbcTemplate because another part of Spring requires that. Personally, I think this is a bug in Spring because the pattern is to refer to interfaces, but it's now a convention to use JdbcTemplate directly, but it's nice to show that both ways work.
Then, amend the constructor to set the template. Spring will inject this for you.
private final JdbcOperations jdbc; //add the field...
//then get Spring to inject it...
public MockCharityRepository(JdbcOperations jdbcOperations) {
jdbc = jdbcOperations;
//...
}
We will also use a Mapper to map from the database results into objects. Add a field of type RowMapper to the repository. This is another provided class that can be typed (using generics) to the target class. Hence, you can read this as "row mapper to charity".
We then use a lambda to initialise it in the constructor.
private final RowMapper<Charity> charityMapper; //declare the field...
//..in the constructor...
charityMapper = (rs, i) -> new Charity(
rs.getString("name"),
rs.getString("registration_id"),
rs.getString("acronym"),
rs.getString("logo_file_name"),
rs.getString("mission"),
true
);
The mapper lambda takes the result set (the data returned from a JDBC statement) and the index number.
We want to change the findAll() method to use the database. Given the changes so far, we have a populated database, access to a template and a suitable mapper. So, what we need to do, is call the right method on the template, pass the correct SQL and then returned the mapped objects.
In MockCharityRepository.java.
public List<Charity> findAll() {
return jdbc.query(
"select id, acronym, name, mission, logo_file_name, registration_id from charity",
new Object[]{},
charityMapper);
}
In this method, the query method is called on the template. The first parameter is the query to send to the database. The 2nd parameter are the parameters to pass into the query (here there are none so we pass an empty array of Objects) and we pass in the mapper. The template will run the SQL, receive the result set, then pass each row to the mapper to create a Charity object, then will collect them back into a List.
There should be some defensive exception handling, but we will return to that later.
The final change to make is to replace our Singleton implementation of the repository with dependency injection. This replaces our singleton with Spring's singleton as, by default, Spring components are only instantiated once.
To do this, we annotate the repository and we make the constructor public.
@Repository
public class MockCharityRepository {
We then change the service class to inject the repository (as it is now a bean).
@Service
public class CharitySearchImpl implements CharitySearch {
private MockCharityRepository mockCharityRepository;
public CharitySearchImpl(MockCharityRepository aRepo) {
mockCharityRepository = aRepo;
}
Finally, we need to adjust the test code to inject the service.
public class CharitySearches {
@Autowired
private CharitySearch charityService;
@Test
public void shouldGet4Charities() throws Exception {
List<CharityDTO> charityDTOList = charityService.findAll();
assertEquals(4, charityDTOList.size());
}
}
We can now inject the service, which will have a repository injected into it which in turn will have a JDBC template injected into it and call the method as before. Now, it uses the database rather than the map.
You can run the amended automated test and try out the full list of charities from the user interface. Try changing the test data and reloading the server to see the changes being reflected.
We introduced a database. The changes have been localised to the DB and repository tiers (in the main) with only a small change to dependency injection in the service layer. There was no change to the methods of the service layer and the controllers are completely unaware of the change.