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


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

/**
 * Simple JDBC MySQL connection demo
 */
public class SimpleJDBCDemo {
    
    // Database connection configuration - Please modify according to the actual situation
    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("    Simple JDBC MySQL connection demo");
        System.out.println("========================================");
        
        SimpleJDBCDemo demo = new SimpleJDBCDemo();
        
        try {
            // 1. Test database connection
            demo.testConnection();
            
            // 2. Create table
            demo.createTable();
            
            // 3. Demonstrate CRUD operations
            demo.runCRUDDemo();
            
        } catch (Exception e) {
            System.err.println("Program execution error: " + e.getMessage());
            e.printStackTrace();
        }
        
        System.out.println("========================================");
        System.out.println("Demo completed!");
        System.out.println("========================================");
    }
    
    /**
     * Test database connection
     */
    public void testConnection() {
        System.out.println("\\n Testing database connection...");
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            System.out.println("Database connection successful!");
            System.out.println("Database information: " + conn.getMetaData().getDatabaseProductName() + 
                             " " + conn.getMetaData().getDatabaseProductVersion());
        } catch (SQLException e) {
            System.err.println("Database connection failed: " + e.getMessage());
            throw new RuntimeException(e);
        }
    }
    
    /**
     * Create user table
     */
    public void createTable() {
        System.out.println("\\n Creating user table...");
        
        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("User table created successfully!");
            
        } catch (SQLException e) {
            System.err.println("Failed to create table: " + e.getMessage());
            throw new RuntimeException(e);
        }
    }
    
    /**
     * Run CRUD operations demo
     */
    public void runCRUDDemo() {
        System.out.println("\\n Starting CRUD operations demo...");
        
        // CREATE - Insert data
        System.out.println("\\n CREATE - Insert user data");
        int userId1 = insertUser("Zhang San", "zhangsan@example.com", 25);
        int userId2 = insertUser("Li Si", "lisi@example.com", 30);
        int userId3 = insertUser("Wang Wu", "wangwu@example.com", 28);
        
        // READ - Query data
        System.out.println("\\n READ - Query user data");
        queryUserById(userId1);
        queryAllUsers();
        
        // UPDATE - Update data
        System.out.println("\\n UPDATE - Update user data");
        updateUser(userId2, "Li Si (updated)", "lisi_new@example.com", 31);
        queryUserById(userId2);
        
        // DELETE - Delete data
        System.out.println("\\n DELETE - Delete user data");
        deleteUser(userId3);
        queryAllUsers();
        
        // Count data
        System.out.println("\\n Statistics");
        countUsers();
    }
    
    /**
     * Insert user - 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)) {
            
            // Set parameters - Prevent SQL injection
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.setInt(3, age);
            
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                // Obtain the generated ID
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        int userId = generatedKeys.getInt(1);
                        System.out.println("✅ User inserted successfully - ID: " + userId + ", Name: " + name);
                        return userId;
                    }
                }
            }
            
            throw new SQLException("Failed to insert user, failed to obtain the generated ID");
            
        } catch (SQLException e) {
            System.err.println(" Failed to insert user: " + e.getMessage());
            throw new RuntimeException(e);
        }
    }
    
    /**
     * Query user by 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("Querying user ID " + userId + ":");
                    printUser(rs);
                } else {
                    System.out.println("User with ID " + userId + " not found");
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Failed to query user: " + e.getMessage());
        }
    }
    
    /**
     * Query all users - 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("All users list:");
            System.out.println("------------------------------------------------------------");
            
            boolean hasUsers = false;
            while (rs.next()) {
                hasUsers = true;
                printUser(rs);
                System.out.println("------------------------------------------------------------");
            }
            
            if (!hasUsers) {
                System.out.println("No user data available");
            }
            
        } catch (SQLException e) {
            System.err.println("Failed to query all users: " + e.getMessage());
        }
    }
    
    /**
     * Update user - 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("User updated successfully - ID: " + userId + ", New name: " + name);
            } else {
                System.out.println("Update failed, User with ID " + userId + " not found");
            }
            
        } catch (SQLException e) {
            System.err.println("Failed to update user: " + e.getMessage());
        }
    }
    
    /**
     * Delete user - 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("User deleted successfully - ID: " + userId);
            } else {
                System.out.println("Delete failed, User with ID " + userId + " not found");
            }
            
        } catch (SQLException e) {
            System.err.println("Failed to delete user: " + e.getMessage());
        }
    }
    
    /**
     * Count users
     */
    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 users: " + total);
            }
            
        } catch (SQLException e) {
            System.err.println("Failed to count users: " + e.getMessage());
        }
    }
    
    /**
     * Print user information
     */
    private void printUser(ResultSet rs) throws SQLException {
        System.out.printf("ID: %d | Name: %s | Email: %s | Age: %d | Created At: %s%n",
            rs.getInt("id"),
            rs.getString("name"),
            rs.getString("email"),
            rs.getInt("age"),
            rs.getTimestamp("created_at")
        );
    }
}