C++操作mysql数据库类

头文件

CMysql.h

  1 #ifndef CMYSQL_H
  2 #define CMYSQL_H
  3 
  4 #include <mysql/mysql.h>
  5 #include <iostream>
  6 #include <string>
  7 #include <vector>
  8 
  9 using namespace std;
 10 
 11 class CMysql
 12 {
 13 public:
 14         CMysql();
 15         ~CMysql();
 16         CMysql(const string host,const string user,const string pwd,const string db,const int port);
 17         int query_0(const string sql);//NOTHING SELECT
 18         string query_1(const string sql);//Select one
 19         int query_N(const string sql,vector<string> &fieldName,vector< vector<string> > &fieldData);//select N
 20         int createdb(const string db);
 21         int dropdb(const string db);
 22 private:
 23         bool connected;
 24         MYSQL *ytsql;
 25 };      
 26 
 27 #endif

定义了2个成员变量ytsql跟connected

ytsql用来保存对mysql操作的MYSQL *结构

connected用来保存是否mysql连接成功

定义了创建跟删除db的成员函数

定义了3个query成员函数

query_0是执行不需要返回结果的语句,如insert,update,detele,create

query_1是执行语句后返回一条结果

query_N是执行语句后返回N条语句结果

field的name保存到vector<string> fieldName中

field的数据保存到vector<vector<string> > fieldData中

CMysql.cpp:

#include "CMysql.h"

CMysql::CMysql()
{
	connected=false;
}

CMysql::~CMysql()
{
	if(connected)
		if(ytsql)
			mysql_close(ytsql);
}

CMysql::CMysql(const string host,const string user,const string pwd,const string db,const int port)
{
	connected=false;
	ytsql=mysql_init(NULL);
	if(ytsql==mysql_real_connect(ytsql,host.c_str(),user.c_str(),pwd.c_str(),db.c_str(),port,NULL,0))
	{	
		if(mysql_select_db(ytsql,db.c_str())<0)
		{
			mysql_close(ytsql);
		}
		connected=true;
	}	
}

//No result;eg INSERT UPDATE DELECT CREATE
int CMysql::query_0(const string sql)
{
	if(connected)
	{
		if(!mysql_real_query(ytsql,sql.c_str(),(unsigned long)sql.length()))
		{
			return (int)mysql_affected_rows(ytsql);
		}
		else
			return -1;
	}
	else
		return -2;
}

//select ONE
string CMysql::query_1(const string sql)
{
	if(connected)
	{
		MYSQL_RES *res;
		MYSQL_ROW row;
		if(!mysql_real_query(ytsql,sql.c_str(),(unsigned long)sql.length()))
		{
			res=mysql_store_result(ytsql);
			row=mysql_fetch_row(res);
			mysql_free_result(res);
			return (row[0]==NULL)?"-1":row[0];
		
		}
		else
			return "-1";
	}
	else
		return "-2";

}


//Select N
int CMysql::query_N(const string sql,vector<string> &fieldName,vector< vector<string> > &fieldData)
{
	if(connected)
	{
		int count,j;
		if(!mysql_real_query(ytsql,sql.c_str(),(unsigned long)sql.length()))
		{
			MYSQL_RES *res;
			MYSQL_ROW row;
			MYSQL_FIELD *fd;
			res=mysql_store_result(ytsql);
			count=(int)mysql_num_rows(res);
			j=mysql_num_fields(res);
			
			//get every row include NAME and TYPE
			for(int i=0;i<j;i++)
			{
				fd=mysql_fetch_field(res);
				fieldName.push_back(fd->name);
				
			}
			
			//get Data
			while(row=mysql_fetch_row(res))
			{
				vector<string> rowVec;
				for(int i=0;i<j;i++)
					rowVec.push_back(row[i]);
				fieldData.push_back(rowVec);
			}
			mysql_free_result(res);
			return count;
		}
		else
			return -1;
	
	}
	else
		return -2;

}

int CMysql::createdb(const string db)
{
	if(connected)
	{
		string temp="CREATE DATABASE ";
		temp+=db;
		if(!mysql_real_query(ytsql,temp.c_str(),(unsigned long)temp.length()))
			return 0;
		else
			return -1;
	}
	else
		return -2;
}

int CMysql::dropdb(const string db)
{
	if(connected)
	{
		string temp="Drop Database ";
		temp+=db;
		if(!mysql_real_query(ytsql,temp.c_str(),(unsigned long)temp.length()))
			return 0;
		else
			return -1;
	}
	else
		return -2;
}