Need Help to Query table with ManyToMany Mapping
rmuruga Oct 24, 2008 12:14 PMDear 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 :
AgentID
AgentName
AgentDepartment
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 ..
@ManyToMany
@JoinTable(name = "channelMappings", joinColumns = @JoinColumn(name = "AGENT_ID"), inverseJoinColumns = @JoinColumn(name = "CHANNEL_ID"))
Table Channles:
ChannelID
ChannelName
ChannelCountry
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
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 :
AgentID
AgentName
AgentDepartment
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 ..
@ManyToMany
@JoinTable(name = "channelMappings", joinColumns = @JoinColumn(name = "AGENT_ID"), inverseJoinColumns = @JoinColumn(name = "CHANNEL_ID"))
Table Channles:
ChannelID
ChannelName
ChannelCountry
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