mysql的封装（linux）

1.设置

Properties

常用API

#include<iostream>
#include<mysql/mysql.h>
#include<cstring>
using namespace std;

int main(){
	//1.建立连接（到数据库服务器的链接）
	MYSQL mysql;
	mysql_init(&mysql);//传地址
	if(mysql_real_connect(&mysql,"127.0.0.1","root","pass","test",3306,NULL,0)==0){
		cout<<"connect error:"<<" "<<mysql_error(&mysql)<<endl;
	}
	//（连接的对象，连接的服务器的地址,登录的用户名，登录的密码,数据库名,端口名,sucked连接用NULL,错误参数）
	//2.编写sql语句
	char* sql =(char*)"select * from usertable";
	//3.执行查询
	mysql_real_query(&mysql,"set names utf8", strlen("set names utf8")); //解决中文乱码
	if(mysql_real_query(&mysql,sql,strlen(sql))){
		cout<<"Query Error:"<<" "<<mysql_error(&mysql)<<endl;
	}
	//4.获取查询结果并且显示

	MYSQL_RES* res=mysql_store_result(&mysql);
	if(!res){
		cout<<"MYSQL_RES ERROR:"<<mysql_error(&mysql)<<endl;
	}
	MYSQL_ROW row;
	while(row=mysql_fetch_row(res)){//一行一行的拿
		for(int i=0;i<mysql_num_fields(res);i++){
			cout<<row[i]<<" ";
		}
		cout<<endl;
	}
	//5.释放资源
	mysql_free_result(res);
	//6.关闭连接
	mysql_close(&mysql);
	return 0;
}

1 小明 13815883177 鼓楼区 2020-07-25 14:20:20
2 小王 15151885888 中国 2020-07-25 14:21:25
3 张三 17312236223 南京 2020-07-25 14:21:45

class ContactUser{
public:
	int id;
	string uname;
	string tel;
	string ctime;
	int bonus;
	int salary;
};


class IUserDAO{
public:
	virtual int add(ContactUser& user)=0;


	virtual int remove(int id) = 0;

	virtual int modify(ContactUser& user)=0;

	virtual vector<ContactUser*> list() =0;

	virtual ~IUserDAO(){}

};

class UserDAO:public IUserDAO{
	int add(ContactUser& user){
		MYSQL mysql;
		mysql_init(&mysql);
		if(!mysql_real_connect(&mysql, "127.0.0.1", "root", "pass", "test", 3306, NULL, 0) ){
			cout << "connect error" << mysql_error(&mysql) << endl;
			exit(1);
		}

		char sql[100]={0};

		sprintf(sql,"insert into tbl_user values(NULL, '%s', '%s', now(), %d, %d) ",
				user.uname.c_str(), user.tel.c_str(), user.bonus, user.salary);

		//cout << "sql:" << sql <<endl;

		if(mysql_real_query(&mysql, sql, strlen(sql) ) ){
			cout << "Query error" << mysql_error(&mysql) << endl;
		}

		mysql_close(&mysql);

		return 0;

	}

	int remove(int id){
		MYSQL mysql;
		mysql_init(&mysql);
		if(!mysql_real_connect(&mysql, "127.0.0.1", "root", "pass", "test", 3306, NULL, 0) ){
			cout << "connect error" << mysql_error(&mysql) << endl;
			exit(1);
		}

		char sql[100]={0};

		sprintf(sql,"delete from tbl_user where id = %d",id);

		//cout << "sql:" << sql <<endl;

		if(mysql_real_query(&mysql, sql, strlen(sql) ) ){
			cout << "Query error" << mysql_error(&mysql) << endl;
		}

		mysql_close(&mysql);

		return 0;
	}

	int modify(ContactUser& user){
		MYSQL mysql;
		mysql_init(&mysql);
		if(!mysql_real_connect(&mysql, "127.0.0.1", "root", "pass", "test", 3306, NULL, 0) ){
			cout << "connect error" << mysql_error(&mysql) << endl;
			exit(1);
		}

		char sql[100]={0};

		sprintf(sql,"update tbl_user set uname = '%s', tel='%s', ctime = now(), bonus=%d, salary=%d  where id = %d ",
				user.uname.c_str(), user.tel.c_str(), user.bonus, user.salary, user.id);

		//cout << "sql:" << sql <<endl;

		if(mysql_real_query(&mysql, sql, strlen(sql) ) ){
			cout << "Query error" << mysql_error(&mysql) << endl;
		}

		mysql_close(&mysql);

		return 0;
	}


	vector<ContactUser*> list(){
		vector<ContactUser*> v;

		MYSQL mysql;
		mysql_init(&mysql);
		if(!mysql_real_connect(&mysql, "127.0.0.1", "root", "pass", "test", 3306, NULL, 0) ){
			cout << "connect error" << mysql_error(&mysql) << endl;
			exit(1);
		}

		char * sql= "select * from tbl_user;";
		mysql_real_query(&mysql,"set names utf8", strlen("set names utf8") );
		if(mysql_real_query(&mysql, sql, strlen(sql) ) ){
			cout << "Query error" << mysql_error(&mysql) << endl;
		}

		MYSQL_RES* res= mysql_store_result(&mysql);
		if(!res){
			cout << "MYSQL_RES error:" << mysql_error(&mysql) << endl;
		}

		MYSQL_ROW row;
		while(row = mysql_fetch_row(res)){
			ContactUser* up=new ContactUser();
			up->id = atoi(row[0]);
			up->uname.assign(row[1]);
			up->tel = row[2];
			up->ctime = row[3];
			up->bonus = atoi(row[4]);
			up->salary = atoi(row[5]);

			v.push_back(up);

		}

		mysql_free_result(res);
		mysql_close(&mysql);

		return v;
	}


};