[Spring Data JPA] Custom Query

Basic Query

public interface BookRepository extends CrudRepository<Book, Long> {
    @Query("SELECT b from Book b WHERE b.available = true")
    List<Book> findAvailableBooks();
}

List<Book> availableBooks = bookRepository.findAvailableBooks();
for (Book book : availableBooks) {
	System.out.println(book.toString());
}

 

It is also possible to write Query in other format:

@Query("FROM Book b WHERE b.available = true")

@Query(value="SELECT * FROM Book b WHERE b.available = 1", nativeQuery = true)

 

Params Query:

@Query("SELECT b FROM Book b WHERE b.author = ?1")
List<Book> findByAuthor(String author);

List<Book> authorBooks = bookRepository.findByAuthor("Jane Austen");

 

@Query("SELECT b FROM Book b WHERE b.author = ?1 AND b.available = ?2")
List<Book> findByAuthorAndAvailability(String author, boolean available);

List<Book> authorAvailableBooks = bookRepository.findByAuthorAndAvailability("William H. Gass", true);

 

Reference Params by name:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

@Query("SELECT b FROM Book b WHERE b.author = :author")
List<Book> findByAuthor2(@Param("author") String author);

@Query("SELECT b FROM Book b WHERE b.author = :author AND b.available = :avail ORDER BY title")
List<Book> findByAuthorAndAvailability2(@Param("author") String author, @Param("avail") boolean availability);

 

Update Operation:

Have to add @Transactional annotation to the Class or Method. Otherwise, will throw error.

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import javax.transaction.Transactional;
...

@Transactional
public interface BookRepository extends CrudRepository<Book, Long> {
    @Modifying
    @Query("UPDATE Book b SET b.title = :title WHERE b.bookId = :id")
    int updateTitleById(@Param("title") String title, @Param("id") Long bookId);
}

bookRepository.updateTitleById("The Tempest", 3L);

 

Delete:

@Transactional
@Modifying
@Query("DELETE FROM Book b WHERE b.title = ?1")
void deleteBookByTitle(String title);

 

Using Named Queries from a Properties File:

1. In resources folder, create a new folder called `META-INF`

2. Under new folder, create a new file called `jpa-named-queries.properties`

Book.findAllNamedQuery=SELECT b FROM Book b ORDER BY b.rating DESC
Book.findByTitleNamedQuery=SELECT b FROM Book b WHERE b.title = ?1
Book.findByAuthorNativeNamedQuery=SELECT * FROM Book b WHERE b.author = :author

3. Add code in respository

    List<Book> findAllNamedQuery();

    List<Book> findByTitleNamedQuery(String title);
    @Query(nativeQuery = true)
    List<Book> findByAuthorNativeNamedQuery(@Param("author")String author);

List<Book> books3 = bookRepository.findByAuthorNativeNamedQuery("Leo Tolstoy");

 

Defining Named Queries in XML:

1. Under resources/META-INF folder, add new file orm.xml

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.0" xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm
        http://java.sun.com/xml/ns/persistence/orm_2_0.xsd ">

    <!--find all books order by price descending-->
    <named-query name="Book.findAllXML">
        <query>SELECT b FROM Book b ORDER BY b.rating DESC</query>
    </named-query>

    <!--find books by minimum rating-->
    <named-query name="Book.findByMinRatingXML">
        <query>SELECT b FROM Book b WHERE b.rating >= ?1 ORDER BY b.rating</query>
    </named-query>

    <!--native SQL query to find a book by author-->
    <named-native-query name="Book.findByAuthorNativeXML"
                        result-class="com.skillsoft.springdatajpa.model.Book">
        <query>SELECT * FROM book b WHERE b.author = :author</query>
    </named-native-query>

</entity-mappings>

2. Add those methods into repository

 

Defining Named Queries in Java Files:

package com.skillsoft.springdatajpa.model;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@NamedQuery(name = "Book.findAvailableBooksJPQL", query="SELECT b FROM Book b WHERE b.available = 1")
@NamedQuery(name = "Book.findByRatingJPQL" ,query = "SELECT b FROM Book b WHERE b.rating >=?1")
@NamedNativeQuery(name="Book.findByTitleNative", query = "SELECT * FROM book b WHERE b.title = :title", resultClass = Book.class)
@Table(name = "Book")
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long bookId;
    private String title;
    private String author;
    private int rating;
    private boolean available;

    public Book() {
    }

    public Book(String title, String author, int rating, boolean available) {
        this.title = title;
        this.author = author;
        this.rating = rating;
        this.available = available;
    }

    @Override
    public String toString() {
        return "Book{" +
                "bookId=" + bookId +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", rating=" + rating +
                ", available=" + available +
                '}'+'\n';
    }
}

2. Update code in repository

@Query(nativeQuery = true)
List<Book> findByTitleNative(@Param("title")String title);