Tell me about JdbcTemplate and its main features in working with databases.

JdbcTemplate is a helper class from Spring Framework that simplifies working with JDBC API. It handles low-level details such as resource management (connections, statements, result sets), exception handling, and executing SQL queries.

Main features:

• Resource management: Automatically opens and closes connections, statements, and result sets, preventing resource leaks.
• Exception handling: Converts native JDBC exceptions into the org.springframework.dao hierarchy, which is more semantic and easily catchable.
• Code simplification: Significantly reduces boilerplate code needed for simple database operations.
• Various query methods: Provides convenient methods for executing different types of SQL queries:
  • execute(): For executing any SQL command.
  • update(): For INSERT, UPDATE, DELETE. Returns the number of affected rows.
  • queryForObject(): For obtaining a single value or object.
  • queryForList(): For obtaining a list of objects.
  • query(): For obtaining a list of arbitrary objects using RowMapper.
• Named parameters support: NamedParameterJdbcTemplate allows using named parameters (:paramName) instead of positional (?), improving readability and security with many parameters.
• Transaction support: Easily integrates with declarative (@Transactional) and programmatic transaction management in Spring.

Example usage:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

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

public class UserRepository {

    private final JdbcTemplate jdbcTemplate;

    // Inject DataSource via constructor
    public UserRepository(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void addUser(String username, String email) {
        String sql = "INSERT INTO users (username, email) VALUES (?, ?)";
        jdbcTemplate.update(sql, username, email);
    }

    public User findUserById(long id) {
        String sql = "SELECT id, username, email FROM users WHERE id = ?";
        // queryForObject with RowMapper to convert ResultSet row to User object
        return jdbcTemplate.queryForObject(sql, new UserRowMapper(), id);
    }

    public List<User> findAllUsers() {
        String sql = "SELECT id, username, email FROM users";
        // query with RowMapper to get list of User objects
        return jdbcTemplate.query(sql, new UserRowMapper());
    }

    // Inner class for mapping ResultSet rows to User objects
    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.setUsername(rs.getString("username"));
            user.setEmail(rs.getString("email"));
            return user;
        }
    }

    // User class example
    public static class User {
        private long id;
        private String username;
        private String email;

        // Getters and setters
        public long getId() { return id; }
        public void setId(long id) { this.id = id; }
        public String getUsername() { return username; }
        public void setUsername(String username) { this.username = username; }
        public String getEmail() { return email; }
        public void setEmail(String email) { this.email = email; }
    }
}