These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 14 posts ] 
  Previous topic | Next topic 
Author Message
rpitre
 Post subject: Case INSENSITIVE joins
PostPosted: Fri Apr 06, 2007 9:33 am 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
Hibernate version: 3.2
Mapping documents:

Code:
        <set name="contacts" cascade="none" order-by="LastName, FirstName" >
         <key column="CompanyID" />
             <one-to-many class="com.shawmut.model.contact.Contact" />
        </set>


Code between sessionFactory.openSession() and session.close(): Using spring
Full stack trace of any exception that occurs: No errors generated
Name and version of the database you are using: SQL Server 2000


We're using a LEGACY database and i can not change the schema. We discovered that hibernate is case sensitive on joins. How can i instruct hibernate to be case insensitive in the mapping files? Let me explain a little more. We have a Company and Contacts tables. The PK on the company table is a string (e.g. SP01) and the PK on the Contacts table is a String as well (e.g. RusPit). A company has many contacts defined by a foreign key on the Contacts table. So I have a Company record with the PK as SP01. I also have many Contact records for this company. The FK on the contacts table may have a CompanyID as 'SP01' or 'sp01'. In the Company.hbm.xml mapping file we have a Set of Contacts defined.

Code:
        <set name="contacts" cascade="none" order-by="LastName, FirstName" >
         <key column="CompanyID" />
             <one-to-many class="com.shawmut.model.contact.Contact" />
        </set>


When i retrieve a Company object from the database:
Code:
Company company = companyService.getComapny("SP01");


and then i iterate through the contacts (company.getContacts();) I don't see all the contacts with a CompanyID of 'sp01'.

How can i override this behavior in hibernate?

Can i override this behavior in hibernate?
Top
 Profile  
 
rpitre
 Post subject:
PostPosted: Fri Apr 06, 2007 11:42 am 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
After digging a little deeper i discovered the hibernate generated sql is returning all contacts from the database despite whether or not the companyid is 'AM22' or 'am22'.

In my test case on i'm calling this method:

Code:
Company company = companyDao.getCompany("AM22");





Here's what i see after turning debug on:



Code:

2007-04-06 11:21:46,939 DEBUG [org.hibernate.engine.CollectionLoadContext] - <2 collections were found in result set for role: com.shawmut.model.company.Company.activeContacts>

2007-04-06 11:21:46,955 DEBUG [org.hibernate.engine.CollectionLoadContext] - <collection fully initialized: [com.shawmut.model.company.Company.activeContacts#AM22]>

2007-04-06 11:21:46,955 DEBUG [org.hibernate.engine.CollectionLoadContext] - <collection fully initialized: [com.shawmut.model.company.Company.activeContacts#am22]>

2007-04-06 11:21:46,955 DEBUG [org.hibernate.engine.CollectionLoadContext] - <2 collections initialized for role: com.shawmut.model.company.Company.activeContacts>





So it looks like Hibernate found 2 collections, one for 'AM22' and one for 'am22'. Hibernate only seems to return the collection with the CompannyID i originally asked for. I'm not sure what to do from here. I need to dig a little deeper.



I wonder if anyone else has ran into this situation.
Top
 Profile  
 
Arvoreen
 Post subject:
PostPosted: Thu Jun 14, 2007 4:01 pm 
Newbie

Joined: Thu Jun 14, 2007 3:43 pm
Posts: 6
I am running into the exact same issue.

I have User and Group tables, and a mapping table between user and group.

The entries in the mapping table may or may not match (casewise) the entries in the User table for example.

So, for a given user id 'aa', and group entries as follows
aa group1
AA group2

I see the following scenarios:

User u = Session.load(User.class,"aa");
u.getGroups() returns 1 group object, 'group1'.

User u = Session.load(User.class,"AA");
u.getGroups() returns 1 group object 'group2'

NOTE that both of the loads find the SAME USER, but the mapping to groups is based on the case of the user's primary key.

User u = Session.load(User.class,"aA"); will find the same user as above, but u.getGroups() will return an empty list.

Does anyone have any idea if this is

a) A bug in Hibernate?
b) Working as deisgned?
c) Any workarounds? (I've tried a custom type for the userid, with no luck)
Top
 Profile  
 
rpitre
 Post subject:
PostPosted: Thu Jun 14, 2007 7:16 pm 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
I extended hibernate's StringType to uppercase the value straight off the resultSet.

Code:
package com.company.util.hibernate.type;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.hibernate.type.StringType;

public class ConvertToUpperCaseStringType extends StringType {

   public Object get(ResultSet rs, String name) throws SQLException {
      if(rs.getString(name) != null) return rs.getString(name).toUpperCase();
      else return null;
   }
}




Use the new type on your PK and FKs.


Code:
<hibernate-mapping package="com.company.model.company">

    <class name="Company" table="Company" >

       <id name="companyId" column="CompanyID" type="com.company.util.hibernate.type.ConvertToUpperCaseStringType" unsaved-value="null">
            <generator class="com.company.util.hibernate.id.CompanyIndentifierGenerator">
                      <param name="storedProcedure">{ call dbo.hbm_Company_NewKey(?) }</param>                      
            </generator>
        </id>
.
.
.
.
.
Top
 Profile  
 
Arvoreen
 Post subject:
PostPosted: Fri Jun 15, 2007 10:38 am 
Newbie

Joined: Thu Jun 14, 2007 3:43 pm
Posts: 6
Using the upper casing string type ALMOST works.

Now, if I look for user "AA", I find both groups.
However, if I look for user "aa", I find NO GROUPS.

I still think this looks like some kind of bug within Hibernate....
Top
 Profile  
 
rpitre
 Post subject:
PostPosted: Fri Jun 15, 2007 11:24 am 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
Did you use the ConvertToUpperCaseStringType on the FK of the child object?
Top
 Profile  
 
Arvoreen
 Post subject:
PostPosted: Fri Jun 15, 2007 11:38 am 
Newbie

Joined: Thu Jun 14, 2007 3:43 pm
Posts: 6
I tried using on the prmiary key of the child. Maybe some actual code examples are in order:


Code:
@Entity
public class User extends BaseDatabasePOJO
{
  protected String              userId;
  protected Set<Group>          groups;

  @Id
@Type(type="com.company.util.hibernate.type.ConvertToUpperCaseStringType")
  public String getUserId ()
  {
    return userId;
  }

  public void setUserId (String s)
  {
    userId = s;
  }

  @ManyToMany
  @JoinTable(name = "tm_user_group", joinColumns = {@JoinColumn(name = "user_id")}, inverseJoinColumns = {@JoinColumn(name = "group_id")})
  public Set<Group> getGroups ()
  {
    return groups;
  }

  public void setGroups (Set<Group> groups)
  {
    this.groups = groups;
  }
}

@Entity
public class Group extends BaseDatabasePOJO
{
  protected String              groupId;
  protected Set<User>           users;

  @Id
@Type(type="com.company.util.hibernate.type.ConvertToUpperCaseStringType")
  public String getGroupId ()
  {
    return groupId;
  }

  public void setGroupId (String s)
  {
    groupId = s;
  }
  @ManyToMany(mappedBy = "groups")
  public Set<User> getUsers ()
  {
    return users;
  }

  public void setUsers (Set<User> users)
  {
    this.users = users;
  }
}




Adding the ConvertToUpperCaseString on the primary key of Group made no difference. If I look for an all lowercase user, I will never find any groups in the list. The same user with all uppercase will now correctly find all groups, regardless of case in the join table. This is true as long as the USER primary key is the custom type.
Top
 Profile  
 
rpitre
 Post subject:
PostPosted: Fri Jun 15, 2007 11:41 am 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
I'm sorry, you also need to use it on the FK of parent object.
Top
 Profile  
 
Arvoreen
 Post subject:
PostPosted: Fri Jun 15, 2007 11:47 am 
Newbie

Joined: Thu Jun 14, 2007 3:43 pm
Posts: 6
Not sure what you mean by FK.

Both User and Group PK's are set to upper case type. The many-to-many relationship is through a join table. I don't think I can apply a custom type to the join table, can I?

PK of User and PK of Group are the only 2 columns in join table, so effectively, the FK IS handled.
Top
 Profile  
 
rpitre
 Post subject:
PostPosted: Fri Jun 15, 2007 12:00 pm 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
Ahhh....I see....I'm not sure how to handle that....Have you tried this:

Code:

  @ManyToMany
  @JoinTable(name = "tm_user_group", joinColumns = {@JoinColumn(name = "UPPER(user_id)")}, inverseJoinColumns = {@JoinColumn(name = "UPPER(group_id)")})
  public Set<Group> getGroups ()
  {
    return groups;
  }


I'm not sure if that will work or not, i don't use annontations.
Top
 Profile  
 
Arvoreen
 Post subject:
PostPosted: Fri Jun 15, 2007 1:52 pm 
Newbie

Joined: Thu Jun 14, 2007 3:43 pm
Posts: 6
No luck there. I can't specify 'UPPER(user_id)' or UPPER(group_id)' since @JoinColumn is expecting ACTUAL column names. Does anyone else have any ideas?

I know that if I can control the initial user load/find to always use a ALL CAPS name, it will work, but that seems like a workaround for a bug....
Top
 Profile  
 
rpitre
 Post subject:
PostPosted: Fri Jun 15, 2007 2:05 pm 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
What about this:

Code:
'UPPER(user_id) AS user_id' or UPPER(group_id) AS group_id'


Top
 Profile  
 
Arvoreen
 Post subject:
PostPosted: Fri Jun 15, 2007 2:51 pm 
Newbie

Joined: Thu Jun 14, 2007 3:43 pm
Posts: 6
Nope no luck there either. It actually tried to figure out how to treat the 'whole string' as a column name. It just doesn't seem to interpert any function escapes or anything.
Top
 Profile  
 
rpitre
 Post subject:
PostPosted: Fri Jun 15, 2007 4:37 pm 
Newbie

Joined: Mon Jan 23, 2006 12:37 pm
Posts: 16
Hmmmm...I'm not sure what to tell you....I can't think of anthing else to try....
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 14 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.