get relation information via jdbc

[stefanwagner]

I like to know, whether it's possible to get relation-informations from a database, how a table is joined (semantically) with another.

Example (* indicating primary key):

orders: order_id *, order_date, ...
order_items: order_id, line_item_id, product_id, ...
product_information: product_id*, product name, product_description, ...

A user (not a dba) uses a program, and needs informations from orders and product_description.
He's not a teached in databases, and the program shall find the relation between orders and product_description on it's own.

Is it possible with sql?
With jdbc?

In this special case, the naming is helpfull - order_id and product_id use the same name in the table, where they are primary key, as well as where they are foreign key.

I know how to get all table- and column-names with metadata, and of course I could try to search for the shortest connection via columnnames between two tables.

But this can be a lot of work for a huge database (reminds me of the travelling-salesman-problem).
It might lead to wrong results, when columnnames match unintentionally.
It might lead to no result, when the creator of the tables didn't use such naming-conventions.

Are such naming conventions very common in the sql-field?

(preferred: a hint for a common solution over a special oracle-solution over something else).

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[haslo]

What we're doing in a project I'm working on right now is, we have two special tables in our database, one being "tables" and one being "fields", and those two tables hold all the metadata we need. But that is something we had planned from the very start.

You're planning to develop in Oracle, right? Does the documentation (for which I unfortunately don't have access without an Oracle account) not mention something about constraint metadata? I guess since Oracle is such a mighty DBMS, you might be lucky - and most JDBC drivers allow arbitrary SQL statements...

Good luck!

haslo@haslo.ch - www.haslo.ch​

 

[stefanwagner]

Primarly for oracle, but it would be nice, it the solution is almost db-vendor-independent.

And it should work on most probable customer-dbs, if possible, so I may not create my own metadata-tables before.
I may create them, to have the user specify the relation only once, and update it, when necessary.

I found

java.sql.Metadata.getPrimaryKeys(String catalog, String schema, String table)

and find out, how to work with that. (overseen by my first search).

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[stefanwagner]

Well - by a second look at the javadocs I found the solution:

DatabaseMetaData dbmd = connection.getMetaData();
ResultSet rs = dbmd.getImportedKeys (catalog, schema, table);

When I was teached SQL, you created tables with 'non null' and made a 'unique index' after that, to create a primary key, and used triggers in forms to implement autoincrement.
Nowadays, the keyword 'primary key' seems to be common (unfortunately differing in syntax from db to db), and then you can get the info directly from the database.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung