Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » ExpressionBuilder left outer join with on clause(need help on building correct code)
ExpressionBuilder left outer join with on clause [message #1801273] Wed, 16 January 2019 05:31 Go to next message
Gene Turner is currently offline Gene TurnerFriend
Messages: 15
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
Re: ExpressionBuilder left outer join with on clause [message #1801759 is a reply to message #1801273] Fri, 25 January 2019 17:55 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1326
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.
Re: ExpressionBuilder left outer join with on clause [message #1801768 is a reply to message #1801759] Fri, 25 January 2019 23:26 Go to previous messageGo to next message
Gene Turner is currently offline Gene TurnerFriend
Messages: 15
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 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1326
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.
Re: ExpressionBuilder left outer join with on clause [message #1801957 is a reply to message #1801942] Tue, 29 January 2019 22:31 Go to previous messageGo to next message
Gene Turner is currently offline Gene TurnerFriend
Messages: 15
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.
Re: ExpressionBuilder left outer join with on clause [message #1803438 is a reply to message #1801957] Thu, 28 February 2019 17:04 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1326
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.
Re: ExpressionBuilder left outer join with on clause [message #1803440 is a reply to message #1803438] Thu, 28 February 2019 17:12 Go to previous messageGo to next message
Gene Turner is currently offline Gene TurnerFriend
Messages: 15
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.
Re: ExpressionBuilder left outer join with on clause [message #1803453 is a reply to message #1803440] Thu, 28 February 2019 21:09 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1326
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.
Re: ExpressionBuilder left outer join with on clause [message #1803454 is a reply to message #1803453] Thu, 28 February 2019 21:19 Go to previous messageGo to next message
Gene Turner is currently offline Gene TurnerFriend
Messages: 15
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.
Re: ExpressionBuilder left outer join with on clause [message #1803503 is a reply to message #1803454] Fri, 01 March 2019 18:16 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1326
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.

Re: ExpressionBuilder left outer join with on clause [message #1803514 is a reply to message #1803503] Sat, 02 March 2019 05:27 Go to previous messageGo to next message
Gene Turner is currently offline Gene TurnerFriend
Messages: 15
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.
Re: ExpressionBuilder left outer join with on clause [message #1803576 is a reply to message #1803514] Mon, 04 March 2019 19:32 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1326
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
Previous Topic:Eclipselink - dead locks in aqcuire and release deferred locks - Eclipselink 2.4.2 and above
Next Topic:executeUpdate failed with EclipseLink-4002 / ORA-00936
Goto Forum:
  


Current Time: Mon Aug 26 06:53:59 GMT 2019

Powered by FUDForum. Page generated in 0.02577 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top