• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Mapping JPA Entity with a View

 
Ranch Hand
Posts: 375
1
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I have a requirement where I need to create a View from multiple tables and map that view with a JPA Entity..
I know the general way of mapping an entity with a table with @Entity and @Table annotations, but I can't understand how to do it with Views..

The problem here may be because View does not generally have a primary key (And that is what happens in my case), but a JPA Entity requires a Primary key..

Can anyone help me or give me some useful link?
 
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

The JPA solution is really most applicable when you need to do updates and I doubt you'll make those views updatable.
For this I'd execute a native sql query and manually map the data to non entity POJOs.
 
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can do what E Armitage suggested. Alternatively if you do want to define an Entity for the view, JPA requires a primary key for each entity, so in this case you would handle it the same way you would handle a table without a key. James had a link in his Wiki here on it:
http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#No_Primary_Key

If there are multiple columns on the view that do make a row unique you can look at using @IdClass or @EmbeddedId
 
R. Jain
Ranch Hand
Posts: 375
1
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

E Armitage wrote:
The JPA solution is really most applicable when you need to do updates and I doubt you'll make those views updatable.
For this I'd execute a native sql query and manually map the data to non entity POJOs.


Hello Armitage,
Can i know what difference does it make when we use em.createQuery() against em.createNativeQuery
I tried to write one and saw that there is of course a difference in writing a Query String for both of them like below: -

createQuery("SELECT b FROM Book b);
createNativeQuery("SELECT * FROM Book);

When should we use Native Query??
 
R. Jain
Ranch Hand
Posts: 375
1
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bill Gorder wrote:You can do what E Armitage suggested. Alternatively if you do want to define an Entity for the view, JPA requires a primary key for each entity, so in this case you would handle it the same way you would handle a table without a key. James had a link in his Wiki here on it:
http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#No_Primary_Key

If there are multiple columns on the view that do make a row unique you can look at using @IdClass or @EmbeddedId


Thanks Bill for the reply,
I have done as you said.. I ahve used a self-generated primary key in my View..
I can also use EmbeddedId that I'll see later after one more problem gets solved.

The problem is I want to make my Entity read-Only (As my View is read-only)..
I tried @ReadOnly annotation, but I'm afraid it doesn't work with JPA. So I tried two attributes of @Column annotation on each field (updatable=false, insertable = false)

I want to ask, is this an eligant way to achieve what I want, or is there anything else which should be used??
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeah there is a hibernate specific annotation http://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/annotations/Immutable.html (make sure to read on this annotation carefully it may not behave exactly as you think it does)

The best way to handle this without vendor specific annotations would probably be to simply make the object immutable.
 
R. Jain
Ranch Hand
Posts: 375
1
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bill Gorder wrote:Yeah there is a hibernate specific annotation http://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/annotations/Immutable.html (make sure to read on this annotation carefully it may not behave exactly as you think it does)

The best way to handle this without vendor specific annotations would probably be to simply make the object immutable.



Hello Bill,
I am facing one problem regarding Key in View..

I am generating a View from multiple Table (5 tables)..


These are my tables: -

ComponentTable : - compId (Primary Key), compName (Varchar)

PackageTable : - pkgId (PrimaryKey), compId (Foreign Key), pkgName (Varchar)

RequireBundle : - reqBundleId (Primary Key), compId (Foreign Key), bundleName (Varchar)

RequiredService : - reqServiceId (Primary Key), compId (Foreign Key), serviceName (varchar)

ProvidedService : - providedServiceId (Primary Key), compId (Foreign Key), serviceName (Varchar)


*NOTE: - A Component can have many requiredBundles, requiredServices, and providedServices


My View will be: -

compId,
compName,
pkgName (From PackageTable),
bundleName (from RequireBundle),
reqServiceName (From RequiredService),
providedServiceName (From ProvidedService)


Now, what I know till now is that, a View cannot have a primary key..
But the entity which I create for that View has to have Primary key..

So, I can have an Embedded Key..
But the problem is, I cannot find out what to take as Embedded Key... (Because In view there will be repeated data for all the combination)

E.g:-

1). Suppose I have 2 entry in RequireBundle Table for a compId 1, and 1 entry in RequriedService Table for a compId 1.......
Then in this case, there will be repeated value for RequiredService in View..

So, we can't take compId, and requiredService as embedded key..

2). Similar is the case with all other combination with compId..

So, from this, one workaround is that: -

We make an Id as a primary key.. But how to link it with View??
 
R. Jain
Ranch Hand
Posts: 375
1
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

R. Jain wrote:

So, from this, one workaround is that: -
We make an Id as a primary key.. But how to link it with View??



Because, when I use it this way, I'm getting Column Index out of range exception : 0 < 1
This may be because, I'm using: -

But when I am creating an Entity object for the View... I have one extra field in my entity for primary key.. for which I don't have any column in View..

Can you help me around this... I don't have any Idea, how to handle this situation??
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeah you can't just add id fields with no corresponding columns in the view.

Lets step back and forget about JPA for a moment. Think of your view like a table. Are you telling me that there is no SQL query that you can write against that view that is guaranteed to return a single result?
 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What's the business key of each of those tables? Apart from the system generated ID, what can be used to uniquely identify the rows?

You need to make sure that your view has the business keys of those tables, and then all the business keys of the dependent tables become the business key of the view. You can treat the business key of the view as the ID.
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, what Jayesh said is what I was trying to get at, but he put it much more eloquently
 
R. Jain
Ranch Hand
Posts: 375
1
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jayesh A Lalwani wrote:What's the business key of each of those tables? Apart from the system generated ID, what can be used to uniquely identify the rows?
You need to make sure that your view has the business keys of those tables, and then all the business keys of the dependent tables become the business key of the view. You can treat the business key of the view as the ID.


By Business key, do you mean Primary key here??
If that is so, then if I have 6 - 7 tables, I'll have a business key in my view with 6 - 7 attributes..
Will it be seen as a good solution??
Because, till now I was not using the business keys from all tables, but just from one table (component table)
 
R. Jain
Ranch Hand
Posts: 375
1
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bill Gorder wrote:Are you telling me that there is no SQL query that you can write against that view that is guaranteed to return a single result?


Well, after some times I found out that, I was getting that exception (Column Index out of Range: 0 < 1) not because I didn't had a column for the primary key in my View..
I was able to fetch a String value from the view (List<String> ) for any one attributes...
But, as and when I try to load Entity object from the Result, it throws an Exception...
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

R. Jain wrote:By Business key, do you mean Primary key here??)



Not necessarily, it could be your primary key but it does not have to be. Consider this simple example:

Say I have a Track table. A track represents a song on an album. Say it looks like this



The primary key is my generated id field. The business key would most likely be represented as a unique constraint. In this case it is a composite of album, disk, and position. This can also uniquely identify a row in this table. Once I have identified which album the song is on, and which disc in the album the song is on, I can use the position (track number) on that disc to uniquely identify a track.
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

R. Jain wrote:Well, after some times I found out that, I was getting that exception (Column Index out of Range: 0 < 1) not because I didn't had a column for the primary key in my View..
I was able to fetch a String value from the view (List<String> ) for any one attributes...
But, as and when I try to load Entity object from the Result, it throws an Exception...



Once again you cannot add fields to your entity which do not map to columns in your view. Think of your view as a table. The same rules apply. You need to identify all of the columns you need to include in your where clause in order to guarantee a unique result when querying your view. Once you have this list make a composite key of those columns for your ID on your entity.
If this ends up being all of the columns in your view then that is fine the important thing is that when used it will guarantee a unique result.
 
Jayesh A Lalwani
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, your view will end up having a lot of columns in the business key, unless there is something about the child tables that helps you eliminate the parent table's business key from the view's business key. Also, note that Hibernate doesn't like it if one of the columns in your composite key is null. So, you need to make sure you key on required columns.
reply
    Bookmark Topic Watch Topic
  • New Topic