Whatever you do, do NOT try to reinvent the wheel

Pain when accessing relational DB? Try JPA Repositories

Petar Pirovski
JPA Repositories

A note pinned to a cork board image via Shutterstock

Accessing a relational database data from an object-oriented application can be a very boring task, especially when things are simple and “just” have to be done. Instead of focusing on what we need, we have to focus on how to extract it. Fortunately, there are a lot of frameworks for Object Relational Mapping (ORM), that help a lot with these tasks.

Writing queries, no matter JPQL, HQL, or plain SQL, appending params, keeping an eye on the syntax have always been not very motivating tasks for me. Fortunately, there are a lot of frameworks for Object Relational Mapping (ORM), that help a lot with these tasks. Also, many developers are, or have been tempted to make up their own ORM helpers or even whole frameworks.

Well, reinventing the wheel is rarely a good approach. So let’s use some old, tested in battle, frameworks,  that have lived long enough to become almost bug-free. Not to forget that if we need help, the more popular the framework is, the bigger the community in StackOverflow. But which one to use? I have worked with many of them, including pure JDBC and what I can tell for sure is “don’t use EJB2.” 

My personal favourite is Spring JPA Repositories. It strongly reduces the amount of boilerplate code and it’s simple, yet very powerful when needed. So, let’s start with the examples.

Our example model is:

Screen Shot 2016-12-21 at 3.34.27 PM

And we have some classes with fields:

public class Country {
   private Long id;
   private String name;
   private List<City> cities;
   //... getters, setters with JPA annotations

public class City {
   private Long id;
   private String name;
   private String postCode;
   private Integer population;
   private Country country;
   private List<Street> streets;
   //... getters, setters with JPA annotations

public class Street {
   private Long id;
   private String name;
   private City city;
   //... getters, setters with JPA annotations

Configuration is omitted for simplicity, but it’s not a big deal – 2-3 annotations, 2-3 configuration beans and the spring magic is set.

Now we have this interface provided by Spring:

public interface CrudRepository<T, ID extends Serializable>
       extends Repository<T, ID> {

   <S extends T> S save(S entity);
   T findOne(ID primaryKey);
   Iterable<T> findAll();
   Long count();
   void delete(T entity);
   boolean exists(ID primaryKey);
   // ...

T is the type of any our entities, ID is Long in this example, methods speak for themselves. We have to extend this interface, to create a repository for concrete entity, for example:

interface CityRepository extends CrudRepository<City, Long> {}

We can inject CityRepository in our services, or anywhere we want and call provided methods, with no need to implement anything.
What about if we want to make slightly more complicated query – we want to have a method that finds all cities in a given Country.

interface CityRepository extends CrudRepository<City, Long> {
   List<City> findByCountry(Country country);

Well, we named it good, but who is going to implement it, who is doing the hard work? Do I have to care who is doing it, if it works? I wanted my cities in the country and I got them. Of course, the hard work is done by Spring infrastructure and if we want we can dig into details, but that’s not the point. The point is we can extract data from database only by writing camelcase method names and if we want to start fast this is very useful. Let’s see some more examples:

If we want to order cities by name we can write the following method:

List<City> findByCountryOrderByName(Country country);

or, for example, the cities with more than given number people:

List<City> findByPopulationGreaterThan(Integer population);

or name containing some string:

List<City> findByNameContainig(String nameSubstring);

we can combine criteria like that:

List<City> findByNameAndCountry(String name, Country country);

we can do even this:

interface StreetRepository extends CrudRepository<Street, Long> {
List<Street> findByCityCountryNameAndCityNameOrderByCityNameAscNameDesc(
String countryName, String cityName);

When expressivity of the method names is not enough, the @Query annotation can be used. For example:

@Query(value="select c from City c where c.name = ?1")
List<City> someMethodName(String name);

?1 matches the first parameter of the method, but when there are many params there is a risk of confusion, so also named params are supported:

@Query(value="select c from City c where c.name = :name or c.postCode = :code")
List<City> findByNameOrCode( 
@Param("code")String postCode, @Param("name")String name);

Native queries are also possible:

@Query(value="SELECT * FROM cities WHERE name = ?0", nativeQuery = true)
List<City> findByName(String name);

If we need pagination and sorting, instead of extending CrudRepository, we can extend PagingAndSortingRepository, which has built-in methods accepting Pageable and Sort objects.

Page<City> cities = cityRepository.findAll(new PageRequest(1, 20));

Well, that’s my favorite framework. It’s easy for beginners, it’s powerful and flexible for the real stuff. It’s not so prone to error since there is not so much code to be written. It’s fun to use.

For further reading:




Petar Pirovski

Petar Pirovski is a Java expert at Dreamix Ltd. He is interested in AI.

comments powered by Disqus