Skip to main content

Queries

JdbcTemplate offers APIs for reading result sets; method names do not force a connection or operation to be read-only. 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.

Choose a Method First​

GoalMethod
Query multiple Bean or Map rowsqueryForList
Query one Bean rowqueryForObject
Query one Map rowqueryForMap
Query a single value, such as count or sumqueryForLong, queryForObject
Query two columns as a MapqueryForPairs
Customize row mappingRowMapper
Customize whole ResultSet handlingResultSetExtractor
Process large results row by rowRowCallbackHandler or streaming query

Query Result Sets​

Query a collection of objects or maps and return them.

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

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

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

Query a Single Object​

Return one row mapped to the specified type. Common use: aggregates or primary-key lookups. No rows returns null; multiple rows raise an exception. Use wrapper types for potentially null scalar results.

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

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

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

Query Key-Value Pairs​

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

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

// Positional arguments (independent example)
{
    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 (independent example)
{
    Map<String, Object> namedArgs = CollectionUtils.asMap("age", 2);
    Map<Long, String> userMap = jdbc.queryForPairs(
            "select uid, name from user where age > :age", Long.class, String.class, namedArgs);
}

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.
// Raw SQL (independent example)
{
    long count = jdbc.queryForLong(
            "select count(*) from user where age > 2");
}

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

// Named arguments (independent example)
{
    Map<String, Object> namedArgs = CollectionUtils.asMap("age", 2);
    long count = jdbc.queryForLong(
            "select count(*) from user where age > :age", namedArgs);
}
Hint
  • jdbc.queryForObject("select count(*) from users", Integer.class); is equivalent.

Result Receiving​

These examples cover the most common return shapes. For more control over result mapping, choose from the following table; see Receiving Results for details.

GoalUseDetails
Convert each row to an objectRowMapperRowMapper
Receive column names and values without an entity classList<Map<String,Object>>List/Map
Consume large results row by row without collecting in memoryRowCallbackHandlerRowCallbackHandler
Control the whole ResultSet traversal yourselfResultSetExtractorResultSetExtractor
Serialize or deserialize single-column valuesTypeHandlerType Handling

Use ResultSetExtractor​

User user = jdbc.query(
    "select * from users where id = ?",
    new Object[]{ 1 },
    (ResultSetExtractor<User>) rs -> {
        if (!rs.next()) {
            return null;
        }

        User u = new User();
        u.setId(rs.getLong("id"));
        u.setName(rs.getString("name"));
        return u;
    }
);

Row-by-Row Processing​

Callbacks avoid collecting all results in application memory. On-demand server reads still depend on the JDBC driver, fetchSize and connection settings.

RowCallbackHandler handler = (rs, rowNum) -> {
    // Process each row without collecting a List
};

jdbc.query("select * from users where age > ?", new Object[]{ 18 }, handler);

Map Case Sensitivity​

By default, Map result keys are case-insensitive. Different databases may return column names with different casing, such as uppercase in Oracle or lowercase in MySQL. If you need strict case-sensitive keys, see List/Map.

Query Argument Notes​

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

  • Positional arguments (put multiple values in an array or collection; the method takes Object args, not varargs)
    • 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.
  • Dynamic SQL
    • Use Rules to bind arguments when building query commands, supporting named arguments.
    • Using ${...} for SQL text substitution 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.