hi guys
I am using hibernate with SQL server 2005 database.
I have three tables. The tables are

Project
PROJECT_ID (PK)
Name


Users
USER_ID (PK)
Fname
Lname


Project_Users
PROJECT_ID (PK)
USER_ID (PK)
Role

The requirments are straight forward:

1. one project can have multiple users. One user can work on multiple projects.
2. When user is searched by UserId, all the projects for that user should be retrieved.
3. When Project is searched by projectId, user information need not be fetched. Only projectId and project name are shown.

I am trying to use 'List' to represent the above relationships but want to make sure i am doing right. Since its a bidirectional relationship, i have inverse="true"

Project.hbm.xml
<class name="hibernate.entity.Project" table="PROJECT" catalog="eProject">
<id name="projectId" type="int">
<column name="PROJECT_ID"/>
<generator class="assigned" />
</id>
<property name="name" type="string">
<column name="NAME" length="50" not-null="true" unique="true" />
</property>

<list name="projectUserses" inverse="true">
<key column="PROJECT_ID" not-null="true"/>
<list-index column="USER_ID"/>
<one-to-many class="hibernate.entity.ProjectUsers"/>
</list>
</class>


User.hbm.xml
<class name="hibernate.entity.Users" table="USERS" catalog="eProject">
<id name="userId" type="string">
<column name="USER_ID" length="20" />
<generator class="assigned" />
</id>
<property name="firstName" type="string">
<column name="FIRST_NAME" length="50" />
</property>
<property name="lastName" type="string">
<column name="LAST_NAME" length="50" />
</property>

<list name="projectUserses" inverse="true">
<key column="USER_ID" not-null="true"/>
<list-index column="PROJECT_ID"/>
<one-to-many class="hibernate.entity.ProjectUsers"/>
</list>
</class>

projectUser.hbm.xml
<class name="hibernate.entity.ProjectUsers" table="PROJECT_USERS" catalog="eProject">
<composite-id name="id" class="hibernate.entity.ProjectUsersId">
<key-property name="projectId" type="int">
<column name="PROJECT_ID" />
</key-property>
<key-property name="userId" type="string">
<column name="USER_ID" length="20" />
</key-property>
</composite-id>
<many-to-one name="users" class="hibernate.entity.Users" lazy="false" update="false" insert="false" fetch="select">
<column name="USER_ID" length="20" not-null="true" />
</many-to-one>
<many-to-one name="project" class="hibernate.entity.Project" update="false" insert="false" fetch="select">
<column name="PROJECT_ID" not-null="true" />
</many-to-one>
<property name="userRole" type="string">
<column name="USER_ROLE" length="50" />
</property>
</class>

Am i doing it correctly? Any help will be greatly appreciated.

thanks.

Hi jaydeep.roy,

I'm afraid no.

• The class "ProjectUsers" is redundant. While having a relationship table "Project_Users" at the database level, at the java level it's simply the class "Project" containing a collection of users and the class "User" containing a collection of Projects.
• The inverse attribute is only allowed on one side of the association The other side is the owning side. Owning side means that the this side must be saved to make changes persistent.
• You cannot use indexed collections (List or Map) for bidirectional many-to-many associations. You will have to use sets or bags.
• If you have specified the package of your entities you can omit the package part in every class-Attribute.
• You should name classes and mapping files consistently i.e. either name the class User or the mapping file Users. I'd recommend changing the class name to User.

Presuming that Project is the owning side of the association the mapping for the colletcions would look like this
Project.hbm.xml

User.hbm.xml


Cheers, ngomo

thanks for sharing knowledge.
But i really want to use List and not a Set. If i understand correctly, is Bag something like a List (without the index part) ?

Also, right now ProjectUsers class contains field 'role' which corresponds to ROLE column in PROJECT_USER table.
So where would 'role' field be placed if ProjectUsers class is removed?

thx.

All right! I should have looked at the additional properties of your classes. My fault. Looks like the ProjectUsers is not as redundant as I thought. Just to make sure I understand your requirements:

• There are projects, which have 0..* users.
• There are users, which work on 0..* projects.
• Users working on a particular project have 1..* (at least one) Roles in that particular project.

Did I get it right?

Cheers ngomo.

There are projects, which have 0..* users.
A Project must have atleast one OR more users.

There are users, which work on 0..* projects.
similarly a user MUST have one OR more projects associated to him/her.

Users working on a particular project have 1..* (at least one) Roles in that particular project.
yes, this is correct. each user has atleast one (OR more) role for a project.

example (Role_Developer ---who is a developer for the project, Role_tester -- who is a tester for the project).
A person can have two roles developer and tester.

thanks.

can anyone help me here, i would really appreciate it.
I want to use hibernate List instead of a Set for my scenario and i guess i cannot use many-to-many because
i have Role field in ProjectUsers class(and PROJECT_USERS table)

thanks in advance.

medanum6298

Post your messages in the right place. Your message doesnt make sense here.

Yes a bag is List..

Try understanding below code.maybe it will help you

<class name="NAME_OF_PARENT_CLASS" table="PARENT_TABLE_NAME">
<id name="PARENT_ID">
<generator class="sequence">
<param name="sequence">NAME_OF_SEQUENCE_OF_PARENT_TABLE</param>
</generator>
</id>

<bag name="NAME_OF_LIST_USED_IN_PARENT_CLASS" cascade="all" >
<key column="NAME_OF_JOIN_ID"/> // Note: ID on which you are joining two tables
<one-to-many class="NAME_OF_CHILD_CLASS"/>
</bag>
<property name="xyz" column="xyz" type="java.lang.String"/>
All properties of parent tables will go here

</class>

<class name="NAME_OF_CHILD_CLASS" table="CHILD_TABLE_NAME">
<id name="CHILD_ID">
<generator class="sequence">
<param name="sequence">NAME_OF_SEQUENCE_OF_CHILD_TABLE</param>
</generator>

</id>

<property name="xyz" column="xyz" type="java.lang.String"/>
All properties of child tables will go here
</class>

yeah i had it running using bag, but i am trying to understand why list does not work.
I set USER_ID as index in the PROJECT_USERS table and mentioned that as indexed column below code

<list name="projectUserses" table="PROJECT_USERS" inverse="true" lazy="false" fetch="select">
<key column="PROJECT_ID" not-null="true" />
<list-index column="USER_ID" />
<one-to-many class="hibernate.entity.ProjectUsers" />
</list>

but the above list is throwing error when i am searching by projectId 227565 (corresponding to hibernate.entity.Project)
and getting this error on the list definition

[4/21/11 15:32:13:463 EDT] 00000017 SystemErr R org.hibernate.exception.DataException: could not initialize a collection: [hibernate.entity.Project.projectUserses#227565]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:100)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
...
....
Caused by: java.sql.SQLException: The value supplied cannot be converted to INTEGER. at net.sourceforge.jtds.jdbc.Support.convert(Support.java:653)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getInt(JtdsResultSet.java:641)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getInt(JtdsResultSet.java:968)



thanks for helping.

please paste your query formation code.It will be help to understand the problem.