Sort a list of objects based on the String pattern of object attribute

Question

I have a list of Device objects. With attributes: userId, activationDatetime, and others.

User ID is constructed as: aaaaXXXX -> aaaa-> string, xxxx->number aaaa String part can start with HCAD, GSAD, SLAD, GLAD, HCZD

Normally the string sort would yield GSAD0000, GLAD0000, HCAD0000, HCZD0000, SLAD0000

I want to customize the sort. Such that the list of objects are ordered by prefix in the following order. HCAD0000 HCAD0001 GSAD0000 GSAD0001 SLAD0000 SLAD0001 GLAD0000 HCZD0001........and so on

How to achieve this in Spring. What is the most efficient way to do this?

SELECT user_id, device_id FROM ( SELECT * FROM db WHERE user_id LIKE 'HCAD%' UNION ALL SELECT * FROM db WHERE user_id LIKE 'GSAD%' UNION ALL SELECT * FROM db WHERE user_id LIKE 'SLAD%' UNION all SELECT * FROM db WHERE user_id LIKE 'GLAD%' UNION ALL SELECT * FROM db WHERE user_id LIKE 'HCZD%' ) ORDER BY CASE WHEN user_id LIKE 'HCAD%' THEN 1 WHEN user_id LIKE 'GSAD%' THEN 2 WHEN user_id LIKE 'SLAD%' THEN 3 WHEN user_id LIKE 'GLAD%' THEN 4 WHEN user_id LIKE 'HCZD%' THEN 5 END, user_id;

the query works fine. but i cannot seem to able to implement it in code

Answer 1

To achieve custom sorting in Spring, you can use Spring Data JPA and make use of the JPA @Query

First define entity:

@Entity
@Table(name = "db")
public class Device {

    @Id
    private Long id;

    @Column(name = "user_id")
    private String userId;

    @Column(name = "device_id")
    private String deviceId;

    // ... other attributes, getters, setters, etc.
}

then create a repository with your query (using order to achieve your custom sorting, i took in consideration that your query is working

public interface DeviceRepository extends JpaRepository<Device, Long> {

    @Query(nativeQuery = true,
           value = "SELECT user_id, device_id FROM (" +
           " SELECT * FROM db WHERE user_id LIKE 'HCAD%' UNION ALL" +
           " SELECT * FROM db WHERE user_id LIKE 'GSAD%' UNION ALL" +
           " SELECT * FROM db WHERE user_id LIKE 'SLAD%' UNION ALL" +
           " SELECT * FROM db WHERE user_id LIKE 'GLAD%' UNION ALL" +
           " SELECT * FROM db WHERE user_id LIKE 'HCZD%'" +
           ") ORDER BY " +
           "CASE " +
           "WHEN user_id LIKE 'HCAD%' THEN 1 " +
           "WHEN user_id LIKE 'GSAD%' THEN 2 " +
           "WHEN user_id LIKE 'SLAD%' THEN 3 " +
           "WHEN user_id LIKE 'GLAD%' THEN 4 " +
           "WHEN user_id LIKE 'HCZD%' THEN 5 " +
           "END, user_id")
    List<Device> findAllOrderedByUserId();
}

Finally, implement the service:

@Service
public class DeviceService {

    @Autowired
    private DeviceRepository deviceRepository;

    public List<Device> getDevicesInOrder() {
        return deviceRepository.findAllOrderedByUserId();
    }
}