Database updated but select query not giving result in hibernate

Question

I am using JPA on top of Hibernate, and it is connected to MariaDb with TokuDb as Engine.

I am running a infinite loop and that loop is checking if the table has new Rows based on some flags, if new rows are found then process it.

But the problem happens when the loop is running if you insert data in table using different thread / mechanism, JPA query is not finding the result it is always giving back no new rows. The code is as follows

do {
        try {
            if (!em.isOpen()) {
                em = PersistenceLocal.getEntityManager();
            }
            TypedQuery<FileMaster> query = em.createQuery("SELECT f FROM FileMaster f WHERE f.misUpdated = :misUpdated AND f.status = :status", FileMaster.class);
            query.setParameter("misUpdated", false);
            query.setParameter("status", "sent");
            query.set
            List<FileMaster> result = query.getResultList();
            if (result.size() > 0) {

                EntityTransaction tx = em.getTransaction();
                tx.begin();
                for (FileMaster fm : result) {

                    SimpleDateFormat dateFormat = new SimpleDateFormat("YYYY-MM-dd");
                    String dt = dateFormat.format(fm.getSheduleDate());

                    // find out what is the uid in file master
                    TypedQuery<Misthird> misthird = em.createNamedQuery("Misthird.findByPrimarKey", Misthird.class);

                    misthird.setParameter("uid", Integer.valueOf(fm.getCrn()));
                    misthird.setParameter("dt", fm.getSheduleDate(), TemporalType.DATE);
                    Misthird m;
                    try {
                        m = misthird.getSingleResult();
                    } catch (Exception ex) {
                        m = new Misthird(Integer.valueOf(fm.getCrn()), fm.getSheduleDate());
                    }
                    Calendar c = Calendar.getInstance();
                    c.setTime(fm.getSheduleDate());
                    //System.out.println("Scheduled Date = "+fm.getSheduleDate());
                    int hr = c.get(Calendar.HOUR_OF_DAY);
                    System.out.println("Hour = " + hr);
                    switch (hr) {
                        case 0:
                            m.setHr(m.getHr() + fm.getTRecord());
                            break;
                        case 1:
                            m.setHr1(m.getHr1() + fm.getTRecord());
                            break;
                        case 2:
                            m.setHr2(m.getHr2() + fm.getTRecord());
                            break;
                        case 3:
                            m.setHr3(m.getHr3() + fm.getTRecord());
                            break;
                        case 4:
                            m.setHr4(m.getHr4() + fm.getTRecord());
                            break;
                        case 5:
                            m.setHr5(m.getHr5() + fm.getTRecord());
                            break;
                        case 6:
                            m.setHr6(m.getHr6() + fm.getTRecord());
                            break;
                        case 7:
                            m.setHr7(m.getHr7() + fm.getTRecord());
                            break;
                        case 8:
                            m.setHr8(m.getHr8() + fm.getTRecord());
                            break;
                        case 9:
                            m.setHr9(m.getHr9() + fm.getTRecord());
                            break;
                        case 10:
                            m.setHr10(m.getHr10() + fm.getTRecord());
                            break;
                        case 11:
                            m.setHr11(m.getHr11() + fm.getTRecord());
                            break;
                        case 12:
                            m.setHr12(m.getHr12() + fm.getTRecord());
                            break;
                        case 13:
                            m.setHr13(m.getHr13() + fm.getTRecord());
                            break;
                        case 14:
                            m.setHr14(m.getHr14() + fm.getTRecord());
                            break;
                        case 15:
                            m.setHr15(m.getHr15() + fm.getTRecord());
                            break;
                        case 16:
                            m.setHr16(m.getHr16() + fm.getTRecord());
                            break;
                        case 17:
                            m.setHr17(m.getHr17() + fm.getTRecord());
                            break;
                        case 18:
                            m.setHr18(m.getHr18() + fm.getTRecord());
                            break;
                        case 19:
                            m.setHr19(m.getHr19() + fm.getTRecord());
                            break;
                        case 20:
                            m.setHr20(m.getHr20() + fm.getTRecord());
                            break;
                        case 21:
                            m.setHr21(m.getHr21() + fm.getTRecord());
                            break;
                        case 22:
                            m.setHr22(m.getHr22() + fm.getTRecord());
                            break;
                        case 23:
                            m.setHr23(m.getHr23() + fm.getTRecord());
                            break;
                    }
                    // update total records 
                    m.setTot(m.getTot() + fm.getTRecord());
                    System.out.println("Total = " + m.getTot());
                    if (em.contains(m)) {
                        em.persist(m);
                    } else {
                        m = em.merge(m);
                        em.persist(m);
                    }
                    // create a new table for fileid and misupdated status, use trigger and keep filemaster free.

                    if (!em.contains(fm)) {
                        fm = em.merge(fm);
                    }

                    fm.setMisUpdated(true);
                    em.persist(fm);
                }

                System.out.println("MIS updated and Transaction Commited " + new Date());

                tx.commit();
            }

        } catch (Exception ex) {

            ex.printStackTrace();
        } finally {
            try {
                em.close();
                Thread.sleep(1000);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    } while (cron_mode);
}

SQL

Hibernate: select filemaster0_.FileID as FileID1_3_, filemaster0_.after_rev as after_re2_3_, filemaster0_.apx_priotity as apx_prio3_3_, filemaster0_.befor_rev as befor_re4_3_, filemaster0_.cancel_date as cancel_d5_3_, filemaster0_.cp as cp6_3_, filemaster0_.credit as credit7_3_, filemaster0_.CRN as CRN8_3_, filemaster0_.cut as cut9_3_, filemaster0_.dnd_Checked as dnd_Che10_3_, filemaster0_.download_status as downloa11_3_, filemaster0_.FileName as FileNam12_3_, filemaster0_.gateway as gateway13_3_, filemaster0_.is_credit_revers as is_cred14_3_, filemaster0_.is_credit_reversed as is_cred15_3_, filemaster0_.mis_updated as mis_upd16_3_, filemaster0_.modem_distibute as modem_d17_3_, filemaster0_.msg as msg18_3_, filemaster0_.pid as pid19_3_, filemaster0_.priority as priorit20_3_, filemaster0_.sender_id as sender_21_3_, filemaster0_.shedule_date as shedule22_3_, filemaster0_.signature as signatu23_3_, filemaster0_.Status as Status24_3_, filemaster0_.t_record as t_recor25_3_, filemaster0_.t_reversal as t_rever26_3_, filemaster0_.type as type27_3_, filemaster0_.unicode as unicode28_3_, filemaster0_.UploadDate as UploadD29_3_ from file_master filemaster0_ where filemaster0_.mis_updated=? and filemaster0_.Status=?

transaction of the insert not yet committed so the connection of the query has an isolation that prevents reading uncommitted data? — Neil Stockton, Jan 04 '16 at 18:46
insert is done through a different process and yes the data is present in table. — rxx, Jan 04 '16 at 18:47
the query at line 6 is always giving result 0 except for first time. — rxx, Jan 04 '16 at 18:53
yes, and what is the SQL? and its connection was created BEFORE the insert was committed?? — Neil Stockton, Jan 04 '16 at 18:54
yes connection was created before insert. And sql was insert into my_table values(); Actually insert is happening through php and the above java code is replacement for a cron job. — rxx, Jan 04 '16 at 19:00
SELECT f FROM FileMaster f WHERE f.misUpdated = false AND f.status = 'sending' , this is the query running repeatedly , every second — rxx, Jan 04 '16 at 19:13
That is JPQL, not SQL. And as already said, your connection doesn't see things that have been committed afterwards. So try getting a connection (i.e starting a txn) just before your query — Neil Stockton, Jan 04 '16 at 19:16
Updated SQL in the main post as length was not permissible for the comment. — rxx, Jan 04 '16 at 19:25
I will start the transaction before query and give results tomorrow. — rxx, Jan 04 '16 at 19:40
well, its either the transaction, or the EntityManager ... depends when your JPA provider allocates a connection. — Neil Stockton, Jan 04 '16 at 19:42
The problem is that you your em is allready open, and most probably has some cached objects inside it. After you check if the em.connectionopen, do a em.clear(). — Mihai, Jan 06 '16 at 10:50

score 0 · Answer 1 · answered Jan 06 '16 at 19:59

0

You can perform a session.flush() (in your case em.flush()) before the em.close() It will probably work.

answered Jan 06 '16 at 19:59

José Mendes

950
2
14
30

score 0 · Accepted Answer · answered Jan 10 '16 at 17:31

0

I used hints and clearing the entitymanager after my transaction is complete.

   query.setHint("javax.persistence.cache.retrieveMode", CacheStoreMode.BYPASS);
   query.setHint("javax.persistence.cache.storeMode", CacheStoreMode.BYPASS);

answered Jan 10 '16 at 17:31

rxx

744
6
16

Database updated but select query not giving result in hibernate

2 Answers2