aboutsummaryrefslogtreecommitdiffstats
path: root/src/site/04_examples.mkd
diff options
context:
space:
mode:
Diffstat (limited to 'src/site/04_examples.mkd')
-rw-r--r--src/site/04_examples.mkd196
1 files changed, 196 insertions, 0 deletions
diff --git a/src/site/04_examples.mkd b/src/site/04_examples.mkd
new file mode 100644
index 0000000..33cb9c4
--- /dev/null
+++ b/src/site/04_examples.mkd
@@ -0,0 +1,196 @@
+## 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();
+
+public static class ProductPrice {
+ public String productName;
+ public String category;
+ public Double price;
+}
+
+// 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();
+
+// reusable, parameterized update query
+String q = db.from(p).set(p.productName).toParameter().where(p.productId).is(1).toSQL();
+db.executeUpdate(q, "Lettuce");
+
+%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<Customer> customersWithLargeOrders =
+ db.from(c).
+ innerJoin(o).on(c.customerId).is(o.customerId).
+ where(o.total).greaterThan(new BigDecimal("500.00")).
+ groupBy(c.customerId).select();
+
+
+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%
+
+## View Statements
+
+%BEGINCODE%
+// the view named "ProductView" is created from the "Products" table
+@IQView(viewTableName = "Products")
+public class ProductView {
+
+ @IQColumn
+ @IQConstraint("this >= 200 AND this < 300")
+ Long id;
+
+ @IQColumn
+ String name;
+}
+
+final ProductView v = new ProductView();
+List<ProductView> allProducts = db.from(v).select();
+
+// this version of the view model "ProductView" inherits table metadata
+// from the Products class which is annotated with IQTable
+@IQView(inheritColumns = true)
+public class ProductView extends Products {
+
+ // inherited BUT replaced to define the constraint
+ @IQColumn
+ @IQConstraint("this >= 200 AND this < 300")
+ Long id;
+
+ // inherited from Products
+ //@IQColumn
+ //String name;
+}
+
+final ProductView v = new ProductView();
+List<ProductView> allProducts = db.from(v).select();
+
+// in this example we are creating a view based on a fluent query
+// and using 2 levels of inheritance. IQConstraints are ignored
+// when using this approach because we are fluently defining them.
+@IQView(inheritColumns = true)
+public class ProductViewInherited extends ProductView {
+
+}
+
+final Products p = new Products();
+db.from(p).where(p.id).atLeast(200L).and(p.id).lessThan(300L).createView(ProductViewInherited.class);
+
+// now replace the view with a variation
+db.from(p).where(p.id).atLeast(250L).and(p.id).lessThan(350L).replaceView(ProductViewInherited.class);
+
+// now drop the view from the database
+db.dropView(ProductViewInherited.class);
+
+%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();
+
+// parameterized query which can be cached and re-used later
+String q = db.from(p).where(p.unitsInStock).isParameter().and(p.productName).likeParameter().orderBy(p.productId).toSQL();
+List<Product> allProducts = db.executeQuery(Product.class, q, 0, "Chef%");
+
+// 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% \ No newline at end of file