Hi,
I have a problem related to many-to-many data retrieval.
I m trying to map two tables with many to many relation and they work fine uptill I insert the data into

them .But when I want to retrieve the data from the related table thru the other table ,it throws me a

SQLGrammarException: Could not initialize a collection.
Kindly throw some light........
the code reads as follows..

Pl.hbm.xml--
<class name="pickList.Pl" table="PL" lazy="false" batch-size="20">
<cache usage="read-write"/>
<id name="id" column="ID">
<generator class="assigned"/>
</id>
<property name="name" column="NAME" not-null="true"/>

<set name="pr" table="PR_TO_PL" inverse="true" cascade="all">
<key column="PL_ID"/>
<many-to-many class="Pr" column="PR_ID"/>
</set>
</class>

Pr.hbm.xml
<class name="Pr" table="PR" lazy="true">
<id name="pr_id" column="PR_ID">
<generator class="assigned"/>
</id>
<property name="pr_code" column="PR_CODE" not-null="true"/>
<set name="pl" table="PR_TO_PL" cascade="all">
<key column="PR_ID"/>
<many-to-many class="Pl" column="PL_ID"/>
</set>
</class>

I havent mapped the joining table do I have to do that?
Coz the insertion works just fine without that.
If I have to do that can you tell me the Mapping for the joining table?
Data manipulation code:

Transaction tx=sess.beginTransaction();
Pr prd=new Pr();
prd.setPrd_id(new Integer(3422));
prd.setPrd_code("tyfuy");
Set plt=new HashSet();
Pl pt1=new Pl();
pt1.setId(new Integer(322));
pt1.setName("nmw");

Pl pt2=new Pl();
pt2.setId(new Integer(37));
pt2.setName("name");

plt.add(pt1) ;
plt.add(pt2);
prd.setPlatforms(plt);
sess.saveOrUpdate(prd);
tx.commit();
works great!.

But when I want to retrieve some data from the tables then it gives the above mentioned error:

Pl pt=(Pl)sess.load(Pl.class,new Integer(5));
System.out.println(pt.getDescription()+"***"+pt.getId()); //works fine

BUT::::::::::::


Set prds=(Set)pt.getPr();
while(
prds.iterator().hasNext()){
Pl pl=(Pl)plt.iterator().next();
out.println(pl.getDescription());
out.println(pl.getId());
Pr pr=(Pr)prd.iterator().next();
System.out.println(pr.getBimes()+"========="+pr.getProd_desc()+"======"+pr.getProd_id());

}

throws :



org.hibernate.exception.SQLGrammarException: could not initialize a collection: [Pl.pr#5]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.loadCollection(Loader.java:1925) at org.hibernate.loader.collection.BatchingCollectionInitializer.initialize(BatchingCollectionInitializer.java:52) at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:563) at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60) at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1716) at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344) at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:86) at org.hibernate.collection.PersistentSet.iterator(PersistentSet.java:141) at _pr._untitled1._jspService(untitled1.jsp:82) [SRC:/Product/untitled1.jsp:40] at com.orionserver[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].http.OrionHttpJspPage.service(OrionHttpJspPage.java:56) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:347) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:509) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:413) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65) at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:649) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:322) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.HttpRequestHandler.run(HttpRequestHandler.java:270) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.HttpRequestHandler.run(HttpRequestHandler.java:112) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:186) at java.lang.Thread.run(Thread.java:534)Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:137) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:625) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181) at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:661) at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:951) at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:693) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1057) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2901) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2942) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].sql.FilterPreparedStatement.executeQuery(FilterPreparedStatement.java:270) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1668) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.loadCollection(Loader.java:1918) ... 22 more

Kindly help : If Im going wrong somewhere, Plz explain do I have to map the joining table for retrieval and how.
Thanks.........

Use show-sql=true and see what SQL is actually generating Hibernate for the select.

Im actually getting confused with the many-to-many mappings . I have tried two different ways to map.
First of them is the one using many-to-many tag in the tables being mapped. And then the there is no mapping for the joining table. Each of the mappings contain a set of the other.This is the one which I have posted the code for.
Second is the one using one-to-many mappings with the joining table from both tables. And mapping the joining table which has has a composite primary key, which in turn is the combination of foreign keys from each of the end tables. Then I had the make a separate hbm.xml for the composite-id.

Kindly help me in taking the right turn.
Which way is the right one.
And then the right way to access or modify the data from the objects.
I have tried to search into millions of sites and forums, but Im not able to find out a complete solution for that.
Thanks

alzjc

Im actually getting confused with the many-to-many mappings . I have tried two different ways to map.
First of them is the one using many-to-many tag in the tables being mapped. And then the there is no mapping for the joining table. Each of the mappings contain a set of the other.This is the one which I have posted the code for.
Second is the one using one-to-many mappings with the joining table from both tables. And mapping the joining table which has has a composite primary key, which in turn is the combination of foreign keys from each of the end tables. Then I had the make a separate hbm.xml for the composite-id.

Kindly help me in taking the right turn.
Which way is the right one.
And then the right way to access or modify the data from the objects.
I have tried to search into millions of sites and forums, but Im not able to find out a complete solution for that.
Thanks

Im actually getting confused with the many-to-many mappings . I have tried two different ways to map.
First of them is the one using many-to-many tag in the tables being mapped. And then the there is no mapping for the joining table. Each of the mappings contain a set of the other.This is the one which I have posted the code for.
Second is the one using one-to-many mappings with the joining table from both tables. And mapping the joining table which has has a composite primary key, which in turn is the combination of foreign keys from each of the end tables. Then I had the make a separate hbm.xml for the composite-id.

Kindly help me in taking the right turn.
Which way is the right one.
And then the right way to access or modify the data from the objects.
I have tried to search into millions of sites and forums, but Im not able to find out a complete solution for that.

Thanks

Im actually getting confused with the many-to-many mappings . I have tried two different ways to map.
First of them is the one using many-to-many tag in the tables being mapped. And then the there is no mapping for the joining table. Each of the mappings contain a set of the other.This is the one which I have posted the code for.
Second is the one using one-to-many mappings with the joining table from both tables. And mapping the joining table which has has a composite primary key, which in turn is the combination of foreign keys from each of the end tables. Then I had the make a separate hbm.xml for the composite-id.

Kindly help me in taking the right turn.
Which way is the right one.
And then the right way to access or modify the data from the objects.
I have tried to search into millions of sites and forums, but Im not able to find out a complete solution for that.

Thanks

I suggest you the second choice. This thread will throw some light:

http://forum.hibernate.org/viewtopic.ph ... 97#2331597

Hey buddy,
Thanks for jumping on responses fairly fast. I need them really bad.
Your light was very bright and it did help me in making a decision!!
You are right. This way is more flexible and extensible.
Im able to retrieve data correctly using this methodolgy. But while inserting the values using this approach Im getting Illegal Argument Exception. :
"IllegalArgumentException occurred calling getter of Pr_To_Pl.id
Object is not an instance of declaring class
"

Ill try to make the code more readable and precise
Pl.hbm.xml--

<class name="Pl" table="PL" lazy="false" batch-size="20">
<cache usage="read-write"/>
<id name="id" column="ID">
<generator class="assigned"/>
</id>
<property name="name" column="NAME" not-null="true"/>
<set name="prpl" outer-join="false" inverse="true" cascade="all">
<key column="PL_ID"/>
<one-to-many class="Pr_To_Pl"/>
</set>
</class>


Pr.hbm.xml--

<class name="Pr" table="PR" lazy="true">
<id name="pr_id" column="PR_ID">
<generator class="assigned"/>
</id>
<property name="pr_code" column="PR_CODE" not-null="true"/>
<set name="prpl" outer-join="false" cascade="all">
<key column="PR_ID"/>
<one-to-many class="Pr_To_Pl"/>
</set>
</class>


Pr_To_.Pl.hbm.xml-- Here the composite key is mapped to another class called Pr_Tp_Plid. And key-properties represent foreign keys from each of the tables.

<class name="Pr_To_Pl" table="PR_TO_PL">
<composite-id name="id" class="Pr_To_Plid">
<key-property name="pr_id" column="PR_ID"/>
<key-property name="pl_id" column="PL_ID"/>
</composite-id>
<many-to-one name="pr" column="PR_ID" class="Pr" insert="false" update="false"/>
<many-to-one name="pl" column="PL_ID" class="Pl" insert="false" update="false"/>
</class>


Pr_To_Pl.java

//constructor
Pr pr;
Pl pl;
Pr_To_Plid id=new Pr_To_Plid(); //composite key class
//getters and setters


Pr_To_Plid.java

//constructor
Pr pr;
Pl pl;
//getters and setters
//hashcode and equals are not required from Hib3


Now when I try to insert the data in one of the tables thru the other table it throws :

org.hibernate.PropertyAccessException: IllegalArgumentException occurred calling getter of Pr_To_Pl.id at org.hibernate.property.BasicPropertyAccessor$BasicGetter.get(BasicPropertyAccessor.java:171) at org.hibernate.tuple.AbstractEntityTuplizer.getIdentifier(AbstractEntityTuplizer.java:176) at org.hibernate.persister.entity.AbstractEntityPersister.getIdentifier(AbstractEntityPersister.java:3402) at org.hibernate.persister.entity.AbstractEntityPersister.isTransient(AbstractEntityPersister.java:3118) at org.hibernate.engine.ForeignKeys.isTransient(ForeignKeys.java:181) at org.hibernate.event.def.AbstractSaveEventListener.getEntityState(AbstractSaveEventListener.java:487) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:84) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70) at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507) at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499) at org.hibernate.engine.CascadingAction$1.cascade(CascadingAction.java:147) at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:213) at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:157) at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:108) at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:299) at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:185) at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:160) at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:108) at org.hibernate.engine.Cascade.cascade(Cascade.java:248) at org.hibernate.event.def.AbstractSaveEventListener.cascadeAfterSave(AbstractSaveEventListener.java:437) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:326) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:180) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:121) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:98) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70) at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507) at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499) at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495) at _product._untitled1._jspService(untitled1.jsp:53) [SRC:/Product/untitled1.jsp] at com.orionserver[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].http.OrionHttpJspPage.service(OrionHttpJspPage.java:56) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:347) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:509) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:413) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65) at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:649) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:322) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.HttpRequestHandler.run(HttpRequestHandler.java:270) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].server.http.HttpRequestHandler.run(HttpRequestHandler.java:112) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.0.0)].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:186) at java.lang.Thread.run(Thread.java:534)Caused by: java.lang.IllegalArgumentException: object is not an instance of declaring class at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.hibernate.property.BasicPropertyAccessor$BasicGetter.get(BasicPropertyAccessor.java:145) ... 44 more



::::I have a feeling that some where I have mapped in a wrong way.
Can you also brief me about how to do updations and insertions here.
Thanks.........

While adding to the Set of Pr or Pl, you have to add objects of Pr_To_Pl.class not Pr or Pl as you may have done previously for your earlier mapping.

Post your POJOs definitions and i'll help you...

Thanks again Guys for the braving effort to help get out of the ditch.
You are right Jayesh. I have added to the joining table instead of going in for the end tables.
But even after that I m getting this error.
And My POJOs are:

pojo -Pl.java

public Pl()
{//comstructor
}
public Pl(Integer id)
{
this.id=id; //another constructor
}
private Integer id;
private String name;
Set prpl=new HashSet();
public void setId(Integer id)
{
this.id = id;
}
public Integer getId()
{
return id;
}


public void setName(String name)
{
this.name = name;
}


public String getName()
{
return name;
}

public void setPrpl(Set prpl)
{
this.prpl = prpl;
}

public Set getPrpl()
{
return prpl;
}


Pr.java


public Pr()
{

}
public Pr(Integer pr_id)
{
this.pr_id=pr_id;
}
public Integer pr_id;
public String pr_code;
Set prpl=new HashSet();
public void setPr_id(Integer pr_id)
{
this.pr_id = pr_id;
}
public Integer getPr_id()
{
return pr_id;
}
public void setPr_code(String pr_code)
{
this.pr_code=pr_code;
}
public String getProd_code()
{
return pr_code;
}
public void setPrpl(Set prpl)
{
this.prpl = prpl;
}
public Set getPrpl()
{
return prpl;
}


Pr_To_Pl.java
public Pr_To_Pl()
{
}

private Integer pr_id;
private Integer pl_id;
public Pr pr;
public Pl pl;
Pr_To_Plid id=new Pr_To_Plid();

public void setPr_id(Integer pr_id)
{
this.pr_id = pr_id;
}


public Integer getPr_id()
{
return pr_id;
}


public void setPl_id(Integer pl_id)
{
this.pl_id = pl_id;
}


public Integer getPl_id()
{
return pl_id;
}


public void setPr(Pr pr)
{
this.pr = pr;
}


public Pr getPr()
{
return pr;
}


public void setPl(Pl pl)
{
this.pl = pl;
}


public Pl getPl()
{
return plat;
}


public void setId(Pr_To_Plid id)
{
this.id = id;
}


public Pr_To_Plid getId()
{
return id;
}


And then Pr_To_Plid.java
is similar to
Pr_To_Pl.java only difference being that it implements Serializable

Your config is more complex than it needs to be. I'll show you how i do this stuff with an example

I need a many to many between Actividad and Sector. So i define an extra POJO called ActividadSector ok? wich can have aditional properties like "activa".

Here are the POJOs, mappings and example of use;

POJOs:

public class Actividad {

private Long id;
private String nombre;
private int numContactos;
private boolean descargada;

//getters and setters
}

public class ActividadSector {

private Long id;
private boolean activa;
private Actividad actividad;
private Sector sector;

//getters and setters
}

public class Sector {

private Long id;
private String nombre;
private String palabrasClave;
private Set<ActividadSector> actividadesSector = new HashSet<ActividadSector>();
}

Mappings:

<hibernate-mapping package="xxxx">
<class name="Actividad">
<id name="id">
<generator class="native"/>
</id>
<property name="nombre" length="100" unique-key="nombre"/>
<property name="numContactos"/>
<property name="descargada"/>
</class>
</hibernate-mapping>


<hibernate-mapping package="xxx">
<class name="ActividadSector">
<id name="id">
<generator class="native"/>
</id>
<property name="activa"/>
<many-to-one name="sector" column="id_sector" not-null="true"/>
<many-to-one name="actividad" column="id_actividad" not-null="true" cascade="lock"/>
</class>
</hibernate-mapping>

<hibernate-mapping package="xxx">
<class name="Sector">
<id name="id">
<generator class="native"/>
</id>
<property name="nombre" unique="true"/>
<property name="palabrasClave" />
<set name="actividadesSector" inverse="true" cascade="all,delete-orphan">
<key column="id_sector"/>
<one-to-many class="ActividadSector"/>
</set>
</hibernate-mapping>


Example code:
Sector sector = session.load(...);
for (ActividadSector actividadSector : sector.getActividadesSector()){
System.out.println("New Actividad: "+ actividadSector.getActividad().getNombre())

Hope this helps.
}

Thanks for posting the example.
The only difference in my position and your example is that my joining table has only two columns but both of those columns are infact foreign keys from the end tables. And the combination of these two columns is a composite-key of that joining table.
How do I map that ?