SQL Objects

The SQLObject API allows for declarative definition of interfaces which will handle the generation of statements and queries on your behalf when needed. Take the following interface:

    public interface TheBasics {
        @SqlUpdate("insert into something (id, name) values (:id, :name)")
        int insert(@BindBean Something something);

        @SqlQuery("select id, name from something where id = :id")
        Something findById(@Bind("id") long id);
    }

First, install the SQL Object plugin:

    Jdbi jdbi = Jdbi.create(dataSource);
    jdbi.installPlugin(new SqlObjectPlugin());

You can obtain an instance of TheBasics via one of three means.

Default Methods

You can declare default methods on your interface, which can call other methods of the interface:

    public interface DefaultMethods {
        @SqlQuery("select * from folders where id = :id")
        Folder getFolderById(int id);

        @SqlQuery("select * from documents where folder_id = :folderId")
        List<Document> getDocuments(int folderId);

        default Node getFolderByIdWithDocuments(int id) {
            Node result = getById(id);
            result.setChildren(listByParendId(id));
            return result;
        }
    }

Mixin Interfaces

All SQL objects implicitly implement the SqlObject interface (whether you declare it or not), which provides a getHandle() method. This is handy when you need to "drop down" to the core API for scenarios not directly supported by SQL Object:

    public interface UsingMixins extends SqlObject {
        @RegisterBeanMapper(value={Folder.class, Document.class}, prefix={"f", "d"})
        default Folder getFolder(int id) {
            return getHandle().createQuery(
                "select f.id f_id, f.name f_name, " +
                    "d.id d_id, d.name d_name, d.contents d_contents " +
                    "from folders f left join documents d " +
                    "on f.id = d.folder_id " +
                    "where f.id = :folderId")
                .bind("folderId", id)
                .reduceRows(Optional.<Folder>empty(), (folder, row) -> {
                    Folder f = folder.orElseGet(() -> row.getRow(Folder.class));
                    if (row.getColumn("d_id", Integer.class) != null) {
                        f.getDocuments().add(row.getRow(Document.class));
                    }
                    return Optional.of(f);
                });
        }
    }

Any interface that extends SqlObject can be used as a SQL Object mixin, provided all of its methods have a SQL method annotation (e.g. @SqlQuery, or are interface default methods.

Transactions

Any SQL Object method annotation with @Transaction will be executed within a transaction. This is most commonly used on interface default methods to roll up multiple method calls:

    public interface TransactionAnnotation {
        @SqlUpdate("insert into folders (id, name) values (:id, :name)")
        void insertFolder(@BindBean Folder folder)

        @SqlBatch("insert into documents (id, folder_id, name, content) " +
            "values (:id, :folderId, :name, :content)")
        void insertDocuments(int folderId, @BindBean List<Document> documents);

        @Transaction
        default void insertFolderWithDocuments(Folder folder) {
            insertFolder(folder);
            insertDocuments(folder.getId(), folder.getDocuments());
        }
    }

Jdbi also provides a Transactional mixin interface. When a SQL Object type extends this interface, callers may invoke method from that interface to manage transactions:

    public interface TransactionalWithDefaultMethods extends Transactional {
        @SqlUpdate("insert into folders (id, name) values (:id, :name)")
        void insertFolder(@BindBean Folder folder)

        @SqlBatch("insert into documents (id, folder_id, name, content) " +
            "values (:id, :folderId, :name, :content)")
        void insertDocuments(int folderId, @BindBean List<Document> documents);
    }

Thus:

    TransactionalWithDefaultMethods dao = jdbi.onDemand(TransactionalWithDefaultMethods.class);
    dao.inTransaction(self -> {
      self.insert(folder);
      self.insertDocuments(folder.getId(), folder.getDocuments());
    });

Note: use caution combining Transactional with on-demand SQL Objects. The only methods considered safe to call with on-demand SQL Objects are inTransaction or useTransaction.