javahibernatepaginationspring-data

Hibernate fetching object linked to current user


I'm currently a little blocked with this and I can't see it clearly.

So I hope one of you have good idea's to help me.

The important code at the moment :

@Entity
@Table(name = "T_NOTA_RECIPIENT")
public class NotaRecipient extends PersistentEntity {

    @Id
    @Column(name = "NOTA_RECIPIENT_SID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "STATUS", insertable = true, updatable = true)
    @Enumerated(EnumType.STRING)
    private Status status = Status.NEW;

    @ManyToOne
    @JoinColumn(name = "NOTA_SID", referencedColumnName = "NOTA_SID", nullable = false)
    private Nota nota;

    @ManyToOne
    @JoinColumn(name = "CREATOR_OFFICE_SID", referencedColumnName = "OFFICE_SID", nullable = false)
    private Office creator;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "notaRecipient")
    private Set<FollowUp> followUps;

    ...
}

Now, actually I don't want to load all the FollowUp who are in the DB but just the one of the current user.
But the problem is that I want to include the FollowUp so I can do database paging query.
We use hibernate, Spring Data and Query DSL with BooleanBuilder to "refine" our search.

I was thinking of using @Formula but this need to be a constant String so I can't include current userId in that.

Second solution could be setting the FollowUp as @Transient and fetch it myself in the DB and set it in mine service.
Problem here is that I can't use it as filter then or ordering by it.

@Formula doesn't have so much documentation, so is it possible to make a @Transient user and use that in the @Formula?

I asked some colleagues but they couldn't help me.

So then it's the time for asking here. I can get the current user in the API, so that's no problem.

Anybody have alternative solutions?


Solution

  • You can define a mapping with expression

    @JoinColumnOrFormula(formula=@JoinFormula(value="(SELECT f.id 
                                                  FROM follow_up_table f 
                                                  WHERE f.nota_id=id
                                                  and f.user_id={USER_ID})", 
     referencedColumnName="...") 
    

    And then add hibernate interceptor (see the example) and change the SQL on fly replacing {USER_ID} with real value in the

    /**
     * Called when sql string is being prepared. 
     * @param sql sql to be prepared
     * @return original or modified sql
     */
    public String onPrepareStatement(String sql);