Package org.jdbi.v3.sqlobject

package org.jdbi.v3.sqlobject

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.

  • You can pass a lambda to Jdbi. A short-lived instance of the interface will be created, and passed to the lambda. The lambda can make any number of calls to that instance before returning. The lifecycle of the SQL Object instance ends when the lambda returns. 

                jdbi.useExtension(TheBasics.class, theBasics -> theBasics.insert(new Something(1, "Alice")));

            Something result = jdbi.withExtension(TheBasics.class, theBasics -> theBasics.findById(1));

    withExtension returns the value returned by the lambda, whereas useExtension has a void return.

  • You can get an instance attached to a particular handle. The SQL Object's lifecycle ends when the handle is closed. 

                try (Handle handle = jdbi.open()) {
                TheBasics attached = handle.attach(TheBasics.class);
                attached.insert(new Something(2, "Bob");
                Something result = attached.findById(2);
            }

  • Finally, you can request an on-demand instance. On-demand instances have an open-ended lifecycle, as they obtain and releases connections for each method call. They are thread-safe, and may be reused across an application. This is handy when you only need to make single calls at a time. 

                TheBasics onDemand = jdbi.onDemand(TheBasics.class);
            onDemand.insert(new Something(3, "Chris"));
            Something result = onDemand.findById(3);

    There is a performance penalty every time a connection is allocated and released. If you need to make multiple calls in a row to a SQL Object, consider using one of the above options for better performance, instead of on-demand.

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.

  • Class
    Description
    CreateSqlObject
    Use this annotation on a sql object method to create a new sql object with the same underlying handle as the sql object the method is invoked on.
    DecoratorOrder
    Determines the order in which SQL method decorators are invoked.
    GenerateSqlObject
    Decorate a SqlObject type to instruct the jdbi3-generator annotation processor to create a compiled implementation.
    Handler
    Implements the contract of a SQL Object method.
    HandlerDecorator
    Decorates Handler objects with additional behavior.
    HandlerDecorators
    Registry for handler decorators.
    HandlerFactory
    Creates Handler objects for methods annotated with a specific SQL method annotation, which satisfy the contract of that annotation.
    Handlers
    Registry for handler factories, which produce handlers for SQL object methods.
    SingleValue
    Indicate to SqlObject that a type that looks like a container should be treated as a single element.
    SqlMethodDecoratingAnnotation
    Annotation used to identify SQL method decorating annotations.
    SqlObject
    SqlObject base interface.
    SqlObjectFactory
    Creates implementations for SqlObject interfaces.
    SqlObjectPlugin
    Plugin that installs the SqlObject extension.
    SqlObjects
    Configuration class for SQL objects.
    SqlOperation
    Annotation used to identify SQL operation annotations.
    UnableToCreateSqlObjectException
    Thrown when constructing a SqlObject fails.