How to write inner join in hibernate using createQuery()

Question

I have a mysql inner join query which joins 3 tables in the database and which results the required resultset. Now i want to write the corresponding hibernate query using session.createQuery(). I have created the model class for these 3 tables in my project. No mapping associated with the 3 tables. The mysql query is given below.

select b.ID, b.Date, d.Name, IF(c.Amount < 0, c.Amount * -1, c.Amount) Amount, IF(c.Amount < 0, "Payment", "Receipt") Type from (select TransactionID from TransactionDetail where AccountID = 56) a inner join TransactionHeader b on a.TransactionID = b.ID inner join TransactionDetail c on a.TransactionID = c.TransactionID inner join Account d on c.AccountID = d.ID where c.AccountID <> 56;

Now please tell me how to write this query using hibernate session.createQuery()? Please help me.Below is the model class for the 3 tables

Accounts.java:

@Entity
@Table(name = "Account")
public class Accounts {
@Id
@GeneratedValue
@Column(name="ID")
private Integer id;

@Column(name="Name")
private String name;

public Accounts() {
    super();
    // TODO Auto-generated constructor stub
}

public Accounts(Integer id, String name) {
    super();
    this.id = id;
    this.name = name;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}


public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}


@Override
public String toString() {
    return name;
}


}

TransactionDetails.java:

@Entity
@Table(name = "TransactionDetail")
public class TransactionDetails {
@Id
@GeneratedValue
@Column(name="ID")
private Integer id;

@Column(name="TransactionID")
private Integer transactionID;

@Column(name="AccountID")
private Integer accountID;

@Column(name="Amount")
private Float amount;

public TransactionDetails() {
    super();
    // TODO Auto-generated constructor stub
}

public TransactionDetails(Integer id, Integer transactionID,
        Integer accountID, Float amount) {
    super();
    this.id = id;
    this.transactionID = transactionID;
    this.accountID = accountID;
    this.amount = amount;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public Integer getTransactionID() {
    return transactionID;
}

public void setTransactionID(Integer transactionID) {
    this.transactionID = transactionID;
}

public Integer getAccountID() {
    return accountID;
}

public void setAccountID(Integer accountID) {
    this.accountID = accountID;
}


public Float getAmount() {
    return amount;
}

public void setAmount(Float amount) {
    this.amount = amount;
}


}

TransactionHeaders.java:

@Entity
@Table(name = "TransactionHeader")
public class TransactionHeaders {
@Id
@GeneratedValue
@Column(name="ID")
private Integer id;

@Column(name="Date")
private Timestamp date;

public TransactionHeaders() {
    super();
    // TODO Auto-generated constructor stub
}

public TransactionHeaders(Integer id,Timestamp date) {
    super();
    this.id = id;
    this.date = date;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public Timestamp getDate() {
    return date;
}

public void setDate(Timestamp date) {
    this.date = date;
}


}

Answer 1

You can put it all in where clause instead of inner join, if there is no relation between entities.

For example a inner join TransactionHeader b on a.TransactionID = b.ID would become from <some table> a, <some other table> b where a.TransactionID = b.ID.

UPDATE

For conditional fields in select clause, use CASE ... WHEN syntax

Query query=getCurrentSession().createQuery("SELECT A.transactionID, A.id, A.accountID, CASE WHEN A.amount < 0 THEN (A.amount*-1) ELSE A.amount END as Amount, CASE WHEN A.amount < 0 THEN 'Payment' ELSE 'Receipt' END as Type, B.date, C.name from TransactionHeaders B, TransactionDetails A, Accounts C where B.id = A.transactionID and A.accountID = C.id and A.transactionID in (SELECT D.transactionID from TransactionDetails D where D.accountID = :Id)");
query.setParameter("Id", id);

Answer 2

With the current entity mapping you'd have to use cross joins (or natural joins) and add the join conditions as where conditions, i.e. something like this:

SELECT <whatever> FROM TransactionHeaders h, TransactionDetails d, Account a 
  WHERE  
    h.TransactionID = d.TransactionID
    AND c.AccountID = d.ID
    AND h.TransactionID IN (SELECT TransactionID from TransactionDetail where AccountID = 56)
    AND c.AccountID != 56

To make use of HQL/JPQL joins you'd have to change your mapping to use entity relations, i.e. many-to-many etc.

Btw, I'd work on your naming scheme to adhere to the convention, i.e. fields would start with lower case letters (e.g. transactionID) and entity class names should describe a singular entity (e.g. TransactionHeader instead of TransactionHeaders).

Answer 3

To make a query with some options you will need:

Query que;
StringBuilder where = new StringBuilder();
String query = "select h from " + entityClass.getName() + " h ";

To make the join:

query = query +" [LEFT] JOIN FETCH h.table t ";

To add parameters

where.append(" t.field LIKE :attribute");
que.setParameter("attribute", "your_value"));

To create the query with given params

que = em.createQuery(String.format("%s WHERE %s", query, where));

To get the results

que.getResultList();