Reputation: 1710
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?
Upvotes: 0
Views: 53
Reputation: 1068
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!
Upvotes: 1