Implement JPA request with JOIN

Question

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?

Do you want a `JPQL` version of those `SQL` queries or you want to execute them with `JPA`? — Andrianekena Moise, Mar 12 '19 at 17:01

score 1 · Accepted Answer · answered Mar 12 '19 at 17:34

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!

I use the latest JPA (spring-boot-starter-data-jpa - spring-boot-starter-parent 2.1.2.RELEASE) — Peter Penzov, Mar 12 '19 at 18:46
So you can use the `on` clause with jpql like in the response. — Andrianekena Moise, Mar 12 '19 at 18:51
I get error Unknown column 't' in 'field list'. Do you know how I can fix it? — Peter Penzov, Mar 12 '19 at 20:11
Can you please show me how you are executing the jpql query? — Andrianekena Moise, Mar 13 '19 at 02:00
ok. Also if it was helpfull, please up vote or mark as response.Thanks — Andrianekena Moise, Mar 13 '19 at 07:35

Implement JPA request with JOIN

1 Answers1