==================== Classic Criteria API ==================== .. contents:: :depth: 4 Introduction ============ .. warning:: Please use the Query DSL introduced on the :doc:`query-dsl` page instead of the Entityql and NativeSql DSLs explained here. The Query DSL is a new, unified interface that combines both Entityql and NativeSql DSLs. There are two kinds of DSLs in the Criteria API: * The Entityql DSL * The NativeSql DSL Both requires predefined Entity classes and metamodel classes. We use the following Entity classes to show you some examples: .. code-block:: java @Entity(metamodel = @Metamodel) public class Employee { @Id private Integer employeeId; private Integer employeeNo; private String employeeName; private Integer managerId; private LocalDate hiredate; private Salary salary; private Integer departmentId; private Integer addressId; @Version private Integer version; @OriginalStates private Employee states; @Association private Department department; @Association private Employee manager; @Association private Address address; // getters and setters } .. code-block:: java @Entity(metamodel = @Metamodel) public class Department { @Id private Integer departmentId; private Integer departmentNo; private String departmentName; private String location; @Version private Integer version; @OriginalStates private Department originalStates; @Association private List employeeList = new ArrayList<>(); // getters and setters } .. code-block:: java @Entity(immutable = true, metamodel = @Metamodel) @Table(name = "EMPLOYEE") public class Emp { @Id private final Integer employeeId; private final Integer employeeNo; private final String employeeName; private final Integer managerId; private final LocalDate hiredate; private final Salary salary; private final Integer departmentId; private final Integer addressId; @Version private final Integer version; @Association private final Dept department; @Association private final Emp manager; // constructor and getters } .. code-block:: java @Entity(immutable = true, metamodel = @Metamodel) @Table(name = "DEPARTMENT") public class Dept { @Id private final Integer departmentId; private final Integer departmentNo; private final String departmentName; private final String location; @Version private final Integer version; // constructor and getters } Note that the above classes are annotated with ``@Entity(metamodel = @Metamodel)``. The ``metamodel = @Metamodel`` indicates that the annotated entity has a corresponding metamodel class generated by Doma's annotation processor . In our examples, the metamodel classes are ``Employee_``, ``Department_``, ``Emp_`` and ``Dept_``. These metamodels allow you to make your query typesafe. You can customize the name of the metamodels by the Metamodel annotation elements. To customize all metamodels in bulk, you can use the annotation processor options. See :doc:`annotation-processing` and check the following options: * doma.metamodel.enabled * doma.metamodel.prefix * doma.metamodel.suffix Entityql DSL ------------ The Entityql DSL can query and associate entities. The entry point is the ``org.seasar.doma.jdbc.criteria.Entityql`` class. This class has the following methods: * from * insert * delete * update You can instantiate the ``Entityql`` class as follows: .. code-block:: java Entityql entityql = new Entityql(config); For example, to query ``Employee`` and ``Department`` entities and associate them, write as follows: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List list = entityql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .where(c -> c.eq(d.departmentName, "SALES")) .associate( e, d, (employee, department) -> { employee.setDepartment(department); department.getEmployeeList().add(employee); }) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION, t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NO, t1_.DEPARTMENT_NAME, t1_.LOCATION, t1_.VERSION from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) where t1_.DEPARTMENT_NAME = ? NativeSql DSL ------------- The NativeSql DSL can issue more complex SQL statements rather than the Entityql DSL. But note that the NativeSql DSL doesn't support to associate entities. The entry point is the ``org.seasar.doma.jdbc.criteria.NativeSql`` class. This class has the following methods: * from * delete * insert * update You can instantiate the ``NativeSql`` class as follows: .. code-block:: java NativeSql nativeSql = new NativeSql(config); For example, to query two columns with GROUP BY and HAVING clauses, write as follows: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List> list = nativeSql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .groupBy(d.departmentName) .having(c -> c.gt(count(), 3L)) .orderBy(c -> c.asc(count())) .select(count(), d.departmentName) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select count(*), t1_.DEPARTMENT_NAME from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) group by t1_.DEPARTMENT_NAME having count(*) > ? order by count(*) asc The difference between two DSLs ------------------------------- The biggest difference between two DSLs is that the Entityql DSL removes duplicated data from the fetched results, but the NativeSQL DSL doesn't. See the following example: .. code-block:: java Department_ d = new Department_(); Employee_ e = new Employee_(); // (1) Use Entityql DSL List list1 = entityql.from(d).innerJoin(e, on -> on.eq(d.departmentId, e.departmentId)).fetch(); // (2) Use NativeSql DSL List list2 = nativeSql.from(d).innerJoin(e, on -> on.eq(d.departmentId, e.departmentId)).fetch(); System.out.println(list1.size()); // 3 System.out.println(list2.size()); // 14 Both (1) and (2) issue the same SQL statement as follows: .. code-block:: java select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION,t0_.VERSION from DEPARTMENT t0_ inner join EMPLOYEE t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) The ResultSet contains 14 rows, but the Entityql DSL returns only 3 rows because it removes duplicate Department entities. The Entityql DSL uses the id properties to know the uniqueness of the entities. On the other hand, the NativeSql DSL returns the data as it retrieves from the database. It puts results into entity objects, but it handles them as the plain DTOs. Select statement ================ Select settings (Entityql, NativeSql) ------------------------------------- We support the following settings: * allowEmptyWhere * comment * fetchSize * maxRows * queryTimeout * sqlLogType They are all optional. You can apply them as follows: .. code-block:: java Employee_ e = new Employee_(); List list = entityql.from(e, settings -> { settings.setAllowEmptyWhere(false); settings.setComment("all employees"); settings.setFetchSize(100); settings.setMaxRows(100); settings.setSqlLogType(SqlLogType.RAW); settings.setQueryTimeout(1000); }).fetch(); Fetching (Entityql, NativeSql) ------------------------------ Both Entityql DSL and NativeSql DSL support the following methods to fetch data from a database: * fetch * fetchOne * fetchOptional * stream .. code-block:: java Employee_ e = new Employee_(); // The fetch method returns results as a list. List list = entityql.from(e).fetch(); // The fetchOne method returns a single result. The result may be null. Employee employee = entityql.from(e).where(c -> c.eq(e.employeeId, 1)).fetchOne(); // The fetchOptional method returns a single result as an optional object. Optional optional = entityql.from(e).where(c -> c.eq(e.employeeId, 1)).fetchOptional(); // The stream method returns results as a stream. // The following code is equivalent to "entityql.from(e).fetch().stream()" Stream stream = entityql.from(e).stream(); Streaming (NativeSql) --------------------- The NativeSql DSL supports the following methods: * mapStream * collect * openStream .. code-block:: java Employee_ e = new Employee_(); // The mapStream method handles a stream. Map> map = nativeSql .from(e) .mapStream(stream -> stream.collect(groupingBy(Employee::getDepartmentId))); // The collect method is a shortcut of the mapStream method. // The following code does the same thing with the above. Map> map2 = nativeSql.from(e).collect(groupingBy(Employee::getDepartmentId)); // The openStream method returns a stream. // You MUST close the stream explicitly. try (Stream stream = nativeSql.from(e).openStream()) { stream.forEach(employee -> { // do something }); } These methods handle the stream that wraps a JDBC ResultSet. So they are useful to process a large ResultSet effectively. Select expression ----------------------------- Entity selection (Entityql, NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ By default, the result entity type is the same as the one specified at the ``from`` method. See the following code: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List list = entityql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) To choose a joined entity type as the result entity type, call the ``select`` method as follows: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List list = entityql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .select(d) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NO, t1_.DEPARTMENT_NAME, t1_.LOCATION, t1_.VERSION from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) Multiple entity selection (NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ You can specify multiple entity types and fetch them as a tuple as follows: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List> list = nativeSql .from(d) .leftJoin(e, on -> on.eq(d.departmentId, e.departmentId)) .where(c -> c.eq(d.departmentId, 4)) .select(d, e) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION, t1_.EMPLOYEE_ID, t1_.EMPLOYEE_NO, t1_.EMPLOYEE_NAME, t1_.MANAGER_ID, t1_.HIREDATE, t1_.SALARY, t1_.DEPARTMENT_ID, t1_.ADDRESS_ID, t1_.VERSION from DEPARTMENT t0_ left outer join EMPLOYEE t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) where t0_.DEPARTMENT_ID = ? The entity included in the tuple may be null when the all properties of the entity are null. Column projection (NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To project columns, use the ``select`` method: To project one column, pass one property to the select method as follows: .. code-block:: java Employee_ e = new Employee_(); List list = nativeSql.from(e).select(e.employeeName).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_NAME from EMPLOYEE t0_ To project two or more columns, pass two or more properties to the select method as follows: .. code-block:: java Employee_ e = new Employee_(); List> list = nativeSql.from(e).select(e.employeeName, e.employeeNo).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_NAME, t0_.EMPLOYEE_NO from EMPLOYEE t0_ Up to 9 numbers, the column results are held by ``Tuple2`` to ``Tuple9``. For more than 9 numbers, the results are held by ``Row``. You can get a ``Row`` list explicitly by using ``selectAsRow`` as follows: .. code-block:: java Employee_ e = new Employee_(); List list = nativeSql.from(e).selectAsRow(e.employeeName, e.employeeNo).fetch(); Column projection and mapping (Entityql, NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To project columns and map them to an entity, use the ``selectTo`` method as follows: .. code-block:: java Employee_ e = new Employee_(); List list = entityql.from(e).selectTo(e, e.employeeName).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME from EMPLOYEE t0_ Note that the select clause of the above SQL statement contains the primary key "EMPLOYEE_ID". The ``selectTo`` method always includes the id properties of the entity, even if you don't. .. _criteria_where: Where expression (Entityql, NativeSql) -------------------------------------- We support the following operators and predicates: * eq - (=) * ne - (<>) * ge - (>=) * gt - (>) * le - (<=) * lt - (<) * isNull - (is null) * isNotNull - (is not null) * like * notLike - (not like) * between * in * notIn - (not in) * exists * notExists - (not exists) .. note:: If the right hand operand is ``null``, the WHERE or the HAVING clause doesn't include the operator. See WhereDeclaration_ and HavingDeclaration_ javadoc for more details. .. _WhereDeclaration: https://www.javadoc.io/doc/org.seasar.doma/doma-core/latest/org/seasar/doma/jdbc/criteria/declaration/WhereDeclaration.html .. _HavingDeclaration: https://www.javadoc.io/doc/org.seasar.doma/doma-core/latest/org/seasar/doma/jdbc/criteria/declaration/HavingDeclaration.html We also support the following utility operators: * eqOrIsNull - ("=" or "is null") * neOrIsNotNull - ("<>" or "is not null") We also support the following logical operators: * and * or * not .. code-block:: java Employee_ e = new Employee_(); List list = entityql .from(e) .where( c -> { c.eq(e.departmentId, 2); c.isNotNull(e.managerId); c.or( () -> { c.gt(e.salary, new Salary("1000")); c.lt(e.salary, new Salary("2000")); }); }) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ where t0_.DEPARTMENT_ID = ? and t0_.MANAGER_ID is not null or (t0_.SALARY > ? and t0_.SALARY < ?) You can write a subquery as follows: .. code-block:: java Employee_ e = new Employee_(); Employee_ e2 = new Employee_(); List list = entityql .from(e) .where(c -> c.in(e.employeeId, c.from(e2).select(e2.managerId))) .orderBy(c -> c.asc(e.employeeId)) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ where t0_.EMPLOYEE_ID in (select t1_.MANAGER_ID from EMPLOYEE t1_) order by t0_.EMPLOYEE_ID asc Dynamic where expression (Entityql, NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A where expression uses only evaluated operators to build a WHERE clause. When every operators are not evaluated in a where expression, the built statement doesn't have any WHERE clause. As well as, when every operators are not evaluated in a logical operator expression, the built statement doesn't have the logical operator expression. For example, suppose that a where expression contains a conditional expression as follows: .. code-block:: java Employee_ e = new Employee_(); List list = entityql .from(e) .where( c -> { c.eq(e.departmentId, 1); if (enableNameCondition) { c.like(e.employeeName, name); } }) .fetch(); In the case that the ``enableNameCondition`` variable is ``false``, the ``like`` expression is ignored. The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ where t0_.DEPARTMENT_ID = ? Join expression --------------- We support the following expressions: - innerJoin - (inner join) - leftJoin - (left outer join) innerJoin (Entityql, NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List list = entityql.from(e).innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) leftJoin (Entityql, NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List list = entityql.from(e).leftJoin(d, on -> on.eq(e.departmentId, d.departmentId)).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ left outer join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) .. _criteria_associate: association (Entityql) ~~~~~~~~~~~~~~~~~~~~~~ You can associate entities with the ``associate`` operation in the Entityql DSL. You have to use the ``associate`` operation with join expression. .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List list = entityql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .where(c -> c.eq(d.departmentName, "SALES")) .associate( e, d, (employee, department) -> { employee.setDepartment(department); department.getEmployeeList().add(employee); }) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION, t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NO, t1_.DEPARTMENT_NAME, t1_.LOCATION, t1_.VERSION from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) where t1_.DEPARTMENT_NAME = ? You can associate many entities: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); Address_ a = new Address_(); List list = entityql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .innerJoin(a, on -> on.eq(e.addressId, a.addressId)) .where(c -> c.eq(d.departmentName, "SALES")) .associate( e, d, (employee, department) -> { employee.setDepartment(department); department.getEmployeeList().add(employee); }) .associate(e, a, Employee::setAddress) .fetch(); association for immutable entities (Entityql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ You can associate immutable entities with the ``associateWith`` operation in the Entityql DSL. You have to use the ``associateWith`` operation with join expression. .. code-block:: java Emp_ e = new Emp_(); Emp_ m = new Emp_(); Dept_ d = new Dept_(); List list = entityql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .leftJoin(m, on -> on.eq(e.managerId, m.employeeId)) .where(c -> c.eq(d.departmentName, "SALES")) .associateWith(e, d, Emp::withDept) .associateWith(e, m, Emp::withManager) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION, t1_.DEPARTMENT_ID, t1_.DEPARTMENT_NO, t1_.DEPARTMENT_NAME, t1_.LOCATION, t1_.VERSION, t2_.EMPLOYEE_ID, t2_.EMPLOYEE_NO, t2_.EMPLOYEE_NAME, t2_.MANAGER_ID, t2_.HIREDATE, t2_.SALARY, t2_.DEPARTMENT_ID, t2_.ADDRESS_ID, t2_.VERSION from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) left outer join EMPLOYEE t2_ on (t0_.MANAGER_ID = t2_.EMPLOYEE_ID) where t1_.DEPARTMENT_NAME = ? Dynamic join expression (Entityql, NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A join expression uses only evaluated operators to build a JOIN clause. When every operators are not evaluated in a join expression, the built statement doesn't have any JOIN clause. For example, suppose that a join expression contains a conditional expression as follows: .. code-block:: java Employee_ e = new Employee_(); Employee_ e2 = new Employee_(); List list = entityql .from(e) .innerJoin( e2, on -> { if (join) { on.eq(e.managerId, e2.employeeId); } }) .fetch(); In the case that the ``join`` variable is ``false``, the ``on`` expression is ignored. The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ Dynamic association (Entityql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ When you use the above dynamic join expression, the association must be optional. To do it, pass the result of ``AssociationOption.optional()`` to the associate method: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List list = entityql .from(e) .innerJoin( d, on -> { if (join) { on.eq(e.departmentId, d.departmentId); } }) .associate( e, d, (employee, department) -> { employee.setDepartment(department); department.getEmployeeList().add(employee); }, AssociationOption.optional()) .fetch(); Aggregate Functions (NativeSql) ------------------------------- We support the following aggregate functions: * avg(property) * avgAsDouble(property) * count() * count(property) * countDistinct(property) * max(property) * min(property) * sum(property) These are defined in the ``org.seasar.doma.jdbc.criteria.expression.Expressions`` class. Use them with static import. For example, you can pass the ``sum`` function to the select method: .. code-block:: java Employee_ e = new Employee_(); Salary salary = nativeSql.from(e).select(sum(e.salary)).fetchOne(); The above query issues the following SQL statement: .. code-block:: sql select sum(t0_.SALARY) from EMPLOYEE t0_ Group by expression (NativeSql) ------------------------------- .. code-block:: java Employee_ e = new Employee_(); List> list = nativeSql.from(e).groupBy(e.departmentId).select(e.departmentId, count()).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.DEPARTMENT_ID, count(*) from EMPLOYEE t0_ group by t0_.DEPARTMENT_ID When you don't specify a group by expression, the expression is inferred from the select expression automatically. So the following code issue the same SQL statement above: .. code-block:: java Employee_ e = new Employee_(); List> list = nativeSql.from(e).select(e.departmentId, count()).fetch(); Having expression (NativeSql) ----------------------------- We support the following operators: * eq - (=) * ne - (<>) * ge - (>=) * gt - (>) * le - (<=) * lt - (<) We also support the following logical operators: * and * or * not .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List> list = nativeSql .from(e) .innerJoin(d, on -> on.eq(e.departmentId, d.departmentId)) .having(c -> c.gt(count(), 3L)) .orderBy(c -> c.asc(count())) .select(count(), d.departmentName) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select count(*), t1_.DEPARTMENT_NAME from EMPLOYEE t0_ inner join DEPARTMENT t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) group by t1_.DEPARTMENT_NAME having count(*) > ? or (min(t0_.SALARY) <= ?) order by count(*) asc Dynamic having expression (NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A having expression uses only evaluated operators to build a HAVING clause. When every operators are not evaluated in a having expression, the built statement doesn't have any HAVING clause. As well as, when every operators are not evaluated in a logical operator expression, the built statement doesn't have the logical operator expression. Order by expression (Entityql, NativeSql) ----------------------------------------- We support the following order operations: * asc * desc .. code-block:: java Employee_ e = new Employee_(); List list = entityql .from(e) .orderBy( c -> { c.asc(e.departmentId); c.desc(e.salary); }) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ order by t0_.DEPARTMENT_ID asc, t0_.SALARY desc Dynamic order by expression (NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ An order by expression uses only evaluated operators to build an ORDER BY clause. When every operators are not evaluated in a order by expression, the built statement doesn't have any ORDER BY clause. Distinct expression (Entityql, NativeSql) ----------------------------------------- .. code-block:: java List list = nativeSql .from(d) .distinct() .leftJoin(e, on -> on.eq(d.departmentId, e.departmentId)) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select distinct t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ left outer join EMPLOYEE t1_ on (t0_.DEPARTMENT_ID = t1_.DEPARTMENT_ID) Limit and Offset expression (Entityql, NativeSql) ------------------------------------------------- .. code-block:: java Employee_ e = new Employee_(); List list = nativeSql.from(e).limit(5).offset(3).orderBy(c -> c.asc(e.employeeNo)).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ order by t0_.EMPLOYEE_NO asc offset 3 rows fetch first 5 rows only Dynamic Limit and Offset expression (Entityql, NativeSql) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A limit expressions uses only non-null value to build a FETCH FIRST clause. When the value is null ,the built statement doesn't have any FETCH FIRST clause. As well as, an offset expressions uses only non-null value to build a OFFSET clause. When the value is null ,the built statement doesn't have any OFFSET clause. For Update expression (Entityql, NativeSql) ------------------------------------------- .. code-block:: java Employee_ e = new Employee_(); List list = nativeSql.from(e).where(c -> c.eq(e.employeeId, 1)).forUpdate().fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ where t0_.EMPLOYEE_ID = ? for update Union expression (NativeSql) ---------------------------- We support the following expressions: - union - unionAll - (union all) .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List> list = nativeSql .from(e) .select(e.employeeId, e.employeeName) .union(nativeSql.from(d) .select(d.departmentId, d.departmentName)) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NAME from EMPLOYEE t0_ union select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NAME from DEPARTMENT t0_ The order by expression with index is supported: .. code-block:: java Employee_ e = new Employee_(); Department_ d = new Department_(); List> list = nativeSql .from(e) .select(e.employeeId, e.employeeName) .union(nativeSql.from(d) .select(d.departmentId, d.departmentName)) .orderBy(c -> c.asc(2)) .fetch(); Derived Table expression (Entityql, NativeSql) ---------------------------------------------- We support subqueries using derived tables. However, an entity class corresponding to the derived table is required. Define the entity class corresponding to the derived table as follows: .. code-block:: java @Entity(metamodel = @Metamodel) public class NameAndAmount { private String name; private Integer amount; public NameAndAmount() {} public NameAndAmount(String accounting, BigDecimal bigDecimal) { this.name = accounting; this.amount = bigDecimal.intValue(); } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAmount() { return amount; } public void setAmount(Integer amount) { this.amount = amount; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; NameAndAmount that = (NameAndAmount) o; return Objects.equals(name, that.name) && Objects.equals(amount, that.amount); } @Override public int hashCode() { return Objects.hash(name, amount); } } A subquery using a derived table can be written as follows. .. code-block:: java Department_ d = new Department_(); Employee_ e = new Employee_(); NameAndAmount_ t = new NameAndAmount_(); SetOperand subquery = nativeSql .from(e) .innerJoin(d, c -> c.eq(e.departmentId, d.departmentId)) .groupBy(d.departmentName) .select(d.departmentName, Expressions.sum(e.salary)); List list = entityql.from(t, subquery).orderBy(c -> c.asc(t.name)).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.NAME, t0_.AMOUNT from ( select t2_.DEPARTMENT_NAME AS NAME, sum(t1_.SALARY) AS AMOUNT from EMPLOYEE t1_ inner join DEPARTMENT t2_ on (t1_.DEPARTMENT_ID = t2_.DEPARTMENT_ID) group by t2_.DEPARTMENT_NAME ) t0_ order by t0_.NAME asc Delete statement ============================ For the specification of the where expression, see :ref:`criteria_where`. The same rule is applied to delete statements. Delete settings (Entityql, NativeSql) ------------------------------------- We support the following settings: * allowEmptyWhere * batchSize * comment * ignoreVersion * queryTimeout * sqlLogType * suppressOptimisticLockException They are all optional. You can apply them as follows: .. code-block:: java Employee_ e = new Employee_(); int count = nativeSql.delete(e, settings -> { settings.setAllowEmptyWhere(true); settings.setBatchSize(20); settings.setComment("delete all"); settings.setIgnoreVersion(true); settings.setQueryTimeout(1000); settings.setSqlLogType(SqlLogType.RAW); settings.setSuppressOptimisticLockException(true); }).execute(); .. note:: If you want to build a delete statement without a WHERE clause, you have to enable the `allowEmptyWhere` setting. Delete statement (Entityql) --------------------------- .. code-block:: java Employee_ e = new Employee_(); Employee employee = entityql.from(e).where(c -> c.eq(e.employeeId, 5)).fetchOne(); Result result = entityql.delete(e, employee).execute(); The above query issues the following SQL statement: .. code-block:: sql delete from EMPLOYEE where EMPLOYEE_ID = ? and VERSION = ? Batch Delete is also supported: .. code-block:: java Employee_ e = new Employee_(); List employees = entityql.from(e).where(c -> c.in(e.employeeId, Arrays.asList(5, 6))).fetch(); BatchResult result = entityql.delete(e, employees).execute(); The execute method may throw following exceptions: * OptimisticLockException: if the entity has a version property and an update count is 0 Delete statement (NativeSql) ---------------------------- .. code-block:: java Employee_ e = new Employee_(); int count = nativeSql.delete(e).where(c -> c.ge(e.salary, new Salary("2000"))).execute(); The above query issues the following SQL statement: .. code-block:: sql delete from EMPLOYEE t0_ where t0_.SALARY >= ? Insert statement ============================ Insert settings (Entityql, NativeSql) ------------------------------------- We support the following settings: * comment * queryTimeout * sqlLogType * batchSize * excludeNull * include * exclude * ignoreGeneratedKeys They are all optional. You can apply them as follows: .. code-block:: java Department_ d = new Department_(); int count = nativeSql .insert(d, settings -> { settings.setComment("insert department"); settings.setQueryTimeout(1000); settings.setSqlLogType(SqlLogType.RAW); settings.setBatchSize(20); settings.excludeNull(true); }) .values( c -> { c.value(d.departmentId, 99); c.value(d.departmentNo, 99); c.value(d.departmentName, "aaa"); c.value(d.location, "bbb"); c.value(d.version, 1); }) .execute(); .. code-block:: java Department_ d = new Department_(); Department department = ...; Result result = entityql.insert(d, department, settings -> settings.exclude(d.departmentName, d.location) ).execute(); Insert statement (Entityql) ---------------------------- .. code-block:: java Department_ d = new Department_(); Department department = new Department(); department.setDepartmentId(99); department.setDepartmentNo(99); department.setDepartmentName("aaa"); department.setLocation("bbb"); Result result = entityql.insert(d, department).execute(); The above query issues the following SQL statement: .. code-block:: sql insert into DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION) values (?, ?, ?, ?, ?) Batch Insert is also supported: .. code-block:: java Department_ d = new Department_(); Department department = ...; Department department2 = ...; List departments = Arrays.asList(department, department2); BatchResult result = entityql.insert(d, departments).execute(); Multi-row Insert is also supported: .. code-block:: java Department_ d = new Department_(); Department department = ...; Department department2 = ...; List departments = Arrays.asList(department, department2); MultiResult result = entityql.insertMulti(d, departments).execute(); The above query issues the following SQL statement: .. code-block:: sql insert into DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION) values (?, ?, ?, ?, ?), (?, ?, ?, ?, ?) Upsert is also supported: By calling on `onDuplicateKeyUpdate`, you can update when a key is duplicated. .. code-block:: java BatchResult = entityql .insert(d, departments) .onDuplicateKeyUpdate() .execute(); By calling on `onDuplicateKeyIgnore`, you can ignore errors when a key is duplicated. .. code-block:: java BatchResult = entityql .insert(d, departments) .onDuplicateKeyIgnore() .execute(); The execute method may throw following exceptions: * UniqueConstraintException: if an unique constraint is violated Insert statement (NativeSql) ---------------------------- .. code-block:: java Department_ d = new Department_(); int count = nativeSql .insert(d) .values( c -> { c.value(d.departmentId, 99); c.value(d.departmentNo, 99); c.value(d.departmentName, "aaa"); c.value(d.location, "bbb"); c.value(d.version, 1); }) .execute(); The above query issues the following SQL statement: .. code-block:: sql insert into DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION) values (?, ?, ?, ?, ?) The execute method may throw following exceptions: * UniqueConstraintException: if an unique constraint is violated We also support the INSERT SELECT syntax as follows: .. code-block:: java Department_ da = new Department_("DEPARTMENT_ARCHIVE"); Department_ d = new Department_(); int count = nativeSql .insert(da) .select(c -> c.from(d).where(cc -> cc.in(d.departmentId, Arrays.asList(1, 2)))) .execute(); The above query issues the following SQL statement: .. code-block:: sql insert into DEPARTMENT_ARCHIVE (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION) select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_ID in (?, ?) Upsert is also supported: By calling on `onDuplicateKeyUpdate`, you can update when a key is duplicated. You can specify keys for duplicate check targets in `keys`. You can specify the value of the update in case of duplicates in `set`. .. code-block:: java int count result = nativeSql .insert(d) .values( c -> { c.value(d.departmentId, 1); c.value(d.departmentNo, 60); c.value(d.departmentName, "DEVELOPMENT"); c.value(d.location, "KYOTO"); c.value(d.version, 2); }) .onDuplicateKeyUpdate() .keys(d.departmentId) .set( c -> { c.value(d.departmentName, c.excluded(d.departmentName)); c.value(d.location, "KYOTO"); c.value(d.version, 3); }) .execute(); By calling on `onDuplicateKeyIgnore`, you can ignore errors when a key is duplicated. You can specify keys for duplicate check targets in `keys`. .. code-block:: java int count result = nativeSql .insert(d, departments) .values( c -> { c.value(d.departmentId, 1); c.value(d.departmentNo, 60); c.value(d.departmentName, "DEVELOPMENT"); c.value(d.location, "KYOTO"); c.value(d.version, 2); }) .onDuplicateKeyIgnore() .keys(d.departmentId) .execute(); Update statement ============================ For the specification of the where expression, see :ref:`criteria_where`. The same rule is applied to update statements. Update settings (Entityql, NativeSql) ------------------------------------- We support the following settings: * allowEmptyWhere * batchSize * comment * ignoreVersion * queryTimeout * sqlLogType * suppressOptimisticLockException * excludeNull * include * exclude They are all optional. You can apply them as follows: .. code-block:: java Employee_ e = new Employee_(); int count = nativeSql.update(e, settings -> { settings.setAllowEmptyWhere(true); settings.setBatchSize(20); settings.setComment("update all"); settings.setIgnoreVersion(true); settings.setQueryTimeout(1000); settings.setSqlLogType(SqlLogType.RAW); settings.setSuppressOptimisticLockException(true); settings.excludeNull(true); }).set(c -> { c.value(e.employeeName, "aaa"); }).execute(); .. code-block:: java Employee_ e = new Employee_(); Employee employee = ...; Result result = entityql.update(e, employee, settings -> settings.exclude(e.hiredate, e.salary) ).execute(); .. note:: If you want to build a update statement without a WHERE clause, you have to enable the `allowEmptyWhere` setting. Update statement (Entityql) ---------------------------- .. code-block:: java Employee_ e = new Employee_(); Employee employee = entityql.from(e).where(c -> c.eq(e.employeeId, 5)).fetchOne(); employee.setEmployeeName("aaa"); employee.setSalary(new Salary("2000")); Result result = entityql.update(e, employee).execute(); The above query issues the following SQL statement: .. code-block:: sql update EMPLOYEE set EMPLOYEE_NAME = ?, SALARY = ?, VERSION = ? + 1 where EMPLOYEE_ID = ? and VERSION = ? Batch Update is also supported: .. code-block:: java Employee_ e = new Employee_(); Employee employee = ...; Employee employee2 = ...; List departments = Arrays.asList(employee, employee2); BatchResult result = entityql.update(e, employees).execute(); The execute method may throw following exceptions: * OptimisticLockException: if the entity has a version property and an update count is 0 * UniqueConstraintException: if an unique constraint is violated Update statement (NativeSql) ---------------------------- .. code-block:: java Employee_ e = new Employee_(); int count = nativeSql .update(e) .set(c -> c.value(e.departmentId, 3)) .where( c -> { c.isNotNull(e.managerId); c.ge(e.salary, new Salary("2000")); }) .execute(); The above query issues the following SQL statement: .. code-block:: sql update EMPLOYEE t0_ set t0_.DEPARTMENT_ID = ? where t0_.MANAGER_ID is not null and t0_.SALARY >= ? The execute method may throw following exceptions: * UniqueConstraintException: if an unique constraint is violated Property expressions (Entityql, NativeSql) ========================================== All expression methods are defined in the ``org.seasar.doma.jdbc.criteria.expression.Expressions`` class. Use them with static import. Arithmetic expressions ---------------------- We provide the following methods: * add - (+) * sub - (-) * mul - (*) * div - (/) * mod - (%) You can use the ``add`` method as follows: .. code-block:: java Employee_ e = new Employee_(); int count = nativeSql .update(e) .set(c -> c.value(e.version, add(e.version, 10))) .where(c -> c.eq(e.employeeId, 1)) .execute(); The above query issues the following SQL statement: .. code-block:: sql update EMPLOYEE t0_ set t0_.VERSION = (t0_.VERSION + ?) where t0_.EMPLOYEE_ID = ? String functions ---------------- We provide the following method: * concat * lower * upper * trim * ltrim * rtrim You can use the ``concat`` method as follows: .. code-block:: java Employee_ e = new Employee_(); int count = nativeSql .update(e) .set(c -> c.value(e.employeeName, concat("[", concat(e.employeeName, "]")))) .where(c -> c.eq(e.employeeId, 1)) .execute(); The above query issues the following SQL statement: .. code-block:: sql update EMPLOYEE t0_ set t0_.EMPLOYEE_NAME = concat(?, concat(t0_.EMPLOYEE_NAME, ?)) where t0_.EMPLOYEE_ID = ? Literal expression ------------------ We provide the following method: * literal (for all basic data types) You can use the ``literal`` method as follows: .. code-block:: java Employee employee = entityql.from(e).where(c -> c.eq(e.employeeId, literal(1))).fetchOne(); The above query issues the following SQL statement: .. code-block:: sql select t0_.EMPLOYEE_ID, t0_.EMPLOYEE_NO, t0_.EMPLOYEE_NAME, t0_.MANAGER_ID, t0_.HIREDATE, t0_.SALARY, t0_.DEPARTMENT_ID, t0_.ADDRESS_ID, t0_.VERSION from EMPLOYEE t0_ where t0_.EMPLOYEE_ID = 1 .. note:: Note that the literal expressions are not recognized as bind variables. Case expression --------------- We support the following method: * when You can use the ``when`` method as follows: .. code-block:: java Employee_ e = new Employee_(); List list = nativeSql .from(e) .select( when( c -> { c.eq(e.employeeName, literal("SMITH"), lower(e.employeeName)); c.eq(e.employeeName, literal("KING"), lower(e.employeeName)); }, literal("_"))) .fetch(); The above query issues the following SQL statement: .. code-block:: sql select case when t0_.EMPLOYEE_NAME = 'SMITH' then lower(t0_.EMPLOYEE_NAME) when t0_.EMPLOYEE_NAME = 'KING' then lower(t0_.EMPLOYEE_NAME) else '_' end from EMPLOYEE t0_ Subquery select expression -------------------------- We support the following method: * select You can use the ``select`` method as follows: .. code-block:: java Employee_ e = new Employee_(); Employee_ e = new Employee_(); Employee_ e2 = new Employee_(); Department_ d = new Department_(); SelectExpression subSelect = select( c -> c.from(e2) .innerJoin(d, on -> on.eq(e2.departmentId, d.departmentId)) .where(cc -> cc.eq(e.departmentId, d.departmentId)) .groupBy(d.departmentId) .select(max(e2.salary))); int count = nativeSql .update(e) .set(c -> c.value(e.salary, subSelect)) .where(c -> c.eq(e.employeeId, 1)) .execute(); The above query issues the following SQL statement: .. code-block:: sql update EMPLOYEE t0_ set t0_.SALARY = ( select max(t1_.SALARY) from EMPLOYEE t1_ inner join DEPARTMENT t2_ on (t1_.DEPARTMENT_ID = t2_.DEPARTMENT_ID) where t0_.DEPARTMENT_ID = t2_.DEPARTMENT_ID group by t2_.DEPARTMENT_ID ) where t0_.EMPLOYEE_ID = ? User-defined expressions ------------------------ You can define user-defined expressions by calling ``Expressions.userDefined``. In the example below, the replace function is defined: .. code-block:: java UserDefinedExpression replace(PropertyMetamodel expression, PropertyMetamodel from, PropertyMetamodel to) { return Expressions.userDefined(expression, "replace", from, to, c -> { c.appendSql("replace("); c.appendExpression(expression); c.appendSql(", "); c.appendExpression(from); c.appendSql(", "); c.appendExpression(to); c.appendSql(")"); }); } You can use the replace function in your query as follows: .. code-block:: java Department_ d = new Department_(); List list = nativeSql .from(d).select(replace(d.location, Expressions.literal("NEW"), Expressions.literal("new"))).fetch(); The above query issues the following SQL statement: .. code-block:: sql select replace(t0_.LOCATION, 'NEW', 'new') from DEPARTMENT t0_ Scopes (Entityql, NativeSql) ========================================== Scoping allow you to specify commonly-used query conditions. To define a simple scope, create the class which has a method annotated with ``@Scope``: .. code-block:: java public class DepartmentScope { @Scope public Consumer onlyTokyo(Department_ d) { return c -> c.eq(d.location, "Tokyo"); } } To enable the scope, specify the above class in the scopes element of ``@Metamodel``: .. code-block:: java @Entity(metamodel = @Metamodel(scopes = { DepartmentScope.class })) public class Department { ... } Now the metamodel ``Department_`` has a ``onlyTokyo`` method. You can use it as follows: .. code-block:: java Department_ d = new Department_(); List list = entityql.from(d).where(d.onlyTokyo()).fetch(); The above query issues the following SQL statement: .. code-block:: sql select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.LOCATION = ? When you want to combine other query conditions with scopes, compose them using the `andThen` method: .. code-block:: java Department_ d = new Department_(); List list = entityql.from(d).where(d.onlyTokyo().andThen(c -> c.gt(d.departmentNo, 50))).fetch(); You can define several scopes in a class as follows: .. code-block:: java public class DepartmentScope { @Scope public Consumer onlyTokyo(Department_ d) { return c -> c.eq(d.location, "Tokyo"); } @Scope public Consumer locationStartsWith(Department_ d, String prefix) { return c -> c.like(d.location, prefix, LikeOption.prefix()); } @Scope public Consumer sortByNo(Department_ d) { return c -> c.asc(d.departmentNo); } } Tips ==== Execution in Dao (Entityql, NativeSql) -------------------------------------- It is useful to execute DSLs in the default method of the Dao interface. To get a ``config`` object, call ``Config.get(this)`` in the default method as follows: .. code-block:: java @Dao public interface EmployeeDao { default Optional selectById(Integer id) { Entityql entityql = new Entityql(Config.get(this)); Employee_ e = new Employee_(); return entityql.from(e).where(c -> c.eq(e.employeeId, id)).fetchOptional(); } } Overwriting the table name (Entityql, NativeSql) ------------------------------------------------ A metamodel constructor accepts the qualified table name and the metamodel overwrites its table name. It is useful to handle two tables that have the same data structure: .. code-block:: java Department_ da = new Department_("DEPARTMENT_ARCHIVE"); Department_ d = new Department_(); int count = nativeSql .insert(da) .select(c -> c.from(d)) .execute(); .. code-block:: sql insert into DEPARTMENT_ARCHIVE (DEPARTMENT_ID, DEPARTMENT_NO, DEPARTMENT_NAME, LOCATION, VERSION) select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ Debugging (Entityql, NativeSql) ------------------------------- To know the SQL statement built by the DSLs, use the ``asSql`` method: .. code-block:: java Department_ d = new Department_(); Listable stmt = entityql.from(d).where(c -> c.eq(d.departmentName, "SALES")); Sql sql = stmt.asSql(); System.out.printf("Raw SQL : %s\n", sql.getRawSql()); System.out.printf("Formatted SQL: %s\n", sql.getFormattedSql()); The above code prints as follows: .. code-block:: sh Raw SQL : select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = ? Formatted SQL: select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = 'SALES' The ``asSql`` method doesn't issue the SQL statement to your Database. It only builds the SQL statement and return it as an ``Sql`` object. You can also get the ``Sql`` object by calling the ``peek`` method. .. code-block:: java Department_ d = new Department_(); List locations = nativeSql .from(d) .peek(System.out::println) .where(c -> c.eq(d.departmentName, "SALES")) .peek(System.out::println) .orderBy(c -> c.asc(d.location)) .peek(sql -> System.out.println(sql.getFormattedSql())) .select(d.location) .peek(sql -> System.out.println(sql.getFormattedSql())) .fetch(); The above code prints as follows: .. code-block:: sql select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = ? select t0_.DEPARTMENT_ID, t0_.DEPARTMENT_NO, t0_.DEPARTMENT_NAME, t0_.LOCATION, t0_.VERSION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = 'SALES' order by t0_.LOCATION asc select t0_.LOCATION from DEPARTMENT t0_ where t0_.DEPARTMENT_NAME = 'SALES' order by t0_.LOCATION asc Sample projects =============== * `simple-examples `_ * `kotlin-sample `_