10

I currently have a system in place which can filter and sort records in the database and return them as a Paged object. One of the lines is like this:

final PageRequest request = new PageRequest(this.pagingSettings.getPageNumber(),
            this.pagingSettings.getPageSize(), sortDirection, sortedBy);

This works correctly, but now I'm having the following situation. I'm trying to sort on a house number, which is a varchar in my Postgres database. For example, we have 1, 12, 111, 1004 but also 1A or 36-BASEMENT. When sorting on these (character) values, these would sort on: 1, 1004, 111, 12, 1A, ...

So, sortedBy is now a String, which in this case is houseNumber. I found out that using the ORDER BY argument ... ORDER BY NULLIF(regexp_replace(container_number, E'\\D', '', 'g'), '')::int"; in Postgres, the sorting would be exactly like I wanted: 1, 1A, 12, 111, ...

However, just changing the sortedBy String to sortedBy = "NULLIF(regexp_replace(container_number, E'\\D', '', 'g'), '')::int"; does not seem to work.

Does anyone have a suggestion on how to sort the character values in a PageRequest numerical, without changing the database?

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
Joetjah
  • 6,292
  • 8
  • 55
  • 90

3 Answers3

3

So basically you need to do two things:

  1. Implement custom comparator,
  2. Annotate the entity class.

Ad.1.

public class HouseComparator implements Comparator<House> {
    @Override
    public int compare(House h1, House h2) {

        String s1 = h1.getHouseNumber().split("[^0-9]")[0];
        String s2 = h2.getHouseNumber().split("[^0-9]")[0];

        return s1.compareTo(s2);
    }
}

You need to add some better handling of your cases. The above comparator says, that h1 is less than h2 when it begins with a smaller number and vise versa. For this comparator 12A is equal 12B but it's up to you.

Ad.2.

@SortComparator(HouseComparator.class)
List<House> findByHouseNumber(Pageable pageable);
Community
  • 1
  • 1
xenteros
  • 15,586
  • 12
  • 56
  • 91
  • 1
    I'm not sure if this is what I seek. The PageRequest makes it possible to have a paged response of the total list of objects. This could mean I have only 10 of the 200 objects available. I'm not sure on how to fit your solution in here. To me, it seems like post-sorting here. I'm going to see how to fit iin this solution and report back what it does. – Joetjah Nov 09 '16 at 14:01
  • I'm using `Page houses = this.houseRepository.findAll(request);` Where should I use this Comparator then? – Joetjah Nov 09 '16 at 14:04
  • What db do you use? – xenteros Nov 09 '16 at 14:14
  • I use a Postgres database. – Joetjah Nov 09 '16 at 14:17
3

Assume that you have entity with a String filed and you want to sort it like Long with jpa Pageable.

So you need to do following things:(Remember this only works with Oracle Database)

  1. Add a new filed in your entity with Long type
  2. for the new filed use @Formula in getter method and evoke to_number()
@Entity
public class testEntity{
         private String oldField;  //Getter and Setter

         private Long newField; //Setter

         @Formula(value = "to_number(oldField)")
         public Long getNewField() {
                return newField;
         } 
 }
  1. in your service find sorted filed and change it to the newfiled
 if (Objects.nonNull(pagingRequest.getSort()) && pagingRequest.getSort().getFieldName().equals("oldField")) {
            pagingRequest.getSort().setFieldName("newField");
 }
Omid Rostami
  • 524
  • 6
  • 10
  • 1
    This solution is the best in my opinion, but one small update from my side: a) It will look better, when you will put `@Formula` around field declaration (it is not needed to put it on getter b) when you will get error like 'Formula mappings are currently not supported', you may need to add `@NotAudited` annotation (more here: https://stackoverflow.com/questions/44327871/formula-mappings-are-currently-not-supported-hibernate-orm-envers c) my case: ` @Column(name="idx") String idx; @Formula("COALESCE(TO_NUMBER(REGEXP_SUBSTR(idx, '^\\d+')), 0)") @NotAudited Long idxNumber; ` – Krzysiek Feb 14 '22 at 02:46
2

I think you could try the Spring Data JpaSort class which allows function calls.

As stated in the documentation you will have something like :

@Query("select u from User u where u.lastname like ?1%")
  List<User> findByAndSort(String lastname, Sort sort);

repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)"));

You could also use it with a Pageable object.

Stephane L
  • 2,879
  • 1
  • 34
  • 44