have u considered making the folder-group relationship an entity?
so, instead of having 3 folder-*** tables, u just have one, with an extra field, say, "permission".
it's much more flexible than ur solution (u can add more pemission types later, e.g. "execute").
i don't think the relationships u described (should i call it "liaisons dangereuses"?) can be implemented with ejb-ql without altering db design.
btw, the ejb-ql of my solution can be:
SELECT Object(u) FROM AccessUser u, IN (u.accessGroup) g, IN (g.folderGroupRel) fgr WHERE fgr.folder.folderId = ?1
and u can implement ur getPublishers()... with ejbSelect methods.
Thanks for your suggestions.
I totaly agree that it is a more flexible solution to have an entitybean defining the permissions.
However I see one problem with your solution.
I relate other type of entity beans to my AccessGroup bean. Therefor I do not whant that the AccessGroup entity bean shall know anything about the Folder bean or the oters that are using it.
Is it still possible to specify an EJB-QL without the accessgroup->folder relation, just using the folder->accessgroup relation.
I did find the answer by myself.
If I write like this I will get all the publishers.
"SELECT OBJECT(f) FROM Folder f, IN (f.publishers) g, IN (g.users) u WHERE f.id = ?1 AND u.id=?2"
Of cource I have to have one question for each accessrigth and I do sometimes have to do several select to find out what I want. I am thinking of implementing your suggestion about the extra entity bean.
But the most important is that it did solve my actual problem. Previously I found out if a user was Publisher using "java" iterating all the accessgroups to see if he was in it. This led to a lot of locking and deadlock situations. Using the finder methods the deadlocks have disapeared and the lockings have dramaticaly been reduced. However the number of selects towards the database has increased.
What is the best way of doing this, except for the lockings? Is it to have a finder method doing a select in the database or is it to iterate the entity beans relations using the local objects?
I am not really interrested in wich object that is found, just if some object is found.
yes, now i totally got what u meant: u wanna know whether a specific folder is accessible to a given user (and maybe with a given "access right").
in my opinion u can do this with either
2) stored procedures.
and the alternative of iterating the relationships is plausible i'm afraid: there are still db-accesses when u iterate the groups.
an ejbselect method wrapped by an ejbhome method can be a lovely idea...