A RESTful JDBC HTTP Server built on top of jOOQ


The jOOQ ecosystem and community is continually growing. We’re personally always thrilled to see other Open Source projects built on top of jOOQ. Today, we’re very happy to introduce you to a very interesting approach at combining REST and RDBMS by Bj枚rn Harrtell.

bjorn-harrtellBj枚rn Harrtell is a swedish programmer since childhood. He is usually busy writing GIS systems and integrations at Sweco Position AB but sometimes he spends time getting involved in Open Source projects and contributing to a few pieces of work related to Open Source projects like GeoTools and OpenLayers. Bj枚rn has also initiated a few minor Open Source projects himself and one of the latest projects he’s been working on is jdbc-http-server.

We’re excited to publish Bj枚rn’s guest post introducing his interesting work:

JDBC HTTP Server

Ever found yourself writing a lot of REST resources that do simple CRUD against a relational database and felt the code was repeating itself? In that case, jdbc-http-server might be a project worth checking out.

jdbc-http-server exposes a relational database instance as a discoverable REST API making it possible to perform simple CRUD from a browser application without requiring any backend code to be written.

A discoverable REST API means you can access the root resource at / and follow links to subresources from there. For example, let’s say you have a database named testdb with a table named testtable in the public schema you can then do the following operations:

Retrieve (GET), update (PUT) or delete (DELETE) a single row at:

/db/testdb/schemas/public/tables/testtable/rows/1

Retrieve (GET), update (PUT) rows or create a new row (POST) at:

/db/testdb/schemas/public/tables/testtable/rows

The above resources accepts parameters select, where, limit, offset
and orderby where applicable. Examples:

GET a maximum of 10 rows where cost>100 at:

/db/testdb/schemas/public/tables/testtable/rows?where=cost>100&limit=10

jdbc-http-server is database engine agnostic since it utilizes jOOQ to generate SQL in a dialect suited to the target database engine. At the moment H2, PostgreSQL and HSQLDB are covered by automated tests. Currently the only available representation data format is JSON but adding more is an interesting possibility.

Feedback and, of course, contributions are welcome 馃檪

Let’s Stream a Map in Java 8 with jOO位


I wanted to find an easy way to stream a Map in Java 8. Guess what? There isn’t!

What I would’ve expected for convenience is the following method:

public interface Map<K, V> {

    default Stream<Entry<K, V>> stream() {
        return entrySet().stream();
    }    
}

But there’s no such method. There are probably a variety of reasons why such a method shouldn’t exist, e.g.:

  • There’s no “clear” preference for entrySet() being chosen over keySet() or values(), as a stream source
  • Map isn’t really a collection. It’s not even an Iterable
  • That wasn’t the design goal
  • The EG didn’t have enough time

Well, there is a very compelling reason for Map to have been retrofitted to provide both an entrySet().stream() and to finally implement Iterable<Entry<K, V>>. And that reason is the fact that we now have Map.forEach():

default void forEach(
        BiConsumer<? super K, ? super V> action) {
    Objects.requireNonNull(action);
    for (Map.Entry<K, V> entry : entrySet()) {
        K k;
        V v;
        try {
            k = entry.getKey();
            v = entry.getValue();
        } catch(IllegalStateException ise) {
            // this usually means the entry is no longer in the map.
            throw new ConcurrentModificationException(ise);
        }
        action.accept(k, v);
    }
}

forEach() in this case accepts a BiConsumer that really consumes entries in the map. If you search through JDK source code, there are really very few references to the BiConsumer type outside of Map.forEach() and perhaps a couple of CompletableFuture methods and a couple of streams collection methods.

So, one could almost assume that BiConsumer was strongly driven by the needs of this forEach() method, which would be a strong case for making Map.Entry a more important type throughout the collections API (we would have preferred the type Tuple2, of course).

Let’s continue this line of thought. There is also Iterable.forEach():

public interface Iterable<T> {
    default void forEach(Consumer<? super T> action) {
        Objects.requireNonNull(action);
        for (T t : this) {
            action.accept(t);
        }
    }
}

Both Map.forEach() and Iterable.forEach() intuitively iterate the “entries” of their respective collection model, although there is a subtle difference:

  • Iterable.forEach() expects a Consumer taking a single value
  • Map.forEach() expects a BiConsumer taking two values: the key and the value (NOT a Map.Entry!)

Think about it this way:

This makes the two methods incompatible in a “duck typing sense”, which makes the two types even more different

Bummer!

Improving Map with jOO位

We find that quirky and counter-intuitive. forEach() is really not the only use-case of Map traversal and transformation. We’d love to have a Stream<Entry<K, V>>, or even better, a Stream<Tuple2<T1, T2>>. So we implemented that in jOO位, a library which we’ve developed for our integration tests at jOOQ. With jOO位, you can now wrap a Map in a Seq type (“Seq” for sequential stream, a stream with many more functional features):

Map<Integer, String> map = new LinkedHashMap<>();
map.put(1, "a");
map.put(2, "b");
map.put(3, "c");

assertEquals(
  Arrays.asList(
    tuple(1, "a"), 
    tuple(2, "b"), 
    tuple(3, "c")
  ),

  Seq.seq(map).toList()
);

What you can do with it? How about creating a new Map, swapping keys and values in one go:

System.out.println(
  Seq.seq(map)
     .map(Tuple2::swap)
     .toMap(Tuple2::v1, Tuple2::v2)
);

System.out.println(
  Seq.seq(map)
     .toMap(Tuple2::v2, Tuple2::v1)
);

Both of the above will yield:

{a=1, b=2, c=3}

Just for the record, here’s how to swap keys and values with standard JDK API:

System.out.println(
  map.entrySet()
     .stream()
     .collect(Collectors.toMap(
         Map.Entry::getValue, 
         Map.Entry::getKey
     ))
);

It can be done, but the every day verbosity of standard Java API makes things a bit hard to read / write

Stop Claiming that you’re Using a Schemaless Database


One of MongoDB’s arguments when evangelising MongoDB is the fact that MongoDB is a “schemaless” database:

Why Schemaless?

MongoDB is a JSON-style data store. The documents stored in the database can have varying sets of fields, with different types for each field.

And that’s true. But it doesn’t mean that there is no schema. There are in fact various schemas:

  • The one in your head when you designed the data structures
  • The one that your database really implemented to store your data structures
  • The one you should have implemented to fulfill your requirements

Every time you realise that you made a mistake (see point three above), or when your requirements change, you will need to migrate your data. Let’s review again MongoDB’s point of view here:

With a schemaless database, 90% of the time adjustments to the database become transparent and automatic. For example, if we wish to add GPA to the student objects, we add the attribute, resave, and all is well 鈥 if we look up an existing student and reference GPA, we just get back null. Further, if we roll back our code, the new GPA fields in the existing objects are unlikely to cause problems if our code was well written.

Everything above is true as well.

“Schema-less” vs. “Schema-ful”

But let’s translate this to SQL (or use any other “schema-ful” database instead):

ALTER TABLE student ADD gpa VARCHAR(10);

And we’re done! Gee, we’ve added a column, and we’ve added it to ALL rows. It was transparent. It was automatic. We “just get back null” on existing students. And we can even “roll back our code”:

ALTER TABLE student DROP gpa;

Not only are the existing objects unlikely to cause problems, we have actually rolled back our code AND database.

Let’s summarise:

  • We can do exactly the same in “schema-less” databases as we can in “schema-ful” ones
  • We guarantee that a migration takes place (and it’s instant, too)
  • We guarantee data integrity when we roll back the change

What about more real-world DDL?

Of course, at the beginning of projects, when they still resemble the typical cat/dog/pet-shop, book/author/library sample application, we’ll just be adding columns. But what happens if we need to change the student-teacher 1:N relationship into a student-teacher M:N relationship? Suddenly, everything changes, and not only will the relational data model prove far superior to a hierarchical one that just yields tons of data duplication, it’ll also be moderately easy to migrate, and the outcome is guaranteed to be correct and tidy!

CREATE TABLE student_to_teacher 
AS
SELECT id AS student_id, teacher_id
FROM student;

ALTER TABLE student DROP teacher_id;

… and we’re done! (of course, we’d be adding constraints and indexes)

Think about the tedious task that you’ll have transforming your JSON to the new JSON. You don’t even have XSLT or XQuery for the task, only JavaScript!

Let’s face the truth

Schemalessness is about a misleading term as much as NoSQL is:

And again, MongoDB’s blog post is telling the truth (and an interesting one, too):

Generally, there is a direct analogy between this 鈥渟chemaless鈥 style and dynamically typed languages. Constructs such as those above are easy to represent in PHP, Python and Ruby. What we are trying to do here is make this mapping to the database natural.

When you say “schemaless”, you actually say “dynamically typed schema” – as opposed to statically typed schemas as they are available from SQL databases. JSON is still a completely schema free data structure standard, as opposed to XML which allows you to specify XSD if you need, or operate on document-oriented, “schema-less” (i.e. dynamically typed) schemas.

(And don’t say there’s json-schema. That’s a ridiculous attempt to mimick XSD)

This is important to understand! You always have a schema, even if you don’t statically type it. If you’re writing JavaScript, you still have types, which you have to be fully aware of in your mental model of the code. Except that there’s no compiler (or IDE) that can help you infer the types with 100% certainty.

An example:

… and more:

So, there’s absolutely nothing that is really easier with “schemaless” databases than with “schemaful” ones. You just defer the inevitable work of sanitising your schema to some other later time, a time when you might care more than today, or a time when you’re lucky enough to have a new job and someone else does the work for you. You might have believed MongoDB, when they said that “objects are unlikely to cause problems”.

But let me tell you the ugly truth:

Anything that can possibly go wrong, does

Murphy

We wish you good luck with your dynamically typed languages and your dynamically typed database schemas – while we’ll stick with type safe SQL.

jOOQ: The best way to write SQL in Java

The dreaded DefaultAbstractHelperImpl


A while ago, we have published this fun game we like to call Spring API Bingo. It is a tribute and flattery to Spring’s immense creativeness when forming meaningful class names like

  • FactoryAdvisorAdapterHandlerLoader
  • ContainerPreTranslatorInfoDisposable
  • BeanFactoryDestinationResolver
  • LocalPersistenceManagerFactoryBean

Two of the above classes actually exist. Can you spot them? If no, play Spring API Bingo!

Clearly, the Spring API suffers from having…

To name things

There are only two hard problems in computer science. Cache invalidation, naming things, and off-by-one errors

– Tim Bray quoting Phil Karlton

There are a couple of these prefixes or suffixes that are just hard to get rid of in Java software. Consider this recent discussion on Twitter, that inevitably lead to an (very) interesting discussion:

Yes, the Impl suffix is an interesting topic. Why do we have it, and why do we keep naming things that way?

Specification vs. body

Java is a quirky language. At the time it was invented, object orientation was a hot topic. But procedural languages had interesting features as well. One very interesting language at the time was Ada (and also PL/SQL, which was largely derived from Ada). Ada (like PL/SQL) reasonably organises procedures and functions in packages, which come in two flavours: specification and body. From the wikipedia example:

-- Specification
package Example is
  procedure Print_and_Increment (j: in out Number);
end Example;

-- Body
package body Example is
 
  procedure Print_and_Increment (j: in out Number) is
  begin
    -- [...]
  end Print_and_Increment;
 
begin
  -- [...]
end Example;

You always have to do this, and the two things are named exactly the same: Example. And they’re stored in two different files called Example.ads (ad for Ada and s for specification) and Example.adb (b for body). PL/SQL followed suit and names package files Example.pks and Example.pkb with pk for Package.

Java went a different way mainly because of polymorphism and because of the way classes work:

  • Classes are both specification AND body in one
  • Interfaces cannot be named the same as their implementing classes (mostly, because there are many implementations, of course)

In particular, classes can be a hybrid of spec-only, with a partial body (when they’re abstract), and full spec and body (when they’re concrete).

How this translates to naming in Java

Not everyone appreciates clean separation of specs and body, and this can certainly be debated. But when you’re in that Ada-esque mind set, then you probably want one interface for every class, at least wherever API is exposed. We’re doing the same for jOOQ, where we have established the following policy to name things:

*Impl

All implementations (bodies) that are in a 1:1 relationship with a corresponding interface are suffixed Impl. If ever possible, we try to keep those implementations package-private and thus sealed in the org.jooq.impl package. Examples are:

This strict naming scheme makes it immediately clear, which one is the interface (and thus public API), and which one is the implementation. We wish Java were more like Ada with this respect, but we have polymorphism, which is great, and…

Abstract*

… and it leads to reusing code in base classes. As we all know, common base classes should (almost) always be abstract. Simply because they’re most often incomplete implementations (bodies) of their corresponding specification. Thus, we have a lot of partial implementations that are also in a 1:1 relationship with a corresponding interface, and we prefix them with Abstract. Most often, these partial implementations are also package-private and sealed in the org.jooq.impl package. Examples are:

In particular, ResultQuery is an interface that extends Query, and thus AbstractResultQuery is a partial implementation that extends the AbstractQuery, which is also a partial implementation.

Having partial implementations makes perfect sense in our API, because our API is an internal DSL (Domain-Specific Language) and thus has thousands of methods that are always the same, no matter what the concrete Field really does – e.g. Substring

Default*

We do everything API related with interfaces. This has proven highly effective already in popular Java SE APIs, such as:

  • Collections
  • Streams
  • JDBC
  • DOM

We also do everything SPI (Service Provider Interface) related with interfaces. There is one essential difference between APIs and SPIs in terms of API evolution:

  • APIs are consumed by users, hardly implemented
  • SPIs are implemented by users, hardly consumed

If you’re not developing the JDK (and thus don’t have completely mad backwards-compatibility rules), you’re probably mostly safe adding new methods to API interfaces. In fact, we do so in every minor release as we do not expect anyone to implement our DSL (who’d want to implement Field‘s 286 methods, or DSL‘s 677 methods. That’s mad!)

But SPIs are different. Whenever you provide your user with SPIs, such as anything suffixed *Listener or *Provider, you can’t just simply add new methods to them – at least not prior to Java 8, as that would break implementations, and there are many of them.

Well. We still do it, because we don’t have those JDK backwards-compatibility rules. We have more relaxed ones. But we suggest our users do not implement the interfaces directly themselves, but extend a Default implementation instead, which is empty. For instance ExecuteListener and the corresponding DefaultExecuteListener:

public interface ExecuteListener {
    void start(ExecuteContext ctx);
    void renderStart(ExecuteContext ctx);
    // [...]
}

public class DefaultExecuteListener
implements ExecuteListener {

    @Override
    public void start(ExecuteContext ctx) {}

    @Override
    public void renderStart(ExecuteContext ctx) {}

    // [...]
}

So, Default* is a prefix that is commonly used to provide a single public implementation that API consumers can use and instantiate, or SPI implementors can extend – without risking backwards-compatibility issues. It’s pretty much a workaround for Java 6 / 7’s lack of interface default methods, which is why the prefix naming is even more appropriate.

Java 8 Version of this rule

In fact, this practice makes it evident that a “good” rule to specify Java-8 compatible SPIs is to use interfaces and to make all methods default with an empty body. If jOOQ didn’t support Java 6, we’d probably specify our ExecuteListener like this:

public interface ExecuteListener {
    default void start(ExecuteContext ctx) {}
    default void renderStart(ExecuteContext ctx) {}
    // [...]
}

*Utils or *Helper

OK, so here’s one for the mock/testing/coverage experts and aficionados out there.

It’s TOTALLY OK to have a “dump” for all sorts of static utility methods. I mean, of course you could be a member of the object-orientation police. But…

Please. Don’t be “that guy”! 馃檪

So, there are various techniques of identifying utility classes. Ideally, you take a naming convention and then stick to it. E.g. *Utils.

From our perspective, ideally you’d even just dump all utility methods that are not stricly bound to a very specific domain in a single class, because frankly, when did you last appreciate having to go through millions of classes to find that utility method? Never. We have org.jooq.impl.Utils. Why? Because it’ll allow you to do:

import static org.jooq.impl.Utils.*;

This then almost feels as if you had something like “top-level functions” throughout your application. “global” functions. Which we think is a nice thing. And we totally don’t buy the “we can’t mock this” argument, so don’t even try starting a discussion

Discussion

… or, in fact, let’s do start a discussion. What are your techniques, and why? Here are a couple of reactions to Tom Bujok’s original Tweet, to help get you started:

Let’s go 馃槈

Don’t Miss out on Writing Java 8 SQL One-Liners with jOO位 or jOOQ


More and more people are catching up with the latest update to our platform by adopting functional programming also for their businesses.

At Data Geekery, we’re using Java 8 for our jOOQ integration tests, as using the new Streams API with lambda expressions makes generating ad-hoc test data so much easier.

However, we don’t feel that the JDK offers as much as it could, which is why we have also implemented and open-sourced jOO位, a small utility library that patches those short-comings.

Note, we don’t aim to replace more sophisticated libraries like functionaljava. jOO位 is really just patching short-comings.

Putting lambdas to work with jOO位 or jOOQ

I’ve recently encountered this Stack Overflow question, which asked for streaming a result set with all columns into a single list. For example:

Input

+----+------------+------------+
| ID | FIRST_NAME | LAST_NAME  |
+----+------------+------------+
|  1 | Joslyn     | Vanderford |
|  2 | Rudolf     | Hux        |
+----+------------+------------+

Output

1
Joslyn
Vanderford
2
Rudolf
Hux

This is a typical school-book example for using functional programming rather than an iterative solution:

Iterative solution

ResultSet rs = ...;
ResultSetMetaData meta = rs.getMetaData();

List<Object> list = new ArrayList<>();

while (rs.next()) {
    for (int i = 0; i < meta.getColumnCount(); i++) {
        list.add(rs.getObject(i + 1));
    }
}

Truth is, the iterative solution isn’t all that bad, but let’s learn how this could be done with functional programming.

Using jOO位

We’re using jOO位 for this example for a couple of reasons:

  • JDBC didn’t really adopt the new features. There is no simple ResultSet to Stream conversion, even if there should be.
  • Unfortunately, the new functional interfaces do not allow for throwing checked exceptions. The try .. catch blocks inside lambdas don’t exactly look nice
  • Interestingly, there is no way of generating a finite stream without also implementing an Iterator or Spliterator

So, here’s the plain code:

ResultSet rs = ...;
ResultSetMetaData meta = rs.getMetaData();

List<Object> list =
Seq.generate()
   .limitWhile(Unchecked.predicate(v -> rs.next()))
   .flatMap(Unchecked.function(v -> IntStream
       .range(0, meta.getColumnCount())
       .mapToObj(Unchecked.intFunction(i ->
           rs.getObject(i + 1)
       ))
   ))
   .toList()

So far, this looks about as verbose (or a bit more) than the iterative solution. As you can see, a couple of jOO位 extensions were needed here:

// This generate is a shortcut to generate an
// infinite stream with unspecified content
Seq.generate()

// This predicate-based stream termination
// unfortunately doesn't exist in the JDK
// Besides, the checked exception is wrapped in a
// RuntimeException by calling Unchecked.wrapper(...)
   .limitWhile(Unchecked.predicate(v -> rs.next()))

// Standard JDK flatmapping, producing a "nested"
// stream of column values for the "outer" stream
// of database rows
   .flatMap(Unchecked.function(v -> IntStream
       .range(0, meta.getColumnCount())
       .mapToObj(Unchecked.intFunction(i ->
           rs.getObject(i + 1)
       ))
   ))

// This is another convenience method that is more
// verbose to write with standard JDK code
   .toList()

Using jOOQ

jOOQ has even more convenience API to operate on result records of your SQL statement. Consider the following piece of logic:

ResultSet rs = ...;

List<Object> list =
DSL.using(connection)
   .fetch(rs)
   .stream()
   .flatMap(r -> Arrays.stream(r.intoArray()))
   .collect(Collectors.toList());

Note that the above example is using standard JDK API, without resorting to jOO位 for convenience. If you want to use jOO位 with jOOQ, you could even write:

ResultSet rs = ...;

List<Object> list = 
Seq.seq(DSL.using(connection).fetch(rs))
   .flatMap(r -> Arrays.stream(r.intoArray()))
   .toList();

Easy? I would say so! Let’s remember that this example:

  • Fetches a JDBC ResultSet into a Java Collection
  • Transforms each record in the result set into an array of column values
  • Transforms each array into a stream
  • Flattens that stream into a stream of streams
  • Collects all values into a single list

Whew!

Conclusion

We’re heading towards exciting times! It will take a while until all Java 8 idioms and functional thinking will feel “natural” to Java developers, also in the enterprise.

The idea of having a sort of data source that can be configured with pipelined data transformations expressed as lambda expressions to be evaluated lazily is very compelling, though. jOOQ is an API that encapsulates SQL data sources in a very fluent and intuitive way, but it doesn’t stop there. jOOQ produces regular JDK collections of records, which can be transformed out-of-the-box via the new streams API.

We believe that this will drastically change the way the Java ecosystem will think about data transformation. Stay tuned for more examples on this blog!