C++使用MySQL Connector对mysql数据库的操作

测试环境：centos7

安装：

sudo yum remove mysql-connector-c++ mysql-connector-c++-devel
sudo yum install mysql-connector-c++ mysql-connector-c++-devel

类封装：

#include <iostream>
#include <mysql-cppconn/jdbc/mysql_driver.h>
#include <mysql-cppconn/jdbc/mysql_connection.h>
#include <mysql-cppconn/jdbc/cppconn/statement.h>
#include <mysql-cppconn/jdbc/cppconn/resultset.h>
#include <mysql-cppconn/jdbc/cppconn/exception.h>
#include <mysql-cppconn/jdbc/cppconn/prepared_statement.h>
#include <unistd.h>

class MySQLDB {
private:
    sql::Connection* con;
    sql::ResultSet* result;
    std::string host;
public:
    MySQLDB(const std::string& host = "127.0.0.1:3306")
        : host(host)
    {
        con = nullptr;
        result = nullptr;
    }

    ~MySQLDB() {
        this->free();
        if (con) {
            delete con;
        }
    }
    void free() {
        if (result) {
            delete result;
            result = nullptr;
        }
    }

    bool connect(std::string user = "", std::string password = "", std::string database = "") {
        if (con) {
            return false;
        }
        try {
            con = sql::mysql::get_mysql_driver_instance()->connect("tcp://" + host, user, password);
            if (!con) {
                return false;
            }
            if (database.empty()) {
                database = user;
            }
            con->setSchema(database);
        }
        catch (sql::SQLException& e) {
            std::cout << "Connection failed: " << e.what() << std::endl;
            return false;
        }
        return true;
    }
    bool setDatabase(const std::string& database) {
        try
        {
            if (con) {
                con->setSchema(database);
                return true;
            }
        }
        catch (sql::SQLException& e)
        {

        }
        return false;
    }


    bool setAutoCommit(bool autoCommit = false) {
        if (con) {
            con->setAutoCommit(autoCommit);
            return true;
        }
        return false;
    }

    bool commit() {
        if (con) {
            try {
                con->commit();
                return true;
            }
            catch (sql::SQLException& e) {
                std::cout << "Transaction commit failed: " << e.what() << std::endl;
            }
        }
        else {
            std::cout << "No active connection." << std::endl;
        }
        return false;
    }

    void rollback() {
        if (con) {
            try {
                con->rollback();
            }
            catch (sql::SQLException& e) {
                std::cout << "Transaction rollback failed: " << e.what() << std::endl;
            }
        }
        else {
            std::cout << "No active connection." << std::endl;
        }
    }

    sql::ResultSet* query(const std::string& query) {
        this->free();
        try {
            if (con) {
                sql::Statement* stmt = con->createStatement();
                result = stmt->executeQuery(query);
                delete stmt;
            }
            else {
                std::cout << "No active connection." << std::endl;
            }
        }
        catch (sql::SQLException& e) {
            std::cout << "Query failed: " << e.what() << std::endl;
        }
        return result;
    }

    bool insert(const std::string& query) {
        this->free();
        try {
            if (con) {
                sql::Statement* stmt = con->createStatement();
                stmt->execute(query);
                std::cout << "Insert successful." << std::endl;
                delete stmt;
            }
            else {
                std::cout << "No active connection." << std::endl;
            }
        }
        catch (sql::SQLException& e) {
            std::cout << "Insert failed: " << e.what() << std::endl;
            return false;
        }
        return true;
    }

    bool executePreparedStatement(const std::string& query, const std::vector<std::string>& params) {
        this->free();
        try {
            if (con) {
                sql::PreparedStatement* pstmt = con->prepareStatement(query);
                for (size_t i = 0; i < params.size(); ++i) {
                    pstmt->setString(i + 1, params[i]);
                }
                pstmt->execute();
                std::cout << "Prepared statement executed successfully." << std::endl;
                delete pstmt;
            }
            else {
                std::cout << "No active connection." << std::endl;
            }
        }
        catch (sql::SQLException& e) {
            std::cout << "Prepared statement execution failed: " << e.what() << std::endl;
            return false;
        }
        
        return true;
    }
};

调用测试：

int main() {
    MySQLDB db;
    db.connect("wordpress", "wordpress");

    db.setAutoCommit(false);

    // 执行插入
    //db.insert("INSERT INTO wp_users (user_login, user_pass) VALUES ('Test username', 'Test password')");

    //std::vector<std::string> params = { "Test Title 222", "Test Content 222" };
    //db.executePreparedStatement("INSERT INTO wp_users (user_login, user_pass1) VALUES (?, ?)", params);

    //db.rollback();

    //db.insert("INSERT INTO wp_users (user_login, user_pass) VALUES ('Test username11122333', 'Test password123123123')");

    std::vector<std::string> params = { "123...", "321" };
    db.executePreparedStatement("UPDATE wp_users set user_login=?, user_pass=? where ID=4", params);

    db.commit();


    sql::ResultSet* res = db.query("SELECT * FROM wp_users");

    while (res->next()) {
        printf("ID: %d\n", res->getInt("ID"));
    }

  
    return 0;
}

×

*转载请注明出处：原文链接：https://cpp.vin/page/140.html