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;
}


}

You might want to provide some information on your entity mapping first. — Thomas
– Thomas, Commented Jan 15, 2015 at 8:43

Predrag Maric · Accepted Answer · 2015-01-15 10:49:05Z

2

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);

edited Jan 15, 2015 at 10:49

answered Jan 15, 2015 at 8:55

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Predrag Maric Over a year ago

Just apply this to the rest of the query, you could do it for the time it took you to write that comment. If there are some other issues with the query beside this, please update the question with your effort and we'll help you further.

KJEjava48 Over a year ago

how could i get the type and amount using if condition in this query?Please help me

Jordi Castilla · Accepted Answer · 2015-01-15 09:02:03Z

0

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();

edited Jan 15, 2015 at 9:02

answered Jan 15, 2015 at 8:54

Jordi Castilla

27.1k8 gold badges73 silver badges113 bronze badges

3 Comments

KJEjava48 Over a year ago

please write my mysql query using createQuery().

Jordi Castilla Over a year ago

I gave you the tools... now it's your turn to work... ;)

Jordi Castilla Over a year ago

what did you try? how you manage the entitys? take the example and try transforming your query in little querys that work via Java. Then add some params and finally join them...

Thomas · Accepted Answer · 2015-01-15 09:31:45Z

0

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).

edited Jan 15, 2015 at 9:31

answered Jan 15, 2015 at 9:05

Thomas

88.9k13 gold badges126 silver badges162 bronze badges

Collectives™ on Stack Overflow

How to write inner join in hibernate using createQuery()

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related