Spring Data JPA sorting on additional column returned by query

user_6774444 0
Viewed 266

I have a Table "team" with below structure:

Column Type Collation Nullable Default
id uuid not null
team_name character varying(200) not null
admin_group uuid not null
lead_group uuid not null
user_group uuid not null

I am using Spring Data JPA and need to execute query equivalent to:

SELECT *,
       CASE
           WHEN lead_group IN groupList THEN 3
           WHEN admin_group IN groupList THEN 2
           WHEN user_group IN groupList THEN 1
           ELSE 0 END AS role
FROM team;

Here groupList is list of all groups user is part of. I get this info from another service.

What is the best way to do it using Spring Data JPA? I also need to support sorting based on all columns and pagination. Do I add a additional field 'role' in my Team entity or create a separate class altogether to collect the data from the query?

spring-data-jpa case sorting spring pagination
3 Answers

There are several ways to achive it:

  • you should define DB view using this query and define entity/repo in your code for read only.

  • you can use annotation Query and Pagination object in repoitory like:

    @Repository
  public interface GroupTeamRepository extends 
  JpaRepository<GroupTeamEntity, UUID>, 
  JpaSpecificationExecutor<GroupTeamEntity>

      @Query(value="""
      SELECT *,
          CASE
            WHEN lead_group IN groupList THEN 3
            WHEN admin_group IN groupList THEN 2
            WHEN user_group IN groupList THEN 1
            ELSE 0 END AS role
          FROM team
      """, nativeQuery = true)
      Page<GroupTeamEntity> getGroupedTeam(Pageable pageable);

There is a really useful feature: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications

user_22800270

That is a tricky one, since you state that you obtain the groupList from a different service.

For a first option lets challenge this assumption. If you instead could get the groupList through a function call or inner select from the database you can get a nice solution if you also happen to use Hibernate: Use the @Formula annotation to make the computation a field of your entity. Paging and sorting should work just fine with that approach. Alternatively you can join the information to your table using a database view and map your entity to that view. Just make sure to make the role read only.

If you really can't obtain it in the database by normal means, maybe we can do a trick: Put that information in the database before you start querying for your team. Create something, that is wrapped around your service, e.g. a web filter or an Aspect. It would obtain groupList and make it available in the database. You could simply write it into a table, using JPA or JDBC, or you could use something more fancy like for example Oracles temporary tables, which might yield performance benefits. Depending on how you make the list available in the database you can then map it to a read only field of your entity or use the @Formula annotation mentioned above.

If you really can't get the groupList in any other way then through a parameter things get a little more tricky. You'll have to resort to custom method implementations on your repository and work with JPA directly. You'd have to create your query manually including the required sorting and pagination, probably using the Criteria API. Since we are talking a single entity, the work involved for this is limited and depending on your experience with JPA on the one hand and lower level database stuff on the other hand, might even be easier than the other solutions.

Note: translating the SQL case expression to JPQL shouldn't be too difficult.

Jens_Schauder_666860

You can define role as one of field (may be @Transient ?) in your Team entity.

And as you mentioned you are getting groupList from external service so I think use of ExampleMatcher will be good for your requirement.

Below psudo-code does following:

  1. Create Team entity & against each group in groupList it sets group value for all 3 fields.

  2. ExampleMatcher.matchingAny() will check for any of predicate if it matches.

  3. You have set pageIndex & pageSize so that entire dataset won't get loaded into memory.

  4. Returned page can be used to transform returned entity so that we can populate role field based on your requirement.

     Team team=new Team();
 int pageIndex=10;
 int pageSize=10;

 for(Group p : groupList){
 team.setLead_group(p);
 team.setAdmin_group(p);
 team.setUser_group(p);
 ExampleMatcher exampleMatcher=ExampleMatcher.matchingAny().withMatcher("lead_group", exact()).withMatcher("admin_group",
                     exact()).withMatcher("user_group",
                     exact());
 Example<Team> ex = Example.of(team, exampleMatcher);
 Page<Team> page;
 do{
  page= jpaRepository.findAll(ex,PageRequest.of(pageIndex, pageSize, Sort.by(Sort.Direction.ASC, "id")));

  List<Team> ls= page.getContent();
  //apply transformation for each Team element of ls & do any further operation as you want
 }while(page.hasNext());}

You can use any stream operations which can do transformation or simply iterate over returned ls & do transformation:

public Team transform(Team t){
  if(null!=t.getUser_group()){
    t.setRole(1);
  }
  //and so on;
  return t;
}
user_50142210
Related