5 Replies Latest reply on Feb 22, 2006 11:13 PM by Gus Gu

    EJB-QL for @ManyToMany?

    Peter Westlin Newbie

      I have to entities, Movie and Genre, with a many-to-many-relation.

      Movie
      name
      genres

      Genre
      name
      movies

      This query works for ONE genre:
      select distinct m from Movie m left join m.genres as g where g.name = :genreName

      How should I write EJB-QL for "find movies by genres" with JBoss 4.0.4RC1?

      Regards Peter

        • 1. Re: EJB-QL for @ManyToMany?
          Emmanuel Bernard Master

          select genre from Genre genre left outer join genre.movies

          Set genres = new HashSet( query.getResultList );

          • 2. Re: EJB-QL for @ManyToMany?
            Gus Gu Expert

            Hi Emmanuel,

            I also have a problem with manytomany query. Following are my codes:

            public class Account {
             ...
             @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE},
             fetch = FetchType.LAZY, mappedBy="accounts")
             public List<Group> getGroups() {
             return groups;
             }
            
             public void setGroups(List<Group> groups) {
             this.groups = groups;
             }
             ...
            }
            
            pulic class Group {
             ...
             @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER)
             @JoinTable(table = @Table(name = "account_groups"),
             joinColumns = {@JoinColumn(name = "ACCOUNT_ID")},
             inverseJoinColumns = {@JoinColumn(name = "GROUP_ID")})
             public List<Account> getAccounts() {
             return accounts;
             }
            
             public void setAccounts(List<Account> accounts) {
             this.accounts = accounts;
             }
             ...
            }
            
            
            EntityManager em;
            
            public Account getAccount(String userName) {
             return (Account) em.createQuery("select a from Account a left outer join a.groups where a.userName = :userName")
             .setParameter ("userName", userName)
             .getSingleResult();
            }
            


            when I run it, I always got the exceptions:
            java.lang.RuntimeException: org.jboss.tm.JBossRollbackException: Unable to commit, tx=TransactionImpl:XidImpl[FormatId=257, GlobalId=Laptop/58, BranchQual=, localId=58] status=STATUS_NO_TRANSACTION; - nested throwable: (org.hibernate.HibernateException: Found shared references to a collection: com.gdocs.model.Account.groups)
            


            I could not figure out what is wrong. Thank you very much for any advice in advance. Any helps are appreciated.

            GUS

            • 3. Re: EJB-QL for @ManyToMany?
              Emmanuel Bernard Master

              The showing code cannot create such an exception AFAIK, you probably have some other code somewhere.

              • 4. Re: EJB-QL for @ManyToMany?
                Gus Gu Expert

                Hi Emmanuel, you are great. Yes, the other codes in the Group class made the exceptions. Now I fixed, but I have a new question. That is how to insert new data into the joined table, e.g. add a new group into table groups(group_id,...) and table group_members (group_id, account_id). Thank you very much.

                GUS

                • 5. Re: EJB-QL for @ManyToMany?
                  Gus Gu Expert

                  Hi Emmanul,

                  I got the insert method. Thank you so much.

                  GUS