Spring Data JPA @Query: JPQL and Native Queries

//Book.java
package com.javabrahman.data.query;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import java.math.BigDecimal;

@Entity
public class Book {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String title;
  private String author;
  private int publishedYear;
  private BigDecimal price;

  protected Book() {
  }

  public Book(String title, String author, int publishedYear, BigDecimal price) {
    this.title = title;
    this.author = author;
    this.publishedYear = publishedYear;
    this.price = price;
  }

  public Long getId() { return id; }
  public String getTitle() { return title; }
  public String getAuthor() { return author; }
  public int getPublishedYear() { return publishedYear; }
  public BigDecimal getPrice() { return price; }

  @Override
  public String toString() {
    return title + " (" + author + ", " + publishedYear + ", $" + price + ")";
  }
}

//AuthorStats.java
package com.javabrahman.data.query;

public record AuthorStats(String author, long books, double averagePrice) {
}

//BookRepository.java
package com.javabrahman.data.query;

import java.math.BigDecimal;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

public interface BookRepository extends JpaRepository<Book, Long> {

  // JPQL with named parameters
  @Query("select b from Book b where b.author = :author and b.publishedYear >= :fromYear order by b.price")
  List<Book> recentBooksBy(@Param("author") String author, @Param("fromYear") int fromYear);

  // JPQL text search: case-insensitive "contains", with a caller-supplied Sort
  @Query("select b from Book b where lower(b.title) like lower(concat('%', :text, '%'))")
  List<Book> searchTitles(@Param("text") String text, Sort sort);

  // JPQL aggregation into a record, with a constructor expression
  @Query("""
      select new com.javabrahman.data.query.AuthorStats(b.author, count(b), avg(b.price))
      from Book b
      group by b.author
      having count(b) > :minBooks
      order by count(b) desc, b.author
      """)
  List<AuthorStats> authorsWithMoreThan(@Param("minBooks") long minBooks);

  // JPQL with paging: the count query for the page is derived from this query
  @Query("select b from Book b where b.price >= :minPrice order by b.price desc, b.id")
  Page<Book> expensiveBooks(@Param("minPrice") BigDecimal minPrice, Pageable pageable);

  // A subquery, which no method name can express
  @Query("select b from Book b where b.price > (select avg(x.price) from Book x) order by b.price desc")
  List<Book> aboveAveragePrice();

  // Native SQL, with positional parameters
  @Query(value = "select * from book where published_year between ?1 and ?2 order by published_year, title", nativeQuery = true)
  List<Book> publishedBetween(int from, int to);

  // Native SQL with a derived table: the decade of each book, as text, then a count per decade
  @Query(value = "select decade, count(*) as books from "
      + "(select concat(floor(published_year / 10) * 10, 's') as decade from book) as t "
      + "group by decade order by decade", nativeQuery = true)
  List<Object[]> booksPerDecade();

  // Bulk update and delete: one statement each, no entities loaded
  @Modifying(clearAutomatically = true)
  @Transactional
  @Query("update Book b set b.price = b.price * :factor where b.author = :author")
  int adjustPrices(@Param("author") String author, @Param("factor") BigDecimal factor);

  @Modifying
  @Transactional
  @Query("delete from Book b where b.publishedYear < :year")
  int deleteOlderThan(@Param("year") int year);
}

//QueryApp.java
package com.javabrahman.data.query;

import java.math.BigDecimal;
import java.util.Collection;
import java.util.List;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

@SpringBootApplication
public class QueryApp {

  @Bean
  CommandLineRunner demo(BookRepository r) {
    return args -> {
      r.saveAll(List.of(
          new Book("Effective Java", "Joshua Bloch", 2018, new BigDecimal("45.99")),
          new Book("Java Puzzlers", "Joshua Bloch", 2005, new BigDecimal("29.99")),
          new Book("Java Concurrency in Practice", "Brian Goetz", 2006, new BigDecimal("49.99")),
          new Book("Clean Code", "Robert C. Martin", 2008, new BigDecimal("37.49")),
          new Book("Clean Architecture", "Robert C. Martin", 2017, new BigDecimal("34.99")),
          new Book("The Clean Coder", "Robert C. Martin", 2011, new BigDecimal("31.99")),
          new Book("Refactoring", "Martin Fowler", 2018, new BigDecimal("47.99")),
          new Book("Spring in Action", "Craig Walls", 2022, new BigDecimal("49.99")),
          new Book("Domain-Driven Design", "Eric Evans", 2003, new BigDecimal("59.99"))));

      show("recentBooksBy(\"Robert C. Martin\", 2010)", r.recentBooksBy("Robert C. Martin", 2010));
      show("searchTitles(\"CLEAN\", Sort.by(\"publishedYear\"))", r.searchTitles("CLEAN", Sort.by("publishedYear")));
      System.out.println("authorsWithMoreThan(1):");
      r.authorsWithMoreThan(1).forEach(s -> System.out.printf("    %s: %d books, average price $%.2f%n", s.author(), s.books(), s.averagePrice()));

      Page<Book> page = r.expensiveBooks(new BigDecimal("40"), PageRequest.of(0, 2));
      System.out.println("expensiveBooks(40), page 1 of size 2: total " + page.getTotalElements() + " books, " + page.getTotalPages() + " pages");
      page.forEach(b -> System.out.println("    " + b));
      show("aboveAveragePrice()", r.aboveAveragePrice());

      show("publishedBetween(2005, 2008) - native", r.publishedBetween(2005, 2008));
      System.out.println("booksPerDecade() - native:");
      r.booksPerDecade().forEach(row -> System.out.println("    " + row[0] + ": " + row[1]));

      int updated = r.adjustPrices("Robert C. Martin", new BigDecimal("0.90"));
      System.out.println("adjustPrices(\"Robert C. Martin\", 0.90) updated " + updated + " rows");
      show("after the update", r.recentBooksBy("Robert C. Martin", 2000));
      System.out.println("deleteOlderThan(2006) deleted " + r.deleteOlderThan(2006) + " rows, " + r.count() + " left");
    };
  }

  private static void show(String call, Collection<Book> books) {
    System.out.println(call + ":");
    books.forEach(b -> System.out.println("    " + b));
  }

  public static void main(String[] args) {
    SpringApplication.run(QueryApp.class, args);
  }
}

//BrokenQueryRepository.java
package com.javabrahman.data.brokenquery;

import com.javabrahman.data.query.Book;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface BrokenQueryRepository extends JpaRepository<Book, Long> {
  @Query("select b from Boook b where b.author = ?1")   // "Boook": a typo
  List<Book> byAuthor(String author);
}

//BrokenQueryApp.java
package com.javabrahman.data.brokenquery;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.persistence.autoconfigure.EntityScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@SpringBootApplication
@EntityScan("com.javabrahman.data.query")
@EnableJpaRepositories(basePackageClasses = BrokenQueryRepository.class)
public class BrokenQueryApp {
  public static void main(String[] args) {
    try (var context = SpringApplication.run(BrokenQueryApp.class, args)) {
      System.out.println("Started");
    } catch (Exception e) {
      Throwable cause = e;
      while (cause.getCause() != null) {
        cause = cause.getCause();
      }
      System.out.println("Application failed to start. Root cause: " + cause.getClass().getSimpleName() + "\n  "
          + cause.getMessage().lines().findFirst().orElse(""));
    }
  }
}