Mapping a Database View

[RogerV]

Could someone point me to an example of how to map to a database view please. I've been googling all day and found loads of sites that imply that it can be done but I haven't found an example of how to do it.

Regards

[tch]

It's the exact same way as for a table, no different. Though you may
want to make your columns read only obviously.

Also make sure your view has a primary key that is unique.

./tch



On Fri, Sep 11, 2009 at 9:54 AM, RogerV <roger.varley@...> wrote:

>
> Could someone point me to an example of how to map to a database view please.
> I've been googling all day and found loads of sites that imply that it can
> be done but I haven't found an example of how to do it.
>
> Regards
> --
> View this message in context: http://www.nabble.com/Mapping-a-Database-View-tp25401383p25401383.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[RogerV]

tch wrote:

It's the exact same way as for a table, no different. Though you may
want to make your columns read only obviously.

Also make sure your view has a primary key that is unique.

./tch

The database view in question is over two tables in a MySQL database and has no primary key definable at the view level within the database - although it does contain the primary key fields for the table. So when I use the JPA mapping tool Eclipse complains that there is no @Id mapping.

Regards

[tch]

Yeah that's the rub working with views, you'll either have to change
your view to have a unique id or make a multi-column @Id that's
reasonably unique across the view.

./tch



On Fri, Sep 11, 2009 at 10:21 AM, RogerV <roger.varley@...> wrote:

>
>
>
> tch wrote:
>>
>> It's the exact same way as for a table, no different. Though you may
>> want to make your columns read only obviously.
>>
>> Also make sure your view has a primary key that is unique.
>>
>> ./tch
>>
>
> The database view in question is over two tables in a MySQL database and has
> no primary key definable at the view level within the database - although it
> does contain the primary key fields for the table. So when I use the JPA
> mapping tool Eclipse complains that there is no @Id mapping.
>
> Regards
>
> --
> View this message in context: http://www.nabble.com/Mapping-a-Database-View-tp25401383p25401857.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[RogerV]

tch wrote:

Yeah that's the rub working with views, you'll either have to change
your view to have a unique id or make a multi-column @Id that's
reasonably unique across the view.

So, are you saying that I just "fake" a uniqueId by simply labelling it as such in my entity.java although there is no primary key defined in the database?

Regards

[tch]

No, that would give you identity/caching issues.

If there are 3 columns that will always make a row unique in your view
you can set that as a compound primary key for example.

./tch



On Fri, Sep 11, 2009 at 10:33 AM, RogerV <roger.varley@...> wrote:

>
>
>
> tch wrote:
>>
>> Yeah that's the rub working with views, you'll either have to change
>> your view to have a unique id or make a multi-column @Id that's
>> reasonably unique across the view.
>>
>
> So, are you saying that I just "fake" a uniqueId by simply labelling it as
> such in my entity.java although there is no primary key defined in the
> database?
>
> Regards
>
> --
> View this message in context: http://www.nabble.com/Mapping-a-Database-View-tp25401383p25402082.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[RogerV]

tch wrote:

No, that would give you identity/caching issues.

If there are 3 columns that will always make a row unique in your view
you can set that as a compound primary key for example.

Sorry if I appear dense, but I don't understand.

I've got a view in the database that is defined as

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_Ecu` AS select `t1`.`platformId` AS `platformId`,`t1`.`busId` AS `busId`,`t1`.`txAddress` AS `txaddress`,`t0`.`idsText` AS `idsText`,`t0`.`idsName` AS `idsName`,`t1`.`description` AS `description`,`t1`.`version` AS `version` from (`PEcu` `t0` join `PEcuText` `t1`) where ((`t0`.`platformId` = `t1`.`platformId`) and (`t0`.`txaddress` = `t1`.`txAddress`) and (`t0`.`busid` = `t1`.`busId`))

where platformId, busId and txAddress form a unique identifier that is, in fact the primary key of both underlying tables. Would you be kind enough to explain or show me how you would map this as vw_Ecu.java

Regards

[ljnelson]

On Fri, Sep 11, 2009 at 10:54 AM, RogerV <roger.varley@...> wrote:

where platformId, busId and txAddress form a unique identifier that is, in
fact the primary key of both underlying tables.

1. Create an @Embeddable class that has these three fields in it.  This will end up being your Java class's composite primary key class.
2. Mark each of those fields with the appropriate @Column annotation.  For example, one of your composite key member variables might be "businessID", in which case your @Column annotation would be @Column(name = "busId").
3. Override its equals() and hashCode() methods so that two instances of this class are equal if and only if all three fields have equal values.
4. With that done, go back to your main .java file and give it a field called, say, ID or PK or something similar.  Mark that field as being its @EmbeddedId.  Its type should be the type of the embeddable class you just created.
   
5. Map that .java file using the @Table annotation to your view.

I hope this helps.

Best,
Laird

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[RogerV]

ljnelson wrote:

   1. Create an @Embeddable class that has these three fields in it.  This
   will end up being your Java class's composite primary key class.
   2. Mark each of those fields with the appropriate @Column annotation.
   For example, one of your composite key member variables might be
   "businessID", in which case your @Column annotation would be @Column(name =
   "busId").
   3. Override its equals() and hashCode() methods so that two instances of
   this class are equal if and only if all three fields have equal values.
   4. With that done, go back to your main .java file and give it a field
   called, say, ID or PK or something similar.  Mark that field as being its
   @EmbeddedId.  Its type should be the type of the embeddable class you just
   created.
   5. Map that .java file using the @Table annotation to your view.

So ... in exactly the same way that I would model a database table that has a composite key? That's basically what I meant by "faking" it. Thanks again for evryone's help.

Regards