2

By default for Oracle Databases validation query is "select 1 from dual". Tomcat says:

ValidationQuery - If specified, this query MUST be an SQL SELECT statement that returns at least one row.

Based on sentences above, can I modify a validation query that will be:

  1. nested or conditioned query
  2. from another table I specify
  3. this table has one row and multiple columns

I've read a lot of documentation, but didn't find any information to say that's impossible or at least incorrect.

Improve this question
1
  • 1
    you can, but why would you do that?
    – user1822
    Commented Dec 15, 2017 at 7:00

1 Answer 1

Reset to default
3

Yes, it can be any valid query - from the Tomcat JDBC documentation:

(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. If not specified, connections will be validation by the isValid() method. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)

You asked about using nested queries, different tables and multiple columns and so I give you something to think about:

The validationQuery is executed every time an application requests a connection from the pool. This is before the application issues it's own query - so at a minimum you issue two queries against the database. With that in mind, why would you want to make the query more complex? All you would do is add unnecessary load on the database server for no reason at all - AFAIK you cannot see the output of the validationQuery within a Java application because it happens at the container level before the connection is passed to the client.

Keep it simple and use one of the recommended queries.

Improve this answer
7
  • 3
    Nah... SELECT * FROM absolutely_huge_table; would be much better to check if the server's running! :-)
    – Vérace
    Commented Dec 15, 2017 at 7:48
  • Also to a-horse-with-no-name: because I want to solve the problem of application jdbc pool that can't be reset and gives me ORA exception when trying to get DB's packages that's being recompiling at the moment. Standard resource attributes like testOnBorrow="true" and validationQuery="select 1 from dual" didn't help.
    – Dmitresso
    Commented Dec 15, 2017 at 7:58
  • I want to create something like a simple one-row table with packages' states (determined as 0 or 1) get SELECT result by subqery (e.g. SUM) returned value with condition statements help. Do I understand correctly validationQuery returned value is table row content or null?
    – Dmitresso
    Commented Dec 15, 2017 at 7:59
  • You can could do something like: select my_validation_function from dual, where within "my_validation_function" you could conduct whatever probes, tests, etc you like
    – Connor McDonald
    Commented Dec 21, 2017 at 1:08
  • @Dmitresso I think it would make sense that you post your real problem in a new question.
    – miracle173
    Commented Jan 17, 2018 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.