Select

To perform a search operation 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. You can define the SQL template either in an SQL file or in the @Sql annotation.

Search condition

Search conditions are defined using method parameters. The following parameter types are supported:

If the parameter type is either a basic type or a domain type, you can pass null as an argument. For all other parameter types, 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 . in the bind variable directive to access fields or invoke methods, 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')

Aggregate strategy

The aggregateStrategy property in @Select allows query results to be mapped to hierarchical entity structures based on a predefined aggregate strategy.

@Select(aggregateStrategy = EmployeeStrategy.class)
Employee selectByName(String name);

For more details, see Aggregate strategies.

Note

The aggregate strategy cannot be used in combination with Stream search or Collector search.

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 pagination, use the offset method to specify the starting position and the limit method to specify the number of records to retrieve in SelectOptions. Then pass this SelectOptions instance 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

Use the count method of SelectOptions to retrieve the total number of records. This is typically used with pagination to get the total record count before any pagination filtering is applied.

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

To ensure that at least one result is returned from the search, set the ensureResult property of the @Select annotation to true.

@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 using the queryTimeout property in 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 using the fetchSize property in 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 using the maxRows property in 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 using the mapKeyNaming property 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 using the sqlLog property of the @Select annotation.

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

SqlLogType.RAW outputs the SQL statement with its binding parameters in the log.