You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Daryl Stultz <da...@6degrees.com> on 2010/06/24 14:07:22 UTC
Virtual Entity
Hello,
I've get a many-to-one relationship between tables A and E. E is a "loose"
association of A. There is no foreign key in A pointing to E. The
relationship is via tables B, C, and D. In SQL, working out this
relationship is pretty easy, just joining in the intermediate tables and
selecting the values from A and E. I can't use this approach in JPQL,
though, since I can't join or filter as there's no physical relationship.
I'd like to create a view and write an entity to model the view instead of a
table. The problem is that there's no primary key of this "Virtual Entity"
nor any compound key that can be formed. So obviously I can't load in this
entity as the Persistence Context and caches would not be happy. I'm
wondering if I can pick an arbitrary (non-unique) column and label it the
primary key just to get enhancing to work, then reference the entity in
queries. Something like this:
select o.e from MyVirtualEntity o
where o.a = :a
Will this work?
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
RE: Virtual Entity
Posted by C N Davies <cn...@cndavies.com>.
Hi Milosz,
It's a while back now because I've been using 2.0 for some time but bascially the behaviour was very inconsistent. I could run a query against a view 10 times and 3 out of 10 times there would be missing rows. The other issue was that if my view included joins, null handling was very inconsistent across different executions.
I am guilty of not logging a Jira for the issues but at the time I was fighting with other issues and didn't find the time. I have used the same on 2.0 and it doesn't seem to have the same issues.
One caveat on all of this is that at the time I was using run time enhancement (stupidly) so there is a pretty good chance that this a contributing factor if not the root cause. But at the end of the day 2.0 is a much better product (just neds the aggregation rules fixed) so I've not go back and double checked it on 1.2.x
TGIF!
Chris
-----Original Message-----
From: Miłosz Tylenda [mailto:mtylenda@o2.pl]
Sent: Friday, 25 June 2010 5:33 PM
To: users@openjpa.apache.org; cnd@cndavies.com
Subject: RE: Virtual Entity
Chris,
Are you able to provide a few details what was wrong with views? Do you mean problems with schema creation or with reading an entity mapped to an existing view?
Regards,
Milosz
> Not sure about OpenJPA 2.0 but on 1.2.2 views on MySQL was totally broken.
>
RE: Virtual Entity
Posted by Miłosz Tylenda <mt...@o2.pl>.
Chris,
Are you able to provide a few details what was wrong with views? Do you mean problems with schema creation or with reading an entity mapped to an existing view?
Regards,
Milosz
> Not sure about OpenJPA 2.0 but on 1.2.2 views on MySQL was totally broken.
>
RE: Virtual Entity
Posted by C N Davies <cn...@cndavies.com>.
Not sure about OpenJPA 2.0 but on 1.2.2 views on MySQL was totally broken.
-----Original Message-----
From: Matthew Adams [mailto:matthew@matthewadams.me]
Sent: Friday, 25 June 2010 3:46 AM
To: users@openjpa.apache.org
Subject: Re: Virtual Entity
Instead of a virtual entity, you could create the view and tell
OpenJPA about the view via @JoinTable annotations...
On Thu, Jun 24, 2010 at 5:07 AM, Daryl Stultz <da...@6degrees.com> wrote:
> Hello,
>
> I've get a many-to-one relationship between tables A and E. E is a "loose"
> association of A. There is no foreign key in A pointing to E. The
> relationship is via tables B, C, and D. In SQL, working out this
> relationship is pretty easy, just joining in the intermediate tables and
> selecting the values from A and E. I can't use this approach in JPQL,
> though, since I can't join or filter as there's no physical relationship.
> I'd like to create a view and write an entity to model the view instead of
a
> table. The problem is that there's no primary key of this "Virtual Entity"
> nor any compound key that can be formed. So obviously I can't load in this
> entity as the Persistence Context and caches would not be happy. I'm
> wondering if I can pick an arbitrary (non-unique) column and label it the
> primary key just to get enhancing to work, then reference the entity in
> queries. Something like this:
>
> select o.e from MyVirtualEntity o
> where o.a = :a
>
> Will this work?
>
> Thanks.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl.stultz@opentempo.com
>
--
mailto:matthew@matthewadams.me
skype:matthewadams12
yahoo:matthewadams
aol:matthewadams12
google-talk:matthewadams12@gmail.com
msn:matthew@matthewadams.me
http://matthewadams.me
http://www.linkedin.com/in/matthewadams
Re: Virtual Entity
Posted by Daryl Stultz <da...@opentempo.com>.
On Thu, Jun 24, 2010 at 1:45 PM, Matthew Adams <ma...@matthewadams.me>wrote:
> Instead of a virtual entity, you could create the view and tell
> OpenJPA about the view via @JoinTable annotations...
>
I think what you are saying is that A would have a field for E and the view
would be the join "table". That could be useful but in this case I might
consider it "dirtying" my model. I'd rather have an unused "virtual entity"
than dirty my core model. I'll consider that idea in the future though, it
looks handy.
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
RE: Virtual Entity
Posted by C N Davies <cn...@cndavies.com>.
Well I must say I gave up allowing OpenJPA to manage my joins a long time
ago ... ok to be honest it is not OpenJPA it is just the immaturity of the
JPA spec. Apart from this issue you raise, the logic of view management is
at best naïve, lazy loading is as dumb as a box of rocks. EBeans! Or EJB
2.0.
Chirs
-----Original Message-----
From: Daryl Stultz [mailto:daryl.stultz@opentempo.com]
Sent: Wednesday, 30 June 2010 3:05 AM
To: users@openjpa.apache.org
Subject: Re: Virtual Entity
On Tue, Jun 29, 2010 at 12:56 PM, Matthew Adams
<ma...@matthewadams.me>wrote:
> Sweet, glad I could help (if I really did)...
>
> Blast! I spoke too soon. Sure enough, the querying works just fine,
> but now
if I try to delete an instance of E, OpenJPA tries to delete the
corresponding records from the join table, which is a view, so naturally
doesn't work. Unless someone has a good idea, I guess I'll have to roll this
behavior into my virtual entity as well.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Virtual Entity
Posted by Daryl Stultz <da...@opentempo.com>.
On Tue, Jun 29, 2010 at 12:56 PM, Matthew Adams <ma...@matthewadams.me>wrote:
> Sweet, glad I could help (if I really did)...
>
> Blast! I spoke too soon. Sure enough, the querying works just fine, but now
if I try to delete an instance of E, OpenJPA tries to delete the
corresponding records from the join table, which is a view, so naturally
doesn't work. Unless someone has a good idea, I guess I'll have to roll this
behavior into my virtual entity as well.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Virtual Entity
Posted by Matthew Adams <ma...@matthewadams.me>.
Sweet, glad I could help (if I really did)...
On Tue, Jun 29, 2010 at 9:03 AM, Daryl Stultz <da...@6degrees.com> wrote:
> On Tue, Jun 29, 2010 at 8:52 AM, Daryl Stultz <da...@6degrees.com> wrote:
>
>>
>> It appears using @JoinTable with @ManyToOne is a JPA 2.0 feature. I'm using
>> OpenJPA 1.2.1 so that explains it.
>>
>
> I was able to get my virtual entity working against a view (postgresql)
> without a problem. The "bogus primary key" is not unique, but I'm using it
> for querying only.
>
> Also, I was able to relate A:E from E to A as @OneToMany with @JoinTable
> (although I wanted to go A to E, but no big deal, it works). Thanks for the
> suggestion, Matthew.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl.stultz@opentempo.com
>
--
mailto:matthew@matthewadams.me
skype:matthewadams12
yahoo:matthewadams
aol:matthewadams12
google-talk:matthewadams12@gmail.com
msn:matthew@matthewadams.me
http://matthewadams.me
http://www.linkedin.com/in/matthewadams
Re: Virtual Entity
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Jun 29, 2010 at 8:52 AM, Daryl Stultz <da...@6degrees.com> wrote:
>
> It appears using @JoinTable with @ManyToOne is a JPA 2.0 feature. I'm using
> OpenJPA 1.2.1 so that explains it.
>
I was able to get my virtual entity working against a view (postgresql)
without a problem. The "bogus primary key" is not unique, but I'm using it
for querying only.
Also, I was able to relate A:E from E to A as @OneToMany with @JoinTable
(although I wanted to go A to E, but no big deal, it works). Thanks for the
suggestion, Matthew.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Virtual Entity
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Jun 29, 2010 at 8:19 AM, Daryl Stultz <da...@6degrees.com> wrote:
>
>
> Matthew, I'm wondering if you can give a few more details here. It seems
> pretty straightforward, yet OpenJPA is throwing an exception.
>
>>
> @ManyToOne(fetch = FetchType.LAZY)
>
>> @JoinTable(schema = "logical", name = "a_e",
>
>> joinColumns = @JoinColumn(name = "a_id"),
>
>> inverseJoinColumns = @JoinColumn(name = "e_id")
>
>> )
>
>> private E e;
>
It appears using @JoinTable with @ManyToOne is a JPA 2.0 feature. I'm using
OpenJPA 1.2.1 so that explains it.
Nevermind.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Virtual Entity
Posted by Daryl Stultz <da...@6degrees.com>.
> On Thu, Jun 24, 2010 at 1:45 PM, Matthew Adams <ma...@matthewadams.me>wrote:
> Instead of a virtual entity, you could create the view and tell
>
>> OpenJPA about the view via @JoinTable annotations...
>
> (apologies if this is a double post - I seem to have a problem using the
right return address...)
Matthew, I'm wondering if you can give a few more details here. It seems
pretty straightforward, yet OpenJPA is throwing an exception. So A has a
field for E where the relationship is defined by a view. Suppose the view is
named logical.a_e with FK columns a_id, and e_id.
>
A:E from an ER-perspective is a many-to-one. I'd prefer this to be
unidirectional, though I'd be willing to make it bidirectional.
>
So I define A.e something like this:
>
@ManyToOne(fetch = FetchType.LAZY)
> @JoinTable(schema = "logical", name = "a_e",
> joinColumns = @JoinColumn(name = "a_id"),
> inverseJoinColumns = @JoinColumn(name = "e_id")
> )
> private E e;
>
I'm not real fluent with @JoinTable. I've tried other variations. It's
throwing something like this:
>
Caused by: <openjpa-1.2.2-r422266:898935 fatal user error>
org.apache.openjpa.persistence.ArgumentException:
> You have supplied columns for "yadda.yadda.A.e <element:class
java.lang.Object>", but this mapping cannot have columns in this context.
>
I searched on this error and it seems there may be a problem with ManyToOne
and JoinTable. I don't really understand what I found. I tried
SecondaryTable but since A.e is nullable, this doesn't seem to be the right
solution.
>
I'm using OpenJPA 1.2.1.
>
Thanks.
>
--
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Virtual Entity
Posted by Daryl Stultz <da...@opentempo.com>.
On Thu, Jun 24, 2010 at 1:45 PM, Matthew Adams <ma...@matthewadams.me>wrote:
> Instead of a virtual entity, you could create the view and tell
> OpenJPA about the view via @JoinTable annotations...
>
> Matthew, I'm wondering if you can give a few more details here. It seems
pretty straightforward, yet OpenJPA is throwing an exception. So A has a
field for E where the relationship is defined by a view. Suppose the view is
named logical.a_e with FK columns a_id, and e_id.
A:E from an ER-perspective is a many-to-one. I'd prefer this to be
unidirectional, though I'd be willing to make it bidirectional.
So I define A.e something like this:
@ManyToOne(fetch = FetchType.LAZY)
@JoinTable(schema = "logical", name = "a_e",
joinColumns = @JoinColumn(name = "a_id"),
inverseJoinColumns = @JoinColumn(name = "e_id")
)
private E e;
I'm not real fluent with @JoinTable. I've tried other variations. It's
throwing something like this:
Caused by: <openjpa-1.2.2-r422266:898935 fatal user error>
org.apache.openjpa.persistence.ArgumentException:
You have supplied columns for "yadda.yadda.A.e <element:class
java.lang.Object>", but this mapping cannot have columns in this context.
I searched on this error and it seems there may be a problem with ManyToOne
and JoinTable. I don't really understand what I found. I tried
SecondaryTable but since A.e is nullable, this doesn't seem to be the right
solution.
I'm using OpenJPA 1.2.1.
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Virtual Entity
Posted by Matthew Adams <ma...@matthewadams.me>.
Instead of a virtual entity, you could create the view and tell
OpenJPA about the view via @JoinTable annotations...
On Thu, Jun 24, 2010 at 5:07 AM, Daryl Stultz <da...@6degrees.com> wrote:
> Hello,
>
> I've get a many-to-one relationship between tables A and E. E is a "loose"
> association of A. There is no foreign key in A pointing to E. The
> relationship is via tables B, C, and D. In SQL, working out this
> relationship is pretty easy, just joining in the intermediate tables and
> selecting the values from A and E. I can't use this approach in JPQL,
> though, since I can't join or filter as there's no physical relationship.
> I'd like to create a view and write an entity to model the view instead of a
> table. The problem is that there's no primary key of this "Virtual Entity"
> nor any compound key that can be formed. So obviously I can't load in this
> entity as the Persistence Context and caches would not be happy. I'm
> wondering if I can pick an arbitrary (non-unique) column and label it the
> primary key just to get enhancing to work, then reference the entity in
> queries. Something like this:
>
> select o.e from MyVirtualEntity o
> where o.a = :a
>
> Will this work?
>
> Thanks.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl.stultz@opentempo.com
>
--
mailto:matthew@matthewadams.me
skype:matthewadams12
yahoo:matthewadams
aol:matthewadams12
google-talk:matthewadams12@gmail.com
msn:matthew@matthewadams.me
http://matthewadams.me
http://www.linkedin.com/in/matthewadams
Re: Virtual Entity
Posted by Daryl Stultz <da...@opentempo.com>.
On Thu, Jun 24, 2010 at 9:10 AM, C N Davies <cn...@cndavies.com> wrote:
> Yes it does have an @id but I have a base entity that creates them
> automagically (Base64 representation of the current system time), so it is
> sort of like generated value type. My getters martial the data to the
> virtual field is retuned in the structure I expect and I guess you could do
> the same thing with setters if you wanted.
>
> That's interesting. I'll give it a shot with a bogus id column since I'm
not planning to instantiate it. I'll get back to you if I change my mind.
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
RE: Virtual Entity
Posted by C N Davies <cn...@cndavies.com>.
Yes it does have an @id but I have a base entity that creates them
automagically (Base64 representation of the current system time), so it is
sort of like generated value type. My getters martial the data to the
virtual field is retuned in the structure I expect and I guess you could do
the same thing with setters if you wanted.
Chris
-----Original Message-----
From: Daryl Stultz [mailto:daryl.stultz@opentempo.com]
Sent: Thursday, 24 June 2010 10:59 PM
To: users@openjpa.apache.org
Subject: Re: Virtual Entity
On Thu, Jun 24, 2010 at 8:33 AM, C N Davies <cn...@cndavies.com> wrote:
> I have virtual entity that has nothing but getters, if you do it his way
> you
> can use JPQL referring to the virtual fields and let OpenJPA do the work
> for
> you.
>
So your virtual entity has no @Id? Or you are using a bogus property as the
id? Does "nothing but getters" imply read-only and JPA treats it
differently? I actually imagined not putting getters or setters in it.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Virtual Entity
Posted by Daryl Stultz <da...@opentempo.com>.
On Thu, Jun 24, 2010 at 8:33 AM, C N Davies <cn...@cndavies.com> wrote:
> I have virtual entity that has nothing but getters, if you do it his way
> you
> can use JPQL referring to the virtual fields and let OpenJPA do the work
> for
> you.
>
So your virtual entity has no @Id? Or you are using a bogus property as the
id? Does "nothing but getters" imply read-only and JPA treats it
differently? I actually imagined not putting getters or setters in it.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
RE: Virtual Entity
Posted by C N Davies <cn...@cndavies.com>.
I have virtual entity that has nothing but getters, if you do it his way you
can use JPQL referring to the virtual fields and let OpenJPA do the work for
you.
Chris
-----Original Message-----
From: Daryl Stultz [mailto:daryl@6degrees.com]
Sent: Thursday, 24 June 2010 10:07 PM
To: OpenJPA User List
Subject: Virtual Entity
Hello,
I've get a many-to-one relationship between tables A and E. E is a "loose"
association of A. There is no foreign key in A pointing to E. The
relationship is via tables B, C, and D. In SQL, working out this
relationship is pretty easy, just joining in the intermediate tables and
selecting the values from A and E. I can't use this approach in JPQL,
though, since I can't join or filter as there's no physical relationship.
I'd like to create a view and write an entity to model the view instead of a
table. The problem is that there's no primary key of this "Virtual Entity"
nor any compound key that can be formed. So obviously I can't load in this
entity as the Persistence Context and caches would not be happy. I'm
wondering if I can pick an arbitrary (non-unique) column and label it the
primary key just to get enhancing to work, then reference the entity in
queries. Something like this:
select o.e from MyVirtualEntity o
where o.a = :a
Will this work?
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com