Spring PagingAndSortingRepository - find by name

Question

Problem: I am trying to load into the grid only the Purchase Orders for which country name is...let's say Ireland. Thing is that I don't really know if that's possible. So far I've tried to change the method in the service class executeQueryFindAll() to something like this.

 private Page<PurchaseOrder> executeQueryFindAll(int page, int maxResults) {
    final PageRequest pageRequest = new PageRequest(page, maxResults, sortByNameASC());

    Page<PurchaseOrder> purchaseOrders = purchaseOrderRepository.findAll(pageRequest);
    Page<PurchaseOrder> selectedPurchaseOrders = new Page<PurchaseOrder>();  //not possibile to instantiate as Page is abstract
    for(PurchaseOrder next : purchaseOrders){
        if(next.getCountry().getCountryname().toString().equals("Ireland"))
            selectedPurchaseOrders.add(next); //thats lame because is not a LIST so it wont work
    }
    return selectedPurchaseOrders;

}

The above approach wont work because Page is an abstract class, therefore cannot be instantiated. I can loop through but I can't do selectedPurchaseOrders.add(next) because that's not a List. In debug mode I can evaluate the next.getCountry().getCountryname().toString() and I get the country name and that's why I've tried to compare the String object .equals("Ireland")).

Any suggestion please?

Model

@Entity
@Table(name = "PURCHASEORDER",schema = "POTOOL")
public class PurchaseOrder {

@Id
@GeneratedValue
private int id;

//other PurchaseOrder properties

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name = "country_id")
private Country country;

//Getter and Setters

Repository

public interface PurchaseOrderRepository extends
    PagingAndSortingRepository<PurchaseOrder, Integer> {

Page<PurchaseOrder> findByTowerLike(Pageable pageable, String name);
}

Service

@Service
@Transactional
public class PurchaseOrderService {

@Autowired
private PurchaseOrderRepository purchaseOrderRepository;

@Transactional(readOnly = true)
public PurchaseOrderListVO findAll(int page, int maxResults) {
    Page<PurchaseOrder> result = executeQueryFindAll(page, maxResults);

    if(shouldExecuteSameQueryInLastPage(page, result)){
        int lastPage = result.getTotalPages() - 1;
        result = executeQueryFindAll(lastPage, maxResults);
    }

    return buildResult(result);
}

private Page<PurchaseOrder> executeQueryFindAll(int page, int maxResults) {
    final PageRequest pageRequest = new PageRequest(page, maxResults,    sortByNameASC());
    return purchaseOrderRepository.findAll(pageRequest);

}

//rest of the class 

Solution

Why would you want to load all records and filter in memory rather than in the database?

Anyway, as this looks like a Spring Data project you should really read the documentation to see what is possible:

http://docs.spring.io/spring-data/jpa/docs/1.8.0.M1/reference/html/#repositories.query-methods.query-creation

Depending on whether or not you require paging, all you need to do on the repository side is add a method to your PurchaseOrderRepository interface:

Page<PurchaseOrder> findByCountryCountryName(Pageable pageable, String name);

List<PurchaseOrder> findByCountryCountryName(String name);

There is no need to create an implementation as Spring Data will do this and create a query based on the method name.

The repository proxy has two ways to derive a store-specific query from the method name. It can derive the query from the method name directly, or by using a manually defined query.

You simply then need to call your new method from your service layer:

@Service
@Transactional
public class PurchaseOrderService {

   @Autowired
   private PurchaseOrderRepository purchaseOrderRepository;

   ....

   private List<PurchaseOrder> loadByCountry(String country) {
      return purchaseOrderRepository.findByCountryCountryName(country);
   }
}

Comments

[user2342259]

This is not going to work, I've also tried to create a List of PurchaseOrder Objects and loop through but problem is when I return the object. I am returning a List rather then a Page<PurchaseOrder> which I can't cast...

[user2342259]

Thanks I've got it sorted. I knew about the "Query creation from method names" but I didn't know that is also possible to do something like findByCountryCountryName while Country is separate entity which in my case is mapped as a ManytoOne relationship in the PurchaseOrder class