About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

Database schema navigation in Java

An important part of jOOQ is jooq-meta, the database schema navigation module. This is used by the code generator to discover relevant schema objects. I was asked several times why I rolled my own instead of using other libraries, such as SchemaCrawler or SchemaSpy, and indeed it’s a pity I cannot rely on other stable third party products.

Here are some thoughts on database schema navigation:

Standards

The SQL-92 standard defines how RDBMS should implement an INFORMATION_SCHEMA containing their dictionary tables. And indeed, some RDBMS do implement parts of the standard specification. These RDBMS ship with some implementation of the standard.


Close to the standard

  • HSQLDB: very close to the true standard
  • Postgres: close to the standard, with some tweaks (also has proprietary dictionary tables)
  • SQL Server: close to the standard but quite incomplete (also has proprietary dictionary tables)

Liberal interpretation of the standard

  • H2 (some backwards-incompatible changes, recently)
  • MySQL (only since 5.0, also has proprietary dictionary tables)

Other RDBMS provide their own idea of dictionary tables. This is something very tricky for schema navigation tools like jOOQ, to get a hold of. The dictionary table landscape can be described like this (my biased opinion):

Neat and well-documented dictionary tables

  • DB2: These dictionary tables somehow look like the standard, with different names. They feel intuitive.
  • Oracle: In my opinion has a better set of dictionary views than the ones proposed by the standard. Very easy to understand and well-documented all over the Internet
  • SQLite: There are no dictionary tables, but the SQLite stored procedures are very simple to use. It’s a simple database, after all

Hard to understand, not well-documented dictionary tables

  • Derby: Created the notion of conglomerates instead of using normal database-speak, such as relations, keys, etc.
  • MySQL: the old mysql schema was quite a pain. Fortunately, this is no longer true with MySQL 5.0
  • Ingres: Well… Ingres is an old database. Usability was not one of the main things in the 70?s…
  • Sybase SQL Anywhere: Lots of objects that have to be joined in complicated relations. Documentation is scarce
  • Sybase ASE: Even more difficult than SQL Anywhere. Some data can only be obtained with “tricks”

JDBC abstraction

The variety of dictionary tables seems to scream for standard abstraction. While the SQL-92 standard could in fact be implemented on most of these RDBMS, JDBC abstraction is even better. JDBC knows of the DatabaseMetaData object and allows for navigating database schemata easily. Unfortunately, every now and then, this API will throw a SQLFeatureNotSupportedException. There is no general rule about which JDBC driver implements how much of this API and when a workaround is needed. For jOOQ code generation, these facts make this API quite useless.

Other tools

There are some other tools in the open source world, as mentioned previously. Here are some drawbacks of using those tools in jOOQ:

  • Both tools that I know of are licensed with LGPL, which is not nicely compatible with jOOQ’s Apache 2 license.
  • Both tools navigate the entity-relationships very well, but seem to lack support for many non-standard constructs, such as UDT’s, advanced stored procedure usage (e.g. returning cursors, UDT’s, etc), ARRAY’s
  • SchemaCrawler supports only 8 RDBMS, jOOQ has 12 now
  • Both tools are rather inactive. See here and here

For more information, visit their sites:

jooq-meta

Because of the above reasons, jOOQ ships with its own database schema navigation: jooq-meta. This module can be used independently as an alternative to JDBC’s DatabaseMetaData, SchemaCrawler or SchemaSpy. jooq-meta uses jOOQ-crafted queries to navigate database meta-data, hence it is also part of the integration test suite. As an example, see how the Ingres foreign key relationships are navigated with jooq-meta:

Result<Record> result = create()
    .select(
        IirefConstraints.REF_CONSTRAINT_NAME.trim(),
        IirefConstraints.UNIQUE_CONSTRAINT_NAME.trim(),
        IirefConstraints.REF_TABLE_NAME.trim(),
        IiindexColumns.COLUMN_NAME.trim())
    .from(IICONSTRAINTS)
    .join(IIREF_CONSTRAINTS)
    .on(Iiconstraints.CONSTRAINT_NAME.equal(IirefConstraints.REF_CONSTRAINT_NAME))
    .and(Iiconstraints.SCHEMA_NAME.equal(IirefConstraints.REF_SCHEMA_NAME))
    .join(IICONSTRAINT_INDEXES)
    .on(Iiconstraints.CONSTRAINT_NAME.equal(IiconstraintIndexes.CONSTRAINT_NAME))
    .and(Iiconstraints.SCHEMA_NAME.equal(IiconstraintIndexes.SCHEMA_NAME))
    .join(IIINDEXES)
    .on(IiconstraintIndexes.INDEX_NAME.equal(Iiindexes.INDEX_NAME))
    .and(IiconstraintIndexes.SCHEMA_NAME.equal(Iiindexes.INDEX_OWNER))
    .join(IIINDEX_COLUMNS)
    .on(Iiindexes.INDEX_NAME.equal(IiindexColumns.INDEX_NAME))
    .and(Iiindexes.INDEX_OWNER.equal(IiindexColumns.INDEX_OWNER))
    .where(Iiconstraints.SCHEMA_NAME.equal(getSchemaName()))
    .and(Iiconstraints.CONSTRAINT_TYPE.equal("R"))
    .orderBy(
        IirefConstraints.REF_TABLE_NAME.asc(),
        IirefConstraints.REF_CONSTRAINT_NAME.asc(),
        IiindexColumns.KEY_SEQUENCE.asc())
    .fetch();

Conclusion

Once more it can be said that the world of RDBMS is very heterogeneous. Database abstraction in Java is established only to a certain degree in technologies such as JDBC, Hibernate/JPA, and third party libraries such as SchemaCrawler, SchemaSpy, and jooq-meta.

Reference: Database schema navigation in Java from our JCG partner at the “Java, SQL, and jOOQ” blog.

Related Articles :
Related Whitepaper:

Bulletproof Java Code: A Practical Strategy for Developing Functional, Reliable, and Secure Java Code

Use Java? If you do, you know that Java software can be used to drive application logic of Web services or Web applications. Perhaps you use it for desktop applications? Or, embedded devices? Whatever your use of Java code, functional errors are the enemy!

To combat this enemy, your team might already perform functional testing. Even so, you're taking significant risks if you have not yet implemented a comprehensive team-wide quality management strategy. Such a strategy alleviates reliability, security, and performance problems to ensure that your code is free of functionality errors.Read this article to learn about this simple four-step strategy that is proven to make Java code more reliable, more secure, and easier to maintain.

Get it Now!  

Leave a Reply


− 2 = seven



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books