JDBC学习

JDBC 核心组件
DriverManager: 管理数据库驱动程序，处理数据库连接的创建。
Connection: 代表与数据库的连接会话。
Statement: 用于执行静态 SQL 语句并返回结果。
PreparedStatement: 预编译的 SQL 语句，可以执行动态 SQL 并提高性能。
CallableStatement: 用于调用数据库中的存储过程。
ResultSet: 包含查询结果的数据集。
2. 建立数据库连接
要建立数据库连接，首先需要加载数据库驱动程序，然后使用 DriverManager 获取连接对象：

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

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

    try {
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("Connected to the database!");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}
3. 执行 SQL 查询
使用 Statement 或 PreparedStatement 执行 SQL 查询：

使用 Statement：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

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

    try (Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("SELECT * FROM users")) {

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

}
使用 PreparedStatement：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
String sql = "SELECT * FROM users WHERE id = ?";

    try (Connection connection = DriverManager.getConnection(url, username, password);
         PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();

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

}
4. 执行更新操作
使用 Statement 或 PreparedStatement 执行插入、更新或删除操作：

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

public class JDBCDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

    try (Connection connection = DriverManager.getConnection(url, username, password);
         PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

        preparedStatement.setString(1, "John Doe");
        preparedStatement.setString(2, "[email protected]");
        int rowsInserted = preparedStatement.executeUpdate();

        System.out.println(rowsInserted + " rows inserted.");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}
5. 事务管理
JDBC 支持事务管理，通过 Connection 对象的 setAutoCommit(boolean autoCommit) 方法可以控制是否自动提交事务。使用 commit() 和 rollback() 方法可以手动提交或回滚事务。

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

public class JDBCDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

    try (Connection connection = DriverManager.getConnection(url, username, password)) {
        connection.setAutoCommit(false); // 关闭自动提交

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setString(2, "[email protected]");
            int rowsInserted = preparedStatement.executeUpdate();

            if (rowsInserted > 0) {
                connection.commit(); // 提交事务
                System.out.println("Transaction committed.");
            } else {
                connection.rollback(); // 回滚事务
                System.out.println("Transaction rolled back.");
            }
        } catch (SQLException e) {
            connection.rollback(); // 回滚事务
            System.out.println("Transaction rolled back due to error.");
            e.printStackTrace();
        } finally {
            connection.setAutoCommit(true); // 恢复自动提交
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}

6. 连接池
   直接使用 DriverManager 创建连接成本较高，通常使用连接池（如 HikariCP, Apache DBCP）来管理数据库连接。