Spring JDBC with JdbcTemplate: Database Access Done Right

If you're building a payment processing system, you don't want an ORM deciding when to flush your transactions. I've seen production outages caused by JPA's auto-flush behavior at exactly the wrong moment. That's where JdbcTemplate shines—it gives you direct SQL control without the ceremony of raw JDBC.

Spring's JdbcTemplate has been battle-tested since Spring 1.0. It wraps JDBC with a clean API, handles resource management, and maps results to objects. But here's the hard truth: most teams misuse it. They either treat it like a toy (using it for everything when JPA would be better) or they over-engineer with unnecessary abstractions.

In this guide, I'll show you the patterns I've used across fintech, e-commerce, and SaaS platforms. We'll cover setup, CRUD operations, batch processing, error handling, and the gotchas that the official docs gloss over. By the end, you'll know exactly when to use JdbcTemplate—and when to walk away.

Setting Up JdbcTemplate: The Right Way

Let's cut through the noise. You need a DataSource and a JdbcTemplate bean. If you're using Spring Boot, auto-configuration does most of the work—just add the dependency and configure your datasource in application.properties. But here's what the docs won't tell you: always set a meaningful pool size and timeout. I once saw a team use the default HikariCP settings (pool size 10) and wonder why their batch jobs timed out.

First, add the dependency: ``xml <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> ``

Then configure your datasource. For production, never use spring.datasource.url without tuning the pool: ``properties spring.datasource.url=jdbc:postgresql://localhost:5432/mydb spring.datasource.username=user spring.datasource.password=pass spring.datasource.hikari.maximum-pool-size=20 spring.datasource.hikari.minimum-idle=5 spring.datasource.hikari.connection-timeout=30000 spring.datasource.hikari.idle-timeout=600000 ``

Now create your JdbcTemplate bean. If you have multiple datasources, qualify them with @Primary or @Qualifier: ```java @Configuration public class DatabaseConfig {

@Bean public JdbcTemplate jdbcTemplate(DataSource dataSource) { return new JdbcTemplate(dataSource); } } ```

That's it. You're ready to query. But remember: JdbcTemplate is thread-safe, so inject it as a singleton. Don't create a new instance per request—I've seen that anti-pattern in legacy code.

@Configuration
public class DatabaseConfig {

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        jt.setFetchSize(1000); // adjust based on expected result size
        return jt;
    }
}

CRUD Operations with JdbcTemplate

Let's build a simple UserRepository using JdbcTemplate. I'll show you the patterns I use in production—no fluff.

Querying for a single object: ``java public User findById(Long id) { String sql = "SELECT id, name, email FROM users WHERE id = ?"; return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id); } ``

Querying for a list: ``java public List<User> findAll() { String sql = "SELECT id, name, email FROM users"; return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class)); } ``

Insert and get generated key: ``java public User save(User user) { String sql = "INSERT INTO users (name, email) VALUES (?, ?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(connection -> { PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"}); ps.setString(1, user.getName()); ps.setString(2, user.getEmail()); return ps; }, keyHolder); user.setId(keyHolder.getKey().longValue()); return user; } ``

Update and delete: ```java public int update(User user) { String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?"; return jdbcTemplate.update(sql, user.getName(), user.getEmail(), user.getId()); }

public int deleteById(Long id) { String sql = "DELETE FROM users WHERE id = ?"; return jdbcTemplate.update(sql, id); } ```

Notice I use BeanPropertyRowMapper—it maps columns to fields by name. But be careful: if your column names don't match exactly (e.g., user_name vs userName), you'll get nulls. In that case, write a custom RowMapper or use aliases in SQL.

@Repository
public class UserRepository {

    private final JdbcTemplate jdbcTemplate;

    public UserRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public User findById(Long id) {
        String sql = "SELECT id, name, email FROM users WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    }

    public List<User> findAll() {
        String sql = "SELECT id, name, email FROM users";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

    public User save(User user) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
            ps.setString(1, user.getName());
            ps.setString(2, user.getEmail());
            return ps;
        }, keyHolder);
        user.setId(keyHolder.getKey().longValue());
        return user;
    }

    public int update(User user) {
        String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
        return jdbcTemplate.update(sql, user.getName(), user.getEmail(), user.getId());
    }

    public int deleteById(Long id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
}

Batch Operations: Bulk Inserts and Updates

Batch operations are where JdbcTemplate really shines. If you're inserting thousands of rows one by one, you're doing it wrong. Use batchUpdate().

Batch insert with simple parameters: ``java public int[] batchInsert(List<User> users) { String sql = "INSERT INTO users (name, email) VALUES (?, ?)"; List<Object[]> batchArgs = new ArrayList<>(); for (User user : users) { batchArgs.add(new Object[]{user.getName(), user.getEmail()}); } return jdbcTemplate.batchUpdate(sql, batchArgs); } ``

Batch update with NamedParameterJdbcTemplate (cleaner): ``java public int[] batchInsertNamed(List<User> users) { String sql = "INSERT INTO users (name, email) VALUES (:name, :email)"; SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(users.toArray()); return namedParameterJdbcTemplate.batchUpdate(sql, batch); } ``

Important: Enable driver-level batching. For PostgreSQL, add ?reWriteBatchedInserts=true to your JDBC URL. Without it, batchUpdate() still sends individual INSERT statements. For MySQL, add ?rewriteBatchedStatements=true. This can give you a 10x performance boost.

Batch size matters. I've seen teams set batch size to 100,000 and wonder why the database stalls. Start with 500-1000 and tune based on your row size and network latency. Monitor the time it takes and adjust.

public int[] batchInsert(List<User> users) {
    String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
    List<Object[]> batchArgs = new ArrayList<>();
    for (User user : users) {
        batchArgs.add(new Object[]{user.getName(), user.getEmail()});
    }
    return jdbcTemplate.batchUpdate(sql, batchArgs);
}

What the Official Docs Won't Tell You

I've been using JdbcTemplate since Spring 2.5. Here are the gotchas that cost me weekends.

1. queryForObject throws EmptyResultDataAccessException when no rows are returned. The docs mention it, but they don't tell you that catching it is ugly. Instead, use query and check the list size, or use Optional: ``java public Optional<User> findById(Long id) { String sql = "SELECT id, name, email FROM users WHERE id = ?"; List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), id); return users.isEmpty() ? Optional.empty() : Optional.of(users.get(0)); } ``

2. BeanPropertyRowMapper silently maps columns by name. If a column is missing or renamed, the field stays null—no error. Always test your mappings with a query that returns actual data. I once spent hours debugging why email was null after a schema migration.

3. batchUpdate() doesn't actually batch by default. The JDBC driver sends individual statements unless you enable driver-level batching. This is the most common performance trap. Always verify by enabling JDBC logging.

4. KeyHolder with PreparedStatementCreator is verbose but necessary. The lambda version is cleaner, but you must specify the column names for the generated keys. If you get -1 as the key, you forgot to pass the column names.

5. Transaction management is not automatic. JdbcTemplate doesn't participate in transactions unless you wrap calls with @Transactional. If you're using it outside a transaction, each call is auto-committed. This can lead to partial updates if you don't manage transactions explicitly.

6. Logging is your best friend. Enable logging.level.org.springframework.jdbc=DEBUG to see every SQL statement and parameter. This alone saves hours of debugging.

public Optional<User> findByIdSafe(Long id) {
    String sql = "SELECT id, name, email FROM users WHERE id = ?";
    List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), id);
    return users.isEmpty() ? Optional.empty() : Optional.of(users.get(0));
}

Error Handling and Exception Translation

Spring's DataAccessException hierarchy is your friend. JdbcTemplate automatically translates SQLException into meaningful exceptions like DuplicateKeyException, DataIntegrityViolationException, and BadSqlGrammarException. But here's the rub: you need to handle them properly.

Don't catch generic Exception. Be specific: ``java try { jdbcTemplate.update(sql, params); } catch (DuplicateKeyException e) { // handle duplicate entry } catch (DataIntegrityViolationException e) { // handle constraint violation } catch (DataAccessException e) { // generic database error } ``

Use @Repository annotation to enable persistence exception translation. Without it, you get raw SQLException instead of Spring's exceptions. This is a common oversight.

Log the SQL and parameters when an exception occurs. I always include a utility method: ``java private void logError(String sql, Object[] params, DataAccessException e) { log.error("SQL: {} | Params: {}", sql, Arrays.toString(params), e); } ``

Consider using @Transactional with rollback rules. By default, runtime exceptions trigger rollback. Checked exceptions do not. If you want a checked exception to rollback, use @Transactional(rollbackFor = {CheckedException.class}).

@Repository
public class UserRepository {

    public User save(User user) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
                ps.setString(1, user.getName());
                ps.setString(2, user.getEmail());
                return ps;
            }, keyHolder);
            user.setId(keyHolder.getKey().longValue());
            return user;
        } catch (DuplicateKeyException e) {
            throw new UserAlreadyExistsException("User with email " + user.getEmail() + " already exists", e);
        } catch (DataAccessException e) {
            logError(sql, new Object[]{user.getName(), user.getEmail()}, e);
            throw new DatabaseException("Failed to save user", e);
        }
    }
}

When to Use JdbcTemplate vs JPA vs Spring Data JDBC

This is the million-dollar question. Here's my rule of thumb:

• Use JdbcTemplate when you need fine-grained SQL control, batch operations, or when working with legacy databases where JPA mappings are painful. Also, if performance is critical and you can't afford ORM overhead.
• Use Spring Data JPA when you have a standard domain model, CRUD operations, and want automatic query generation. It's great for rapid development.
• Use Spring Data JDBC as a middle ground—it gives you aggregate-oriented persistence without JPA's complexity. Good for microservices with simple entities.

When NOT to use JdbcTemplate: - Complex object graphs with many relationships (use JPA). - When you need lazy loading or caching (JPA provides that). - If your team is more productive with JPA (but don't use JPA just because it's trendy).

Real-world example: In a high-frequency trading system, we used JdbcTemplate for all database access. JPA's caching and lazy loading caused inconsistent reads. JdbcTemplate gave us predictable performance.

// JdbcTemplate: explicit SQL
public List<User> findActiveUsers() {
    return jdbcTemplate.query("SELECT * FROM users WHERE active = true", 
        new BeanPropertyRowMapper<>(User.class));
}

// Spring Data JPA: derived query
public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByActiveTrue();
}

// Spring Data JDBC: similar to JPA but without lazy loading
public interface UserRepository extends CrudRepository<User, Long> {
    List<User> findByActiveTrue();
}