Database queries with Spring Boot

Getting into Spring Boot, part 4 Database queries with Spring Boot

Basic find-methods such as findById() or findAll (), we have presented in the previous part of this series. In practice, database queries are usually more complex, people will search for, for example, with the name and date of birth. Spring Data provides various options that we present here.

Companies

Spring Data offers for database queries in various ways, which we will introduce in this post.Spring Data offers for database queries in various ways, which we will introduce in this post.

(Image: Spring.io)

To expand the beginning we have the test data more attributes. The entity Person is given a birth date and a Status. Getter and Setter are not listed here for reasons of space:

@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 the data.sql will be extended 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 of all, should be the first name of a Person looking for. 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 be used, for example, in the Controller, to search for matching entries in the database:

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

As expected, is found as the only Person John DOE … but wait a Moment – we have but the method is still not implemented?! Here are Spring-magic strikes again. If the method name matches a specific set of criteria, derived Spring Data JPA from the name, the functionality, and the implementation at runtime automatically.

These queries are called “derived”, so the derived Queries. The key is the name of the conventions to be adhered to. The method name starts with findBy (there are a couple of Alternatives such as readBy). Then the Entity is present, variable names beginning with uppercase letters. Multiple search criteria can be separated by And or or.

To understand this, probably with an example. In order to find a Person on the basis of the first and last name, the following Query can be used:

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

In the standard case, it is performed a comparison for equality using the equals. But there are a number of other operators, which can alternatively use:

To find about all the people that have a specific sequence of characters in the first name of the method looks with Containing Operator as follows:

List<Person> findByFirstnameContaining(String str);

The search results Derived Queries with the help of the naming Convention also sort. This includes the addition OrderBy is easy, followed depends on the sort criteria and sort direction (asc for ascending or desc for descending order) of. People with a particular surname, in ascending order according to the date of birth, sorted, you can find so follow these steps:

List<Person> findByLastnameOrderByBirthdateAsc(String firstname);

Also a limit to the number of search results is provided. The label First, followed by the desired number of results between find and by Packed. In the following example, only the first two hits are returned:

List<Person> findFirst2ByLastnameOrderByBirthdateAsc(String firstname);

@Query: queries formulate yourself

Derived Queries belong to the real Killer Features of Spring Data with you a lot of time and work you can save. You come to their limits, if it is more complex. In addition, there are indeed cases where the name of the method a professional context, not expressed to be, the taps may not be as easily by the naming Convention.

In these cases, a method signature in the Repository Interface with a freely chosen name with the Annotation @Query code you can draw. In the Value attributes of the Annotation you and then formulate the query by Hand. In principle, two types of query are languages available: classic SQL or JPQL. JPQL is Java Persistence Query Language, is the successor to the Hibernate Query Language.

About the Status of a Person, the Repository method in JPQL as follows:

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

Of course the parameters can be used:

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

The listed properties, such as the Person’s Name, or the attribute status are related in JPQL on the Entity object model to the database table. This is particularly important when the name of the Java class in the database are the same.

The same issue in SQL can be formulated as follows, then here is the database table Person and the table status column refers to:

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

Also here it is possible to use parameters with question mark and Index. Important: Because JPQL, the Standard query language must be set when you use SQL, you use the attributes native query to true.

Which of the two languages is used, it is a bit of a matter of taste. JPQL is very powerful and is likely to be the Java developers that are in the object-world. SQL is more widely used, and many of the queries are already available in SQL.

In the next part of the series is the Spring Developer Tools will be presented a kind of tool box which will facilitate the outsourcing work a little. 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