Eclipse Community Forums: EclipseLink » ExpressionBuilder left outer join with on clause

Home » Eclipse Projects » EclipseLink » ExpressionBuilder left outer join with on clause(need help on building correct code)

Show: Today's Messages :: Show Polls :: Message Navigator

ExpressionBuilder left outer join with on clause [message #1801273]

Wed, 16 January 2019 05:31

Missing name Mising name

Messages: 16
Registered: September 2011

Junior Member

This is what I have:
@Override
public void customize(ClassDescriptor classDescriptor) {
ExpressionBuilder eb = new ExpressionBuilder();
Expression satellite = eb.getTable("SATELLITE");
Expression ephemeris = eb.getTable("EPHEMERIS");
Expression join = satellite.leftJoin(ephemeris, eb.get("satelliteUid").equalOuterJoin(eb.getAllowingNull("ephemerisSatelliteUid")));

classDescriptor.getQueryManager().setMultipleTableJoinExpression(join);
}

It should be easy to see that the two tables are SATELLITE and EPHEMERIS, each have a SATELLITE_UID PK column, EPHEMERIS is a child record that may or may not exist for a given SATELLITE record, hence the need for a left outer join.

@Table(name = "SATELLITE")
@SecondaryTables({@SecondaryTable(name = "EPHEMERIS")})
@Customizer(SatelliteEphemerisCustomizer.class)
public class SatelliteEntity

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1801759 is a reply to message #1801273]

Fri, 25 January 2019 17:55

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

What is the problem or question?

If you have SatelliteEntity, what do you want to happen when there is a row in the SATELLITE table but NOT in the EPHEMERIS table and you set data that would be stored in a EPHEMERIS row? If there isn't a strict 1:1 between these tables, they should be broken up into a OneToOne mapping where you can control the existence of these records.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1801768 is a reply to message #1801759]

Fri, 25 January 2019 23:26

Missing name Mising name

Messages: 16
Registered: September 2011

Junior Member

A left outer join DEFINES what I want to happen, the Satellite row is returned and there is null Ephemeris data.
In the past, I used the non-ANSI SQL that set s.satelliteUid(+) = e.satelliteUid, but when that gets "involved" with another entity that uses ANSI SQL left outer joins, the two collide and an error is given that states that the non-
ANSI SQL cannot be used, which is why I am trying to figure out how to enter is this way. But every example that is close, makes no difference in the query. And I am NOT using a @OneToOne relationship mapping, I am using a @SecondaryTable. MAYBE that is the problem???

[Updated on: Fri, 25 January 2019 23:27]

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1801942 is a reply to message #1801768]

Tue, 29 January 2019 17:28

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

By using a secondary table mapping, you are saying your entity has one row in two different tables associated to it - that is what you are specifying defines your entity. If these rows are not strict 1:1 existence, you need to map your entity differently, and a OneToOne mapping seems to match what you are after.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1801957 is a reply to message #1801942]

Tue, 29 January 2019 22:31

Missing name Mising name

Messages: 16
Registered: September 2011

Junior Member

As I have stated with my code excerpt, I am using a Customizer to over ride the strict 1:1 mapping of SecondaryTable. I have done this many times, but never trying to use ANSI SQL "left outer join" specifications. That is what I am trying to do. The code to build a left out join with ExpressionBuilder is ALL I am interested in please.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1803438 is a reply to message #1801957]

Thu, 28 February 2019 17:04

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

ExpressionBuilder has a getAllowingNull for left joins, and if yours is a read-only model, then you might be fine.

If it isn't read-only, or you ever plan to handle updates in the future, you never answered my question - What do you want to have happen when you insert or update to these objects in the various use cases? A SecondaryTables annotation ONLY handles the case where there is a strict 1:1, so if this ever becomes a requirement, you are going to be stuck working out how to do it. Just trying to point out out the potential problems, as forums are referenced by others who might be better advised to use a different approach.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1803440 is a reply to message #1803438]

Thu, 28 February 2019 17:12

Missing name Mising name

Messages: 16
Registered: September 2011

Junior Member

You can consider this entity class as read only. very few of our classes insert/update thru a relationship. We use the FETCH_GROUP capability for performance, so most child table data is loaded separately or thru "read-only" entities like this one, or, thru another mechanism. We do not use the in-memory cache. I know this might sound crazy, but we have many reasons why we work this way. It is one of the reasons I do not like answers to questions like mine to even BE in the forums, because I really do NOT want other people following my example, OTHER than by HOW do I get the durn thing to produce the correct query SQL. My attempts at use the "getAllowingNulls" showed no difference in the resulting SQL.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1803453 is a reply to message #1803440]

Thu, 28 February 2019 21:09

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Show the full SQL generated. I would guess that EclipseLink is adding in an inner join for the @SecondaryTable(name = "EPHEMERIS"). You might have to not use the @SecondaryTable(name = "EPHEMERIS") and define your mapping entirely through customizers, adding in the secondary mapping fields through the customizer.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1803454 is a reply to message #1803453]

Thu, 28 February 2019 21:19

Missing name Mising name

Messages: 16
Registered: September 2011

Junior Member

And that is exactly what I am TRYING to do, all of the code I provided is in the customize method.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1803503 is a reply to message #1803454]

Fri, 01 March 2019 18:16

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

You can choose to be snarky or even ignore my questions and statements, but then, everyone else can do the same for your posts on a free forum when looking for free help.

Since I'm trying to help, I'll restate:
1) did you remove the @SecondaryTables annotation?
- I'd also suggest you remove any mappings using this table until you have the join working, if you haven't already.
2)Show the SQL you have from your latest attempt.

Your expression is trying to use the java class properties when it needs to be using the Table fields from the table expressions instead. More along the lines of

Expression exp = eb.get("satelliteUid").equalOuterJoin(ephemeris.getField("EPHEMERIS.SATELLITE_UID"));

That should allow you to build a join using the SatelliteEntity. satelliteUid mapping to the unmapped EPHEMERIS.SATELLITE_UID DB field. Check the SQL is what you want, and then you can add or fix the JPA mappings to use the fields in the secondary table.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1803514 is a reply to message #1803503]

Sat, 02 March 2019 05:27

Missing name Mising name

Messages: 16
Registered: September 2011

Junior Member

I am NOT being "snarky". I am asking for help and getting everything BUT what I am asking for. You are trying to get me to do it a different WAY from what I WANT to do, INSTEAD of helping me with what I asked for. If THAT is being "snarky", then please feel free to ignore my posts. I have been working with JPA since it first came out and I am NOT a newbie, I know what I am doing, but I don't know EVERYTHING.

Report message to a moderator

Re: ExpressionBuilder left outer join with on clause [message #1803576 is a reply to message #1803514]

Mon, 04 March 2019 19:32

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

I am not an expert in the English language: Snarky might not be the definition of giving curt responses with certain words written in caps that do little to further explain the context of the problem or provide additional information, but I couldn't think of a better one to use in a public forum. In form posts, I find it is better not to make any assumptions, as even experts can make basic yet hard to find code mistakes or use design patterns that might not best fit their use cases.

Since you aren't going to post your SQL or respond to my suggested expression changes, all I can do is wish you luck with your problem and hope someone else is willing to help you with what you have provided.

Regards,
Chris

Report message to a moderator

Previous Topic:	EclipseLink does not see created tables in H2 DB
Next Topic:	executeUpdate failed with EclipseLink-4002 / ORA-00936

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 01 03:44:18 GMT 2023

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter