Spring Boot JPA Query modify dynamically-CodePudding

Using Spring boot,I am working on one business use case where i need to modify the JPA query generated at runtime based on configuration.

For Example .. if query that JPA generates is

select * from customers where id=1234

I want to modify it in runtime like based on user's logged in context. (Context has one attribute business unit) like given below ..

select * from customers where id=1234 and ***business_unit='BU001'***

Due to certain business use case restrictions i can't have statically typed query.

Using Spring boot and Postgres SQL.

thank you for help !!

CodePudding user response：

Try JPA criteria builder , it let you to create dynamics query programmatically.

Take look in this post

CodePudding user response：

What is stopping you to extract the business unit from the context and pass it to the query?

If you have this Entity

@Entity
CustomerEntity {
  Long id;
  String businessUnit;
  //geters   setters
}

you can add this query to your JPA Repository interface:

CustomerEntity findByIdAndBusinessUnit(Long id, String businessUnit)

This will generate the following "where" clause:

… where x.id=?1 and x.businessUnit=?2

for complete documentation check Spring Data Jpa Query creation guide.

CodePudding user response：

you would do something like this, this lets you dynamically define additional predicates you need in your query. if you don't want to have all the conditions in your query with @Query

The below example just adds a single predicate.

import java.util.ArrayList;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;

import study.spring.data.jpa.models.TicketPrice;

@Component
public class TricketPriceCriteriaRepository {
    @Autowired
    TicketPriceJpaRepository ticketPriceJpaRepository;

    public List<TicketPrice> findByCriteria(int price) {
        return ticketPriceJpaRepository.findAll(new Specification<TicketPrice>() {
            @Override
            public Predicate toPredicate(Root<TicketPrice> root, CriteriaQuery<?> query,
                    CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (price > 0) {
                    predicates.add(
                            criteriaBuilder.and(criteriaBuilder.greaterThan(root.get("basePrice"), price)));
                }
                // Add other predicates here based on your inputs 
                // Your session based predicate
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        });
    }
}

Your base repository would be like

// Other imports
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface TicketPriceJpaRepository
        extends JpaRepository<TicketPrice, Long>, JpaSpecificationExecutor<TicketPrice> {}

the model consists basePrice

@Column(name = "base_price")
private BigDecimal basePrice;