We help IT Professionals succeed at work.

Can I use JPA to map a table or view with absolutly no unique id?

Uncle_J
Uncle_J asked
on
Medium Priority
870 Views
Last Modified: 2012-05-11
I am developing an application against a DB schema that I have no control over. There are tables and views in this schema with absolutely no unique ID (explicit or derived). Even using all columns as IDs would not work for these tables/views. But they do have FK relationships suitable for unidirectional mappings to other entities that I would prefer to take advantage of. Is there a strategy for mapping against tables/entities with absolutely no unique ID? I realize any solution would have to be read-only.
Comment
Watch Question

Awarded 2011
Awarded 2011

Commented:

Author

Commented:
This solution (and every that I have seen) assume that you can create a unique composite key from combining columns. In my situation I cannot.
Java Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Name                           Null     Type
------------------------------ -------- --------------- 
DB_NAME                                 VARCHAR2(12)
HOSTNAME                                VARCHAR2(12)
APP_ID                                  VARCHAR2(8)
FACILITY_ID                    NOT NULL VARCHAR2(2)
DEPT_ID                                 VARCHAR2(8)
SITE_LABEL                     NOT NULL VARCHAR2(50)
SITE_VALUE                              VARCHAR2(255)
TEXT                                    VARCHAR2(50)
CHANGE_DT                               DATE
CREATION_DT                             DATE
MODIFIED_BY_SEQ                         NUMBER(10)
AUDIT_TEXT                              VARCHAR2(255)

Open in new window


Notice that only the SITE_LABEL and FACILITY_ID are NOT NULL which would make only them eligible for unique keys. Of which they are not. This table is queried using different combinations of department, facility, site_label, app_id, database, db_name. None of which are required. There is no way to guarantee uniqueness in this table.

Mostly, I am concerned about the consequences of using ORM managed entities (Hibernate JPA/HBM) with false IDs. I would only be querying using these mappings an not performing any other CRUD operations. Is there a situation where Hibernate would puke due to different entities equating to each other? Possibly in some collection in the L1 cache? Is there a reliable strategy for using these types of mappings (disable caching, eager loading and quick detatch, etc..)?
Awarded 2011
Awarded 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Though it may be unlikely, it is still possible that this returns multiple records. This table is used by a number of different applications which have different access patterns. There is control over how these applications create records.
Awarded 2011
Awarded 2011

Commented:
But you can check if this is unique with the query

Author

Commented:
Assuming you expect unique records based on the values in the composite key. Which may not always be the case.

I did more research and I think I have my answer. The consensus seems to be that managed ORM tools should really only be used for truly relational (the R in ORM) entities. I will use projections/report queries for mapping to tables without a well defined unique key.
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
The correct answer is that it is not possible to map an entity without a unique id (35479636)
I also mentioned that multipole columns could be used as key

Awarded 2011
Awarded 2011

Commented:
I think it would be fair to split 250/250
between http:#35479636  and http:#35484661

Author

Commented:
My apologies to Object. I my haste, I actually did not realize that there were 2 responders to my question. I thought all comments were coming from the same person.

I realize now that Object did answer the question:
Q: Can I use JPA to map to a table or view with absolutely no unique ID?
A: You need to have a unique id to be able to map an entity (it doesn't have to be a single column)

This answer could have used a bit more elaboration. The truth is you can map an entity without a unique ID (the ORM does not validate that the ID(s) you have assigned are unique). In the example DDL I posted, I could identify the 2 non-null columns as the ID (or even the columns that for_yan suggested). As long as I did not attempt a single result query (like findById) I am able to use the ORM to manage these entities and even unidirectional FK relationships to other entities for my queries. However, if any of the entities returned by a query are tested for equality (say, in a sortable collection), they may be considered equal, even if some properties (not included in the composite ID) differ. It is for this reason that these entities could not be used for delete or update operations. The app I am creating is read only. I would be using the entities merely as a convenient way to translate DB queries into POJOs, taking advantage of type translation lazy loading. It seemed this would work for what I need but was concerned about any unforeseen consequences by the implementation (Hibernate). I was looking for ways to use ORM in this scenario, assuming that this was a common enough situation that there was one. It seems there is not.

Thank you again for the input and I will be more detailed in my questions next time.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.