Property expressions

We already know how to use the functionality of the Spring CRUD repository, which lets us use the simplest methods of data management. However, that's not enough for real projects. In this topic, we will learn how to make more queries in which property expressions are used. Let's get started!

Setting goals

We are going to develop an application for our fitness center. We will work with our customer base. There is a need to store and process a lot of data about the customers to make our center efficient and earn a lot of money. We are going to work with the H2 database, but the new features covered in this topic work in other databases too. Let's begin with the customer class:

@Entity
public class Customer {
    
    @Id
    @GeneratedValue
    private long id;
    private String name;
    private String surname;
    private String phoneNumber;
    private LocalDate registrationDate;
    private LocalDate subscriptionEndsOn;
    private boolean isSubscriptionActive;

    // constructor, getters, setters
}

@Entity
class Customer(
    @Id
    @GeneratedValue
    var id: Long = 0,
    var name: String = "",
    var surname: String = "",
    var phoneNumber: String = "",
    var registrationDate: LocalDate = LocalDate.now(),
    var subscriptionEndsOn: LocalDate = LocalDate.now(),
    var isSubscriptionActive: Boolean = false
)

SQL allows us to create complicated queries. We can filter the data, sort it, and perform some other data management with the help of SQL. For example, we can select only the customers who have an active subscription and sort them by their last names:

SELECT id, name
FROM customer
WHERE is_subscription_active = true
ORDER BY surname

We can do the same things with the help of Spring Data and use only Java or Kotlin code. Moreover, we don't need to implement the methods for data management: we just define their signatures in the repositories. Spring data will do the rest of the work for us. The repository of our application is empty so far:

public interface CustomerRepository extends CrudRepository<Customer, Long> {
}

interface CustomerRepository : CrudRepository<Customer, Long>

Here is the runner for the new features:

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }

    @Component
    public class Runner implements CommandLineRunner {
        private final CustomerRepository repository;

        public Runner(CustomerRepository repository) {
            this.repository = repository;
        }

        @Override
        public void run(String... args) {
            // work with the repository here
        }
    }
}

@SpringBootApplication
class Application

fun main(args: Array<String>) {
    runApplication<Application>(*args)
}

@Component
class Runner(@Autowired val repository: CustomerRepository) : CommandLineRunner {

    override fun run(vararg args: String) {
        // work with the repository here
    }
}

Find expression

The features of Spring Data let us search the data in the database table by the value of a class field. The name of the method consists of its function (findBy in this case) and the name of a class field, by which we'll search.

Let's find out how to use it in the example. Imagine we want to disable access to customers, whose subscription ends today. So, we need to find these customers by the value of the subscriptionEndsOn field. The repository containing the method needed would look like this:

public interface CustomerRepository extends CrudRepository<Customer, Long> {
    List<Customer> findBySubscriptionEndsOn(LocalDate date);
}

interface CustomerRepository : CrudRepository<Customer, Long> {
    fun findBySubscriptionEndsOn(date: LocalDate): List<Customer>
}

And now our method is ready for use:

List<Customer> customerList = repository.findBySubscriptionEndsOn(LocalDate.now());

val customerList = repository.findBySubscriptionEndsOn(LocalDate.now())

Looks simple, doesn't it? Let's understand how it works. In short, Spring Data parses the method's name into the SQL query. Then it is executed by Hibernate. You can log the queries with the help of the following instruction in application.properties:

spring.jpa.show-sql=true

It works differently with NoSQL databases like MongoDB, but the mechanism remains the same.

Also, you can define the type of the return value. In Java, it's very convenient to use Optional for fields with unique values, in Kotlin you just use nullable types. As you may remember, the default method findById returns Optional. Let's also look at an example. If we want to find the customer by phone number, we may define the following method in the repository:

Optional<Customer> findByPhoneNumber(String phoneNumber);

fun findByPhoneNumber(phoneNumber: String): Customer?

You may use the words get, read, search, query, and stream instead of find in the names of the methods. There is no difference between them.

Delete expression

Spring Data also allows us to delete the table rows with the fields containing a certain value. The mechanism is the same as it was with the find expression. The name of a method consists of deleteBy and the name of a field by which we will delete the data.

Imagine that we want to conduct an audit of our database and delete all the users whose subscription is not active. We need to define the following method in the repository:

void deleteByIsSubscriptionActive(boolean isActive);

fun deleteByIsSubscriptionActive(isActive: Boolean)

Now we have everything to reach our goal:

repository.deleteByIsSubscriptionActive(false);

repository.deleteByIsSubscriptionActive(false)

But if we try to run it, we will get an exception because this method requires a transaction. So we need to use the @Transactional annotation. You can use it in the repository before the method signature:

@Transactional
void deleteByIsSubscriptionActive(boolean isActive);

@Transactional
fun deleteByIsSubscriptionActive(isActive: Boolean)

Another way is to annotate the method or class in which we are going to use our custom delete. Why did everything work when we used default CRUD operations? The thing is that these methods are already annotated with @Transactional under the hood.

Also, our custom delete can return some values. If we define long in the signature, we will get the number of deleted rows. We can return a List of deleted objects as well. In the documentation or some other projects, you can find the word "remove" instead of "delete" in methods' names. Don't worry — there is no difference between them.

Exist and count expressions

Also, with the help of Spring Data, we can check whether there are any objects in our database that hold a specific value. The names of these methods begin with existsBy and they return a boolean. Let's get down to an example. If we want every user to have a unique phone number, we should check the uniqueness before registration. First, we need a method to find customers by phoneNumber in the repository:

boolean existsByPhoneNumber(String phoneNumber);

fun existsByPhoneNumber(phoneNumber: String): Boolean

We may use this method for our goal as follows:

if (repository.existsByPhoneNumber("1234")) {
    throw new UniqueConstraintException("Phone number already taken");
}

if (repository.existsByPhoneNumber("1234")) {
    throw UniqueConstraintException("Phone number already taken")
}

You can count the number of objects with a certain property value with the help of methods that start with countBy . For instance, it is always useful to know how many customers with an active subscription you have. The following method will help you with that:

int countByIsSubscriptionActive(boolean isActive);

fun countByIsSubscriptionActive(isActive: Boolean): Int

Possible errors

When using these Spring Data features, you should pay attention to the names of methods and variables. Remember the rule: a method begins with its function ( findBy or deleteBy) and continues with the name of the class field, which should be the same as the name in our entity implementation. If they don't match, the PropertyReferenceException is thrown. This rule seems quite simple but it's easy to miss in a big project.

The second part of the method definition may begin with a small letter, but it's better to use a capital one to keep everything in camelCase. Also, the method's signature may contain some operators, which we are going to discuss next.

Logical expressions

Spring Data allows us to filter data by several fields at once, combining them with logical operators. Let's learn how to use this feature with an example. Imagine we want to find a customer by name and surname. Since we may have full namesakes, it is better to return a list. So, our method will go like this:

List<Customer> findByNameAndSurname(String name, String surname);

fun findByNameAndSurname(name: String, surname: String): List<Customer>

We can use the OR operator in the same way.

Expressions with operators

Spring Data provides a great variety of different operators for the creation of complicated queries. Let's look at some of them in the examples. Imagine that we want to inform our customers that their subscription will expire within a month. You can do it using the Between operator:

List<Customer> findBySubscriptionEndsOnBetween(LocalDate begin, LocalDate end);

fun findBySubscriptionEndsOnBetween(begin: LocalDate, end: LocalDate): List<Customer>

It's easy to find such customers now:

LocalDate today = LocalDate.now();
List<Customer> list1 = repository.findBySubscriptionEndsOnBetween(today, today.plusMonths(1));

val today = LocalDate.now()
val list1 = repository.findBySubscriptionEndsOnBetween(today, today.plusMonths(1))

Also, we may solve this problem with the help of the LessThanEqual operator. In our repository, this method has the following signature:

List<Customer> findBySubscriptionEndsOnLessThanEqual(LocalDate date);

fun findBySubscriptionEndsOnLessThanEqual(date: LocalDate): List<Customer>

Keep in mind that the result of this method will include customers whose subscriptions have already ended.

Limits and ordering

The last feature of Spring Data we will talk about in this topic is the ability to sort and limit the query results.

You can sort data with the help of the OrderBy keyword. The data is sorted in ascending order by default. You can change it with the Desc keyword. You can define the following method in the repository to sort the customers by surname:

List<Customer> findByOrderBySurname();

fun findByOrderBySurname(): List<Customer>

If you want to sort the customers in descending order, you can define such a method:

List<Customer> findByOrderBySurnameDesc();

fun findByOrderBySurnameDesc(): List<Customer>

You can limit the results of the database query using the First and Top keywords. There is no difference between them. These keywords are used between find (or another word, which means the method's main function) and By. After the limiting keyword, the number is defined. It indicates how many objects should be extracted from the database. This feature is convenient to use in combination with the OrderBy keyword. Let's look at the example. Imagine we want to give a discount to the 20 most loyal clients. We can find them using following method:

List<Customer> findTop20ByOrderByRegistrationDate();

fun findTop20ByOrderByRegistrationDate(): List<Customer>

Conclusion

Spring Data has lots of great features for making database queries. These features are convenient to use: you can manage your data using just Java or Kotlin code. So, having mastered making queries with Spring Data. you will easily work with databases in your web applications.