case insensitive sort using spring data

问题:

how can I do case insensitive sorting using Spring-data Pageable?

I have this method in my Repository

public interface ItemRepository extends QueryDslPredicateExecutor<Item>{
    @Query("SELECT o FROM Item o WHERE o.status = ?1")
    Page<Item> findByStatus(Item.Status status, Pageable pageable);
}

I want to be able to call that with:

itemRepository.findByStatus(Status.completed, new PageRequest(0, 10, Direction.ASC, "lower(name)")

Note the lower function in the property string. That doesn't work as Spring-data expects a property there. That will get translated to something like:

SELECT o FROM Item o WHERE o.status = ?1 ORDER BY o.lower(name)

which of course won't work as there is no 'lower' property on the object.

Is there a way to make this work?

回答1:

Sort.Order.ignoreCase() was introduce around 8 months ago into spring-data-jpa, look here:

https://jira.springsource.org/browse/DATAJPA-296

https://github.com/kraymondksc/spring-data-jpa/commit/c3adce0bd36799d3754a5f4c61aee64982abd7e0

Once you have an appropriate version of spring-data-jpa (I think since 1.4 M1, I have 1.4.1) you can write something like this:

Sort.Order order = new Sort.Order(Sort.Direction.ASC, "name").ignoreCase();
itemRepository.findByStatus(Status.completed, new PageRequest(0, 10, new Sort(order));

回答2:

Following is the code I would use to do to sorting:

db.getCollection('employee').aggregate([
  {
    $project: {
         fieldToSortWith: { $toLower: "$lastName" },
         subject: 1
             }
 },
{
    $sort: { fieldToSortWith: 1 },
}
])

This is how you do it in spring data

DBCollection coll = mongoTemplate.getCollection("employee");
       List<DBObject> pipe = new ArrayList<>();
        DBObject prjflds = new BasicDBObject();
        prjflds.put("id", 1);
        prjflds.put("firstName", 2);
        prjflds.put("lastName", new BasicDBObject("$toLower", "$lastName"));
        DBObject project = new BasicDBObject();
        project.put("$project", prjflds);
        pipe.add(project);
        DBObject sort = new BasicDBObject();
        sort.put("$sort", new BasicDBObject("lastName", 1));
        pipe.add(sort);
        BasicDBObject skip = new BasicDBObject("$skip", searchCriteria.getCurrentPage() * searchCriteria.getPageSize());
        BasicDBObject limit = new BasicDBObject("$limit", searchCriteria.getPageSize());
        pipe.add(skip);
        pipe.add(limit);
        BasicDBObject employeeRoleFilter = new BasicDBObject("$match", new BasicDBObject("employeeRoles", new BasicDBObject("$in", searchCriteria.getEmployeeRolesNames())));
        pipe.add(employeeRoleFilter);
        BasicDBObject employeeStatusFilter = new BasicDBObject("$match", new BasicDBObject("active", new BasicDBObject("$eq", Boolean.valueOf(searchCriteria.getEmployeeStatus()))));
        pipe.add(employeeStatusFilter);
        searchCriteria.isEmployeeNameSearchIndicator());
        pipe.add(searchTextFilter);
        coll.aggregate(pipe).results();

After that you will get native BasicDBObjects which you need to transform in your domain. This code is messy if you need any help do inform.