JPA(Toplink Essentials) using JTA on Glassfish: Issue In Loading References

[Adeel Ansari]

Consider this log.

[#|2010-01-05T12:26:14.000+0800|FINE|sun-appserver9.1|oracle.toplink.essentials.session.file:
/software/SUN/appserver/current/domains/domain1/applications/j2ee-apps/something-ear-0.10SIT/somthing-0.10SIT_jar/-some.sql
|_ThreadID=283;_ThreadName=p: thread-pool-1; w: 489;ClassName=null;MethodName=null;_RequestID=c061d8c8-b6f4-4bf1-bf07-d8a158dbf4bf;|

SELECT t0.ID, t0.TRANSACTION_INDICATOR, t0.ACCOUNT_NUMBER, t0.BENEFICIARY_ID, t0.LAST_MODIFIED, t0.CURRENCY_CODE, t0.TRANSACTION_MODE,
 t0.STATUS_CODE, t0.PROCESSING_TIMESTAMP, t0.STATUS_DESCRIPTION, t0.MODIFIER_ID, t0.TRANSACTION_DATE, t0.TRANSACTION_AMOUNT 
FROM MW_TRANSACTION_DETAILS t0, MW_REFERENCE_KEEPERS t2, MW_TRANSACTION_REQUESTS t1 WHERE ((((t1.TRANSACTION_STATUS = ?) 
AND NOT ((t0.STATUS_CODE IS NULL))) AND (t0.TRANSACTION_INDICATOR = ?)) AND ((t2.MW_TXN_DETAIL_ID = t0.ID) 
AND (t1.ID = t2.MW_TXN_REQUEST_ID))) FOR UPDATE
        bind => [PENDING, DR]|#]
    
[#|2010-01-05T12:26:14.000+0800|FINE|sun-appserver9.1|oracle.toplink.essentials.session.file:
/software/SUN/appserver/current/domains/domain1/applications/j2ee-apps/something-ear-0.10SIT/somthing-0.10SIT_jar/-some.sql
|_ThreadID=283;_ThreadName=p: thread-pool-1; w: 489;ClassName=null;MethodName=null;_RequestID=c061d8c8-b6f4-4bf1-bf07-d8a158dbf4bf;|

SELECT ID, MWARE_REF_NUM, HOST_REF_NUM, MODIFIER_ID, LAST_MODIFIED, MW_TXN_REQUEST_ID, MW_TXN_DETAIL_ID 
FROM MW_REFERENCE_KEEPERS WHERE (MW_TXN_DETAIL_ID = ?)
        bind => [3bdac813d6034a26b336804794fb667f]|#]

Now, the first query, stated in the log, is giving me 5 records.

    t0.ID (Omitting other columns for brevity.)
    --------------------------------
    3bdac813d6034a26b336804794fb667f
    d60c6c41a3a74103818ad33e2f037168
    8babfd4383764b2a8ba9d76a1bdb33b3
    0c413d954dd84da0b64f3346b51a9ab3
    c120e0ded4154f038d3d41b99fff86fd

And for some stupid reason its not retrieving anything with the second query in the log when its binding value, `3bdac813d6034a26b336804794fb667f`. I expect one record, of course. For other 4 values I am getting things perfectly fine, just not getting for the first one. I omitted the log for other values, by the way. And yes, its not particularly happens to the first record always. The behaviour is arbitrary sometimes happens, sometimes not, and could happen to any record.

To make it clear explicitly, the first query is `FOR UPDATE`, all others are just loading relations.

Now the code snippet.

    public List<DebitTxnDetail> findPendingTxns(){
        Query query = em.createNamedQuery(TxnDetail.GET_PENDING_TXNS);
        query.setParameter("txnStatus", TxnRequestStatus.PENDING);

        List<DebitTxnDetail> details = query.getResultList();
        for(DebitTxnDetail debitDetail : details){
            TxnRequest txnRequest = debitDetail.getRefKeeper().getTxnRequest();
            for(ReferenceKeeper ref : txnRequest.getRefKeepers()){
                TxnDetail txnDetail  = ref.getTxnDetail();
                if(txnDetail instanceof CreditTxnDetail){
                    CreditTxnDetail creditDetail = (CreditTxnDetail) txnDetail;
                    if(creditDetail.isBatchCredit()){
                        creditDetail.getTxnDetailAddendumRefs().size();
                    }
                }
            }
        }

        return details;
    }

In the code, I am trying to load references eagerly. Didn't put the `fetchType=FETCH_TYPE.EAGER` attribute, because don't always want that kinda behaviour. But in this case I need that, so doing it manually.

[Adeel Ansari]

I think, I have solved it. Will post the fix tomorrow, hopefully, after testing.

[Adeel Ansari]

I think no I didn't find anything. By some reason its not loading OneToOne, and a ManyToOne relationship for one of the object. That I expect it to load eagerly.

[James Sutherland]

Are you sure the data is in the database? Ty executing the SQL directly through JDBC, do you get the expected result?

Perhaps turn logging on finest, or try to debug.

[Adeel Ansari]

Thanks James, for your input.

I just finished up fixing this. Actually, I noticed the data which existed before I start my Glassfish, doesn't have any issue in loading. But when I persist some fresh entities, and try to load that along with all its eager fetch associations. The associations were being picked up from cache, I believe. Because problem doesn't persist after I restart the Glassfish, and invoke my bean. For fresh entities its always the same, not getting loaded.

Since, my actual query is FOR UPDATE, that really picks all the records from database, but its associations are get loaded from cache if found. So, I tried to use TopLinkQueryHints.REFRESH=true, and TopLinkQueryHints.REFRESH_CASCADE=CascadePolicy.CascadeAllParts. That didn't work, no idea why. It should, IMO.

Now the fix. I manually called `em.referesh()` on that particular entity, which was being picked up from cache. That solved my issue. Below is the modified method,

    public List<DebitTxnDetail> findPendingTxns(){
        Query query = em.createNamedQuery(TxnDetail.GET_PENDING_TXNS);
        query.setParameter("txnStatus", TxnRequestStatus.PENDING);

        List<DebitTxnDetail> details = query.getResultList();
        for(DebitTxnDetail debitDetail : details){
            TxnRequest txnRequest = debitDetail.getRefKeeper().getTxnRequest();

            //Below line has been added to the original code
            em.refresh(txnRequest);

            for(ReferenceKeeper ref : txnRequest.getRefKeepers()){
                TxnDetail txnDetail  = ref.getTxnDetail();
                if(txnDetail instanceof CreditTxnDetail){
                    CreditTxnDetail creditDetail = (CreditTxnDetail) txnDetail;
                    if(creditDetail.isBatchCredit()){
                        creditDetail.getTxnDetailAddendumRefs().size();
                    }
                }
            }
        }

        return details;
    }