VsCode + CMake构建项目 C/C++连接Mysql数据库 | 数据库增删改查C++封装

VsCode + CMake构建项目 C/C++连接Mysql数据库 | 数据库增删改查C++封装 | 信息管理系统通用代码 ---- 课程笔记

这个是B站Up主：程序员程子青的视频

C++封装Mysql增删改查操作_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1m24y1a79o/?p=6&spm_id_from=pageDriver&vd_source=a934d7fc6f47698a29dac90a922ba5a3安装mysql:mysql 下载和安装和修改MYSQL8.0 数据库存储文件的路径-CSDN博客

创建数据库和表：

C:\Users\heheda>mysql -u heheda -p
mysql> create database test;
mysql> show databases;
mysql> use test;
mysql> create table student(stuId int not null auto_increment primary key,stuName varchar(255) not null,className varchar(255) not null);
mysql> show tables;
mysql> insert into student values(3,'杰瑞','终极三班');
Query OK, 1 row affected (0.00 sec)

参考这篇文章：windows下使用vscode原生态配置c++链接mysql数据库：windows下使用vscode原生态配置c++链接mysql数据库_vscode 链接 lib库-CSDN博客

将mysql下的include文件夹直接拷贝到项目目录下【或者只拷贝include中的mysql.h文件】方便引用mysql.h头文件
拷贝libmysql.dll 、libmysql.lib、mysqlclient.lib文件直接放在工程目录下因为这里可执行文件在其所在目录下直接寻找动态链接源文件
当cmake构建好项目之后，我们可以把libmysql.dll 、libmysql.lib文件直接放在bin目录下

StudentManager.h

#pragma once
#include <mysql.h>
#include <iostream>
#include <string>
#include <vector>

using namespace std;

typedef struct student {
    int stuId;
    string stuName;
    string className;
}Student;

class StudentManager {
private:
    StudentManager();
    ~StudentManager();
public:
    static StudentManager* GetInstance() { //单例修改
        static StudentManager StudentManager;
        return &StudentManager;
    }
public:
    // 增上改查
    bool insertStu(Student& stu);
    bool updateStu(Student& stu);
    bool deleteStu(int stuId);
    vector<Student> queryStu(string condition = "");
private:
    MYSQL* conn;
    const char* host = "127.0.0.1";
    const char* user = "heheda";
    const char* pwd = "123456";
    const char* dbName = "test";
    const unsigned short port = 3306;
    const char* tableName = "student";
};

StudentManager.cpp

#include "StudentManager.h"

StudentManager::StudentManager() {
    conn = mysql_init(NULL);
    // 设置字符编码
    mysql_options(conn, MYSQL_SET_CHARSET_NAME, "GBK");
    if(!mysql_real_connect(conn,host,user,pwd,dbName,port,NULL,0)) {
        std::cout<<"Failed to connect"<<std::endl;
        exit(1);
    }
}

StudentManager::~StudentManager() {
    mysql_close(conn);
}

bool StudentManager::insertStu(Student &stu) {
    char sql[1024];
    sprintf(sql,"insert into student (stuId,stuName,className) values(%d,'%s','%s')",
            stu.stuId,stu.stuName.c_str(),stu.className.c_str());
    // mysql_query成功返回0，失败返回非0
    if(mysql_query(conn,sql)) {
        fprintf(stderr,"Failed to insert data into database!!!Error:%s\n",
                mysql_error(conn));
        return false;
    }
    return true;
}

// c_str()：生成一个const char*指针，指向以空字符终止的数组
bool StudentManager::updateStu(Student &stu) {
    char sql[1024];
    sprintf(sql,"UPDATE student SET stuName = '%s',className = '%s'"
    "where stuId = %d",stu.stuName.c_str(),stu.className.c_str(),stu.stuId);
    if(mysql_query(conn,sql)) {
        fprintf(stderr,"Failed to update data!!!Error:%s\n",mysql_error(conn));
        return false;
    }
    return true;
}

bool StudentManager::deleteStu(int stuId) {
    char sql[1024];
    sprintf(sql,"DELETE FROM student WHERE stuId = '%d'",stuId);
    if(mysql_query(conn,sql)) {
        fprintf(stderr,"Failed to delete data!!!Error:%s\n",mysql_error(conn));
        return false;
    }
    return true;
}

vector<Student> StudentManager::queryStu(string condition) {
    vector<Student> stuList;
    char sql[1024];
    sprintf(sql,"SELECT * FROM student %s",condition.c_str());
    if(mysql_query(conn,sql)) {
        fprintf(stderr,"Failed to select data!!!Error:%s\n",mysql_error(conn));
        return {};
    }
    MYSQL_RES* res = mysql_store_result(conn);
    MYSQL_ROW row;
    while((row = mysql_fetch_row(res))) {
        Student stu;
        stu.stuId = atoi(row[0]);
        stu.stuName = row[1];
        stu.className = row[2];
        stuList.push_back(stu);
    }
    return stuList;
}

CMakeLists.txt

cmake_minimum_required(VERSION 3.10)
project(test)
include_directories(${PROJECT_SOURCE_DIR}/include)

set(StudentManager ${PROJECT_SOURCE_DIR}/StudentManager) 
include_directories(${StudentManager}/include)

aux_source_directory(${StudentManager}/src StudentManagerSrc)

link_directories(${PROJECT_SOURCE_DIR}/lib)
add_executable(app test.cpp ${StudentManagerSrc})
target_link_libraries(app mysql)

# 指定输出的路径
set(HOME ${PROJECT_SOURCE_DIR}) # 定义一个变量用于存储一个绝对路径
set(EXECUTABLE_OUTPUT_PATH ${HOME}/bin) # 将拼接好的路径值设置给 EXECUTABLE_OUTPUT_PATH 变量

test.cpp

#include "StudentManager.h"
#include <mysql.h>

int main() {
    Student stu{3,"杰瑞","猫鼠一班"};
    // StudentManager::GetInstance()->insertStu(stu);
    // StudentManager::GetInstance()->deleteStu(3);
    StudentManager::GetInstance()->updateStu(stu);
    char condition[1024];
    sprintf(condition,"where className = '%s'","终极一班");
    // sprintf(condition,"where stuId = %d",2);
    // string condition = string();
    vector<Student> ret= StudentManager::GetInstance()->queryStu(condition);
    for(auto& it:ret){
        std::cout<<"打印: ";
        cout<<it.stuId<<" "<<it.stuName<<" "<<it.className<<endl;
    }
    std::cout<<"I am heheda!"<<std::endl;
    return 0;
}