Pattern for compile time discovery of unsafe/wrong queries

Question

My team is developing a large java application which extensively queries a MySQL database (in different classes and modules). I'd like to known if there is a pattern that allows me to be notified at compile time if there are queries that refer to a wrong table structure (for instance if I remove or add a field on a table and the query string refers to it), in order to prevent runtime errors. This should work also for JOIN queries.

Answer 1

I would say that the simple answer is "no". The more complete answer is "yes, to some degree", depending on your willingness to jump through hoops.

Unless you have a java representation of your database schema you will never be able to get compile time notification if your queries are wrong (these classes can be generated). Also, you must use these classes to build your queries, so the method you use today (query strings) must be abandoned. To be able to use the java classes to build your queries, you must also use tricks. LiquidForm uses the required tricks to build JPA queries, but I have not seen a similar library for constructing SQL queries (LiquidForm is new and quite brilliant). You would actually have to build a similar library yourself. So, as you see, getting compile time warnings when constructing SQLs is hard, but not impossible (only nearly impossible). But even if you should be able to create what I suggest, your java representation of the schema must be updated immediately after a schema change, so the generation of java classes would have to be built into your IDE or build tool.

I would suggest you rather have good unit tests that will notice when your queries become illegal as a result of schema change. This is the most common way to achieve what you want. Also, should you decide to "upgrade" to JPA, you could use LiquidForm to get what you want.

Answer 2

There's an open-source tool called DODS (Data Object Design Studio) that could do what you want. The DODS tool was originally part of the Enhydra Java application server project, and since the company backing that project went kablooey in 2002, DODS has been hosted and maintained at ObjectWeb. Anyway, it's open-source (LGPL).

http://forge.objectweb.org/projects/dods

The concept is that you describe your schema in an XML file, and DODS generates Java POJO classes with which you can query and manipulate the database tables. Of course every time you change your schema, you need to run DODS again to re-generate the ORM classes, and recompile your app against them.

But the result is that if a table or column disappears, and your app is querying database metadata that no longer exists, you do get a compile-time error, because your code is now calling a corresponding class or method that no longer exists.

Answer 3

Querydsl is similar to LiquidForm and supports both JPA / Hibernate and SQL based backends.

For the SQL based version we currently support MySQL (5.? tested), Oracle (10g tested) and HSQLDB.

In a nutshell a query like this

select count(*) from test where name = null

would become

long count = query.from(test).where(test.name.isnull()).count();

Querydsl SQL uses code generation to reflect SQL schemas into Java classes.