import java.sql.*;

public class JDBCBasic {
    // MySQL 连接串
    private static final String URL = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) throws SQLException {
        // 1. 加载驱动（JDK 6+ 自动加载，可省略）
        // Class.forName("com.mysql.cj.jdbc.Driver");

        // 2. 获取连接
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            // 3. 执行查询
            try (ResultSet rs = stmt.executeQuery("SELECT id, name, age FROM users")) {
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    System.out.printf("ID: %d, 姓名: %s, 年龄: %d%n", id, name, age);
                }
            }
        }
    }
}

import java.sql.*;

public class PreparedStatementDemo {
    // ❌ 危险：SQL 注入
    // String sql = "SELECT * FROM users WHERE name = '" + userInput + "'";

    // ✅ 安全：使用 PreparedStatement
    static User findUserByName(Connection conn, String name) throws SQLException {
        String sql = "SELECT id, name, age, email FROM users WHERE name = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);  // 参数索引从 1 开始
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("email")
                    );
                }
                return null;
            }
        }
    }

    // 批量插入
    static void batchInsert(Connection conn, List<User> users) throws SQLException {
        String sql = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (User user : users) {
                ps.setString(1, user.name());
                ps.setInt(2, user.age());
                ps.setString(3, user.email());
                ps.addBatch();
            }
            int[] results = ps.executeBatch();
            System.out.println("插入 " + results.length + " 条记录");
        }
    }

    // 获取自增 ID
    static int insertUser(Connection conn, String name, int age) throws SQLException {
        String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);
            ps.setInt(2, age);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) return keys.getInt(1);
            }
        }
        return -1;
    }

    record User(int id, String name, int age, String email) {}
}

public class TransactionDemo {
    // 转账：A 向 B 转账
    static void transfer(Connection conn, int fromId, int toId, double amount)
            throws SQLException {
        conn.setAutoCommit(false);  // 开启事务
        try (PreparedStatement debit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
             PreparedStatement credit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

            // 扣款
            debit.setDouble(1, amount);
            debit.setInt(2, fromId);
            debit.setDouble(3, amount);
            int rows = debit.executeUpdate();
            if (rows == 0) {
                throw new SQLException("余额不足");
            }

            // 入账
            credit.setDouble(1, amount);
            credit.setInt(2, toId);
            credit.executeUpdate();

            conn.commit();  // 提交事务
            System.out.println("转账成功");
        } catch (SQLException e) {
            conn.rollback();  // 回滚事务
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }

    // Savepoint 部分回滚
    static void partialRollbackDemo(Connection conn) throws SQLException {
        conn.setAutoCommit(false);
        Savepoint sp = null;
        try (PreparedStatement ps = conn.prepareStatement("INSERT INTO logs(msg) VALUES(?)")) {
            ps.setString(1, "操作开始");
            ps.executeUpdate();

            sp = conn.setSavepoint("checkpoint");

            ps.setString(1, "操作中间");
            ps.executeUpdate();

            // 某些条件导致需要回滚到 savepoint
            conn.rollback(sp);
            conn.commit();  // 保留 checkpoint 之前的记录
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.*;

public class HikariCPDemo {
    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(10);        // 最大连接数
        config.setMinimumIdle(5);             // 最小空闲连接
        config.setConnectionTimeout(30000);   // 获取连接超时 30s
        config.setIdleTimeout(600000);        // 空闲超时 10min
        config.setMaxLifetime(1800000);       // 连接最大生命周期 30min
        dataSource = new HikariDataSource(config);
    }

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

    // 使用模板
    public static <T> T execute(ConnectionCallback<T> action) throws SQLException {
        try (Connection conn = getConnection()) {
            return action.doInConnection(conn);
        }
    }

    @FunctionalInterface
    interface ConnectionCallback<T> {
        T doInConnection(Connection conn) throws SQLException;
    }

    public static void main(String[] args) throws SQLException {
        List<String> names = execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM users");
                 ResultSet rs = ps.executeQuery()) {
                List<String> result = new ArrayList<>();
                while (rs.next()) result.add(rs.getString("name"));
                return result;
            }
        });
        System.out.println(names);
    }
}

public class SimpleORM {
    public static <T> List<T> query(Connection conn, String sql,
                                      RowMapper<T> mapper, Object... params)
            throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            try (ResultSet rs = ps.executeQuery()) {
                List<T> results = new ArrayList<>();
                while (rs.next()) {
                    results.add(mapper.mapRow(rs));
                }
                return results;
            }
        }
    }

    @FunctionalInterface
    interface RowMapper<T> {
        T mapRow(ResultSet rs) throws SQLException;
    }

    // 使用
    public static void main(String[] args) throws Exception {
        try (Connection conn = HikariCPDemo.getConnection()) {
            var users = query(conn,
                "SELECT id, name, age FROM users WHERE age > ?",
                rs -> new User(rs.getInt("id"), rs.getString("name"), rs.getInt("age")),
                18);
            users.forEach(System.out::println);
        }
    }
}