Search
Annotate with @Select
to Dao method for execute search.
@Dao
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 EmployeeDepartment 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.
Arbitrary type
Basic classes , Domain classes or arbitrary type are within java.util.Optional
Basic classes or Domain classes 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 classes or Domain classes.
Parameter must not be null
if the type is other than that.
Query that is used basic type or domain class
You declare Basic classes or Domain classes 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 execute 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.
java.util.Map<String, Object>
Either Basic classes , Domain classes , Entity classes 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
.
java.util.Map<String, Object>
Either Basic classes or Domain classes 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 receiving 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.
java.util.Map<String, Object>
Either Basic classes or Domain classes 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
.
java.util.Map<String, Object>
Either Basic classes or Domain classes 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.
java.util.Map<String, Object>
Either Basic classes or Domain classes 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.
@Dao
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
To implement paging, specify the starting position with the offset
method and
the number of records to retrieve with the limit
method in SelectOptions
.
Then, pass an instance of SelectOptions
to the DAO method.
SelectOptions options = SelectOptions.get().offset(5).limit(10);
EmployeeDao dao = new EmployeeDaoImpl();
List<Employee> list = dao.selectByDepartmentName("ACCOUNT", options);
Paging is achieved by modifying the original SQL, which must meet the following conditions:
it is a SELECT statement.
it does not perform set operations like UNION, EXCEPT, or INTERSECT at the top level (though subqueries are allowed).
it does not include paging operations.
Additionally, specific conditions must be met according to the dialect.
Dialect |
Condition |
---|---|
Db2Dialect |
When specifying an offset, all columns listed in the ORDER BY clause must be included in the SELECT clause. |
Mssql2008Dialect |
When specifying an offset, all columns listed in the ORDER BY clause must be included in the SELECT clause. |
MssqlDialect |
When specifying an offset, the ORDER BY clause is required. |
StandardDialect |
The ORDER BY clause is required. All columns listed in the ORDER BY clause must be included in the SELECT clause. |
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 Configuration 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 Configuration 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 Configuration 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 Configuration.
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.