Queries

JPQL vs native SQL vs Criteria API?

	Written against	Use when
JPQL / HQL	entities and fields (`select p from Product p`)	most queries; portable; returns managed entities
Native SQL	tables and columns	DB-specific features: window functions, JSON, hints, upserts
Criteria API	a type-safe Java builder	dynamic queries built from optional filters

How do derived query methods work in Spring Data JPA?

Spring Data parses the method name at startup: a prefix (find/count/exists/delete), By, then properties joined with And/Or and keywords such as Between, LessThan, Containing, In, OrderBy, Top. A wrong property name fails at startup.

List<Product> findByCategoryNameAndPriceLessThanOrderByPriceAsc(String category, BigDecimal max);
Optional<User> findByEmailIgnoreCase(String email);
List<Product> findTop5ByOrderByCreatedAtDesc();
boolean existsBySku(String sku);

Tip: When a name gets too long, switch to @Query.

How do you write update/delete queries with @Query?

Add @Modifying and run it inside a transaction. A bulk statement bypasses the persistence context, so managed entities can become stale: use clearAutomatically = true (and flushAutomatically = true to write pending changes first).

@Transactional
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query("update Product p set p.price = p.price * :factor where p.category.id = :categoryId")
int repriceCategory(@Param("factor") BigDecimal factor, @Param("categoryId") Long categoryId);

What are projections? Interface vs DTO (record) projections?

Projections select only some columns instead of whole entities: less data, no dirty checking, no lazy-loading surprises. Ideal for read-only screens and APIs.

// Interface projection: Spring creates a proxy
interface ProductSummary { String getName(); BigDecimal getPrice(); }
List<ProductSummary> findByCategoryName(String category);

// DTO projection with a record and a constructor expression
record ProductCard(Long id, String name, BigDecimal price) {}

@Query("select new com.shopease.dto.ProductCard(p.id, p.name, p.price) from Product p where p.stock > 0")
List<ProductCard> findInStockCards();

How do you build dynamic search queries (optional filters)?

Use the Criteria API, or in Spring Data use Specifications (JpaSpecificationExecutor) and combine only the filters the user supplied. Alternatives: Querydsl, or Query by Example for simple equality filters.

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

static Specification<Product> nameContains(String q) {
    return (root, query, cb) -> q == null ? null : cb.like(cb.lower(root.get("name")), "%" + q.toLowerCase() + "%");
}
static Specification<Product> priceBelow(BigDecimal max) {
    return (root, query, cb) -> max == null ? null : cb.lessThan(root.get("price"), max);
}

Page<Product> page = repo.findAll(Specification.where(nameContains(q)).and(priceBelow(max)), pageable);

Page vs Slice vs List in Spring Data pagination?

Return type	Extra COUNT query	Knows
`Page<T>`	yes	total elements, total pages, hasNext
`Slice<T>`	no (fetches size + 1 rows)	only hasNext; good for infinite scroll
`List<T>` with Pageable	no	just the rows

Pageable pageable = PageRequest.of(0, 20, Sort.by("price").descending());
Page<Product> page = productRepository.findByCategoryName("Mobiles", pageable);

Note: Page numbers are zero-based. For very deep pages, keyset (seek) pagination (where id > :lastId) is faster than OFFSET.

How do you prevent SQL injection with JPA?

Always bind values as parameters (:name or ?1); never build JPQL/SQL by concatenating user input. Derived queries, Criteria and Specifications bind parameters automatically. Dynamic sort columns cannot be bound, so whitelist them.

// BAD: injectable
em.createQuery("select u from User u where u.email = '" + email + "'");

// GOOD
em.createQuery("select u from User u where u.email = :email", User.class)
  .setParameter("email", email)
  .getResultList();