Table Models

A model class represents a single table within your database. Fields within your model class represent columns in the table. The object types of your fields are reflectively mapped to SQL types by iciql at runtime.

Models can be manually written using one of three approaches: annotation configuration, interface configuration, or POJO configuration. All approaches can be used within a project and all can be used within a single model class, although that is discouraged.

Alternatively, model classes can be automatically generated by iciql using the model generation tool. Please see the tools page for details.

Configuration Requirements and Limitations

1. Your model class must provide a public default constructor.
2. All Object fields are assumed NULLABLE unless explicitly set @IQColumn(nullable = false) or Define.nullable(field, false).
3. All Primitive fields are assumed NOT NULLABLE unless explicitly set @IQColumn(nullable = true) or Define.nullable(field, true).
4. Only the specified types are supported. Any other types are not supported.
5. Triggers, views, and other advanced database features are not supported.

Standard Supported Data Types

Fully Supported Types can be used for all iciql expressions
Object	Primitive	SQL Type
java.lang.String		VARCHAR (length > 0) or CLOB (length == 0)
java.lang.Boolean	boolean	BOOLEAN can only declare and explicitly reference one primitive boolean per model multiple primitives are allowed if not using where/set/on/and/or/groupBy/orderBy(boolean)
java.lang.Byte	byte	TINYINT
java.lang.Short	short	SMALLINT
java.lang.Integer	int	INT
java.lang.Long	long	BIGINT
java.lang.Float	float	REAL
java.lang.Double	double	DOUBLE
java.math.BigDecimal		DECIMAL (length == 0) or DECIMAL(length,scale) (length > 0)
java.sql.Date		DATE
java.sql.Time		TIME
java.sql.Timestamp		TIMESTAMP
java.util.Date		TIMESTAMP
java.lang.Enum.name() default type		VARCHAR (length > 0) or CLOB (length == 0) EnumType.NAME can only declare and explicitly reference one instance of each enum type per model multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum)
java.lang.Enum.ordinal()		INT EnumType.ORDINAL can only declare and explicitly reference one instance of each enum type per model multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum)
java.lang.Enum implements com.iciql.Iciql.EnumId.enumId()		variable EnumType.ENUMID can only declare and explicitly reference one instance of each enum type per model multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum)
Partially Supported Types can not be directly referenced in an expression
byte []		BLOB
Custom	create a DataTypeAdapter<Custom>	Custom
H2 Database Types fully supported when paired with an H2 database
java.util.UUID		UUID

!!! note The reverse lookup used for model generation, SQL type -> Java type, contains more mappings.
Please consult the com.iciql.ModelUtils class for details.

Annotation Configuration

The recommended approach to setup a model class is to annotate the class and field declarations.

advantages

• annotated models support annotated field inheritance making it possible to design a single base class that defines the fields and then create table subclasses that specify the table mappings.
• model runtime dependency is limited to the small, portable com.iciql.Iciql class file which contains the annotation definitions

disadvantages

• more verbose model classes
• indexes are defined using "fragile" string column names
• compound primary keys are defined using "fragile" string column names

field mapping

• By default, ONLY fields annotated with @IQColumn are mapped.
• scope is irrelevant.
• transient is irrelevant.

default values

You may specify default values for an @IQColumn by either:

1. specifying the default value string within your annotation
   NOTE:
   The annotated default value always takes priority over a field default value.

// notice the single ticks!
@IQColumn(defaultValue="'2000-01-01 00:00:00'")
Date myDate;

2. setting a default value on the field
   NOTE:
   Primitive types have an implicit default value of 0 or false.

@IQColumn
Date myDate = new Date(100, 0, 1);

@IQColumn
int myId;

If you want to specify a database-specific variable or function as your default value (e.g. CURRENT_TIMESTAMP) you must do that within the annotation. Also note that the IQColumn.defaultValue must be a well-formatted SQL DEFAULT expression whereas object defaults will be automatically converted to an SQL DEFAULT expression.

Special Case: primitive autoincrement fields and 0

@IQColumn(autoIncrement = true)
int myId;

Because primitive types have implicit default values, this field will be excluded from an INSERT statement if its value is 0. Iciql can not differentiate an implicit/uninitialized 0 from a explicitly assigned 0.

Example Annotated Model

import com.iciql.Iciql.EnumType;
import com.iciql.Iciql.IQColumn;
import com.iciql.Iciql.IQEnum;
import com.iciql.Iciql.IQIndex;
import com.iciql.Iciql.IQTable;

@IQTable
@IQIndexes({
  @IQIndex({"productName", "category"}),
  @IQIndex(name="nameindex", value="productName")
})
public class Product {

	@IQEnum(EnumType.ORDINAL)
	public enum Availability {
		ACTIVE, DISCONTINUED;
	}

	@IQColumn(primaryKey = true)
	public Integer productId;

	@IQColumn(length = 200, trim = true)
	public String productName;

	@IQColumn(length = 50, trim = true)
	public String category;

	@IQColumn
	public Double unitPrice;

	@IQColumn(name = "units")
	public Integer unitsInStock;

	@IQColumn
	private Integer reorderQuantity;

	@IQColumn
	private Availability availability;

    @IQColumn(typeAdapter = MyCustomClassAdapter.class)
    private MyCustomClass;

	// ignored because it is not annotated AND the class is @IQTable annotated
	private Integer ignoredField;

	public Product() {
		// default constructor
	}
}

Foreign Keys

@IQTable(name = "AnnotatedProduct", primaryKey = "id")
@IQIndexes({ @IQIndex({ "name", "cat" }), @IQIndex(name = "nameidx", type = IndexType.HASH, value = "name") })
@IQContraintForeignKey(
    foreignColumns= { "cat" },
    referenceName = "AnnotatedCategory",
    referenceColumns = { "categ" },
    deleteType = ConstraintDeleteType.CASCADE
)
public class ProductAnnotationOnlyWithForeignKey {

    public String unmappedField;

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

    @IQColumn(name = "cat", length = 15, trim = true)
    public String category;

    @IQColumn(name = "name", length = 50)
    public String productName;

    @SuppressWarnings("unused")
    @IQColumn
    private Double unitPrice;

    @IQColumn
    private Integer unitsInStock;
}

Views with Field Constraints

@IQView(name = "AnnotatedProductView", tableName = "AnnotatedProduct")
public class ProductView {

    public String unmappedField;

    @IQColumn(name = "id", autoIncrement = true)
    @IQConstraint("this <= 7 AND this > 2")
    public Long productId;     
    @IQColumn(name = "name")
    public String productName;

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

Interface Configuration

Alternatively, you may map your model classes using the interface approach by implementing the com.iciql.Iciql interface.

This is a less verbose configuration style, but it comes at the expense of introducing a compile-time dependency on the logic of the iciql library. This might be a deterrent, for example, if you were serializing your model classes to another process that may not have the iciql library.

The com.iciql.Iciql interface specifies a single method, defineIQ(). In your implementation of defineIQ() you would use static method calls to set:

• the schema name
• the table name (if it's not the class name)
• the column name (if it's not the field name)
• the max length and trim of a string field
• the precision and scale of a decimal field
• the autoincrement flag of a long or integer field
• the nullable flag of a field
• the primaryKey (single field or compound)
• any indexes (single field or compound)

advantages

• less verbose model class
• compile-time index definitions
• compile-time compound primary key definitions

disadvantages

• model runtime dependency on entire iciql library
• defineIQ() is called from a static synchronized block which may be a bottleneck for highly concurrent systems

field mapping

• ALL fields are mapped unless annotated with @IQIgnore.
• scope is irrelevant.
• transient is irrelevant.

default values

You may specify default values for an field by either:

1. specifying the default value string within your defineIQ() method
   NOTE:
   The defineIQ() value always takes priority over a field default value.

Date myDate;

public void defineIQ() {
    // notice the single ticks!
    Define.defaultValue(myDate, "'2000-01-01 00:00:00'");
}

2. setting a default value on the field
   NOTE:
   Primitive types have an implicit default value of 0 or false.

Date myDate = new Date(100, 0, 1);

int myId;

Example Interface Model

import com.iciql.Iciql;
import com.iciql.Iciql.IQIgnore;

public class Product implements Iciql {
	public Integer productId;
	public String productName;
	public String category;
	public Double unitPrice;
	public Integer unitsInStock;

	@IQIgnore
	Integer reorderQuantity;

	public Product() {
	}

	@Override
	public void defineIQ() {
		com.iciql.Define.primaryKey(productId);
		com.iciql.Define.columnName(unitsInStock, "units");
		com.iciql.Define.length(productName, 200);
		com.iciql.Define.length(category, 50);
		com.iciql.Define.index(productName, category);
	}
}

POJO (Plain Old Java Object) Configuration

This approach is very similar to the interface configuration approach; it is the least verbose and also the least useful.

This approach would be suitable for quickly modeling an existing table where only SELECT and INSERT statements will be generated.

advantages

• nearly zero-configuration

disadvantages

• can not execute DELETE, UPDATE, or MERGE statements (they require a primary key specification)
• table name MUST MATCH model class name
• column names MUST MATCH model field names
• can not specify any column attributes
• can not specify indexes

field mapping

• ALL fields are mapped unless annotated with @IQIgnore.
• scope is irrelevant.
• transient is irrelevant.

default values

You may specify a default value on the field.

!!! note Primitive types have an implicit default value of 0 or false.

Date myDate = new Date(100, 0, 1);

int myId;

Example POJO Model

import com.iciql.Iciql.IQIgnore;

public class Product {
	public Integer productId;
	public String productName;
	public String category;
	public Double unitPrice;
	public Integer units;

	@IQIgnore
	Integer reorderQuantity;

	public Product() {
	}   
}