how can one search for a token in the entire database?

Assuming you have 5 tables all have the same table structure you could code a single SQL statement like this:

SELECT * FROM TABLE1 WHERE COL1 = 'Hong Kong'

UNION ALL

SELECT * FROM TABLE2 WHERE COL1 = 'Hong Kong'

UNION ALL

SELECT * FROM TABLE3 WHERE COL1 = 'Hong Kong'

UNION ALL

SELECT * FROM TABLE4 WHERE COL1 = 'Hong Kong'

UNION ALL

SELECT * FROM TABLE5 WHERE COL1 = 'Hong Kong'

If you are concerned about searching all 5 columns, you can code the SQL like this and stil use UNION ALL.

SELECT * FROM TABLE3 WHERE COL1 = 'Hong Kong'

OR COL2 = 'Hong Kong'

OR COL3 = 'Hong Kong'

OR COL4 = 'Hong Kong'

OR COL5 = 'Hong Kong'

WorkForFooda at 2007-7-11 23:42:02 >

> so what's the alternative?

Build a free-text index on the data as it goes in in the first place:

http://lucene.apache.org/java/docs/

If you've got a pre-existing database that you have to free-text index, then write a tool to build free-text indexes from it using Lucene, and run it as a batch job. That's very much *not* a preferable approach though.

dcmintera at 2007-7-11 23:42:02 >

> well you can select table_name from user_tables;

> and than iterate over the result

You are describing a DBMS specific solution which goes against most of the principles of using Java/JDBC. I described the correct way to do this, which is by using DatabaseMetaData which will work with most if not all vendors?databases. Your description is just an reiteration of what I stated except yours is specific to Oracle. There isn't anything in the OP's post that makes me think this is Oracle, but even if this were Oracle I would recommend the OP NOT implement the DBMS specific solution you are describing when the Java/JDBC API provides a strong vendor neutral solution using DatabaseMetaData.

WorkForFooda at 2007-7-11 23:42:02 >