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
[...]

Answer 1

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.

Answer 2

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.

Answer 3

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.