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