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

Spring JDBC - Agent Reference

Spring JDBC provides JDBC abstraction with automatic resource management, exception translation, and template-based operations.

Quick Setup

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>7.0.1</version>
</dependency>

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.JdbcClient;
import javax.sql.DataSource;

// Setup
DataSource ds = new DriverManagerDataSource("jdbc:postgresql://localhost:5432/mydb", "user", "pass");
JdbcTemplate jdbc = new JdbcTemplate(ds);
NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(ds);
JdbcClient client = JdbcClient.create(ds);  // Spring 6.1+

Core Classes Quick Reference

Class	Use Case	Parameters
`JdbcTemplate`	Basic JDBC operations	Positional `?`
`NamedParameterJdbcTemplate`	Named parameters	`:paramName`
`JdbcClient`	Fluent API (modern)	Both positional and named
`SimpleJdbcInsert`	Metadata-driven inserts	Auto key retrieval
`SimpleJdbcCall`	Stored procedures/functions	Auto parameter mapping

Operation Decision Tree

Query Operations:

Single value → queryForObject(sql, Class, args)
Single object → queryForObject(sql, RowMapper, args)
List of objects → query(sql, RowMapper, args)
List of simple types → queryForList(sql, Class, args)
Map results → queryForMap(sql, args) or queryForList(sql, args)
Large dataset → queryForStream(sql, RowMapper, args)
Custom processing → query(sql, ResultSetExtractor, args)

Update Operations:

Simple update → update(sql, args)
With generated keys → update(PreparedStatementCreator, KeyHolder)
Batch updates → batchUpdate(sql, List<Object[]>)
Named batch → namedTemplate.batchUpdate(sql, SqlParameterSource[])

Complex Operations:

Stored procedures → SimpleJdbcCall
Dynamic inserts → SimpleJdbcInsert
Transactions → @Transactional or TransactionTemplate

Common Patterns

Query Single Object

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

Query List

List<User> users = jdbc.query(
    "SELECT id, name, email FROM users WHERE status = ?",
    (rs, row) -> new User(rs.getLong("id"), rs.getString("name"), rs.getString("email")),
    "active"
);

Insert with Generated Key

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");
    return ps;
}, keyHolder);
Long id = keyHolder.getKey().longValue();

Named Parameters

Map<String, Object> params = Map.of("status", "active", "minAge", 18);
List<User> users = named.query(
    "SELECT * FROM users WHERE status = :status AND age >= :minAge",
    params,
    (rs, row) -> new User(...)
);

Fluent API (JdbcClient)

User user = client.sql("SELECT * FROM users WHERE id = ?")
    .param(123)
    .query((rs, row) -> new User(...))
    .single();

List<User> users = client.sql("SELECT * FROM users WHERE status = :status")
    .param("status", "active")
    .query((rs, row) -> new User(...))
    .list();

Batch Insert

List<Object[]> batch = List.of(
    new Object[]{"John", "john@example.com"},
    new Object[]{"Jane", "jane@example.com"}
);
int[] counts = jdbc.batchUpdate("INSERT INTO users (name, email) VALUES (?, ?)", batch);

SimpleJdbcInsert

SimpleJdbcInsert insert = new SimpleJdbcInsert(ds)
    .withTableName("users")
    .usingGeneratedKeyColumns("id");

Number id = insert.executeAndReturnKey(Map.of("name", "John", "email", "john@example.com"));

Transaction

@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    jdbc.update("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromId);
    jdbc.update("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toId);
}

API Reference Structure

Core Operations

Core JDBC Operations - JdbcTemplate, NamedParameterJdbcTemplate, JdbcClient, common queries
Simplified Operations - SimpleJdbcInsert, SimpleJdbcCall

Data Handling

Result & Parameter Handling - RowMapper, callbacks, parameter sources
Advanced Patterns - Batch operations, SQL objects, reusable queries

Infrastructure

DataSource Management - DataSource implementations, routing, proxies
Embedded Databases - H2, HSQLDB, Derby setup
Transaction Management - @Transactional, programmatic transactions
Exception Translation - SQLException to DataAccessException mapping

Specialized Features

LOB Support - BLOB/CLOB handling
Key Generation - Auto-generated keys, sequences
Database Initialization - SQL script execution
SqlRowSet - Disconnected, scrollable result sets

Exception Hierarchy

DataAccessException (unchecked)
├── BadSqlGrammarException (invalid SQL)
├── DataIntegrityViolationException
│   └── DuplicateKeyException
├── DataAccessResourceFailureException
│   └── CannotGetJdbcConnectionException
├── InvalidResultSetAccessException
└── UncategorizedSQLException

Handle specific exceptions:

try {
    jdbc.update("INSERT INTO users (email) VALUES (?)", "user@example.com");
} catch (DuplicateKeyException e) {
    // Handle duplicate
} catch (DataAccessException e) {
    // Handle other errors
}

Thread Safety

Thread-safe (reusable):

JdbcTemplate, NamedParameterJdbcTemplate, JdbcClient
SimpleJdbcInsert, SimpleJdbcCall (after configuration)
SqlQuery, SqlUpdate, StoredProcedure (after compilation)

Not thread-safe:

RowMapper, PreparedStatementSetter, BatchPreparedStatementSetter (single-use)

Performance Guidelines

Reuse template instances - Create once, inject everywhere
Use batch operations - 10-100x faster for bulk operations
Set fetch size - For large result sets: jdbc.setFetchSize(1000)
Stream large datasets - Use queryForStream() instead of query()
Use connection pooling - HikariCP, not DriverManagerDataSource
Cache prepared statements - Most pools do this automatically
Use named parameters - Better readability, same performance
Optimize batch size - Test 100-1000 rows per batch