Spring Data JPA

// Fix 1: JOIN FETCH in JPQL
@Query("SELECT a FROM Author a JOIN FETCH a.books")
List<Author> findAllWithBooks();

// Fix 2: EntityGraph (Spring Data)
@EntityGraph(attributePaths={"books"})
List<Author> findAll();

// Fix 3: Batch for large sets
@BatchSize(size=50) private List<Book> books;

// SAVE
save(entity)           // INSERT if id null, else UPDATE
saveAll(iterable)      // bulk
saveAndFlush(entity)   // save + immediate DB sync

// FIND
findById(id)           // Optional<T> — hits DB now
getReferenceById(id)   // lazy proxy, SELECT on access
findAll()              // ⚠️ no Pageable = full table scan
findAllById(ids)       // WHERE id IN (...)
findAll(pageable)      // paginated
findAll(sort)          // sorted

// CHECK
existsById(id)         // boolean
count()                // SELECT COUNT(*)

// DELETE
deleteById(id)
delete(entity)
deleteAll()            // ⚠️ clears whole table
deleteAllInBatch()     // single DELETE — faster
flush()                // sync context to DB

// Numeric
List<P> findByPriceLessThan(double p);
List<P> findByPriceBetween(double min, double max);
List<P> findByStockGreaterThanAndCategoryIn(
              int s, Collection<String> cats);

// String
List<P> findByNameIgnoreCase(String name);
List<P> findByNameContainingIgnoreCase(String kw);
List<P> findByNameStartingWith(String prefix);

// Null / Boolean / Enum
List<P> findByDescriptionIsNull();
List<P> findByActiveTrue();
List<P> findByStatusIn(Collection<Status> s);

// Date
List<P> findByCreatedAtAfter(LocalDateTime d);
List<P> findByCreatedAtBetween(LocalDateTime f, LocalDateTime t);

// Limiting
Optional<P> findFirstByOrderByPriceAsc();
List<P>     findTop5ByOrderByRatingDesc();

// Count / Exists / Delete
long    countByCategory(String cat);
boolean existsByEmail(String email);
long    deleteByCreatedAtBefore(LocalDateTime d);

@Query("SELECT u FROM User u WHERE u.email=:e")
Optional<User> byEmail(@Param("e") String e);

// JOIN FETCH — solves N+1
@Query("SELECT p FROM Post p JOIN FETCH p.comments WHERE p.author.id=:id")
List<Post> withComments(@Param("id") Long id);

// Aggregate
@Query("SELECT AVG(p.price) FROM Product p WHERE p.category=:c")
Double avgPrice(@Param("c") String c);

// UPDATE/DELETE — @Modifying required
@Modifying @Transactional
@Query("UPDATE User u SET u.active=false WHERE u.lastLogin<:d")
int deactivateOld(@Param("d") LocalDate d);

@Query(value="SELECT * FROM users WHERE email LIKE %:d",
       nativeQuery=true)
List<User> byDomain(@Param("d") String d);

// Native + Pagination needs countQuery
@Query(value="SELECT * FROM products ORDER BY price DESC",
       countQuery="SELECT COUNT(*) FROM products",
       nativeQuery=true)
Page<Product> allNative(Pageable p);

public interface UserSummary {
  String getName();
  String getEmail();
  @Value("#{target.firstName+' '+target.lastName}")
  String getFullName();  // computed field
}
List<UserSummary> findByActive(boolean a);

public record UserDTO(String name, String email, long cnt) {}

@Query("""
  SELECT new com.example.UserDTO(u.name, u.email, COUNT(p))
  FROM User u LEFT JOIN u.posts p GROUP BY u.id""")
List<UserDTO> findUserSummaries();

<T> List<T> findByActive(boolean a, Class<T> type);

repo.findByActive(true, User.class);        // full
repo.findByActive(true, UserSummary.class); // slim
repo.findByActive(true, UserDTO.class);     // dto

// alias must match getter name (camelCase)
@Query(value="SELECT p.name, p.category_id AS categoryId FROM products p",
       nativeQuery=true)
List<ProductSummary> findActiveSummaries();

// Repository — add Pageable param
Page<User>  findByActive(boolean a, Pageable p);
Slice<User> findByDept(String d, Pageable p);

// Build Pageable (0-based pages!)
PageRequest.of(0, 10);
PageRequest.of(2, 10, Sort.by("name"));

// Page<T> metadata
page.getContent()        // List<T>
page.getNumber()         // current page (0-based)
page.getTotalElements()  // total DB rows
page.getTotalPages()     // ceil(total/size)
page.isFirst() / isLast() / hasNext()
page.map(UserDTO::fromEntity) // transform in-place

// Sort patterns
Sort.by("name")                    // ASC default
Sort.by(Direction.DESC, "name")
Sort.by("dept").ascending()
    .and(Sort.by("salary").descending())  // chained
// Fine-grained
Sort.by(
  Sort.Order.asc("lastName").nullsLast(),
  Sort.Order.desc("salary").nullsFirst(),
  Sort.Order.asc("name").ignoreCase()
)

// REST controller pattern
@GetMapping("/users")
public Page<UserDTO> list(
  @RequestParam(defaultValue="0")  int page,
  @RequestParam(defaultValue="10") int size,
  @RequestParam(defaultValue="id") String sort) {
  return repo.findAll(PageRequest.of(page, size,
    Sort.by(sort))).map(UserDTO::fromEntity);
}

// 1. Specification class
public class EmployeeSpec {
  public static Specification<Employee>
  hasDepartment(String dept) {
    return (root, query, cb) ->
      dept == null ? null
      : cb.equal(root.get("department"), dept);
  }

  public static Specification<Employee>
  salaryBetween(Double min, Double max) {
    return (root, query, cb) ->
      cb.between(root.get("salary"), min, max);
  }
}

// 2. Service — compose specs
Specification<Employee> spec =
  Specification.where(EmployeeSpec.hasDepartment(dept))
               .and(EmployeeSpec.salaryBetween(min, max));
return repo.findAll(spec, pageable);

try {
  txManager.begin();        // 1. START TX
  yourMethod();             // 2. run code
  persistenceCtx.flush();  // 3. sync dirty entities
  txManager.commit();       // 4. COMMIT
} catch (RuntimeException e) {
  txManager.rollback();     // 5. ROLLBACK
  throw e;
}

// ⚠️ Checked exceptions do NOT rollback by default!
@Transactional(rollbackFor=Exception.class)

// ⚠️ Self-invocation BREAKS @Transactional
public void outer() {
  inner();  // this.inner() — bypasses proxy!
}  // Fix: inject self, or split to two beans

@Transactional(
  propagation  = Propagation.REQUIRED,
  isolation    = Isolation.READ_COMMITTED,
  readOnly     = false,
  timeout      = 30,
  rollbackFor  = {CustomException.class},
  noRollbackFor= {BusinessWarning.class}
)

@Service
@Transactional(readOnly=true)  // class default: reads
public class ProductService {

  // Inherits readOnly=true ✅
  public Page<Product> findAll(Pageable p) {
    return repo.findAll(p);
  }

  // Override for writes ✅
  @Transactional  // readOnly=false
  public Product save(Product p) {
    return repo.save(p);
  }

  @Transactional
  public Product update(Long id, ProductDTO dto) {
    Product p = repo.findById(id).orElseThrow();
    p.setName(dto.getName());
    return p; // no save()! dirty check auto-flushes
  }
}