H数据库操作.java

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

public class DatabaseConnector {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/my_db"; // 数据库地址，就像去朋友家的路
        String username = "root"; // 数据库的门牌号
        String password = "123456"; // 密码，就跟咱家wifi密码似的

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
            System.out.println("哇塞！数据库连上了，这感觉就像是打通了任督二脉！");
            // 后面就能撒欢儿地玩数据库啦
        } catch (SQLException e) {
            System.err.println("哎呀妈呀！连数据库都失败了，这路咋走不通呢？" + e.getMessage());
        }
    }
}

二、查询数据，把数据库当字典查

连上数据库了，咱就得干点正经事儿，先从查数据开始。

import java.sql.ResultSet;
import java.sql.Statement;

public class DataQuerier {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/my_db";
        String username = "root";
        String password = "123456";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT id, name, age FROM people")) {

            System.out.println("查询结果来啦，瞧一瞧，看一看！");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.printf(" ID：%d ，名字：%s ，年龄：%d ，这人看起来挺有意思！%n", id, name, age);
            }
        } catch (SQLException e) {
            System.err.println("查询失败，莫非是 SQL 写错了？" + e.getMessage());
        }
    }
}

三、更新数据，给数据库动个小手术

光会查可不行，还得会更新数据，这就像是给数据库动个小手术，把旧数据换个新模样。

public class DataUpdater {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/my_db";
        String username = "root";
        String password = "123456";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             java.sql.Statement statement = connection.createStatement()) {

            int rowsAffected = statement.executeUpdate("UPDATE people SET age = 30 WHERE name = '张三'");
            System.out.println("更新成功！一共改了" + rowsAffected + "行，这人张三现在年龄变年轻啦！");
        } catch (SQLException e) {
            System.err.println("更新失败，难道是定位不到张三？" + e.getMessage());
        }
    }
}

四、PreparedStatement，给 SQL 戴个口罩防病毒

直接用 Statement 像是光着膀子在干活，容易被 SQL 注入这种“病毒”盯上。PreparedStatement 就像是给 SQL 戴了个口罩，安全多了。

public class PreparedStatementDemo {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/my_db";
        String username = "root";
        String password = "123456";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {

            // 插入数据，像往数据库的篮子里放个新苹果
            String insertSQL = "INSERT INTO people (name, age) VALUES (?, ?)";
            try (java.sql.PreparedStatement pstmtInsert = connection.prepareStatement(insertSQL)) {
                pstmtInsert.setString(1, "李四");
                pstmtInsert.setInt(2, 25);
                int rowsAffected = pstmtInsert.executeUpdate();
                System.out.println("插入成功！新来的李四已经入住数据库小区啦，影响了" + rowsAffected + "行。");
            }

            // 查询数据，就像在人群里找一个戴眼镜的
            String querySQL = "SELECT id, name, age FROM people WHERE name = ?";
            try (java.sql.PreparedStatement pstmtQuery = connection.prepareStatement(querySQL)) {
                pstmtQuery.setString(1, "李四");
                try (java.sql.ResultSet resultSet = pstmtQuery.executeQuery()) {
                    while (resultSet.next()) {
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        int age = resultSet.getInt("age");
                        System.out.printf("找到了！ID：%d ，名字：%s ，年龄：%d ，这人就是我们要找的李四！%n", id, name, age);
                    }
                }
            }
        } catch (SQLException e) {
            System.err.println("操作失败，是 SQL 写错还是别的啥问题？" + e.getMessage());
        }
    }
}

五、连接池，数据库连接的“共享单车”

每次都去创建、关闭数据库连接，就像每次都去买一辆新车，太费事儿。连接池就像是共享单车，用完放回去，别人还能接着用。

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class ConnectionPoolDemo {
    public static void main(String[] args) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/my_db");
        config.setUsername("root");
        config.setPassword("123456");
        config.setMaximumPoolSize(10); // 最多能借出 10 辆“单车”
        config.setMinimumIdle(2); // 至少得留 2 辆“单车”在池子里备用
        config.setIdleTimeout(30000); // 如果一辆“单车”闲了 30 秒，就回收了

        HikariDataSource dataSource = new HikariDataSource(config);

        try (Connection connection = dataSource.getConnection()) {
            System.out.println("从连接池成功借到连接，就像从共享单车池里抽出一辆车！");
            // 用这连接干点啥事儿...
        } catch (SQLException e) {
            System.err.println("从连接池借连接失败，这池子是不是干了？" + e.getMessage());
        }
    }
}

六、事务，要么一起成，要么一起败

有些事儿不能半途而废，比如转账，要么全转成功，要么啥都不干。这事儿就靠事务来撑腰。

public class TransactionDemo {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/my_db";
        String username = "root";
        String password = "123456";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            connection.setAutoCommit(false); // 先跟数据库说一声，咱要开始个重要操作啦

            try {
                // 假装这是从一个账户扣钱
                String sql1 = "UPDATE account SET balance = balance - 100 WHERE account_id = 1";
                java.sql.PreparedStatement pstmt1 = connection.prepareStatement(sql1);
                pstmt1.executeUpdate();

                // 假装这是给另一个账户加钱
                String sql2 = "UPDATE account SET balance = balance + 100 WHERE account_id = 2";
                java.sql.PreparedStatement pstmt2 = connection.prepareStatement(sql2);
                pstmt2.executeUpdate();

                connection.commit(); // 俩操作都成功啦，commit 提交，跟说“咱这事儿成啦”
                System.out.println("转账成功！钱都转好了，大家都开心！");
            } catch (SQLException e) {
                connection.rollback(); // 要是中途出岔子，rollback 回滚，就像啥事儿都没发生过
                System.err.println("转账失败，把钱都退回去，这事儿黄了！" + e.getMessage());
            }
        } catch (SQLException e) {
            System.err.println("数据库操作有毛病，快检查检查！" + e.getMessage());
        }
    }
}

七、数据库元数据，瞧瞧数据库的底细

数据库也有不少小秘密，元数据就像是它的户口本，能瞅瞅它都有啥家当。

import java.sql.DatabaseMetaData;

public class MetadataDemo {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/my_db";
        String username = "root";
        String password = "123456";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            DatabaseMetaData metaData = connection.getMetaData();

            // 瞅瞅数据库都有啥表，就像逛超市看都有啥货架
            try (java.sql.ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"})) {
                System.out.println("咱来看看数据库都有啥表！");
                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    System.out.println("瞅见一张表，名字叫：" + tableName);
                }
            }

            // 瞅瞅某张表都有啥列，就像看菜谱都写了啥材料
            try (java.sql.ResultSet columns = metaData.getColumns(null, null, "people", null)) {
                System.out.println("咱来看看 people 表都有啥列！");
                while (columns.next()) {
                    String columnName = columns.getString("COLUMN_NAME");
                    String columnType = columns.getString("TYPE_NAME");
                    int columnSize = columns.getInt("COLUMN_SIZE");
                    System.out.printf("瞅见一列，名字：%s，类型：%s，长度：%d%n", columnName, columnType, columnSize);
                }
            }
        } catch (SQLException e) {
            System.err.println("瞅数据库底细都瞅不成，这都啥情况？" + e.getMessage());
        }
    }
}

八、实战演练

搭建一个简易博客系统

需求分析

为了让大家更好地理解 Java 数据库操作，咱们来搭建一个简易的博客系统。这个系统需要支持以下功能：

用户注册与登录
博客文章的增删改查
用户评论功能

数据库设计

先设计好数据库，这就像盖房子先搭好骨架。

CREATE DATABASE blog_system;

USE blog_system;

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 博客文章表
CREATE TABLE blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 评论表
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES blog_posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

用户注册与登录模块

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

public class BlogUserManager {
    private static final String jdbcUrl = "jdbc:mysql://localhost:3306/blog_system";
    private static final String username = "root";
    private static final String password = "123456";

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        System.out.println("欢迎来到博客系统！");
        System.out.println("1. 注册");
        System.out.println("2. 登录");
        System.out.print("请选择：");

        int choice = scanner.nextInt();
        scanner.nextLine(); // 消耗换行符

        if (choice == 1) {
            registerUser(scanner);
        } else if (choice == 2) {
            login(scanner);
        } else {
            System.out.println("别乱选啊，瞎捣鼓！");
        }

        scanner.close();
    }

    // 用户注册
    private static void registerUser(Scanner scanner) {
        System.out.print("请输入用户名：");
        String inputUsername = scanner.nextLine();
        System.out.print("请输入密码：");
        String inputPassword = scanner.nextLine();
        System.out.print("请输入邮箱：");
        StringinputEmail = scanner.nextLine();

        String insertSQL = "INSERT INTO users (username, password, email) VALUES (?, ?, ?)";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             java.sql.PreparedStatement pstmt = connection.prepareStatement(insertSQL)) {

            pstmt.setString(1, inputUsername);
            pstmt.setString(2, inputPassword); // 实际项目中密码应该加密存储
            pstmt.setString(3, inputEmail);

            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("注册成功！快去登录瞅瞅吧！");
            } else {
                System.out.println("注册失败，可能是用户名或邮箱重复了！");
            }

        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) { // 判断是不是重复键值错误
                System.out.println("哎呀，这用户名或邮箱已经被别人用啦，换个试试！");
            } else {
                System.out.println("注册出问题啦：" + e.getMessage());
            }
        }
    }

    // 用户登录
    private static void login(Scanner scanner) {
        System.out.print("请输入用户名：");
        String inputUsername = scanner.nextLine();
        System.out.print("请输入密码：");
        String inputPassword = scanner.nextLine();

        String querySQL = "SELECT id, username FROM users WHERE username = ? AND password = ?";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             java.sql.PreparedStatement pstmt = connection.prepareStatement(querySQL)) {

            pstmt.setString(1, inputUsername);
            pstmt.setString(2, inputPassword);

            try (java.sql.ResultSet resultSet = pstmt.executeQuery()) {

                if (resultSet.next()) {
                    int userId = resultSet.getInt("id");
                    String usernameResult = resultSet.getString("username");
                    System.out.println("登录成功！欢迎，" + usernameResult + "！");
                    // 登录成功后可以进入博客系统主界面
                    showBlogSystemMenu(scanner, userId);
                } else {
                    System.out.println("用户名或密码不对，登录失败！");
                }
            }

        } catch (SQLException e) {
            System.out.println("登录出问题啦：" + e.getMessage());
        }
    }

    // 博客系统主菜单
    private static void showBlogSystemMenu(Scanner scanner, int userId) {
        while (true) {
            System.out.println("\n博客系统主菜单：");
            System.out.println("1. 发表新文章");
            System.out.println("2. 查看所有文章");
            System.out.println("3. 评论文章");
            System.out.println("4. 退出登录");
            System.out.print("请选择：");

            int choice = scanner.nextInt();
            scanner.nextLine(); // 消耗换行符

            if (choice == 1) {
                publishNewPost(scanner, userId);
            } else if (choice == 2) {
                viewAllPosts(scanner);
            } else if (choice == 3) {
                commentOnPost(scanner, userId);
            } else if (choice == 4) {
                System.out.println("退出登录，欢迎下次再来！");
                break;
            } else {
                System.out.println("别乱选，没这选项！");
            }
        }
    }

    // 发表新文章
    private static void publishNewPost(Scanner scanner, int userId) {
        System.out.print("请输入文章标题：");
        String title = scanner.nextLine();
        System.out.print("请输入文章内容：");
        String content = scanner.nextLine();

        String insertSQL = "INSERT INTO blog_posts (user_id, title, content) VALUES (?, ?, ?)";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             java.sql.PreparedStatement pstmt = connection.prepareStatement(insertSQL)) {

            pstmt.setInt(1, userId);
            pstmt.setString(2, title);
            pstmt.setString(3, content);

            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("文章发表成功！你可真是个写作小能手！");
            } else {
                System.out.println("文章发表失败，这是咋回事儿？");
            }

        } catch (SQLException e) {
            System.out.println("发表文章出问题啦：" + e.getMessage());
        }
    }

    // 查看所有文章
    private static void viewAllPosts(Scanner scanner) {
        String querySQL = "SELECT bp.id, bp.title, bp.content, bp.created_at, u.username " +
                          "FROM blog_posts bp " +
                          "JOIN users u ON bp.user_id = u.id " +
                          "ORDER BY bp.created_at DESC";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             java.sql.PreparedStatement pstmt = connection.prepareStatement(querySQL);
             java.sql.ResultSet resultSet = pstmt.executeQuery()) {

            System.out.println("\n所有文章：");
            while (resultSet.next()) {
                int postId = resultSet.getInt("bp.id");
                String title = resultSet.getString("bp.title");
                String content = resultSet.getString("bp.content");
                String createdAt = resultSet.getTimestamp("bp.created_at").toString();
                String author = resultSet.getString("u.username");

                System.out.printf("文章ID：%d，标题：%s，作者：%s，发布时间：%s%n", 
                                 postId, title, author, createdAt);
                System.out.println("内容：" + content);
                System.out.println("-----------------------------------");
            }

        } catch (SQLException e) {
            System.out.println("查看文章出问题啦：" + e.getMessage());
        }
    }

    // 评论文章
    private static void commentOnPost(Scanner scanner, int userId) {
        System.out.print("请输入要评论的文章ID：");
        int postId = scanner.nextInt();
        scanner.nextLine(); // 消耗换行符
        System.out.print("请输入评论内容：");
        String commentContent = scanner.nextLine();

        String insertSQL = "INSERT INTO comments (post_id, user_id, content) VALUES (?, ?, ?)";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             java.sql.PreparedStatement pstmt = connection.prepareStatement(insertSQL)) {

            pstmt.setInt(1, postId);
            pstmt.setInt(2, userId);
            pstmt.setString(3, commentContent);

            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("评论成功！你的观点很重要哦！");
            } else {
                System.out.println("评论失败，可能是文章ID不存在！");
            }

        } catch (SQLException e) {
            System.out.println("评论出问题啦：" + e.getMessage());
        }
    }
}

给博客文章添加标签功能

为了提升博客系统的功能，咱们再给文章加个标签功能。这样用户就能按标签搜索文章啦。

修改数据库表结构

-- 标签表
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- 文章标签关联表
CREATE TABLE post_tags (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES blog_posts(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

添加标签功能代码

// 在 BlogUserManager 类中添加以下方法

// 给文章添加标签
private static void addTagsToPost(Scanner scanner, int userId) {
    System.out.print("请输入要添加标签的文章ID：");
    int postId = scanner.nextInt();
    scanner.nextLine(); // 消耗换行符

    System.out.print("请输入标签名称（多个标签用逗号分隔）：");
    String tagNames = scanner.nextLine();

    // 先插入标签，再关联文章和标签
    String insertTagSQL = "INSERT INTO tags (name) VALUES (?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)";
    String insertPostTagSQL = "INSERT INTO post_tags (post_id, tag_id) VALUES (?, ?)";

    try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
        connection.setAutoCommit(false); // 开始事务

        try (
            java.sql.PreparedStatement pstmtInsertTag = connection.prepareStatement(insertTagSQL);
            java.sql.PreparedStatement pstmtInsertPostTag = connection.prepareStatement(insertPostTagSQL)
        ) {
            String[] tags = tagNames.split(",");

            for (String tagName : tags) {
                tagName = tagName.trim();

                // 插入标签，如果标签已存在，则获取其ID
                pstmtInsertTag.setString(1, tagName);
                int tagInsertResult = pstmtInsertTag.executeUpdate();

                int tagId = 0;
                try (java.sql.ResultSet generatedKeys = pstmtInsertTag.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        tagId = generatedKeys.getInt(1);
                    } else {
                        // 如果标签已存在，通过查询获取ID
                        String queryTagIdSQL = "SELECT id FROM tags WHERE name = ?";
                        try (java.sql.PreparedStatement pstmtQueryTagId = connection.prepareStatement(queryTagIdSQL)) {
                            pstmtQueryTagId.setString(1, tagName);
                            try (java.sql.ResultSet resultSet = pstmtQueryTagId.executeQuery()) {
                                if (resultSet.next()) {
                                    tagId = resultSet.getInt("id");
                                }
                            }
                        }
                    }
                }

                if (tagId > 0) {
                    // 关联文章和标签
                    pstmtInsertPostTag.setInt(1, postId);
                    pstmtInsertPostTag.setInt(2, tagId);
                    pstmtInsertPostTag.executeUpdate();
                }
            }

            connection.commit(); // 提交事务
            System.out.println("标签添加成功！文章现在有新标签啦！");
        } catch (SQLException e) {
            connection.rollback(); // 回滚事务
            System.out.println("添加标签失败啦：" + e.getMessage());
        }
    } catch (SQLException e) {
        System.out.println("数据库操作出问题啦：" + e.getMessage());
    }
}

// 按标签搜索文章
private static void searchPostsByTag(Scanner scanner) {
    System.out.print("请输入要搜索的标签：");
    String tag = scanner.nextLine();

    String querySQL = "SELECT bp.id, bp.title, bp.content, bp.created_at, u.username " +
                      "FROM blog_posts bp " +
                      "JOIN post_tags pt ON bp.id = pt.post_id " +
                      "JOIN tags t ON pt.tag_id = t.id " +
                      "JOIN users u ON bp.user_id = u.id " +
                      "WHERE t.name = ? " +
                      "ORDER BY bp.created_at DESC";

    try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
         java.sql.PreparedStatement pstmt = connection.prepareStatement(querySQL);
         java.sql.ResultSet resultSet = pstmt.executeQuery()) {

        System.out.println("\n搜索结果：");
        boolean found = false;
        while (resultSet.next()) {
            found = true;
            int postId = resultSet.getInt("bp.id");
            String title = resultSet.getString("bp.title");
            String content = resultSet.getString("bp.content");
            String createdAt = resultSet.getTimestamp("bp.created_at").toString();
            String author = resultSet.getString("u.username");

            System.out.printf("文章ID：%d，标题：%s，作者：%s，发布时间：%s%n", 
                             postId, title, author, createdAt);
            System.out.println("内容：" + content);
            System.out.println("-----------------------------------");
        }

        if (!found) {
            System.out.println("没找到带这个标签的文章，换个标签试试？");
        }

    } catch (SQLException e) {
        System.out.println("搜索文章出问题啦：" + e.getMessage());
    }
}

// 修改主菜单，添加新选项
private static void showBlogSystemMenu(Scanner scanner, int userId) {
    while (true) {
        System.out.println("\n博客系统主菜单：");
        System.out.println("1. 发表新文章");
        System.out.println("2. 查看所有文章");
        System.out.println("3. 评论文章");
        System.out.println("4. 给文章添加标签");
        System.out.println("5. 按标签搜索文章");
        System.out.println("6. 退出登录");
        System.out.print("请选择：");

        int choice = scanner.nextInt();
        scanner.nextLine(); // 消耗换行符

        if (choice == 1) {
            publishNewPost(scanner, userId);
        } else if (choice == 2) {
            viewAllPosts(scanner);
        } else if (choice == 3) {
            commentOnPost(scanner, userId);
        } else if (choice == 4) {
            addTagsToPost(scanner, userId);
        } else if (choice == 5) {
            searchPostsByTag(scanner);
        } else if (choice == 6) {
            System.out.println("退出登录，欢迎下次再来！");
            break;
        } else {
            System.out.println("别乱选，没这选项！");
        }
    }
}

使用连接池优化博客系统

随着用户增多，直接获取数据库连接的方式可能会拖慢系统。咱们用连接池来优化一下。

添加 Maven 依赖

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

使用 HikariCP 连接池重构代码

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

// 在 BlogUserManager 类中添加以下静态变量和方法
private static HikariDataSource dataSource;

// 初始化连接池
private static void initializeDataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:mysql://localhost:3306/blog_system");
    config.setUsername("root");
    config.setPassword("123456");
    config.setMaximumPoolSize(10); // 最大连接数
    config.setMinimumIdle(2); // 最小空闲连接数
    config.setIdleTimeout(30000); // 空闲超时时间（毫秒）
    config.setConnectionTimeout(5000); // 连接超时时间（毫秒）

    dataSource = new HikariDataSource(config);
    System.out.println("数据库连接池初始化成功！就像准备了一池子的水，随时可以取用！");
}

// 从连接池获取连接
private static Connection getConnection() throws SQLException {
    return dataSource.getConnection();
}

// 修改主方法，初始化连接池
public static void main(String[] args) {
    initializeDataSource(); // 初始化连接池
    Scanner scanner = new Scanner(System.in);

    System.out.println("欢迎来到博客系统！");
    System.out.println("1. 注册");
    System.out.println("2. 登录");
    System.out.print("请选择：");

    int choice = scanner.nextInt();
    scanner.nextLine(); // 消耗换行符

    if (choice == 1) {
        registerUser(scanner);
    } else if (choice == 2) {
        login(scanner);
    } else {
        System.out.println("别乱选啊，瞎捣鼓！");
    }

    scanner.close();
    dataSource.close(); // 关闭连接池
}

// 修改所有获取连接的地方，使用连接池获取连接
// 例如，将下面代码：
// try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
// 修改为：
// try (Connection connection = getConnection();

实现博客文章的点赞功能

再给博客加个点赞功能，这样用户就能表达对文章的喜爱啦。

修改数据库表结构

-- 点赞表
CREATE TABLE likes (
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    liked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id),
    FOREIGN KEY (post_id) REFERENCES blog_posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

添加点赞功能代码

// 在 BlogUserManager 类中添加以下方法

// 点赞文章
private static void likePost(Scanner scanner, int userId) {
    System.out.print("请输入要点赞的文章ID：");
    int postId = scanner.nextInt();
    scanner.nextLine(); // 消耗换行符

    String insertSQL = "INSERT INTO likes (post_id, user_id) VALUES (?, ?)";
    String querySQL = "SELECT COUNT(*) AS like_count FROM likes WHERE post_id = ?";

    try (Connection connection = getConnection();
         java.sql.PreparedStatement pstmtInsert = connection.prepareStatement(insertSQL);
         java.sql.PreparedStatement pstmtQuery = connection.prepareStatement(querySQL)) {

        // 点赞
        pstmtInsert.setInt(1, postId);
        pstmtInsert.setInt(2, userId);
        int rowsAffected = pstmtInsert.executeUpdate();

        if (rowsAffected > 0) {
            // 查询当前点赞数
            pstmtQuery.setInt(1, postId);
            try (java.sql.ResultSet resultSet = pstmtQuery.executeQuery()) {
                if (resultSet.next()) {
                    int likeCount = resultSet.getInt("like_count");
                    System.out.println("点赞成功！这篇文章现在有 " + likeCount + " 个赞啦！");
                }
            }
        } else {
            System.out.println("你已经点过赞啦，别重复点赞哦！");
        }

    } catch (SQLException e) {
        System.out.println("点赞出问题啦：" + e.getMessage());
    }
}

// 查看带点赞数的文章列表
private static void viewPosts_withLikes(Scanner scanner) {
    String querySQL = "SELECT bp.id, bp.title, bp.content, bp.created_at, u.username, " +
                      "COUNT(l.user_id) AS like_count " +
                      "FROM blog_posts bp " +
                      "JOIN users u ON bp.user_id = u.id " +
                      "LEFT JOIN likes l ON bp.id = l.post_id " +
                      "GROUP BY bp.id, bp.title, bp.content, bp.created_at, u.username " +
                      "ORDER BY bp.created_at DESC";

    try (Connection connection = getConnection();
         java.sql.PreparedStatement pstmt = connection.prepareStatement(querySQL);
         java.sql.ResultSet resultSet = pstmt.executeQuery()) {

        System.out.println("\n所有文章（带点赞数）：");
        while (resultSet.next()) {
            int postId = resultSet.getInt("bp.id");
            String title = resultSet.getString("bp.title");
            String content = resultSet.getString("bp.content");
            String createdAt = resultSet.getTimestamp("bp.created_at").toString();
            String author = resultSet.getString("u.username");
            int likeCount = resultSet.getInt("like_count");

            System.out.printf("文章ID：%d，标题：%s，作者：%s，发布时间：%s，点赞数：%d%n", 
                             postId, title, author, createdAt, likeCount);
            System.out.println("内容：" + content);
            System.out.println("-----------------------------------");
        }

    } catch (SQLException e) {
        System.out.println("查看文章出问题啦：" + e.getMessage());
    }
}

// 修改主菜单，添加新选项
private static void showBlogSystemMenu(Scanner scanner, int userId) {
    while (true) {
        System.out.println("\n博客系统主菜单：");
        System.out.println("1. 发表新文章");
        System.out.println("2. 查看所有文章");
        System.out.println("3. 评论文章");
        System.out.println("4. 给文章添加标签");
        System.out.println("5. 按标签搜索文章");
        System.out.println("6. 点赞文章");
        System.out.println("7. 查看带点赞数的文章列表");
        System.out.println("8. 退出登录");
        System.out.print("请选择：");

        int choice = scanner.nextInt();
        scanner.nextLine(); // 消耗换行符

        if (choice == 1) {
            publishNewPost(scanner, userId);
        } else if (choice == 2) {
            viewAllPosts(scanner);
        } else if (choice == 3) {
            commentOnPost(scanner, userId);
        } else if (choice == 4) {
            addTagsToPost(scanner, userId);
        } else if (choice == 5) {
            searchPostsByTag(scanner);
        } else if (choice == 6) {
            likePost(scanner, userId);
        } else if (choice == 7) {
            viewPosts_withLikes(scanner);
        } else if (choice == 8) {
            System.out.println("退出登录，欢迎下次再来！");
            break;
        } else {
            System.out.println("别乱选，没这选项！");
        }
    }
}

总结

今天咱这博客系统算是搭了个雏形，从用户注册登录，到发表文章、评论、添加标签、点赞，基本功能都有了。在这个过程中，咱也把 Java 数据库操作的方方面面都实践了一遍：

JDBC 基础：学会了怎么连接数据库，查询、插入、更新、删除数据，这是玩数据库的看家本领。
PreparedStatement：明白了用它能防 SQL 注入，写代码也更规范，就像给 SQL 戴上了安全帽。
连接池：知道了连接池的好，用 HikariCP 优化了数据库连接管理，让系统能扛住更多用户访问。
事务管理：在关键操作（比如点赞、评论）中用事务保证数据一致性，这在金融、电商系统里更是关键。
数据库设计：从最开始设计用户表、文章表、评论表，到后来扩展标签表、点赞表，体会了数据库设计的灵活性和扩展性。

实际开发中，咱还会用到很多高级技巧和框架，比如用 MyBatis 或 Hibernate 这样的 ORM 框架简化代码，用 Redis 缓存热点数据减轻数据库压力，用 Elasticsearch 做全文搜索提升用户体验。但万变不离其宗，今天学的这些基础，就像盖楼打下的地基，后面不管盖多高，这基础都得扎实。

AI Agent技术社区

Agent 垂直技术社区，欢迎活跃、内容共建。

更多推荐

SeedVR2终极指南：如何用单步扩散模型实现专业级视频修复

还在为老旧视频的模糊画质而烦恼吗？面对抖动严重的监控录像或褪色的家庭影像，你是否曾希望有一种简单高效的修复方案？SeedVR2正是为解决这些痛点而生的一站式视频修复工具。作为字节跳动推出的先进视频修复模型，它能够在单步推理中实现专业级的视频质量提升，彻底改变了传统视频修复的工作流程。## 核心价值：为什么选择SeedVR2进行视频修复？**传统视频修复的三大瓶颈**：1. **计算成本高

AI Agent技术社区

深度解析Amlogic S9xxx电视盒子Armbian系统：从闲置设备到Linux服务器的终极实战指南

你是否拥有一台闲置的Amlogic S9xxx系列电视盒子？想让它摆脱单一的视频播放功能，变身为功能强大的Linux服务器、开发平台或家庭媒体中心？本指南将为你详细解析如何通过Armbian系统实现这一转变，让你的电视盒子重获新生，成为高性能的Linux设备。Armbian系统基于Debian/Ubuntu构建，专为ARM芯片优化，提供了完整的Linux生态支持。## 🎯 核心价值与独特优势

AI Agent技术社区

CANN/runtime Event管理API

本章节描述 CANN Runtime 的 Event 管理接口，用于事件的创建、记录、同步、计时及 IPC 跨进程共享。- [`aclError aclrtCreateEvent(aclrtEvent *event)`](#aclrtCreateEvent)：创建Event，创建出来的Event可用于统计两个Event之间的耗时、多Stream之间的任务同步等场景。- [`aclError