[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

Using Hibernate List

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:

one project can have multiple users. One user can work on multiple projects.

When user is searched by UserId, all the projects for that user should be retrieved.
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"

My 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>

Open in new window


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>

Open in new window


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>

Open in new window


Am i doing it correctly? Any help will be greatly appreciated.
0
royjayd
Asked:
royjayd
  • 4
  • 2
2 Solutions
 
Sathish David Kumar NCommented:
>>>>one project can have multiple users. One user can work on multiple projects
You can use many to many relationship...

>>>>>When user is searched by UserId, all the projects for that user should be retrieved.

You can put joines and retives the what filed you requried ...

>>>>>>When Project is searched by projectId, user information need not be fetched. Only projectId and project name are shown.

you can get the deatils only  in the Project table not other ...

>>>>>>Since its a bidirectional relationship, i have inverse="true"
Yes your right !!




0
 
josephtsangCommented:
>>>>one project can have multiple users. One user can work on multiple projects
The many-to-many relationship here unfortunately is NOT applicable. It is because there is an additional field "Role" in your M-to-M table Project_Users. This additional field makes Hibernate recognizing the table as a new entity, just like User and Project. So what you are doing now should be correct.

>>>>>When user is searched by UserId, all the projects for that user should be retrieved.
You may consider using "Set" instead of "List", because the uniqueness of the elements in a set matches your definition of the User-Project relationship better. You may also define the relationship in User as fetch="eager" such that whenever a User is loaded, the associated ProjectUsers are also fetched.

>>>>>>When Project is searched by projectId, user information need not be fetched. Only projectId and project name are shown.
What you have defined is correct. So when the Project is loaded, ProjectUsers are not fetched but only the Project attributes/fields.

 
0
 
royjaydAuthor Commented:
>>>The many-to-many relationship here unfortunately is NOT applicable. It is because there is an additional field "Role" in your M-to-M table Project_Users

thanks for this sugession dude. A bunch of guys said use Many to many but i think it cannot be done  because there is a Role field.

>>>You may consider using "Set" instead of "List",
I dont want to. I am using a flex UI , so i want to use List. Flex does not have much support for Set.
I can always modify the query to get distinct* projects.

but the problem i am facing right now is in this section
<list name="projectUserses" inverse="true" lazy=false>        
            <key column="PROJECT_ID" not-null="true"/>
          <list-index column="USER_ID"/>          
       <one-to-many class="hibernate.entity.ProjectUsers"/>
 </list>

It says list cannot be initialized

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
josephtsangCommented:
Would you like to post the error message you got and tell at what situation you got it hit?
0
 
royjaydAuthor Commented:
i am searching by id 227565 which is the projectId (corresponding to hibernate.entity.Project)
but getting this error::

[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)

0
 
royjaydAuthor Commented:
just to clarify
 Project contains projectUserses  List  . so this is my mapping:
<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>

Open in new window

My question is
 <key column="PROJECT_ID" not-null="true"/>  -- should this be key of PROJECT table or
PROJECT_USERS table ?

<list-index column="USER_ID" />      -- I have indexed USER_ID column in PROJECT_USERS table.Is that correct ?

Also i just noticed: The detailed error is:

[4/21/11 18:26:15:752 EDT] 00000016 SystemOut     O Hibernate:
    /* load one-to-many hibernate.entity.Project.projectUserses */ select
        projectuse0_.PROJECT_ID as PROJECT1_1_,
        projectuse0_.USER_ID as USER2_1_,      
        projectuse0_.PROJECT_ID as PROJECT1_8_0_,
        projectuse0_.USER_ID as USER2_8_0_,
        projectuse0_.USER_ROLE as USER3_8_0_
    from
        etrade.PROJECT_USERS projectuse0_
    where
        projectuse0_.PROJECT_ID=?
[4/21/11 18:26:15:877 EDT] 00000016 SystemOut     O 18:26:15,877 [WebContainer : 0] INFO  IntegerType - could not read column value from result set: USER2_1_; The value supplied cannot be converted to INTEGER.
[4/21/11 18:26:15:892 EDT] 00000016 SystemOut     O 18:26:15,892 [WebContainer : 0] WARN  JDBCExceptionReporter - SQL Error: 0, SQLState: 22000
[4/21/11 18:26:15:892 EDT] 00000016 SystemOut     O 18:26:15,892 [WebContainer : 0] ERROR JDBCExceptionReporter - The value supplied cannot be converted to INTEGER.
[4/21/11 18:26:15:892 EDT] 00000016 SystemOut     O 18:26:15,892 [WebContainer : 0] ERROR ProjectDao - could not initialize a collection: [hibernate.entity.Project.projectUserses#227565]
[4/21/11 18:26:16:017 EDT] 00000016 SystemOut     O 18:26:16,017 [WebContainer : 0] WARN  LoadContexts - fail-safe cleanup (collections) : org.hibernate.engine.loading.CollectionLoadContext@11431143<rs=org.apache.commons.dbcp.DelegatingResultSet@11051105>
[4/21/11 18:26:16:017 EDT] 00000016 SystemOut     O 18:26:16,017 [WebContainer : 0] WARN  CollectionLoadContext - On CollectionLoadContext#cleanup, localLoadingCollectionKeys contained [1] entries
[4/21/11 18:26:15:892 EDT] 00000016 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)

.....
.....
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)
      at org.apache.commons.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:275)
      at org.apache.commons.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:275)
      at org.hibernate.type.IntegerType.get(IntegerType.java:51)
      at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
      at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)
      at org.hibernate.persister.collection.AbstractCollectionPersister.readIndex(AbstractCollectionPersister.java:730)
      at org.hibernate.collection.PersistentList.readFrom(PersistentList.java:402)
      at org.hibernate.loader.Loader.readCollectionElement(Loader.java:1031)
      at org.hibernate.loader.Loader.readCollectionElements(Loader.java:669)
      at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:614)
      at org.hibernate.loader.Loader.doQuery(Loader.java:724)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
      at org.hibernate.loader.Loader.loadCollection(Loader.java:2015)
      ... 117 more

I think this line >><list-index column="USER_ID" />  is the primary suspect?

any help will be greatly appreciated. I am struggling to get this last two days.

thanks.
0
 
royjaydAuthor Commented:
Realized this question is little complex/confusing so i broke it down in this question

http://www.experts-exchange.com/Programming/Languages/Java/Q_26979259.html

any help will be greatly appreciated

thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now