Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Converting SQL to JPQL

 
Randy Maddocks
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I am not sure if the subject heading correctly conveys what I am asking, but here goes...I have the following SQL query which retrieves the correct record from a database for me (tested it in DBVisualizer connected to database). I am trying to figure out what the equivalent would be in JPQL.

SQL statement:

SELECT DISTINCT p.POLICY_1_NUM, p.TOTAL_PAYOUT_AMT, f.*
FROM FOREIGN_PARTY f, POLICY_PAYMENT p
WHERE p.POLICY_1_NUM = '1234567'
AND f.FOREIGN_PARTY_ID = p.FOREIGN_PARTY_ID


I have an Oracle database with a parent table (FOREIGN_PARTY) and a child table (POLICY_PAYMENT), with FOREIGN_PARTY_ID being the Primary Key in FOREIGN_PARTY and Foreign Key in POLICY_PAYMENT.

Parent Entity:



Child Entity:



I have tried this for JPQL (using @NamedQuery, and only need to retrieve data from a couple of columns in the child table):

SELECT f, p.policy1Num, p.totalPayoutAmt FROM ForeignParty f, PolicyPayment p WHERE p.policy1Num = :policy1Num

Which retrieves ALL records from parent table, yet strangely enough lists the policy number entered (the :policy1Num parameter) for all records, which is not correct. I also tried this for JPQL (which I thought more closely resembles the SQL statement above):

SELECT f FROM ForeignParty f JOIN f.policyPaymentCollection p WHERE p.policy1Num = :policy1Num AND f.foreignPartyId = p.foreignPartyId

But I get a The left and right expressions type must be of the same type error, referring to the f.foreignPartyId = p.foreignPartyId condition in the WHERE clause. I know each of those objects refers to a different entity, but both refer to the foreignPartyId variable associated with the PK/FK, so I don't understand why it doesn't work.

I am just trying to retrieve a value (or values) from the child table in addition to the parent table, and then populate a primefaces form with those values. I have no problem with primefaces itself, just the retrieval process as it relates to JPQL. I know it is also because I don't fully understand JPQL (and am likely doing the JPQL queries incorrectly), but am currently going through online tutorials and articles to learn it. If anyone can shed some light for me it would be greatly appreciated. Thank you!
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For the first JPQL you used you have no join, so it is joining every ForeignParty row with every PolicyPayment that has that policyNum, hence the mass of results.

For the second one you are joining on the id column of ForeignParty, using the ForeignParty attribute of PolicyPayment. The former is an int (presumably) and the latter is a ForeignParty object, hence the error. This is one reason to get your naming correct. The attribute in PolicyPayment is not an id, it is an actual ForeignParty object.

Anyway, you have already defined the relationship between these tables so you can skip the join entirely and simply work from the PolicyPayment:



For the data you are interested in simply:

Note the last one gives you your ForeignParty object (and I changed the name).
 
Randy Maddocks
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dave,

Many thanks for your reply, and for clarifying on the use of the ForeignParty attribute!!

Dumb question: The object on which you changed the name (i.e. p.foreignParty), if I try entering that in the @NamedQuery I get the message The state field path p.foreignParty cannot be resolved to a valid type.

No doubt I need to make a change to make that a valid type, but not sure how to do that?

Sorry, this should be obvious, but as you can tell, I am learning how this works. Thanks again for your help, very much appreciated!!
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At the moment you have your field in PolicyPayment called foreignPartyId.
You shouldn't be thinking in terms of ids for foreign keys in an Entity, but the classes/objects themselves.
The attribute should be (and is) a foreignParty.

Once that name is changed you'll need to change the mappedBy value in ForeignParty to match.

Does that make sense?
 
Randy Maddocks
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Got it, that makes sense. Thanks again Dave!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic