当两个都具有复合主键的Hibernate联接两个不相关的表 [英] Hibernate Join two unrelated table when both has Composite Primary Key

问题描述

我正在使用hibernate 5.2编写Java应用程序，但没有HQL

I'm writing java application using hibernate 5.2 but without HQL

有两个表Transactions和ResponseCode

我想由Hibernate生成的select语句的逻辑应该像下面的select波纹管

The logic of select statement which I want to be generated by Hibernate should look like this select bellow

SELECT t.tranType
      ,t.tranId
      ,t.requestDate
      ,t.rcCode
      ,t.tranAmount
      ,r.description
      ,r.status
  FROM transactions t
  LEFT OUTER JOIN responseCode r
    ON t.rcCode = r.rcCode
   AND (r.lang = 'en')
 WHERE (t.merchant_id =5 )

但是我的代码出了点问题，这是我的实现代码段

But something is wrong in my code, here is my implementation snippet

交易实体

@Entity
@Table(name = "transactions")
public class Transaction implements java.io.Serializable {
    private static final long serialVersionUID = 1L;

        @Column(name = "merchant_id", nullable = true)
        private String merchantID;

        @Column(name = "tran_amount", nullable = true)
        private String tranAmount;

        @Id
        @Column(name = "tran_type", nullable = true)
        private String tranType;

        @Column(name = "auth_request_date", nullable = true)
        @Temporal(TemporalType.TIMESTAMP)
        private Date authRequestDate;

        @Id
        @Column(name = "tran_id", nullable = true)
        private String tranID;

        @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
        @JoinColumn(name="rc")
        private ResponseCode rc;

        // Contructos and getters/setters

ResponseCode实体

@Entity
@Table(name = "response_codes")

public class ResponseCode implements java.io.Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "response_code")
    private String rcCode;

    @Column(name = "rc_status")
    private String rcStatus;

    @Column(name = "rc_description")
    private String rcDesc;

    @Column(name = "rc_lang")
    private String rcLang;
    // Contructos and getters/setters

实施代码

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Transaction> criteria = builder.createQuery(Transaction.class);
Root<Transaction> transaction = criteria.from(Transaction.class);
Join<Transaction, ResponseCode> bJoin = transaction.join("rc",JoinType.LEFT);
bJoin.on(builder.equal(bJoin.get("rcLang"), tRequest.getLang()));

Predicate predicate = builder.and(transaction.get("merchantID").in(tRequest.getMerchantList()));
predicate = builder.and(predicate, builder.between(transaction.get("authRequestDate"), dateFrom, dateTo));
criteria.where(predicate);

Hibernate生成两个select语句，第一个语句获取事务列表，第二个语句获取事务代码列表中包含的响应代码详细信息.

Hibernate Generates two select statement, first statement gets transactions list, and second statement gets the response code details which is included in transactions list.

示例: 如果有30000个事务，并且15000个事务的响应代码为000，5000个事务的响应代码为116，而10000个事务的响应代码为400，则将运行第二条选择语句 三次，分别是000116和400 rcCode.

example: if there is 30000 transaction, and 15000 transaction has 000 response code, 5000 transaction has 116 response code and 10000 transaction has 400 response code, it will run second select statement three times, for 000,116 and 400 rcCode.

，但问题是ResponseCode表包含一种响应代码的几种语言

but the problem is that ResponseCode table contains several language for one response code

第一个选择语句包含对语言的限制，但是第二个选择语句不具有此限制，并且它不计量第一个语句中提供的语言，事务对象的最终结果包含某些事务en语言rc描述以及某些交易ge语言rc描述.

first select statement contains the restriction on language but second select statement does not has this restriction, and it does not meter which language is provided in first statement, the final result of transactions object contains for some transactions en language rc description and for some transactions ge language rc descriptions.

我认为这取决于oracle最终选择了哪种语言描述

休眠生成的选择我

SELECT t.tran_type
      ,t.tran_id
      ,t.auth_request_date
      ,t.merchant_id
      ,t.rc
      ,t.tran_amount
  FROM transactions t
  LEFT OUTER JOIN response_codes r
    ON t.rc = r.response_code
   AND (r.rc_lang = ?)
 WHERE (t.merchant_id IN (?))
   AND (t.AUTH_REQUEST_DATE BETWEEN ? AND ?)
 ORDER BY t.AUTH_REQUEST_DATE ASC

休眠生成的选择II

SELECT r.response_code  
      ,r.rc_description 
      ,r.rc_lang        
      ,r.rc_status      
  FROM response_codes r
 WHERE r.response_code = ? 
 //this select statement should have 'AND r.rc_lang = ?'

PS 如果我建立OneToMany关系，它将获得30000笔交易， 执行30000个附加查询以获取每个的响应代码描述 操作

P.s If I make OneToMany relation it gets 30000 transaction and performs 30000 additional query to get response Code description for each operation

您知道如何解决吗?

推荐答案

最后我发现

标准API不支持加入不相关的实体. JPQL确实 也不支持.但是，Hibernate在HQL中支持它，因为 5.1. https://discourse. hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966

Criteria API does not support joining unrelated entities. JPQL does not support that either. However, Hibernate supports it in HQL since 5.1. https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966

也许有一些解决方法，但是在这种情况下，我认为更好的方法是使用HQL而不是Criteria API.

Maybe there is some workarounds, but in this case, I think the better way is to use HQL instead of Criteria API.

这是HQL实现代码段(实体类中未进行任何更改)

Here is HQL implementation code snippet (nothing was changed in entity classes)

String hql = "FROM Transaction t \r\n" + 
             " LEFT OUTER JOIN FETCH t.rc r \r\n" +
             " WHERE (t.merchantID IN (:merchant_id))\r\n" +
             " AND (t.authRequestDate BETWEEN :from AND :to)\r\n" +
             " AND (r.rcLang = :rcLang or r.rcLang is null)\r\n";

Query query =  session.createQuery(hql,Transaction.class);
query.setParameter("merchant_id", tRequest.getMerchantList());
query.setParameter("rcLang", tRequest.getLang());
query.setParameter("from", dateFrom);
query.setParameter("to", dateTo);

List<Transaction> dbTransaction = query.getResultList();

这篇关于当两个都具有复合主键的Hibernate联接两个不相关的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！