JPA Repository query example in Spring Boot | Derived Query
Tags: webdev, java, spring, todayilearned
Published: 2022-03-07 09:56:45

In previous posts, you've known how to use <a href="https://www.bezkoder.com/spring-jpa-query/">JPQL</a> and <a href="https://www.bezkoder.com/jpa-native-query/">native Query</a> to retrieve data from the database using <code>@Query</code> annotation. Today, I will show you way to implement Spring Data JPA Repository query in Spring Boot with Derived Query methods:

<ul>
	<li>Structure of Derived Query methods</li>
	<li>Configure Spring Boot application to work with different database</li>
	<li>JPA find by field, column name, multiple columns</li>
	<li>JPA query methods for pagination and sorting</li>
</ul>

Full article: [JPA Repository Query example](https://www.bezkoder.com/jpa-repository-query/)

<h2>Structure of Derived Query methods</h2>
Typically, a Derived Query method has 2 elements: subject (the action), and predicate (the conditions).
<ul>
	<li><strong>Subject</strong>: is the introducing clause (<code>find…By</code>, <code>exists…By</code>, <code>count…By</code> for example), it may contain further expressions (between <code>find</code>/<code>exists</code>/<code>count</code> and <code>By</code>) for result-limiting keywords such as <code>Distinct</code> or <code>Top</code>/<code>First</code>.</li>
	<li><strong>Predicate</strong>: is placed after the subject. It can be entity properties (concatenating with <code>And</code>/<code>Or</code>) followed by one or more keywords (<code>StartingWith</code>, <code>EndingWith</code>, <code>Containing</code>, <code>IgnoreCase</code>...).</li></ul>

For example:
```java
List&lt;Tutorial> findByTitleContainingIgnoreCase(String title);

List&lt;Tutorial> findTop3ByTitleContainingAndPublished(String title, boolean isPublished);
```

You can find the full list at <a href="https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#appendix.query.method.subject">query method subject keywords</a> and <a href="https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#appendix.query.method.predicate">query method predicate keywords</a>.

<h2>JPA Repository Query example with Spring Boot</h2>
- Technology:
<ul>
    	<li>Java 8</li>
    	<li>Spring Boot 2.6.3 (with Spring Data JPA)</li>
    	<li>MySQL/PostgreSQL/H2 (embedded database)</li>
    	<li>Maven 3.8.1</li>
</ul>

- Project Structure:

![jpa-repository-query-example-spring-boot-project](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5wmqc8uq7z316u0k1427.png)

Let me explain it briefly.

<ul>
    	<li><code>Tutorial</code> data model class correspond to entity and table <em>tutorials</em>.</li>
    	<li><code>TutorialRepository</code> is an interface that extends <a href="https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html">JpaRepository</a> for derived query methods. It will be autowired in <code>SpringBootQueryExampleApplication</code>.</li>
    	<li><code>SpringBootQueryExampleApplication</code> is <code><a href="https://docs.spring.io/spring-boot/docs/current/api/org/springframework/boot/autoconfigure/SpringBootApplication.html">SpringBootApplication</a></code> which implements <code><a href="https://docs.spring.io/spring-boot/docs/current/api/org/springframework/boot/CommandLineRunner.html">CommandLineRunner</a></code>. We will use <code>TutorialRepository</code> to run Query methods here.</li>
    	<li>Configuration for Spring Datasource, JPA & Hibernate in <strong>application.properties</strong>.</li>
    	<li><strong>pom.xml</strong> contains dependencies for Spring Boot and MySQL/PostgreSQL/H2 database.</li>
</ul>

<h2>Create Entity</h2>
In <strong>model</strong> package, we define <code>Tutorial</code> class.

Tutorial has four fields: id, title, level, description, published, createdAt.

<strong>model</strong>/<em>Tutorial.java</em>
```java
package com.bezkoder.spring.jpa.query.model;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "tutorials")
public class Tutorial {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  private String title;

  private String description;
  
  private int level;

  private boolean published;
  
  @Temporal(TemporalType.TIMESTAMP)
  private Date createdAt;

  public Tutorial() {

  }

  public Tutorial(String title, String description, int level, boolean published, Date createdAt) {
    this.title = title;
    this.description = description;
    this.level = level;
    this.published = published;
    this.createdAt = createdAt;
  }

  // getters and setters
}
```

- <code>@Entity</code> annotation indicates that the class is a persistent Java class.
- <code>@Table</code> annotation provides the table that maps this entity.

- <code>@Id</code> annotation is for the primary key.
- <code>@GeneratedValue</code> annotation is used to define generation strategy for the primary key. 

- <code>@Temporal</code> annotation converts back and forth between timestamp and <code>java.util.Date</code> or time-stamp into time. For example, <code>@Temporal(TemporalType.DATE)</code> drops the time value and only preserves the date.

```java
@Temporal(TemporalType.DATE)
private Date createdAt;
```

<h2>Define JPA Repository Query methods</h2>
Let's create a repository to interact with database.
In <strong>repository</strong> package, create <code>TutorialRepository</code> interface that extend <code>JpaRepository</code>.

<strong>repository</strong>/<em>TutorialRepository.java</em>
```java
package com.bezkoder.spring.jpa.query.repository;

import com.bezkoder.spring.jpa.query.model.Tutorial;

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {

}
```

In this interface, we will write JPA Derived Queries to fetch data from database with <strong>tutorials</strong> table like this:

![jpa-repository-query-example-spring-boot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/upk3zzm6f1v7svc4wmx1.png)

For step by step and Github, please visit:
[JPA Repository Query example](https://www.bezkoder.com/jpa-repository-query/)

## Further Reading
Using Native Query instead:
<a href="https://www.bezkoder.com/jpa-native-query/">Spring JPA Native Query example with Spring Boot</a>

Or JPQL:
<a href="https://www.bezkoder.com/spring-jpa-query/">Spring JPA @Query example with JPQL</a>

Associations:
- <a href="https://www.bezkoder.com/jpa-one-to-many/">JPA One To Many example with Hibernate and Spring Boot</a>
- <a href="https://www.bezkoder.com/jpa-many-to-many/">JPA Many to Many example with Hibernate in Spring Boot</a>

You can apply this implementation in following tutorials:
- <a href="https://www.bezkoder.com/spring-boot-jpa-h2-example/">Spring JPA + H2 example</a>
- <a href="https://bezkoder.com/spring-boot-jpa-crud-rest-api/">Spring JPA + MySQL example</a>
- <a href="https://bezkoder.com/spring-boot-postgresql-example/">Spring JPA + PostgreSQL example</a>
- <a href="https://www.bezkoder.com/spring-boot-hibernate-oracle/">Spring JPA + Oracle example</a>
- <a href="https://www.bezkoder.com/spring-boot-sql-server/">Spring JPA + SQL Server example</a>

You can continue to write CRUD Rest APIs with:
<a href="https://www.bezkoder.com/spring-boot-jpa-crud-rest-api/">Spring Boot, Spring Data JPA – Rest CRUD API example</a>

If you want to write Unit Test for the JPA Repository:
<a href="https://www.bezkoder.com/spring-boot-unit-test-jpa-repo-datajpatest/">Spring Boot Unit Test for JPA Repository with @DataJpaTest</a>

You can also know:
- how to deploy this Spring Boot App on AWS (for free) with <a href="https://www.bezkoder.com/deploy-spring-boot-aws-eb/">this tutorial</a>.
- dockerize with <a href="https://www.bezkoder.com/docker-compose-spring-boot-mysql/">Docker Compose: Spring Boot and MySQL example</a>
- way to upload an Excel file and store the data in MySQL database with <a href="https://www.bezkoder.com/spring-boot-upload-excel-file-database/">this post</a>
- upload CSV file and store the data in MySQL with <a href="https://www.bezkoder.com/spring-boot-upload-csv-file/">this post</a>.