JDBC 支持

LCGYL Framework 提供了简洁的 JDBC 操作 API，简化数据库访问代码。

JdbcTemplate

基础查询

import com.lcgyl.data.JdbcTemplate;

public class UserRepository {
    
    private final JdbcTemplate jdbcTemplate;
    
    public UserRepository(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    // 查询单个对象
    public User findById(Long id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, User.class, id);
    }
    
    // 查询列表
    public List<User> findAll() {
        String sql = "SELECT * FROM users";
        return jdbcTemplate.queryForList(sql, User.class);
    }
    
    // 查询单个值
    public int count() {
        String sql = "SELECT COUNT(*) FROM users";
        return jdbcTemplate.queryForInt(sql);
    }
}

插入操作

public class UserRepository {
    
    // 插入并返回自增ID
    public Long save(User user) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        return jdbcTemplate.insert(sql, 
            user.getName(), 
            user.getEmail(), 
            user.getAge()
        );
    }
    
    // 批量插入
    public void batchSave(List<User> users) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        
        List<Object[]> batchArgs = users.stream()
            .map(user -> new Object[]{
                user.getName(), 
                user.getEmail(), 
                user.getAge()
            })
            .toList();
        
        jdbcTemplate.batchUpdate(sql, batchArgs);
    }
}

更新操作

public class UserRepository {
    
    // 更新
    public int update(User user) {
        String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
        return jdbcTemplate.update(sql, 
            user.getName(), 
            user.getEmail(), 
            user.getAge(), 
            user.getId()
        );
    }
    
    // 删除
    public int deleteById(Long id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
}

结果映射

自动映射

// 实体类
public class User {
    private Long id;
    private String name;
    private String email;
    private Integer age;
    private LocalDateTime createdAt;
    
    // Getters and Setters
}

// 自动映射（字段名匹配）
User user = jdbcTemplate.queryForObject(
    "SELECT * FROM users WHERE id = ?", 
    User.class, 
    1L
);

自定义映射

// RowMapper
public 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.setName(rs.getString("name"));
        user.setEmail(rs.getString("email"));
        user.setAge(rs.getInt("age"));
        user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return user;
    }
}

// 使用自定义映射
List<User> users = jdbcTemplate.query(
    "SELECT * FROM users", 
    new UserRowMapper()
);

批处理

public class BatchOperations {
    
    private final JdbcTemplate jdbcTemplate;
    
    // 批量插入
    public void batchInsert(List<User> users) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        
        jdbcTemplate.batchUpdate(sql, users, 100, (ps, user) -> {
            ps.setString(1, user.getName());
            ps.setString(2, user.getEmail());
        });
    }
    
    // 批量更新
    public void batchUpdate(List<User> users) {
        String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
        
        jdbcTemplate.batchUpdate(sql, users, 100, (ps, user) -> {
            ps.setString(1, user.getName());
            ps.setString(2, user.getEmail());
            ps.setLong(3, user.getId());
        });
    }
}

存储过程

public class StoredProcedureExample {
    
    private final JdbcTemplate jdbcTemplate;
    
    public void callProcedure() {
        jdbcTemplate.execute(connection -> {
            CallableStatement cs = connection.prepareCall("{call getUserCount(?)}");
            cs.registerOutParameter(1, Types.INTEGER);
            cs.execute();
            int count = cs.getInt(1);
            return count;
        });
    }
}

下一步

• 事务管理
• 数据源配置