Spring JdbcTemplate
Imagine you own a library with thousands of books. Without a proper catalog system, finding a book would be a nightmare. You would have to search every shelf manually. JDBC is like that manual search – it works, but it is tedious and error-prone.
Now imagine you have a computerized catalog system. You just type the book name, and the system finds it instantly. Spring JdbcTemplate is that catalog system for database operations.
What is JdbcTemplate?
JdbcTemplate is a Spring class that simplifies JDBC (Java Database Connectivity) by handling connection management, exception handling, and resource cleanup. You just write your SQL queries.
Here is how to set up JdbcTemplate:
@Configuration
public class DatabaseConfig {
@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
dataSource.setUsername("root");
dataSource.setPassword("password");
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(dataSource());
}
}
CRUD Operations with JdbcTemplate:
@Repository
public class StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
// CREATE - Insert a student
public int saveStudent(Student student) {
String sql = "INSERT INTO students (name, email, age) VALUES (?, ?, ?)";
return jdbcTemplate.update(sql,
student.getName(),
student.getEmail(),
student.getAge()
);
}
// READ - Get all students
public List getAllStudents() {
String sql = "SELECT * FROM students";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class));
}
// READ - Get student by id
public Student getStudentById(int id) {
String sql = "SELECT * FROM students WHERE id = ?";
return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), id);
}
// UPDATE - Update student
public int updateStudent(Student student) {
String sql = "UPDATE students SET name = ?, email = ?, age = ? WHERE id = ?";
return jdbcTemplate.update(sql,
student.getName(),
student.getEmail(),
student.getAge(),
student.getId()
);
}
// DELETE - Delete student
public int deleteStudent(int id) {
String sql = "DELETE FROM students WHERE id = ?";
return jdbcTemplate.update(sql, id);
}
}
Key JdbcTemplate methods:
- update() – for INSERT, UPDATE, DELETE
- query() – for SELECT returning multiple rows
- queryForObject() – for SELECT returning single row
- queryForList() – returns List of Map objects
Two Minute Drill
- JdbcTemplate simplifies JDBC by handling connection and exception management.
- Configure DataSource and JdbcTemplate as Spring beans.
- Use update() for INSERT/UPDATE/DELETE operations.
- Use query() and queryForObject() for SELECT operations.
- BeanPropertyRowMapper automatically maps rows to Java objects.
Need more clarification?
Drop us an email at career@quipoinfotech.com