we have a UI sortable table that is backed by the following query, clicking a column does order by ascending or descending on that column. Problem is that one of the columns either renders the the users role name or their human name dependent on a conditional, and we need to be able to sort that column.
currently this the repository definition
Page<UserActivityLog> findByActivityTargetUserId( Long id, Pageable pageable );
I'm trying to write a specification that's looks more or less like this.
private static class UserSpecification implements Specification<UserActivityLog> {
@Override
public Predicate toPredicate(
final Root<UserRelatedEntity> root,
final CriteriaQuery<?> query,
final CriteriaBuilder cb )
cb.selectCase()
.when( root.<Boolean>get( "user.setting.fieldBoolean" ),
root.<RoleType>get( "user.role.roleTypeEnum" ).toString() )
.otherwise( root.<String>get("user.humanNameString") )
... //magic
this is obviously incomplete. I'm not sure how to take this (assuming it's right for the case statement I want to do) and make it return the Predicate. I may not even be able to use the Specification to add the case statement, though I suspect I can. I'm also unsure of once I get it to work, how I'll pass the "field name" to a Sort (I know how to pass a field name, I guess I'm just not sure how to figure out what the field name will be).
It's worth saying I don't really understand the CriteriaBuilder api yet.
Update slightly modified example of a case statement that I grabbed from a an oracle tutorial which demonstrates what I need to do, I don't need the results of the case-ed column in my return object though.
SQL> select job, ename
2 , case
3 when msal <= 2500
4 then 'cheap'
5 else 'expensive'
6 end as class
7 from employees
8 where bdate < date '1964-01-01'
9 order by class;
How can sort my results based on a case statement generated field while still using a Spring Data repository?
This is what I ended up doing. I needed to fetch each attribute at a time (not obvious by tutorials imho) and then at the end in order to return a predicate I have created another specification which I delegate to which has the actual where clause (in this case a simple by id). It's worth saying that the .Index.NAME stuff is just a static string that contains the property name, we haven't decided whether to generate the metamodel yet.
public class FilterByIdForUserSpecification<AL extends AbstractActivityLogWithSiteUser> implements Specification<AL>
{
private final Sort.Direction direction;
private final Specification<AL> specification;
public FilterByIdForUserSpecification(
final Specification<AL> specification,
final Sort.Direction direction )
{
this.specification = specification;
this.direction = direction;
}
@Override
public Predicate toPredicate(
final Root<AL> root,
final CriteriaQuery<?> query,
final CriteriaBuilder cb )
{
Path<Object> siteUser = root.get( AbstractActivityLogWithSiteUser.Index.USER );
Path<Object> ownerOrg = siteUser.get( SiteUser.Index.PRI_ORG ).get( Organization.Index.OWNER );
Path<Object> fullName = siteUser.get( SiteUser.Index.CONTACT ).get( Contact.Index.FULL_NAME );
Path<Object> roleDisp = siteUser.get( SiteUser.Index.ROLE ).get( Role.Index.DISPLAY );
Expression<Object> queryCase = cb.selectCase()
.when( cb.equal( ownerOrg, true ), roleDisp )
.otherwise( fullName );
query.orderBy( direction( cb, queryCase, direction ) );
return specification.toPredicate( root, query, cb );
}
static Order direction( final CriteriaBuilder cb, final Expression<?> e, final Sort.Direction direction )
{
if ( direction == Direction.ASC )
{
return cb.asc( e );
}
return cb.desc( e );
}
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments