Some few years ago I was developing a database backed Java application and grew annoyed at how cumbersome interacting with a database really was in that language. I just wanted to interface with SQL using Java call semantics and native Java objects. I didn’t want to have to write dumb container objects copying the result set type in those cases where that was all I needed, and under no circumstance did I want write anything to copy data back and forth, neither code nor any kind of configuration to explain to some tool how to do that. I just wanted a simple, boilerplate-free and seamless way to run SQL commands from Java, kind of like running SQL from PL/pgSQL is.
Additionally, since I was working in Java I expected that everything would be statically checked. I expected types to be checked between the database and application and that all SQL in the application would be verified against the database without having to do anything such as set up tests or, again, write configuration or something. For example syntax errors in SQL commands in the application or passing a Java string to a date field in a SQL command should generate a compile time error.
So I started tinkering on my spare time, and ended up with an approach relying on the metaprogramming facilities in Java to simply take SQL commands and Java method signatures and figure out how to connect them. Others have used similar approaches too, in Java and other languages; those that follow Planet PostgreSQL might have noticed Aurynn Shaws Simpycity for tool Python, which operates in the same fashion despite being simpler.
The tool is accidentally named TMDBC, and consists of a compile time and runtime library. The compile time is technically implemented as a code generating Java 6/JSR 269 annotation processor, a consequence of which is that it’s invoked by the Java compiler when it detects the relevant annotations, and while the run time is actually optional you won’t have all the fun without it. It has been developed against PostgreSQL and only tested successfully against that.
The implementation still has a few rough edges, but it’s quite usable and since you can mix that approach and plain JDBC in the same transaction using it shouldn’t introduce any hurdles. I feel quite confident in that there aren’t any severe, outstanding issues.
Enough ranting, code here
Here’s an example. If the use-cases one were to satisfy required that one could fetch a single entry, a range of entries or a list of entries from a “employee” table then a complete example that satisfies everything I’ve mentioned could be:
package test;
import java.util.List;
import no.thormick.tmdbc.annotation.*;
@JDBC( // Connection to development database, not production
driver = "org.postgresql.Driver",
value = "jdbc:postgresql://localhost/postgres?user=test&password=test" )
@DBAPI public interface MinimalDBI {
// abstract class also works and is more flexible
@SQL(
"SELECT name, salary, emp_id\n" +
"FROM employee\n" +
"WHERE emp_id = ?")
Employee getEmployee( int empId );
@SQL(
"SELECT name, salary, emp_id\n" +
"FROM employee\n" +
"WHERE emp_id BETWEEN ? AND ?")
List<Employee> getEmployees( int empIdLow, int empIdHigh );
@SQL(
"SELECT name, salary, emp_id\n" +
"FROM employee\n" +
"WHERE emp_id = ANY ( ? )")
List<Employee> getEmployees( int[] empIds );
}
And that’s everything, done, no configuration files anywhere. When this is compiled an implementation of this interface as well as the “Employee” class will be generated (unless you supply it, in which case it will be type-checked against the result set). If there’s a type error between a SQL command and Java method or if PostgreSQL finds something wrong with a SQL command, then a compile error will be signaled. To use it, the following would suffice (the connection handling here is just the simplest way to do it, other more transparent approaches are supported):
package test;
import java.sql.*;
public class Main {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");
Connection conn = null;
try {
// This, on the other hand, is the production connection
conn = DriverManager.getConnection(
"jdbc:postgresql://localhost/postgres?" +
"user=test&password=test");
// Handling the connection like this is just one way to do it
MinimalDBI db = new MinimalDBIImpl(conn);
System.out.println("One employee:");
Employee employee = db.getEmployee(2);
System.out.println("Employee "+ employee.getEmpId() +": "+
employee.getName() +", "+ employee.getSalary() );
System.out.println("Two employees:");
for(Employee emp : db.getEmployees(new int[]{1,3}) ){
System.out.println( "Employee "+ emp.getEmpId() +": "+
emp.getName() +", "+ emp.getSalary() );
}
} finally {
if( conn != null )
conn.close();
}
}
}
Note how the class “MinimalDBIImpl” is instantiated. This is the generated database interface class (the name can be set by passing an argument to the DBAPI annotation) and contains the glue between Java and SQL (or JDBC, to be precise.)
That example might produce output like the following:
One employee:
Employee 2: Bar, 60
Two employees:
Employee 1: Foo, 50
Employee 3: Bruce, 70
This example is very minimalistic. Using a interface for the @DBAPI class makes TMDBC use some (hopefully reasonable) defaults for the implementation, an abstract class can be used for more flexibility. Java collection types are used, which causes the results to be materialized, but there are runtime library classes that can be used instead to return connected objects, pretty much a cursor that returns JavaBeans. To use that instead just change the return-type in the method signatures from “List” to “ResultStatement“. Those can be used in a for each loop like above too.
The web page for the tool is here, and there’s also JavaDoc for the API. I’ve written a considerably more elaborate example on how to use TMDBC together with GlassFish to create a PostgreSQL backed Web Service, also demonstrating a different way to handle connections.
Have fun with it!
