Re: Help with query timeout

Em 07/10/2012 02:46, Andreas Kretschmer escreveu:
> Edson Richter <edsonrichter@hotmail.com> wrote:
>
>> Dear friends,
>>
>> I'm using Java 1.6 with Jdbc 4 driver with PostgreSQL 9.1.5 on Windows
>> 64 and Linux 64.
>> Trying to use setQueryTimeout(int), I get the following stack trace:
>> Internal Exception: org.postgresql.util.PSQLException: Method
>> org.postgresql.jdbc4.Jdbc4PreparedStatement.setQueryTimeout(int) not yet
>> implemented.
>> (free translation of the equivalent Portuguese message).
>>
>> My question is: if setQueryTimeout is not implemented, how can I set a
>> query timeout parameter (per query or per session) using PostgreSQL? Is
>> there any "set ..." or connection parameter I can use?
> Yeah:
>
> statement_timeout
>
>
>
> Andreas
Yes, I see.

But there is a little problem then: I'm using JPA. And I can only obtain
a java.sql.Connection from inside a transaction (JPA 2 limitation).

I managed to make it works, and I would like to share with the community:

I've a BrokerUtil class with several JPA utility methods. I've one
method to get EntityManager, and one to close EntityManager.
Then I've created the method setTimeOut and resetTimeout as:

------------------------------------------------------------------------------------
   public static void setTimeout(EntityManager em, int segundos) throws
SQLException {
     EntityTransaction et = em.getTransaction();

     if(!et.isActive()) {
       et.begin();
     }

     java.sql.Connection cn = em.unwrap(java.sql.Connection.class);
     cn.createStatement().execute("SET statement_timeout TO
"+(segundos*1000));
   }

   public static void resetTimeout(EntityManager em) {
     try {
       java.sql.Connection cn = em.unwrap(java.sql.Connection.class);
       cn.createStatement().execute("RESET statement_timeout");
     } catch (Exception e) {
       Util.debug(e);
     }
   }
------------------------------------------------------------------------------------

In my application, before issuing the query, I just call

BrokerUtil.setTimeout(em, 60); // set query timeout to 60 seconds

and before closing the EntityManager (or better, inside method that
closes EntityManager), just call:

BrokerUtil.resetTimeout(em); // reset to default configuration before
closing


I hope this helps others in the future. This would go to PostgreSQL
Wiki, since is solves the missing part of Jdbc timeout.

Regards,

Edson