or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

advanced-patterns.md core-operations.md database-init.md datasource.md embedded-database.md exception-translation.md index.md key-generation.md lob-support.md result-parameter-handling.md simplified-operations.md sqlrowset.md transactions.md

tile.json

Result & Parameter Handling

Row mapping, callbacks, and parameter sources for processing query results and providing SQL parameters.

Row Mapping

RowMapper

Maps individual rows to objects.

@FunctionalInterface
public interface RowMapper<T> {
    T mapRow(ResultSet rs, int rowNum) throws SQLException;
}

Usage:

RowMapper<User> userMapper = (rs, rowNum) -> new User(
    rs.getLong("id"),
    rs.getString("name"),
    rs.getString("email"),
    rs.getBoolean("active")
);

List<User> users = jdbc.query("SELECT * FROM users", userMapper);

// Inline
User user = jdbc.queryForObject(
    "SELECT * FROM users WHERE id = ?",
    (rs, row) -> new User(rs.getLong("id"), rs.getString("name"), rs.getString("email")),
    123
);

Complex mapping:

RowMapper<Order> orderMapper = (rs, rowNum) -> {
    Order order = new Order();
    order.setId(rs.getLong("id"));
    order.setUserId(rs.getLong("user_id"));
    order.setTotal(rs.getBigDecimal("total"));
    order.setStatus(OrderStatus.valueOf(rs.getString("status")));

    Timestamp ts = rs.getTimestamp("created_at");
    order.setCreatedAt(ts != null ? ts.toLocalDateTime() : null);

    return order;
};

NULL handling:

RowMapper<User> mapper = (rs, row) -> {
    User user = new User();
    user.setId(rs.getLong("id"));
    user.setName(rs.getString("name"));

    String middleName = rs.getString("middle_name");
    user.setMiddleName(rs.wasNull() ? null : middleName);

    // For primitives
    int age = rs.getInt("age");
    user.setAge(rs.wasNull() ? null : age);

    return user;
};

BeanPropertyRowMapper

Automatic bean mapping via reflection (matches column names to bean properties).

import org.springframework.jdbc.core.BeanPropertyRowMapper;

// Auto-mapping
List<User> users = jdbc.query(
    "SELECT id, name, email FROM users",
    new BeanPropertyRowMapper<>(User.class)
);

// Case-insensitive
RowMapper<User> mapper = new BeanPropertyRowMapper<>(User.class);
mapper.setPrimitivesDefaultedForNullValue(true);  // Default primitives to 0/false

Column name mapping:

user_id → userId
email_address → emailAddress
first_name → firstName

DataClassRowMapper

For Java records and data classes (Spring 6.1+).

import org.springframework.jdbc.core.DataClassRowMapper;

// Java record
record User(Long id, String name, String email) {}

List<User> users = jdbc.query(
    "SELECT id, name, email FROM users",
    new DataClassRowMapper<>(User.class)
);

SingleColumnRowMapper

Maps single column to simple type.

import org.springframework.jdbc.core.SingleColumnRowMapper;

List<String> names = jdbc.query(
    "SELECT name FROM users",
    new SingleColumnRowMapper<>(String.class)
);

List<Long> ids = jdbc.query(
    "SELECT id FROM users WHERE status = ?",
    new SingleColumnRowMapper<>(Long.class),
    "active"
);

ColumnMapRowMapper

Maps rows to Map<String, Object>.

import org.springframework.jdbc.core.ColumnMapRowMapper;

List<Map<String, Object>> rows = jdbc.query(
    "SELECT * FROM users",
    new ColumnMapRowMapper()
);

for (Map<String, Object> row : rows) {
    Long id = (Long) row.get("id");
    String name = (String) row.get("name");
}

Result Extraction

ResultSetExtractor

Processes entire ResultSet, returns single result.

@FunctionalInterface
public interface ResultSetExtractor<T> {
    T extractData(ResultSet rs) throws SQLException, DataAccessException;
}

Group by:

Map<String, List<Order>> ordersByUser = jdbc.query(
    "SELECT user_id, id, total FROM orders",
    (ResultSetExtractor<Map<String, List<Order>>>) rs -> {
        Map<String, List<Order>> map = new LinkedHashMap<>();
        while (rs.next()) {
            String userId = rs.getString("user_id");
            Order order = new Order(rs.getLong("id"), rs.getBigDecimal("total"));
            map.computeIfAbsent(userId, k -> new ArrayList<>()).add(order);
        }
        return map;
    }
);

Aggregate:

BigDecimal totalRevenue = jdbc.query(
    "SELECT amount FROM orders WHERE status = 'completed'",
    (ResultSetExtractor<BigDecimal>) rs -> {
        BigDecimal total = BigDecimal.ZERO;
        while (rs.next()) {
            total = total.add(rs.getBigDecimal("amount"));
        }
        return total;
    }
);

Custom data structure:

TreeNode root = jdbc.query(
    "SELECT id, parent_id, name FROM categories ORDER BY parent_id, id",
    (ResultSetExtractor<TreeNode>) rs -> {
        // Build tree structure
        Map<Long, TreeNode> nodes = new HashMap<>();
        TreeNode root = null;

        while (rs.next()) {
            Long id = rs.getLong("id");
            Long parentId = rs.getLong("parent_id");
            String name = rs.getString("name");

            TreeNode node = new TreeNode(id, name);
            nodes.put(id, node);

            if (rs.wasNull()) {  // parent_id is NULL
                root = node;
            } else {
                TreeNode parent = nodes.get(parentId);
                parent.addChild(node);
            }
        }

        return root;
    }
);

RowCallbackHandler

Processes rows without returning result (side effects).

@FunctionalInterface
public interface RowCallbackHandler {
    void processRow(ResultSet rs) throws SQLException;
}

Email notification:

jdbc.query(
    "SELECT email FROM users WHERE status = 'new'",
    (RowCallbackHandler) rs -> {
        String email = rs.getString("email");
        emailService.sendWelcome(email);
    }
);

Export to file:

try (PrintWriter writer = new PrintWriter("users.csv")) {
    jdbc.query(
        "SELECT id, name, email FROM users",
        (RowCallbackHandler) rs -> {
            writer.printf("%d,%s,%s%n",
                rs.getLong("id"),
                rs.getString("name"),
                rs.getString("email")
            );
        }
    );
}

Streaming processing:

AtomicInteger count = new AtomicInteger();
jdbc.query(
    "SELECT * FROM large_table",
    (RowCallbackHandler) rs -> {
        processRecord(rs);
        if (count.incrementAndGet() % 1000 == 0) {
            logger.info("Processed {} records", count.get());
        }
    }
);

JDBC Callbacks

ConnectionCallback

Direct Connection access.

Boolean supportsTransactions = jdbc.execute(
    (ConnectionCallback<Boolean>) con ->
        con.getMetaData().supportsTransactions()
);

StatementCallback

List<String> tables = jdbc.execute(
    (StatementCallback<List<String>>) stmt -> {
        List<String> result = new ArrayList<>();
        ResultSet rs = stmt.executeQuery("SHOW TABLES");
        while (rs.next()) {
            result.add(rs.getString(1));
        }
        return result;
    }
);

PreparedStatementCallback

Integer count = jdbc.execute(
    "SELECT COUNT(*) FROM users WHERE status = ?",
    (PreparedStatementCallback<Integer>) ps -> {
        ps.setString(1, "active");
        ResultSet rs = ps.executeQuery();
        return rs.next() ? rs.getInt(1) : 0;
    }
);

PreparedStatementCreator

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbc.update(
    con -> {
        PreparedStatement ps = con.prepareStatement(
            "INSERT INTO users (name, email) VALUES (?, ?)",
            Statement.RETURN_GENERATED_KEYS
        );
        ps.setString(1, "John");
        ps.setString(2, "john@example.com");
        ps.setFetchSize(1000);  // Custom configuration
        return ps;
    },
    keyHolder
);

PreparedStatementSetter

List<User> users = jdbc.query(
    "SELECT * FROM users WHERE status = ? AND country = ?",
    ps -> {
        ps.setString(1, "active");
        ps.setString(2, "USA");
    },
    new UserRowMapper()
);

Parameter Sources

SqlParameterSource

Interface for providing parameters by name.

public interface SqlParameterSource {
    boolean hasValue(String paramName);
    Object getValue(String paramName);
    int getSqlType(String paramName);
    String getTypeName(String paramName);
    String[] getParameterNames();
}

MapSqlParameterSource

Fluent Map-based parameters.

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

MapSqlParameterSource params = new MapSqlParameterSource()
    .addValue("name", "John")
    .addValue("email", "john@example.com")
    .addValue("status", "active");

namedTemplate.update(
    "INSERT INTO users (name, email, status) VALUES (:name, :email, :status)",
    params
);

// With SQL types
params.addValue("id", 123, Types.BIGINT);
params.addValue("amount", new BigDecimal("99.99"), Types.DECIMAL);
params.addValue("data", jsonData, Types.OTHER, "jsonb");

// From existing Map
Map<String, Object> map = Map.of("name", "John", "email", "john@example.com");
params = new MapSqlParameterSource(map);
params.addValue("status", "active");

BeanPropertySqlParameterSource

JavaBean-based parameters.

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;

User user = new User();
user.setName("John");
user.setEmail("john@example.com");
user.setStatus("active");

SqlParameterSource params = new BeanPropertySqlParameterSource(user);

namedTemplate.update(
    "INSERT INTO users (name, email, status) VALUES (:name, :email, :status)",
    params
);

Note: Does NOT support nested properties. For nested objects, use MapSqlParameterSource:

OrderRequest request = new OrderRequest();
request.setUserId(123);
request.setAddress(new Address("123 Main St", "Springfield"));

MapSqlParameterSource params = new MapSqlParameterSource()
    .addValue("userId", request.getUserId())
    .addValue("street", request.getAddress().getStreet())
    .addValue("city", request.getAddress().getCity());

EmptySqlParameterSource

For queries without parameters.

import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;

SqlRowSet rowSet = namedTemplate.queryForRowSet(
    "SELECT COUNT(*) FROM users",
    EmptySqlParameterSource.INSTANCE
);

// Or use empty Map
List<User> users = namedTemplate.query(
    "SELECT * FROM users",
    Map.of(),
    new UserRowMapper()
);

Batch Parameter Sources

SqlParameterSourceUtils

Creates SqlParameterSource arrays for batch operations.

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;

// From beans
List<User> users = getUsers();
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(users.toArray());

namedTemplate.batchUpdate(
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    batch
);

// From maps
Map<String, Object>[] maps = new Map[]{
    Map.of("name", "User1", "email", "user1@example.com"),
    Map.of("name", "User2", "email", "user2@example.com")
};
batch = SqlParameterSourceUtils.createBatch(maps);

Manual batch creation

List<User> users = getUsers();
SqlParameterSource[] batch = users.stream()
    .map(user -> new MapSqlParameterSource()
        .addValue("name", user.getName())
        .addValue("email", user.getEmail())
        .addValue("status", "active")
        .addValue("createdAt", LocalDateTime.now()))
    .toArray(SqlParameterSource[]::new);

int[] counts = namedTemplate.batchUpdate(
    "INSERT INTO users (name, email, status, created_at) VALUES (:name, :email, :status, :createdAt)",
    batch
);

Conditional Parameters

MapSqlParameterSource params = new MapSqlParameterSource();
StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");

if (nameFilter != null) {
    sql.append(" AND name LIKE :namePattern");
    params.addValue("namePattern", "%" + nameFilter + "%");
}

if (statusFilter != null) {
    sql.append(" AND status = :status");
    params.addValue("status", statusFilter);
}

if (minAge != null) {
    sql.append(" AND age >= :minAge");
    params.addValue("minAge", minAge);
}

List<User> users = namedTemplate.query(sql.toString(), params, new UserRowMapper());

Performance Notes

RowMapper:

Lightweight, created per query
Use lambda for simple mappings
Extract to class for reuse

BeanPropertyRowMapper:

Reflection overhead
Good for prototyping
Consider custom RowMapper for performance-critical code

ResultSetExtractor:

Full control over ResultSet
Use for complex aggregations
Better than multiple queries

RowCallbackHandler:

Memory efficient
Use for side effects
No result accumulation

Parameter Sources:

MapSqlParameterSource: Most flexible, good performance
BeanPropertySqlParameterSource: Convenient but reflection overhead
Choose based on use case, not performance difference

Version

Tile

Files