DSL Usage

Aside from this brief usage guide, please consult the DSL examples and the unit tests.

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.

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

Please see the examples 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.

List<Product> restock = db.from(p).where("unitsInStock=? and productName like ? order by productId", 0, "Chef%").select();

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:

Make sure to select * in your query otherwise db.buildObjects() will throw a RuntimeException
There is no model class type checking nor field type checking.

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

Or if you want access to the raw ResultSet before building your model object instances...

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

Compound Nested Conditions

It is possible to specify type-safe compound nested conditions in your WHERE clauses using And and Or statements.

final Customer model = new Customer();

// SELECT * FROM CUSTOMERS
//    WHERE customerId IS NOT NULL
//    AND region IS NOT NULL
//    AND (
//      region = 'LA' OR region = 'CA'
//    )
List<Customer> regionals = db.from(model)
      .where(model.customerId).isNotNull()
      .and(model.region).isNotNull()
      .and(new Or<Customer>(db, model) {{
            or(model.region).is("LA");
            or(model.region).is("CA");
      }});

Finding Matches for a List of Values

You can use SQL's IN WHERE clause with the oneOf or noneOf conditions.

final Customer model = new Customer();

List<Customer> regionals = db.from(model).where(model.region).oneOf("CA", "LA");
List<Customer> others = db.from(model).where(model.region).noneOf("CA", "LA");

Read-only Views

View model classes can inherit their field definitions from a parent table model class.

@IQView(name = "AnnotatedProductViewInherited", inheritColumns = true)
public class ProductViewFromQuery extends ProductAnnotationOnly {

    public String unmappedField;

    @IQColumn(name = "id")
    public Long productId;

    public String toString() {
        return productName + " (" + productId + ")";
    }
}

You can then create or replace the VIEW in the database using a fluent syntax.

// create view from query
ProductAnnotationOnly product = new ProductAnnotationOnly();
db.from(product).where(product.productId).exceeds(2L)
    .and(product.productId).atMost(7L).createView(ProductViewFromQuery.class);

// select from the created view
ProductViewFromQuery view = new ProductViewFromQuery();
List<ProductViewFromQuery> products = db.from(view).select();

// replace the view
db.from(product).where(product.productId).exceeds(3L)
    .and(product.productId).atMost(8L).replaceView(ProductViewFromQuery.class);

// select from the replaced view
products = db.from(view).select();

Natural Syntax

!!! warning Not Actively Developed

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:

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

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.

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

IciqlLogger.activeConsoleLogger();
IciqlLogger.deactiveConsoleLogger();

SLF4J Logging

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

Custom Logging

IciqlListener custom = new IciqlListener() {
    public void logIciql(StatementType type, String statement) {
        // do log
    }
};
IciqlLogger.registerListener(custom);
IciqlLogger.unregisterListener(custom);

Understanding Aliases and Model Classes

Consider the following example:

Product p = new Product();
List<Product> restock = db.from(p).where(p.unitsInStock).is(0).select();

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.

Alias instances are NOT thread-safe and must not be used concurrently.
Alias instances have no other purpose than to provide a compile-time/runtime map of your table.
If you inspected an alias instance after using one you would find that it's fields have been assigned numeric values.
These values are assigned from a static counter in com.iciql.Utils.newObject() during execution of the db.from() method.For Object fields, these values are meaningless since objects are mapped by reference.
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 variable if you do not want to keep instantiating alias instances. A utility function is included for easily creating ThreadLocal variables.

final ThreadLocal<Product> p = Utils.newThreadLocal(Product.class);
db.from(p.get()).select();

Best Practices

Close your Db instances when you are done with them, this closes the underlying connection or directs the pool to "close" the connection.
Aliases instances are not thread-safe so DO NOT SHARE an alias!
Consider using a ThreadLocal alias instance with the com.iciql.Utils.newThreadLocal() utility method.

Not Thread-Safe

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

Thread-Safe

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly