How to generically test a database connection with hibernate

Question

I have a service method on an api that can be called to check the health of my database connection.

The method is pulling the query string from a properties file (depends on DB vendor, using Sybase and HSQL for now, more in future), and executing it. Then the method lets the caller know if it succeeded or failed.

In addition to this, I was using the Query.setHint("javax.persistence.query.timeout") to set a timeout on the query:

javax.persistence.EntityManager entityManager;
...
Query heartbeatQuery = entityManager.createNativeQuery(heartbeatQueryString);
heartbeatQuery.setHint("javax.persistence.query.timeout", heartbeatTimeout);
heartbeatQuery.getResultList();

My problem is the timeout property is working against my Sybase DB, but not against my HSQL DB. It sounds like it depends on the vendor, so I don't know for sure when it will work.

Is there a better way to generically test the DB connection & include some kind of timeout parameter?

Shivan Dragon · Accepted Answer · 2012-01-13 22:03:25Z

1

Well sadly no. JPA's query hints are not mandatory, i.e. it's up to the implementator (EclipseLink, Hibernate, etc) to enforce them or not. Moreover, even if the implementator does chose to recognize a certain query hint, if that hint's functionality is not supported by the database then it won't work (here some implementators are nice and tell you if a certain hint won't work agains the current db while others fail silently). In the case of HSQLDB there's no way to set the query timeout. You can only set a timeout for the login (i.e. how long should it wait for a successful login before failing), but not for the queries duration.

Things are not so grim however. On the one hand, even if you'd solve this, you'd still stumble over other issues with HSQLDB, as it does not support a lot of other nice functionalities that most dbs have. You should only use HSQLDB for basic integration/unit testing. For more involved testing, you can use the integrated MySQL Java library. You can find it here:

http://dev.mysql.com/doc/refman/5.0/en/connector-mxj.html

This is simply a packaged fully working Mysql server, which has a Java api for star and stop, works on most major OSs (win,lin, os x, etc). This way you can have your integration tests start a real Mysql server, and try your code there, where such stuff as a query timeout hint will work fine.

answered Jan 13, 2012 at 22:03

Shivan Dragon

15.2k10 gold badges69 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gregdferrell Over a year ago

Thank you for the explanation. I feared that may be the case, however this is not a major problem for me. If there were a better way for Hibernate users to test a DB connection, I'd be interested in hearing it.I did not know about the MySQL Java library -that is interesting and I'll take a look at it.

Collectives™ on Stack Overflow

How to generically test a database connection with hibernate

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related