File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Mapping JPA Entity with a View Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Mapping JPA Entity with a View" Watch "Mapping JPA Entity with a View" New topic
Author

Mapping JPA Entity with a View

R. Jain
Ranch Hand

Joined: Aug 11, 2012
Posts: 370
    
    1

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?
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9

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.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1648
    
    7

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


[How To Ask Questions][Read before you PM me]
R. Jain
Ranch Hand

Joined: Aug 11, 2012
Posts: 370
    
    1

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

Joined: Aug 11, 2012
Posts: 370
    
    1

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

Joined: Mar 07, 2010
Posts: 1648
    
    7

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

Joined: Aug 11, 2012
Posts: 370
    
    1

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

Joined: Aug 11, 2012
Posts: 370
    
    1

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

Joined: Mar 07, 2010
Posts: 1648
    
    7

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?
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2333
    
  28

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

Joined: Mar 07, 2010
Posts: 1648
    
    7

Yes, what Jayesh said is what I was trying to get at, but he put it much more eloquently
R. Jain
Ranch Hand

Joined: Aug 11, 2012
Posts: 370
    
    1

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

Joined: Aug 11, 2012
Posts: 370
    
    1

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

Joined: Mar 07, 2010
Posts: 1648
    
    7

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

Joined: Mar 07, 2010
Posts: 1648
    
    7

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
Bartender

Joined: Jan 17, 2008
Posts: 2333
    
  28

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Mapping JPA Entity with a View