Spring Data JPA

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("select o from Order o where o.customer.id = :customerId and o.total >= :minTotal")
    List<Order> findLargeOrdersForCustomer(@Param("customerId") Long customerId,
                                            @Param("minTotal") BigDecimal minTotal);

    @Modifying
    @Query("update Order o set o.total = o.total * :factor where o.placedAt < :cutoff")
    int applyLegacyDiscount(@Param("factor") BigDecimal factor, @Param("cutoff") Instant cutoff);

    // nativeQuery = true switches from JPQL to vendor SQL
    @Query(value = """
            select c.id, c.full_name, sum(o.total) as lifetime_value
            from customers c join orders o on o.customer_id = c.id
            group by c.id, c.full_name
            having sum(o.total) > :threshold
            """, nativeQuery = true)
    List<Object[]> findHighValueCustomers(@Param("threshold") BigDecimal threshold);
}

public interface CustomerRepository extends JpaRepository<Customer, Long>,
        JpaSpecificationExecutor<Customer> {
}

public final class CustomerSpecifications {

    public static Specification<Customer> hasStatus(CustomerStatus status) {
        return (root, query, cb) -> status == null ? null : cb.equal(root.get("status"), status);
    }

    public static Specification<Customer> nameContains(String fragment) {
        return (root, query, cb) -> fragment == null ? null
                : cb.like(cb.lower(root.get("fullName")), "%" + fragment.toLowerCase() + "%");
    }
}

// composing at call time, skipping null (inactive) filters
Specification<Customer> spec = Specification.where(CustomerSpecifications.hasStatus(status))
        .and(CustomerSpecifications.nameContains(nameFilter));
List<Customer> results = customerRepository.findAll(spec);

// interface (closed) projection -- Spring Data generates a proxy backed by the query result
public interface CustomerSummary {
    Long getId();
    String getFullName();
}

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    List<CustomerSummary> findByStatus(CustomerStatus status);

    // DTO projection -- a constructor expression, instantiated directly by JPQL
    @Query("select new com.example.dto.CustomerDto(c.id, c.fullName, c.email) from Customer c where c.status = :status")
    List<CustomerDto> findDtosByStatus(@Param("status") CustomerStatus status);

    // dynamic projection -- the caller picks the projection type per call
    <T> List<T> findByEmail(String email, Class<T> projectionType);
}

// dynamic projection usage
List<CustomerSummary> summaries = customerRepository.findByEmail(email, CustomerSummary.class);

@Entity
@Table(name = "places")
public class Place {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @Column(columnDefinition = "geometry(Point,4326)")
    private Point location;   // org.locationtech.jts.geom.Point
}

public interface PlaceRepository extends JpaRepository<Place, Long> {

    @Query(value = """
            select * from places
            where ST_DWithin(location::geography, ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography, :radiusMeters)
            """, nativeQuery = true)
    List<Place> findWithinRadius(@Param("lon") double lon, @Param("lat") double lat,
                                  @Param("radiusMeters") double radiusMeters);

    @Query(value = """
            select p.*, ST_Distance(p.location::geography, ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography) as distance
            from places p
            order by distance
            limit :limit
            """, nativeQuery = true)
    List<Place> findNearest(@Param("lon") double lon, @Param("lat") double lat, @Param("limit") int limit);
}

@Service
public class OrderService {

    private final OrderRepository orderRepository;
    private final InventoryService inventoryService;

    public OrderService(OrderRepository orderRepository, InventoryService inventoryService) {
        this.orderRepository = orderRepository;
        this.inventoryService = inventoryService;
    }

    @Transactional
    public Order placeOrder(Order order) {
        inventoryService.reserveStock(order);   // participates in the same transaction (REQUIRED, the default)
        return orderRepository.save(order);
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void recordAuditEntry(Long orderId, String message) {
        // runs in its own transaction, committed independently of the caller's outcome
    }

    @Transactional(readOnly = true)
    public List<Order> listOrders(Long customerId) {
        return orderRepository.findByCustomerId(customerId);
    }
}

@Entity
public class Account {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private BigDecimal balance;

    @Version
    private Long version;

    // getters and setters omitted
}

@Service
public class AccountService {

    private final AccountRepository accounts;

    public AccountService(AccountRepository accounts) {
        this.accounts = accounts;
    }

    @Transactional
    public void withdraw(Long accountId, BigDecimal amount) {
        Account account = accounts.findById(accountId).orElseThrow();
        account.setBalance(account.getBalance().subtract(amount));
        try {
            accounts.save(account); // increments and checks @Version under the hood
        } catch (ObjectOptimisticLockingFailureException ex) {
            // someone else updated this account first -- reload and retry, or surface a 409 Conflict
            throw new ConcurrentModificationException("Account " + accountId + " was modified concurrently", ex);
        }
    }
}

public interface AccountRepository extends JpaRepository<Account, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(@QueryHint(name = "jakarta.persistence.lock.timeout", value = "3000"))
    Optional<Account> findWithLockById(Long id);
}

@Transactional
public void withdrawExclusively(Long accountId, BigDecimal amount) {
    Account account = accounts.findWithLockById(accountId).orElseThrow();  // row locked until commit
    account.setBalance(account.getBalance().subtract(amount));
}

@SpringBootApplication
@EnableJpaAuditing(auditorAwareRef = "auditorProvider")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Bean
    public AuditorAware<String> auditorProvider() {
        return () -> Optional.ofNullable(SecurityContextHolder.getContext().getAuthentication())
                .map(Authentication::getName);
    }
}

@Entity
@EntityListeners(AuditingEntityListener.class)
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @CreatedDate
    private Instant createdAt;

    @LastModifiedDate
    private Instant updatedAt;

    @CreatedBy
    private String createdBy;

    @LastModifiedBy
    private String updatedBy;
}

@Repository
public class ReportingRepository {

    private final JdbcClient jdbcClient;

    public ReportingRepository(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }

    public List<CustomerSummary> findActiveSummaries() {
        return jdbcClient.sql("select id, full_name from customers where status = :status")
                .param("status", "ACTIVE")
                .query(CustomerSummary.class)
                .list();
    }

    public Optional<BigDecimal> findLifetimeValue(long customerId) {
        return jdbcClient.sql("select sum(total) from orders where customer_id = ?")
                .param(customerId)
                .query(BigDecimal.class)
                .optional();
    }

    public int deactivateStaleCustomers(Instant cutoff) {
        return jdbcClient.sql("update customers set status = 'INACTIVE' where last_login < :cutoff")
                .param("cutoff", cutoff)
                .update();
    }
}

@Repository
public class LegacyReportingRepository {

    private final NamedParameterJdbcTemplate namedJdbcTemplate;
    private final JdbcTemplate jdbcTemplate;

    public LegacyReportingRepository(NamedParameterJdbcTemplate namedJdbcTemplate) {
        this.namedJdbcTemplate = namedJdbcTemplate;
        this.jdbcTemplate = namedJdbcTemplate.getJdbcTemplate();
    }

    private static final RowMapper<CustomerSummary> SUMMARY_MAPPER =
            (rs, rowNum) -> new CustomerSummary(rs.getLong("id"), rs.getString("full_name"));

    public List<CustomerSummary> findByStatus(String status) {
        Map<String, Object> params = Map.of("status", status);
        return namedJdbcTemplate.query(
                "select id, full_name from customers where status = :status", params, SUMMARY_MAPPER);
    }

    public int[] batchInsertAuditEvents(List<AuditEvent> events) {
        String sql = "insert into audit_events (order_id, message, recorded_at) values (?, ?, ?)";
        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                AuditEvent event = events.get(i);
                ps.setLong(1, event.orderId());
                ps.setString(2, event.message());
                ps.setTimestamp(3, Timestamp.from(event.recordedAt()));
            }

            @Override
            public int getBatchSize() {
                return events.size();
            }
        });
    }
}