Hibernate: ArryaList Collection associations are not working

[Saravanan Vijayappan]

Hi,

I have 2 POJOs: Menugroup, Catgroup. Menugroup has one-to-many replationship with Catgroup. Evenything works fine when I declared Catgroups as java.util.HashSet. I started getting the problem when I changed the collections type to java.util.List.

Getting the below exception:

 
SEVERE: Exception sending context initialized event to listener instance of class com.cs.deals.web.MenugroupCacheListener
org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.persistence.Menugroup.catgroups#6]
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:2001)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:36)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:565)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1716)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:454)
at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:797)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:241)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.cs.deals.web.MenugroupCacheImpl.loadCache(MenugroupCacheImpl.java:34)
at com.cs.deals.web.MenugroupCacheListener.contextInitialized(MenugroupCacheListener.java:18)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3843)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4350)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:578)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'catgroups0_.idx' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1994)
... 30 more

Menugroup.java POJO is below:-

/**
 * Menugroup generated by hbm2java
 */
public class Menugroup implements java.io.Serializable {
 
/**
 * 
 */
private static final long serialVersionUID = -8028230107775063517L;
private int menugroupId;
private String name;
private String description;
private boolean published;
private Date lastupdate;
private List<Catgroup> catgroups = new ArrayList<Catgroup>();
//private Set<Catgroup> catgroups = new LinkedHashSet<Catgroup>();

public Menugroup() {
}
 
public int getMenugroupId() {
return this.menugroupId;
}
 
public void setMenugroupId(int menugroupId) {
this.menugroupId = menugroupId;
}
 
public String getName() {
return this.name;
}
 
public void setName(String name) {
this.name = name;
}
 
public String getDescription() {
return this.description;
}
 
public void setDescription(String description) {
this.description = description;
}
 
public boolean isPublished() {
return this.published;
}
 
public void setPublished(boolean published) {
this.published = published;
}
 
public Date getLastupdate() {
return this.lastupdate;
}
 
public void setLastupdate(Date lastupdate) {
this.lastupdate = lastupdate;
}

public void setCatgroups (List<Catgroup> catgroups)
{
this.catgroups = catgroups;
}

public List<Catgroup> getCatgroups ()
{
return this.catgroups;
}

/*public void setCatgroups (Set<Catgroup> catgroups)
{
this.catgroups = catgroups;
}

public Set<Catgroup> getCatgroups ()
{
return this.catgroups;
}*/
}

Catgroup.java POJO is below;-

public class Catgroup implements java.io.Serializable {
 
/**
 * 
 */
private static final long serialVersionUID = 3924209159210565205L;
private int catgroupId;
private Menugroup menugroup;
private String name;
private String description;
private String imgurl;
private boolean published;
private String sugesstion;
private Date lastupdate;
 
public Catgroup() {
}
 

public int getCatgroupId() {
return this.catgroupId;
}
 
public void setCatgroupId(int catgroupId) {
this.catgroupId = catgroupId;
}
 
public Menugroup getMenugroup() {
return this.menugroup;
}
 
public void setMenugroup(Menugroup menugroup) {
this.menugroup = menugroup;
}
 
public String getName() {
return this.name;
}
 
public void setName(String name) {
this.name = name;
}
 
public String getDescription() {
return this.description;
}
 
public void setDescription(String description) {
this.description = description;
}
 
public String getImgurl() {
return this.imgurl;
}
 
public void setImgurl(String imgurl) {
this.imgurl = imgurl;
}
 
public boolean isPublished() {
return this.published;
}
 
public void setPublished(boolean published) {
this.published = published;
}
 
public String getSugesstion() {
return this.sugesstion;
}
 
public void setSugesstion(String sugesstion) {
this.sugesstion = sugesstion;
}
 
public Date getLastupdate() {
return this.lastupdate;
}
 
public void setLastupdate(Date lastupdate) {
this.lastupdate = lastupdate;
}
 

}

Menugroup.hbm.xml is below:-

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jun 21, 2008 9:49:19 PM by Hibernate Tools 3.2.0.CR1 -->
<hibernate-mapping package="com.persistence">
 <class name="com.persistence.Menugroup" table="MENUGROUP">
  <id name="menugroupId" type="int">
   <column name="MENUGROUP_ID"/>
   <generator class="identity"/>
  </id>
  <property generated="never" lazy="false" name="name" type="string">
   <column length="124" name="NAME" not-null="true">
    <comment>MenuGroup name</comment>
   </column>
  </property>
  <property generated="never" lazy="false" name="description" type="string">
   <column length="65535" name="DESCRIPTION">
    <comment>Description about MenuGroup</comment>
   </column>
  </property>
  <property generated="never" lazy="false" name="published" type="boolean">
   <column name="PUBLISHED" not-null="true">
    <comment>Indicates whether this Menugroup should be displayed on Web: 0= Menugroup should not be displayed, 1= Menugroup should be displayed.</comment>
   </column>
  </property>
  <property generated="never" lazy="false" name="lastupdate" type="timestamp">
   <column length="0" name="LASTUPDATE">
    <comment>Lastupdated timestamp.</comment>
   </column>
  </property>
  <list lazy="false" name="catgroups" table="CATGROUP">
   <key column="MENUGROUP_ID"/>
   <index/>
   <one-to-many class="Catgroup"/>
  </list> 
  <!-- <set inverse="true" batch-size="10" lazy="false"  name="catgroups">
   <key>
    <column name="MENUGROUP_ID">
     <comment>Menu Group relationship</comment>
    </column>
   </key>
   <one-to-many class="Catgroup"/>
  </set>-->
 </class>
</hibernate-mapping>

Catgroup.hbm.xml is below:-

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jun 21, 2008 9:49:19 PM by Hibernate Tools 3.2.0.CR1 -->
<hibernate-mapping package="com.persistence">
    <class name="com.persistence.Catgroup" table="CATGROUP">
        <id name="catgroupId" type="int">
            <column name="CATGROUP_ID" />
            <generator class="identity" />
        </id>
       <many-to-one  name="menugroup" class="com.persistence.Menugroup" fetch="select">
            <column name="MENUGROUP_ID">
                <comment>Menu Group relationship</comment>
            </column>
        </many-to-one>
        <property name="name" type="string">
            <column name="NAME" length="124" not-null="true">
                <comment>Name of the catgroup</comment>
            </column>
        </property>
        <property name="description" type="string">
            <column name="DESCRIPTION" length="65535">
                <comment>Description of the catgroup</comment>
            </column>
        </property>
        <property name="imgurl" type="string">
            <column name="IMGURL" length="65535">
                <comment>Catgroup Image URL</comment>
            </column>
        </property>
        <property name="published" type="boolean">
            <column name="PUBLISHED" not-null="true">
                <comment>Indicates whether this Catgroup should be displayed : 0= Catgroup should not be displayed, 1= Catgroup should be displayed.</comment>
            </column>
        </property>
        <property name="sugesstion" type="string">
            <column name="SUGESSTION" length="65535">
                <comment>Suggestion to the user if no products under this category</comment>
            </column>
        </property>
        <property name="lastupdate" type="timestamp">
            <column name="LASTUPDATE" length="0" not-null="true">
                <comment>Lastupdated timestamp of the entity</comment>
            </column>
        </property>
       
    </class>
</hibernate-mapping>

This is my jave code snippet tries pull the data from DB:

List<?> menuGroups = dao.getSession().createCriteria(Menugroup.class).list;


Hibernate generates the below SQLs in the console:

Hibernate: 
    select
        this_.MENUGROUP_ID as MENUGROUP1_0_0_,
        this_.NAME as NAME0_0_,
        this_.DESCRIPTION as DESCRIPT3_0_0_,
        this_.PUBLISHED as PUBLISHED0_0_,
        this_.LASTUPDATE as LASTUPDATE0_0_ 
    from
        cyddev.MENUGROUP this_
Hibernate: 
    select
        catgroups0_.MENUGROUP_ID as MENUGROUP2_1_,
        catgroups0_.CATGROUP_ID as CATGROUP1_1_,
        catgroups0_.idx as idx1_,
        catgroups0_.CATGROUP_ID as CATGROUP1_1_0_,
        catgroups0_.MENUGROUP_ID as MENUGROUP2_1_0_,
        catgroups0_.NAME as NAME1_0_,
        catgroups0_.DESCRIPTION as DESCRIPT4_1_0_,
        catgroups0_.IMGURL as IMGURL1_0_,
        catgroups0_.PUBLISHED as PUBLISHED1_0_,
        catgroups0_.SUGESSTION as SUGESSTION1_0_,
        catgroups0_.LASTUPDATE as LASTUPDATE1_0_ 
    from
        cyddev.CATGROUP catgroups0_ 
    where
        catgroups0_.MENUGROUP_ID=?

I don't know, from where hibernate adds this column 'catgroups0_.idx' in the SQL since I never defined this column anywhere. The same thing works fine for Set Collections, but it is not working for java.util.List, this is really strange, I don't know whether I am really missing some thing or this a BUG in hibernate ?. Could any one please help me.

Thanks
Sarav

[gopal venu]

I think it problem with your mapping xml file.

I THINK list element requires <list-index column="yourcolumnName"/> for indexing.Just add this and see I hope it will be working.

[Saravanan Vijayappan]

It worked, Thank you buddy !!!

[Cameron Wallace McKenzie]

Indeed, SQLGrammarException problems most often boil down to some type of mapping problem.

-Cameron McKenzie

[Konda Golamaru]

Hi Saravanan,

Could you please post the code you have used/developed to persist the said objects using one to many relation.

Thanks