C++图书管理系统（三）MySQL基本操作

实现菜单

首先我们想确定我们的图书管理需要实现哪些功能？我们这里实现最基础的增删改查。

我们这里先实现数据库的链接，以及一个简单的菜单。

#include <iostream>
#include <string>
// MySQL Connector/C++ 核心头文件
#include <mysql_driver.h>    // MySQL驱动接口
#include <mysql_connection.h> // 数据库连接类
#include <cppconn/statement.h> // SQL语句执行接口
#include <cppconn/resultset.h> // 结果集处理类
#include <cppconn/exception.h> // SQL异常类

void addBook(sql::Connection *con);

void deleteBook() {};

void viewBook() {};

void updateBook() {};

int main() {
    try {
        // 创建链接
        sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();
        sql::Connection *con = driver->connect("tcp://127.0.0.1:3306", "root", "123456");
        con->setSchema("testdb");

        int choice;
        do { // 显示菜单
            std::cout << "\n==== 图书管理系统 ====\n"
                         "1. 增加图书\n"
                         "2. 查看所有图书\n"
                         "3. 更新图书\n"
                         "4. 删除图书\n"
                         "5. 退出系统\n";
            std::cin >> choice;
            if (choice == 1) addBook(con);
            else if (choice == 2) viewBook();
            else if (choice == 3) updateBook();
            else if (choice == 4) deleteBook();
            else if (choice == 5) break;
            else std::cout << "错误选项\n";
        } while (true);
        std::cout << "系统已退出\n";
    } catch (sql::SQLException &e) {
        std::cout << "数据库链接失败\n";
    }
    return 0;
}

void addBook(sql::Connection *con) {

}

添加图书

首先我们要确定的一点是，要操作数据库，就必须要获得sql::Connection，因此我们把函数的形参修改为

void addBook(sql::Connection *con);

我们在观察我们的表

+---------+------------------------+---------------+----------------+
| book_id | title                  | author        | published_date |
+---------+------------------------+---------------+----------------+
|       1 | The Catcher in the Rye | J.D. Salinger | 1951-07-16     |
|       2 | Harry Potter           | J.K. Rowing   | 1997-06-26     |
|    1234 | The Hobbit             | J.R.R Tolkien | 1937-09-21     |
|    1235 | 1984                   | Orwell        | 1949-06-08     |
+---------+------------------------+---------------+----------------+

我们这里只需要书名、作者、出版日期就好了。

这里面，我们为了可以对sql语句进行复用，我们这里引入了sql::PreparedStatement类，这个类简单来说就是可以?来作为占位符，后续在通过setString(), setInt()等方法将常数绑定到语句，并最后通过execute()调用来执行sql语句

因此我们最终的函数可以写成如下形式

void addBook(sql::Connection *con) {
    try {
        std::string title, author, date;
        std::cout << "请输入书名：\n";
        std::cin.ignore();
        std::getline(std::cin, title);
        std::cout << "请输入作者：\n";
        std::getline(std::cin, author);
        std::cout << "请输入出版日期(YYYY-MM-DD)：\n";
        std::getline(std::cin, date);

        sql::PreparedStatement *pstmt = con->prepareStatement(
                "INSERT INTO books (title,author,published_date) VALUES (?,?,?)");
        pstmt->setString(1, title);
        pstmt->setString(2, author);
        pstmt->setString(3, date);
        pstmt->execute();
        std::cout << "图书添加成功\n";
        delete pstmt;
    } catch (sql::SQLException &e) {
        std::cout << "图书添加失败\n";
    }
}

当然了这里不需要复用，如果真的需要复用可以用lambda等方式封装即可。

查看所有图书

与插入相比，删除操作我们的语句是相对固定的，因此直接使用就好了。和之前从测试代码很类似。

void viewBook(sql::Connection *con) {
    try {
        sql::Statement *stmt = con->createStatement();
        sql::ResultSet *res = stmt->executeQuery("SELECT * FROM books");
        std::cout << "\n====图书列表====\n";
        while(res->next()) {
            std::cout << "ID: " << res->getInt("book_id") << "\n";
            std::cout << "标题: " << res->getString("title") << "\n";
            std::cout << "作者: " << res->getString("author") << "\n";
            std::cout << "出版日期: " << res->getString("published_date") << "\n";
            std::cout << "----------------------------------\n";
        }
        delete stmt;
        delete res;
    } catch (sql::SQLException &e) {
        std::cout << "ca\n";
    }
}

删除图书

要删除图书，我们只要得到图书的id就好了。

void deleteBook(sql::Connection *con) {
    try {
        int book_id;
        std::cout << "输入要删除图书的ID\n";
        std::cin >> book_id;
        sql::PreparedStatement *pstmt = con->prepareStatement("DELETE FROM books WHERE book_id = ?");
        pstmt->setInt(1, book_id);
        pstmt->execute();
        std::cout << "图书删除成功\n";
        delete pstmt;
    } catch (sql::SQLException &e) {
        std::cout << "图书删除失败\n";
    }
}

修改图书

修改图书其实有点类似把删除和添加拼起来。

void updateBook(sql::Connection *con) {
    try {
        int book_id;
        std::cout << "输入要更新图书的ID\n";
        std::cin >> book_id;

        std::string title, author, date;
        std::cout << "请输入书名：\n";
        std::cin.ignore();
        std::getline(std::cin, title);
        std::cout << "请输入作者：\n";
        std::getline(std::cin, author);
        std::cout << "请输入出版日期(YYYY-MM-DD)：\n";
        std::getline(std::cin, date);

        sql::PreparedStatement *pstmt = con->prepareStatement(
                "UPDATE books SET title = ?, author = ?, published_date = ? WHERE book_id = ?");
        pstmt->setString(1, title);
        pstmt->setString(2, author);
        pstmt->setString(3, date);
        pstmt->setInt(4, book_id);
        pstmt->execute();

        std::cout << "图书更新成功\n";
        delete pstmt;
    } catch (sql::SQLException &e) {
        std::cout << "图书更新失败\n";
    }
}

完整代码

#include <iostream>
#include <string>
// MySQL Connector/C++ 核心头文件
#include <mysql_driver.h>    // MySQL驱动接口
#include <mysql_connection.h> // 数据库连接类
#include <cppconn/statement.h> // SQL语句执行接口
#include <cppconn/resultset.h> // 结果集处理类
#include <cppconn/exception.h> // SQL异常类
#include <cppconn/prepared_statement.h>

void addBook(sql::Connection *con);

void deleteBook(sql::Connection *con);

void viewBook(sql::Connection *con);

void updateBook(sql::Connection *con);

int main() {
    try {
        // 创建链接
        sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();
        sql::Connection *con = driver->connect("tcp://127.0.0.1:3306", "root", "123456");
        con->setSchema("testdb");

        int choice;
        do { // 显示菜单
            std::cout << "\n==== 图书管理系统 ====\n"
                         "1. 增加图书\n"
                         "2. 查看所有图书\n"
                         "3. 更新图书\n"
                         "4. 删除图书\n"
                         "5. 退出系统\n";
            std::cin >> choice;
            if (choice == 1) addBook(con);
            else if (choice == 2) viewBook(con);
            else if (choice == 3) updateBook(con);
            else if (choice == 4) deleteBook(con);
            else if (choice == 5) break;
            else std::cout << "错误选项\n";
        } while (true);
        std::cout << "系统已退出\n";
    } catch (sql::SQLException &e) {
        std::cout << "数据库链接失败\n";
    }
    return 0;
}

void addBook(sql::Connection *con) {
    try {
        std::string title, author, date;
        std::cout << "请输入书名：\n";
        std::cin.ignore();
        std::getline(std::cin, title);
        std::cout << "请输入作者：\n";
        std::getline(std::cin, author);
        std::cout << "请输入出版日期(YYYY-MM-DD)：\n";
        std::getline(std::cin, date);

        sql::PreparedStatement *pstmt = con->prepareStatement(
                "INSERT INTO books (title,author,published_date) VALUES (?,?,?)");
        pstmt->setString(1, title);
        pstmt->setString(2, author);
        pstmt->setString(3, date);
        pstmt->execute();
        std::cout << "图书添加成功\n";
        delete pstmt;
    } catch (sql::SQLException &e) {
        std::cout << "图书添加失败\n";
    }
}

void viewBook(sql::Connection *con) {
    try {
        sql::Statement *stmt = con->createStatement();
        sql::ResultSet *res = stmt->executeQuery("SELECT * FROM books");
        std::cout << "\n====图书列表====\n";
        while (res->next()) {
            std::cout << "ID: " << res->getInt("book_id") << "\n";
            std::cout << "标题: " << res->getString("title") << "\n";
            std::cout << "作者: " << res->getString("author") << "\n";
            std::cout << "出版日期: " << res->getString("published_date") << "\n";
            std::cout << "----------------------------------\n";
        }
        delete stmt;
        delete res;
    } catch (sql::SQLException &e) {
        std::cout << "ca\n";
    }
}

void deleteBook(sql::Connection *con) {
    try {
        int book_id;
        std::cout << "输入要删除图书的ID\n";
        std::cin >> book_id;
        sql::PreparedStatement *pstmt = con->prepareStatement("DELETE FROM books WHERE book_id = ?");
        pstmt->setInt(1, book_id);
        pstmt->execute();
        std::cout << "图书删除成功\n";
        delete pstmt;
    } catch (sql::SQLException &e) {
        std::cout << "图书删除失败\n";
    }
}

void updateBook(sql::Connection *con) {
    try {
        int book_id;
        std::cout << "输入要更新图书的ID\n";
        std::cin >> book_id;

        std::string title, author, date;
        std::cout << "请输入书名：\n";
        std::cin.ignore();
        std::getline(std::cin, title);
        std::cout << "请输入作者：\n";
        std::getline(std::cin, author);
        std::cout << "请输入出版日期(YYYY-MM-DD)：\n";
        std::getline(std::cin, date);


        sql::PreparedStatement *pstmt = con->prepareStatement(
                "UPDATE books SET title = ?, author = ?, published_date = ? WHERE book_id = ?");
        pstmt->setString(1, title);
        pstmt->setString(2, author);
        pstmt->setString(3, date);
        pstmt->setInt(4, book_id);
        pstmt->execute();

        std::cout << "图书更新成功\n";
        delete pstmt;
    } catch (sql::SQLException &e) {
        std::cout << "图书更新失败\n";
    }
}
posted @   PHarr  阅读(5)  评论(0)  编辑  收藏  举报
相关博文：
·  C++图书管理系统（一）SQL光速入门
·  C++图书管理系统（二）环境配置
·  图书管理系统（第一版C++文件操作）
·  图书管理系统（最终版C++文件操作）
·  cpp-opt-mysql
阅读排行：
· 开发的设计和重构，为开发效率服务
· ThreeJs-16智慧城市项目（重磅以及未来发展ai）
· 从零开始开发一个 MCP Server！
· .NET 原生驾驭 AI 新基建实战系列（一）：向量数据库的应用与畅想
· Ai满嘴顺口溜，想考研？浪费我几个小时
历史上的今天：
2024-03-19 20240319天梯赛训练
2024-03-19 AtCoder Beginner Contest 345
2023-03-19 AtCoder Beginner Contest 293
2022-03-19 AcWing题目选做
点击右上角即可分享
微信分享提示