Hi,

I have a class with a collection of components. The primary key of the class is char(6) (legacy database) and the mapping looks like:

<class name="org.myclub.hibernate.ClubInfo" table="clubs"
lazy="false">
<id name="code">
<generator class="assigned"/>
</id>
<bag name="regSessions" table="regularsessions" lazy="false">
<key column="club"/>
<composite-element class="org.myclub.hibernate.PlaySession">
<property name="day"/>
<property name="time"/>
<property name="info"/>
</composite-element>
</bag>

It's a legacy database where the "code" column in table "clubs" is declared as "char(6)" type, and the same type for "club" column in the table "regularsessions". Accessor methods for these columns are just standard ones.

THE PROBLEM:

If the identifier is 6 characters long (ie. using the full length of the char(6) field), the mapping works just fine. But for identifiers that are shorter, the elements from "regularsessions" table are not fetched.

I think it might have something to do with DB padding spaces, not really sure. I tried trimming spaces in the accessor methods, but then I had Hibernate exceptions saying:

org.hibernate.HibernateException: identifier of an instance of org.myclub.hibernate.ClubInfo was altered from 4545 to 4545

I don't really have much idea how to go about this one. Hibernate reference says that "string" type is mapped to "varchar", but nothing is said about older, fixed size character types.

Hibernate version:3.1.3
Name and version of the database you are using:
Postgresql 7.4 (debian distro), with pg74.216.jdbc3.jar JDBC driver.

It's a web application running within Tomcat 5.5.17

I also tried the whole thing on Windows, and the behaviour is the same.

Any help/hints would be appreciated

I ran into the same problem where a join table used a CHAR and one of the entities used a VARCHAR. Since I couldn't use a formula for the ID, I added a read only property that used a padded version of the ID using Oracles RPAD function. The relationship uses a property-ref to use the padded value rather than the other one:



This worked great for us, but I don't know if Postgres has a similar function.

Ryan-

Dear Ryan,

I tried it and it worked like magic. PostgreSQL does have the same function. There were minor problems with your code, this is what I ended up with:



The only difference is I had to use "code" rather than "club" column in the rpad invocation, and I dropped the third parameter to rpad. I added accessor getPaddedCode() to org.myclub.hibernate.ClubInfo class and that was all.

Before I got your reply, I was reading on Hibernate's facility to extend the set of types, to add my own custom type, but your solution is far simpler.

Thank you very,very much

No problem! I had a bitch of a time trying to figure that out myself. A PL/SQL guru suggest the rpad() function and I got it work. My trouble got even better since in my case it was a bi-directional, many-to-many relationship. On the other side, it required us to use the trim() function on the related key. You gotta love how flexible Hibernate can be with legacy data :)

Ryan-