Fetching Records with Spring JdbcTemplate: A Step-by-Step Guide

Fetching records using Spring JdbcTemplate?

Using Spring's JdbcTemplate is a common way to interact with a relational database in a Spring application. It simplifies the process of executing SQL queries and handling results. Below is a step-by-step guide on how to fetch records using JdbcTemplate.

Step 1: Add Dependencies

Make sure you have the necessary dependencies in your pom.xml if you are using Maven. You will need Spring JDBC and a database driver (e.g., H2, MySQL, PostgreSQL).

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.10</version> <!-- Use the latest version -->
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Step 2: Configure DataSource

You need to configure a DataSource bean in your Spring configuration. This can be done in a Java configuration class or in an XML configuration file.

Java Configuration Example

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:mem:testdb");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }
}

Step 3: Create a JdbcTemplate Bean

You can create a JdbcTemplate bean that will use the DataSource.

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

@Configuration
public class AppConfig {

    // DataSource bean as shown above

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

Step 4: Fetch Records

Now you can use JdbcTemplate to fetch records from the database. Here’s an example of how to do this.

Example Entity

Assume you have a simple entity class:

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

    // Getters and Setters
}

Repository Class

Create a repository class to handle database operations.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<User> findAll() {
        String sql = "SELECT id, name, email FROM users"; // Adjust the SQL as per your table structure
        return jdbcTemplate.query(sql, new UserRowMapper());
    }

    private static class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setEmail(rs.getString("email"));
            return user;
        }
    }
}

Step 5: Use the Repository

You can now use the UserRepository in your service or controller to fetch users.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserRepository userRepository;

    @GetMapping("/users")
    public List<User> getUsers() {
        return userRepository.findAll();
    }
}

Summary

1. Add the necessary dependencies.
2. Configure a DataSource bean.
3. Create a JdbcTemplate bean.
4. Implement a repository to fetch records using JdbcTemplate.
5. Use the repository in your service or controller.

This is a basic example, and you can expand upon it by adding error handling, transaction management, and more complex queries as needed.