JPA OneToOne annotation across two different jdbc drivers

[Jeffrey H. Coffield]

We have data in two different databases types (MySQL and OpenVMS). Both
have JDBC drivers. Does someone have an example of using the JPA
annotations to connect across two different persistence units?

Thanks in advance,
Jeff Coffield
www.digitalsynergyinc.com

 

[Robert Klemme]

We have data in two different databases types (MySQL and OpenVMS). Both
have JDBC drivers. Does someone have an example of using the JPA
annotations to connect across two different persistence units?

I would be surprised if you could get this working because for this to
work a JPA implementation would need to have a _complete_ RDBMS
implementation inside (i.e. including handling of distributed
transactions, joining in memory, maybe even checking FKs etc.).

If, OTOH you buy a federated database product which does all this for
you and has a JDBC driver then you could get this working - but for
JPA then this would be a single database only hence no special case.

A solution which might work is to use features of RDBMS which are
capable of querying remote databases (Oracle's database link comes to
mind). In that case some tables would appear to reside in a different
schema and you would need to annotate accordingly.

Kind regards

robert

 

[Lew]

We have data in two different databases types (MySQL and OpenVMS). Both
have JDBC drivers. Does someone have an example of using the JPA
annotations to connect across two different persistence units?

What, precisely, do you mean by "connect across two different persistence units"?

You can connect to two persistence units by defining two 'EntityManager' instances, one for each PU.

Is that what you're asking for?

Which annotations did you have in mind? Certainly '@Entity' and '@EntityManager' will work with multiple persistence units. Of course, annotations like '@OneToMany' will not, AFAIK, since they map to foreign-key relationships in the DBMS.

How about giving us a /precise/ explanation of what you wish to accomplish?

Programming is an art of precision. "Using annotations to connect across two persistence units" is so vague as to be meaningless.

 

[jlp]

Le 22/11/2011 16:16, Lew a écrit :

What, precisely, do you mean by "connect across two different persistence units"?

You can connect to two persistence units by defining two 'EntityManager' instances, one for each PU.

Is that what you're asking for?

Which annotations did you have in mind? Certainly '@Entity' and '@EntityManager' will work with multiple persistence units. Of course, annotations like '@OneToMany' will not, AFAIK, since they map to foreign-key relationships in the DBMS.

How about giving us a /precise/ explanation of what you wish to accomplish?

Programming is an art of precision. "Using annotations to connect across two persistence units" is so vague as to be meaningless.

And completing Lew 's advices
do you need XA Transaction ( 2 phases commit) between the 2 databases ?

 

[Jeffrey H. Coffield]

What, precisely, do you mean by "connect across two different persistence units"?

You can connect to two persistence units by defining two 'EntityManager' instances, one for each PU.

Is that what you're asking for?

Which annotations did you have in mind? Certainly '@Entity' and '@EntityManager' will work with multiple persistence units. Of course, annotations like '@OneToMany' will not, AFAIK, since they map to foreign-key relationships in the DBMS.

I had in mind specifically the annotation in the subject. OneToOne. With
that, I think I could get the rest to work or understand the problems
better.

Although the OneToOne and OneToMany use foreign key information when you
generate entity classes from a database,in my testing, they do not
generate SQL joins. The JDBC driver for OpenVMS is one I wrote myself
and I have spent a considerable amount of time in the last three months
debugging and testing it to get it to work correctly with NetBeans and
JPA. The driver is currently in beta testing at a customer site and the
question about annotations came up.

If you have a class A with a OneToOne or a OneToMany relation to class
B, you get a query to load class A and then a separate query to load a
either class B or collection of class B. This works with either driver
as long as the foreign keys information is available. This could work
across two different persistence units as long as the right query gets
sent to the right driver and that information is in the persistence.xml
file.

So, to be precise, I have two different databases, both with JDBC
drivers. The persistence.xml file defines two persistence units and from
each I can create an EntityManagerFactory and get an EntityManager and
then do queries on each. Now there is the problem of annotations. Which
annotations to be precise? Well, how about the one in the subject,
OneToOne. Now I ask (quite precisely):

"Does someone have an example of using the JPA annotations to connect
across two different persistence units?"

But perhaps someone has an example of an annotation other that OneToOne.
That could also be helpful as I would like to understand more about how
the annotations work, so I left out of the question which annotation.

Since there is no way to define in either database a foreign key that
crossed the two databases, if an annotation could work, it would have to
be added manually. We know how to change the entity classes directly to
do this without using annotations, but would prefer the annotation
approach as it should be more clear, more consistent and (hopefully)
less code to debug.

Jeff Coffield

 

[Jeffrey H. Coffield]

Answering my on post...

I just found out from another source that this feature takes EclipseLink
2.3 or later which takes NetBeans 7.1. I was using Netbeans 7.0.1 so
nothing worked. NetBeans 7.1 is in Beta now so I am going to download it
and try again.

Thanks to those who responded.

Jeff Coffield

 

[Arved Sandstrom]

Answering my on post...

I just found out from another source that this feature takes EclipseLink
2.3 or later which takes NetBeans 7.1. I was using Netbeans 7.0.1 so
nothing worked. NetBeans 7.1 is in Beta now so I am going to download it
and try again.

Thanks to those who responded.

Jeff Coffield

Yes, composite persistence units are in the EclipseLink documentation as
being a feature new in 2.3. I'd look at the advertised limitations
really carefully.

Just a nitpick: no such thing as EclipseLink 2.3 requiring any version
of any IDE at all. Not unless you specifically mean IDE tooling support.

AHS
--
You should know the problem before you try to solve it.
Example: When my son was three he cried about a problem with his hand. I
kissed it several times and asked him about the problem. He peed on his
hand.
-- Radia Perlman, inventor of spanning tree protocol

 

[Lew]

I had in mind specifically the annotation in the subject. OneToOne. With
that, I think I could get the rest to work or understand the problems
better.

Don't leave things just in the subject line. That isn't part of the query.

Always put enough in the _body_ of your post.

All too often on Usenet, the subject line turns out not to relate very well with the content of the post. The convention therefore is to make the body self-sufficient. You did not do that.

 

[Gene Wirchenko]

Don't leave things just in the subject line. That isn't part of the query.

Always put enough in the _body_ of your post.

All too often on Usenet, the subject line turns out not to relate very well

with the content of the post. The convention therefore is to make
the body self-sufficient. You did not do that.

It does not matter if the subject line is relevant. Often,
people do not read it. Consider a post with subject
Possible Compiler Bug with Foo 1.2.3
and the version is not mentioned in the post body. Quite often, you
will see a reply asking which version of Foo is being used.

Sincerely,

Gene Wirchenko

 

[Arne Vajhøj]

with the content of the post. The convention therefore is to make
the body self-sufficient. You did not do that.

It does not matter if the subject line is relevant. Often,
people do not read it. Consider a post with subject
Possible Compiler Bug with Foo 1.2.3
and the version is not mentioned in the post body. Quite often, you
will see a reply asking which version of Foo is being used.

It happens.

But whose fault is that?

I think it would be fair for a poster to assume that the
people trying to reply read everything.

Starting to assume that they do not:
- read the subject
- read code examples
- read any text beyond 20 lines
- remember what was written at the top when reading at the bottom
etc.etc. makes it ridiculous to try and post a question.

Arne

 

[Arne Vajhøj]

We have data in two different databases types (MySQL and OpenVMS). Both
have JDBC drivers. Does someone have an example of using the JPA
annotations to connect across two different persistence units?

Another JDBC driver for index-sequential files?

Arne

 

[Arne Vajhøj]

Yes, composite persistence units are in the EclipseLink documentation as
being a feature new in 2.3. I'd look at the advertised limitations
really carefully.

I would also note that if the JPA standard does not guarantee it
to work, then the code would no longer be JPA code but EclipseLink
code.

Arne

 

[Gene Wirchenko]

It happens.

But whose fault is that?

The replier?

I think it would be fair for a poster to assume that the
people trying to reply read everything.

It might be fair, but IME, it is not realistic.

Starting to assume that they do not:
- read the subject
- read code examples
- read any text beyond 20 lines
- remember what was written at the top when reading at the bottom
etc.etc. makes it ridiculous to try and post a question.

It does make it more challenging than optimum.

You really seem to be in a contrarian mood with your latest
posts. Please lighten up.

Sincerely,

Gene Wirchenko

 

[Jeffrey H. Coffield]

I just found out from another source that this feature takes EclipseLink
2.3 or later which takes NetBeans 7.1. I was using Netbeans 7.0.1 so
nothing worked. NetBeans 7.1 is in Beta now so I am going to download it
and try again.

Thanks to those who responded.

Jeff Coffield

Answering my own post again.

I got the annotations for OneToOne and OneToMany to work bidirectionally
across two different databases with different JDBC drivers using
NetBeans 7.1 Beta and EclipseLink 2.3. It appears to be quite fragile as
I got numerous NetBeans internal errors until I got everything exactly
right. If someone is interested, I will put together an outline, but for
someone more familiar with the way annotations work, the Sun Eclipselink
pages on composite persistence units do point the way. It is very new
and I suspect some aspects will change as more development is done, so I
am not sure we are going to adopt this just yet but it is possible to do
and does seem to be the in the future of JPA.

Also we have done no testing on using these annotations to actually
update databases.

Again, thanks to all who posted comments.

Jeff Coffield

 

[Arved Sandstrom]

Answering my own post again.

I got the annotations for OneToOne and OneToMany to work bidirectionally
across two different databases with different JDBC drivers using
NetBeans 7.1 Beta and EclipseLink 2.3. It appears to be quite fragile as
I got numerous NetBeans internal errors until I got everything exactly
right. If someone is interested, I will put together an outline, but for
someone more familiar with the way annotations work, the Sun Eclipselink
pages on composite persistence units do point the way. It is very new
and I suspect some aspects will change as more development is done, so I
am not sure we are going to adopt this just yet but it is possible to do
and does seem to be the in the future of JPA.

Also we have done no testing on using these annotations to actually
update databases.

Again, thanks to all who posted comments.

Jeff Coffield

Thanks for the feedback. I think I'll give it a whirl myself, just to be
prepared for ongoing development of this feature. This is one of those
EclipseLink things, where if the development team decides to include or
exclude or modify just one little capability, it can make a specific use
case possible or completely refuse it. Like you said, things will change
with composite PUs, easily through 2.4.

AHS

 

[Arne Vajhøj]

The replier?


It might be fair, but IME, it is not realistic.

I would tend to agree, but that does still not make it the
posters fault.

Arne