Criteria API ¶

Contents

Criteria API

Warning

The criteria API is an experimental feature.

Introduction ¶

We provide our criteria API as a Kotlin library. You have to use the API from your kotlin program, but you can define your Entity classes both in Java and Kotlin.

We use the following Entity classes to show you some examples:

@Entity
public class Emp {
    @Id public Integer employeeId;
    public String employeeName;
    public Integer managerId;
    @Transient public Emp manager;
    public Integer departmentId;
    @Transient public Dept department;
    @Version public Integer version;
}

@Entity
public class Dept {
    @Id public Integer departmentId;
    public String departmentName;
    @Version public Integer version;
    @Transient public List<Emp> employeeList = new ArrayList<>();
}

Note that the above classes are written in Java.

There are two kind of DSLs in the Criteria API:

The Entityql DSL
The Sql DSL

Both requires predefined Entity classes and the meta code generated by Doma’s annotation processors.

Build with Gradle ¶

To use the Criteria API, write your build.gradle as follows:

plugins {
    id "org.seasar.doma.compile" version "1.0.0"
}

kapt {
    arguments {
      arg("doma.criteria.enabled", true)
    }
}

dependencies {
    kapt "org.seasar.doma:doma-processor:2.31.0"
    implementation "org.seasar.doma:doma-criteria:2.31.0"
}

Check points:

Use the Doma Compile Plugin
Use the kapt extension and make the doma.criteria.enabled option enabled
Depends on the the doma-processor and the doma-criteria artifact

Entityql DSL ¶

The Entityql DSL can query and associate entities.

Write your query in a entityql block as follows:

val query = entityql {
    // write your query here
}

For example, to query Emp entities, write as follows:

val query = entityql {
    from(::Emp_) { e ->
        where {
            e.managerId ge 3
            e.managerId le 6
        }
    }
}

The ::Emp_ is the reference to the meta class of the Emp entity class.

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID >= ? and t0_.MANAGER_ID <= ?

Entityql DSL Examples ¶

or operator ¶

val query = entityql {
    from(::Emp_) { e ->
        where {
            e.managerId eq 1
            or {
                e.managerId eq 6
            }
        }
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID = ? or (t0_.MANAGER_ID = ?)

in predicate ¶

val query = entityql {
    from(::Emp_) { e ->
        where {
            e.managerId `in` (listOf(1, 6))
        }
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID in (?, ?)

in predicate with subquery ¶

val query = entityql {
    from(::Emp_) { e ->
        where {
            e.managerId `in` {
                from(::Emp_) { e2 ->
                    select(e2.employeeId)
                }
            }
        }
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_
where t0_.MANAGER_ID in (select t1_.EMPLOYEE_ID from EMP t1_)

join clause ¶

You can use the following functions to issue JOIN:

innerJoin
leftJoin

left join¶

val query = entityql {
    from(::Emp_) { e ->
        val d = leftJoin(::Dept_) { d ->
            e.departmentId eq d.departmentId
        }
        where {
            d.departmentName eq "RESEARCH"
        }
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_ left outer join DEPT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
where t1_.DEPARTMENT_NAME = ?

left join and association¶

When you invoke the innerJoin or the leftJoin function, you can associate entities with the associate function:

val query = entityql {
    from(::Emp_) { e ->
        val d = leftJoin(::Dept_) { d ->
            e.departmentId eq d.departmentId
        }
        where {
            e.employeeName eq "SMITH"
        }
        associate(e, d) { employee, department ->
            employee.department = department
            department.employeeList.add(employee)
        }
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION,
t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NAME, t1_.VERSION
from EMP t0_left outer join DEPT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
where t0_.EMPLOYEE_NAME = ?

self-join and association¶

We also support self-join as follows:

val query = entityql {
    from(::Emp_) { e ->
        val m = leftJoin(::Emp_) { m ->
            e.managerId eq m.employeeId
        }
        associate(e, m) { employee, manager ->
            employee.manager = manager
        }
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION,
t1_.EMPLOYEE_ID, t1_.EMPLOYEE_NAME, t1_.MANAGER_ID, t1_.DEPARTMENT_ID, t1_.VERSION
from EMP t0_ left outer join EMP t1_ on (t0_.MANAGER_ID = t1_.EMPLOYEE_ID)

limit and offset ¶

val query = entityql {
    from(::Emp_) { e ->
        orderBy {
            e.employeeId.asc()
        }
        limit(5)
        offset(3)
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.DEPARTMENT_ID, t0_.VERSION
from EMP t0_ order by t0_.EMPLOYEE_ID asc limit 5 offset 3

Sql DSL ¶

The Sql DSL can issue more complex SQL statements rather than the Entityql DSL, but can’t associate entities.

Write your query in a sql block as follows:

val query = sql {
    // write your query here
}

For example, to query one column, write as follows:

val query = sql {
    from(::Emp_) { e ->
        where {
            e.employeeId eq 1
        }
        select(e.employeeName)
    }
}

Note that the Sql DSL requires a invocation of the select function in the last statement of the from block. The above query issues the following SQL statement:

select t0_.EMPLOYEE_NAME from EMP t0_ where t0_.EMPLOYEE_ID = ?

Sql DSL Examples ¶

DTO mapping ¶

data class MyDto(val name: String?, val id: Int?)

val query = sql {
    from(::Emp_) { e ->
        where {
            e.employeeId eq 1
        }
        select(e.employeeName, e.departmentId) {
            MyDto(it[e.employeeName], it[e.departmentId])
        }
    }
}

The above query issues the following SQL statement:

select t0_.EMPLOYEE_NAME, t0_.DEPARTMENT_ID from EMP t0_ where t0_.EMPLOYEE_ID = ?

aggregate function ¶

val query = sql {
    from(::Emp_) { e ->
        val d = leftJoin(::Dept_) { d ->
            e.departmentId eq d.departmentId
        }
        groupBy(d.departmentName)
        having {
            count(`*`) gt 2
        }
        orderBy {
            count(`*`).desc()
        }
        select(d.departmentName, count(`*`), min(e.employeeName))
    }
}

The above query issues the following SQL statement:

select t1_.DEPARTMENT_NAME, count(*), min(t0_.EMPLOYEE_NAME)
from EMP t0_
left outer join DEPT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID)
group by t1_.DEPARTMENT_NAME
having count(*) > ?
order by count(*) desc

insert statement ¶

val query = sql {
    insert.into(::Dept_) { d ->
        values {
            it[d.departmentId] = 99
            it[d.departmentName] = "MARKETING"
            it[d.version] = 1
        }
    }
}

The above query issues the following SQL statement:

insert into DEPT (DEPARTMENT_ID, DEPARTMENT_NAME, VERSION) values (?, ?, ?)

delete statement ¶

val query = sql {
    delete.from(::Emp_) { e ->
        where {
            e.departmentId eq 1
        }
    }
}

The above query issues the following SQL statement:

delete from EMP t0_ where t0_.DEPARTMENT_ID = ?

update statement ¶

val query = sql {
    update(::Emp_) { e ->
        set {
            it[e.managerId] = 2
        }
        where {
            e.departmentId eq 1
        }
    }
}

The above query issues the following SQL statement:

update EMP t0_ set t0_.MANAGER_ID = ? where t0_.DEPARTMENT_ID = ?