<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>


import java.sql.*;
import java.util.Scanner;

/**
 * 简单的JDBC MySQL连接演示
 */
public class SimpleJDBCDemo {
    
    // 数据库连接配置 - 请根据实际情况修改
    private static final String DB_URL = "jdbc:mysql://tdsql_host:3306/test";
    private static final String DB_USER = "tdsql_user";
    private static final String DB_PASSWORD = "tdsql_password";
    
    public static void main(String[] args) {
        System.out.println("========================================");
        System.out.println("    简单JDBC MySQL连接演示");
        System.out.println("========================================");
        
        SimpleJDBCDemo demo = new SimpleJDBCDemo();
        
        try {
            // 1. 测试数据库连接
            demo.testConnection();
            
            // 2. 创建表
            demo.createTable();
            
            // 3. 演示CRUD操作
            demo.runCRUDDemo();
            
        } catch (Exception e) {
            System.err.println("程序执行出错: " + e.getMessage());
            e.printStackTrace();
        }
        
        System.out.println("========================================");
        System.out.println("演示完成！");
        System.out.println("========================================");
    }
    
    /**
     * 测试数据库连接
     */
    public void testConnection() {
        System.out.println("\\n 测试数据库连接...");
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            System.out.println("数据库连接成功！");
            System.out.println("数据库信息: " + conn.getMetaData().getDatabaseProductName() + 
                             " " + conn.getMetaData().getDatabaseProductVersion());
        } catch (SQLException e) {
            System.err.println("数据库连接失败: " + e.getMessage());
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 创建用户表
     */
    public void createTable() {
        System.out.println("\\n 创建用户表...");
        
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY," +
                    "name VARCHAR(50) NOT NULL," +
                    "email VARCHAR(100) NOT NULL," +
                    "age INT," +
                    "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                    ")";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement()) {
            
            stmt.executeUpdate(sql);
            System.out.println("用户表创建成功！");
            
        } catch (SQLException e) {
            System.err.println("创建表失败: " + e.getMessage());
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 运行CRUD操作演示
     */
    public void runCRUDDemo() {
        System.out.println("\\n 开始CRUD操作演示...");
        
        // CREATE - 插入数据
        System.out.println("\\n CREATE - 插入用户数据");
        int userId1 = insertUser("张三", "zhangsan@example.com", 25);
        int userId2 = insertUser("李四", "lisi@example.com", 30);
        int userId3 = insertUser("王五", "wangwu@example.com", 28);
        
        // READ - 查询数据
        System.out.println("\\n READ - 查询用户数据");
        queryUserById(userId1);
        queryAllUsers();
        
        // UPDATE - 更新数据
        System.out.println("\\n UPDATE - 更新用户数据");
        updateUser(userId2, "李四（已更新）", "lisi_new@example.com", 31);
        queryUserById(userId2);
        
        // DELETE - 删除数据
        System.out.println("\\n DELETE - 删除用户数据");
        deleteUser(userId3);
        queryAllUsers();
        
        // 统计数据
        System.out.println("\\n 统计信息");
        countUsers();
    }
    
    /**
     * 插入用户 - CREATE
     */
    public int insertUser(String name, String email, int age) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            // 设置参数 - 防止SQL注入
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.setInt(3, age);
            
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                // 获取生成的ID
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        int userId = generatedKeys.getInt(1);
                        System.out.println("✅ 用户插入成功 - ID: " + userId + ", 姓名: " + name);
                        return userId;
                    }
                }
            }
            
            throw new SQLException("插入用户失败，未获取到生成的ID");
            
        } catch (SQLException e) {
            System.err.println(" 插入用户失败: " + e.getMessage());
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 根据ID查询用户 - READ
     */
    public void queryUserById(int userId) {
        String sql = "SELECT * FROM users WHERE id = ?";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, userId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.println("查询用户 ID " + userId + ":");
                    printUser(rs);
                } else {
                    System.out.println("未找到ID为 " + userId + " 的用户");
                }
            }
            
        } catch (SQLException e) {
            System.err.println("查询用户失败: " + e.getMessage());
        }
    }
    
    /**
     * 查询所有用户 - READ
     */
    public void queryAllUsers() {
        String sql = "SELECT * FROM users ORDER BY id";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("所有用户列表:");
            System.out.println("------------------------------------------------------------");
            
            boolean hasUsers = false;
            while (rs.next()) {
                hasUsers = true;
                printUser(rs);
                System.out.println("------------------------------------------------------------");
            }
            
            if (!hasUsers) {
                System.out.println("暂无用户数据");
            }
            
        } catch (SQLException e) {
            System.err.println("查询所有用户失败: " + e.getMessage());
        }
    }
    
    /**
     * 更新用户 - UPDATE
     */
    public void updateUser(int userId, String name, String email, int age) {
        String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.setInt(3, age);
            pstmt.setInt(4, userId);
            
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                System.out.println("用户更新成功 - ID: " + userId + ", 新姓名: " + name);
            } else {
                System.out.println("更新失败，未找到ID为 " + userId + " 的用户");
            }
            
        } catch (SQLException e) {
            System.err.println("更新用户失败: " + e.getMessage());
        }
    }
    
    /**
     * 删除用户 - DELETE
     */
    public void deleteUser(int userId) {
        String sql = "DELETE FROM users WHERE id = ?";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, userId);
            
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                System.out.println("用户删除成功 - ID: " + userId);
            } else {
                System.out.println("删除失败，未找到ID为 " + userId + " 的用户");
            }
            
        } catch (SQLException e) {
            System.err.println("删除用户失败: " + e.getMessage());
        }
    }
    
    /**
     * 统计用户数量
     */
    public void countUsers() {
        String sql = "SELECT COUNT(*) as total FROM users";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            if (rs.next()) {
                int total = rs.getInt("total");
                System.out.println("用户总数: " + total);
            }
            
        } catch (SQLException e) {
            System.err.println("统计用户失败: " + e.getMessage());
        }
    }
    
    /**
     * 打印用户信息
     */
    private void printUser(ResultSet rs) throws SQLException {
        System.out.printf("ID: %d | 姓名: %s | 邮箱: %s | 年龄: %d | 创建时间: %s%n",
            rs.getInt("id"),
            rs.getString("name"),
            rs.getString("email"),
            rs.getInt("age"),
            rs.getTimestamp("created_at")
        );
    }
}