In the previous tutorial, we introduced database integration and a very simple query. In this tutorial, we introduce how to pass parameters into select queries.
Since the repository is no longer a mock repository, let's rename it to CharityRepository. Use IntelliJ refactoring to update the service class as well.
To implement a find by acronym search, we need a method that will return an Optional object and which will accept a parameter.
The template provides many methods. One that fits our needs is queryForObject since it returns the matching object as long as one row is returned from the query. So this current code is not very robust as it will throw an exception if the acronym does not exist. This is fixed in the next tag.
If you are observant, you may also spot that this method is deprecated. In the next tag, we replace it with the new preference.
However, it is still a reasonable example. Rather than pass an empty array of objects, we now pass the acronym and we provide a where clause with a "?" to indicate the position of the parameter. We can have many parameters and they will be substituted in order.
In CharityRepository.java.
public Optional<Charity> findByAcronym(String acronym) {
return Optional.of(
jdbc.queryForObject(
"select id, acronym, name, mission, logo_file_name, registration_id from charity where acronym=?",
new Object[]{acronym},
charityMapper)
);
}
This is a more interesting question. Let's look at the code.
In CharityRepository.java.
public List<Charity> findBySearchTerm(String search) {
return jdbc.query(
"select id, acronym, name, mission, logo_file_name, registration_id from charity where acronym=? or name like ? or registration_id = ?",
new Object[]{search, "%" + search + "%", search},
charityMapper);
}
Our previous search (on the map) was done across multiple fields, so our query now has 3 parameters and we use "or" to link the clauses together.
We use the database "like" comparison so we need to wrap the search parameter with "%"s to get the full benefit of this.
The "like" functionality is fairly common across all relational databases so this implementation will stay portable if we move from H2 to MySQL or similar.
However, some databases offer specific functions. Oracle, for example, offers functions to search for "sound-alike" matches (e.g. Smith and Smyth). This may not be portable to other databases. For those other databases, the work may have to be done in the Java tier which could be expensive in terms of data transfer across the network.
These are design decisions and ideally they should be made consistently across the codebase and/or exemptions from the decision should be clearly separated in the code.
All of the searches should now work consistently and can be tested via the UI.
The save functionality has not been migrated, so don't test that.
We've introduced parameters and in so doing, opened up a broader discussion on design and the allocation of responsibility across tiers.
There are no right answers to the design questions, but there are standard forces at play (portability, data volumes, etc) and standard disciplines to follow (document your decisions, make exemptions clear, etc).