JDBC 权威使用指南

JDBC 核心概念

JDBC (Java Database Connectivity) 是 Java 提供的标准 API，用于与关系型数据库交互。它通过驱动程序实现与不同数据库的通信，提供统一的接口。

基础使用步骤

1. 加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

2. 建立连接

String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);

3. 创建 Statement

Statement statement = connection.createStatement();

4. 执行查询

String sql = "SELECT * FROM users";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    System.out.println("ID: " + id + ", Name: " + name);
}
 

5. 执行更新操作

String sql = "UPDATE users SET name = ? WHERE id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "John Doe");
preparedStatement.setInt(2, 1);
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows affected: " + rowsAffected);
 

6. 执行事务操作

try {
    connection.setAutoCommit(false);
    
    String sql1 = "UPDATE account SET balance = balance - 100 WHERE id = 1";
    String sql2 = "UPDATE account SET balance = balance + 100 WHERE id = 2";
    
    Statement statement = connection.createStatement();
    statement.executeUpdate(sql1);
    statement.executeUpdate(sql2);
    
    connection.commit();
} catch (SQLException e) {
    connection.rollback();
    e.printStackTrace();
}
 

7. 关闭资源

if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
 

高级用法

预编译 Statement

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "John Doe");
preparedStatement.setString(2, "john@example.com");
preparedStatement.executeUpdate();

批处理操作

connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement("INSERT INTO users (name) VALUES (?)");

ps.setString(1, "User1");
ps.addBatch();

ps.setString(1, "User2");
ps.addBatch();

int[] results = ps.executeBatch();
connection.commit();

事务管理

try {
    connection.setAutoCommit(false);
    
    // 执行多个SQL操作
    
    connection.commit();
} catch (SQLException e) {
    connection.rollback();
} finally {
    connection.setAutoCommit(true);
}

Spring Boot 最佳实践

1. 配置数据源 在 application.properties 中:

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

2. 使用 JdbcTemplate

@Repository
public class UserRepository {
    private final JdbcTemplate jdbcTemplate;

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

    public List<User> findAll() {
        return jdbcTemplate.query(
            "SELECT id, name, email FROM users",
            (rs, rowNum) -> new User(
                rs.getLong("id"),
                rs.getString("name"),
                rs.getString("email")
            )
        );
    }

    public void save(User user) {
        jdbcTemplate.update(
            "INSERT INTO users (name, email) VALUES (?, ?)",
            user.getName(),
            user.getEmail()
        );
    }
}

3. 事务管理

@Service
@Transactional
public class UserService {
    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public void createUser(User user) {
        userRepository.save(user);
    }
}

4. 连接池配置 在 application.properties 中:

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=2000000

5. 最佳实践建议

• 始终使用 try-with-resources 或确保资源被正确关闭
• 使用 PreparedStatement 防止 SQL 注入
• 在 Service 层管理事务
• 合理配置连接池参数
• 使用 RowMapper 或 BeanPropertyRowMapper 简化结果映射
• 考虑使用 Spring Data JDBC 或 JPA 进行更高级的抽象

最后，给出完整 Spring Boot 示例如下：

@SpringBootApplication
public class JdbcDemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(JdbcDemoApplication.class, args);
    }
}

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
class User {
    @Id
    private Long id;
    private String name;
    private String email;
}

@Repository
public class UserRepository {
    private final JdbcTemplate jdbcTemplate;
    private final RowMapper<User> userRowMapper = BeanPropertyRowMapper.newInstance(User.class);

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

    public List<User> findAll() {
        return jdbcTemplate.query("SELECT * FROM users", userRowMapper);
    }

    public Optional<User> findById(Long id) {
        List<User> users = jdbcTemplate.query(
            "SELECT * FROM users WHERE id = ?",
            userRowMapper,
            id
        );
        return users.isEmpty() ? Optional.empty() : Optional.of(users.get(0));
    }

    public User save(User user) {
        if (user.getId() == null) {
            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);
            user.setId(keyHolder.getKey().longValue());
        } else {
            jdbcTemplate.update(
                "UPDATE users SET name = ?, email = ? WHERE id = ?",
                user.getName(),
                user.getEmail(),
                user.getId()
            );
        }
        return user;
    }
}

@Service
@Transactional
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;

    public List<User> getAllUsers() {
        return userRepository.findAll();
    }

    public User createUser(User user) {
        return userRepository.save(user);
    }
}

@RestController
@RequestMapping("/api/users")
@RequiredArgsConstructor
public class UserController {
    private final UserService userService;

    @GetMapping
    public List<User> getAllUsers() {
        return userService.getAllUsers();
    }

    @PostMapping
    public User createUser(@RequestBody User user) {
        return userService.createUser(user);
    }
}

使用 Spring Data JPA

Spring Data JPA 是 Spring Boot 中推荐的数据库访问方式，它基于 JPA（Java Persistence API）和 Hibernate，简化了数据库操作。

添加依赖

在 pom.xml 中添加以下依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
 

配置数据源

在 application.properties 中配置数据库连接：

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update
 

定义实体类

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "name")
    private String name;
    
    // Getters and Setters
}
 

定义 Repository 接口

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByName(String name);
}
 

使用 Repository 操作数据库

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    
    public List<User> getUsersByName(String name) {
        return userRepository.findByName(name);
    }
    
    public User saveUser(User user) {
        return userRepository.save(user);
    }
}
 

事务管理

Spring Boot 默认支持声明式事务管理，只需在方法上添加 @Transactional 注解：

@Transactional
public void transferMoney(Long fromId, Long toId, double amount) {
    User fromUser = userRepository.findById(fromId).orElseThrow();
    User toUser = userRepository.findById(toId).orElseThrow();
    
    fromUser.setBalance(fromUser.getBalance() - amount);
    toUser.setBalance(toUser.getBalance() + amount);
    
    userRepository.save(fromUser);
    userRepository.save(toUser);
}