SQL DSL Examples

Here are some examples of using the Iciql SQL DSL.

Select Statements#

// 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 distinct customerId from customers where region='WA'
Customer c = new Customer();
List<String> customerIds = db.from(c).where(c.region).is("WA").selectDistinct(c.customerId);

public static class ProductPrice {
    public String productName;
    public String category;
    public Double price;

// select with generation of new anonymous inner class
List<ProductPrice> productPrices =
    .select(new ProductPrice() {{
        productName = p.productName;
        category = p.category;
        price = p.unitPrice;

// select category, COUNT(*) from products where category != 'Seafood' group by category
List<ValueCount<String>> categoryCounts =

Insert Statements#

// single record insertion

// single record insertion with primary key retrieval
Long key = db.insertAndGetKey(singleProduct);

// batch record insertion

// batch insertion with primary key retrieval
List<Long> myKeys = db.insertAllAndGetKeys(list);

Update Statements#

// single record update

// batch record updates

// update query

// reusable, parameterized update query
String q = db.from(p).set(p.productName).toParameter().where(p.productId).is(1).toSQL();
db.executeUpdate(q, "Lettuce");

Upsert/Merge Statements#

The Upsert or Merge methods will insert a new object if the primary key does not already exist or will update the record for the primary key.

Product pChang = db.from(p).where(p.productName).is("Chang").selectFirst();
pChang.unitPrice = 19.5;
pChang.unitsInStock = 16;

Delete Statements#

// single record deletion

// batch record deletion

// delete query

Inner Join Statements#

final Customer c = new Customer();
final Order o = new Order();

List<Customer> customersWithLargeOrders =
    where(o.total).greaterThan(new BigDecimal("500.00")).

List<CustOrder> orders =
    where(o.total).lessThan(new BigDecimal("500.00")).
    select(new CustOrder() {{
        customerId = c.customerId;
        orderId = o.orderId;
        total = o.total;

View Statements#

// the view named "ProductView" is created from the "Products" table
@IQView(viewTableName = "Products")
public class ProductView {

    @IQConstraint("this >= 200 AND this < 300")
    Long id;

    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
    @IQConstraint("this >= 200 AND this < 300")
    Long id;

    // inherited from Products
    //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();

// now replace the view with a variation

// now drop the view from the database

Dynamic Queries#

Dynamic queries skip all field type checking and, depending on which approach you use, may skip model class/table name checking too.

// 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);