JdbcClient and Spring Data JDBC: When Not to Use JPA

create table book (
  id bigint generated by default as identity primary key,
  title varchar(255) not null,
  author varchar(255) not null,
  price int not null
);

create table purchase_order (
  id bigint generated by default as identity primary key,
  customer varchar(255) not null
);

create table order_line (
  purchase_order bigint not null references purchase_order(id),
  product varchar(255) not null,
  quantity int not null
);

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

import org.springframework.data.annotation.Id;

public record Book(@Id Long id, String title, String author, int price) {
}

//BookCatalog.java
package com.javabrahman.data.jdbc;

import java.util.List;
import java.util.Optional;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

@Repository
public class BookCatalog {
  private final JdbcClient jdbc;

  public BookCatalog(JdbcClient jdbc) {
    this.jdbc = jdbc;
  }

  public long add(String title, String author, int price) {
    KeyHolder key = new GeneratedKeyHolder();
    jdbc.sql("insert into book (title, author, price) values (:title, :author, :price)")
        .param("title", title)
        .param("author", author)
        .param("price", price)
        .update(key, "id");
    return key.getKey().longValue();
  }

  public List<Book> byAuthor(String author) {
    return jdbc.sql("select id, title, author, price from book where author = :author order by title")
        .param("author", author)
        .query(Book.class)          // columns mapped to the record's components by name
        .list();
  }

  public Optional<String> titleOf(long id) {
    return jdbc.sql("select title from book where id = ?")
        .param(id)
        .query(String.class)
        .optional();
  }

  public int raisePrices(String author, int amount) {
    return jdbc.sql("update book set price = price + :amount where author = :author")
        .param("amount", amount)
        .param("author", author)
        .update();                  // number of rows changed
  }

  public List<String> totalsPerAuthor() {
    return jdbc.sql("select author, count(*), sum(price) from book group by author order by author")
        .query((rs, row) -> rs.getString(1) + ": " + rs.getInt(2) + " books, total price " + rs.getInt(3))
        .list();
  }
}

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

import java.util.List;
import org.springframework.data.repository.ListCrudRepository;

public interface BookRepository extends ListCrudRepository<Book, Long> {
  List<Book> findByPriceGreaterThanOrderByPrice(int price);
}

//OrderLine.java
package com.javabrahman.data.jdbc;

public record OrderLine(String product, int quantity) {
}

//PurchaseOrder.java
package com.javabrahman.data.jdbc;

import java.util.HashSet;
import java.util.Set;
import org.springframework.data.annotation.Id;

public class PurchaseOrder {
  @Id
  private Long id;
  private String customer;
  private Set<OrderLine> lines = new HashSet<>();

  public PurchaseOrder(String customer) {
    this.customer = customer;
  }

  public void add(String product, int quantity) {
    lines.add(new OrderLine(product, quantity));
  }

  public Long getId() { return id; }
  public String getCustomer() { return customer; }
  public Set<OrderLine> getLines() { return lines; }
}

//OrderRepository.java
package com.javabrahman.data.jdbc;

import org.springframework.data.repository.CrudRepository;

public interface OrderRepository extends CrudRepository<PurchaseOrder, Long> {
}

//JdbcApp.java
package com.javabrahman.data.jdbc;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
public class JdbcApp {

  @Bean
  CommandLineRunner demo(BookCatalog catalog, BookRepository books, OrderRepository orders) {
    return args -> {
      System.out.println("== JdbcClient");
      long id = catalog.add("Effective Java", "Joshua Bloch", 45);
      catalog.add("Java Puzzlers", "Joshua Bloch", 30);
      catalog.add("Clean Code", "Robert Martin", 38);
      System.out.println("generated id: " + id);
      System.out.println("title of " + id + ": " + catalog.titleOf(id).orElse("-"));
      System.out.println("title of 99: " + catalog.titleOf(99).orElse("not found"));
      catalog.byAuthor("Joshua Bloch").forEach(System.out::println);
      System.out.println("rows updated: " + catalog.raisePrices("Joshua Bloch", 5));
      catalog.totalsPerAuthor().forEach(System.out::println);

      System.out.println("== Spring Data JDBC repository");
      Book saved = books.save(new Book(null, "Refactoring", "Martin Fowler", 50));
      System.out.println("saved: " + saved);
      System.out.println("count: " + books.count());
      books.findByPriceGreaterThanOrderByPrice(40).forEach(System.out::println);

      System.out.println("== Spring Data JDBC aggregate");
      PurchaseOrder order = new PurchaseOrder("Ann");
      order.add("keyboard", 1);
      order.add("mouse", 2);
      order = orders.save(order);
      order.add("monitor", 1);
      orders.save(order);
      PurchaseOrder loaded = orders.findById(order.getId()).orElseThrow();
      System.out.println("order " + loaded.getId() + " for " + loaded.getCustomer() + ": " + loaded.getLines().size() + " lines");
    };
  }

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