Tell me about JdbcTemplate and its use in Java…

JdbcTemplate is the primary data access tool in the Spring Framework, significantly simplifying working with the JDBC API. It abstracts standard JDBC operations (obtaining connections, preparing statements, exception handling, resource closing), allowing developers to focus on SQL queries.

Usage:

Configuring Data Source: JdbcTemplate requires a configured DataSource (e.g., Commons DBCP, HikariCP), which provides database connections.

@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource dataSource() {
        // Example configuration for HikariCP
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setJdbcUrl("jdbc:postgresql://localhost:5432/mydatabase");
        dataSource.setUsername("myuser");
        dataSource.setPassword("mypassword");
        return dataSource;
    }

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

Injecting JdbcTemplate: Inject JdbcTemplate into your component (e.g., DAO).

@Repository
public class UserRepository {

    private final JdbcTemplate jdbcTemplate;

    // Constructor injection
    public UserRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // ... database operation methods
}

Executing queries: JdbcTemplate provides many methods for various SQL operations.

Data retrieval queries:

queryForObject: for getting a single object or value.
query: for getting a list of objects.
queryForRowSet: for getting SqlRowSet.

public int countUsers() {
    String sql = "SELECT COUNT(*) FROM users";
    // Query to get a single int value
    return jdbcTemplate.queryForObject(sql, Integer.class);
}

public User findById(Long id) {
    String sql = "SELECT id, username, email FROM users WHERE id = ?";
    // Query to get a single User object
    return jdbcTemplate.queryForObject(sql, new Object[]{id}, (rs, rowNum) -> {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        return user;
    });
}

public List<User> findAll() {
    String sql = "SELECT id, username, email FROM users";
    // Query to get a list of Users
    return jdbcTemplate.query(sql, (rs, rowNum) -> {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        return user;
    });
}

Data modification queries (INSERT, UPDATE, DELETE):

update: returns the number of affected rows.

public int createUser(User user) {
    String sql = "INSERT INTO users (username, email) VALUES (?, ?)";
    // Insert data, returns number of affected rows (1)
    return jdbcTemplate.update(sql, user.getUsername(), user.getEmail());
}

public int updateUser(User user) {
    String sql = "UPDATE users SET username = ?, email = ? WHERE id = ?";
    // Update data, returns number of affected rows
    return jdbcTemplate.update(sql, user.getUsername(), user.getEmail(), user.getId());
}

public int deleteUser(Long id) {
    String sql = "DELETE FROM users WHERE id = ?";
    // Delete data, returns number of affected rows
    return jdbcTemplate.update(sql, id);
}

Advantages:

Simplifies code: Eliminates JDBC boilerplate code.
Exception handling: Converts SQLException into Spring's DataAccessException hierarchy.
Resource management: Automatically closes connections, Statements, and ResultSets.
Spring integration: Easily integrates with Spring transactions.
Flexibility: Maintains the ability to write pure SQL.

Disadvantages:

Low-level: Requires manual writing of SQL queries.
Not suitable for complex mappings: For complex object-to-table mappings, JPA/Hibernate is preferable.

Tell me about JdbcTemplate and its use in Java applications.