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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
## Usage
Aside from this brief usage guide, please consult the [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);
### Compile-Time Statements
You compose your statements using the builder pattern where each method call returns an object that is used to specify the next part of the statement. Through clever usage of generics, pioneered by the original JaQu project, compile-time safety flows through the statement.
%BEGINCODE%
Db db = Db.open("jdbc:h2:mem:", "sa", "sa");
db.insertAll(Product.getList());
db.insertAll(Customer.getList());
List<Product> restock =
db.from(p).
where(p.unitsInStock).
is(0).orderBy(p.productId).select();
for (Product product : restock) {
db.from(p).
set(p.unitsInStock).to(25).
where(p.productId).is(product.productId).update();
}
db.close();
%ENDCODE%
Please see the [examples](examples.html) page for more code samples.
### Dynamic Runtime Queries
Iciql gives you compile-time type-safety, but it becomes inconvenient if your design requires more dynamic statement generation. For these scenarios iciql offers some runtime query support through a hybrid approach or a pure JDBC approach.
#### Where String Fragment Approach
This approach is a mixture of iciql and jdbc. It uses the traditional prepared statement *field=?* tokens with iciql compile-time model class type checking. There is no field token type-safety checking.
%BEGINCODE%
List<Product> restock = db.from(p).where("unitsInStock=? and productName like ? order by productId", 0, "Chef%").select();
%ENDCODE%
#### Db.executeQuery Approaches
There may be times when the hybrid approach is still too restrictive and you'd prefer to write straight SQL. You can do that too and use iciql to build objects from your ResultSet, but be careful:
1. Make sure to _select *_ in your query otherwise db.buildObjects() will throw a RuntimeException
2. There is no model class type checking nor field type checking.
%BEGINCODE%
List<Product> allProducts = db.executeQuery(Product.class, "select * from products");
List<Product> restock = db.executeQuery(Product.class, "select * from products where unitsInStock=?", 0);
// parameterized query which can be cached and re-used later
String q = db.from(p).where(p.unitsInStock).isParameter().toSQL();
List<Product> restock = db.executeQuery(Product.class, q, 0);
%ENDCODE%
Or if you want access to the raw *ResultSet* before building your model object instances...
%BEGINCODE%
ResultSet rs = db.executeQuery("select * from products");
List<Product> allProducts = db.buildObjects(Product.class, rs);
// This method ensures the creating statement is closed
JdbcUtils.closeSilently(rs, true);
%ENDCODE%
### Natural Syntax
<span class="warning">work-in-progress</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:
%BEGINCODE%
long count = db.from(co).
where(new Filter() { public boolean where() {
return co.id == x
&& co.name.equals(name)
&& co.value == new BigDecimal("1")
&& co.amount == 1L
&& co.birthday.before(new java.util.Date())
&& co.created.before(java.sql.Timestamp.valueOf("2005-05-05 05:05:05"))
&& co.time.before(java.sql.Time.valueOf("23:23:23"));
}
}).selectCount();
%ENDCODE%
### JDBC Statements, ResultSets, and Exception Handling
Iciql opens and closes all JDBC objects automatically. SQLExceptions thrown during execution of a statement (except for *close()* calls), will be caught, wrapped, and rethrown as an `IciqlException`, which is a RuntimeException.
Iciql does not throw any [checked exceptions](http://en.wikipedia.org/wiki/Exception_handling#Checked_exceptions).
### Statement Logging
Iciql provides a mechanism to log generated statements and warnings to the console, to SLF4J, or to your own logging framework. Exceptions are not logged using this mechanism; exceptions are wrapped and rethrown as `IciqlException`, which is a RuntimeException.
#### Console Logging
%BEGINCODE%
IciqlLogger.activeConsoleLogger();
IciqlLogger.deactiveConsoleLogger();
%ENDCODE%
#### SLF4J Logging
%BEGINCODE%
Slf4jIciqlListener slf4j = new Slf4jIciqlListener();
slf4j.setLevel(StatementType.CREATE, Level.WARN);
slf4j.setLevel(StatementType.DELETE, Level.WARN);
slf4j.setLevel(StatementType.MERGE, Level.OFF);
IciqlLogger.registerListener(slf4j);
IciqlLogger.unregisterListener(slf4j);
%ENDCODE%
#### Custom Logging
%BEGINCODE%
IciqlListener custom = new IciqlListener() {
public void logIciql(StatementType type, String statement) {
// do log
}
};
IciqlLogger.registerListener(custom);
IciqlLogger.unregisterListener(custom);
%ENDCODE%
## Understanding Aliases and Model Classes
Consider the following example:
%BEGINCODE%
Product p = new Product();
List<Product> restock = db.from(p).where(p.unitsInStock).is(0).select();
%ENDCODE%
The Product model class instance named **p** is an *alias* object. An *alias* is simply an instance of your model class that is only used to build the compile-time/runtime representation of your table.
1. *Alias* instances are **NOT** thread-safe and must not be used concurrently.
2. *Alias* instances have no other purpose than to provide a compile-time/runtime map of your table.
3. If you inspected an *alias* instance after using one you would find that it's fields have been assigned numeric values.<br/>These values are assigned from a static counter in `com.iciql.Utils.newObject()` during execution of the *db.from()* method.<p/>For *Object* fields, these values are meaningless since objects are mapped by reference.<br/>For *Primitive* fields these values do matter because primitives are mapped by value. The proper alias is selected as long as the primitive variant methods are used. e.g. db.from(p).where(int).is(Integer).select()
If your statement is a query, like in the above example, iciql will generate new instances of your *alias* model class and return them as a list where each entry of the list represents a row from the JDBC `ResultSet`.
### Why are Aliases not thread-safe?
The _db.from(p)_ call reinstantiates each member field of p. Those reinstantiated fields are then subsequently used in clauses like _where(p.unitsInStock)_. If your *alias* instance is shared concurrently then its highly probable that when _queryA_ executes, _queryC_ has reinstantiated all the *alias* fields and broken _queryA's_ runtime field mapping.
Depending on your design, you might consider using a [ThreadLocal](http://download.oracle.com/javase/6/docs/api/java/lang/ThreadLocal.html) variable if you do not want to keep instantiating *alias* instances. A utility function is included for easily creating ThreadLocal variables.
%BEGINCODE%
final ThreadLocal<Product> p = Utils.newThreadLocal(Product.class);
db.from(p.get()).select();
%ENDCODE%
## Best Practices
1. Close your *Db* instances when you are done with them, this closes the underlying connection or directs the pool to "close" the connection.
2. Aliases instances are not thread-safe so DO NOT SHARE an alias!<br/>Consider using a *ThreadLocal* alias instance with the `com.iciql.Utils.newThreadLocal()` utility method.
<p/>
<table class="table">
<tr><th>Not Thread-Safe</th><th>Thread-Safe</th></tr>
<tr><td>
%BEGINCODE%
final Product p = new Product();
for (int i = 0; i < 5; i++) {
Thread thread = new Thread(new Runnable() {
public void run() {
// from(p) reinstantiates p's fields
db.from(p).select();
}
}, "Thread-" + i);
thread.start();
}
%ENDCODE%
</td><td>
%BEGINCODE%
final ThreadLocal<Product> p = Utils.newThreadLocal(Product.class);
for (int i = 0; i < 5; i++) {
Thread thread = new Thread(new Runnable() {
public void run() {
// a unique p for this thread
db.from(p.get()).select();
}
}, "Thread-" + i);
thread.start();
}
%ENDCODE%
</td></tr>
</table>
|
