I am new to JPA trying to write a criteria api query for the below query but cant find a good example that can help me get this done. THe date and the cardholder id are suppose to be passed as a parameter and can change. I have added the entities that i have in my code.
Any help or guidance is appreciated.
select tx.id,tx.date,tx.settled,tx.settledDate,te.debit,te.amount,te.account,tx.card, acc.accountType, pl.name,mer.id, mer.name , ch.id
from cardholder ch
join txn_entry te on ch.id=te.cardholder
join txn tx on te.txn = tx.id
join merchant mer on tx.merchant=mer.id
join account acc on te.account = acc.id
join plan pl on acc.plan= pl.id
where te.cardholder= 2 and tx.date between '2015/11/19' and '2015/11/21'
order by tx.date desc;
@Entity
@Table(name = "cardholder")
public class CardHolderEntity implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private String firstname;
private String lastname;
private String email;
private String phone;
private Set<CardEntity> cardHoldercards = new HashSet<CardEntity>();
private Set<CardHolderAccountsEntity> cardHolderAccounts = new HashSet<CardHolderAccountsEntity>();
private Set<TransactionEntryEntity> transactionEntryEntity = new HashSet<TransactionEntryEntity>();
private IssuerEntity issuerEntity;
@Id
@Column(name = "id")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name = "firstname")
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
@Column(name = "lastname")
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
@Column(name = "email")
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Column(name = "phone")
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "cardHolderEntity", cascade = CascadeType.ALL, orphanRemoval = true)
@Fetch(FetchMode.SUBSELECT)
@OrderBy
public Set<CardEntity> getCardHoldercards() {
return cardHoldercards;
}
public void setCardHoldercards(Set<CardEntity> cardHoldercards) {
this.cardHoldercards = cardHoldercards;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "cardHolderAccountsEntity", cascade = CascadeType.ALL, orphanRemoval = true)
@Fetch(FetchMode.SUBSELECT)
@OrderBy
public Set<CardHolderAccountsEntity> getCardHolderAccounts() {
return cardHolderAccounts;
}
public void setCardHolderAccounts(Set<CardHolderAccountsEntity> cardHolderAccounts) {
this.cardHolderAccounts = cardHolderAccounts;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "cardHolderEntity", cascade = CascadeType.ALL, orphanRemoval = true)
@Fetch(FetchMode.SUBSELECT)
public Set<TransactionEntryEntity> getTransactionEntryEntity() {
return transactionEntryEntity;
}
public void setTransactionEntryEntity(Set<TransactionEntryEntity> transactionEntryEntity) {
this.transactionEntryEntity = transactionEntryEntity;
}
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "ISSUER")
public IssuerEntity getIssuerEntity() {
return issuerEntity;
}
public void setIssuerEntity(IssuerEntity issuerEntity) {
this.issuerEntity = issuerEntity;
}
}
@Entity
@Table(name = "txn_entry")
public class TransactionEntryEntity implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private String debit;
private BigDecimal amount;
private char valid;
private CardHolderEntity cardHolderEntity;
private TransactionEntity transactionEntity;
private AccountEntity accountEntity;
@Id
@Column(name = "id")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name = "debit")
public String getDebit() {
return debit;
}
public void setDebit(String debit) {
this.debit = debit;
}
@Column(name = "amount")
public BigDecimal getAmount() {
return amount;
}
public void setAmount(BigDecimal amount) {
this.amount = amount;
}
@Column(name = "valid")
public char getValid() {
return valid;
}
public void setValid(char valid) {
this.valid = valid;
}
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "CARDHOLDER")
public CardHolderEntity getCardHolderEntity() {
return cardHolderEntity;
}
public void setCardHolderEntity(CardHolderEntity cardHolderEntity) {
this.cardHolderEntity = cardHolderEntity;
}
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "TXN")
public TransactionEntity getTransactionEntity() {
return transactionEntity;
}
public void setTransactionEntity(TransactionEntity transactionEntity) {
this.transactionEntity = transactionEntity;
}
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "ACCOUNT")
public AccountEntity getAccountEntity() {
return accountEntity;
}
public void setAccountEntity(AccountEntity accountEntity) {
this.accountEntity = accountEntity;
}
}
@Entity
@Table(name = "txn")
public class TransactionEntity implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private Date date;
private boolean settled;
private Date settleddate;
private String retcode;
private MerchantEntity merchantEntity;
private CardEntity cardEntity;
private Set<TransactionEntryEntity> transactionEntryEntity = new HashSet<TransactionEntryEntity>();
@Id
@Column(name = "id")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name = "date")
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@Column(name = "settled")
public boolean isSettled() {
return settled;
}
public void setSettled(boolean settled) {
this.settled = settled;
}
@Column(name = "settleddate")
public Date getSettleddate() {
return settleddate;
}
public void setSettleddate(Date settleddate) {
this.settleddate = settleddate;
}
@Column(name = "retcode")
public String getRetcode() {
return retcode;
}
public void setRetcode(String retcode) {
this.retcode = retcode;
}
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "MERCHANT")
public MerchantEntity getMerchantEntity() {
return merchantEntity;
}
public void setMerchantEntity(MerchantEntity merchantEntity) {
this.merchantEntity = merchantEntity;
}
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "CARD")
public CardEntity getCardEntity() {
return cardEntity;
}
public void setCardEntity(CardEntity cardEntity) {
this.cardEntity = cardEntity;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "transactionEntity", cascade = CascadeType.ALL, orphanRemoval = true)
@Fetch(FetchMode.SUBSELECT)
public Set<TransactionEntryEntity> getTransactionEntryEntity() {
return transactionEntryEntity;
}
public void setTransactionEntryEntity(Set<TransactionEntryEntity> transactionEntryEntity) {
this.transactionEntryEntity = transactionEntryEntity;
}
@Entity
@Table(name = "merchant")
public class MerchantEntity implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private String name;
private Set<TransactionEntity> transactionEntity = new HashSet<TransactionEntity>();
@Id
@Column(name = "id")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name = "name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "merchantEntity", cascade = CascadeType.ALL, orphanRemoval = true)
@Fetch(FetchMode.SUBSELECT)
public Set<TransactionEntity> getTransactionEntity() {
return transactionEntity;
}
public void setTransactionEntity(Set<TransactionEntity> transactionEntity) {
this.transactionEntity = transactionEntity;
}
@Entity
@Table(name = "plan")
public class PlanEntity {
private String id;
private Date endDate;
private String accountAccessLevel;
private String name;
private Set<AccountEntity> accountEntity = new HashSet<AccountEntity>();
@Id
@Column(name = "id")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name = "enddate")
public Date getEndDate() {
return endDate;
}
public void setEndDate(Date endDate) {
this.endDate = endDate;
}
@Column(name = "onlineaccess")
public String getAccountAccessLevel() {
return accountAccessLevel;
}
public void setAccountAccessLevel(String accountAccessLevel) {
this.accountAccessLevel = accountAccessLevel;
}
@Column(name = "name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "planEntity", cascade = CascadeType.ALL, orphanRemoval = true)
@Fetch(FetchMode.SUBSELECT)
public Set<AccountEntity> getAccountEntity() {
return accountEntity;
}
public void setAccountEntity(Set<AccountEntity> accountEntity) {
this.accountEntity = accountEntity;
}
@Entity
@Table(name = "account")
public class AccountEntity implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private String accounttype;
private Date endDate;
private boolean active;
private PlanEntity planEntity;
private Set<TransactionEntryEntity> transactionEntry = new HashSet<TransactionEntryEntity>();
@Id
@Column(name = "id")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Column(name = "accounttype")
public String getAccounttype() {
return accounttype;
}
public void setAccounttype(String accounttype) {
this.accounttype = accounttype;
}
@Column(name = "enddate")
public Date getEndDate() {
return endDate;
}
public void setEndDate(Date endDate) {
this.endDate = endDate;
}
@Column(name = "active")
public boolean isActive() {
return active;
}
public void setActive(boolean active) {
this.active = active;
}
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "PLAN")
public PlanEntity getPlanEntity() {
return planEntity;
}
public void setPlanEntity(PlanEntity planEntity) {
this.planEntity = planEntity;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "accountEntity", cascade = CascadeType.ALL, orphanRemoval = true)
@Fetch(FetchMode.SUBSELECT)
public Set<TransactionEntryEntity> getTransactionEntry() {
return transactionEntry;
}
public void setTransactionEntry(Set<TransactionEntryEntity> transactionEntry) {
this.transactionEntry = transactionEntry;
}
}
] 
Search
Help
Register
Login
Home
