# Join two table when both has Composite Primary Key

**URL:** <https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966>\
**Category:** Hibernate ORM\
**Created:** [January 4, 2019, 2:21pm UTC](https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966 "2019-01-04T14:21:30Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![data\_jibo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/data_jibo/32/960_2.png) [@data\_jibo](https://discourse.hibernate.org/u/data_jibo)\
**Post date:** [January 4, 2019, 2:21pm UTC](https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966/1 "2019-01-04T14:21:30Z")

</div>

I’m writing java application using `hibernate 5.2` but without `HQL`

there is two table, `Transactions` and `ResponseCode`

![3I5Qo](https://cdck-file-uploads-canada1.s3.dualstack.ca-central-1.amazonaws.com/flex035/uploads/hibernate/original/1X/b5d2580100979bccc98f423d73086af30a6e6aaa.jpeg)

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

```auto
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

**Transaction Entity**

```auto
@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**

```auto
@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

```

**Implementation code**

```auto
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 Generates two select statement, first statement gets transactions list, and second statement gets the response code details which is included in transactions list.

**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._

but the problem is that `ResponseCode` table contains several language for one response code  
 ![WwrDR](https://cdck-file-uploads-canada1.s3.dualstack.ca-central-1.amazonaws.com/flex035/uploads/hibernate/original/1X/c7ce2c91ee9acc6376a3573f6970aa3e62812dde.jpeg)

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.

_I think it depends on which language description was selected by oracle at last_

**Hibernate generated select I**

```auto
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

```

**Hibernate generated select II**

```auto
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 = ?'

```

Do you know how to fix it?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [January 4, 2019, 4:38pm UTC](https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966/2 "2019-01-04T16:38:23Z")

</div>

Criteria API does not support joining unrelated entities. JPQL does not support that either. However, Hibernate supports it in HQL since 5.1. Check out [this article for more details](https://vladmihalcea.com/how-to-join-unrelated-entities-with-jpa-and-hibernate/).

So, to fix it, you have two options:

1. Either you use HQL
2. Or, you use native SQL.

---

<div class="post-metadata">

**Author:** ![data\_jibo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/data_jibo/32/960_2.png) [@data\_jibo](https://discourse.hibernate.org/u/data_jibo)\
**Post date:** [January 8, 2019, 11:49am UTC](https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966/3 "2019-01-08T11:49:21Z")

</div>

Vlad, Thanks for answer  
now I am trying to implement code using `HQL` without changing anything in Entity classes, but It works very slow

> **example** if My first implementation with `Criteria Api` worked 15 second, now my `HQL` implementation works 88 second

here is code snippet

```auto

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

@SuppressWarnings("unchecked")
List<Transaction> dbTransaction = query.setFetchSize(1000).getResultList();
List<ResponseTransaction> responseTransaction = generateTransactionResponse(dbTransaction);

```

If i run the sql generated by Hibernate on Pl/SQL developer, it takes 15 second to get and fetch records

```auto
SELECT transactio0_.tran_type AS tran_type1_5_0_
      ,transactio0_.tran_id AS tran_id2_5_0_
      ,responseco1_.response_code AS response_code1_3_1_
      ,transactio0_.auth_request_date AS auth_request_date3_5_0_
      ,transactio0_.merchant_id AS merchant_id10_5_0_
      ,transactio0_.rc AS rc16_5_0_
      ,responseco1_.rc_description AS rc_description2_3_1_
      ,responseco1_.rc_lang AS rc_lang3_3_1_
      ,responseco1_.rc_status AS rc_status4_3_1_
  FROM transactions transactio0_
  LEFT OUTER JOIN response_codes responseco1_
    ON transactio0_.rc = responseco1_.response_code
 WHERE (transactio0_.merchant_id IN (?))
   AND (transactio0_.auth_request_date BETWEEN ? AND ?)
   AND (responseco1_.rc_lang = ? OR responseco1_.rc_lang IS NULL)

```

How to Improve the performance?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [January 8, 2019, 6:39pm UTC](https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966/4 "2019-01-08T18:39:22Z")

</div>

Follow these steps and they will guide to tune the performance of the entity query:

1. You need to use pagination. Check out [this article](https://vladmihalcea.com/query-pagination-jpa-hibernate/) for more details. Why fetch 1000 transactions when you can’t display all of them in the UI.
2. Next, you need to log the SQL query executed by Hibernate. Check out [this article](https://vladmihalcea.com/the-best-way-to-log-jdbc-statements/) for more details about how you can do that.
3. Also, you need to make sure you are using the right indexes. [This article](https://vladmihalcea.com/execution-plan-oracle-hibernate-query-hints/) explains how to get the actual Execution Plan when using Oracle.

---

<div class="post-metadata">

**Author:** ![data\_jibo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/data_jibo/32/960_2.png) [@data\_jibo](https://discourse.hibernate.org/u/data_jibo)\
**Post date:** [January 9, 2019, 3:44pm UTC](https://discourse.hibernate.org/t/join-two-table-when-both-has-composite-primary-key/1966/5 "2019-01-09T15:44:25Z")

</div>

Thanks Vlad,

Application gets many record from database to generate CSV file, that’s why i need big fetch size to limit request to Database via the network, to get records faster. For UI fetch size is set to 50.

The reason that the HQL was working slowly was the hibernate logging which I had turned on to `TRACE`, and the most time was wasted to create 500 MB hibernate log file 🙂  
i just changed `<property name="hibernate.generate_statistics">true</property>` to false  
and change TRACE to INFO in to `log4j2.properties` file, and know everything works perfectly.