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.