1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
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---
|
