JDBC数据访问

JdbcTemplate概述

Spring的JdbcTemplate是JDBC的核心类，简化了数据库操作。

配置数据源

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver

@Configuration
public class DataSourceConfig {
    
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

CRUD操作

查询操作

@Repository
public class UserRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // 查询单个值
    public int count() {
        return jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM users", 
            Integer.class
        );
    }
    
    // 查询单个对象
    public User findById(Long id) {
        return jdbcTemplate.queryForObject(
            "SELECT id, name, email FROM users WHERE id = ?",
            new Object[]{id},
            (rs, rowNum) -> {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                return user;
            }
        );
    }
    
    // 查询列表
    public List<User> findAll() {
        return jdbcTemplate.query(
            "SELECT id, name, email FROM users",
            (rs, rowNum) -> {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                return user;
            }
        );
    }
    
    // 使用RowMapper
    private static final RowMapper<User> USER_ROW_MAPPER = (rs, rowNum) -> {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setName(rs.getString("name"));
        user.setEmail(rs.getString("email"));
        return user;
    };
    
    public User findByIdWithMapper(Long id) {
        return jdbcTemplate.queryForObject(
            "SELECT id, name, email FROM users WHERE id = ?",
            USER_ROW_MAPPER,
            id
        );
    }
}

更新操作

@Repository
public class UserRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // 插入
    public int save(User user) {
        return jdbcTemplate.update(
            "INSERT INTO users(name, email) VALUES(?, ?)",
            user.getName(),
            user.getEmail()
        );
    }
    
    // 更新
    public int update(User user) {
        return jdbcTemplate.update(
            "UPDATE users SET name = ?, email = ? WHERE id = ?",
            user.getName(),
            user.getEmail(),
            user.getId()
        );
    }
    
    // 删除
    public int deleteById(Long id) {
        return jdbcTemplate.update("DELETE FROM users WHERE id = ?", id);
    }
    
    // 插入并获取自增ID
    public long saveAndGetId(User user) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO users(name, email) VALUES(?, ?)",
                Statement.RETURN_GENERATED_KEYS
            );
            ps.setString(1, user.getName());
            ps.setString(2, user.getEmail());
            return ps;
        }, keyHolder);
        
        return keyHolder.getKey().longValue();
    }
}

批量操作

@Repository
public class UserRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // 批量插入
    public int[] batchInsert(List<User> users) {
        return jdbcTemplate.batchUpdate(
            "INSERT INTO users(name, email) VALUES(?, ?)",
            users.stream()
                .map(user -> new Object[]{user.getName(), user.getEmail()})
                .collect(Collectors.toList())
        );
    }
    
    // 使用BatchPreparedStatementSetter
    public int[] batchInsertWithSetter(List<User> users) {
        return jdbcTemplate.batchUpdate(
            "INSERT INTO users(name, email) VALUES(?, ?)",
            new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setString(1, users.get(i).getName());
                    ps.setString(2, users.get(i).getEmail());
                }
                
                @Override
                public int getBatchSize() {
                    return users.size();
                }
            }
        );
    }
}

NamedParameterJdbcTemplate

@Repository
public class UserRepository {
    
    @Autowired
    private NamedParameterJdbcTemplate namedJdbcTemplate;
    
    // 使用命名参数
    public User findById(Long id) {
        Map<String, Object> params = Map.of("id", id);
        
        return namedJdbcTemplate.queryForObject(
            "SELECT id, name, email FROM users WHERE id = :id",
            params,
            USER_ROW_MAPPER
        );
    }
    
    // 使用SqlParameterSource
    public User findByEmail(String email) {
        SqlParameterSource params = new MapSqlParameterSource()
            .addValue("email", email);
        
        return namedJdbcTemplate.queryForObject(
            "SELECT id, name, email FROM users WHERE email = :email",
            params,
            USER_ROW_MAPPER
        );
    }
    
    // 使用BeanPropertySqlParameterSource
    public int update(User user) {
        SqlParameterSource params = new BeanPropertySqlParameterSource(user);
        
        return namedJdbcTemplate.update(
            "UPDATE users SET name = :name, email = :email WHERE id = :id",
            params
        );
    }
}

存储过程调用

@Repository
public class UserRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // 调用存储过程
    public int callProcedure(Long userId) {
        return jdbcTemplate.queryForObject(
            "CALL get_user_count(?)",
            Integer.class,
            userId
        );
    }
    
    // 使用SimpleJdbcCall
    public Map<String, Object> callProcedureWithResult(Long userId) {
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("get_user_info");
        
        Map<String, Object> in = Map.of("p_user_id", userId);
        return jdbcCall.execute(in);
    }
}

下一步

继续学习 事务管理，了解Spring的事务管理机制。