OneYoung

Appearance

Java 提供了 JDBC（Java Database Connectivity）API，用于连接和操作各种数据库。通过 JDBC，开发者可以执行 SQL 语句、处理结果集以及管理数据库事务。

一、JDBC 概述 ​

JDBC 是 Java 提供的一种标准 API，用于与数据库进行交互。它支持多种数据库，包括 MySQL、PostgreSQL、Oracle 等。

二、连接数据库 ​

1. 添加 JDBC 驱动 ​

在使用 JDBC 连接数据库之前，需要确保添加相应的 JDBC 驱动到项目中。例如，对于 MySQL 数据库，可以在 Maven 项目中添加以下依赖：

xml
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version> <!-- 请根据需要选择版本 -->
</dependency>

2. 连接数据库示例 ​

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase"; // 数据库 URL
        String user = "root"; // 数据库用户名
        String password = "password"; // 数据库密码

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            System.out.println("成功连接到数据库！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

三、常用数据库操作 ​

1. 创建表 ​

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTable {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        String createTableSQL = "CREATE TABLE users ("
                + "id INT PRIMARY KEY AUTO_INCREMENT, "
                + "name VARCHAR(100), "
                + "email VARCHAR(100))";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {
            statement.execute(createTableSQL);
            System.out.println("表创建成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. 插入数据 ​

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertData {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
            preparedStatement.setString(1, "张三");
            preparedStatement.setString(2, "zhangsan@example.com");
            preparedStatement.executeUpdate();
            System.out.println("数据插入成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. 查询数据 ​

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class QueryData {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        String querySQL = "SELECT * FROM users";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(querySQL)) {
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. 更新数据 ​

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateData {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        String updateSQL = "UPDATE users SET email = ? WHERE name = ?";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
            preparedStatement.setString(1, "newemail@example.com");
            preparedStatement.setString(2, "张三");
            preparedStatement.executeUpdate();
            System.out.println("数据更新成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. 删除数据 ​

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeleteData {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        String deleteSQL = "DELETE FROM users WHERE name = ?";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
            preparedStatement.setString(1, "张三");
            preparedStatement.executeUpdate();
            System.out.println("数据删除成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

四、数据库连接池 ​

在实际应用中，频繁地连接和断开数据库会导致性能问题。为了提高效率，通常会使用连接池来管理数据库连接。连接池可以在应用程序启动时创建一定数量的数据库连接，并在需要时复用这些连接，而不是每次操作都重新建立连接。

1. 添加 HikariCP 依赖 ​

如果你使用 Maven，可以在 pom.xml 中添加 HikariCP 的依赖：

xml
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version> <!-- 请根据需要选择版本 -->
</dependency>

2. 配置连接池 ​

java
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.SQLException;

public class DatabaseConnectionPool {
    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(10); // 设置最大连接数
        dataSource = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

3. 使用连接池进行数据库操作 ​

在进行数据库操作时，可以通过 DatabaseConnectionPool.getConnection() 方法获取连接，而不是每次都创建新的连接。

java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DatabaseOperations {
    public static void insertData(String name, String email) {
        String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (Connection connection = DatabaseConnectionPool.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, email);
            preparedStatement.executeUpdate();
            System.out.println("数据插入成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void queryData() {
        String querySQL = "SELECT * FROM users";
        try (Connection connection = DatabaseConnectionPool.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(querySQL);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}