Re: Help with query timeout

Em 07/10/2012 02:46, Andreas Kretschmer escreveu:
> Edson Richter <edsonrichter(at)hotmail(dot)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