QT MySQL驱动

老狼主

于 2024-05-14 17:14:06 发布

阅读量111

点赞数 1

文章标签： qt c++ mysql

本文链接：https://blog.csdn.net/u012156872/article/details/138862215

版权

在某些场合QT自带的QSQLDatabase驱动有效，但是open的时候，程序直接崩溃了，原因未知。不得已将上次使用过的C++版本MySQL驱动根据QT接口进行改动，进行无缝衔接。封装代码如下：

1、头文件定义

#ifndef QSQLDATABASE_H
#define QSQLDATABASE_H

#include <QString>
#include <QVariant>

#ifdef _MSC_VER
#include <string>
#include <map>
#include <vector>
#include "mysql.h"

using namespace std;
typedef std::map<std::string, std::string> FieldsValue;

class QSqlDatabasePrivate;
class QSqlDatabase
{
protected:
	//代表一个到数据库的连接
	MYSQL *mysql;
	//操作的结果集
	MYSQL_RES *dataSet;
	//返回查询得到的结果数
	my_ulonglong num;
	//错误提示信息
	std::string error;
	//查询语句返回一条结果
	FieldsValue record;
	//查询结果集
	std::vector<FieldsValue> records;
	//查询表结构的字段名称
	std::vector<std::string> fields;

public:
    static QSqlDatabase* instance(){
        static QSqlDatabase db;
        return &db;
    }

    QSqlDatabase(void);
    virtual ~QSqlDatabase(void);
    void setDatabaseName(const QString& name);
    void setUserName(const QString& name);
    void setPassword(const QString& password);
    void setHostName(const QString& host);
    void setPort(int p);
    bool open();

	// 连接驱动
	unsigned int connect(std::string host, std::string database, std::string user, std::string password, unsigned int port=3306);
	//执行sql语句
	unsigned int exec(std::string sql);
	unsigned int exec(QString sql);
	//返回查询得到的结果
	std::vector<FieldsValue> fieldValues();
	//返回错误信息
	std::string errorCode();
	//返回查询表结构字段名称
	std::vector<std::string> fieldsName();
	//向数据库中插入一条数据
	unsigned int insert(std::string table, FieldsValue *data);
	//返回最后一个自动增量的值
    unsigned long getlastinsertid();
	//返回一条sql语句影响的行数
	unsigned long numRowsAffected();
	//根据条件修改一条数据
	unsigned int update(std::string table, FieldsValue *data, std::string condition);
	//根据条件删除数据
	unsigned int remove(std::string table, std::string condition);

	//判断数据库是否连接
    bool isOpen();
	//断开数据库连接
    void close();

	void assign(QSqlDatabase*);
private:
    friend class QSqlDatabasePrivate;
    QSqlDatabasePrivate *d;
};

class QSqlQuery
{
public:
    QSqlQuery(QSqlDatabase* pMysqlDriver);
    QSqlQuery();
    virtual ~QSqlQuery(void);
	//执行sql语句
    bool exec(std::string sql);
    bool exec(QString sql);
    bool exec();

    QVariant value(const QString& fieldName);
	//取字符串值
    bool stringValue(std::string &str, std::string fieldName);
	//取整型值
	bool intValue(long &lVal, std::string fieldName);
	//取浮点型值
	bool floatValue(double &lVal, std::string fieldName);
	//返回一条sql语句影响的行数
	long numRowsAffected();
	//记录下移
	bool next();
	//记录上移
	bool previous();
	//记录开头
	bool first();
	//记录结尾
	bool last();
	//是否到记录尾
	bool eof();
	//是否到记录头
	bool bof();
    //最后插入id
    unsigned long lastInsertId();

    bool prepare(const QString& query);
    void bindValue(const QString& placeholder, const QVariant& val);

private:
	//当前值在记录集中的问题
	int index;
    QString sql;

	//记录集
	std::vector<FieldsValue> records;
	//驱动
    QSqlDatabase	*m_pSqlDriver;
};

#endif

#endif

2、源码声明

#ifdef _MSC_VER
#pragma execution_character_set("utf-8")
#endif

#include "QSqlDatabase.h"
#include <assert.h>
#ifdef _MSC_VER

class QSqlDatabasePrivate{
public:
    QSqlDatabasePrivate(QSqlDatabase *d):
        q(d),
        port(-1)
    {

    }

    QSqlDatabase *q;
    QString dbname;
    QString uname;
    QString pword;
    QString hname;
    int port;
};

QSqlDatabase::QSqlDatabase(void)
{
	mysql = NULL;
	num = 0;
	error="";
	dataSet = NULL;
    d = new QSqlDatabasePrivate(this);
}

QSqlDatabase::~QSqlDatabase(void)
{
	if (dataSet != NULL)
		mysql_free_result(dataSet);

    delete d;
}

void QSqlDatabase::setDatabaseName(const QString& name)
{
    d->dbname = name;
}

void QSqlDatabase::setUserName(const QString& name)
{
    d->uname = name;
}

void QSqlDatabase::setPassword(const QString& password)
{
    d->pword = password;
}

void QSqlDatabase::setHostName(const QString& host)
{
    d->hname = host;
}

void QSqlDatabase::setPort(int p)
{
    d->port = p;
}

bool QSqlDatabase::open()
{
    return (0 == connect(d->hname.toStdString(), d->dbname.toStdString(), d->uname.toStdString(), d->pword.toStdString(), d->port));
}

// 设置数据库连接参数
unsigned int QSqlDatabase::connect(std::string host,std::string database, std::string user, std::string password,unsigned int port/*=3306*/)
{
    if (isOpen())
		return 0;

	MYSQL *con;
	if(mysql == NULL)
	{
		mysql = ::mysql_init(NULL);

		if (mysql == NULL)
		{
			error = "初始化mysql错误";
			return 1;
		}
	}

	my_bool my_true = true;
	mysql_options(mysql, MYSQL_OPT_RECONNECT, &my_true);
	con = ::mysql_real_connect(mysql,host.c_str(),user.c_str(),password.c_str(),NULL,port,NULL,0);
	if(con == NULL)
	{
		error = ::mysql_error(mysql);
		return ::mysql_errno(mysql);
	}

	// mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "gb2312");
    ::mysql_set_character_set(mysql,"utf8");

	unsigned int re;
	re = ::mysql_select_db(mysql,database.c_str());
	if(re != 0)
	{
		error += ::mysql_error(mysql);
	}

	return re;
}

//查询数据库
unsigned int QSqlDatabase::exec(QString sql) {
	return exec(sql.toUtf8().toStdString());
}

unsigned int QSqlDatabase::exec(std::string sql)
{
	if(dataSet != NULL )
		mysql_free_result(dataSet);

	unsigned int re;
	if( mysql == NULL) return 1;
	assert(!sql.empty());
	re = ::mysql_query(mysql,sql.c_str());
	if(re == 0)
	{
		dataSet = ::mysql_store_result(mysql);
		num = ::mysql_affected_rows(mysql);
		record.clear();
		records.clear();
		fields.clear();
	}
	else
	{
		re = ::mysql_errno(mysql);
		error = ::mysql_error(mysql);
	}

	return re;
}

/*获取查询得到的所有结果*/
std::vector<FieldsValue> QSqlDatabase::fieldValues()
{
	MYSQL_ROW row;
	unsigned int i;
	FieldsValue tmp;
	assert(mysql != NULL);
	if(records.size() > 0) return records;
	if(dataSet != NULL)
	{
		fieldsName();
		while(row = mysql_fetch_row(dataSet))
		{
			if(row != NULL)
			{
				for(i=0;i<fields.size();i++)
				{
					if ((char *)row[i] != NULL)
					{
						tmp[fields[i]] = (char *)row[i];
					}
					else
					{
						tmp[fields[i]] = "";
					}
				}

				records.push_back(tmp);
			}
		}
	}

	return records;
}

//返回错误信息
std::string QSqlDatabase::errorCode()
{
	return error;
}

//返回查询后的列值
std::vector<std::string> QSqlDatabase::fieldsName()
{
	/*
	field = mysql_fetch_fields(dataSet);
	然后通过field[i].name访问在此有错误，不知道为什么，可能是mysql的bug
	*/
	MYSQL_FIELD *field;
	assert(mysql != NULL);
	if(fields.size()>0) return fields;
	while(field = mysql_fetch_field(dataSet))
	{
		fields.push_back(field->name);
	}

	return fields;
}

//向数据库中插入一条数据
unsigned int QSqlDatabase::insert(std::string table, FieldsValue *data)
{
	FieldsValue::const_iterator iter;
	std::string sql;
	int flag=0;
	assert(mysql != NULL);
	assert(!table.empty());
	assert(data != NULL);
	for(iter = data->begin();iter!= data->end();iter++)
	{
		if(flag == 0)
		{
			sql = "insert into ";
			sql += table;
			sql += " set ";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
			flag++;
		}
		else
		{
			sql += ",";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
		}
	}

	return exec(sql);
}

//返回最后一个自动增量的值
unsigned long QSqlDatabase::getlastinsertid()
{
	return (unsigned long)::mysql_insert_id(mysql);
}

//返回一条sql语句影响的行数
unsigned long QSqlDatabase::numRowsAffected()
{
	return (unsigned long)num;
}

//根据条件修改一条数据
unsigned int QSqlDatabase::update(std::string table, FieldsValue *data, std::string condition)
{
	FieldsValue::const_iterator iter;
	std::string sql;
	int flag=0;
	assert(mysql != NULL);
	assert(!table.empty());
	assert(data != NULL);
	for(iter = data->begin();iter!= data->end();iter++)
	{
		if(flag == 0)
		{
			sql  = "update ";
			sql += table;
			sql += " set ";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
			flag++;
		}
		else
		{
			sql += ",";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
		}
	}

	if(!condition.empty())
	{
		sql += " where ";
		sql += condition;
	}

	return exec(sql);
}

//根据条件删除数据
unsigned int QSqlDatabase::remove(std::string table, std::string condition)
{
	std::string sql;
	assert(mysql != NULL);
	assert(!table.empty());
	sql = "delete from ";
	sql += table;
	if(!condition.empty())
	{
		sql += " where ";
		sql += condition;
	}

	return exec(sql);
}

bool QSqlDatabase::isOpen()
{
    if (mysql == NULL/* || mysql->host==NULL*/)
		return false;

    if (mysql_ping(mysql)==0)
    {
        return true;
    }
    else{
        return false;
    }
}

void QSqlDatabase::close()
{
	if(dataSet != NULL)
		::mysql_free_result(dataSet);
	fields.clear();
	error = "";
	record.clear();
	records.clear();
	::mysql_close(mysql);
	mysql = NULL;
}

void QSqlDatabase::assign(QSqlDatabase* target)
{
	target->mysql = this->mysql;
}
//

QSqlQuery::QSqlQuery(QSqlDatabase* pSqlDriver)
{
	m_pSqlDriver = new QSqlDatabase();
	if (pSqlDriver)
		pSqlDriver->assign(this->m_pSqlDriver);
}

QSqlQuery::QSqlQuery()
{
	m_pSqlDriver = new QSqlDatabase();
	QSqlDatabase::instance()->assign(this->m_pSqlDriver);
}

QSqlQuery::~QSqlQuery(void)
{

}

bool QSqlQuery::exec(QString sql)
{
    return this->exec(sql.toStdString());
}

bool QSqlQuery::exec(std::string sql)
{
	if (m_pSqlDriver != NULL)
	{	
		if (m_pSqlDriver->exec(sql)==0)
		{
			records = m_pSqlDriver->fieldValues();
            index = -1;
            return true;
            // if (records.size() > 0)
            // 	return true;
            // else
            // 	return false;
		}
		else
			return false;
	}

	return false;
}

QVariant QSqlQuery::value(const QString& fieldName)
{
    std::string result;
    this->stringValue(result, fieldName.toStdString());
    return QVariant::fromValue(QString::fromStdString(result));
}

bool QSqlQuery::stringValue(std::string &str, std::string fieldName)
{
	str = records[index][fieldName];
	return true;
}

bool QSqlQuery::intValue(long &lVal, std::string fieldName)
{
	const char* pszVal = records[index][fieldName].c_str();
	lVal = atoi(pszVal);
	return true;
}

bool QSqlQuery::floatValue(double &lVal, std::string fieldName)
{
	const char* pszVal = records[index][fieldName].c_str();
	lVal = atof(pszVal);
	return true;
}

long QSqlQuery::numRowsAffected()
{
	if (m_pSqlDriver != NULL)
	{
		return m_pSqlDriver->numRowsAffected();
	}

	return 0;
}

bool QSqlQuery::next()
{
	if (m_pSqlDriver == NULL) return false;

	if (++index >= (int)m_pSqlDriver->numRowsAffected()) return false;
	
	return true;
}

bool QSqlQuery::previous()
{
	if (m_pSqlDriver == NULL) return false;

	if (--index < 0) return false;

	return true;
}

bool QSqlQuery::first()
{
	if (m_pSqlDriver == NULL) return false;
	index= 0;
	if (index >= (int)m_pSqlDriver->numRowsAffected()) return false;

	return true;
}

bool QSqlQuery::last()
{
	if (m_pSqlDriver == NULL) return false;
	index= m_pSqlDriver->numRowsAffected() - 1;
	if (index >= (int)m_pSqlDriver->numRowsAffected()) return false;
	if (index < 0) return false;

	return true;
}

bool QSqlQuery::eof()
{
	if (m_pSqlDriver == NULL) return false;
	if (index == m_pSqlDriver->numRowsAffected()) return true;
	return false;
}

bool QSqlQuery::bof()
{
	if (m_pSqlDriver == NULL) return false;
	if (index == 0 && m_pSqlDriver->numRowsAffected()>0) return true;
	return false;
}

unsigned long QSqlQuery::lastInsertId()
{
    if (m_pSqlDriver == NULL) return 0;
    return m_pSqlDriver->getlastinsertid();
}

bool QSqlQuery::prepare(const QString& query)
{
    if (query.isEmpty())
        return false;

    sql = query;
    return true;
}

void QSqlQuery::bindValue(const QString& placeholder, const QVariant& val)
{
    QString value = QString("\"%1\"").arg(val.toString());
    sql.replace(placeholder, value);
}

bool QSqlQuery::exec()
{
    return exec(sql);
}

#endif