Spring Specification Criteria Multiple Joins ? How?

Question

I got stuck using a Spring Project with Spring Data + specification + criteria api.

I will try to simulate the situation with general entities we used write to get easy example.

The Entities: Consider all attributes of the each entity is passed on the constructor showed below

Country(Long id, String name, String iso)
State(Long id, String name, String iso)
City(Long id, String name, String iso)

This is my repository:

public interface CityRepository extends PagingAndSortingRepository<City, Integer>, JpaSpecificationExecutor<City> {

}

As you can see, I don't need to implement anything on the repository

This is my service

@Service
@Transactional
public class CityService {

    @Autowired
    private CityRepository cityRepository;

    @Transactional(readOnly = true)
    public CityListVO findByNameLike(String name, PageRequest pageRequest) {
        name = "%" + name + "%";

        if (pageRequest == null) {
            List<City> result = cityRepository.findAll(fillGridCriteria(name));
            return new CityListVO(1, result.size(), result);
        } else {
            Page<City> result = cityRepository. findAll(fillGridCriteria(name), pageRequest);
            return new CityListVO(result.getTotalPages(), result.getTotalElements(), result.getContent());
        }

    }

    private static Specification<City> fillGridCriteria(String name) {
        return new Specification<City>() {

            @Override
            public Predicate toPredicate(
                    Root<City> root,
                    CriteriaQuery<?> query,
                    CriteriaBuilder builder) { 

             /*
                The current return I can do a like by name, and it works fine. 

                My problem is if for any reason I need to do multiple joins like the folow jpql: 
                select ci FROM City ci, State st, Country co where ci.st = st AND st.co = co AND co.name = 'Canada';

                How to do this from here ? Inside this method.
                How is gonna be the return for this method ?

             */

                return builder.like(root.get("name"), name.trim());
            }

        };
    }

}

Answer 1

Let's assume you want all the cities that their country's name like name and you have a relational Model in which :

Country(Long id, String name, String iso)
State(Long id,Long country, String name, String iso)
City(Long id, Long state, String name, String iso)

Predicate:

private static Specification<City> fillGridCriteria(String name) {
    return new Specification<City>() {

        @Override
        public Predicate toPredicate(
                Root<City> root,
                CriteriaQuery<?> query,
                CriteriaBuilder builder) { 

          return 
            builder.like(root.get("state").get("country").get("name"), name.trim());
        }

    };
}