spring / hibernate - filter by current user id

Question

hi,

I have a table CompanyList in my Oracle database :

CMP_ID INTEGER -- ID of company
CMP_NAME VARCHAR2 -- name of company
USR_ID INTEGER -- Foreign key to the USERS table

i have my Spring 3 MVC app all configured using annotations, my POJOs as well , my DAO objects (CompanyDao) using hibernate to retrieve for exemple a list of companies.

CompanyDao :

@Transactional
    public Set<Company> findAllCompanys() throws DataAccessException {

        return findAllCompanies(-1, -1);
    }

    @SuppressWarnings("unchecked")
    @Transactional
    public Set<Company> findAllCompanies(int startResult, int maxRows) throws DataAccessException {
        Query query = createNamedQuery("findAllCompanies", startResult, maxRows);
        return new LinkedHashSet<Company>(query.getResultList());
    }

And my company domain :

@Entity
@NamedQueries( {
        @NamedQuery(name = "findAllCompanies", query = "select myCompany from Company myCompany")})
...
public class Company implements Serializable {
...

Then I setup spring security, so all my pages require identification.

What is the best way to filter the rows returned by CompanyDao, using the USER ID of the current logged in user session ?

Answer 1

Short answer: SecurityContextHolder.

Slightly longer answer: If the User entity that is the parent side of the user_id foreign key relationship also implements the UserDetails interface, then the User entity can be used directly in Spring's security context.

You can call the SecurityContextHolder.getContext() method from either your DAO layer or a layer above it...doesn't really matter because the instance returned will be scoped to the thread local of the request.

You can then get the UserDetails from the context instance, cast it to your User entity and pass it as a named parameter to a DAO call.

As a quick followup, I realize that your original question implied that you'd run the named query as it currently exists and then filter out the non-matching companies. You can still use that approach by comparing the users associated with your company with the user from the security context, but in general I wouldn't recommend that approach; ask the database for exactly what you need.

Answer 2

Actually, Spring Security 3 can filter returned collections with @PostFilter annotation, see 15.3.1 @Pre and @Post Annotations.

But I think in your case, when Company has many-to-one relation to User, Jeff's suggestion is more appropriate - you should add a condition to the query, i.e. replace findAllCompanies() by findAllCompaniesForUser(), and pass userId extracted from SecurityContextHolder at presentation or service layer as an argument.