?
Solved

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

Posted on 2011-04-27
12
Medium Priority
?
819 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.
0
Comment
Question by:Uncle_J
  • 5
  • 4
  • 2
11 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 35479352
0
 

Author Comment

by:Uncle_J
ID: 35479629
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.
0
 
LVL 92

Accepted Solution

by:
objects earned 1000 total points
ID: 35479636
You need to have a unique id to be able to map an entity (it doesn't have to be a single column)

>  But they do have FK relationships suitable for unidirectional mappings to other entities that I would prefer to take advantage of.

That would suggest that you actually do have an id, can you post the schema to see if you do have a candidate id.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Uncle_J
ID: 35484512
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..)?
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 1000 total points
ID: 35484661
Is it possible that DB_Name + hostname + app_id + facility_id + site_label would not be unique?
What happens if you

select distinct (DB_Name || hostname  || app_id  || facility_id || site_label), count(*) group by
 (DB_Name || hostname  || app_id  || facility_id || site_label)  having count(*) > 1  
0
 

Author Comment

by:Uncle_J
ID: 35484835
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.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35484893
But you can check if this is unique with the query
0
 

Author Comment

by:Uncle_J
ID: 35485508
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.
0
 
LVL 92

Expert Comment

by:objects
ID: 35487816
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

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35489458
I think it would be fair to split 250/250
between http:#35479636  and http:#35484661
0
 

Author Closing Comment

by:Uncle_J
ID: 35492618
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question