How do I use JDBC with Spring?

By I Wayan Saryada in Spring JDBC 0 Comment

You can use JDBC with Spring through Spring’s JDBC support, especially JdbcTemplate. It removes much of the repetitive JDBC boilerplate such as opening connections, closing resources, handling PreparedStatement, iterating ResultSet, and translating SQLException into Spring’s DataAccessException hierarchy.

The typical setup is:

  1. Configure a DataSource
  2. Create a JdbcTemplate
  3. Inject it into a repository/DAO class
  4. Use it to run queries and updates

1. Add Spring JDBC and a database driver

For a Maven project, you usually need spring-jdbc and your database driver.

Example for PostgreSQL:

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

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.7.7</version>
    </dependency>
</dependencies>

If you use Spring Boot, you would usually use:

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

plus the database driver.

2. Configure a DataSource

In plain Spring Java configuration, you can define a DataSource bean.

A common choice is HikariCP:

package org.kodejava.spring;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.time.Duration;

@Configuration
public class DatabaseConfig {

    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl("jdbc:postgresql://localhost:5432/app");
        config.setUsername("postgres");
        config.setPassword("postgres");

        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(Duration.ofSeconds(5).toMillis());
        config.setPoolName("AppHikariPool");

        return new HikariDataSource(config);
    }
}

You would also need the HikariCP dependency if you are not using Spring Boot:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>6.3.0</version>
</dependency>

3. Create a JdbcTemplate bean

Spring can create JdbcTemplate from the configured DataSource.

package org.kodejava.spring;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class JdbcConfig {

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

If you are using Spring Boot, Boot usually autoconfigures JdbcTemplate for you as long as a DataSource exists.

4. Create a model class

For example, suppose you have a users table:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL
);

You can map rows to a Java object:

package org.kodejava.spring;

public class User {
    private Long id;
    private String name;
    private String email;

    public User() {
    }

    public User(Long id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }
}

5. Use JdbcTemplate in a repository

A repository class can receive JdbcTemplate through constructor injection.

package org.kodejava.spring;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@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,
                (rs, rowNum) -> new User(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("email")
                ),
                id
        );
    }

    public List<User> findAll() {
        String sql = """
                SELECT id, name, email
                FROM users
                ORDER BY id
                """;

        return jdbcTemplate.query(
                sql,
                (rs, rowNum) -> new User(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("email")
                )
        );
    }

    public int insert(User user) {
        String sql = """
                INSERT INTO users (id, name, email)
                VALUES (?, ?, ?)
                """;

        return jdbcTemplate.update(
                sql,
                user.getId(),
                user.getName(),
                user.getEmail()
        );
    }

    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);
    }
}

6. Enable component scanning

If you are using plain Spring, your configuration class should scan for repositories and services.

package org.kodejava.spring;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@ComponentScan("org.kodejava.spring")
public class AppConfig {
}

Then you can bootstrap Spring:

package org.kodejava.spring;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class SpringJdbcExample {
    public static void main(String[] args) {
        try (AnnotationConfigApplicationContext context =
                     new AnnotationConfigApplicationContext(AppConfig.class, DatabaseConfig.class, JdbcConfig.class)) {

            UserRepository userRepository = context.getBean(UserRepository.class);

            User user = new User(1L, "Alice", "[email protected]");
            userRepository.insert(user);

            User savedUser = userRepository.findById(1L);
            System.out.println(savedUser.getName());
        }
    }
}

7. Handling query results safely

queryForObject() is convenient, but it throws an exception when no row is found. You can handle that explicitly:

package org.kodejava.spring;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Optional;

public class UserRepository {
    private final JdbcTemplate jdbcTemplate;

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

    public Optional<User> findOptionalById(Long id) {
        String sql = """
                SELECT id, name, email
                FROM users
                WHERE id = ?
                """;

        try {
            User user = jdbcTemplate.queryForObject(
                    sql,
                    (rs, rowNum) -> new User(
                            rs.getLong("id"),
                            rs.getString("name"),
                            rs.getString("email")
                    ),
                    id
            );

            return Optional.ofNullable(user);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }
}

8. Using NamedParameterJdbcTemplate

For more readable SQL parameters, use NamedParameterJdbcTemplate.

package org.kodejava.spring;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class NamedUserRepository {
    private final NamedParameterJdbcTemplate jdbcTemplate;

    public NamedUserRepository(NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public User findById(Long id) {
        String sql = """
                SELECT id, name, email
                FROM users
                WHERE id = :id
                """;

        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("id", id);

        return jdbcTemplate.queryForObject(
                sql,
                params,
                (rs, rowNum) -> new User(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("email")
                )
        );
    }
}

You can define it as a bean:

package org.kodejava.spring;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class NamedJdbcConfig {

    @Bean
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
}

9. Transactions

For multiple database operations that should succeed or fail together, use Spring transactions.

Add a transaction manager:

package org.kodejava.spring;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
public class TransactionConfig {

    @Bean
    public TransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

Then use @Transactional in a service:

package org.kodejava.spring;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class UserService {
    private final UserRepository userRepository;

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

    @Transactional
    public void registerUser(User user) {
        userRepository.insert(user);

        // Other related database operations can go here.
        // If a RuntimeException occurs, the transaction is rolled back.
    }
}

10. Typical Spring Boot configuration

If you are using Spring Boot, the configuration is simpler.

application.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/app
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.hikari.maximum-pool-size=10

Repository:

package org.kodejava.spring;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserRepository {
    private final JdbcTemplate jdbcTemplate;

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

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

Summary

To use JDBC with Spring:

  1. Add spring-jdbc and your database driver.
  2. Configure a DataSource.
  3. Create or autoconfigure JdbcTemplate.
  4. Inject JdbcTemplate into repository classes.
  5. Use query(), queryForObject(), and update() for database operations.
  6. Use @Transactional for operations that need transaction boundaries.

For most applications, prefer JdbcTemplate over raw JDBC because it keeps the code shorter, safer, and easier to test.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.