5 Replies Latest reply on Nov 29, 2002 12:03 PM by jacques lebrun

    SQL to EJB-QL

    Erik Turesson Novice

      Hello!

      I am using JBoss 3.0.4 and 2.0 CMP entity beans.
      I have three entity beans, Folder, AccessGroup and User.

      They look like this, pseudo code.

      Folder{
      long getId();
      Set getPublishers(); // One Folder has 0:N Publisher AccessGroups
      Set getReaders(); // One Folder has 0:N Reader AccessGroups
      Set getFullAccess(); // One Folder has 0:N FullAccess AccessGroups
      }

      AccessGroup{
      long getId();
      Set getUsers(); // One AccessGroup has 0:N Users
      }

      User{
      long getId();
      Set getAccessGroups(); //One user bellongs to 0:N AccessGroups
      }

      I would like to define a finder method that finds out if a User bellongs to one or more of the AccessGroups related to a specific Folder.

      If I should do this in SQL it would look like this:

      SELECT COUNT(ag) AS Expr1
      FROM ACCESSGROUP_USER
      WHERE (userid = 2036) AND (ag IN
      (SELECT fullaccess FROM FOLDER_FULLACCESS
      WHERE folder = 1326)) OR
      ag IN
      (SELECT publishers FROM FOLDER_PUBLISHERS
      WHERE(folder = 1326)) OR
      ag IN
      (SELECT readersFROM FOLDER_READERS
      WHERE (folder = 1326)))

      But how do I do it in EJB-QL?

      /Erik

        • 1. Re: SQL to EJB-QL
          jacques lebrun Newbie

          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.

          • 2. Re: SQL to EJB-QL
            jacques lebrun Newbie

            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.

            • 3. Re: SQL to EJB-QL
              Erik Turesson Novice

              Hello!

              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.

              /Erik

              • 4. Re: SQL to EJB-QL
                Erik Turesson Novice

                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.


                /Erik

                • 5. Re: SQL to EJB-QL
                  jacques lebrun Newbie

                  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
                  1) ejb-ql,
                  or
                  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...