Skip navigation links

Package org.jdbi.v3.sqlobject

SQL Objects

See: Description

Package org.jdbi.v3.sqlobject Description

SQL Objects

The sql objects API allows for declarative definition of interfaces which will handle the generation of sql 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.

Skip navigation links

Copyright © 2018. All rights reserved.