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

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Loading data with duplicate IDs
PostPosted: Fri Sep 04, 2009 5:25 pm 
Newbie

Joined: Fri Sep 04, 2009 5:07 pm
Posts: 2
I've searched both this forum and google for a long time today, and I just have no clue how to solve my problem.

The problem is, we've got an application where we have no control over the database structure. It is what it is.

I have one table (let's call it DET) with a column for diagnostic type (DIG). That DIG code is tied to a reference table (let's call it DIG_CDS).

So, in the DET.DIG column, we can have a value such as '7330' (Yes, it's a string). However, in our reference code table, DIG_CDS, we've got multiple rows identified by DIG. the DIG_CDS table has additional columns for Version and effective start/end dates. Basically, I need to find the highest version number where the start/end dates surround another date on a parent table to DET.

I've tried mapping subselects, filters, functions, and nothing gets me to where I need it.

Is there anything I can do with interceptors or event listeners before or during the load to manipulate the query?

When I try to load the DET object it is fine, but once I try to lazy load the diagnosis code:
Code:
detRecord.getDiagnosisType()

I get the expected error of:
Code:
The exception is: More than one row with the given identifier was found: 7330, for class: xxx.det.DiagnosisType.


I realize this database design isn't proper, but we cannot change the db structure. Is there anything we can do to load the DiagnosisType? I can load lists of Diagnosis Types, and I can even save Diagnosis types with values that are either unique or duplicated in the reference table. It's just loading an individual value for on-screen display is dying.

Thanks


Top
 Profile  
 
 Post subject: Re: Loading data with duplicate IDs
PostPosted: Fri Sep 04, 2009 7:17 pm 
Expert
Expert

Joined: Tue May 13, 2008 3:42 pm
Posts: 919
Location: Toronto & Ajax Ontario www.hibernatemadeeasy.com
So, 7330 isn't a primary key? Is there a way to make a record unique in the associated table through a compound key? Then use the compound key as the foreign key?

I've seen these types of things before. I call them 'mystery keys' because they are a mystery as to how anyone who designed the database every thought they could work.

You really need a proper foreign key. This must be an old database, because all foreign keys should have a constraint, just for the sake of integrity.

I'd remove the association, and then just do a separate query for the record. This design means your mapping isn't really correct, and that's not something you want to put into production.

_________________
Cameron McKenzie - Author of "Hibernate Made Easy" and "What is WebSphere?"
http://www.TheBookOnHibernate.com Check out my 'easy to follow' Hibernate & JPA Tutorials


Top
 Profile  
 
 Post subject: Re: Loading data with duplicate IDs
PostPosted: Sat Sep 05, 2009 11:20 pm 
Newbie

Joined: Fri Sep 04, 2009 5:07 pm
Posts: 2
Nope, 7330 isn't a PK, but the diagnosis code table does have a version column on it so we could use a composite key containing DIG_CDS.DIG and DIG_CDS.VER columns.

Problem is, the DET table references DIG_CDS with just a single column, DIG, so we can't store which version is being used.

Breaking the association in the mapping would definitely work, it's so simple I can't believe I didn't think about it, but you're right, this isn't good stuff to do. It doesn't help that this is one of at least 10 tables with the same problem, and we'd have to change a number of places that rely on lazy loading.

Actually, I could break the mapping, then write an event listener which would trigger after the load, and populate these dangling 'mystery keys'...

The reason we don't have foreign keys, is that our table is the DET table, and we're using an alias to the DIG_CDS table which is a shared/global table for use by any application, because of how security is done where I work. Because of this they don't/can't/won't have FKs to the shared tables. It's messy, and I'm not a fan one bit. I'm tempted to just say "hey forget the shared table, lets make our own table, and just load it from the shared table, but at least give it some true UID's or something" but that would have to wait until a future release.

Thanks.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

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.