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
List<T> queryForObject(String sql, Class<T> requiredType);
List<T> queryForObject(String sql, Object args, Class<T> requiredType);
List<T> queryForObject(String sql, PreparedStatementSetter pss, Class<T> requiredType);

// RowMapper
List<T> queryForObject(String sql, RowMapper<T> rowMapper);
List<T> queryForObject(String sql, Object args, RowMapper<T> rowMapper);
List<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

ResultSetExtractor gives the most flexibility; implement how to extract results from ResultSet without worrying about exception handling.

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

// 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: customize ResultSet processing.
• RowMapper: focus on per-row mapping without handling ResultSet iteration.
• RowCallbackHandler: stream large datasets without keeping rows in memory.
• TypeHandler: via @BindTypeHandler, handle serialization/deserialization for one cell or single-column queries.

Query Argument Notes

Query APIs accept arguments as Object. Common forms:

• Positional varargs (Object... args): pass scalars or objects in order.
• PreparedStatementSetter: fully custom binding logic when you need manual type control.
• Named/bean arguments: when SQL is built with Rules or uses named placeholders, pass Map, entity beans, SqlArg, or other iterable structures for name-based binding.
• Arrays/collections: arrays or Collection are expanded in order (same as Object...).

Arguments are processed through TypeHandler to map Java types to JDBC types correctly.