JdbcTemplate使用

JdbcTemplate简化JDBC操作，自动处理资源管理和异常转换。

配置与注入

@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource dataSource() {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        ds.setUsername("root");
        ds.setPassword("password");
        return ds;
    }

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

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
}

查询操作

查询单值

// 查询计数
public int countUsers() {
    return jdbcTemplate.queryForObject(
        "SELECT COUNT(*) FROM users", Integer.class);
}

// 查询单个字段
public String findUsername(Long id) {
    return jdbcTemplate.queryForObject(
        "SELECT username FROM users WHERE id = ?", String.class, id);
}

查询单条记录

// 使用RowMapper
public User findById(Long id) {
    return jdbcTemplate.queryForObject(
        "SELECT id, username, email FROM users WHERE id = ?",
        new UserRowMapper(), id);
}

// RowMapper实现
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;
    }
}

// 使用BeanPropertyRowMapper（自动映射）
public User findByIdSimple(Long id) {
    return jdbcTemplate.queryForObject(
        "SELECT id, username, email FROM users WHERE id = ?",
        new BeanPropertyRowMapper<>(User.class), id);
}

查询列表

// 查询所有
public List<User> findAll() {
    return jdbcTemplate.query(
        "SELECT id, username, email FROM users",
        new UserRowMapper());
}

// 条件查询
public List<User> findByAge(int minAge) {
    return jdbcTemplate.query(
        "SELECT id, username, email FROM users WHERE age > ?",
        new UserRowMapper(), minAge);
}

// Lambda方式
public List<User> findAllLambda() {
    return jdbcTemplate.query(
        "SELECT id, username, email FROM users",
        (rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setUsername(rs.getString("username"));
            user.setEmail(rs.getString("email"));
            return user;
        });
}

更新操作

INSERT

public int insert(User user) {
    return jdbcTemplate.update(
        "INSERT INTO users(username, email, age) VALUES(?, ?, ?)",
        user.getUsername(), user.getEmail(), user.getAge());
}

// 获取自增主键
public Long insertAndGetId(User user) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(connection -> {
        PreparedStatement ps = connection.prepareStatement(
            "INSERT INTO users(username, email) VALUES(?, ?)",
            Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getEmail());
        return ps;
    }, keyHolder);
    return keyHolder.getKey().longValue();
}

UPDATE

public int update(User user) {
    return jdbcTemplate.update(
        "UPDATE users SET username = ?, email = ? WHERE id = ?",
        user.getUsername(), user.getEmail(), user.getId());
}

// 条件更新
public int updateStatus(Long id, String status) {
    return jdbcTemplate.update(
        "UPDATE users SET status = ? WHERE id = ?", status, id);
}

DELETE

public int delete(Long id) {
    return jdbcTemplate.update("DELETE FROM users WHERE id = ?", id);
}

// 批量删除
public int deleteByIds(List<Long> ids) {
    String sql = "DELETE FROM users WHERE id IN (?)";
    StringBuilder sb = new StringBuilder();
    for (Long id : ids) {
        sb.append(id).append(",");
    }
    return jdbcTemplate.update(sql, sb.substring(0, sb.length() - 1));
}

批量操作

// 批量插入
public int[] batchInsert(List<User> users) {
    return jdbcTemplate.batchUpdate(
        "INSERT INTO users(username, email) VALUES(?, ?)",
        users, 100,  // batchSize
        (ps, user) -> {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getEmail());
        });
}

// 批量更新
public int[] batchUpdate(List<User> users) {
    return jdbcTemplate.batchUpdate(
        "UPDATE users SET email = ? WHERE id = ?",
        users, 100,
        (ps, user) -> {
            ps.setString(1, user.getEmail());
            ps.setLong(2, user.getId());
        });
}

事务支持

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        jdbcTemplate.update(
            "UPDATE account SET balance = balance - ? WHERE id = ?",
            amount, fromId);

        jdbcTemplate.update(
            "UPDATE account SET balance = balance + ? WHERE id = ?",
            amount, toId);
    }
}

NamedParameterJdbcTemplate

@Repository
public class NamedUserDao {

    @Autowired
    private NamedParameterJdbcTemplate namedTemplate;

    // 使用命名参数
    public User findById(Long id) {
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);

        return namedTemplate.queryForObject(
            "SELECT id, username, email FROM users WHERE id = :id",
            params, new UserRowMapper());
    }

    // 使用SqlParameterSource
    public List<User> findByCondition(String username, int minAge) {
        SqlParameterSource params = new MapSqlParameterSource()
            .addValue("username", username)
            .addValue("minAge", minAge);

        return namedTemplate.query(
            "SELECT * FROM users WHERE username LIKE :username AND age > :minAge",
            params, new UserRowMapper());
    }

    // 使用BeanPropertySqlParameterSource
    public int update(User user) {
        SqlParameterSource params = new BeanPropertySqlParameterSource(user);
        return namedTemplate.update(
            "UPDATE users SET username = :username, email = :email WHERE id = :id",
            params);
    }
}

异常处理

// Spring自动将SQLException转换为DataAccessException
public User findOrThrow(Long id) {
    try {
        return jdbcTemplate.queryForObject(
            "SELECT * FROM users WHERE id = ?",
            new UserRowMapper(), id);
    } catch (EmptyResultDataAccessException e) {
        // 结果为空
        throw new UserNotFoundException("用户不存在: " + id);
    }
}

要点总结

• JdbcTemplate自动管理连接和异常转换
• queryForObject查询单值或单条记录
• query查询列表，使用RowMapper映射
• update执行INSERT/UPDATE/DELETE
• batchUpdate支持批量操作
• NamedParameterJdbcTemplate使用命名参数
• Spring自动转换SQLException为DataAccessException