Query

LambdaTemplate supports multiple result forms:

List: map results to a list of objects.
Object: map a single result.
Count: rewrite the query to COUNT using dialect support.
Streaming: handle results via RowCallbackHandler or ResultSetExtractor.
Pagination: paginate queries.

Tip

For building conditions used in queries, see where builder.

Query list

Run a query and map results to a list.

Map to entity type
LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .le(User::getId, 100)   // match id <= 100
               .queryForList();        // map to entity type

Map to a specific type
LambdaTemplate lambda = ...;

List<UserVO> result = null;
result = lambda.query(User.class)
               .le(User::getId, 100)        // match id <= 100
               .queryForList(UserVO.class); // map to target type

When mapping User results to another type, mapping follows the target type’s rules. See Object Mapping for details.

Map to Map
LambdaTemplate lambda = ...;

List<Map<String,Object>> result = null;
result = lambda.query(User.class)
               .le(User::getId, 100)    // match id <= 100
               .queryForMapList();      // map to Map

Map keys correspond to User property names.

LambdaTemplate lambda = ...;
RowMapper<UserVO> rowMapper = new BeanMappingRowMapper(UserVO.class);

List<UserVO> result = null;
result = lambda.query(User.class)
               .le(User::getId, 100)    // match id <= 100
               .queryForList(rowMapper);// use RowMapper for mapping

Use RowMapper to customize row mapping from ResultSet.

Query a single object

Return the first matching object, or null when no records match. To select the first row in a specific order, request that order explicitly where the data source supports it.

Map to entity type
LambdaTemplate lambda = ...;

User result = null;
result = lambda.query(User.class)
               .eq(User::getId, 100)   // match id = 100
               .queryForObject();      // map to entity type

Map to a specific type
LambdaTemplate lambda = ...;

UserVO result = null;
result = lambda.query(User.class)
               .eq(User::getId, 100)          // match id = 100
               .queryForObject(UserVO.class); // map to target type

When mapping User results to another type, mapping follows the target type’s rules. See Object Mapping for details.

Map to Map
LambdaTemplate lambda = ...;

Map<String,Object> result = null;
result = lambda.query(User.class)
               .eq(User::getId, 100) // match id = 100
               .queryForMap();       // map to Map

Map keys correspond to User property names.

LambdaTemplate lambda = ...;
RowMapper<UserVO> rowMapper = new BeanMappingRowMapper(UserVO.class);

UserVO result = null;
result = lambda.query(User.class)
               .eq(User::getId, 100)     // match id = 100
               .queryForObject(rowMapper);// use RowMapper for mapping

Use RowMapper to customize row mapping from ResultSet.

Count

dbVisitor rewrites the query into a COUNT using dialect support. Example:

-- original query
select id, name from users where id <= 100;
-- rewritten as count query
select count(*) from (select id, name from users where id <= 100) as TEMP_T;

LambdaTemplate lambda = ...;
RowMapper<UserVO> rowMapper = new BeanMappingRowMapper(UserVO.class);

int count = lambda.query(User.class)
                  .le(User::getId, 100)  // match id <= 100
                  .queryForCount();      // count as int
                //.queryForLargeCount(); // count as long

Choose queryForCount or queryForLargeCount based on size needs.

Streaming

Use RowCallbackHandler to process each row without collecting the result set.

RowCallbackHandler handler = new RowCallbackHandler() {
    @Override
    public void processRow(ResultSet rs, int rowNum) throws SQLException {
        ...
    }
};

LambdaTemplate lambda = ...;

lambda.query(User.class)
      .eq(User::getId, 100)  // match id = 100
      .query(handler);       // process via RowCallbackHandler

Use ResultSetExtractor to process the entire ResultSet.

ResultSetExtractor<Map<Integer, String>> extractor = new ResultSetExtractor<Map<Integer, String>>() {
    public Map<Integer, String> extractData(ResultSet rs) throws SQLException {
        Map<Integer, String> hashMap = new HashMap<>();

        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            hashMap.put(id, name);
        }

        return hashMap;
    }
};

LambdaTemplate lambda = ...;

Map<Integer, String> result = null;
result = lambda.query(User.class)
               .eq(User::getId, 100)  // match id = 100
               .query(extractor);     // process via ResultSetExtractor

Pagination

Use the paging methods below to fetch a page. To traverse records in batches, use iteration in pages.

Tip

Pagination depends on dialect support; see database support for supported databases.

dbVisitor provides built-in pagination with no extra config. Usage:

Using page arguments
LambdaTemplate lambda = ...;

List<User> result = null;
result = lambda.query(User.class)
               .le(User::getId, 100) // match id <= 100
               .initPage(10, 1)      // page size 10, fetch page 2 (0-based)
               .queryForList();      // paged query

Using a Page object
LambdaTemplate lambda = ...;

// Page object
Page page = new PageObject();
page.setPageNumberOffset(1); // start page numbering at 1
page.setPageSize(10);        // 10 rows per page
page.setCurrentPage(2);      // fetch page 2

// paged query
List<User> result = null;
result = lambda.query(User.class)
               .le(User::getId, 100) // match id <= 100
               .usePage(page)        // pagination info
               .queryForList();      // paged query

The Pagination Object provides many useful methods. See Pagination Object for more details.
The query list and result processing result receiving methods can be combined with pagination.

Iterate in pages

Use an iterator when you do not want to collect every row in one List. The following query fetches up to 100 rows per page:

Iterator<? extends UserInfo> users = lambda.query(UserInfo.class)
        .asc("id").iteratorByBatch(100);
while (users.hasNext()) {
    UserInfo user = users.next();
    // Process this record.
}

iteratorForLimit(500, 100) limits the total to 500 records with a page size of 100; a negative total limit means no total limit. Use a stable order where the data source supports one. This is paginated iteration, not a JDBC server-side streaming guarantee; concurrent changes can affect the pages.

Query list​

Query a single object​

Count​

Streaming​

Pagination​

Iterate in pages​