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 =
    db.from(p)
    .orderBy(p.productId)
    .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 =
    db.from(p)
    .where(p.category).isNot('Seafood')
    .selectCount(p.category);

Insert Statements

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

Update Statements

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

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;
db.merge(pChang);

Delete Statements

// single record deletion
db.delete(singleProduct);  

// batch record deletion
db.deleteAll(myProducts);

// delete query
db.from(p).where(p.productId).atLeast(10).delete();

Inner Join Statements

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

View Statements

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

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