笨蛋学C++之 C++对数据库实现CRUD

头文件

testcrud.h

#pragma once
#include <mysql.h>
#include <iostream>
#include <vector>
#include <cstring> // 包含字符串操作相关的头文件
using namespace std;

typedef struct Test {
	int id;
	string name;
	Test(){}
	// 添加一个构造函数
	Test(int id, string name){
		this->id = id;
		this->name = name;
	}
}Test;

class testCrud{
	testCrud();
	~testCrud();
public:
	//创建单例模式
	static testCrud* getInstance() {
		static testCrud instance; // 创建一个静态实例
		return &instance;        // 返回这个静态实例的地址
	}
public:
	bool test_insert(Test &test);
	bool test_update(Test& test);
	bool test_delete(Test& test);
	vector<Test> test_select(string condition = "");

private:
	MYSQL* con;
	const char* host = "127.0.0.1";
	const char* user = "root";
	const char* password = "数据库密码";
	const char* database_name = "数据库名";
	const int port = 3306;
};

源文件

testcrud.cpp

#include <iostream>
#include "test-crud.h"
using namespace std;

testCrud::testCrud() {
	// 设置链接
	con = mysql_init(NULL);
	// 设置编码
	mysql_options(con, MYSQL_SET_CHARSET_NAME, "GBK");
	// 声明链接
	if (!mysql_real_connect(con, host, user, password, database_name, port, NULL, 0)) {
		cerr << "Failed to connect to database Error: " << mysql_error(con) << endl;
		exit(1);
	}
}
testCrud::~testCrud() {
	//关闭连接
	mysql_close(con);

}

//插入
bool testCrud::test_insert(Test& test) {
	char sql[1024];
	sprintf_s(sql, sizeof(sql), "INSERT INTO test (id,name) VALUES (%d,'%s')", test.id, test.name.c_str());

	if (mysql_query(con, sql)) {
		cerr << "Failed to insert to database Error: " << mysql_error(con) << endl;
		return false;
	}
	return true;
}
//修改
bool testCrud::test_update(Test& test) {
	char sql[1024];
	sprintf_s(sql, sizeof(sql), "UPDATE test SET name = '%s' WHERE id = %d", test.name.c_str(),test.id);

	if (mysql_query(con, sql)) {
		cerr << "Failed to update to database Error: " << mysql_error(con) << endl;
		return false;
	}
	return true;
}
//删除
bool testCrud::test_delete(Test& test) {
	char sql[1024];
	sprintf_s(sql, sizeof(sql), "DELETE FROM test WHERE id = %d",test.id);

	if (mysql_query(con, sql)) {
		cerr << "Failed to delete to database Error: " << mysql_error(con) << endl;
		return false;
	}
	return true;
}
//查询
vector<Test> testCrud::test_select(string condition) {

	vector<Test> testList;
	char sql[1024];
	//若condition为空，查出所有的数据
	sprintf_s(sql, sizeof(sql), "SELECT * FROM test %s", condition.c_str());

	if (mysql_query(con, sql)) {
		cerr << "Failed to select to database Error: " << mysql_error(con) << endl;
		return {};
	}
	MYSQL_RES* res = mysql_store_result(con);

	MYSQL_ROW row;
	while ((row = mysql_fetch_row(res))) {
		Test test;
		test.id = atoi(row[0]);
		test.name = atoi(row[1]);

		testList.push_back(test);
	}
	return vector<Test>();
}

main.cpp

#include <mysql.h>
#include <iostream>
#include <cstring> // 包含字符串操作相关的头文件

#include "test-crud.h"
using namespace std;

int main(void) {
    Test test(4, "王九");
    testCrud::getInstance()->test_update(test); // 调用 update_test 方法
    return 0;
}