Query Optimization in Derby with Spring Boot

September 25, 2025

#1

This guide shows how to improve query performance in Apache Derby when using Spring Boot with JPA and Hibernate.

1. Add Dependencies

In pom.xml:

<dependency>
  <groupId>org.apache.derby</groupId>
  <artifactId>derby</artifactId>
  <scope>runtime</scope>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

2. Configure Derby

In application.properties:

spring.datasource.url=jdbc:derby:memory:demoDB;create=true
spring.datasource.driver-class-name=org.apache.derby.jdbc.EmbeddedDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

3. Create Entity with Indexes

import jakarta.persistence.*;

@Entity
@Table(
    name = "orders",
    indexes = {@Index(name = "idx_customer", columnList = "customer")}
)
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String customer;
    private double amount;

    // getters and setters
}

Indexes help Derby speed up queries on frequently searched columns.

4. Repository with Queries

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByCustomer(String customer);
}

Spring Data generates optimized queries under the hood.

5. Use Pagination for Large Data

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

public interface OrderRepository extends JpaRepository<Order, Long> {
    Page<Order> findByCustomer(String customer, Pageable pageable);
}

6. Service with Query Hints

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class OrderService {
    @PersistenceContext
    private EntityManager em;

    public List<Order> getOrdersByCustomer(String customer) {
        return em.createQuery("SELECT o FROM Order o WHERE o.customer = :cust", Order.class)
                 .setParameter("cust", customer)
                 .setHint("org.hibernate.fetchSize", "50")
                 .getResultList();
    }
}

7. Test Performance

Run:

mvn spring-boot:run

Check queries in logs and verify Derby uses indexes for faster lookups.