Spring Data JPA — Database Access

Spring Data JPA turns an interface into a working repository: you declare methods, and Spring generates the implementation on top of Hibernate. The examples share one Spring Boot 3.5 project with spring-boot-starter-data-jpa and the in-memory H2 database, so there is nothing to install. Each example is a CommandLineRunner enabled by a profile, run with java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=<name>, and --spring.jpa.show-sql=true makes Hibernate print each SQL statement it runs.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

With an embedded database on the classpath and no spring.datasource.url, Spring Boot creates an in-memory H2 database and sets Hibernate to create the schema at startup and drop it at shutdown. For PostgreSQL or MySQL you would add the driver and set spring.datasource.url, username and password.

An entity and a repository

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)    // the database assigns the id
    private Long id;

    @Column(nullable = false, length = 100)
    private String name;

    private String category;

    @Column(precision = 10, scale = 2)
    private BigDecimal price;

    protected Product() {}                                 // required by JPA

    public Product(String name, String category, String price) {
        this.name = name;
        this.category = category;
        this.price = new BigDecimal(price);
    }

    public Long getId()           { return id; }
    public String getName()       { return name; }
    public String getCategory()   { return category; }
    public BigDecimal getPrice()  { return price; }
    public void setPrice(BigDecimal price) { this.price = price; }

    @Override
    public String toString() { return "Product[" + id + ", " + name + ", " + category + ", " + price + "]"; }
}

public interface ProductRepository extends JpaRepository<Product, Long> {
}

@Component
@Profile("crud")
public class CrudDemo implements CommandLineRunner {
    private final ProductRepository products;
    public CrudDemo(ProductRepository products) { this.products = products; }

    @Override
    public void run(String... args) {
        Product saved = products.save(new Product("Keyboard", "electronics", "49.99"));
        System.out.println("saved:    " + saved);

        products.findById(saved.getId()).ifPresent(p -> System.out.println("found:    " + p));

        saved.setPrice(new BigDecimal("44.99"));
        products.save(saved);                                     // id set -> UPDATE
        System.out.println("count:    " + products.count());

        products.deleteById(saved.getId());
        System.out.println("after delete, findById(1): " + products.findById(1L));
    }
}

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=crud --spring.jpa.show-sql=true | grep -vE 'customer|purchase_orders'
Hibernate: drop table if exists products cascade 
Hibernate: create table products (price numeric(10,2), id bigint generated by default as identity, name varchar(100) not null, category varchar(255), primary key (id))
Hibernate: insert into products (category,name,price,id) values (?,?,?,default)
saved:    Product[1, Keyboard, electronics, 49.99]
Hibernate: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from products p1_0 where p1_0.id=?
found:    Product[1, Keyboard, electronics, 49.99]
Hibernate: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from products p1_0 where p1_0.id=?
Hibernate: update products set category=?,name=?,price=? where id=?
Hibernate: select count(*) from products p1_0
count:    1
Hibernate: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from products p1_0 where p1_0.id=?
Hibernate: delete from products where id=?
Hibernate: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from products p1_0 where p1_0.id=?
after delete, findById(1): Optional.empty
Hibernate: drop table if exists products cascade 

(grep hides the tables of the later examples.) An empty interface was enough: JpaRepository supplies save, findById, findAll, count, deleteById and more. Hibernate generated the create table from the annotations — @Column(nullable = false, length = 100) became varchar(100) not null, IDENTITY became generated by default as identity. The INSERT’s id is default because the database assigns it, and save returned the entity with that id filled in. save on an entity that already has an id merges it: Hibernate first selects the row, then issues an UPDATE. Each repository call ran in its own short transaction, which is why findById hits the database every time.

Derived query methods

Spring Data parses method names into queries: findBy + property + optional operator, joined with And/Or, plus OrderBy, countBy, existsBy, Top/First.

public interface ProductRepository extends JpaRepository<Product, Long> {

    List<Product> findByCategory(String category);
    List<Product> findByPriceLessThanOrderByPriceAsc(BigDecimal max);
    List<Product> findByNameContainingIgnoreCase(String fragment);
    long countByCategory(String category);
    boolean existsByName(String name);
}

final class SampleData {
    static List<Product> products() {
        return List.of(
                new Product("Keyboard", "electronics", "49.99"),
                new Product("Mouse", "electronics", "19.99"),
                new Product("Monitor", "electronics", "189.00"),
                new Product("Desk", "furniture", "199.00"),
                new Product("Chair", "furniture", "129.50"),
                new Product("Lamp", "furniture", "35.50"),
                new Product("USB Cable", "electronics", "7.99"));
    }
}

// QueryDemo.run(), after products.saveAll(SampleData.products())
System.out.println("furniture:      " + products.findByCategory("furniture"));
System.out.println("under 40:       " + products.findByPriceLessThanOrderByPriceAsc(new BigDecimal("40")));
System.out.println("contains 'o':   " + products.findByNameContainingIgnoreCase("O").size() + " products");
System.out.println("electronics:    " + products.countByCategory("electronics"));
System.out.println("has a Lamp?     " + products.existsByName("Lamp"));

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=derived
furniture:      [Product[4, Desk, furniture, 199.00], Product[5, Chair, furniture, 129.50], Product[6, Lamp, furniture, 35.50]]
under 40:       [Product[7, USB Cable, electronics, 7.99], Product[2, Mouse, electronics, 19.99], Product[6, Lamp, furniture, 35.50]]
contains 'o':   3 products
electronics:    4
has a Lamp?     true
$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=derived --spring.jpa.show-sql=true | grep 'Hibernate: select' | head -2
Hibernate: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from products p1_0 where p1_0.category=?
Hibernate: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from products p1_0 where p1_0.price<? order by p1_0.price

The method names are checked at startup against the entity’s properties — a typo such as findByCatgory stops the application from starting, rather than failing on first use. Arguments are always bound as parameters (?), never concatenated, so derived queries are safe from SQL injection. Past two or three conditions the names get unreadable; switch to @Query.

@Query: JPQL, native SQL and bulk updates

    @Query("select p from Product p where p.price between :lo and :hi order by p.price desc")
    List<Product> priceBetween(@Param("lo") BigDecimal lo, @Param("hi") BigDecimal hi);

    @Query("select p.category, count(p), avg(p.price) from Product p group by p.category order by p.category")
    List<Object[]> statsByCategory();

    @Query(value = "select * from products where lower(name) like 'm%'", nativeQuery = true)
    List<Product> namesStartingWithM();

    @Modifying
    @Query("update Product p set p.price = p.price * :factor where p.category = :category")
    int reprice(@Param("category") String category, @Param("factor") BigDecimal factor);

// JpqlDemo.run() is @Transactional: @Modifying queries need a transaction
System.out.println("40..190:  " + products.priceBetween(new BigDecimal("40"), new BigDecimal("190")));
for (Object[] row : products.statsByCategory())
    System.out.printf("%-12s count=%d avg=%.2f%n", row[0], row[1], row[2]);
System.out.println("native:   " + products.namesStartingWithM());
int changed = products.reprice("furniture", new BigDecimal("1.10"));
System.out.println("repriced " + changed + " rows; Lamp is now " + products.findByNameContainingIgnoreCase("lamp").get(0).getPrice());

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=jpql
40..190:  [Product[3, Monitor, electronics, 189.00], Product[5, Chair, furniture, 129.50], Product[1, Keyboard, electronics, 49.99]]
electronics  count=4 avg=66.74
furniture    count=3 avg=121.33
native:   [Product[2, Mouse, electronics, 19.99], Product[3, Monitor, electronics, 189.00]]
repriced 3 rows; Lamp is now 35.50

JPQL names the entity Product and its fields; native SQL names the table products and is tied to one database’s dialect. An aggregate query returns Object[] rows. The last line is the surprise: three rows were updated by 10%, yet the Lamp still reads 35.50. A bulk update goes straight to the database and bypasses the persistence context — the cache of entities already loaded in this transaction — and the Lamp was already there from saveAll. Practice task 1 fixes it.

Paging and sorting

    Page<Product> findByCategory(String category, Pageable pageable);   // in ProductRepository

PageRequest first = PageRequest.of(0, 3, Sort.by("price").descending());
for (Page<Product> page = products.findAll(first); ; page = products.findAll(page.nextPageable())) {
    System.out.printf("page %d of %d (%d total): %s%n", page.getNumber(), page.getTotalPages(),
            page.getTotalElements(), page.getContent().stream().map(Product::getName).toList());
    if (!page.hasNext()) break;
}
Page<Product> furniture = products.findByCategory("furniture", PageRequest.of(0, 2, Sort.by("name")));
System.out.println("furniture page 0: " + furniture.getContent().stream().map(Product::getName).toList()
        + ", hasNext=" + furniture.hasNext());

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=paging
page 0 of 3 (7 total): [Desk, Monitor, Chair]
page 1 of 3 (7 total): [Keyboard, Lamp, Mouse]
page 2 of 3 (7 total): [USB Cable]
furniture page 0: [Chair, Desk], hasNext=true
$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=paging --spring.jpa.show-sql=true | grep 'Hibernate: select' | head -2
Hibernate: select p1_0.id,p1_0.category,p1_0.name,p1_0.price from products p1_0 order by p1_0.price desc offset ? rows fetch first ? rows only
Hibernate: select count(p1_0.id) from products p1_0

A Pageable turns into offset … fetch first … rows only (LIMIT/OFFSET on other databases), and pages are numbered from 0. Returning Page costs a second count query so it can report getTotalPages(); return Slice or List instead when the total is not needed. Any derived or @Query method can take a trailing Pageable. In a web controller, a Pageable parameter is filled from ?page=1&size=20&sort=price,desc automatically.

Relationships and the N+1 problem

@Entity
public class Customer {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<PurchaseOrder> orders = new ArrayList<>();      // inverse side: no column here

    protected Customer() {}
    public Customer(String name) { this.name = name; }

    public void addOrder(PurchaseOrder order) {                  // keep both sides in sync
        orders.add(order);
        order.setCustomer(this);
    }

    public Long getId() { return id; }
    public String getName() { return name; }
    public List<PurchaseOrder> getOrders() { return orders; }
}

@Entity
@Table(name = "purchase_orders")
public class PurchaseOrder {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private BigDecimal total;

    @ManyToOne(fetch = FetchType.LAZY)                           // owning side: holds customer_id
    @JoinColumn(name = "customer_id")
    private Customer customer;

    protected PurchaseOrder() {}
    public PurchaseOrder(String total) { this.total = new BigDecimal(total); }

    public BigDecimal getTotal() { return total; }
    void setCustomer(Customer customer) { this.customer = customer; }
}

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Query("select distinct c from Customer c left join fetch c.orders order by c.id")
    List<Customer> findAllWithOrders();                          // one query, orders included
}

// RelationsDemo.run(): saves Ada, Grace and Linus with 2 orders each, then
tx.executeWithoutResult(s -> {                  // lazy loading needs an open transaction
    System.out.println("--- findAll(), then touch each customer's orders");
    customers.findAll().forEach(c -> System.out.println(c.getName() + ": " + c.getOrders().size() + " orders"));
});
tx.executeWithoutResult(s -> {
    System.out.println("--- findAllWithOrders()");
    customers.findAllWithOrders().forEach(c -> System.out.println(c.getName() + ": " + c.getOrders().size() + " orders"));
});

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=relations --spring.jpa.show-sql=true | grep -vE 'drop table|products|insert'
Hibernate: create table customer (id bigint generated by default as identity, name varchar(255), primary key (id))
Hibernate: create table purchase_orders (total numeric(38,2), customer_id bigint, id bigint generated by default as identity, primary key (id))
Hibernate: alter table if exists purchase_orders add constraint FKs3cbu181auvlo0wbl95c9xb9k foreign key (customer_id) references customer
--- findAll(), then touch each customer's orders
Hibernate: select c1_0.id,c1_0.name from customer c1_0
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.total from purchase_orders o1_0 where o1_0.customer_id=?
Ada: 2 orders
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.total from purchase_orders o1_0 where o1_0.customer_id=?
Grace: 2 orders
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.total from purchase_orders o1_0 where o1_0.customer_id=?
Linus: 2 orders
--- findAllWithOrders()
Hibernate: select distinct c1_0.id,c1_0.name,o1_0.customer_id,o1_0.id,o1_0.total from customer c1_0 left join purchase_orders o1_0 on c1_0.id=o1_0.customer_id order by c1_0.id
Ada: 2 orders
Grace: 2 orders
Linus: 2 orders

Only purchase_orders got a column: @ManyToOne with @JoinColumn is the owning side, while mappedBy = "customer" marks the list as the inverse view of that same foreign key. Saving a customer inserted its orders because of cascade = CascadeType.ALL.

The first block is the N+1 problem: one query for the customers, then one more per customer the moment getOrders() is touched, because @OneToMany collections are lazy. Three customers cost four queries; a page of 100 would cost 101. left join fetch loads everything in one query. The TransactionTemplate keeps a transaction open while the lazy collection is read — touching it after the transaction ends throws LazyInitializationException.

Transactions

@Service
public class PricingService {
    private final ProductRepository products;
    public PricingService(ProductRepository products) { this.products = products; }

    @Transactional
    public void discount(String category, String factor, boolean fail) {
        for (Product p : products.findByCategory(category))
            p.setPrice(p.getPrice().multiply(new BigDecimal(factor)).setScale(2, RoundingMode.HALF_UP));
        // no save() call: changes to managed entities are flushed at commit ("dirty checking")
        if (fail) throw new IllegalStateException("payment provider down");
    }

    @Transactional
    public void discountChecked(String category, String factor) throws Exception {
        discount(category, factor, false);
        throw new Exception("checked failure");                 // checked: does NOT roll back by default
    }
}

// TxDemo.run(), after products.saveAll(SampleData.products()); prices(c) lists name=price for a category
pricing.discount("furniture", "0.90", false);
System.out.println("committed:   " + prices("furniture"));

try { pricing.discount("electronics", "0.50", true); }
catch (IllegalStateException e) { System.out.println("caught: " + e.getMessage()); }
System.out.println("rolled back: " + prices("electronics"));

try { pricing.discountChecked("electronics", "0.50"); }
catch (Exception e) { System.out.println("caught: " + e.getMessage()); }
System.out.println("committed!   " + prices("electronics"));

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=tx
committed:   [Desk=179.10, Chair=116.55, Lamp=31.95]
caught: payment provider down
rolled back: [Keyboard=49.99, Mouse=19.99, Monitor=189.00, USB Cable=7.99]
caught: checked failure
committed!   [Keyboard=25.00, Mouse=10.00, Monitor=94.50, USB Cable=4.00]

Three lessons in five lines. The furniture prices changed although nothing called save(): entities loaded inside a transaction are managed, and Hibernate writes their changes at commit (dirty checking). The IllegalStateException rolled every price change back. But the checked Exception committed the 50% discount: by default @Transactional rolls back only for unchecked exceptions. Also note discountChecked calls discount on this — that call does not go through Spring’s proxy, so discount’s own annotation is ignored and it simply joins the outer transaction. Put @Transactional on service methods called from outside the class.

Practice

1. Make the bulk reprice query leave no stale entities behind, so the last line of the jpql example reports the Lamp’s new price.

    @Modifying(clearAutomatically = true)          // empty the persistence context afterwards
    @Query("update Product p set p.price = p.price * :factor where p.category = :category")
    int reprice(@Param("category") String category, @Param("factor") BigDecimal factor);

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=jpql | tail -1
repriced 3 rows; Lamp is now 39.05

2. Make discountChecked roll back too, and relabel the last line of TxDemo as after checked:.

    @Transactional(rollbackFor = Exception.class)
    public void discountChecked(String category, String factor) throws Exception {

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=tx | tail -2
caught: checked failure
after checked: [Keyboard=49.99, Mouse=19.99, Monitor=189.00, USB Cable=7.99]

3. Add a derived query returning the two most expensive products, without writing any JPQL.

    List<Product> findTop2ByOrderByPriceDesc();

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=derived | tail -1
top 2 by price: [Product[4, Desk, furniture, 199.00], Product[3, Monitor, electronics, 189.00]]

4. Fetch customers whose name starts with a prefix with their orders in one query, using @EntityGraph on a derived query instead of JPQL.

    @EntityGraph(attributePaths = "orders")
    List<Customer> findByNameStartingWith(String prefix);

$ java -jar target/store-0.0.1-SNAPSHOT.jar --spring.profiles.active=relations --spring.jpa.show-sql=true | sed -n '/StartingWith/,$p' | grep -v 'drop table'
--- findByNameStartingWith("G")
Hibernate: select c1_0.id,c1_0.name,o1_0.customer_id,o1_0.id,o1_0.total from customer c1_0 left join purchase_orders o1_0 on c1_0.id=o1_0.customer_id where c1_0.name like ? escape '\'
Grace: 2 orders