如何使用创建查询（） 在Hibernate中写入内部联接

Question

我有一个mysql内部联接查询，它联接数据库中的3个表，并生成所需的结果集。现在我想使用session.createQuery（）编写相应的hibernate查询。我已经在我的项目中为这3个表创建了模型类。没有与3个表关联的映射。mysql查询如下所示。

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;

现在请告诉我如何使用hibernate session.createQuery()编写这个查询？请帮助我。下面是3个表的模型类

帐户. 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

在当前实体映射中，您必须使用交叉连接（或自然连接），并将连接条件添加为where条件，例如：

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

要使用HQL/JPQL连接，您必须更改映射以使用实体关系，即多对多等。

顺便说一句，我将致力于您的命名方案以遵守约定，即字段将以小写字母开头（例如transactionID），实体类名应描述单个实体（例如TransactionHeader而不是TransactionHeaders）。

Answer 2

要使用一些选项进行查询，您需要：

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

要加入，请执行以下操作：

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

添加参数

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

使用给定的参数创建查询

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

要获得结果

que.getResultList();

Answer 3

如果实体之间没有关系，则可以将其全部放在 where 子句中，而不是内部连接。

例如，a.事务 ID 上的内部连接事务标题 b = b.ID 将从

更新

对于select子句中的条件字段，使用CASE… WHEN语法

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