8

Is there a library which can determine for a jdbc driver class the appropriate validation query to use?

For example If the jdbc driver is com.mysql.jdbc.Driver or com.mysql.jdbc.jdbc2.optional.MysqlXADataSource the library will know the validation query is "SELECT 1"

c3po http://www.mchange.com/projects/c3p0/index.html#preferredTestQuery

dbcp http://commons.apache.org/dbcp/configuration.html "validationQuery"

mikera
  • 105,238
  • 25
  • 256
  • 415
joshjdevl
  • 7,092
  • 12
  • 45
  • 57
  • What do you mean "validation query"? You mean checking if the connection is alive? – skaffman Feb 12 '11 at 14:31
  • 4
    most connection pools require the end user to set the validation query to use to test the connection. sql test queries are not portable. # for MySQL/PostgreSQL/MS SQL use: SELECT 1 # for Oracle use: SELECT 1 from dual # for DB2 use: SELECT 1 FROM sysibm.sysdummy1 what is the reason for the end user to worry about these things? and why should every programmer do the same thing? hence the library suggestion – joshjdevl Feb 12 '11 at 19:03
  • c3po has a nice concept - automaticTestTable. this idea can be expanded since scenarios may not allow the jdbc user to create tables. so, instead as we use liquibase, every schema has databasechangelog. we can then use SELECT * from databasechangelog. – joshjdevl Feb 12 '11 at 23:44
  • 1
    This answer can help you as well: http://stackoverflow.com/questions/10684244/dbcp-validationquery-for-different-databases And here is simple class which determine validation query according to jdbc driver class. [Blog - validationQuery for various Databases](http://vondrnotes.blogspot.cz/2012/05/validationquery-for-different-databases.html) – bugs_ Feb 18 '13 at 19:30

2 Answers2

11

JDBC type 4 provides an isValid method

joshjdevl
  • 7,092
  • 12
  • 45
  • 57
0

I don't think a library is the appropriate place for such a thing.

If you're using a connection pool, it's more likely that you'll ask it to check connections before handing them out. You'll simply enter the appropriate SQL and let the pool handle it. A library would be a step back, because it suggests that your code would be calling it.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • I think it's a step back because you're talking about an entire library, another dependency, for something that at its heart is trivially simple. Write "SELECT 1" and move on. – duffymo Feb 12 '11 at 19:17
  • 1
    Apparently they don't agree with you, since none of the pooling solutions you cite have such a thing. No one else seems to think it's that big a deal. Good luck on the crusade to convince them. No one else has bothered to answer, either. What does that say? – duffymo Feb 12 '11 at 21:19