SpEL formatted Query Spring Data JPA w/ Spring Sec

Question

I have a Spring Data JPA Entity

@Entity    
public class User  {

    private static final long serialVersionUID = 1L;

    @Id
    private Long id;

Which I want to include a property mutualFriends which is the result of a query against the querying user principle (in Spring Security), using SpEL and the EvaluationContext extension model?:

Following the SpEL examples I came up with something like this:

@Formula("select count(friendship) from Friendship friendship where " +
        "friendship.owner.id = id " +
        "and friendship.friend in " +
        "(SELECT f.friend FROM Friendship f where f.owner.id = " +
        "?#{principle != null ? principal.id : id}) ")
    private Integer mutualFriends;

The same entity (User) is used when authentication user's so I need to have some logic to determine if the principle is available yet or I get an error:

Caused by: org.postgresql.util.PSQLException: No value specified for parameter 2.

?#{principle != null ? principal.id : id}

Is this formatted correctly?

Here is the relevant part of the hibernate query that is executed when I get the exception:

select user0_.id as id1_19_, user0_.created_by as created_2_19_, user0_.created_date as created_3_19_, user0_.last_modified_by as last_mod4_19_, user0_.last_modified_date as last_mod5_19_, user0_.activated as activate6_19_, user0_.activation_key as activati7_19_, user0_.avatar_url as avatar_u8_19_, user0_.banner_url as banner_u9_19_, user0_.description as descrip10_19_, user0_.email as email11_19_, user0_.first_name as first_n12_19_, user0_.lang_key as lang_ke13_19_, user0_.last_name as last_na14_19_, user0_.login as login15_19_, user0_.online as online16_19_, user0_.password_hash as passwor17_19_, user0_.reset_date as reset_d18_19_, user0_.reset_key as reset_k19_19_, user0_.test_data as test_da20_19_, 

select count(user0_.friendship) from Friendship friendship where friendship.friend in ( SELECT f.friend FROM Friendship f where f.owner.id = COALESCE(?#{principle != null ? principal.id : user0_.null},user0_.id)) and friendship.owner.id = user0_.id as formula2_ from user user0_ where user0_.login=?

SpEL/JPA correctly implements replaces id with user0_.id, but something is still missing? Also, it does not appear that select count(user0_.friendship) from Friendship friendship where friendship.friend in (SELECT f.friend FROM Friendship f where f.owner.id = COALESCE(?#{principle != null ? principal.id : user0_.null},user0_.id)) and friendship.owner.id = user0_.id as formula2_ portion of the query has been correctly parsed?

Answer 1

Sorry, completely missed the obvious:

@Formula is a Hibernate thing. It knows nothing about SpEL and wound evaluate such expressions.

What you can do, and what the example you linked to does is: create a method in your repository, add a @Query annotation and use SpEL in there.

Answer 2

I think the problem might be that id is not known in the SpEL context. Replacing the SpEL expression with

COALESCE(?#{principle != null ? principal.id : null},id)

should do the trick.