3

I'm trying to figure out how to configure my project such that JPA will timeout and throw an exception after a configured amount of time. There are two situations where I would like this to happen:

  • When JPA is unable to even connect to the database
  • When a JPA query takes longer than the timeout threshold to return a result set

I'm not sure if these two scenarios can be configured separately (a different timeout threshold for each), or if one threshold is used for both.

My project is currently set up as follows:

  • Coding to the JPA 2.0 specification
  • Using Hibernate 3.5.6 as the JPA implementation
  • Using c3p0 connection pooling with Hibernate
  • Using persistence.xml configuration file (using Hibernate-specific property values only when necessary)
  • NOT using any Hibernate-specific configuration files
Jim Tough
  • 14,843
  • 23
  • 75
  • 96

3 Answers3

7

JPA2 persistence property "javax.persistence.query.timeout" allows you to set the timeout for a query (assuming the underlying JDBC driver supports it).

DataNucleus
  • 15,497
  • 3
  • 32
  • 37
  • +1 for the heads-up on this property. Unfortunately it doesn't seem to work with the Oracle JDBC driver that comes in the ojdbc14.jar file. I can't get a newer driver yet because the project is using an Oracle 10g server and will not be upgrading for a while. – Jim Tough Oct 05 '10 at 16:40
1

You should be setting both Java client as well as database server default timeouts: If you were using Spring, use @Transactional(timeout=10) which eventually sets the preparedStatement.setQueryTimeout() hint to close the transaction within 10 seconds. Also, enable your server to timeout slightly above the client timeout so "java client user" to timeout, say, in 15 s: https://mariadb.com/kb/en/mariadb/query-limits-and-timeouts/

kisna
  • 2,869
  • 1
  • 25
  • 30
  • Ofcourse, for your stack with Spring+Hibernate, you will have to add 1 more second to your timeout to avoid this bug: https://hibernate.atlassian.net/browse/HHH-9482 – kisna Apr 27 '16 at 23:59
0

This page on the Hibernate wiki details how to configure the c3p0 connection pool, including the timeout settings.

Note that these type of details really don't have much to do with JPA or Hibernate, but are settings that you set on the DataSource / database connection (c3p0 in this case) itself.

matt b
  • 138,234
  • 66
  • 282
  • 345
  • So this seems to answer my question about the connection timeout, but I still don't understand how I would set a timeout for a query. When you say "DataSource", are you referring to something at the JDBC layer? How would I access that from JPA? – Jim Tough Oct 05 '10 at 16:17
  • Correct me if I am wrong but C3P0 will just pool connections, and you still need to configure the underlying datasource - is this right? If so what I am referring to is that you need to configure the underlying JDBC driver for timeouts, both to acquire a connection and for individual queries – matt b Oct 05 '10 at 16:25
  • The only connection properties that I've needed to set are the driver class (oracle.jdbc.OracleDriver), the connection URL (jdbc:oracle:thin:@myhost:1521:ORCL) and the user and password. Those are all done via 4 standard JPA 2 properties in the persistence.xml file. I wouldn't know where to go if I wanted to try configuring JDBC stuff. – Jim Tough Oct 05 '10 at 16:37