How to Execute Raw SQL with Spring Boot's JdbcTemplate - Using queryForObject, RowMapper, and Batch Updates

JPA feels heavy, MyBatis feels like overkill. But you still want to write raw SQL safely. In situations like that, the handiest option is Spring’s standard JdbcTemplate.

In this article, we’ll walk through everything in a copy-and-paste ready form: from fetching a single row with queryForObject, to mapping multiple rows with RowMapper, updating with update / batchUpdate, and named binding with NamedParameterJdbcTemplate.

What Is JdbcTemplate

JdbcTemplate is a standard Spring class that takes over the entire boilerplate of JDBC processing. It handles all the tedious parts you’d otherwise write every time: acquiring and closing connections, creating the PreparedStatement, and converting SQLException into DataAccessException.

Thanks to that, we can focus solely on “what SQL to send and how to receive the results.” It becomes the first choice for smaller projects where bringing in an ORM would be excessive, or for situations where you want to fine-tune with raw SQL.

If you’re wondering how to choose between JPA and MyBatis, take a look at the MyBatis vs JPA comparison article as well. Here, we’ll set the selection discussion aside and focus on implementation.

Dependencies and Bean Injection

First, add spring-boot-starter-jdbc.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

After that, just add a DB driver (such as PostgreSQL) and write the connection details.

spring.datasource.url=jdbc:postgresql://localhost:5432/app
spring.datasource.username=app
spring.datasource.password=secret

With this, the DataSource is auto-configured, and JdbcTemplate and NamedParameterJdbcTemplate are registered as beans. All that’s left is to inject them via the constructor.

@Repository
public class UserRepository {

    private final JdbcTemplate jdbcTemplate;

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

Fetching a Single Value or Single Row with queryForObject

Scalar values like a count can be retrieved by simply passing the return type to queryForObject.

public int countAll() {
    return jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM users", Integer.class);
}

When fetching a single object by primary key, pass a RowMapper as the second argument, followed by the values to bind to the ? placeholders.

public User findById(long id) {
    return jdbcTemplate.queryForObject(
            "SELECT id, user_name, email FROM users WHERE id = ?",
            (rs, rowNum) -> new User(
                    rs.getLong("id"),
                    rs.getString("user_name"),
                    rs.getString("email")),
            id);
}

One thing to watch out for here is that queryForObject throws EmptyResultDataAccessException when there are zero results. If you’re expecting “null when not found,” it will blow up. If you want to handle the not-found case normally, it’s safer to receive a List with query and return the first element.

public Optional<User> findByEmail(String email) {
    List<User> list = jdbcTemplate.query(
            "SELECT id, user_name, email FROM users WHERE email = ?",
            userRowMapper, email);
    return list.stream().findFirst();
}

Fetching Multiple Rows with query and RowMapper

Multiple rows are received with query(sql, rowMapper). You can think of RowMapper as a function that converts a single row of the ResultSet into a single object.

public List<User> findAll() {
    return jdbcTemplate.query(
            "SELECT id, user_name, email FROM users ORDER BY id",
            (rs, rowNum) -> new User(
                    rs.getLong("id"),
                    rs.getString("user_name"),
                    rs.getString("email")));
}

If you use the same mapping repeatedly, extracting the RowMapper as a constant removes the duplication and makes the code more readable.

private static final RowMapper<User> userRowMapper = (rs, rowNum) -> new User(
        rs.getLong("id"),
        rs.getString("user_name"),
        rs.getString("email"));

Automating Mapping with BeanPropertyRowMapper

If your column names map straightforwardly to your property names, you can skip the hand-written mapping with BeanPropertyRowMapper.

public List<User> findAll() {
    return jdbcTemplate.query(
            "SELECT id, user_name, email FROM users ORDER BY id",
            new BeanPropertyRowMapper<>(User.class));
}

This automatically converts snake_case like user_name into camelCase like userName. However, it requires a corresponding setter (or an argument constructor), and relying on SELECT * tends to break when the table changes, so it’s recommended to specify the columns explicitly. For aggregate columns and the like, use AS to match the property names.

Executing INSERT, UPDATE, and DELETE with update

All modification operations use update. The return value is the number of affected rows, so you can use it to determine whether a delete or update succeeded.

public int insert(User user) {
    return jdbcTemplate.update(
            "INSERT INTO users (user_name, email) VALUES (?, ?)",
            user.getUserName(), user.getEmail());
}

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

Always pass values via ? placeholders. String concatenation like "... WHERE email = '" + email + "'" is an entry point for SQL injection, so avoid it at all costs. When you use placeholders, values are always treated as parameters and are never interpreted as SQL syntax. Beyond safety, it’s also convenient because type conversion and escaping are handled for you.

Using Named Parameters with NamedParameterJdbcTemplate

As the number of parameters grows, counting the order of ? becomes painful. That’s where NamedParameterJdbcTemplate, which supports the :name format, comes in.

private final NamedParameterJdbcTemplate namedTemplate;

public int update(User user) {
    String sql = "UPDATE users SET user_name = :name, email = :email WHERE id = :id";
    var params = new MapSqlParameterSource()
            .addValue("name", user.getUserName())
            .addValue("email", user.getEmail())
            .addValue("id", user.getId());
    return namedTemplate.update(sql, params);
}

If the entity’s property names match the parameter names, you can bind them all at once with BeanPropertySqlParameterSource, which is even more concise.

public int insert(User user) {
    String sql = "INSERT INTO users (user_name, email) VALUES (:userName, :email)";
    return namedTemplate.update(sql, new BeanPropertySqlParameterSource(user));
}

Performing Bulk INSERT and UPDATE with batchUpdate

When inserting large amounts of data, running update one record at a time in a loop gets slow due to the number of round trips. With batchUpdate, you can send them all together, which makes it visibly faster.

public void insertAll(List<User> users) {
    List<Object[]> batch = users.stream()
            .map(u -> new Object[]{u.getUserName(), u.getEmail()})
            .toList();
    jdbcTemplate.batchUpdate(
            "INSERT INTO users (user_name, email) VALUES (?, ?)", batch);
}

For the named version, pass a SqlParameterSource[]. It pays off when the record count is large (such as initial data seeding or import processing), so it’s worth remembering.

Retrieving Auto-Generated Keys

When you want to obtain the auto-generated primary key from an INSERT, use GeneratedKeyHolder.

public long insertAndReturnId(User user) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(con -> {
        var ps = con.prepareStatement(
                "INSERT INTO users (user_name, email) VALUES (?, ?)",
                new String[]{"id"});
        ps.setString(1, user.getUserName());
        ps.setString(2, user.getEmail());
        return ps;
    }, keyHolder);
    return keyHolder.getKey().longValue();
}

If you want a slightly lighter writing experience, SimpleJdbcInsert is convenient, and specifying usingGeneratedKeyColumns("id") makes it return the key. As a rough guide, choose SimpleJdbcInsert for simple INSERTs and KeyHolder when you want to hold the SQL yourself—that’s enough to go on.

Integration with @Transactional

When you want to group multiple updates into a single transaction, just add @Transactional to the Service method. JdbcTemplate automatically participates in Spring-managed transactions.

@Service
public class UserService {

    private final UserRepository repository;

    public UserService(UserRepository repository) {
        this.repository = repository;
    }

    @Transactional
    public void register(User user, Profile profile) {
        long id = repository.insertAndReturnId(user);
        repository.insertProfile(id, profile);
    }
}

By default, a rollback occurs when an unchecked exception such as RuntimeException is thrown. If an exception occurs partway through, both INSERTs are canceled, so no half-finished state remains. For finer-grained behavior such as propagation levels, refer to the transaction management article.

Summary

The JdbcTemplate API is split simply between retrieval and modification.

• For retrieval, use queryForObject (single) and query + RowMapper (multiple)
• For modification, use update (single) and batchUpdate (bulk)
• When you have many parameters, use NamedParameterJdbcTemplate for named binding

Just grasp this much, and you can write a full range of CRUD with raw SQL without bringing in an ORM. As a next step, if you’re curious about JPA’s query methods, check out the JPA query methods article, and if you want to dig into load distribution for read operations, take a look at read/write splitting across multiple data sources as well.