Home

iciql is...

• a model-based, database access wrapper for JDBC
• for modest database schemas and basic statement generation
• for those who want to write code, instead of SQL, using IDE completion and compile-time type-safety
• small (<250KB) with debug symbols and no runtime dependencies
• pronounced icicle (although it could be French: ici ql - here query language)
• a friendly fork of the H2 JaQu subproject

iciql is not...

• a complete alternative to JDBC
• designed to compete with more powerful database query tools like jOOQ or QueryDSL
• designed to compete with enterprise ORM tools like Hibernate or mybatis

Fluent, type-safe SQL DSL with rich object mapping

Born from the unfinished JaQu subproject of H2 in August 2011, Iciql has advanced the codebase & DSL greatly. It supports more SQL syntax, more SQL data types, and all standard JDBC object types.

try (Db db = Db.open("jdbc:h2:mem:iciql")) {

    db.insertAll(Product.getList());
    Product p = new Product();
    List<Product> restock = db.from(p).where(p.unitsInStock).is(0).select();
    List<Product> all = db.executeQuery(Product.class, "select * from products");
    List<String> names = db.from(p).where(p.unitsInStock).exceeds(0).selectDistinct(p.name);

}

Dynamic, annotated DAO with standard crud operations

Inspired by JDBI, Iciql offers a similar DAO feature. There are some clear benefits to using SQL directly rather than SQL-through-a-DSL so use each one where it makes the mose sense.

// Define your DAO with SQL annotations and optional type adapters
public interface MyDao extends Dao {

    @SqlQuery("select * from Product where unitsInStock = 0")
    Product[] getProductsOutOfStock();

    @SqlQuery("select * from Product where productId = :id")
    Product getProduct(@Bind("id") long id);

    // retrieve a custom type from the matched row in the Invoices table
    @SqlQuery("select invoice from Invoices where id = :arg0")
    @InvoiceAdapter
    Invoice getInvoice(long id);

    // retrieve a custom type from the matched row in the Invoices table
    @SqlQuery("select invoice from Invoices where id = :p.invoiceId")
    @InvoiceAdapter
    Invoice getInvoice(@BindBean("p") Product product);

    // update a custom type for the matched row in the Invoices table
    @SqlStatement("update Invoices set invoice = :2 where id = :1")
    boolean updateInvoice(long id, @InvoiceAdapter Invoice invoice);

}

// Define a type adapter annotation for the Invoice object
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD, ElementType.FIELD, ElementType.PARAMETER })
@TypeAdapter(InvoiceAdapterImpl.class)
public @interface InvoiceAdapter { }

// Create a DAO instance with your Db and work more clearly
try (Db db = Db.open("jdbc:h2:mem:iciql")) {

    MyDao dao = db.open(MyDao.class);
    dao.insertAll(Product.getList());
    Product[] outofstock = dao.getProductsOutOfStock();
    Product p = dao.getProduct(1);
    Invoice i123 = dao.getInvoice(123);
    i123.approved = true;
    dao.updateInvoice(123, i123);

    // use the underlying Db instance for full-power
    dao.db().dropTable(Product.class);

}

Flexible field data types

The Data Type Adapter feature allows you to customize how your SQL column data types map to/from Java objects.

This is very useful for mapping your field domain models to SQL without having to flatten them out to additional columns within your table. In other words, you can use your database as an object store at the column level by implementing a @TypeAdapter (de)serialization step.

You might use this to take advantage of the underlying database's type system. For example, PostgreSQL ships with the compelling JSON/JSONB/XML data types. Iciql provides String and Object adapters to facilitate use of those data types.

Runtime mode support

Mode support allows you to tweak the behavior of your @TypeAdapter and DAO implementations to adapt to runtime conditions such as developing on a different database than you deploy on.

Supported Databases (Unit-Tested)

• H2 1.4
• HSQLDB 2.3
• Derby 10.12
• MySQL 5.6
• PostgreSQL 9.5
• SQLite 3.8

Support for others is possible and may only require creating a simple "dialect" class.

Downloading

As of 2.0.0 iciql is now distributed through Maven Central and it's coordinates have changed slightly.

<dependencies>
    <dependency>
        <groupId>com.gitblit.iciql</groupId>
        <artifactId>iciql</artifactId>
        <version>2.1.1</version>
    </dependency>
</dependencies>

Model Code Generation

You can use exec-maven-plugin to create the model classes.

<plugin>
	<groupId>org.codehaus.mojo</groupId>
	<artifactId>exec-maven-plugin</artifactId>
	<executions>
		<execution>
			<phase>generate-sources</phase>
			<goals>
				<goal>java</goal>
			</goals>
		</execution>
	</executions>
	<configuration>
		<mainClass>com.iciql.util.GenerateModels</mainClass>
		<arguments>
			<argument>-url</argument>
			<argument>jdbc:mysql://localhost:3306/sakila</argument>
			<argument>-user</argument>
			<argument>root</argument>
			<argument>-password</argument>
			<argument>*********</argument>
			<argument>-folder</argument>
			<argument>src/main/java/yourproject/model/iciql</argument>
			<argument>-package</argument>
			<argument>yourproject.model.iciql</argument>
		</arguments>
	</configuration>
</plugin>

Java Runtime Requirement

iciql requires a Java 6 Runtime Environment (JRE) or a Java 6 Development Kit (JDK).

License

iciql is distributed under the terms of the Apache Software Foundation license, version 2.0