Join two table when both has Composite Primary Key


#1

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

there is two table, Transactions and ResponseCode

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

Transaction Entity

@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

@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

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

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

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

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?


#2

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.

So, to fix it, you have two options:

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

#3

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


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

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?


#4

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 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 for more details about how you can do that.
  3. Also, you need to make sure you are using the right indexes. This article explains how to get the actual Execution Plan when using Oracle.

#5

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 :slight_smile:
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.