Implement Dao proxy generation with annotated sql statement execution

This functionality is inspired by JDBI but is not based on it's implementation.

7 files changed, 261 insertions, 38 deletions

diff --git a/src/site/dao.mkd b/src/site/dao.mkd
new file mode 100644
index 0000000..660c7af
--- /dev/null
+++ b/src/site/dao.mkd
@@ -0,0 +1,137 @@
+## Data Access Object (DAO)

+

+[JDBI](http://jdbi.org) 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.

+

+---JAVA---

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

+db.open(MyDao.class);

+---JAVA---

+

+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.

+

+---JAVA---

+public interface MyDao extends Dao {

+}

+---JAVA---

+

+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

+

+---JAVA---

+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();

+

+}

+---JAVA---

+

+### @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

+

+---JAVA---

+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);

+

+}

+---JAVA---

+

diff --git a/src/site/examples.mkd b/src/site/examples.mkd
index d8d3dfd..21dd773 100644
--- a/src/site/examples.mkd
+++ b/src/site/examples.mkd
@@ -1,4 +1,8 @@
-## Select Statements

+## SQL DSL Examples

+

+Here are some examples of using the Iciql SQL DSL.

+

+### Select Statements

 

 ---JAVA---

 // select * from products

@@ -25,7 +29,7 @@ List<ProductPrice> productPrices =
     }});

 ---JAVA---

 

-## Insert Statements

+### Insert Statements

 

 ---JAVA---

 // single record insertion

@@ -41,7 +45,7 @@ db.insertAll(myProducts);
 List<Long> myKeys = db.insertAllAndGetKeys(list);

 ---JAVA---

 

-## Update Statements

+### Update Statements

 

 ---JAVA---

 // single record update

@@ -61,8 +65,9 @@ String q = db.from(p).set(p.productName).toParameter().where(p.productId).is(1).
 db.executeUpdate(q, "Lettuce");

 ---JAVA---

 

-## Merge Statements

-Merge statements currently generate the [H2 merge syntax](http://h2database.com/html/grammar.html#merge).

+### Upsert/Merge Statements

+

+The Upsert or Merge methods will insert a new object if the primary key does not already exist or will update the record for the primary key.

  

 ---JAVA---

 Product pChang = db.from(p).where(p.productName).is("Chang").selectFirst();

@@ -71,7 +76,7 @@ pChang.unitsInStock = 16;
 db.merge(pChang);

 ---JAVA---

 

-## Delete Statements

+### Delete Statements

 

 ---JAVA---

 // single record deletion

@@ -84,7 +89,7 @@ db.deleteAll(myProducts);
 db.from(p).where(p.productId).atLeast(10).delete();

 ---JAVA---

 

-## Inner Join Statements

+### Inner Join Statements

 

 ---JAVA---

 final Customer c = new Customer();

@@ -108,7 +113,7 @@ List<CustOrder> orders =
     }});

 ---JAVA---

 

-## View Statements

+### View Statements

 

 ---JAVA---

 // the view named "ProductView" is created from the "Products" table

@@ -162,7 +167,7 @@ db.from(p).where(p.id).atLeast(250L).and(p.id).lessThan(350L).replaceView(Produc
 db.dropView(ProductViewInherited.class);

 ---JAVA---

 

-## Dynamic Queries

+### Dynamic Queries

 

 Dynamic queries skip all field type checking and, depending on which approach you use, may skip model class/table name checking too.

 

diff --git a/src/site/index.mkd b/src/site/index.mkd
index bddebd7..a79233c 100644
--- a/src/site/index.mkd
+++ b/src/site/index.mkd
@@ -5,9 +5,9 @@ iciql **is**...
 - a model-based, database access wrapper for JDBC

 - for modest database schemas and basic statement generation

 - for those who want to write code, instead of SQL, using IDE completion and compile-time type-safety

-- small (200KB) with debug symbols and no runtime dependencies

+- small (225KB) with debug symbols and no runtime dependencies

 - pronounced *icicle* (although it could be French: *ici ql* - here query language)

-- a friendly fork of the H2 [JaQu][jaqu] project

+- a friendly fork of the H2 [JaQu][jaqu] subproject

 

 iciql **is not**...

 

@@ -15,26 +15,73 @@ iciql **is not**...
 - designed to compete with more powerful database query tools like [jOOQ][jooq] or [QueryDSL][querydsl]

 - designed to compete with enterprise [ORM][orm] tools like [Hibernate][hibernate] or [mybatis][mybatis]

 

-### Example Usage

-<table class="table">

-<tr>

-<th>iciql</th><th>sql</th>

-</tr>

-<tr>

-<td>

+### fluent, type-safe SQL DSL with rich object mapping

+

+Born from the unfinished [JaQu][jaqu] subproject of H2 in August 2011, Iciql has advanced the codebase & DSL greatly.  It supports more SQL syntax, more SQL data types, and all standard JDBC object types.

+

 ---JAVA---

-Product p = new Product();

-List<Product> restock = db.from(p).where(p.unitsInStock).is(0).select();

-List<Product> all = db.executeQuery(Product.class, "select * from products");

+try (Db db = Db.open("jdbc:h2:mem:iciql")) {

+

+    db.insertAll(Product.getList());

+    Product p = new Product();

+    List<Product> restock = db.from(p).where(p.unitsInStock).is(0).select();

+    List<Product> all = db.executeQuery(Product.class, "select * from products");

+    

+}

 ---JAVA---

 

-</td><td>

-<br/>

-select * from products p where p.unitsInStock = 0<br/>

-select * from products

-</td>

-</tr>

-</table>

+### dynamic, annotated DAO with standard crud operations

+

+Inspired by [JDBI](http://jdbi.org), Iciql offers a similar Dao feature.  There are some clear benefits to using SQL directly rather than SQL-through-a-DSL so use them both where it makes the most sense for your need.

+

+---JAVA---

+// Define your DAO with SQL annotations and optional type adapters

+public interface MyDao extends Dao {

+

+    @SqlQuery("select * from Product where unitsInStock = 0")

+    Product[] getProductsOutOfStock();

+

+    @SqlQuery("select * from Product where productId = :id")

+    Product getProduct(@Bind("id") long id);

+ 

+    // retrieve a custom type from the matched row in the Invoices table

+    @SqlQuery("select invoice from Invoices where id = :arg0")

+    @InvoiceAdapter

+    Invoice getInvoice(long id);

+

+    // retrieve a custom type from the matched row in the Invoices table

+    @SqlQuery("select invoice from Invoices where id = :p.invoiceId")

+    @InvoiceAdapter

+    Invoice getInvoice(@BindBean("p") Product product);

+

+    // update a custom type for the matched row in the Invoices table

+    @SqlStatement("update Invoices set invoice = :2 where id = :1")

+    boolean updateInvoice(long id, @InvoiceAdapter Invoice invoice);

+

+}

+

+// Define a type adapter annotation for the Invoice object

+@Retention(RetentionPolicy.RUNTIME)

+@Target({ ElementType.METHOD, ElementType.FIELD, ElementType.PARAMETER })

+@TypeAdapter(InvoiceAdapterImpl.class)

+public @interface InvoiceAdapter { }

+

+// Crate a DAO instance with your Db and work more clearly

+try (Db db = Db.open("jdbc:h2:mem:iciql")) {

+

+    MyDao dao = db.open(MyDao.class);

+    dao.insertAll(Product.getList());

+    Product[] outofstock = dao.getProductsOutOfStock();

+    Product p = dao.getProduct(1);

+    Invoice i123 = dao.getInvoice(123);

+    i123.approved = true;

+    dao.updateInvoice(123, i123);

+    

+    // use the underlying Db instance for full-power

+    dao.db().dropTable(Product.class);

+

+}

+---JAVA---

 

 ### Supported Databases (Unit-Tested)

 - [H2](http://h2database.com) ${h2.version}

diff --git a/src/site/jaqu_comparison.mkd b/src/site/jaqu_comparison.mkd
index e7afbf8..2740517 100644
--- a/src/site/jaqu_comparison.mkd
+++ b/src/site/jaqu_comparison.mkd
@@ -20,6 +20,7 @@ This is an overview of the fundamental differences between the original JaQu pro
 <tr><td>BETWEEN</td><td>syntax for specifying a BETWEEN x AND y clause</td><td>--</td></tr>

 <tr><td>(NOT) IN</td><td>syntax (oneOf, noneOf) for specifying a (NOT) IN clause</td><td>--</td></tr>

 <tr><td>compound nested conditions</td><td>WHERE (x = y OR x = z) AND (y = a OR y = b)</td><td>--</td></tr>

+<tr><td>dynamic DAOs</td><td>DAO interfaces with annotated statements may be declared and dynamically generated</td><td>--</td></tr>

 <tr><th colspan="3">types</th></tr>

 <tr><td>primitives</td><td>fully supported</td><td>--</td></tr>

 <tr><td>enums</td><td>fully supported</td><td>--</td></tr>

diff --git a/src/site/model_classes.mkd b/src/site/model_classes.mkd
index 747c094..8c29782 100644
--- a/src/site/model_classes.mkd
+++ b/src/site/model_classes.mkd
@@ -1,4 +1,4 @@
-## Model Classes

+## Table Model Classes

 A model class represents a single table within your database.  Fields within your model class represent columns in the table.  The object types of your fields are reflectively mapped to SQL types by iciql at runtime.

 

 Models can be manually written using one of three approaches: *annotation configuration*, *interface configuration*, or *POJO configuration*.  All approaches can be used within a project and all can be used within a single model class, although that is discouraged.

@@ -13,7 +13,7 @@ Alternatively, model classes can be automatically generated by iciql using the m
 4. Only the specified types are supported.  Any other types are not supported.

 5. Triggers, views, and other advanced database features are not supported.

 

-### Supported Data Types

+### Standard Supported Data Types

 

 ---NOMARKDOWN---

 <table class="table">

diff --git a/src/site/table_versioning.mkd b/src/site/table_versioning.mkd
index 2e95aaa..480dd22 100644
--- a/src/site/table_versioning.mkd
+++ b/src/site/table_versioning.mkd
@@ -26,4 +26,34 @@ both of which allow for non-linear upgrades.  If the upgrade method call is succ
 The actual upgrade procedure is beyond the scope of iciql and is your responsibility to implement.  This is simply a mechanism to automatically identify when an upgrade is necessary.

 

 **NOTE:**<br/>

-The database entry of the *iq_versions* table is specified as SCHEMANAME='' and TABLENAME=''.
\ No newline at end of file
+The database entry of the *iq_versions* table is specified as SCHEMANAME='' and TABLENAME=''.

+

+### Effective use of Versioning with a DAO

+

+When Iciql identifies that a version upgrade is necessary it will call the appropriate method and give you a `Db` instance.  With the `Db` instance you may open a version-specific [DAO](dao.html) instance that could give you a clean way to define all your upgrade commands.

+

+---JAVA---

+public interface V2Upgrade extends Dao {

+

+    @SqlStatement("ALTER TABLE PRODUCT ADD COLUMN TEST INT DEFAULT 0")

+    void updateProductTable();

+    

+    @SqlStatement("UPDATE PRODUCT SET CATEGORY = :new WHERE CATEGORY = :old"")

+    void renameCategory(@Bind("old") String oldName, @Bind("new") String newName);

+}

+

+public class MyUpgrader implements DbUpgrader {

+

+    public boolean upgradeDatabase(Db db, int fromVersion, int toVersion) {

+    

+        if (2 == toVersion) {

+            V2Upgrade dao = db.open(V2Upgrade.class);

+            dao.updateProductTable();

+            dao.renameCategory("Condiments", "Dressings");

+            return true;

+        }

+        

+        return false;

+    }

+

+---JAVA---
\ No newline at end of file
diff --git a/src/site/usage.mkd b/src/site/usage.mkd
index 21c262e..b714512 100644
--- a/src/site/usage.mkd
+++ b/src/site/usage.mkd
@@ -1,15 +1,17 @@
-## Usage

+## SQL DSL Usage

 

-Aside from this brief usage guide, please consult the [examples](examples.html), the [javadoc](javadoc.html) and the [source code](${project.scmUrl}).

+Aside from this brief usage guide, please consult the [DSL examples](examples.html), the [javadoc](javadoc.html) and the [source code](${project.scmUrl}).

 

 ### Instantiating a Db

 

 Use one of the static utility methods to instantiate a Db instance:

 

-    Db.open(String url, String user, String password);

-    Db.open(String url, String user, char[] password);

-    Db.open(Connection conn);

-    Db.open(DataSource dataSource);

+---JAVA---

+Db.open(String url, String user, String password);

+Db.open(String url, String user, char[] password);

+Db.open(Connection conn);

+Db.open(DataSource dataSource);

+---JAVA---

  

 ### Compile-Time Statements

 

@@ -144,11 +146,12 @@ products = db.from(view).select();
 

 ### Natural Syntax

 

-<span class="warning">work-in-progress</span>

+<span class="alert alert-warning">Not Actively Developed</span>

 

 The original JaQu source offers partial support for Java expressions in *where* clauses.

 

 This works by decompiling a Java expression, at runtime, to an SQL condition.  The expression is written as an anonymous inner class implementation of the `com.iciql.Filter` interface.

+

 A proof-of-concept decompiler is included, but is incomplete.

 

 The proposed syntax is: