How to set the timeout period on a JPA EntityManager query

Question

I'm currently getting connection timeout errors from my EntityManager queries. Is it possible to set a timeout for these?

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="CallPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>call.structure.Task</class>
    <class>call.structure.Installation</class>
    <class>call.structure.Contents</class>
    <class>call.structure.Recipient</class>
    <class>call.structure.CallTask</class>
    <class>call.structure.SmsTask</class>
    <class>call.structure.EmailTask</class>
    <class>call.security.User</class>
    <class>call.structure.content.Content</class>
    <class>call.structure.content.RecordContent</class>
    <class>call.structure.content.WaitContent</class>
    <class>call.structure.content.TextContent</class>
    <class>call.structure.content.VariableContent</class>
    <class>call.structure.content.SoundContent</class>
    <class>call.structure.content.SubjectContent</class>
    <class>call.structure.content.FileContent</class>
    <class>call.structure.Bounce</class>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@127.0.0.1:1521:TEST"/>
      <property name="javax.persistence.jdbc.password" value="userpassword"/>
      <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
      <property name="javax.persistence.jdbc.user" value="username"/>
    </properties>
   </persistence-unit>
</persistence>

Code times out in the run function of my thread:

private class TaskDB extends Thread {

    private final long WAITING_TIME = 20000L;

    @Override
    public void run() {

        Set<SmsTask> remove = SMSManager.this.getRemoveTask();
        Set<SmsTask> normal = SMSManager.this.getNormalTask();

        try {
            while(true){
                EntityManager em = DB.getEM();  //Calls EntityManagerFactory.createEntityManager()
                em.getTransaction().begin();

                Set<SmsTask> normalClone = new HashSet<SmsTask>(normal);
                // Abort task in futur.
                List<SmsTask> taskToRemove = new ArrayList<SmsTask>();
                if (!remove.isEmpty()) {

                    String queryString = "SELECT t FROM SmsTask t WHERE t.id IN :remove ";
                    if (!normalClone.isEmpty())
                        queryString += "AND t.id NOT IN :normal ";

                    Query query = em.createQuery(queryString);
                    query.setParameter("remove", Utils.taskToIdList(remove));
                    if (!normalClone.isEmpty())
                        query.setParameter("normal", Utils.taskToIdList(normalClone));

                    taskToRemove = (List<SmsTask>) query.getResultList();
                    for (SmsTask task : taskToRemove) {
                        removedTask.add(task);
                        remove.remove(task);
                    }
                }

                String queryString = "SELECT t FROM SmsTask t WHERE (t.scheduleTime IS NULL OR t.scheduleTime < :dateNow) AND t.status = co.dium.call.structure.Task.StatusTask.NOT_START ";
                if (!taskToRemove.isEmpty())
                    queryString += "AND t.id NOT IN :toRemove ";

                Query query = em.createQuery(queryString);
                query.setParameter("dateNow", Utils.obtainUniversalTime());
                if (!taskToRemove.isEmpty())
                    query.setParameter("toRemove", Utils.taskToIdList(taskToRemove));
                List<SmsTask> taskResults = (List<SmsTask>) query.getResultList();

                em.getTransaction().commit();

                for (SmsTask task : taskResults) 
                    addTask(task);

                SMSManager.TaskRemove.sleep(WAITING_TIME);
            }
        } catch (InterruptedException ex) {
            Logger.getLogger(SMSManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        System.out.println("Thread interrompu !");
        Thread.currentThread().interrupt();
    }
}

The timeout errors that I get:

org.clipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLRecoverableException: I/O error: Socket read timed out
Error Code: 17002
Call: [....sql query...]
[...]
at org.eclipse.persistence.internal.EJBQueryImpl.getResultList(EJBQueryImpl.java:742)
at call.manager.sms.SMSManager$TaskDB.run(SMSManager.java:367)
Caused by: java.sql.SQLRecoverableException: I/O Error: Scoket read timed out
[...]

score 26 · Accepted Answer · answered Jun 16 '14 at 18:26

26

Yes, there javax.persistence.query.timeout. According JPA 2.0 specification support for this query hint is optional:

Portable applications should not rely on this hint. Depending on the persistence provider and database in use, the hint may or may not be observed.

Default value (in milliseconds) can be set to persistence.xml for all queries:

<property name="javax.persistence.query.timeout" value="1000"/>

Same property can be also given when creating EntityManagerFactory via Persistence.createEntityManagerFactory.

It can also be overridden/set per query:

query.setHint("javax.persistence.query.timeout", 2000);

Same functionality can be obtained via attribute hints in NamedQuery.

answered Jun 16 '14 at 18:26

Mikko Maunu

41,366
10
132
135

seems the firewall was dropping the connection on long connections I needed to add to my persistence.xml: javax.persistence.query.timeout (10000) oracle.jdbc.ReadTimeout (40000) and (ENABLE=broken) in my tnsnames.ora as well as modify the KeepAliveInterval and the KeepAliveTime in the windows registry – James Jun 17 '14 at 19:02
4

Though its an old question,I have similar issue while calling entitymanager.persist(). How can I set timeout property in such a case? – Mandroid Jan 02 '16 at 19:06
I also have same question, is there an easy way to set timeout on entitymanager.persist(). So far it seems the only option is to convert this to native insert sql statement. – Abdel-Rahman Tawakol Mar 26 '23 at 22:54

Vlad Mihalcea · Answer 2 · 2021-01-10T10:46:41.287

There are two ways you can set up the query timeout period with Hibernate.

The Hibernate-specific way

If you are bootstrapping Hibernate natively or if you are unwrapping the JPA java.persistence.Query to its org.hibernate.query.Query equivalent, then you can just use the setTimeout method:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.unwrap(org.hibernate.query.Query.class)
.setTimeout(1)
.getResultList();

Notice that the setTimeout method takes an int argument which specifies the timeout value in seconds.

The JPA query hint way

You can also use a JPA query hint, as illustrated in the following example:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.setHint("javax.persistence.query.timeout", 50)
.getResultList();

Notice that the javax.persistence.query.timeout query hint takes the timeout value in milliseconds.

The Hibernate query hint way

You can also use the org.hibernate.timeout query hint:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where lower(p.title) like lower(:titlePattern)", Post.class)
.setParameter("titlePattern", "%Hibernate%")
.setHint("org.hibernate.timeout", 1)
.getResultList();

Notice that the org.hibernate.timeout query hint takes the timeout value in seconds.

score 0 · Answer 3 · answered Mar 03 '20 at 14:54

0

Instead of using the query hint 'javax.persistence.query.timeout', you should use 'javax.persistence.lock.timeout'.

In the latter case, if the query does not return within the specified milliseconds you'll receive a CannotAcquireLockException. In the first case, if the transaction does not finish within the specified milliseconds, you'll receive a QueryTimeoutException and the transaction will be rolled back.

answered Mar 03 '20 at 14:54

Stefan

147
1
11

To elaborate, if you use `javax.persistence.query.timeout` you'll need to finish the complete transaction within the specified amount of milliseconds. If you use `javax.persistence.lock.timeout` only the lock should be acquired within the specified amount of milliseconds. – Stefan Mar 04 '20 at 07:25

How to set the timeout period on a JPA EntityManager query

3 Answers3

The Hibernate-specific way

The JPA query hint way

The Hibernate query hint way

Linked

Related