Select

To execute a search using the SELECT statement, annotate the DAO method with @Select.

@Dao
public interface EmployeeDao {
    @Select
    List<Employee> selectByDepartmentName(String departmentName);
    ...
}

The @Select annotation requires an SQL template. Describe the SQL template in an SQL file or in the @Sql annotation.

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.

Search condition

The search condition make use of method parameters. The available types of parameters are as follows:

If the parameter type is one of either Basic classes or Domain classes, it is permissible to set the argument to null. If the parameter type is anything other than these, the argument must not be null.

Query using basic classes or domain classes

Declare Basic classes or Domain classes as method parameters.

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

Use the Bind variable directive to bind method parameters to SQL.

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

Query using arbitrary type

When using arbitrary types as method parameters, use a dot . within the bind variable directive to perform field access or method invocation, and bind the result to SQL.

@Select
List<Employee> selectByExample(Employee employee);

select * from employee where employee_name = /* employee.name */'hoge' and salary > /* employee.getSalary() */100

Multiple parameters can be specified.

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

Mapping to the IN clause

To bind to the IN clause, use a subtype of java.lang.Iterable as the parameter.

@Select
List<Employee> selectByNames(List<String> names);

select * from employee where employee_name in /* names */('aaa','bbb','ccc')

Search options

By using SelectOptions, you can convert the SELECT statement into SQL for paging or pessimistic locking purposes.

SelectOptions is defined as a parameter of the DAO method.

@Dao
public interface EmployeeDao {
    @Select
    List<Employee> selectByDepartmentName(String departmentName, SelectOptions options);
    ...
}

You can obtain an instance of SelectOptions through a 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 can indicate pessimistic concurrency control using the forUpdate method of SelectOptions.

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

SelectOptions provides methods for pessimistic concurrency control with names starting with forUpdate, such as forUpdate to specify aliases for tables or columns to be locked, and forUpdateNowait to acquire locks without waiting.

Pessimistic concurrency control is achieved by rewriting 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 pessimistic concurrency control operations.

Depending on the dialect, some or all of the methods for pessimistic concurrency control may not be available for use.

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()

MysqlDialect (V8)

You can use forUpdate(), forUpdate(String… aliases), forUpdateNowait(), and forUpdateNowait(String… aliases).

OracleDialect

You can use forUpdate(), forUpdate(String… aliases), forUpdateNowait(), forUpdateNowait(String… aliases), forUpdateWait(int waitSeconds), and 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.

Count

By calling the count method of SelectOptions, you can retrieve the total count of records. Typically, this is used in combination with paging options to retrieve the total count of records when not filtering through 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();

The total count of records is obtained using the getCount method of SelectOptions after calling the DAO method. If the count method hasn’t been executed before the DAO method call, the getCount method will return -1.

Ensure the existence of search results

If you want to ensure that there is at least one search result, specify true for the ensureResult element of @Select.

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

If there are no search results, a NoResultException will be thrown.

Ensure the mapping of search results

If you want to ensure that all columns of the result set are mapped to properties of the entity without missing any, specify true for the ensureResultMapping element of @Select.

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

If there are properties in the entity that are not mapped to columns in the result set, a ResultMappingException will be thrown.

Query timeout

You can specify the query timeout in seconds for the queryTimeout property within the @Select annotation.

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

If the value of the queryTimeout property is not set, the query timeout specified in the Configuration will be used.

Fetch size

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

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

If the value of the fetchSize property is not set, the fetch size specified in the Configuration will be used.

Max row count

You can specify the maximum number of rows in the maxRows property within the @Select annotation.

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

If the value of the maxRows property is not set, the maximum number of rows specified in the Configuration will be used.

The naming convention for the keys of the Map

If you are mapping search results to java.util.Map<String, Object>, you can specify the naming convention for the keys of the map in the mapKeyNaming element of @Select.

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

MapKeyNamingType.CAMEL_CASE indicates that the column names will be converted to camel case. There are also conventions to convert column names to uppercase or lowercase.

The final conversion result is determined by the value specified here and the implementation of MapKeyNaming specified in the Configuration.

Output format of SQL logs

You can specify the format of SQL log output in the sqlLog element of the @Select annotation.

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

SqlLogType.RAW indicates logging SQL with bound parameters.