package com.example.demo.repository;
import com.example.demo.model.User;
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.Predicate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
public class UserSpecifications {
public static Specification<User> hasName(String name) {
return (root, query, cb) ->
name == null ? null : cb.like(cb.lower(root.get("name")),
"%" + name.toLowerCase() + "%");
}
public static Specification<User> hasEmail(String email) {
return (root, query, cb) ->
email == null ? null : cb.equal(root.get("email"), email);
}
public static Specification<User> isActive(Boolean active) {
return (root, query, cb) ->
active == null ? null : cb.equal(root.get("active"), active);
}
public static Specification<User> createdBetween(
LocalDateTime start,
LocalDateTime end
) {
return (root, query, cb) -> {
if (start == null && end == null) return null;
if (start == null) return cb.lessThanOrEqualTo(root.get("createdAt"), end);
if (end == null) return cb.greaterThanOrEqualTo(root.get("createdAt"), start);
return cb.between(root.get("createdAt"), start, end);
};
}
public static Specification<User> buildDynamicQuery(
String name,
String email,
Boolean active,
LocalDateTime startDate,
LocalDateTime endDate
) {
return (root, query, cb) -> {
List<Predicate> predicates = new ArrayList<>();
if (name != null && !name.isEmpty()) {
predicates.add(cb.like(cb.lower(root.get("name")),
"%" + name.toLowerCase() + "%"));
}
if (email != null && !email.isEmpty()) {
predicates.add(cb.equal(root.get("email"), email));
}
if (active != null) {
predicates.add(cb.equal(root.get("active"), active));
}
if (startDate != null) {
predicates.add(cb.greaterThanOrEqualTo(
root.get("createdAt"), startDate));
}
if (endDate != null) {
predicates.add(cb.lessThanOrEqualTo(
root.get("createdAt"), endDate));
}
return cb.and(predicates.toArray(new Predicate[0]));
};
}
}
// Usage:
// Page<User> results = userRepository.findAll(
// Specification.where(UserSpecifications.hasName("John"))
// .and(UserSpecifications.isActive(true)),
// PageRequest.of(0, 20, Sort.by("createdAt").descending())
// );
package com.example.demo.repository;
import com.example.demo.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
@Repository
public interface UserRepository extends JpaRepository<User, Long>,
JpaSpecificationExecutor<User> {
// Query method - derived from method name
Optional<User> findByEmail(String email);
List<User> findByNameContainingIgnoreCase(String name);
List<User> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);
boolean existsByEmail(String email);
long countByActiveTrue();
// Custom JPQL query
@Query("SELECT u FROM User u WHERE u.email = :email AND u.active = true")
Optional<User> findActiveUserByEmail(@Param("email") String email);
// JPQL with pagination
@Query("SELECT u FROM User u WHERE u.name LIKE %:keyword%")
Page<User> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
// Native SQL query
@Query(value = "SELECT * FROM users WHERE created_at > :date", nativeQuery = true)
List<User> findUsersCreatedAfter(@Param("date") LocalDateTime date);
// Projection - returns only specific fields
@Query("SELECT u.name as name, u.email as email FROM User u")
List<UserProjection> findAllUserProjections();
// Custom update query
@Query("UPDATE User u SET u.active = :active WHERE u.id = :id")
void updateActiveStatus(@Param("id") Long id, @Param("active") boolean active);
interface UserProjection {
String getName();
String getEmail();
}
}
