table of contents »

Circumflex ORM Documentation

Table Of Contents

This document is also available in a multi-page format.

Overview

Circumflex ORM is an Object-Relational Mapping (ORM) framework for creating fast, concise and efficient data-centric applications with elegant DSL.

The term «Object-Relational Mapping» refers to the technique of mapping a data representation from an object model to a relational data model. ORM tools may significantly speed up development by eliminating boilerplates for common CRUD operations, making applications more portable by incapsulating vendor-specific SQL dialects, providing object-oriented API for querying, allowing transparent navigation between object associations and much more.

Installation & Configuration

If you use Maven for building your project, add following lines to pom.xml (or merge XML sections accordingly):

<properties>
  <cx.version><!-- desired version --></cx.version>
</properties>
<dependencies>
  <dependency>
    <groupId>ru.circumflex</groupId>
    <artifactId>circumflex-orm</artifactId>
    <version>${cx.version}</version>
  </dependency>
</dependencies>

If you prefer SBT, make sure that libraryDependencies of your project contains following artifact:

"ru.circumflex" % "circumflex-orm" % cxVersion % "compile->default"

where cxVersion points to desired Circumflex version. Here's the sample project configuration:

import sbt._

class MyProject(info: ProjectInfo) extends DefaultProject(info) {
  val cxVersion = "2.0"

  override def libraryDependencies = Set(
      "ru.circumflex" % "circumflex-orm" % cxVersion % "compile->default"
  ) ++ super.libraryDependencies

}

You can follow SBT Setup Guide to create a new project.

Note that first-time builds usually require a substantial amount of dependencies downloads.

Configure database access by specifying following configuration parameters:

Here's the example cx.properties file:

orm.connection.driver=org.postgresql.Driver
orm.connection.url=jdbc:postgresql://localhost:5432/mydb
orm.connection.username=myuser
orm.connection.password=mypassword

Please refer to Circumflex Configuration API for more information on how to configure your application.

Imports

All code examples assume that you have following import statement in code where necessary:

import ru.circumflex.orm._

Central Abstractions

Applications built with Circumflex ORM usually operate on following abstractions:

Data Definition

The process of creating the domain model of application is refered to as data definition. It usually involves following steps:

Here's a simple example of fictional domain model:

class Country extends Record[String, Country] {
  val code = "code".VARCHAR(2).NOT_NULL
  val name = "name".TEXT.NOT_NULL

  def PRIMARY_KEY = code
  def relation = Country
}

object Country extends Country with Table[String, Country]

Record

In this example the Country table will have two fields, code and name. The first type parameter, String, designates the type of primary key (we refer to this type as PK). The second type parameter points to class itself to ensure type safety. The Record class has two abstract methods which should be implemented: PRIMARY_KEY and relation.

The PRIMARY_KEY method points to Field which type matches PK (String in our example). Primary key uniquely identifies a record in database table. Unfortunately, Circumflex ORM does not support composite primary keys yet.

The relation points to companion object which corresponds to record. It must have the same name as record class and should extend a record itself to inherit all its fields.

The body of record class contains field definitions. A field should be a public immutable (val) member of record class. Each field corresponds to a column in database table.

As the example above shows, the syntax of field definition closely resembles classic DDL for generating database schema for tables: you specify the column name with String, then you call one of the methods to create a field of certain type, then you optionally call one of methods that change the definition of target column.

Generally, spaces may be used to delimit method calls and improve readability of column definitions. However, sometimes Scala compiler forces you to use dot-notation:

val name = "name".TEXT.NOT_NULL

Following methods are used to create field definitions:

Method SQL type Scala type Implementing class
INTEGER INTEGER Int IntField
BIGINT BIGINT Long LongField
DOUBLE(precision: Int, scale: Int) NUMERIC(p, s) Double DoubleField
NUMERIC(precision: Int, scale: Int roundingMode: BigDecimal.RoundingMode.RoundingMode) NUMERIC(p, s) scala.math.BigDecimal NumericField
TEXT TEXT String TextField
VARCHAR(length: Int) VARCHAR(l) String TextField
BOOLEAN BOOLEAN Boolean BooleanField
DATE DATE java.util.Date DateField
TIME TIME java.util.Date TimeField
TIMESTAMP TIMESTAMP java.util.Date TimestampField

In the table above the default SQL types show the types defined in default dialect, which can be overriden in vendor-specific dialects. Besides it is possible to define a field with custom SQL type by subclassing the Field class. Refer to Circumflex ORM API documentation for details.

Since version 2.0 genearated columns will not have NOT NULL constraints by default (this behavior is consistent with SQL specifications). You should call NOT_NULL method to express NOT NULL constraint in column definition:

val mandatory = "mandatory".TEXT.NOT_NULL
val optional = "optional".TEXT

You can optionally initialize a field with value with NOT_NULL:

val createdAt = "created_at".TIMESTAMP.NOT_NULL(new Date)

You can also specify the default expression for the field, it will be rendered in database column definition:

val radius = "radius".NUMERIC.NOT_NULL
val square = "square".NUMERIC.NOT_NULL.DEFAULT("PI() * (radius ^ 2)")

You can also create a single-column unique constraint using the UNIQUE method:

val login = "login".VARCHAR(64).NOT_NULL.UNIQUE

Fields operate with values. The syntax for accessing and setting values is self-descriptive:

val age = "age".INTEGER  // Field[Int, R]
// accessing
age.value                     // Option[Int]
age.get                       // Option[Int]
age()                         // Int
age.getOrElse(default: Int)   // Int
age.null_?                    // Boolean
// setting
age := 25
age.set(25)
age.set(Some(25))
age.set(None)
age.setNull

It is a good practice to place domain-specific logic inside record classes. The following example shows the most trivial case: overriding toString and providing alternative constructor:

class Country extends Record[String, Country] {
  def PRIMARY_KEY = code
  def relation = Country
  // Constructor shortcuts
  def this(code: String, name: String) = {
    this()
    this.code := code
    this.name := name
  }
  // Fields
  val code = "code" VARCHAR(2) DEFAULT("'ch'")
  val name = "name" TEXT
  // Miscellaneous
  override def toString = name.getOrElse("Unknown")
}

Relation

Relation is defined as a companion object for corresponding record. As mentioned before, the relation object should have the same name as its corresponding record class, should extend that record class and should mix in one of the Relation traits (Table or View):

class Country extends Record[String, Country] {
  def relation = Country
  // ...
}
object Country extends Country with Table[String, Country]

You can place the definitions of constraints and indexes inside the body of relation, they should be public immutable (val) members of relation:

object Country extends Country with Table[String, Country] {
  // a named UNIQUE constraint
  val codeKey = CONSTRAINT("code_uniq").UNIQUE(this.code)
  // a UNIQUE constraint with default name
  val codeKey = UNIQUE(this.code)
  // a named CHECK constraint:
  val codeChk = CONSTRAINT("code_chk").CHECK("code IN ('ch', 'us', 'uk', 'fr', 'es', 'it', 'pt')")
  // a named FOREIGN KEY constraint:
  val fkey = CONSTRAINT("eurozone_code_fkey").FOREIGN_KEY(EuroZone, this.code -> EuroZone.code)
  // an index:
  val idx = "country_code_idx".INDEX("LOWER(code)").USING("btree").UNIQUE
}

Consult Circumflex ORM API Documentation for other definition options.

The relation object is also the right place for various querying methods:

object User extends Table[Long, User] {
  def findByLogin(l: String): Option[User] = (this AS "u").map(u =>
      SELECT(u.*).FROM(u).WHERE(u.login LIKE l).unique)
}

See querying, data manipulation and Criteria API sections for more information.

Generating Identifiers

Circumflex ORM allows you to use database-generated identifiers as primary keys. Let's take a look at following data definition snippet:

class City extends Record[Long, City] with IdentityGenerator[Long, City] {
  val id = "id".BIGINT.NOT_NULL.AUTO_INCREMENT
  val name = "name".TEXT.NOT_NULL
  def PRIMARY_KEY = id
  def relation = City
}

object City extends City with Table[Long, City]

This snippet shows a surrogate primary key example. The value of id is generated when a record is inserted. Then additional SQL select is issued to read this generated value.

For more information refer to Circumflex ORM API Documentation.

Associations

An association provides a way to link one relation with another.

class City extends Record[Long, City] {
  val country = "country_code".TEXT.REFERENCES(Country).ON_DELETE(CASCADE).ON_UPDATE(NO_ACTION)
}

As the example above shows, associations are created from fields using the REFERENCES method. The type of the field must match the type of primary key of referenced relation.

Associations also implicitly add foreign key constraint to table's definition. The cascading actions can be specified by invoking ON_DELETE and ON_UPDATE with one of the following arguments:

Associations are directed: the relation that owns an association is often refered to as a child relation, while the relation to which an associations references is often refered to as a parent relation.

Like with regular field, you can set an retrieve the association's value:

// accessing
country.value                       // Option[Country]
country.get                         // Option[Country]
country()                           // Country
country.getOrElse(default: Country) // Country
country.null_?                      // Boolean
// setting
country := switzerland
country.set(switzerland)
country.set(Some(switzerland))
country.set(None)
country.setNull

Associations do not store objects themselves. Instead they store the primary key of an object in their internal field. You can access and set this value directly using the field method:

country.field   // Field[String, R]
country.field := "ch"

When you access association using its get, apply, value or getOrElse methods, the actual record is returned from cache of current transaction. However, if record does not exist in cache yet, a transparent SQL select will be issued to fetch this record. This technique is usually refered to as lazy initialization or lazy fetching:

val c = new City
c.id := 16
c.country()   // a SELECT query is executed to retrieve a Country
              // for the City with id = 16
c.country()   // further selects are not issued

The other side of association can optionally define an inverse association using following syntax:

class Country extends Record[String, Country] {
  def cities = inverseMany(City.country)
}

Inverse associations are not represented by field in their relation, they are initialized by issuing the SELECT statement against child relation:

val c = new Country
c.code := 'ch'
c.cities()   // a SELECT query is executed to retrieve a set of City objects
             // which have country_code = 'ch'
c.cities()   // further selects are not issued

Here we have the so-called «one-to-many» relationship. The «one-to-one» relationship is simulated by placing a unique constraint on association (in child table) and using inverseOne in parent table.

You can also perform association prefetching for both straight and inverse associations using the Criteria API.

Validation

A record can be optionally validated before it is saved into database.

The validation is performed using one or more validators, functions which take a Record and return Option[Msg]: None if validation succeeds or Some[Msg] otherwise. In case of failed validation the Msg object is used to describe the exact problem. Refer to Circumflex Messages API Documentation to find out how to work with messages.

Validators are added to the validation object inside relation:

object Country extends Table[String, Country] {
  validation.add(r => ...)
      .add(r => ...)
}

There are several predefined validators available for your convenience:

object Country extends Table[String, Country] {
  validation.notNull(_.code)
      .notEmpty(_.code)
      .pattern(_.code, "(?i:[a-z]{2})")
}

A record is validated when either validate or validate_! is invoked. The first one returns Option[MsgGroup]:

rec.validate match {
  case None => ...            // validation succeeded
  case Some(errors) => ...    // validation failed
}

The second one does not return anything, but throws ValidationException if validation fails.

The validate_! method is also called when a record is being saved into database, read more in Insert, Update & Delete section.

It is also fairly easy to implement custom validators. Following example shows a validator for checking unique email addresses:

object Account extends Table[Long, Account] {
  validation.add(r => criteria
      .add(r.email EQ r.email())
      .unique
      .map(a => new Msg(r.email.uuid + ".unique")))
}

Querying

A precise request for information retrieval from database is often refered to as query. There are various ways you can query your data with Circumflex ORM:

All data retrieval queries derive from the SQLQuery[T] class. It defines following methods for query execution:

Select Queries

Select queries are used to retrieve records or arbitrary projections with neat object-oriented DSL which closely resembles SQL syntax:

// prepare relation nodes which will participate in query:
val co = Country AS "co"
val ci = City AS "ci"
// prepare a query:
val q = SELECT (co.*) FROM (co JOIN ci) WHERE (ci.name LIKE "Lausanne") ORDER_BY (co.name ASC)
// execute a query:
q.list    // returns Seq[Country]

The Select class provides functionality for select queries. It has following structure:

Relation Nodes

RelationNode wraps a Relation with an alias so that it can be a part of FROM clause of database query.

Relation nodes are represented by the RelationNode class, they are created by calling the AS method of Relation:

val co = Country AS "co"
// fetch all countries
SELECT (co.*) FROM (co) list

A handy map method can be used to make code a bit clearer:

// fetch all countries
(Country AS "CO").map(co => SELECT (co.*) FROM (co) list)

Relation nodes can be organized into query trees using joins.

Projections

Projection reflects the type of data returned by query. Generally, it consists of expression which can be understood in the SELECT clause of database and a logic to translate the corresponding part of result set into specific type.

Projections are represented by the Projection[T] trait, where T denotes to the type of objects which should be read from result set. Projections which only read from single database column are refered to as atomic projections, they are subclassed from the AtomicProjection trait. Projections which span across multiple database columns are refered to as composite projections, they are subclassed from the CompositeProjection trait and consist of one or more subProjections.

The most popular projection is RecordProjection, it is designed to retrieve records. The * method of RelationNode returns a corresponding RecordProjection for relation.

You can also query single fields, Field is converted to FieldProjection implicitly when called against RelationNode:

val ci = City AS "ci"
(SELECT (ci.id) FROM ci).list      // returns Seq[Long]
(SELECT (ci.name) FROM ci).list    // returns Seq[String]

You can also query a pair of two projections with following syntax:

val co = Country AS "co"
val ci = City AS "ci"
SELECT (ci.* -> co.*) FROM (co JOIN ci) list    // returns Seq[(Option[City], Option[Country])]

Another useful projection is AliasMapProjection:

val co = Country AS "co"
val ci = City AS "ci"
SELECT(ci.* AS "city", co.* AS "country").FROM(co JOIN ci).list    // returns Seq[Map[String, Any]]

In this example the query returns a set of maps. Each map contains a City record under city key and a Country record under the country key. The SELECT clause accepts arbitrary quantity of projections.

You can even use arbitrary expression which your database understands as long as you specify the expected type:

SELECT(expr[java.util.Date]("current_timestamp")).unique   // returns Option[java.util.Date]

There are also some predefined projection helpers for your convenience:

For example, following snippet will return the count of records in the City table:

(City AS "ci").map(ci => SELECT(COUNT(ci.id)).FROM(ci).unique)

You can easily implement your own projection helper. For example, if you use SQL substring function frequently, you can «teach» Circumflex ORM to select substrings.

Here's the code you should place somewhere in your library (or utility singleton):

object MyOrmUtils {
  def SUBSTR(f: TextField, from: Int = 0, length: Int = 0) = {
    var sql = "substring(" + f.name
    if (from > 0) sql += " from " + from
    if (length > 0) sql += " for " + length
    sql += ")"
    new ExpressionProjection[String](sql)
  }
}

And here's the code to use it:

import MyOrmUtils._
(Country AS "co")
    .map(co => SELECT(SUBSTR(co.code, 1, 1)).FROM(co).list)   // returns Seq[String]

Predicates

Predicate is a parameterized expression which is resolved by database into a boolean-value function. Generally, predicates are used inside WHERE or HAVING clauses of SQL queries to filter the rows in result set.

Predicates are represented by the Predicate class. The easiest way to compose a Predicate instance is to use implicit conversion from String or Field to SimpleExpressionHelper and call one of it's methods:

SELECT (co.*) FROM (co) WHERE (co.name LIKE "Switz%")

Following helper methods are available in SimpleExpressionHelper:

Group Method SQL equivalent
Comparison operators EQ(value: Any) = ?
NE(value: Any) <> ?
GT(value: Any) > ?
GE(value: Any) >= ?
LT(value: Any) < ?
LE(value: Any) <= ?
BETWEEN(lower: Any, upper: Any) BETWEEN ? AND ?
Null handling IS_NULL IS NULL
IS_NOT_NULL IS NOT NULL
Subqueries IN(query: SQLQuery[_]) IN (SELECT ...)
NOT_IN(query: SQLQuery[_]) NOT IN (SELECT ...)
EQ_ALL(query: SQLQuery[_]) = ALL (SELECT ...)
NE_ALL(query: SQLQuery[_]) <> ALL (SELECT ...)
GT_ALL(query: SQLQuery[_]) > ALL (SELECT ...)
GE_ALL(query: SQLQuery[_]) >= ALL (SELECT ...)
LT_ALL(query: SQLQuery[_]) < ALL (SELECT ...)
LE_ALL(query: SQLQuery[_]) <= ALL (SELECT ...)
EQ_SOME(query: SQLQuery[_]) = SOME (SELECT ...)
NE_SOME(query: SQLQuery[_]) <> SOME (SELECT ...)
GT_SOME(query: SQLQuery[_]) > SOME (SELECT ...)
GE_SOME(query: SQLQuery[_]) >= SOME (SELECT ...)
LT_SOME(query: SQLQuery[_]) < SOME (SELECT ...)
LE_SOME(query: SQLQuery[_]) <= SOME (SELECT ...)
Miscellaneous LIKE(value: Any) LIKE ?
ILIKE(value: Any) ILIKE ?
IN(params: Any*) IN (?, ?, ...)

You can combine several predicates into AggregatePredicate using either OR or AND methods:

AND(co.name LIKE "Switz%", co.code EQ "ch")
// or in infix notation:
(co.name LIKE "Switz%") OR (co.code EQ "ch")

You can negotiate a predicate using the NOT method:

NOT(co.name LIKE "Switz%")

String values are implicitly converted into SimpleExpression predicate without parameters:

SELECT (co.*) FROM (co) WHERE ("co.code like 'ch'"))

You can also use prepareExpr to compose a custom expression with parameters:

prepareExpr("co.name like :name or co.code like :code", "name" -> "Switz%", "code" -> "ch")

Ordering

Ordering expressions appear in ORDER_BY clause of Select, they determine how rows in result set will be sorted. The easiest way to specify ordering expressions is to use implicit convertions from String or Field into Order:

SELECT (co.*) FROM (co) ORDER_BY (co.name)

You can also add either ASC or DESC ordering specificator to explicitly set the direction of sorting:

SELECT (co.*) FROM (co) ORDER_BY (co.name ASC)

If no specificator given, ascending sorting is assumed by default.

Joins

Joins are used to combine records from two or more relations within a query.

Joins concept is a part of [relational algebra][rel-algebra-wiki]. If you are not familiar with joins in relational databases, consider spending some time to learn a bit about them. A good place to start will be the Join_(SQL) article on Wikipedia.

Joins allow you to build queries which span across several associated relations:

val co = Country AS "co"
val ci = City AS "ci"
// find cities by the name of their corresponding countries:
SELECT (ci.*) FROM (ci JOIN co) WHERE (co.name LIKE 'Switz%')

As the example above shows, joins are intended to be used in the FROM clause of query. The result of calling the JOIN method is an instance of JoinNode class:

val co2ci = (Country AS "co") JOIN (City AS "ci")   // JoinNode[Country, City]

Every JoinNode has it's left side and right side (co JOIN ci is not equivalent to ci JOIN co).

Left Associativity

An important thing to know is that the join operation is left-associative: if join is applied to JoinNode instance, the operation will be delegated to the left side of JoinNode.

To illustrate this, let's take three associated tables, Country, City and Street:

val co = Country AS "co"
val ci = City AS "ci"
val st = Street AS "st"

We want to join them in following order: Country → (City → Street). Since join operation is left-associative, we need extra parentheses:

co JOIN (ci JOIN st)

Now let's join the same tables in following order: (City → Street) → Country. In this case the parentheses can be omitted:

ci JOIN st JOIN co

Joining Predicate

By default Circumflex ORM will try to determine joining predicate (the ON subclause) by searching the associations between relations.

Let's say we have two associated relations, Country and City. We can use implicit joins between Country and City:

Country AS "co" JOIN (City AS "ci")
// country AS co LEFT JOIN city AS ci ON ci.country_code = co.code
City AS "ci" JOIN (Country AS "co")
// city AS ci LEFT JOIN country AS co ON ci.country_code = co.code

However, if no explicit association exist between relations (or if they are ambiguous), you may need to specify the join predicate explicitly:

ci.JOIN(co).ON("ci.country_code = co.code")

Join Types

Like in SQL, joins can be of several types. Depending on the type of join, rows which do not match the joining predicate will be eliminated from one of the sides of join. Following join types are available:

If no join type specified explicitly, LEFT join is assumed by default.

You can specify the type of join by passing an argument to the JOIN method:

(Country AS "co").JOIN(City AS "ci", INNER)

Or you may call one of specific methods instead:

Country AS "co" INNER_JOIN (City AS "ci")
Country AS "co" LEFT_JOIN (City AS "ci")
Country AS "co" RIGHT_JOIN (City AS "ci")
Country AS "co" FULL_JOIN (City AS "ci")

Grouping & Having

A query can optionally condense into a single row all selected rows that share the same value for a subset of query projections. Such queries are often refered to as grouping queries and the projections are usually refered to as grouping projections.

Grouping queries are built using the GROUP_BY clause:

SELECT (co.*) FROM co GROUP_BY (co.*)

As the example above shows, grouping projections are specified as arguments to the GROUP_BY method.

Grouping queries are often used in conjunction with aggregate functions. If aggregate functions are used, they are computed across all rows making up each group, producing separate value for each group, whereas without GROUP_BY an aggregate produces a single value computed across all the selected rows:

val co = Country AS "co"
val ci = City AS "ci"
// how many cities correspond to each selected country?
SELECT (co.* -> COUNT(ci.id)) FROM (co JOIN ci) GROUP_BY (co.*)

Groups can be optionally filtered using the HAVING clause. It accepts a predicate:

SELECT (co.* -> COUNT(ci.id)) FROM (co JOIN ci) GROUP_BY (co.*) HAVING (co.code LIKE "c_")

Note that HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP_BY, while HAVING filters group rows created by GROUP_BY.

Limit & Offset

The LIMIT clause specifies the maximum number of rows a query will return:

// select 10 first countries:
SELECT (co.*) FROM co LIMIT 10

The OFFSET clause specifies the number of rows to skip before starting to return results. When both are specified, the amount of rows specified in the OFFSET clause is skipped before starting to count the maximum amount of returned rows specified in the LIMIT clause:

// select 5 countries starting from 10th:
SELECT (co.*) FROM co LIMIT 5 OFFSET 10

Note that query planners in database engines often take LIMIT and OFFSET into account when generating a query plan, so you are very likely to get different row orders for different LIMIT/OFFSET values. Thus, you should use explicit ordering to achieve consistent and predictable results when selecting different subsets of a query result with LIMIT/OFFSET.

Union, Intersect & Except

Most database engines allow to comine the results of two queries using the set operations. Following set operations are available:

The syntax for using set operations is:

// select the names of both countries and cities in a single result set:
SELECT (co.name) FROM co UNION (SELECT (ci.name) FROM ci)

Set operations can also be nested and chained:

q1 INTERSECT q2 EXCEPT q3
(q1 UNION q2) INTERSECT q3

The queries combined using set operations should have matching projections. Following will not compile:

SELECT (co.*) FROM co UNION (SELECT (ci.*) FROM ci)

Reusing Query Objects

When working with data-centric applications, you often need the same query to be executed with different parameters. The most obvious solution is to build Query objects dynamically:

object Country extends Table[String, Country] {
  def findByCode(code: String): Option[Country] = (this AS "co").map(co =>
      SELECT (co.*) FROM co WHERE (co.code LIKE code) unique)
}

However, you can use named parameters to reuse the same Query object:

object Country extends Table[String, Country] {
  val co = AS("co")
  val byCode = SELECT (co.*) FROM co WHERE (co.code LIKE ":code")
  def findByCode(c: String): Option[Country] = byCode.set("code", c).unique
}

Criteria API

Most (if not all) of your data retrieval queries will be focused to retrieve only one type of records. Criteria API aims to minimize your effort on writing such queries. Following snippet shows three equivalents of the same query:

// Select query:
(Country AS "co").map(co => SELECT (co.*) FROM (co) WHERE (co.name LIKE "Sw%") list)
// Criteria query:
Country.criteria.add(Country.name LIKE "Sw%").list
// or with RelationNode:
co.criteria.add(co.name LIKE "Sw%").list

As you can see, Criteria queries are more compact because boilerplate SELECT and FROM clauses are omitted.

But aside from shortening the syntax, Criteria API offers unique functionality — associations prefetching, which can greatly speed up your application when working with graphs of associated objects.

The Criteria[R] object has following methods for execution:

You can use predicates to narrow the result set. Unlike Select queries, predicates are added to Criteria object using the add method and then are assembled into the conjunction:

co.criteria
    .add(co.name LIKE "Sw%")
    .add(co.code LIKE "ch")
    .list

You can apply ordering using the addOrder method:

co.criteria.addOrder(co.name).addOrder(co.code).list

Also you can add one or more associated relations to the query plan using the addJoin method so that you can specify constraints upon them:

val co = Country AS "co"
val ci = City AS "ci"
co.criteria.addJoin(ci).add(ci.name LIKE "Lausanne").list

Automatic joins are used to update query plan properly. There is no limitation on quantity or depth of joined relations. However, some database vendors have limitations on maximum size of queries or maximum amount of relations participating in a single query.

One serious limitation of Criteria API is that it does not support LIMIT and OFFSET clauses due to the fact that association prefetching normally causes result set to yield more than one row per record. You can still use LIMIT and OFFSET with SQL queries;

Prefetching Associations

When working with associated records you often need a whole graph of associations to be fetched.

Normally associations are fetched eagerly first time they are accessed, but when it is done for every record in a possibly big result set, it would result in significant performance degradation (see the [n + 1 selects problem explained][n+1] blogpost).

With Criteria API you have an option to fetch as many associations as you want in a single query. This technique is refered to as associations prefetching or eager fetching.

To understand how associations prefetching works, let's take a look at the following domain model sample:

class Country extends Record[String, Country] {
  def PRIMARY_KEY = code
  def relation = Country
  val code = "code" VARCHAR(2) DEFAULT("'ch'")
  val name = "name" TEXT
  def cities = inverseMany(City.country)
}

object Country extends Country with Table[String, Country]

class City extends Record[Long, City] with IdentityGenerator[Long, City] {
  def PRIMARY_KEY = id
  def relation = City
  val id = "id".LONG.NOT_NULL.AUTO_INCREMENT
  val name = "name" TEXT
  val country = "country_code".VARCHAR(2).NOT_NULL
      .REFERENCES(Country).ON_DELETE(CASCADE).ON_UPDATE(CASCADE)
}

object City extends City with Table[Long, City]

You see two relations, Country and City. Each city has one associated country, and, conversely, each country has a list of corresponding cities.

Now you wish to fetch all cities with their corresponding countries in a single query:

val cities = City.criteria.prefetch(City.country).list
cities.foreach(c => println(c.country()))   // no selects issued

The example above shows the prefetching for straight associations. Same logic applies to inverse associations prefetching, for example, fetching all countries with their corresponding cities:

val countries = Country.criteria.prefetch(City.country).list
countries.foreach(c => println(c.cities()))   // no selects issued

Okay. Now we totally hear you saying: “How is that really possible?” — so let's explain a bit. Each Criteria object maintains it's own tree of associations, which is used to form the FROM clause of the query (using automatic left-joins) and, eventually, to parse the result set. The data from result set is parsed into chunks and loaded into transaction-scoped cache, which is subsequently used by associations and inverse associations to avoid unnecessary selects.

There is no limitation on quantity or depth of prefetches. However, some database vendors have limitations on maximum size of queries or maximum amount of relations participating in a single query.

Data Manipulation

Aside from information retrieval tasks, queries may be intended to change data in some way:

Such queries are often refered to as data manipulation queries.

Insert, Update & Delete

Circumflex ORM employs Active Record design pattern. Each Record has following data manipulation methods which correspond to their SQL analogues:

Save

Circumflex ORM provides higher abstraction for persisting records — the save_! method. It's algorithm is trivial:

There is also a handy save() method, which runs record validation and then delegates to save_!().

Note that in order to use save and save_! methods your records should support identifier generation.

Bulk Queries

Circumflex ORM provides support for the following bulk data manipulation queries:

All data manipulation queries derive from the DMLQuery class. It defines a single method for execution, execute(), which executes corresponding statement and returns the number of affected rows.

Also note that each execution of any data manipulation query evicts all records from transaction-scoped cache.

Insert-Select

The InsertSelect query has following syntax:

// prepare query
val q = (Country AS "co").map(co => INSERT_INTO (co) SELECT ...)
// execute it
q.execute

Note that projections of specified SQLQuery must match the columns of the Relation.

Update & Delete

SQL databases support UPDATE and DELETE statements for bulk operations. Circumflex ORM provides equivalent abstractions for these operations, Update and Delete respectively.

The Update query allows you to use DSL for updating fields of multiple records at a time:

(Country AS "co").map(co =>
  UPDATE (co) SET (co.name, "United Kingdom") SET (co.code, "uk") execute)

The Delete query allows you to delete multiple records from a single relation:

(Country AS "co").map(co => DELETE (co) execute)

An optional WHERE clause specifies predicate for searched update or delete:

UPDATE (co) SET (co.name, "United Kingdom") WHERE (co.code LIKE 'uk')
DELETE (co) WHERE (co.code LIKE 'uk')

Many database vendors also allow USING clause in UPDATE and DELETE statements. Circumflex ORM does not support this feature yet.

Exporting Database Schema

Database schema scripts are generated with DDLUnit. You can use this class to create and drop database objects programmatically:

val ddl = new DDLUnit(Country, City)
// drop objects
ddl.DROP
// create objects
ddl.CREATE
// drop and then create objects
ddl.DROP_CREATE

DDLUnit creates objects in the following order:

Respectively, drop script works with objects in a reverse order.

After the execution, DDLUnit produces messages.

You can also setup maven-cx-plugin to export the schema for your Maven project within a build profile. Read more on Circumflex Maven Plugin page.