Converting SQL to JPQL

[Randy Maddocks]

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:

@Entity
@Table(name = "FOREIGN_PARTY")
public class ForeignParty implements Serializable {

@OneToMany(cascade = CascadeType.ALL, mappedBy = "foreignPartyId")
private Set<PolicyPayment> policyPaymentCollection;

//getters and setters 

Child Entity:

@Entity
@Table(name = "POLICY_PAYMENT")
public class PolicyPayment implements Serializable {

@JoinColumn(name = "FOREIGN_PARTY_ID", referencedColumnName = "FOREIGN_PARTY_ID")
@ManyToOne
private ForeignParty foreignPartyId;

//getters and setters

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]

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:

SELECT DISTINCT whatever
FROM PolicyPayment p
WHERE p.policy1Num = :policy1Num

For the data you are interested in simply:

SELECT DISTINCT p.policy1Num, p.totalPayoutAmt, p.foreignParty
....

Note the last one gives you your ForeignParty object (and I changed the name).

[Randy Maddocks]

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]

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]

Got it, that makes sense. Thanks again Dave!