Typesafe database interaction with Java 8

Method references in Java 8 will allow us to build much nicer APIs for interacting with databases.

For example when you combine method references with features we already had in Java it’s possible to create clean, typesafe queries without needing code generation.

Full examples and implementation available on github.

Here’s an example of what’s possible

Queries

    Optional<Person> person = 
        from(Person.class)
            .where(Person::getFirstName)
            .like("%ji")
            .and(Person::getLastName)
            .equalTo("weber")
            .select(personMapper, connectionFactory::openConnection);

This queries the database for a person with last name ‘weber’ and first name ending in ‘ji’, and returns a Person if found.

It generates the following SQL, fills in the parameters, and deserialises the result into a Person object for us.

    SELECT * FROM person WHERE first_name LIKE ? AND last_name = ?

We are even able to make it typecheck the comparisons. The following gives a compile-time error as “hello” is not an Integer.

    Optional<Person> result = 
        from(Person.class)
            .where(Person::getFavouriteNumber)
            .equalTo(5) // This is fine.
            .and(Person::getFavouriteNumber)
            .equalTo("hello") // This line fails to compile as "hello" is not an Integer

Here’s how it works.

Firstly Optional<Person> uses Java8’s Optional type to indicate that the query may not have found any matching people.

Person::getFirstName is a method reference to an instance method on a Person. What this gives us is a function that takes an instance of Person and returns the result of calling getFirstName on said Person instance. In this instance a Function<Person, String>.

Passing this to our where() method gives us back a SelectComparison<Person, String> instance. SelectComparison is an interface which has comparison methods such as equalTo or notEqualTo that only accept values that match the second type parameter – in our case String (because our method reference had a String as its return type.

    public <U> SelectComparison<T,U> where(Function<T,U> getter)

This lets us build up valid queries. The next trick is to for the query builder to work out what the method name “getFirstName” actually is. We have just passed in a method reference, it doesn’t know what it is called.

To work around this we can use a Java dynamic proxy. When we call from(Person.class) we create a dynamic proxy that impersonates the Person type and simply records the names of any methods invoked on it. This is a similar approach to that used by mocking frameworks for tests.

The cglib library makes this really easy – and it now even works with Java 8.

    public class RecordingObject implements MethodInterceptor {
        private String currentPropertyName = "";
 
        ...
 
        public Object intercept(Object o, Method method, Object[] os, MethodProxy mp) throws Throwable {
            currentPropertyName = Conventions.toDbName(method.getName());
            ...
        }
 
        public String getCurrentPropertyName() {
            return currentPropertyName;
        }
    }

With this in place when we call where(Person::getFirstName) the implementation invokes the passed Function<Person, String> against our dummy proxy-object and asks the proxy object for the name of the invoked method and keeps a note of it for query generation. We can convert the names to an alternative format for the database using a naming convention. My preference is to convert namesLikeThis to names_like_this.

    public <U> SelectComparison<T,U> where(Function<T,U> getter) {
 
        // Invoke against dummy object, find name
        getter.apply(recorder.getObject());
 
        // Ask the dummy object what the name is.
        String fieldName = recorder.getCurrentPropertyName(); 
 
        return new SelectComparison<T, U>() {
            public Select<T> equalTo(U value) {
                // Record the values for query generation/execution
                whereFieldNames.add(new FieldNameValue<>(fieldName, value, "="));
                return Select.this;
            }

Mappers

Now that we can generate these queries, we can use a similar approach to convert the ResultSet that we get from Jdbc back into a Person.

    Mapper<Person> personMapper = 
        mapper(Person::new)
            .set(Person::setFirstName)
            .set(Person::setLastName)
            .set(Person::setFavouriteNumber);

Here we are constructing a Mapper that is able to take a row from a ResultSet and convert it into a Person type. We construct it with a builder that first takes in a Supplier<T> – a factory method that can give us back an instance of the type we are creating, ready to populate. In this instance we are using Person::new which is a method reference to the constructor of Person.

Next we pass in references to the setters that we want to call to populate the Person with values from the database.

We are able to use a similar trick to building the queries above. Here, our method reference Person::setFirstName gives us a function that takes a Person and also another value that the setter itself accepts. Our set() method accepts a BiConsumer<T,U> in this case a BiConsumer<Person,String> for the first two setters and a BiConsumer<Person,Integer> for the last one.

Using the above dynamic proxy trick we are able to again record the names of the setters for later use in querying the resultset. We also store the setter functions themselves to invoke when populating the object.

The map(ResultSet) function then just involves

1. Construct a new instance using the factory method Person::new
2. For each method reference passed in
   a) Query the resultset for its name
   b) Invoke the method reference, passing in the instance from 1. and the value from the ResultSet.

We can extend this to more than just queries. We can create tables

DDL

    create(Person.class)
        .field(Person::getFirstName)
        .field(Person::getLastName)
        .field(Person::getFavouriteNumber)
        .execute(connectionFactory::openConnection);

Which generates

    CREATE TABLE IF NOT EXISTS person ( first_name text, last_name text, favourite_number INTEGER )

Inserting Values

For inserts we pass in an instance of Person rather than a Class<Person>. This means that when executing the insert statement we can invoke the passed method references against our Person instance to obtain the values for use in the insert statement.

    Person benji = new Person("benji","weber");
    benji.setFavouriteNumber(9);
 
    insert(benji)
        .value(Person::getFirstName)
        .value(Person::getLastName)
        .value(Person::getFavouriteNumber)
        .execute(connectionFactory::openConnection);

Which generates the following, populating it with values from the “benji” object.

    INSERT INTO person (first_name, last_name, favourite_number) VALUES ( ?, ?, ? )

We invoke the getter function twice. Once against our proxy to get the name, and once against our instance to get the value.

    public <U extends Serializable> Upsert<T> value(Function<T,U> getter) {
        U result = getter.apply(recorder.getObject());
        String fieldName = recorder.getCurrentPropertyName();
        setFieldNames.add(new FieldNameValue(fieldName, getter.apply(value)));
        return this;
    }

Updates

We can of course also do updates. We just combine the approaches used for queries and inserts.

    update(benji)
        .value(Person::getFirstName)
        .where(Person::getLastName)
        .equalTo("weber")
        .execute(connectionFactory::openConnection);

Which generates the following, as before – populating it with values from the “benji” object.

    UPDATE person SET first_name = ? WHERE last_name = ?

There’s lots to look forward to with Java 8. It will be interesting to see what framework developers start doing with these features. Now we just have to wait for Java 8 to actually be released.

In case you missed the links above, read the full code examples and implementation on github.