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?
