CODEWITHSUNDEEP
javapostgresqlhibernatequerydsl
John
John

Reputation: 708

Hibernate is not responding while querying

How to handle if Hibernate queries take too long to return the result. I have already configured a query time out but on debugging it shows that the DB is responding by returning data, but hibernate fails to map the given data.

I do not want this scenario to happen in production, because my query might fail since the hibernate is not responding back.

I need a solution to come out from this scenario.

setProperty("javax.persistence.query.timeout", 180000);

JPAQuery query = queryFactory.select(....)
do{
   List<Tuple> data = query.fetch().limit(5000);
   //--------
} while(flag)

The above code works fine with data which are less in size, but for some data sets/ conditions the data is huge and eventually hibernate is not responding.

Upvotes: 1

Views: 897

Answers (4)

John
John

Reputation: 708

Finally i couldn't find any direct way to get control over a hibernate query call. The time out was not working for me since the Postgres already returned the result set but hibernate was taking time in mapping (correct me if i am wrong) due to data size.

Following piece of code saved me.

ExecutorService executor = Executors.newSingleThreadExecutor();
List<Future<List<Tuple>>> futureData = executor.invokeAll(Arrays.asList(new QueryService(params...)), 2, TimeUnit.MINUTES);
executor.shutdown();
for (Future future : futureData) {
    try {
         data = (List<Tuple>) future.get();
         } catch (CancellationException e) {
                 if (EXPORT_LIMIT > 1000) {
                            EXPORT_LIMIT = 1000;
                        } else if (EXPORT_LIMIT > 500) {
                            EXPORT_LIMIT = 500;
                        } else if (EXPORT_LIMIT > 100) {
                            EXPORT_LIMIT = 100;
                        } else {
                            throw e;
                        }
                        isValid = false;
                        break;
                    }
                }

So basically my default fetch limit of 5000 if not working, then i keep trying till 100. If fetch size of 100 is also failing an exception will be thrown.

Thank you.

Upvotes: 0

Ramandeep Kaur
Ramandeep Kaur

Reputation: 111

1) For setting the timeout in Hibernate query you can set hint "javax.persistence.query.timeout"


Code snippet ::

List<Test> test= em.createQuery("SELECT * FROM Test t")
    .setHint("javax.persistence.query.timeout", 1)
    .getResultList();

2) In case 2 columns are containing large data ,you can use CLOB and BLOB types for huge dataset.

Upvotes: 1

Leviand
Leviand

Reputation: 2805

Based on your last comment, you are looking for a way to manage a timeout for certains queries.

You can achieve this while creating your org.hibernate.Query with Hibernate:

Query queryObject = //initialize your query as you need;
queryObject.setTimeout(10); //that int represents the seconds of timeouts.

Hope this helps

Upvotes: 0

ideano1
ideano1

Reputation: 150

Try to follow these steps, if

  • Use Lazy Fetching instead of Eager Fetching like @ManyToMany(mappedBy="authors", fetch=FetchType.LAZY)

  • Or May be check if any of these Mistakes

  • You are using HibernateDaoSupport.getSession(), without ever returning them using releaseSession() (as described in the javadocs).

a) use HibernateDaoSupport.getHibernateTemplate() to cleanly create/destroy sessions b) use getSession()/releaseSession() in a finally block c) forget about HibernateDaoSupport, define transactions and use sessionFactory.getCurrentSession()

  • use, session.refresh(entity) or entityManager.refresh(entity) (if you use JPA) will give you fresh data from DB.

Upvotes: 1

Related Questions