Hibernate Join two unrelated table when both has Composite Primary Key

Question

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

P.s If I make OneToMany relation it gets 30000 transaction and performs 30000 additional query to get response Code description for each operation, known as N + 1 problem

Do you know how to fix it?

Answer 1

Finally I found out that

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

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

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

Answer 2

Change the relation from @OneToOne to @OneToMany and use fetch instead of join , it will execute only one query and hopefully it works.

 Join<Transaction, ResponseCode> join =
        (Join<Transaction,ResponseCode>)transaction.fetch("rc",JoinType.LEFT);

and you can try it with @OneToOne too.

Answer 3

You have mapped a single ResponseCode entity in Transaction, which is wrong. The response code is not a PK, it does not uniquely identifies a ResponseCode entity for a given Transaction entity. E.g. for a transaction with response code 000 there are 2 ResponseCode entities (with 'en' and 'ge' langs).

I recommend you to try to map a collection instead.

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name="rc")
private List<ResponseCode> rcList;

Since your query WHERE conditions apply to the Transaction only, you can simply query the Transaction entities. Hibernate cache will optimize the eventual subqueries you need for each response code (one query for '000', one for '116', etc).

Answer 4

Your mapping of both entities is wrong.

Let's start with the ResponseCode entity. Your table model shows a composite primary key that consists of the RcCode and Lang columns. But your entity mapping only declares the rcCode attribute as the primary key. You need to add an additional @Id annotation to the rcLang attribute of the ResponseCode entity.

This should be the fixed mapping of the 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;

    @Id
    @Column(name = "rc_lang")
    private String rcLang;

    // Contructors and getters/setters
}

After fixing the primary key of your ReponseCode entity, you need to reference both attributes/columns in the association mapping of your Transaction entity. With Hibernate 5.2, you can do that with 2 of Hibernate's @JoinColumn annotations. Older Hibernate versions and the JPA standard in version 2.1 need to wrap these annotations in an additional @JoinColumns annotation.

Here is the fixed mapping of your 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_id", referencedColumnName = "id")
    @JoinColumn(name="rc_lang", referencedColumnName = "lang")
    private ResponseCode rc;

    // Contructos and getters/setters

Answer 5

You need to change your mapping. rcCode cannot be an identifier because it does not identify records uniquely. I think that it will bring a lot of problems. ResponseCode must have a different identifier.

@OneToOne means one to one. You have one transaction, one response code but many languages.

You can mapping (@OneToOne) a connection between a Transaction and a ResponseCode with a specific language (through a composite key).

You can use @OneToMany, but usually in that case the reference should be from the ResponseCode table to the Transaction table.

But maybe you need 3 tables: transactions, response codes (with the code itself and its general information), response codes localizations (with messages on different languages). transactions one-to-one response_codes, response_codes one-to-many rc_localizations.

Or maybe you do not need hibernate-relation between Transaction and ResponseCode.

public class Transaction implements java.io.Serializable {
...
    @Column(name="rc")
    private String rc;
...
}

You can select the necessary ResponseCode by code and language. Two selects: 1 - select Transaction (with String rc-code); 2 - select ResponseCode (with the necessary language) by rc-code from Transaction.