Database queries with Spring Boot

 

Getting started in Spring Boot, Part 4 Database Queries with Spring Boot

We have presented basic find methods such as findById() or findAll() in the previous part of this series. In practice, database queries are usually more complex, people will be searched for, for example, by name and date of birth. Spring Data offers various possibilities, which we present here.

Companies on the topic

Spring Data offers various possibilities for database queries, which we present in this post.Spring Data offers various possibilities for database queries, which we present in this post.

(Image: Spring.io)

At the beginning we extend the test data to have more attributes available. The entity person is given a date of birth and a status. Getter and setter are not listed here for space reasons:

@Entitypublic class Person {   @Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
private String firstname;
private String lastname;
private LocalDate birthdate;
private Integer status;
}

Also the inserts in data.sql will be expanded accordingly:

INSERT INTO person (id, firstname, lastname, birthday, status) VALUES (-1, 'Max', 'Mustermann', '1976-02-21', 0);
INSERT INTO person (id, firstname, lastname, birthday, status) VALUES (-2, 'Erika', 'Mustermann', '1975-09-12', 1);

Derived Queries: queries with conventions

First, search for the first name of a person. For this purpose, a new method findByFirstname is inserted into the repository interface:

public interface PersonRepository extends CrudRepository<Person, Long>{   List<Person> findByFirstname(String firstname);
}

This method can now be used, for example, in the controller to search for suitable entries in the database:

List<Person> persons = (List<Person>) personRepository.findByFirstname("Max");

As expected, Max Mustermann is the only person to be found … but wait a minute – we haven’t implemented the method yet?! Here spring magic strikes again. If the method name meets certain criteria, Spring Data JPA derives the functionality from the name and automatically makes the implementation available at runtime.

These queries are therefore called “derived”, i.e. derived queries. It is crucial to comply with the naming conventions. The method name starts with findBy (there are a few alternatives such as readBy). The variable names present in the entity are then listed, starting with a capital letter. Multiple search criteria are separated by And or Or.

The best way to understand this is probably by means of an example. To find a person by their first and last name, the following query can be used:

List<Person> findByFirstnameAndLastname(String firstname, String lastname);

In the standard case, a comparison for equality is carried out using equals. However, there are a number of other operators that can be used alternatively:

For example, to find all persons who have a certain string in their first name, the method with the containing operator looks like this:

List<Person> findByFirstnameContaining(String str);

The search results in derived queries can also be sorted using the naming convention. To do this, simply add the addition OrderBy, followed by the sorting criterion and the sorting direction (asc for ascending or desc for descending). Persons with a certain surname, sorted by date of birth, can be found as follows:

List<Person> findByLastnameOrderByBirthdateAsc(String firstname);

There is also a limit on the number of search results. To do this, the label First, followed by the number of desired results between find and by is packed. In the following example, only the first two hits are returned:

List<Person> findFirst2ByLastnameOrderByBirthdateAsc(String firstname);

@Query: Formulate queries yourself

Derived Queries are among the real killer features of Spring Data, with them you can save a lot of time and work. But they reach their limits when things get more complex. In addition, there are certainly cases where the name of the method is intended to express a technical connection, which may not be so easily revealed by the naming convention.

In these cases, a method signature in the repository interface with a freely assigned name can be marked with the annotation @Query. In the value attribute of the annotation, the query is then formulated by hand. In principle, two different query languages are available: classic SQL or JPQL. JPQL is the Java Persistence Query Language, the successor of the Hibernate Query Language.

For example, to query the status of a person, the repository method in JPQL looks like this:

@Query("SELECT p FROM Person p WHERE p.status = 1")
List<Person> findAllActivePersons();

Of course, parameters can also be used here:

@Query("SELECT p FROM Person p WHERE p.firstname = ?1")
Person findPersonByFirstname(String firstname);

For JPQL, the properties listed, such as the name Person or the attribute status, refer to the entity object model and not to the database table. This is especially important if the names in the Java class and in the database are not identical.

The same situation in SQL can be formulated as follows, where the database table Person and the table column status are meant here:

@Query(value = "SELECT * FROM Person p WHERE p.status = 1", nativeQuery = true)
List<Person> findAllActivePersons();

Again, it is possible to use parameters with question mark and index. Important: Since JPQL is the default query language, the NativeQuery attribute must be set to true when using SQL.

Which of the two languages is used is a bit of a matter of taste. JPQL is very powerful and probably more suited to the Java outstaffing developers, who are at home in the object world. SQL is more common and many queries are already available in SQL.

In the next part of the series, the Spring Developer Tools will be presented – a kind of toolbox that will make our work a little easier. Stay tuned!

(ID:47038000)

Ready to see us in action:

More To Explore

IWanta.tech
Logo
Enable registration in settings - general
Have any project in mind?

Contact us:

small_c_popup.png