Skip to main content

v6.7.0: queryForPairs

· 3 min read
ZhaoYongChun
ZhaoYongChun
Maintainers
Hint

This article is generated by AI translation.

"Select two columns and build a Map" is a common pattern — ID-to-name, code-to-description, etc. The new queryForPairs in dbVisitor 6.7.0 returns a Map<K, V> directly in one call, no manual iteration needed.

The Pain: Repetitive Boilerplate​

The old approach:

// Build an ID → Name mapping
List<UserInfo> users = lambda.query(UserInfo.class)
      .between(UserInfo::getId, 1001, 1003)
      .queryForList();

Map<Integer, String> idToName = new LinkedHashMap<>();
for (UserInfo u : users) {
    idToName.put(u.getId(), u.getName());
}

First query the list, then iterate and populate. If you only need two columns, fetching the entire entity is wasteful.

The New Way: One Line​

Fluent API — Lambda Style​

Map<Integer, String> idToName = lambda.query(UserInfo.class)
      .between(UserInfo::getId, 1001, 1003)
      .queryForPairs(UserInfo::getId, UserInfo::getName, Integer.class, String.class);

// {1001="Alice", 1002="Bob", 1003="Charlie"}

The framework automatically narrows the SELECT to the specified two columns, then builds a Map with the first column as Key and the second as Value.

Fluent API — String Style​

Map<Integer, String> idToName = lambda.query(UserInfo.class)
      .between("id", 1001, 1003)
      .queryForPairs("id", "name", Integer.class, String.class);

JdbcTemplate — Raw SQL​

queryForPairs is also available at the JdbcTemplate level, supporting multiple parameter-passing styles:

// 1. No parameters
Map<Integer, String> result = jdbcTemplate.queryForPairs(
    "SELECT id, name FROM user_info WHERE id BETWEEN 1001 AND 1003",
    Integer.class, String.class
);

// 2. Positional parameters
Map<String, Integer> nameToAge = jdbcTemplate.queryForPairs(
    "SELECT name, age FROM user_info WHERE id >= ? AND id <= ?",
    String.class, Integer.class,
    new Object[] { 1001, 1003 }
);

// 3. Named parameters
Map<String, Object> params = new HashMap<>();
params.put("minId", 1001);
params.put("maxId", 1003);

Map<Integer, String> idToEmail = jdbcTemplate.queryForPairs(
    "SELECT id, email FROM user_info WHERE id >= :minId AND id <= :maxId",
    Integer.class, String.class,
    params
);

Design Details​

Automatic Type Conversion​

queryForPairs internally uses PairsResultSetExtractor, which automatically selects the appropriate TypeHandler based on the specified Key/Value types:

// Long Key + Date Value — automatic type conversion
Map<Long, Date> idToDate = jdbcTemplate.queryForPairs(
    "SELECT CAST(id AS BIGINT), create_time FROM user_info",
    Long.class, Date.class
);

Single-Column Scenario​

If the SELECT returns only one column, Value defaults to null:

Map<Integer, Object> ids = jdbcTemplate.queryForPairs(
    "SELECT id FROM user_info",
    Integer.class, Object.class
);
// {1001=null, 1002=null, 1003=null}

Key Conflicts​

When multiple rows share the same Key, the later value overwrites the earlier one — consistent with Java's Map.put() behavior.

Use Cases​

  • Dropdown list loading: SELECT code, label FROM dict_item WHERE type = ?
  • Batch ID-to-name resolution: SELECT id, name FROM user WHERE id IN (...)
  • Configuration reading: SELECT key, value FROM app_config WHERE group = ?
  • Association pre-fetching: Replace N+1 queries with pre-loaded association maps