I just figured out a super-useful feature of JDBI: how to use @Transaction blocks to neatly group a set of related inserts into a single, transactional query.

As an example, suppose you had a DAO like:

public abstract class JdbiDatabaseBackend {
    @SqlUpdate("INSERT INTO users (username, email) VALUES " + 
        "(:username, :email) RETURNING id")
    public abstract int createUser(@Bind("username") String username, 
        @Bind("email") String email);

    @SqlUpdate("INSERT INTO ACCOUNTS (user_id, created) VALUES " +
        "(:user_id, current_timestamp)")
    public abstract void createUserAccount(@Bind("user_id") int userID);
}

and you wanted to chain these two queries together into one logical ‘create-user-and-account’ call:

// In e.g. a Jersey resource:
int userID = databaseBackend.createUser("ahamilton", "ahamilton@treasury.gov");
databaseBackend.createUserAccount(userID);

The above works fine, but I ran into the case where the second query (createUserAccount) could sometimes fail, leaving me with an orphaned row in my Users table. How much better would it be if I could ensure that either both queries succeeded or neither did?

Enter JDBI’s handy @Transaction annotation! Create a new method on your DAO that represents the transaction, and make your queries there:

public abstract class JdbiDatabaseBackend {
    @SqlUpdate("INSERT INTO users (username, email) VALUES " + 
        "(:username, :email) RETURNING id")
    public abstract int createUser(@Bind("username") String username, 
        @Bind("email") String email);

    @SqlUpdate("INSERT INTO ACCOUNTS (user_id, created) VALUES " +
        (:user_id, current_timestamp)")
    public abstract void createUserAccount(@Bind("user_id") int userID);

    @Transaction
    public void createUserAndAccount(String username, String email) {
        int userID = this.createUser("ahamilton", "ahamilton@treasury.gov");
        this.createUserAccount(userID);
    }
}

…replace your previous separate calls to createUser and createUserAccount with a single call to the transactional createUserAndAccount:

// In e.g. a Jersey resource:
databaseBackend.createUserAndAccount("ahamilton", "ahamilton@treasury.gov");

…and you’re good to go! No more orphaned rows, as JDBI will roll back any successful updates inside a method annotated with @Transaction if anything fails.