一、Maven 驱动依赖
<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") ); }}