Java database access has never been easier.

Java Database Connectivity (JDBC) is inescapable if you want to talk to a SQL database from Java. Unfortanately, JDBC is an API that is absolutely no fun to use!

MDBI is a wrapper around JDBC that takes the pain away, loosely inspired by JDBI, Benedict Elliott-Smith's Fetch library for Java and Harriet Cooper's Phi library for Scala (both, sadly, proprietary).

The basics

Getting started with MDBI is easy: all you need is a javax.sql.DataSource or Connection:

final Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
MDBI.of(conn).execute(sql("create table people (name text, age int)"));

As well as executing raw SQL, it is of course possible to interpolate Java objects into the query:

final String name = "Michael O'Hare";
MDBI.of(conn).execute(sql("insert into people (name, age) values (").$(name).sql(", 30)"));

To get back Java objects from the database, simply use a "query" method rather than "execute":

final int age = MDBI.of(conn).queryFirst(sql("select age from people"), int.class);
println(age); // 30

NULL safety

Most people who work with JDBC have been burned at some point by the fact that it silently turns NULLs in the database into zeroes, which is almost never what you want. MDBI removes this misfeature, so the following query actually throws a NullPointerException:

MDBI.of(conn).queryFirst(sql("select null"), int.class);

Of course, you can still retrieve nulls if you explicitly ask for them:

final Integer nully = MDBI.of(conn).queryFirst(sql("select null"), Integer.class);
println(nully); // null

Note that database NULLs are supported when retrieving primitive doubles and floats, where they can be cleanly mapped to NaNs:

final double nullyDouble = MDBI.of(conn).queryFirst(sql("select null"), double.class);
println(nullyDouble); // NaN

Batch update

Batch statements are of course fully supported:

final List<String> names = Arrays.asList("Fry", "Leela");
final List<Integer> ages = Arrays.asList(1025, 25);
MDBI.of(conn).updateBatch(sql("insert into people (name, age) values (")
                                        .$s(names).sql(",").$s(ages).sql(")"));

You can even mix batched and non-batched bits of the query:

final List<String> moreNames = Arrays.asList("Foo", "Bar");
final int anotherAge = 13;
MDBI.of(conn).updateBatch(sql("insert into people (name, age) values (")
                                        .$s(moreNames).sql(",").$(anotherAge).sql(")"));

Conveniences

MDBI has built-in support for IN clauses:

final List<Integer> foundAges = MDBI.of(conn).queryList(
                     sql("select age from people where name ").in("Fry", "Foo"), int.class);
println(foundAges); // [1025, 13]

These IN clauses work properly with empty argument lists, even if the database does not normally support nullary IN clauses (and most databases don't):

final int count = MDBI.of(conn).queryFirst(
                        sql("select count(*) from people where name not ").in(), int.class);
println(count); // 5

There is also transaction support that's really easy to use (no messing around with the confusing setAutoCommit interface):

try {
    Transactionally.run(conn, () -> {
        MDBI.of(conn).execute(sql("insert into people (name, age) values ('foo', 1)"));
        throw new IllegalArgumentException("Changed my mind!");
    });
} catch (IllegalArgumentException _) {}

final int postTransactionCount = MDBI.of(conn).queryFirst(
                                    sql("select count(*) from people"), int.class);
println(postTransactionCount); // 5

Structured result types

So far we've shown how to retrieve single rows and lists from the datbase. But MDBI has native support for other useful types of result. For example:

final Map<String, Integer> ageMap = MDBI.of(conn).queryMap(
                            sql("select name, age from people"), String.class, int.class);
println(ageMap.get("Fry")); // 1025

One option that is particularly handy is the "matrix":

final Object[] matrix = MDBI.of(conn).query(sql("select name, age from people order by name"),
                                            BatchReads.matrix(String.class, int.class));
final String[] nameColumn = (String[])matrix[0];
final int[] ageColumn = (int[])matrix[1];
println(nameColumn[0] + ": " + ageColumn[1]); // Bar: 13

Alternatively, that can be written as:

final MatrixBatchReadBuilder mrb = MatrixBatchReadBuilder.create();
final Supplier names = mrb.add(sql("name"), String.class);
final Supplier ages = mrb.addInt(sql("int"));
mrb.buildAndExecute(MDBI.of(conn), columns ->
    sql("select ", columns, " from people order by name"));
println(names.get()[0] + ": " + ages.get()[1]); // Bar: 13

Custom type mapping

MDBI has great support for Java primitive types, but it can also be extended with support for your own types. Let's say you have a bean, PersonBean, with Name and Age properties. This works:

final Context ctxt0 = Context.Builder.createDefault()
        .registerRead(PersonBean.class, Reads.bean(PersonBean.class, "Name", "Age"))
        .build();
final PersonBean bean = MDBI.of(ctxt0, conn).queryFirst(
                    sql("select name, age from people order by name"), PersonBean.class);
println(bean.getName()); // Bar

If you don't like beans, that's no problem. There are also strongly-typed interfaces suitable for immutable data types. For example, if we have defined a Person type as follows:

public class Person {
    public final String name;
    public final int age;

    public Person(String name, int age) {
        this.name = name;
        this.age = age;
    }
}

Then we can read this back from the database like so:

final Context ctxt1 = Context.Builder.createDefault()
        .registerRead(Person.class, Reads.tuple(Person.class))
        .build();
final Person person = MDBI.of(ctxt1, conn).queryFirst(
                        sql("select name, age from people order by name"), Person.class);
println(person.name); // Bar

Or even skip the Context entirely:

MDBI.of(ctxt1, conn).queryFirst(
      sql("select name, age from people order by name"), Reads.tuple(Person.class));

Or perhaps even get a Person by running something other than a constructor:

final Person person = MDBI.of(ctxt1, conn).queryFirst(
    sql("select name, age from people order by name"),
    Reads.ofFunction(new Object() {
        public f(String name, int age) { return new Person(name, -age); }      
    }));
println(person.age); // -13

We've shown how you can read your custom types back from the database, but custom types are also usable when you are constructing SQL queries. You just need to use Context.registerWrite instead of Context.registerRead:

final Context ctxt2 = Context.Builder.createDefault()
        .registerWrite(Person.class, TupleWriteBuilder.<Person>create()
                                        .add(String.class, p -> p.name)
                                        .add(int.class,    p -> p.age)
                                        .build())
        .build();
final Person personToSave = new Person("Max", 29);
MDBI.of(ctxt2, conn).execute(
    sql("insert into people (name, age) values (").$(personToSave).sql(")"));

Conclusion

There are lots more features besides:

All of this comes with no runtime dependencies at all -- you only need the JDK.

To get started, grab a jar from Maven Central and check out the JavaDocs.