Data Access Object (DAOs)

JDBI brings an interesting feature to the table with dynamic generation of an annotation-based, partially type-safe DAO. This is a great idea and one that Iciql has absorbed into it’s featureset.

The Iciql implementation is quite different, but the usage is very similar. Iciql does not aim to recreate all features and capabilities of JDBI’s DAO.

Instantiating a DAO#

Once you have a Db instance, you may generate a dynamic DAO instance which is backed by it.

Db db = Db.open("jdbc:h2:mem:iciql");
db.open(MyDao.class);

A minimal DAO is an interface that extends the Dao interface. This gives your DAO instance access to the standard Iciql CRUD methods for interacting with your database models, the db() method to retrieve the underlying db instance, and the close() method for closing the underlying JDBC connection.

public interface MyDao extends Dao {
}

Your Dao instance is also auto-closable so you may use the Java 7 try-with-resources syntax.

Note: You never implement the DAO methods - that is taken care of for you through the magic of java.lang.reflect.Proxy and com.iciql.DaoProxy.

@SqlQuery#

DAO queries are method declarations annotated with @SqlQuery.

Return types#

  1. An @SqlQuery method must specify a non-void return a type.
  2. The return type may not be a java.util.Collection, but it may be an array [] type. This is due to generic type erasure by javac whereas arrays preserve their component type information.
    NOTE: Iciql will always return a 0-length array instead of a null when there are no results so you won’t have to worry about null checks.
  3. An @SqlQuery method may specify a data type adapter using the @TypeAdapter annotation if the returned value is a field, not a row.
Returning a field with @TypeAdapter#

Normally, Iciql will map the fields in a query ResultSet to your return type object. However, if you are querying a single field from a table then you may specify a @TypeAdapter on an @SqlQuery method allowing you to deserialize complex data into an object.

For example, if you are using the Postgres JSON/JSONB column type in your table then you might want to directly deserialize the raw JSON stored in Postgres into an object rather than just retrieving the JSON document and manually transforming it. You can use a @TypeAdapter to perform this work for you.

Method Argument->Statement Parameter mapping#

@SqlQuery supports 6 techniques for mapping method arguments to statement parameters.

  1. :? where the method argument order implicitly determines statement parameter order. This is similar to a PreparedStatement.
  2. :arg0 where you specify the 0-based index of the method argument.
  3. :1 where you specify the 1-based index of the method argument.
  4. :name automatic Java 8 method parameter naming, assuming you are compiling on Java 8 with the -parameters javac flag.
  5. @Bind("name") + :name argument annotation where you explicitly name the statement parameter.
  6. @BindBean("prefix") + :prefix.property argument annotation which flags the argument as a JavaBean. This allows you to access JavaBean properties from your statement.
    NOTE: If the prefix is empty, your JavaBean properties will be directly accessible. (e.g. :property not :p.property)

Example @SqlQuery usage#

public interface MyDao extends Dao {

    @SqlQuery("select * from Product")
    Product [] getAllProducts();

    // Named parameters
    @SqlQuery("select * from Product where productId = :id")
    Product getProduct(@Bind("id") long id);

    // Reflection-style 0-indexed args
    @SqlQuery("select * from Product where productId = :arg0")
    Product getProduct2(long id);

    // JDBC-style 1-indexed parameters
    @SqlQuery("select * from Product where productId = :1")
    Product getProduct2(long id);

    // If you are compiling on Java 8 with -parameters
    @SqlQuery("select * from Product where productId = :id")
    Product getProduct2(long id);

    // demonstrates how to use bean binding
    @SqlQuery("select productId from Product where category = :p.category and unitsInStock >= :p.unitsInStock")
    long [] getSimilarInStockItemIds(@BindBean("p") Product p);

    // You can extract a field with full standard type mapping
    @SqlQuery("select orderDate from Orders order by orderDate desc limit 1")
    Date getMostRecentOrderDate();

    // You can extract a field that requires a data type adapter (e.g. a Postgres JSON/JSONB, BLOB, etc)
    @SqlQuery("select invoice from Invoices order by received desc limit 1")
    @TypeAdapter(InvoiceAdapterImpl.class)
    Invoice getMostRecentInvoice();

}

@SqlStatement#

DAO statements are method declarations annotated with @SqlStatement.

Return types#

Statements to now return a ResultSet so @SqlStatement methods have three acceptable return types:

  1. void
  2. boolean, if the affected row count is non-zero, true is returned, otherwise false
  3. int, returns the affected row count

@TypeAdapter may not be annotated on a @SqlStatement method. However it may be used on the method arguments.

Method Argument->Statement Parameter mapping#

The parameter mapping rules are exactly the same as for @SqlQuery.

Example @SqlStatement usage#

public interface MyDao extends Dao {

    // this statement does not return anything
    @SqlStatement("update Product set productName = :name where productId = :id")
    void setProductName(@Bind("id") long id, @Bind("name") String name);

    // this statement returns true if at least one row was affected
    @SqlStatement("update Product set productName = :name where productId = :id")
    boolean renameProduct(@Bind("id") long id, @Bind("name") String name);

    // this statement returns the number of affected rows
    @SqlStatement("update Product set category = :new where category = :old")
    int renameProductCategory(@Bind("old") String oldCategory, @Bind("new") String newCategory);

    // You can update a field that requires a data type adapter
    @SqlStatement("update Invoices set invoice = :2 where id = :1")
    boolean setInvoice(long id, @TypeAdapter(InvoiceAdapterImpl.class) Invoice invoice);

}

Runtime Mode & External Statements#

Sometimes you may need to specify a slightly different SQL statement for a database engine you might be using in development but not in production. For example, you might develop with H2 and deploy with PostgreSQL.

Being able to switch the DAO statements executed based on the runtime mode would be helpful for some scenarios. Iciql supports this use-case with a DaoStatementProvider and provides three mode options: DEV, TEST, and PROD.

External Statement DAO Example#

public interface MyDao extends Dao {
    @SqlQuery("some.query")
    Product [] getProductsWithRuntimeModeDependentQuery();
}

Db db = Db.open("jdbc:h2:mem:iciql");
// set a classpath statement resource provider
db.setDaoStatementProvider(new DaoClasspathStatementProvider());

// open the dao and retrieve the products
MyDao dao = db.open(MyDao.class);
Product [] products = dao.getProductsWithRuntimeModeDependentQuery();

External Statement Resource Example#

some.query = select * from Products                                       # default statement
%prod.some.query = select * from Products                                 # will be used in PROD mode
%test.some.query = select * from Products where category = 'Beverages'    # will be used in TEST mode
%dev.some.query = select * from Products where category = 'Condiments'    # will be used in DEV mode

DaoClasspathStatementProvider#

Iciql ships with one useful implementation of a DaoStatementProvider: DaoClasspathStatementProvider.

This provider will load a single external statement resource from the classpath, if found. It tries to locate one of the following classpath resources and loads the first one identified using the java.util.Properties class.

  1. /iciql.properties
  2. /iciql.xml
  3. /conf/iciql.properties
  4. /conf/iciql.xml

Every @SqlQuery and @SqlStatement method will ask the DaoStatementProvider for the statement to execute based on the annotation value and the runtime mode. For the DaoClasspathStatementProvider, if the annotation value is not a key in the resource file it is assumed to be a statement and is returned to the DAO object for execution. This allows you to externalize a handful of statements - or all of them if you do not want to hard-code anything.