1 Reply Latest reply on Nov 4, 2008 3:14 PM by Muruga boopathy

    Need Help to Query table with ManyToMany Mapping

    Muruga boopathy Newbie
      Dear All,
          I have two tables with manytomany mapping and i need to do the following works ony tables, but i am getting some exception ,

      Here is my table structures

          Table Agent :
                  AssignedChannesl (Assigned Channels is a Set in POJO
      (Here is the Many to Many mapping is done to have more than one channels for a agent> and i have  added the following annotations in my pojo to do the same ..

              @JoinTable(name = "channelMappings", joinColumns = @JoinColumn(name = "AGENT_ID"), inverseJoinColumns = @JoinColumn(name = "CHANNEL_ID"))

                 Table Channles:

      Iam getting the tables generated and whenever i map the channels for every new user or change the channels for the existing user the table channelmappings is getting updated,

      But here is what i want to do with these tables

      I need to know the agent details which are under particular channels  , this i am able to retrieve using normal query in oracle   and here is the query

      select AGENTNAME,AGENTDEPARTMENT from agents ag,channelmappings cm where cm.channel_id=41 and ag.agentid=cm.agent_id

      but the same i need to display as rich data table . i tried using class by inheriting EntityQuery which says channelmappings is not mapped to the entity. and also tried with EntityController .

      Here is my HSQL in EntityQuery :

      //Query tried to retrieve the Channel Details to passed as AssingedChannels  and its working ......
      Channel channelList=(Channel)createQuery("select ch from Channels ch where ch.channelID=:channelID").setParameter("channelID",channelID).getSingleResult();

      //Query tried to get the Agent Details by passing Channelobject to assignedchannels and its not working .
      createQuery("select ag from Agents ag where ag.assignedChannels =:channelList").setParameter("channelList",channelList).getResultList();

      and iam gettng this errors

      WARN  [JDBCExceptionReporter] SQL Error: 17041, SQLState: null
      15:40:27,545 INFO  [STDOUT] 15:40:27,545 ERROR [JDBCExceptionReporter] Missing IN or OUT parameter at index:: 1