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

How can you fetch records by spring JdbcTemplate?

In Spring, you can use JdbcTemplate to fetch records from a database easily. JdbcTemplate is part of the Spring JDBC module and provides a simplified way to interact with a relational database. Below are the steps to fetch records using JdbcTemplate.

Step 1: Set Up Your Spring Project

Make sure you have the necessary dependencies in your pom.xml (for Maven) or build.gradle (for Gradle). For Maven, you might include:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.10</version> <!-- Use the latest version -->
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.3.10</version> <!-- Use the latest version -->
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.3.10</version> <!-- Use the latest version -->
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.200</version> <!-- Example database -->
</dependency>

Step 2: Configure DataSource

You need to configure a DataSource bean. This can be done in a configuration class or XML configuration. Here’s an example using Java configuration:

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 uses the DataSource:

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

@Configuration
public class JdbcConfig {

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

Step 4: Fetch Records Using JdbcTemplate

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

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";
        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.getLong("id"));
            user.setName(rs.getString("name"));
            user.setEmail(rs.getString("email"));
            return user;
        }
    }
}

Step 5: Define the User Model

You need a model class to represent the records fetched from the database:

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

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

Step 6: 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

In summary, you set up a DataSource, create a JdbcTemplate bean, define a repository to fetch records, and use a model class to represent the data. This is a basic example, and you can expand upon it with error handling, transactions, and more complex queries as needed.