blob: d4f98da142753b96be71ddc9ff34a6cc8590f1f1 (
plain)
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
|
## Select Statements
%BEGINCODE%
// select * from products
List<Product> allProducts = db.from(p).select();
// select * from customers where region='WA'
Customer c = new Customer();
List<Customer> waCustomers = db.from(c). where(c.region).is("WA").select();
// select with generation of new anonymous inner class
List<ProductPrice> productPrices =
db.from(p).
orderBy(p.productId).
select(new ProductPrice() {{
productName = p.productName;
category = p.category;
price = p.unitPrice;
}});
%ENDCODE%
## Insert Statements
%BEGINCODE%
// single record insertion
db.insert(singleProduct);
// single record insertion with primary key retrieval
Long key = db.insertAndGetKey(singleProduct);
// batch record insertion
db.insertAll(myProducts);
// batch insertion with primary key retrieval
List<Long> myKeys = db.insertAllAndGetKeys(list);
%ENDCODE%
## Update Statements
%BEGINCODE%
// single record update
db.update(singleProduct);
// batch record updates
db.updateAll(myProducts);
// update query
db.from(p).set(p.productName).to("updated")
.increment(p.unitPrice).by(3.14)
.increment(p.unitsInStock).by(2)
.where(p.productId).is(1).update();
%ENDCODE%
## Merge Statements
Merge statements currently generate the [H2 merge syntax](http://h2database.com/html/grammar.html#merge).
%BEGINCODE%
Product pChang = db.from(p).where(p.productName).is("Chang").selectFirst();
pChang.unitPrice = 19.5;
pChang.unitsInStock = 16;
db.merge(pChang);
%ENDCODE%
## Delete Statements
%BEGINCODE%
// single record deletion
db.delete(singleProduct);
// batch record deletion
db.deleteAll(myProducts);
// delete query
db.from(p).where(p.productId).atLeast(10).delete();
%ENDCODE%
## Inner Join Statements
%BEGINCODE%
final Customer c = new Customer();
final Order o = new Order();
List<CustOrder> orders =
db.from(c).
innerJoin(o).on(c.customerId).is(o.customerId).
where(o.total).lessThan(new BigDecimal("500.00")).
orderBy(1).
select(new CustOrder() {{
customerId = c.customerId;
orderId = o.orderId;
total = o.total;
}});
%ENDCODE%
## Dynamic Queries
Dynamic queries skip all field type checking and, depending on which approach you use, may skip model class/table name checking too.
%BEGINCODE%
// where fragment with object parameters
List<Product> restock = db.from(p).where("unitsInStock=? and productName like ? order by productId", 0, "Chef%").select();
// statement with binding to your model class
List<Product> allProducts = db.executeQuery(Product.class, "select * from products");
// statement with object parameters and binding to your model class
List<Product> restock = db.executeQuery(Product.class, "select * from products where unitsInStock=?", 0);
/**
* If you want to process the intermediate ResultSet
* yourself make sure to use the <i>closeSilently()</i> method
* to ensure the parent statement is closed too.
*/
ResultSet rs = db.executeQuery("select * from products");
List<Product> allProducts = db.buildObjects(Product.class, rs);
JdbcUtils.closeSilently(rs, true);
%ENDCODE%
|
