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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
## 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---
### 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
---JAVA---
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();
---JAVA---
#### External Statement Resource Example
---FIXED---
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
---FIXED---
#### 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.
|
