Hint

This article is generated by AI translation.

Queries

JdbcTemplate offers several read-only APIs. Most take an SQL string plus arguments (object or array) and return objects built from the result set; some provide callbacks/mappers for custom processing.

Query Result Sets

Query a collection of objects or maps and return them.

ObjectMapping

// Raw SQL
List<User> users = jdbc.queryForList(
        "select * from users where id > 2", User.class);

// Positional arguments
Object[] args = new Object[]{ 2 };
List<User> result = jdbc.queryForList(
        "select * from users where id > ?", args, User.class);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("id", 2);
List<User> result = jdbc.queryForList(
        "select * from users where id > :id", args, User.class);

Map

// Raw SQL
List<Map<String, Object>> users = jdbc.queryForList(
        "select * from users where id > 2");

// Positional arguments
Object[] args = new Object[]{ 2 };
List<Map<String, Object>> result = jdbc.queryForList(
        "select * from users where id > ?", args);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("id", 2);
List<Map<String, Object>> result = jdbc.queryForList(
        "select * from users where id > :id", args);

Signatures

// Object mapping or type handler
List<T> queryForList(String sql, Class<T> elementType);
List<T> queryForList(String sql, Object args, Class<T> elementType);
List<T> queryForList(String sql, PreparedStatementSetter pss, Class<T> elementType);

// RowMapper
List<T> queryForList(String sql, RowMapper<T> rowMapper);
List<T> queryForList(String sql, Object args, RowMapper<T> rowMapper);
List<T> queryForList(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper);

// Receive as List/Map without mapping
List<Map<String, Object>> queryForList(String sql);
List<Map<String, Object>> queryForList(String sql, Object args);
List<Map<String, Object>> queryForList(String sql, PreparedStatementSetter pss);

Query a Single Object

Return one row mapped to the specified type. Common use: aggregates or primary-key lookups. Multiple rows will raise an exception.

ObjectMapping

// Raw SQL
User user = jdbc.queryForObject(
        "select * from users where id = 2", User.class);

// Positional arguments
Object[] args = new Object[]{ 2 };
User user = jdbc.queryForObject(
        "select * from users where id = ?", args, User.class);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("id", 2);
User user = jdbc.queryForObject(
        "select * from users where id = :id", args, User.class);

Map

// Raw SQL
Map<String, Object> user = jdbc.queryForMap(
        "select * from users where id = 2");

// Positional arguments
Object[] args = new Object[]{ 2 };
Map<String, Object> user = jdbc.queryForMap(
        "select * from users where id = ?", args);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("id", 2);
Map<String, Object> user = jdbc.queryForMap(
        "select * from users where id = :id", args);

Signatures

// Object Mapping or Type Handler
T queryForObject(String sql, Class<T> requiredType);
T queryForObject(String sql, Object args, Class<T> requiredType);
T queryForObject(String sql, PreparedStatementSetter pss, Class<T> requiredType);

// RowMapper
T queryForObject(String sql, RowMapper<T> rowMapper);
T queryForObject(String sql, Object args, RowMapper<T> rowMapper);
T queryForObject(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper);

// Receive as List/Map without mapping
Map<String, Object> queryForMap(String sql);
Map<String, Object> queryForMap(String sql, Object args);
Map<String, Object> queryForMap(String sql, PreparedStatementSetter pss);

Query Key-Value Pairs

Use the first and second columns of the result set as Map key/value.

Example

// Raw SQL
Map<Long, String> userMap = jdbc.queryForPairs(
        "select uid, name from user where age > 2", Long.class, String.class);

// Positional arguments
Object[] args = new Object[]{ 2 };
Map<Long, String> userMap = jdbc.queryForPairs(
        "select uid, name from user where age > ?", Long.class, String.class, args);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("age", 2);
Map<Long, String> userMap = jdbc.queryForPairs(
        "select uid, name from user where age > :age", Long.class, String.class, args);

Signatures

// pairKey and pairValue are mapped via object mapping or type handlers
Map<K, V> queryForPairs(String sql,
            Class<K> pairKey, Class<V> pairValue);
Map<K, V> queryForPairs(String sql,
            Class<K> pairKey, Class<V> pairValue, Object args);
Map<K, V> queryForPairs(String sql,
            Class<K> pairKey, Class<V> pairValue, PreparedStatementSetter args);

Query Values

Values are the smallest mappable units (types registered in TypeHandlerRegistry), e.g., String, Int, Date. Two common cases:

• Value lists, e.g., an ID collection.
• Aggregate info, e.g., counts or averages.

Single value

// Raw SQL
long count = jdbc.queryForLong(
        "select count(*) from user where age > 2");

// Positional arguments
Object[] args = new Object[]{ 2 };
long count = jdbc.queryForLong(
        "select count(*) from user where age > ?", args);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("age", 2);
long count = jdbc.queryForLong(
        "select count(*) from user where age > :age", args);

Hint

• jdbc.queryForObject("select count(*) from users", Integer.class); is equivalent.

Multiple value

// Raw SQL
List<String> result = jdbc.queryForList(
        "select name from users", String.class);

// Positional arguments
Object[] args = new Object[]{ 2 };
List<String> result = jdbc.queryForList(
        "select name from users where name = ?", args, String.class);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("name", "Bob");
List<String> result = jdbc.queryForList(
        "select name from users where id = :id", args, String.class);

Signatures

// Query Long
Long queryForLong(String sql);
Long queryForLong(String sql, Object args);
Long queryForLong(String sql, PreparedStatementSetter args)

// Query int
Integer queryForInt(String sql);
Integer queryForInt(String sql, Object args);
Integer queryForInt(String sql, PreparedStatementSetter args);

// Query String
String queryForString(String sql);
String queryForString(String sql, Object args);
String queryForString(String sql, PreparedStatementSetter args);

Use ResultSetExtractor

Using the ResultSetExtractor interface is the most flexible way to handle result sets. Users need to implement the actual work of extracting results from the ResultSet themselves, without worrying about exception handling.

Example

// Custom result extraction using ResultSetExtractor
User user = jdbc.query(
    "select * from users where id = ?",
    new Object[]{ 1 },
    (ResultSetExtractor<User>) rs -> {
        if (rs.next()) {
            User u = new User();
            u.setId(rs.getLong("id"));
            u.setName(rs.getString("name"));
            return u;
        }
        return null;
    }
);

Signatures

// Use ResultSetExtractor
T query(String sql, ResultSetExtractor<T> rse);
T query(String sql, Object args, ResultSetExtractor<T> rse);
T query(String sql, PreparedStatementSetter args, ResultSetExtractor<T> rse);

Streaming Queries

For very large result sets, stream rows to save memory; each row is handled immediately in the callback.

Example

// Stream rows
RowCallbackHandler handler = (rs, rowNum) -> {
    // handle one row
};

// Raw SQL
jdbc.query("select * from user where age > 2", handler);

// Positional arguments
Object[] args = new Object[]{ 2 };
jdbc.query("select * from user where age > ?", args, handler);

// Named arguments
Map<String, Object> args = CollectionUtils.asMap("age", 2);
jdbc.query("select * from user where age > :age", args, handler);

Signatures

void query(String sql, RowCallbackHandler rch);
void query(String sql, Object args, RowCallbackHandler rch);
void query(String sql, PreparedStatementSetter args, RowCallbackHandler rch);

Map Case Sensitivity

When storing results in Map, keys are case-insensitive by default.

Example query

List<Map<String, Object>> result = jdbc.queryForList("select * from users");

Running the same table/data on different databases can yield different column name casing:

• Oracle defaults to uppercase column names.
• MySQL defaults to lowercase column names.

dbVisitor uses LinkedCaseInsensitiveMap by default to store column names case-insensitively (resultsCaseInsensitive defaults to true).

• If you want case-sensitive storage, set it to false.

JdbcTemplate jdbc = ...
jdbc.setResultsCaseInsensitive(false);

List<Map<String, Object>> result = jdbc.queryForList("select * from users");

With jdbc.setResultsCaseInsensitive(false), column names become case-sensitive and LinkedHashMap is used.

• Default true means column names are case-insensitive.
• false preserves case strictly.

Result Handlers

• ResultSetExtractor: use the ResultSetExtractor interface to customize ResultSet processing.
• RowMapper: the benefit of using RowMapper is that you don't need to worry about ResultSet processing; just focus on processing each individual row.
• RowCallbackHandler: typically used for streaming large-scale data. Data does not remain in memory during processing.
• TypeHandler: associate a TypeHandler via the @BindTypeHandler annotation to handle field serialization/deserialization in single-row-single-column or single-column-multi-row queries.

Query Argument Notes

In query-related APIs, arguments are passed uniformly as Object. Supported types include:

• Positional arguments (equivalent to Object... args)
  • Arrays, Collection, types registered in TypeRegistry
• Named arguments
  • Map, entity Bean, SqlArg (name will be fixed as arg0)
• Custom: fully custom argument-setting logic for scenarios requiring manual type control.
  • PreparedStatementSetter, SqlArgSource
• Dynamic SQL
  • Use Rules to bind arguments when building query commands, supporting named arguments.
  • Using ${...} for SQL injection does not participate in PreparedStatement but is still a form of argument passing.

Argument types are ultimately processed through TypeHandler to ensure correct mapping between JDBC types and Java types.