19 October 2017

Opinionated Database Access in Java

Every now and then I get my hands on a Java project that requires to talk to a database. Depending on who of my team mates I'm talking to, database experts or Java developers, there exist different views on how to model and access data.

In my early days as software developer I used PHP scripts on the LAMP stack to talk to a MySQL database. It was really productive for simple database schemas. The phpMyAdmin tool offered all I needed to design the database scripts. With PHP I then fired plain SQL queries against the database.

I recognized:

• PHP isn't type safe. This added a whole class of possible errors. Over time, it got harder to maintain the application.
• The plain SQL queries were bound to a specific database dialect.

Some projects later I used erwin Data Modeler and Toad to design logical and physical Oracle database schemas, independent of a specific application. To that time, Hibernate and EclipseLink were state-of-the-art to model an object-relational schema on the Java side. This led to several team discussions on whether to generate the schema top-down from an object model or to do it the good old way, bottom-up from the database.

I recognized:

• When using an object model it is tempting to use auto-generated technical keys.
• The database guys preferred composite business keys over technical keys.
• On a greenfield project it might seem simple to create a database from an object model until schema evolution comes into play.
• Real world schemas need history triggers, versioning of data, access rules, ... we soon leave the object oriented world and use database features to implement these.
• Modern applications are stateless, objects are short-lived (e.g. request/response cycle). We don't need to use a big object tree when we want to remove one database entity that is located somewhere down the tree.

In Java I used plain JDBC and code generators to generate my entity layer. I had to use EJB 2.1/3.0, Hibernate and JPA 1.0/2.x. Relatively late I got my hands on Spring JDBC Template. All of these frameworks and abstractions had drawbacks.

I recognized:

• Generators increased the overall complexity of the project because they were never finished. It is awesome to see great parts of the application automatically generated based on a domain model. But someone needs to maintain the generators.
• Generators are good in code repetition but not in code abstraction. That aspect is orthogonal to code generation and done by humans.
• First I jumped on the object-relational train. But I started to hate the Hibernate/JPA magic: first/second level caches took me hours/days? to understand bugs. Specific operational modes for complex object trees looked alien to me. It took me hours/days? to perform opearations on the database that looked relatively simple.
• I wanted to talk SQL but I needed to learn a different language (e.g. JPA).

I further experimented with EBean for Java and Anorm & Slick for Scala in conjunction with the Play Framework. These approaches looked nice to me. EBean and Anorm are simple, straight-forward and tightly bound to the underlying domain: SQL. Slick was different, it mapped standard Scala collection operations to SQL - pretty slick!

I recognized:

• EBean and Anorm went back to simplicity. PHP-like productivity came back.
• First I was really thrilled about Slick but it also reminded me of the functional variant of an object-relational mapper. History showed that abstractions are always behind the real thing. Like GWT for web programming. You will quickly reach the limits of the framework.
• Standards like the Web or SQL will unlikely pass away. But does the same apply to a complex library that tries to abstract the whole thing away (Web/cross-browser, SQL/cross-dialect)?

There are other approaches, like jOOQ by Lukas Eder. I hadn't the opportunity to try it out, yet, but it sounds really promising. jOOQ follows the principles that are also in my mind:

• Database first, e.g. bottom up data modelling
• Think in SQL when writing queries, not in an intermediate abstraction like an object-relational language
• Type-safe data access helps

There is one thing I already mentioned above: a library that provides a cross-dialect SQL abstraction is never finished and highly complex. Compared to that, the JDBC standard is relatively fix regarding the database basics.

Do we really need complex database operations on the client side? I currently try to answer that question by experimenting with a tiny Java layer above JDBC. This opinionated world is database centric:

• The schema is modeled on the database side.
• Complex SQL queries are moved to the database, e.g. into views.
• Simple applications only need CRUD, this should be typesafe and easy.
• Complex things should still be possible but not necessarily be abstracted as internal DSL.

I'm thinking about a Java abstraction. The API is still unfinished but it will look similar to this (updated example):

@Component
class CustomerDao {

    static class CustomerTable extends Table {
        final Column<String> NAME = Column.asVarchar("name");
        final Column<Integer> NR = Column.asNumber("number");
        CustomerTable(JdbcAdapter jdbcAdapter) {
            super(jdbcAdapter);
        }
    }
    
    static class AddressTable extends Table {
        final Column<Integer> CUSTOMER_NR = Column.asNumber("customer_nr");
        final Column<String> STREET = Column.asVarchar("street");
        final Column<String> CITY = Column.asVarchar("city");
        final Column<Integer> ZIP = Column.asNumber("zip");
        AddressTable(JdbcAdapter jdbcAdapter) {
            super(jdbcAdapter);
        }
    }
    
    final CustomerTable cust;
    final AddressTable addr;
    
    @Autowired
    CustomerDao(JdbcTemplate jdbcTemplate) {
        final JdbcAdapter jdbcAdapter = new JdbcTemplateAdapter(jdbcTemplate);
        this.cust = new CustomerTable(jdbcAdapter);
        this.addr = new AddressTable(jdbcAdapter);
    }
    
    void addCustomer(String name, int number, List<Address> addresses) {
        Tx.run(() -> {
            cust.insert(
                cust.NAME.set(name),
                cust.NUMBER.set(number)
            );
            addresses.stream().forEach(address -> {
                addr.insert(
                    addr.CUSTOMER_NR.set(number),
                    addr.STREET.set(address.street),
                    addr.CITY.set(address.city),
                    addr.ZIP.set(address.zip)
                );
            });
        });
    }
    
    List<Address> getAddresses(int customerNr) {
        return addr.findAll(
            addr.CUSTOMER_NR.isEqualTo(customerNr)
        ).map(rs -> new Address(
            addr.STREET.get(rs),
            addr.CITY.get(rs),
            addr.ZIP.get(rs)
        ));
    }
}

We see that the API is centered around Table and Column. A table definition is a class that has column declarations. SQL types like Varchar are automatically mapped to Java types. We do not declare field lengths, nullability or primary keys. However, maybe we will make use of Optional regarding nullability.

The JdbcAdapter interface is the bridge between our library and JDBC.

The Table type provides the CRUD operations, like

• exists(Condition) : boolean
• find(Condition).map(rs -> ...) : Optional<R>
• find(Column...).where(Condition).map(rs -> ...) : Optional<R>
• findAll(Condition).map(rs -> ...) : List<R>
• findAll(Column...).where(Condition).map(rs -> ...) : List<R>
• insert(Assignment...) : void
• update(Assignment...).where(Condition) : int
• delete(Condition) : int

Beside stable names and safe types the Column type provides

• get(ResultSet) : T
• set(T) : Assignment

and most/all? operations of the SQL-92 standard, including

• isEqualTo(T) : Condition
• isIn(T...) : Condition
• isNull() : Condition
• isNotNull() : Condition

Some Column operations are available only for specific SQL types, e.g.

• isLike(String) : Condition works only for Varchar and Char
• isTrue() / isFalse() : Condition work only for Boolean

Conditions are composable:

• and(Condition) : Condition
• or(Condition) : Condition
• static not(Condition) : Condition // not clear, or non-static not()

Summing up, the API is similar to JDBC Template but it is type-safe. There are things that I still have to think about a bit, like transactions. But one thing is clear: we will not add many features afterwards.

My current prototype does contain ~500 LOC (including javadoc and whitespace). It will grow a bit when adding the missing SQL operators but I want to keep it that small. Please give me a hint, if I've overseen an existing JDBC library that covers my use-cases.

- Daniel

Update

The library would be so small, it would be super-easy to provide separate implementations with syntactic sugar for other languages like Javascript, Scala, Kotlin, ...