Working with JdbcTemplate

Learn how to run queries and updates with JdbcTemplate, using queryForObject, query, and update in a working example.

Introduction

JdbcTemplate is the workhorse class of Spring JDBC - it is what you actually call to run SQL. In this lesson, you will use its three most common methods: queryForObject for a single row, query for multiple rows, and update for INSERT, UPDATE, and DELETE statements, and then put them together into a small, complete repository class.

What You Will Learn

How to obtain and configure a JdbcTemplate bean.
How queryForObject fetches a single row into a Java object.
How query fetches a list of rows with a RowMapper.
How update runs INSERT, UPDATE, and DELETE statements.
How these methods come together in a working repository class.

Setting Up JdbcTemplate

JdbcTemplate needs a DataSource to work with. You can declare it as a bean explicitly, or - in a Spring Boot application with a DataSource already configured - simply autowire it directly, since Spring Boot auto-configures a JdbcTemplate bean for you.

@Configuration
public class JdbcConfig {

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

queryForObject: Fetching One Row

queryForObject runs a query expected to return exactly one row and maps it into a single object using a RowMapper - a small callback that turns one ResultSet row into a Java object.

public Book findById(long id) {
    String sql = "SELECT id, title, author FROM books WHERE id = ?";
    return jdbcTemplate.queryForObject(sql, (rs, rowNum) ->
        new Book(
            rs.getLong("id"),
            rs.getString("title"),
            rs.getString("author")
        ), id);
}

Example Call

Click Run to see what this code prints.

Zero or Many Rows

queryForObject throws EmptyResultDataAccessException if no row matches, and IncorrectResultSizeDataAccessException if more than one row matches. Both are subclasses of DataAccessException.

query: Fetching Many Rows

query runs a statement and maps every returned row into a List, using the same RowMapper idea as queryForObject.

public List<Book> findByAuthor(String author) {
    String sql = "SELECT id, title, author FROM books WHERE author = ?";
    return jdbcTemplate.query(sql, (rs, rowNum) ->
        new Book(
            rs.getLong("id"),
            rs.getString("title"),
            rs.getString("author")
        ), author);
}

Example Result

Click Run to see what this code prints.

update: Insert, Update, Delete

Any statement that does not return rows - INSERT, UPDATE, or DELETE - goes through the update method, which returns the number of rows affected.

public int save(Book book) {
    String sql = "INSERT INTO books (title, author) VALUES (?, ?)";
    return jdbcTemplate.update(sql, book.getTitle(), book.getAuthor());
}

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

A Complete Repository Example

Putting all three methods together into one @Repository bean gives you a clean, testable data access class the service layer can depend on.

@Repository
public class BookRepository {

    private final JdbcTemplate jdbcTemplate;

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

    private final RowMapper<Book> bookRowMapper = (rs, rowNum) -> new Book(
        rs.getLong("id"), rs.getString("title"), rs.getString("author")
    );

    public Book findById(long id) {
        String sql = "SELECT id, title, author FROM books WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, bookRowMapper, id);
    }

    public List<Book> findAll() {
        return jdbcTemplate.query("SELECT id, title, author FROM books", bookRowMapper);
    }

    public int save(Book book) {
        String sql = "INSERT INTO books (title, author) VALUES (?, ?)";
        return jdbcTemplate.update(sql, book.getTitle(), book.getAuthor());
    }

    public int deleteById(long id) {
        return jdbcTemplate.update("DELETE FROM books WHERE id = ?", id);
    }
}

Reusable RowMapper

Defining bookRowMapper once as a field avoids repeating the same row-to-object mapping logic in every method that queries the books table.

Common Mistakes

Avoid These Mistakes

Using queryForObject for a query that can return zero rows, without handling EmptyResultDataAccessException.
Building SQL by string concatenation instead of using ? placeholders, which opens the door to SQL injection.
Duplicating RowMapper logic across multiple methods instead of extracting one reusable mapper.
Forgetting that update() returns an int row count, not the generated id - use a KeyHolder if you need the generated key.
Putting SQL directly inside a @Controller or @Service instead of isolating it in a @Repository.

Best Practices

Always use parameterized queries (? placeholders) - never concatenate user input into SQL.
Isolate all JdbcTemplate calls inside @Repository classes, not controllers or services.
Extract a reusable RowMapper for any entity queried in more than one place.
Handle EmptyResultDataAccessException explicitly wherever a missing row is a normal, expected outcome.
Keep repository methods focused on one query each, named after what they return.

Frequently Asked Questions

How do I get a generated auto-increment id after an insert?

Use a GeneratedKeyHolder together with a PreparedStatementCreator passed into jdbcTemplate.update(), which lets you retrieve the generated key after the insert.

Can JdbcTemplate run batch inserts?

Yes - batchUpdate() accepts a SQL statement and a list of argument arrays, executing them all in a single batch for much better performance than looping over update().

Is NamedParameterJdbcTemplate different?

Yes - it lets you use named placeholders like :title instead of positional ? marks, which is easier to read and less error-prone once a query has many parameters.

Key Takeaways

queryForObject fetches exactly one row and maps it with a RowMapper.
query fetches any number of rows into a List using the same RowMapper approach.
update handles INSERT, UPDATE, and DELETE and returns the number of affected rows.
A RowMapper is a small callback that converts one ResultSet row into a Java object.
Isolating JdbcTemplate calls inside a @Repository keeps data access testable and swappable.

Summary

JdbcTemplate gives you full SQL control with almost none of raw JDBC's boilerplate. With controllers, services, and now a repository layer in place, the next lesson looks at how to actually test all of this - starting with unit tests for individual Spring beans.

Next Lesson →