LiveDB

From APIDesign

Current revision

Another use of AnnotationProcessors (in addition to generate CompileTimeCaches) is to provide type-safe, compile-time view of a live database structure. Something that might be called JavaOnRails.

For a while languages like Ruby or Groovy have been blessed for being able to easily access structures in databases without all the hassle associated with traditional object relational mapping needed in Java. Maybe there was something more in the whole rails hype, but for me, rails mean exactly this. Be able to see content of LiveDB.

This page documents my experiment showing how to do something similar in good old Java. Well, it is not old good Java, but Java6. Java6 has been around for few years already, but it is common that good ideas take time. As a result we are only slowly recognizing how useful AnnotationProcessors can be.

Get the sources

The sources are now available and their execution is fully automated (even the Derby database is started automatically via the Ant build script). To get the sources follow these steps:

$ hg clone http://source.apidesign.org/hg/apidesign/
$ cd apidesign
$ hg pull
$ hg update

The right project is located in samples/livedb directory. You can either play with it directly from command line using Ant or you can open the project directory inside NetBeans IDE 6.9 and later:

$ cd samples/livedb
samples/livedb$ JAVA_HOME=/jdk-11 mvn clean test

Playing with the Sample

Tables in the database

The first thing you need to do to really play with the sample is to create your own database. Maybe you have one, maybe you need to create one. If you have NetBeans IDE then one is ready for you. Just start the Derby database as explained for example at JavaDB tutorial. Create a table called AGE with two columns NAME and AGE.

Now you need to define a connection to your database. Do it by using the @LiveDB annotation:

Code from LiveDB.java:

@Target(ElementType.PACKAGE)
@Retention(RetentionPolicy.SOURCE)
public @interface LiveDB {
    String url();
    String user();
    String password();
    String query();
    String classname();
}
 

As you can see this is compile time annotation, as such it needs to be processed by AnnotationProcessor. Indeed there is one in this project too, but rather than dicovering the magic, let's explore the ease of use: To access a database, just use the above given annotation on some package statement:

Code from package-info.java:

@LiveDB(
    classname="Age", password="j1", user="j1",
    query="select * from APP.AGE", 
    url="jdbc:derby:classpath:db"
)
package org.apidesign.livedb.example;
 

Of course you need to fill in proper connection credentials, and also the name of a class (Age in the above case) that you wish to use to represent result of your query. Done? If so, you are ready to use the class (which almost magically appears in your package). For example, in the following code snipptet, you can refer to the Age.query method and also access the fields of the class:

Code from LiveDBTest.java:

List<Age> ages = Age.query();
assertEquals("One record", 1, ages.size());
Age age = ages.get(0);
assertEquals("name is apidesign", "apidesign", age.NAME);
assertEquals("it is three years old", 3, age.AGE.intValue());
 

If you done things correctly, you have working access to live database. Everything compiles, runs and last, but not least, your IDE (if it is any good, like NetBeans IDE 6.9), will properly understood and use the on-the-fly generated Age class. Try for example code completion on:

age.<ctrl+space>

You will correctly see fields representing individual rows in the t able. Moreover these fields have proper types (e.g. NAME is String, AGE is BigInteger). Feels like Java on Rails? All of this is brought to you by the power of AnnotationProcessors!

You can also modify the table structure by adding new column. As soon as you do it (and tell the IDE to refresh), you can immediatelly use the new fields in your code. Everything compiles. Btw. you need to tell the NetBeans IDE to refresh. You can either use new action Refresh All Metadata for that or you can step into package-info.java, add an empty line and save the file.

Extending IDEs

The beauty of using AnnotationProcessors is in their ability to naturally extend functionality of any IDE (that supports AnnotationProcessors). As AnnotationProcessor API is part of JDK6, it is the most standard way to extend any Java IDE. For example there can be AnnotationProcessor that supports SQL syntax inside of the annotation's strings.

However providing static syntax is not all AnnotationProcessor can do. It can generate classes, resource files and can also add dynamic code completion items. It is possible to allow the processor to connect to live SQL database and show list of existing tables as suggestions for completion inside of the string representing SQL statement:

Still you are just writing a library. You don't need to understand any IDE's APIs. This is all standard Java6 and there is no need for any hacks. AnnotationProcessors turn Java into suitable meta-language for writing DSLs. Here is the code that provides static and dynamic code completion:

Code from LiveDBProcessor.java:

@Override
public Iterable<? extends Completion> getCompletions(
    Element element, AnnotationMirror annotation, 
    ExecutableElement member, String userText
) {
    if (!"query".equals(member.getSimpleName().toString())) {
        return Collections.emptyList();
    }
    if (userText == null || userText.length() <= 1) {
        return Collections.singleton(Completions.of("\"SELECT "));
    }
    if (userText.toUpperCase().matches(".*FROM *")) {
        String user = extractValue(annotation, "user");
        String password = extractValue(annotation, "password");
        String url = extractValue(annotation, "url");
        if (user == null || password == null || url == null) {
            return Collections.emptyList();
        }
        try {
            List<Completion> arr = new ArrayList<Completion>();
            Connection c = getConnection(url, user, password);
            DatabaseMetaData meta = c.getMetaData();
            ResultSet res = meta.getTables(null, null, "%", null);
            boolean ok = res.first();
            while (ok) {
                String txt = userText + res.getString("TABLE_NAME");
                arr.add(Completions.of(txt));
                ok = res.next();
            }
            return arr;
        } catch (SQLException ex) {
            throw new IllegalStateException(ex);
        }
    }
    return Collections.emptyList();
}
 

The code that generates the data access class follows. Indeed, writing such code requires a bit of understanding of the Java source model, but overall it is not that complex:

Code from LiveDBProcessor.java:

@SupportedAnnotationTypes("org.apidesign.livedb.LiveDB")
@SupportedSourceVersion(SourceVersion.RELEASE_6)
@ServiceProvider(service=Processor.class)
public final class LiveDBProcessor extends AbstractProcessor {
    @Override
    public boolean process(
        Set<? extends TypeElement> annotations, RoundEnvironment roundEnv
    ) {
        final Filer filer = processingEnv.getFiler();
        for (Element e : roundEnv.getElementsAnnotatedWith(LiveDB.class)) {
            LiveDB db = e.getAnnotation(LiveDB.class);
            PackageElement pe = (PackageElement)e;
            String clsName = pe.getQualifiedName() + "." + db.classname();
            try {
                JavaFileObject src = filer.createSourceFile(clsName, pe);
                Writer w = src.openWriter();
                Connection c = getConnection(
                    db.url(), db.user(), db.password()
                );
                CallableStatement s = c.prepareCall(db.query());
                ResultSet rs = s.executeQuery();
                ResultSetMetaData md = rs.getMetaData();
                generateClassHeader(w, pe, db);
                w.append("class " + db.classname() + " {\n");
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    generateField(w, md, i);
                }
                generateConstructor(w, db, md);
                generateQueryMethod(w, db, md);
                w.append("}");
                w.close();
            } catch (IOException ex) {
                throw new IllegalStateException(ex);
            } catch (SQLException ex) {
                throw new IllegalStateException(ex);
            }
        }
        return true;
    }
}
 

Just locate all packages annotated by @LiveDB, read the annotation data and use them to generate new Java source file. The generated file willl later be compiled by the JavaC compiler.

Philosohical Aspects

In some sense with an annotation like @LiveDB the structure of your database becomes part of your sources. But that is good, is not it? I really hate when a truth is spelled out at two different places (e.g. in Hibernate entity classes and also in the database schema). With LiveDB you just need a lightweight compile time database (like Derby) with the same structure as your deployment database being ready. The schema of such database is your definition of truth which remains at a single place.

<comments/>