Cheat SheetsSpring BootData Access

Data Access — Cheat Sheet

Spring Boot · 7 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org
Data Access
Spring Boot7 topicsQuick revision reference
1

Spring Data JPA Basics

Spring Data JPA generates repository implementations at runtime, eliminating boilerplate DAO code for standard CRUD and derived-query operations.

  • ✓Extend JpaRepository<Entity, ID> — Spring generates the implementation at runtime. No @Autowired implementation class needed.
  • ✓Derived query methods: Spring parses findByFieldAnd/Or/Between/In/Like… and generates the JPQL — no @Query needed for simple queries.
  • ✓@Query accepts JPQL (entity/field names) or native SQL (nativeQuery=true) for complex queries.
  • ✓@Modifying + @Transactional is required for @Query UPDATE and DELETE — without them Spring throws an exception.
  • ✓Return Page<T> by adding a Pageable parameter to any query method — Spring Data handles the COUNT query automatically.
  • ✓Projection interfaces (with getX() methods matching JPQL aliases) avoid loading full entities for read-only summary queries.
Java — JpaRepository
// 1. Entity
@Entity
@Table(name = "orders")
public class Order {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String reference;
    private String status;
    @Column(name = "customer_id")
    private Long customerId;
    private BigDecimal total;
    private LocalDateTime createdAt;
    // getters / setters
}

// 2. Repository — just an interface, Spring provides the implementation
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    // built-in: save, findById, findAll, delete, count, existsById, etc.
}

// 3. Service — inject and use
@Service
@RequiredArgsConstructor
public class OrderService {
    private final OrderRepository orderRepo;

    public Order create(Order order) {
        return orderRepo.save(order);     // INSERT
    }

    public Optional<Order> find(Long id) {
        return orderRepo.findById(id);    // SELECT … WHERE id=?
    }

    public Page<Order> list(Pageable page) {
        return orderRepo.findAll(page);   // SELECT with LIMIT/OFFSET
    }
}
2

JpaRepository & CrudRepository

JpaRepository extends PagingAndSortingRepository adding flush, batch-delete, and JPA-specific bulk operations on top of basic CRUD.

  • ✓Extend JpaRepository<Entity, Id> — Spring generates the implementation at runtime with no code required
  • ✓save() performs INSERT for new entities (no id) and UPDATE (merge) for managed/detached entities
  • ✓deleteAllInBatch() issues a single DELETE ... WHERE id IN (...) — much faster than N individual deleteById calls
  • ✓Derived method names generate JPQL automatically; use @Query for complex conditions to keep method names short
  • ✓Always use readOnly = true on @Transactional for query-only methods — reduces overhead and enables Hibernate optimisations
  • ✓Interface projections SELECT only specified columns; DTO projections (constructor expression) work for multi-table queries
Java — JpaRepository definition and core method usage
// Define repository — Spring generates the implementation
public interface OrderRepository extends JpaRepository<Order, Long> {
    // All JpaRepository methods available immediately:
    // save, findById, findAll, delete, count, existsById, ...
}

// Usage in service
@Service
@Transactional(readOnly = true)
public class OrderService {

    private final OrderRepository orderRepository;

    public Optional<Order> findById(Long id) {
        return orderRepository.findById(id);       // returns Optional — no NPE
    }

    public List<Order> findAll() {
        return orderRepository.findAll();
    }

    @Transactional
    public Order save(Order order) {
        return orderRepository.save(order);        // INSERT or UPDATE (merge)
    }

    @Transactional
    public void delete(Long id) {
        orderRepository.deleteById(id);
    }

    public long count() {
        return orderRepository.count();
    }

    // Batch operations — much faster than N individual deletes
    @Transactional
    public void deleteAll(List<Order> orders) {
        orderRepository.deleteAllInBatch(orders);  // single DELETE ... WHERE id IN (...)
    }
}
3

Custom Queries with @Query

@Query accepts JPQL or native SQL strings and can return projections, DTOs, or pageable results; @Modifying enables UPDATE/DELETE statements.

  • ✓@Query with JPQL references entity class and field names — independent of table/column names
  • ✓JOIN FETCH in @Query eagerly loads associations in one query, preventing N+1 for that method
  • ✓nativeQuery = true sends raw SQL — use for CTEs, window functions, or DB-specific functions
  • ✓@Modifying + @Transactional enables bulk UPDATE/DELETE that bypass entity lifecycle hooks
  • ✓clearAutomatically = true evicts stale first-level cache entries after a bulk update
  • ✓Native @Query with Pageable requires an explicit countQuery — Spring cannot auto-derive count for native SQL
Java — @Query with JPQL, JOIN FETCH, DTO projection, and Pageable
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Simple JPQL with named parameter
    @Query("SELECT o FROM Order o WHERE o.status = :status AND o.total > :minAmount")
    List<Order> findActiveOrdersAbove(@Param("status") OrderStatus status,
                                      @Param("minAmount") BigDecimal minAmount);

    // JOIN FETCH — load customer in same query (prevents N+1)
    @Query("SELECT o FROM Order o JOIN FETCH o.customer c WHERE c.email = :email")
    List<Order> findByCustomerEmail(@Param("email") String email);

    // DTO projection via constructor expression
    @Query("SELECT new com.example.dto.OrderSummaryDTO(o.id, o.status, o.total) " +
           "FROM Order o WHERE o.customer.id = :customerId")
    List<OrderSummaryDTO> findSummariesByCustomer(@Param("customerId") Long id);

    // Pageable with @Query — Spring adds ORDER BY and LIMIT automatically
    @Query("SELECT o FROM Order o WHERE o.status = :status")
    Page<Order> findByStatusPaged(@Param("status") OrderStatus status, Pageable pageable);

    // Count query for pagination (optional — Spring auto-derives it)
    @Query(value = "SELECT o FROM Order o WHERE o.status = :status",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status")
    Page<Order> findByStatusPagedWithCount(@Param("status") OrderStatus status,
                                            Pageable pageable);
}
4

Pagination & Sorting

Pass a Pageable object to repository methods; Spring Data returns a Page<T> containing the slice of results plus total-count metadata.

  • ✓Spring MVC resolves Pageable automatically from ?page=0&size=20&sort=field,direction request parameters.
  • ✓Page<T> issues a COUNT(*) query for totalElements; Slice<T> skips it — use Slice for infinite scroll.
  • ✓Always sanitise sort field names — never pass raw user input to Sort to prevent field-name injection.
  • ✓Use @PageableDefault to set default page size and sort direction when no query parameters are provided.
  • ✓Map Page<Entity> to a custom DTO before returning from the controller — do not leak JPA proxy internals.
  • ✓For large offsets (page=10000), consider cursor-based pagination — OFFSET scans all preceding rows.
Java — repository + controller with Pageable
// Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Spring Data generates: SELECT * FROM orders WHERE status=? LIMIT ? OFFSET ?
    // + COUNT(*) query for totalElements
    Page<Order> findByStatus(String status, Pageable pageable);

    // Custom JPQL with pagination
    @Query("SELECT o FROM Order o WHERE o.createdAt > :since")
    Page<Order> findRecentOrders(@Param("since") LocalDateTime since,
                                  Pageable pageable);

    // Slice<T>: no count query — use for infinite scroll
    Slice<Order> findByCustomerId(Long customerId, Pageable pageable);
}

// Controller — Spring resolves Pageable from ?page=0&size=20&sort=createdAt,desc
@RestController
@RequestMapping("/api/orders")
public class OrderController {

    @GetMapping
    public Page<OrderDto> list(
            @RequestParam(defaultValue = "PENDING") String status,
            @PageableDefault(size = 20, sort = "createdAt",
                             direction = Sort.Direction.DESC) Pageable pageable) {
        return orderRepository.findByStatus(status, pageable)
                              .map(orderMapper::toDto);
    }
}
5

Transaction Management

@Transactional wraps method execution in a DB transaction; propagation and isolation attributes control nested-transaction and concurrency behaviour.

  • ✓@Transactional works through a CGLIB proxy — self-invocation (calling a @Transactional method from the same class) bypasses the proxy and starts no transaction.
  • ✓Default rollback: only RuntimeException and Error. Checked exceptions do NOT roll back unless rollbackFor = Exception.class is configured.
  • ✓readOnly = true is a performance hint — it skips Hibernate dirty-checking and may enable DB-level optimisations like read replicas.
  • ✓REQUIRES_NEW suspends the outer transaction and opens a new independent one — useful for audit logs that must survive an outer rollback.
  • ✓REQUIRED (default propagation) joins an existing transaction; if none exists, it creates one.
  • ✓A @Transactional annotation on a private method has no effect — Spring proxies only intercept public method calls on injected beans.
Java — Spring @Transactional
@Service
@RequiredArgsConstructor
public class OrderService {

    private final OrderRepository   orderRepo;
    private final InventoryService  inventoryService;

    // ✅ Transactional — commits when method returns, rolls back on RuntimeException
    @Transactional
    public Order placeOrder(OrderRequest req) {
        Order order = orderRepo.save(new Order(req));
        inventoryService.reserve(req.getItems());  // same transaction — any RuntimeException
                                                   // here rolls back the whole unit of work
        return order;
    }

    // ✅ Read-only hint — lets the DB / ORM skip dirty-checking & flush
    @Transactional(readOnly = true)
    public Order getOrder(Long id) {
        return orderRepo.findById(id).orElseThrow();
    }

    // ✅ Also rollback on checked exceptions
    @Transactional(rollbackFor = Exception.class)
    public void importOrders(List<OrderRequest> orders) throws IOException {
        for (OrderRequest req : orders) {
            orderRepo.save(new Order(req));
        }
    }
}
6

Connection Pool — HikariCP

Spring Boot auto-configures HikariCP as the default connection pool; tuning maximumPoolSize, connectionTimeout, and keepaliveTime is critical for production.

  • ✓HikariCP is Spring Boot's default pool — ~50 μs acquire time, minimal overhead
  • ✓maximumPoolSize is the most critical tuning knob — too low queues requests, too high overwhelms the DB
  • ✓Set minimumIdle = maximumPoolSize for a fixed pool with no cold-start latency
  • ✓maxLifetime must be less than the DB server's wait_timeout to prevent stale connections
  • ✓keepaliveTime pings idle connections to prevent firewall/NAT dropping them
  • ✓Monitor hikaricp_connections_pending — non-zero means pool exhaustion; tune size or fix slow queries
Spring Boot — HikariCP key properties
# application.properties — HikariCP tuning
spring.datasource.hikari.maximum-pool-size=20       # max concurrent connections
spring.datasource.hikari.minimum-idle=20            # keep pool warm (= max for fixed pool)
spring.datasource.hikari.connection-timeout=30000   # 30s wait for connection from pool
spring.datasource.hikari.idle-timeout=600000        # 10min: release idle connections
spring.datasource.hikari.max-lifetime=1800000       # 30min: max connection age (rotate before DB server times out)
spring.datasource.hikari.keepalive-time=60000       # 1min: ping idle connections to prevent firewall drops
spring.datasource.hikari.pool-name=OrderDB-Pool     # name shown in JMX/logs

# Validate configuration at startup
spring.datasource.hikari.initialization-fail-timeout=1  # fail fast if DB unreachable
7

Spring Data REST

Exports repository operations as hypermedia-driven REST endpoints automatically, following HATEOAS principles with HAL or HAL-FORMS media types.

  • ✓Spring Data REST auto-generates CRUD + search endpoints from repository interfaces
  • ✓Responses are HAL+JSON with _links for navigation (self, collection, next/prev pages)
  • ✓@RepositoryRestResource(exported=false) hides a repository from the REST layer
  • ✓@Projection defines named field subsets; clients opt-in with ?projection=name
  • ✓@RepositoryEventHandler intercepts before/after save, create, delete events
  • ✓Use spring.data.rest.base-path=/api to namespace all generated endpoints
Java — repository export and search endpoints
@RepositoryRestResource(collectionResourceRel = "products", path = "products")
public interface ProductRepository extends PagingAndSortingRepository<Product, Long> {

    // Custom finder exposed at: GET /products/search/findByCategory?category=ELECTRONICS
    List<Product> findByCategory(@Param("category") String category);
}

// Generated endpoints:
// GET    /products          — paginated list with _links
// POST   /products          — create
// GET    /products/{id}     — single item
// PUT    /products/{id}     — full replace
// PATCH  /products/{id}     — partial update
// DELETE /products/{id}     — delete
Learn this free with Aria, your AI tutor → AiCanCode.org/learn/spring-boot