How to write a select query in Spring Data Jpa tha

问题:

I am trying to display results when the user search for several combinations, the options he can search by -

1. country
2. state
3. district
4. zipCode

example combinations he can search are (country,state), (country,district), (state,zipCode) etc..

I am using Spring Data Jpa for my queries and pagination as well.

I am new to Spring Jpa, Any help will be appreciated.

Thank you!

回答1:

There is a very simple trick to perform what do you need ;)

@Query("select e from Entity e where "
      +"(:country = '' or e.country like '%:country%') and "
      +"(:state = '' or e.state like '%:state%') and "
      +"(:district = '' or e.district like '%:district%') and "
      +"(:zipCode = '' or e.zipCode like '%:zipCode%')"
Page<Entity> advancedSearch(@Param("country") String country,
                            @Param("state") String state,
                            @Param("district") String district,
                            @Param("zipCode") String zipCode,
                            Pageable page);

So when you need to invoke advancedSearch you can set parameters only you need and other set to "":

Page<Entity> enityPages = advancedSearch("", "California", "1st", "", new PageRequest(...)); 

回答2:

Use Criteria :

public Collection<? extends Profile> get(Collection<? extends Integer> ids, boolean deleted) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Profile> q = cb.createQuery(Profile.class);
    Root<Profile> r = q.from(Profile.class);

    if (deleted) {
        Predicate andClose = cb.and(cb.equal(r.get("deleted"), true));
        q.where(r.get("id").in(ids), andClose);
    } else {
        q.where(r.get("id").in(ids));
    }            

    q.orderBy(cb.asc(r.get("id")));

    TypedQuery<Profile> query = em.createQuery(q);

    return query.getResultList();
}

By the way you're free to add some if (... ) to use or not use some Predicate depending on the incoming arguments of get

回答3:

If you're extending the JpaRepository interface and letting Spring Data handle the wiring and auto-generation of the queries you can easily extend the interface to handle your scenarios without writing anything overly complicated:

public interface CustomerRepository extends JpaRepository<Customer, Integer> {
    public List<Customer> findAllByCountryAndState(String country, String State);

}

You can use this method as follows:

@Autowired
private CustomerRepository customerRepository;

public void testMethod(){
    List<Customer> customerList = customerRepository.findAllByCountryAndState("Canada","Ontario");
}

The Spring documentation has lots of examples: Spring Data Query Creation