We are trying to implement Many to Many relationship using Hibernate and Sybase. We have a product database with Category, Item and Category_Item tables. There is a many to many relationship between Category and Item and it is stored in Category_Item table.

We are trying to get items for a given category and it returns an empty set. This only happens when we use Sybase DB and sybase driver. But when we do this using HSQLDB ( in memory DB) we get items for a given category.

Has anyone faced this problem with Sybase or with hibernate in general. We also tried creating an entity class for the relationship table Category_Item and it also failed.




Hibernate version:
3.1

Mapping documents:
Category
<hibernate-mapping package="com.entities" >

<class name="Category" table="category" schema="dbo" catalog="product">
<id name="id" type="string">
<column name="category_id" sql-type="char(10)" length="10"/>
<generator class="assigned" />
</id>
<property name="description" type="string">
<column name="description" length="75" not-null="true" />
</property>
<set name="items"
table="category_item"
lazy="false">
<key>
<column name="category_id" sql-type="char(10)" not-null="true" />
</key>
<many-to-many class="Item" column="item_id"/>
</set>

</class>
</hibernate-mapping>

Item
<hibernate-mapping package="com.entities">
<class name="Item" table="item" schema="dbo" catalog="product">
<id name="id" type="string">
<column name="item_id" length="40" />
<generator class="assigned" />
</id>
<property name="description" type="string">
<column name="description" length="75" not-null="true" />
</property>
</class>
</hibernate-mapping>


Pojos
Category
package com.entities

import java.util.Set;

public class Category extends BusinessObject {

public Category() {}

public String id;
public String description;
public Set items;

public void _finalize() {
}


public Set getItems() {
return items;
}


public void setItems(Set items) {
this.items= items;
}
..............


}

Item
package com.entities

public class Item extends BusinessObject {


private String id;
private String description;
public Entitlement() {}

public void _finalize() {}

public String getDescription() {
return description;
}

public void setDescription(String description) {
this.description = description;
}

public String getId() {
return id;
}

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

}


Test Class
CategoryTest
package com.entities

import java.util.Collection;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.entities.dao.ObjectManager;

public class CategoryTest extends HibernateBase {
private static Log log = LogFactory.getLog(CategoryTest.class);
private Category category = null;


public void setUp() {
mgr = ObjectManager.Factory.getManager(PRODUCT_CONTEXT);
}

publicCategoryTest(String testName) {
super(testName);
}


public void testCategoryItem() {

category = (Category) mgr.getObject(Category.class.getName(), "Servers");
assertNotNull(category);
log.info(type);

Collection items = (Collection) type.getItems();
log.info(items);
assertFalse("items.size() == 0", items.size() == 0);

}

}

Name and version of the database you are using:
Sybase
Driver Class com.sybase.jdbc2.jdbc.SybDriver
hibernate.dialect=org.hibernate.dialect.Sybase11Dialect

Test Result
The assertFalse statement fails for Sybase whereas it works for HSQLDB.

Have you tried running the resulting sql through the Sybase tools to see what you get back?

Yes we did and we get results back. Infact we went into debug mode and went well into hibernate code and found that the resultset has results but for some reason the PersistenceBag/Set doesnot get set with the data from the result set.