Implement JPA request with JOIN

I have these 3 entities:

Payment Transactions:

@Entity
@Table(name = "payment_transactions")
public class PaymentTransactions implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;
    .....
}

WPF Payments:

@Entity
@Table(name = "wpf_payments")
public class WpfPayments implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;
    ............
}

WPF Payments Payment transactions:

@Entity
@Table(name = "wpf_payment_payment_transactions")
public class WpfPaymentPaymentTransactions implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false, length = 3)
    private int id;

    @Column(length = 4)
    private Integer wpf_payment_id;

    @Column(length = 4)
    private Integer payment_transaction_id;
    .....
}

I use these SQL requests to get proper data based in id:

SELECT  `payment_transactions`.* FROM `payment_transactions` INNER JOIN `wpf_payment_payment_transactions` ON `payment_transactions`.`id` = `wpf_payment_payment_transactions`.`payment_transaction_id` WHERE `wpf_payment_payment_transactions`.`wpf_payment_id` = 75  ORDER BY `payment_transactions`.`id` ASC LIMIT 1


SELECT `payment_transactions`.* FROM `payment_transactions` INNER JOIN `wpf_payment_payment_transactions` ON `payment_transactions`.`id` = `wpf_payment_payment_transactions`.`payment_transaction_id` WHERE `wpf_payment_payment_transactions`.`wpf_payment_id` = 75

Is there some way to implement these SQL requests using JPA queries?

If you are using JPA 2.0, it is not possible to use JPQL with your queries since you cannot use the ON clause.

One solution is to implement a Bidirectional Mapping on the entities WpfPaymentPaymentTransactions, PaymentTransactions to be able to make a join :

 @Entity
 @Table(name = "payment_transactions")
 public class PaymentTransactions implements Serializable {
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id", unique = true, updatable = false, nullable = false)
 private int id;

 @OneToOne(mappedBy="paymentTransactions") //or OneToMany depending on your model
 private WpfPaymentPaymentTransactions wpfPaymentPaymentTransactions;

 }


 @Entity
 @Table(name = "wpf_payment_payment_transactions")
 public class WpfPaymentPaymentTransactions implements Serializable {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id", unique = true, updatable = false, nullable = false, length = 3)
 private int id;

 @ManyToOne
 @JoinColumn(name="wpf_payment_id")
 private PaymentTransactions paymentTransactions;
 }

Then you can join the two entities like this :

  SELECT t FROM PaymentTransactions t 
  INNER JOIN WpfPaymentPaymentTransactions wppt 

Starting from JPA 2.1, You can add the ON clause with JPQL query. So for the first query, it will be like this :

      SELECT t FROM PaymentTransactions t 
      INNER JOIN WpfPaymentPaymentTransactions wppt 
      ON t.id = wppt.paymentTransactionId` 
      WHERE wppt.wpfPaymentId = :param  
      ORDER BY t.id ASC LIMIT 1

Hope it helps!