MS Lesson 18: Dynamic Search

package az.etibarli.demospecification.service;

import az.etibarli.demospecification.dto.request.ProductSearchRequest;
import az.etibarli.demospecification.dto.request.SearchMatchMode;
import az.etibarli.demospecification.entity.Product;
import az.etibarli.demospecification.repository.ProductRepository;
import az.etibarli.demospecification.specification.ProductSpecifications;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Query;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import lombok.RequiredArgsConstructor;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@RequiredArgsConstructor
public class ProductService {

    private final EntityManagerFactory emf;
    private final ProductRepository productRepository;

    @Transactional(readOnly = true)
    public Product findById(Long id) {
        return productRepository.findById(id).get();
    }

    @Transactional(readOnly = true)
    public List<Product> findWithCriteriaBuilder(ProductSearchRequest request) {
        EntityManager em = emf.createEntityManager();
        try {
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<Product> cq = cb.createQuery(Product.class);
            Root<Product> root = cq.from(Product.class);

            List<Predicate> predicates = new ArrayList<>();

            if (request.getSku() != null) {
                predicates.add(cb.equal(root.get("sku"), request.getSku()));
            }
            if (request.getActive() != null) {
                predicates.add(cb.equal(root.get("active"), request.getActive()));
            }
            if (request.getName() != null && !request.getName().isBlank()) {
                String pattern = "%" + request.getName().toLowerCase() + "%";
                predicates.add(cb.like(cb.lower(root.get("name")), pattern));
            }
            if (request.getMinPrice() != null && request.getMaxPrice() != null) {
                predicates.add(cb.between(root.get("price"), request.getMinPrice(), request.getMaxPrice()));
            } else if (request.getMinPrice() != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("price"), request.getMinPrice()));
            } else if (request.getMaxPrice() != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("price"), request.getMaxPrice()));
            }
            if (request.getCreatedAtFrom() != null && request.getCreatedAtTo() != null) {
                predicates.add(cb.between(
                        root.get("createdAt"), request.getCreatedAtFrom(), request.getCreatedAtTo()));
            } else if (request.getCreatedAtFrom() != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("createdAt"), request.getCreatedAtFrom()));
            } else if (request.getCreatedAtTo() != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("createdAt"), request.getCreatedAtTo()));
            }

            if (!predicates.isEmpty()) {
                Predicate combined = request.getMatchMode() == SearchMatchMode.ANY
                        ? cb.or(predicates.toArray(Predicate[]::new))
                        : cb.and(predicates.toArray(Predicate[]::new));
                cq.where(combined);
            }

            return em.createQuery(cq).getResultList();
        } finally {
            em.close();
        }
    }

    public List<Product> findWithNativeQuery(ProductSearchRequest request) {
        EntityManager em = emf.createEntityManager();
        Map<String, Object> params = new HashMap<>();

        StringBuilder sql = new StringBuilder("SELECT * FROM products WHERE 1=1 ");

        if (request.getSku() != null) {
            sql.append(" AND sku = :sku");
            params.put("sku", request.getSku());
        }
        if (request.getActive() != null) {
            sql.append(" AND active = :active");
            params.put("active", request.getActive());
        }
        if (request.getName() != null && !request.getName().isBlank()) {
            sql.append(" AND LOWER(name) LIKE LOWER(:name)");
            params.put("name", "%" + request.getName() + "%");
        }
        if (request.getMinPrice() != null) {
            sql.append(" AND price >= :minPrice");
            params.put("minPrice", request.getMinPrice());
        }
        if (request.getMaxPrice() != null) {
            sql.append(" AND price <= :maxPrice");
            params.put("maxPrice", request.getMaxPrice());
        }
        if (request.getCreatedAtFrom() != null) {
            sql.append(" AND created_at >= :createdAtFrom");
            params.put("createdAtFrom", request.getCreatedAtFrom());
        }
        if (request.getCreatedAtTo() != null) {
            sql.append(" AND created_at <= :createdAtTo");
            params.put("createdAtTo", request.getCreatedAtTo());
        }

        try {
            Query query = em.createNativeQuery(sql.toString(), Product.class);
            params.forEach(query::setParameter);
            @SuppressWarnings("unchecked")
            List<Product> result = query.getResultList();
            return result;
        } finally {
            em.close();
        }
    }

}