Skip to content

Latest commit

 

History

History
495 lines (338 loc) · 18 KB

select.rst

File metadata and controls

495 lines (338 loc) · 18 KB
Raw

Search

Annotate with @Select to Dao method for execute search.

@Config(config = AppConfig.class)
public interface EmployeeDao {
    @Select
    List<Employee> selectByDepartmentName(String departmentName);
    ...
}

SQL file is required in search. There is no feature that auto generating search SQL.

Note

You need creating entity class depending on search result. For example, result set including column in EMPLOYEE table is accepted Employee class if the Employee class that correspond EMPLOYEE table is declared. But, you need different class from the Employee entity class(For example EmmployeeDepartment class) for result set that is get by joining EMPLOYEE table and DEPARTMENT table.

Query condition

You use method parameters for query condition. Available types is below.

  • ../basic
  • ../domain
  • Arbitrary type
  • ../basic , ../domain or arbitrary type are within java.util.Optional
  • ../basic or ../domain are within java.util.Iterable
  • java.util.OptionalInt
  • java.util.OptionalLong
  • java.util.OptionalDouble

Parameters count is no limit. You can set null to parameter if parameter type is ../basic or ../domain. Parameter must not be null if the type is other than that.

Query that is used basic type or domain class

You declare ../basic or ../domain to method or parameter.

@Select
List<Employee> selectByNameAndSalary(String name, Salary salary);

You map method parameter to SQL by using SQL comment in SQL file. In SQL comment, method parameter name is referenced.

select * from employee where employee_name = /* name */'hoge' and salary > /* salary */100

Query that is used arbitrary type

You map to SQL by access field or call method there are using by dot . if using arbitrary parameter type in method parameter.

@Select
List<Employee> selectByExample(Employee employee);
select * from employee where employee_name = /* employee.name */'hoge' and salary > /* employee.getSalary() */100

You can specify multiple parameter.

@Select
List<Employee> selectByEmployeeAndDepartment(Employee employee, Department department);

Mapping to IN clauses by using Iterable.

You use subtype of java.lang.Iterable if excute searching by using IN clauses.

@Select
List<Employee> selectByNames(List<String> names);
select * from employee where employee_name in /* names */('aaa','bbb','ccc')

Single record search

You specify method return value type either of below for search single record.

  • ../basic
  • ../domain
  • ../entity
  • java.util.Map<String, Object>
  • Either ../basic , ../domain , ../entity or java.util.Map<String, Object> is within java.util.Optional
  • java.util.OptionalInt
  • java.util.OptionalLong
  • java.util.OptionalDouble
@Select
Employee selectByNameAndSalary(String name, BigDecimal salary);

null is return if return type is not Optional and result count is 0. If Ensure of search result is enabled, exception is thrown regardless return value type if search count is 0.

NonUniqueResultException is thrown if result exists 2 or more.

Multiple record search

You specify java.util.List to method return value type to for search multiple record. You can use below property in List.

  • ../basic
  • ../domain
  • ../entity
  • java.util.Map<String, Object>
  • Either ../basic or ../domain is within java.util.Optional
  • java.util.OptionalInt
  • java.util.OptionalLong
  • java.util.OptionalDouble
@Select
List<Employee> selectByNameAndSalary(String name, Salary salary);

Empty list instead of null is return if result count is 0. But if Ensure of search result is enabled, exception is thrown if search count is 0.

Stream search

You can use stream search if handle all record at one try as java.util.stream.Stream rather than recieve as java.util.List.

There are two kind in stream search such as return the return value and pass Stream to java.util.Function.

Pass to the Function

You set SelectType.STREAM to strategy property within @Select annotation and define subtype that is java.util.Function<Stream<TARGET>, RESULT> or java.util.Function<Stream<TARGET>, RESULT> to method parameter.

@Select(strategy = SelectType.STREAM)
BigDecimal selectByNameAndSalary(String name, BigDecimal salary, Function<Stream<Employee>, BigDecimal> mapper);

Caller receive stream and pass lambda expression that return result.

EmployeeDao dao = new EmployeeDaoImpl();
BigDecimal result = dao.selectByNameAndSalary(name, salary, stream -> {
    return ...;
});

Function<Stream<TARGET>, RESULT> corresponding type parameter TARGET must be either of below.

  • ../basic
  • ../domain
  • ../entity
  • java.util.Map<String, Object>
  • Either ../basic or ../domain is within java.util.Optional
  • java.util.OptionalInt
  • java.util.OptionalLong
  • java.util.OptionalDouble

Type parameter RESULT must match to Dao method return value.

If Ensure of search result is enabled, exception is thrown if search count is 0.

Return the return value

You define java.util.stream.Stream to method return value. You can use following type at property within Stream.

  • ../basic
  • ../domain
  • ../entity
  • java.util.Map<String, Object>
  • Either ../basic or ../domain within java.util.Optional
  • java.util.OptionalInt
  • java.util.OptionalLong
  • java.util.OptionalDouble
@Select
Stream<Employee> selectByNameAndSalary(String name, BigDecimal salary);

Below is a caller.

EmployeeDao dao = new EmployeeDaoImpl();
try (Stream<Employee> stream = dao.selectByNameAndSalary(name, salary)) {
  ...
}

If Ensure of search result is enabled, exception is thrown if search count is 0.

Warning

Make sure to close the stream for prevent forgetting of release the resource. If you do not close the stream, java.sql.ResultSet or java.sql.PreparedStatement , java.sql.Connection those are not closing.

Note

Consider adoption of pass to Function unless there is some particular reason, because return the return value has the risk that is forgetting of release the resource. Doma display warning message at Dao method for attention. You specify @Suppress below for suppress warning.

@Select
@Suppress(messages = { Message.DOMA4274 })
Stream<Employee> selectByNameAndSalary(String name, BigDecimal salary);

Collect search

You can use collect search if handle result as java.util.Collector.

You set SelectType.COLLECT to strategy property within @Select annotation and define subtype that is java.stream.Collector<TARGET, ACCUMULATION, RESULT> or java.stream.Collector<TARGET, ?, RESULT> to method parameter.

@Select(strategy = SelectType.COLLECT)
<RESULT> RESULT selectBySalary(BigDecimal salary, Collector<Employee, ?, RESULT> collector);

Caller pass Collector instance.

EmployeeDao dao = new EmployeeDaoImpl();
Map<Integer, List<Employee>> result =
    dao.selectBySalary(salary, Collectors.groupingBy(Employee::getDepartmentId));

Collector<TARGET, ACCUMULATION, RESULT> corresponding type parameter TARGET must be either of below.

  • ../basic
  • ../domain
  • ../entity
  • java.util.Map<String, Object>
  • Either ../basic or ../domain within java.util.Optional
  • java.util.OptionalInt
  • java.util.OptionalLong
  • java.util.OptionalDouble

Type parameter RESULT must match Dao method return value.

If Ensure of search result is enabled, exception is thrown if search count is 0.

Note

Collect search is the shortcut that pass to Function within stream search. You can do equivalent by using collect method in Stream` object that is getting from stream search.

Using search option search

You can automatically generate SQL for paging and pessimistic concurrency control from SQL file that is wrote SELECT clauses by you use SelectOptions that is represent search option.

You use SelectOptions in combination with Single record search , Multiple record search , Stream search

You define SelectOptions as Dao method parameter.

@Config(config = AppConfig.class)
public interface EmployeeDao {
    @Select
    List<Employee> selectByDepartmentName(String departmentName, SelectOptions options);
    ...
}

You can get SelectOptions instance by static get method.

SelectOptions options = SelectOptions.get();

Paging

You specify start position by offset method and get count by limit method those are within SelectOptions, and pass the SelectOptions instance to Dao method.

SelectOptions options = SelectOptions.get().offset(5).limit(10);
EmployeeDao dao = new EmployeeDaoImpl();
List<Employee> list = dao.selectByDepartmentName("ACCOUNT", options);

Paging is materialized by rewriting original SQL writing in file and executing. Original SQL must be satisfied condition below.

  • SQL is SELECT clauses
  • In top level, set operation is not executed like UNION, EXCEPT, INTERSECT.(But using at subquery is able)
  • Paging process is not included.

In addition, particular condition must be satisfied according to the database dialect.

If specify offset, there are ORDER BY clauses and all column that is specified at ORDER BY clauses is included in SELECT clauses.

Dialect Condition
Db2Dialect

If specify offset, there are ORDER BY clauses and all column that is specified at ORDER BY clauses is included in SELECT clauses.
Mssql2008Dialect

If specify offset, there are ORDER BY clauses and all column that is specified at ORDER BY clauses is included in SELECT clauses.
MssqlDialect

If specify offset, there are ORDER BY clauses.
StandardDialect

There are ORDER BY clauses and all column that is specified at ORDER BY clauses is included in SELECT clauses.

Pessimistic concurrency control

You indicate executing pessimistic concurrency control by forUpdate within SelectOptions, and pass the SelectOptions instance to Dao method.

SelectOptions options = SelectOptions.get().forUpdate();
EmployeeDao dao = new EmployeeDaoImpl();
List<Employee> list = dao.selectByDepartmentName("ACCOUNT", options);

The method that name is started forUpdate for pessimistic concurrency control is prepared such as forUpdateNowait method that do not wait for getting lock and forUpdate method that can specify lock target table or column alias.

Pessimistic concurrency control is executed by rewriting original SQL writing in file. Original SQL must be satisfied condition below.

  • SQL is SELECT clauses
  • In top level, set operation is not executed like UNION, EXCEPT, INTERSECT.(But using at subquery is able)
  • Pessimistic concurrency control process is not included.

Part or all of pessimistic concurrency control method can not used according to the database dialect.

Dialect Description
Db2Dialect

You can use forUpdate().
H2Dialect

You can use forUpdate().
HsqldbDialect

You can use forUpdate().
Mssql2008Dialect

You can use forUpdate() and forUpdateNowait(). However, FROM clauses in original SQL must consist single table.
MysqlDialect

You can use forUpdate()
OracleDialect

You can use forUpdate(), forUpdate(String... aliases), forUpdateNowait(), forUpdateNowait(String... aliases), forUpdateWait(int waitSeconds), forUpdateWait(int waitSeconds, String... aliases).
PostgresDialect

You can use forUpdate() and forUpdate(String... aliases).
StandardDialect

You can not use all of pessimistic concurrency control method.

Aggregate

You can get aggregate count by calling count method within SelectOptions. Usually, you use combination in paging option and use in case of getting all count if not narrowing by paging.

SelectOptions options = SelectOptions.get().offset(5).limit(10).count();
EmployeeDao dao = new EmployeeDaoImpl();
List<Employee> list = dao.selectByDepartmentName("ACCOUNT", options);
long count = options.getCount();

Aggregate count is get by using getCount method within SelectOptions after calling Dao method. The getCount method is return -1 if you do not execute count method before calling method.

Ensure of search result

You specify true to ensureResult property within @Select annotation if you want to ensure of search result count is over 1.

@Select(ensureResult = true)
Employee selectById(Integer id);

NoResultException is thrown if search result count is 0.

Ensure of mapping search result

You specify true to ensureResultMapping property within @Select annotation, if you want ensure that mapping result set column to all entity properties without exception.

@Select(ensureResultMapping = true)
Employee selectById(Integer id);

ResultMappingException is thrown if there are property that is not mapping to result set column.

Query timeout

You can specify seconds of query timeout to queryTimeout property within @Update annotation.

@Select(queryTimeout = 10)
List<Employee> selectAll();

Query timeout that is specified in ../config is used if queryTimeout property is not set value.

Fetch size

You can specify fetch size to fetchSize property within @Select annotation.

@Select(fetchSize = 20)
List<Employee> selectAll();

Fetch size that is specified in ../config is used if value is not set.

Max row count

You can specify max row count to maxRows property within @Select annotation.

@Select(maxRows = 100)
List<Employee> selectAll();

Max row count that is is specified in ../config is used if value is not set.

Naming rule of map's key

You can specify naming rule of map's key to mapKeyNaming property within @Select annotation, if you want mapping search result to java.util.Map<String, Object>.

@Select(mapKeyNaming = MapKeyNamingType.CAMEL_CASE)
List<Map<String, Object>> selectAll();

MapKeyNamingType.CAMEL_CASE present converting column name to camel case. In addition to there are rule that converting upper case or lower case.

The final conversion result is decide by value specified here and implementation of MapKeyNaming is specified at ../config.

SQL log output format

You can specify SQL log output format to sqlLog property within @Select annotation.

@Select(sqlLog = SqlLogType.RAW)
List<Employee> selectById(Integer id);

SqlLogType.RAW represent outputting log that is sql with a binding parameter.