Derived Query Methods in Spring Data JPA Repositories

Derived Query Methods in Spring Data JPA Repositories

In this tutorial, we’ll explore how Spring Data JPA leverages this idea in the form of a method naming convention.

In this tutorial, we’ll explore how Spring Data JPA leverages this idea in the form of a method naming convention.

1. Introduction

For simple queries, it’s easy to derive what the query should be just by looking at the corresponding method name in our code.

2. Structure of Derived Query Methods in Spring

Derived method names have two main parts separated by the first By keyword:

List findByName(String name)

The first part – like find – is the introducer and the rest – like ByName – is the criteria.

Spring Data JPA supports find, read, query, count and get. So, for example, we could have done _queryByName _and Spring Data would behave the same.

We can also use _Distinct, First, _or Top to remove duplicates or limit our result set:

List findTop3ByAge()

The criteria part contains the entity-specific condition expressions of the query. We can use the condition keywords along with the entity’s property names. We can also concatenate the expressions with And and _Or, _as well see in just a moment.

3. Sample Application

First, we’ll, of course, need an application using Spring Data JPA.

In that application, let’s define an entity class:

@Table(name = "users")
@Entity
class User {
    @Id
    @GeneratedValue
    private Integer id;

    private String name;
    private Integer age;
    private ZonedDateTime birthDate;
    private Boolean active;

    // standard getters and setters
}

And, let’s also define a repository. It’ll extend JpaRepository, one of the Spring Data Repository types:

interface UserRepository extends JpaRepository {}

This is where we’ll place all our derived query methods.

4. Equality Condition Keywords

Exact equality is one of the most-used conditions in queries. We have several options to express = or IS operators in the query.

We can just append the property name without any keyword for an exact match condition:

List findByName(String name);

And we can add Is or Equals for readability:

List findByNameIs(String name);
List findByNameEquals(String name);

This extra readability comes in handy when we need to express inequality instead:

List findByNameIsNot(String name);

This is quite a bit more readable than findByNameNot(String)!

As null equality is a special case, we shouldn’t use the = operator. Spring Data JPA handles null parameters by default. So, when we pass a null value for an equality condition, Spring interprets the query as IS NULL in the generated SQL.

We can also use the IsNull keyword to add IS NULL criteria to the query:

List findByNameIsNull();
List findByNameIsNotNull();

Note that, neither IsNull nor IsNotNull requires a method argument.

There are also two more keywords that don’t require any arguments. We can use True and False keywords to add equality conditions for boolean types:

List findByActiveTrue();
List findByActiveFalse();

Of course, sometimes we want something more lenient than exact equality, let’s see what else we can do.

5. Similarity Condition Keywords

When we need to query the results with a pattern of a property, we have a few options.

We can find names that start with a value using StartingWith:

List findByNameStartingWith(String prefix);

Roughly, this translates to “WHERE name LIKE ‘value%’“.

If we want names that end with a value, then EndingWith is what we want:

List findByNameEndingWith(String suffix);

Or, we can find which names contain a value with Containing:

List findByNameContaining(String infix);

Note that all conditions above are called predefined pattern expressions. So, we don’t need to add _% _operator inside the argument when these methods are called.

But, let’s suppose we are doing something more complex. Say we need to fetch the users whose names start with an a, contain b, and end with c.

For that, we can add our own LIKE with the Like keyword:

List findByNameLike(String likePattern);

And we can then hand in our LIKE pattern when we call the method:

String likePattern = "a%b%c";
userRepository.findByNameLike(likePattern);

That’s enough about names for now. Let’s try some other values in User.

6. Comparison Condition Keywords

Furthermore, we can use LessThan and LessThanEqual keywords to compare the records with the given value using the < and <= operators:

List findByAgeLessThan(Integer age);
List findByAgeLessThanEqual(Integer age);

On the other hand, in the opposite situation, we can use GreaterThan and GreaterThanEqual keywords:

List findByAgeGreaterThan(Integer age);
List findByAgeGreaterThanEqual(Integer age);

Or, we can find users who are between two ages with Between:

List findByAgeBetween(Integer startAge, Integer endAge);

We can also supply a collection of ages to match against using In:

List findByAgeIn(Collection ages);

Since we know the users’ birthdates, we might want to query for users who were born before or after a given date. We’d use Before and After for that:

List findByBirthDateAfter(ZonedDateTime birthDate);
List findByBirthDateBefore(ZonedDateTime birthDate);

7. Multiple Condition Expressions

We can combine as many expressions as we need by using And and Or keywords:

List findByNameOrBirthDate(String name, ZonedDateTime birthDate);
List findByNameOrBirthDateAndActive(String name, ZonedDateTime birthDate, Boolean active);

The precedence order is And then _Or, _just like Java.

While Spring Data JPA imposes no limit to how many expressions we can add, we shouldn’t go crazy here. Long names are unreadable and hard to maintain. For complex queries, take a look at the @Query annotation instead.

8. Sorting the Results

Next up is sorting. We could ask that the users be sorted alphabetically by their name using OrderBy:

List findByNameOrderByName(String name);
List findByNameOrderByNameAsc(String name);

Ascending order is the default sorting option, but we can use Desc instead to sort them in reverse:

List findByNameOrderByNameDesc(String name);

9. findOne vs findById in a CrudRepository

The Spring team made some major changes in CrudRepository with Spring Boot 2.x. One of them is renaming findOne to findById.

Previously with Spring Boot 1.x, we’d call findOne when we wanted to retrieve an entity by its primary key:

User user = userRepository.findOne(1);

Since Spring Boot 2.x we can do the same with findById:

User user = userRepository.findById(1);

Note that the _findById() _method is already defined in CrudRepository for us. So we don’t have to define it explicitly in custom repositories that extend CrudRepository.

10. Conclusion

In this article, we explained the query derivation mechanism in Spring Data JPA. We used the property condition keywords to write derived query methods in Spring Data JPA repositories.

The source code of this tutorial is available on the Github project.

Spring Boot Tutorial For Beginner- Spring Boot Full Course

Spring Boot Tutorial For Beginner- Spring Boot Full Course

This "Spring Boot Tutorial For Beginner- Spring Boot Full Course" video will help you learn Spring Boot Framework from scratch with examples. This Spring Tutorial is ideal for both beginners as well as professionals who want to master the Spring Boot Framework

Spring Boot Full Course - Learn Spring Boot In 4 Hours | Spring Boot Tutorial For Beginner

Below are the topics covered in this Spring Boot Tutorial for Beginners video:

1:40 What is Spring Boot?
2:35 Features of Spring Boot
3:50 Why Do We Need Spring Boot?
4:30 Spring Boot Market Trend
5:15 Spring vs Spring Boot
6:25 Install & Setup Spring Boot
6:45 System Requirements
7:35 Install & Set up Spring Boot CLI
14:00 Install & Setup Spring Tool Suite
25:40 Model View Controller
26:00 What is MVC?
27:35 Model View Controller Workflow
29:00 What is Dependency Injection?
31:50 Inversion of Control
33:10 Types of Dependency Injection
34:05 Benefits of Dependency Injection
48:35 Auto wire
49:50 Create First Web Application Using Spring Boot
1:06:50 Create a Web Application To Pass Client Data
1:13:40 Model View & Object Example
1:20:30 Create a Submission Form In Spring Boot
1:40:50 Connect Web Application To Database
2:04:50 REST API
2:07:35 What is REST API?
2:08:50 Features of REST API
2:09:35 Principles of REST API
2:11:40 Methods of REST API
2:12:20 REST API Hands-On
2:35:55 Spring Data REST
2:36:55 Spring Data REST Hands-On
2:46:35 Spring Security
2:47:30 Secure Applications Using Spring Boot
2:58:56 Spring Boot Interview Questions

Full-stack Reactive Java with Spring Framework, Spring Boot and Project Reactor

Full-stack Reactive Java with Spring Framework, Spring Boot and Project Reactor

Reactive programming offers Java developers a way to build message-driven, elastic, resilient, and responsive services...yet many Java developers don't know where to begin.

Reactive programming offers Java developers a way to build message-driven, elastic, resilient, and responsive services...yet many Java developers don't know where to begin.

The Reactive Streams initiative provides a baseline and Project Reactor provides a great way to become immediately productive, leveraging reactive capabilities from end to end. Whether you're coming from a Spring MVC environment or a functional perspective, Reactor empowers you to spin up fully reactive Spring Boot 2 applications quickly and efficiently.

In this talk, the presenter dives into the net-new Netty-based web runtime and shows you how to:

  • integrate easily with existing Spring-stack technologies
  • easily transition from blocking to reactive applications & systems
  • define your API in an imperative style and functionally, reaping all benefits both ways
  • leverage powerful new testing mechanisms to make code better and life easier

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading about Java Spring Framework and Spring Boot

Java Programming Masterclass for Software Developers

100+ Java Interview Questions and Answers In 2019

Build a microservices architecture with Spring Boot and Spring Cloud

Spring Framework - Top 50 Questions for Interview In 2019

Building a Simple CRUD App using Spring Boot, MySQL, JPA/Hibernate

Full Stack Web Development with Angular and Spring MVC

Spring Boot vs. Spring MVC vs. Spring: How Do They Compare?

Spring Boot vs. Spring MVC vs. Spring: How Do They Compare?

In this article, you will receive overviews of Spring, Spring MVC, and Spring Boot, learn what problems they solve, and where they’re best applied. The most important thing that you will learn is that Spring, Spring MVC, and Spring Boot are not competing for the same space. They solve different problems and they solve them very well.

In this article, you will receive overviews of Spring, Spring MVC, and Spring Boot, learn what problems they solve, and where they’re best applied. The most important thing that you will learn is that Spring, Spring MVC, and Spring Boot are not competing for the same space. They solve different problems and they solve them very well.

Spring, Spring Boot, Spring MVC, there are springs everywhere! Let's walk through where and when you should apply each of these tools.

What Is the Core Problem That Spring Framework Solves?

Think long and hard. What’s the problem Spring Framework solves?

The most important feature of Spring Framework is Dependency Injection. At the core of all Spring Modules is Dependency Injection or IOC Inversion of Control.
Why is this important? Because, when DI or IOC is used properly, we can develop loosely coupled applications. And loosely coupled applications can be easily unit tested.

Let’s consider a simple example.

Example Without Dependency Injection

Consider the example below: WelcomeController depends on WelcomeService to get the welcome message. What is it doing to get an instance of WelcomeService?

WelcomeService service = new WelcomeService();

It’s creating an instance of it. And that means they are tightly coupled. For example: If I create a mock for WelcomeService in a unit test for WelcomeController, how do I make WelcomeController use the mock? Not easy!

@RestController
public class WelcomeController {

    private WelcomeService service = new WelcomeService();

    @RequestMapping("/welcome")
    public String welcome() {
        return service.retrieveWelcomeMessage();
    }
}

Same Example with Dependency Injection

The world looks much simpler with dependency injection. You let the Spring Framework do the hard work. We just use two simple annotations: @Component and @Autowired.

  • Using @Component, we tell Spring Framework: Hey there, this is a bean that you need to manage.
  • Using @Autowired, we tell Spring Framework: Hey find the correct match for this specific type and autowire it in.

In the example below, Spring framework would create a bean for WelcomeService and autowire it into WelcomeController.

In a unit test, I can ask the Spring framework to auto-wire the mock of WelcomeService into WelcomeController. (Spring Boot makes things easy to do this with @MockBean. But, that’s a different story altogether!)

@Component
public class WelcomeService {
    //Bla Bla Bla
}

@RestController
public class WelcomeController {

    @Autowired
    private WelcomeService service;

    @RequestMapping("/welcome")
    public String welcome() {
        return service.retrieveWelcomeMessage();
    }
}

What Else Does Spring Framework Solve?

Problem 1: Duplication/Plumbing Code

Does Spring Framework stop with Dependency Injection? No. It builds on the core concept of Dependency Injection with a number of Spring Modules

  • Spring JDBC
  • Spring MVC
  • Spring AOP
  • Spring ORM
  • Spring JMS
  • Spring Test

Consider Spring JMS and Spring JDBC for a moment.

Do these modules bring in any new functionality? No. We can do all this with J2EE or Java EE. So, what do these bring in? They bring in simple abstractions. The aim of these abstractions is to

  • Reduce Boilerplate Code/Reduce Duplication
  • Promote Decoupling/Increase Unit Testability

For example, you need much less code to use a JDBCTemplate or a JMSTemplate compared to a traditional JDBC or JMS.

Problem 2: Good Integration With Other Frameworks

The great thing about Spring Framework is that it does not try to solve problems that are already solved. All that it does is to provide a great integration with frameworks which provide great solutions.

  • Hibernate for ORM
  • iBatis for Object Mapping
  • JUnit and Mockito for Unit Testing
What Is the Core Problem That Spring MVC Framework Solves?

Spring MVC Framework provides decoupled way of developing web applications. With simple concepts like Dispatcher Servlet, ModelAndView and View Resolver, it makes it easy to develop web applications.## Why Do We Need Spring Boot?

Spring based applications have a lot of configuration.

When we use Spring MVC, we need to configure component scan, dispatcher servlet, a view resolver, web jars(for delivering static content) among other things.

  <bean
        class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix">
            <value>/WEB-INF/views/</value>
        </property>
        <property name="suffix">
            <value>.jsp</value>
        </property>
  </bean>

  <mvc:resources mapping="/webjars/**" location="/webjars/"/>


The code snippet below shows the typical configuration of a dispatcher servlet in a web application.

    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>
            org.springframework.web.servlet.DispatcherServlet
        </servlet-class>
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>/WEB-INF/todo-servlet.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>


When we use Hibernate/JPA, we would need to configure a datasource, an entity manager factory, a transaction manager among a host of other things.

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass" value="${db.driver}" />
        <property name="jdbcUrl" value="${db.url}" />
        <property name="user" value="${db.username}" />
        <property name="password" value="${db.password}" />
    </bean>

    <jdbc:initialize-database data-source="dataSource">
        <jdbc:script location="classpath:config/schema.sql" />
        <jdbc:script location="classpath:config/data.sql" />
    </jdbc:initialize-database>

    <bean
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
        id="entityManagerFactory">
        <property name="persistenceUnitName" value="hsql_pu" />
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
        <property name="dataSource" ref="dataSource" />
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>


Problem #1: Spring Boot Auto Configuration: Can We Think Different?

Spring Boot brings a new thought process around this.

Can we bring more intelligence into this? When a spring mvc jar is added into an application, can we auto configure some beans automatically?* How about auto-configuring a Data Source if Hibernate jar is on the classpath?

  • How about auto-configuring a Dispatcher Servlet if Spring MVC jar is on the classpath?

There would be provisions to override the default auto configuration.

Spring Boot looks at a) Frameworks available on the CLASSPATH b) Existing configuration for the application. Based on these, Spring Boot provides basic configuration needed to configure the application with these frameworks. This is called Auto Configuration.### Problem #2: Spring Boot Starter Projects: Built Around Well-Known Patterns

Let’s say we want to develop a web application.

First of all, we would need to identify the frameworks we want to use, which versions of frameworks to use and how to connect them together.

All web application have similar needs. Listed below are some of the dependencies we use in our Spring MVC Course. These include Spring MVC, Jackson Databind (for data binding), Hibernate-Validator (for server side validation using Java Validation API) and Log4j (for logging). When creating this course, we had to choose the compatible versions of all these frameworks.

<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-webmvc</artifactId>
   <version>4.2.2.RELEASE</version>
</dependency>

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.5.3</version>
</dependency>

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>5.0.2.Final</version>
</dependency>

<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>


Here’s what the Spring Boot documentations says about starters.

Starters are a set of convenient dependency descriptors that you can include in your application. You get a one-stop-shop for all the Spring and related technology that you need, without having to hunt through sample code and copy paste loads of dependency descriptors. For example, if you want to get started using Spring and JPA for database access, just include the spring-boot-starter-data-jpa dependency in your project, and you are good to go.
Let’s consider an example starter: Spring Boot Starter Web.

If you want to develop a web application or an application to expose restful services, Spring Boot Start Web is the starter to pick. Let’s create a quick project with Spring Boot Starter Web using Spring Initializr.

Dependency for Spring Boot Starter Web

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>


The following screenshot shows the different dependencies that are added into our application

Dependencies can be classified into:

  • Spring: core, beans, context, aop
  • Web MVC: (Spring MVC)
  • Jackson: for JSON Binding
  • Validation: Hibernate Validator, Validation API
  • Embedded Servlet Container: Tomcat
  • Logging: logback, slf4j

Any typical web application would use all these dependencies. Spring Boot Starter Web comes pre-packaged with these. As a developer, I would not need to worry about either these dependencies or their compatible versions.

Spring Boot Starter Project Options

As we see from Spring Boot Starter Web, starter projects help us in quickly getting started with developing specific types of applications.

  • spring-boot-starter-web-services: SOAP Web Services
  • spring-boot-starter-web: Web and RESTful applications
  • spring-boot-starter-test: Unit testing and Integration Testing
  • spring-boot-starter-jdbc: Traditional JDBC
  • spring-boot-starter-hateoas: Add HATEOAS features to your services
  • spring-boot-starter-security: Authentication and Authorization using Spring Security
  • spring-boot-starter-data-jpa: Spring Data JPA with Hibernate
  • spring-boot-starter-cache: Enabling Spring Framework’s caching support
  • spring-boot-starter-data-rest: Expose Simple REST Services using Spring Data REST
Other Goals of Spring Boot

There are a few starters for technical stuff as well

  • spring-boot-starter-actuator: To use advanced features like monitoring and tracing to your application out of the box
  • spring-boot-starter-undertow, spring-boot-starter-jetty, spring-boot-starter-tomcat: To pick your specific choice of Embedded Servlet Container
  • spring-boot-starter-logging: For Logging using logback
  • spring-boot-starter-log4j2: Logging using Log4j2

Spring Boot aims to enable production ready applications in quick time.

  • Actuator: Enables Advanced Monitoring and Tracing of applications.
  • Embedded Server Integrations: Since the server is integrated into the application, I would need to have a separate application server installed on the server.
  • Default Error Handling