Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPA Criteria Query(Create a JPA query for th sql)
JPA Criteria Query [message #1716750] Mon, 07 December 2015 23:32
Grinishy Nepal is currently offline Grinishy NepalFriend
Messages: 1
Registered: December 2015
Junior Member
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;
}
}
Previous Topic:Using SessionBroker for sharded DB
Next Topic:Keyword search on MySQL database in JPA
Goto Forum:
  


Current Time: Fri Mar 05 04:46:26 GMT 2021

Powered by FUDForum. Page generated in 0.01485 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top