views:

459

answers:

4

Does anyone know of a Java library that provides a useful abstraction for analyzing and manipulating arbitrary relational database schemata? I'm thinking of something that could do things like

LibraryClass dbLib = ...;
DbSchema schema = dbLib.getSchema("my_schema");
List<DbTable> tables = schema.getTables();

and

DbTable myTable  = ...
for(DbColumn col : myTable.getColumns()){
    ... = col.getType();
}

or even manipulate tables like

myTable.addColumn(
    new DbColumn("my_new_column", Type.UNSIGNED_INTEGER);
);

DbColumn myColumn = ...
myTable.removeColumn(myColumn);

Most Database modeling tools will have such an abstraction internally, but is there one in Java that I can use, or will I have to roll my own?

A: 

I haven't used it in years but Hibernate used to have tools for manipulating data models at build time. Hibernate also has the notion dialects which would be helpful if you're targeting more than one database vendor.

digitalsanctum
As far as I know, Hibernate only includes a tool to generate DDLs from existing mapping files. This unfortunately won't help in my case. Is this what you meant, or is there something else?Thanks!
Henning
+5  A: 

JDBC itself has such an abstraction. Look at java.sql.DatabaseMetaData. However, this is an optional part of the standard and it depends on the JDBC driver you are using wether it is implemented or not.

Gowri
I'm using MySQL Connector 3.1.10, which does implement java.sql.DatabaseMetaData. This is going to be very helpful as far as gathering and displaying table information is concerned. Thanks!
Henning
A: 

When I was at MetaMatrix, we built such a thing using EMF (Eclipse Modeling Framework) where we created a representational meta-model in UML, then generated it from code. The nice thing about metamodeling is that if you do it well, you can interoperate things across metamodels, provided you have made good choices against the meta-meta-model.

We also had an importer that would import metadata from the JDBC API and create the appropriate equivalent model objects (database, table, column, keys, etc).

This code might be open source some day since they got bought by JBoss but I don't think it is yet.

Alex Miller
+2  A: 

DdlUtils has what you're looking for. You can read/write schemas to/from XML (in Torque format) or a live database, or even define the database schema in pure Java. Better yet, read the on-line doco, it's quite good.

Andrew Swan