Our simple queries were able to use the "named queries" approach of Spring Data JPA. This is very useful for simple "by field" queries and can be used quite extensively to create more complicated multi-field, aggregated and ordered queries.
However, sometimes, it is easier to write a method name that makes more sense and separate the query. An example is our search method which compares the search term to multiple fields.
In this tutorial, we'll introduce the @Query annotation to assign a query to the method.
JPQL is a SQL-like language that works against the defined JPA entity model.
Let's add the method to our repository.
In CharityRepositoryJPA.java
@Query("select c from Charity c where c.acronym = ?1 or c.registration = ?1 or c.name like %?1% or c.missionStatement like %?1%")
List<Charity> findBySearchTerm(String search);
The @Query annotation allows us to specify the JPQL query to use when the method is called.
The ?1 is a positional parameter. That is, the first parameter will be substited into the query. Note how JPQL supports like comparisons.
Let's test that new method.
We'll use a parameterised test.
In CharityDataJPATests.java
@ParameterizedTest
@CsvSource({"heart,1","cancer, 3", "dogs, 0", "cats, 2", "prevention, 6"})
public void shouldGetCharitiesFromSearch(String search, Integer count) throws Exception{
List<Charity> charityList = charityRepository.findBySearchTerm(search);
assertEquals(count, charityList.size());
}
Do check the repo as some typos in the test data have been corrected.
We can write OO code and OO queries to converse with a relational DB.
The nice thing about JPA is that the framework will handle the SQL generation and that allows our application to be portable.
However, there will be times when we are committed to particular database, or when we want to take advantage of particular features in the database.
For example, the Oracle database has useful text searching features that enable soundalike ("Smith", "Smyth") queries as well as other fuzzy searches.
@Query supports a nativeQuery attribute and if this is set to true, then the query string can be written as SQL.#
JPA is a very powerful technology with many options for writing queries. In addition to named queries and @Query annotations, there is also a query-by-example API. JPA can also support ‘projections' that allow selective fields to be returned.