QT5.6 连接操作 Mysql 5.7 方式工具整理

初次使用MYSQL时会出现：QMYSQL driver not loaded无法连接问题，此时下载相关dll（https://download.youkuaiyun.com/download/Hello___Sunshine/12000677）进行使用即可。

正常使用后开始下一步。

首先在.pro文件中添加配置：

QT       += sql

创建头文件：

#ifndef YMMYSQLAPI_H
#define YMMYSQLAPI_H

#include <QStringList>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlQueryModel>
#include <QTime>

#include "dbutils_global.h"

class DBUTILSSHARED_EXPORT YmMysqlApi
{
public:
    YmMysqlApi();
    ~YmMysqlApi();

//    void setDriverName(QString value) {mDriverName = value;}
    void setDbName(QString value) {mDbName = value;}
    void setServer(QString value) {mServer = value;}  //"127.0.0.1"
    void setUserName(QString value) {mUserName = value;}
    void setPassWord(QString value) {mPassWord = value;}
    void setPort(int value) {mPort = value;}

    QString getDriverName() {return mDriverName;}
    QString getDbName() {return mDbName;}
    QString getServer() {return mServer;}
    QString getUserName() {return mUserName;}
    QString getPassWord() {return mPassWord;}
    int getPort() {return mPort;}
    QString getDbConnectName();
    // ///////////////////////////////////////////////////////////////
    QSqlDatabase *getDbHandle();
    int dbConnect();
    QStringList getTabsList();
    int getTabsCount();
    QStringList getAllFieldList(QString tabName);
    int getCurrTabRows(QString tabName);
    int getCurrTabCols(QString tabName);
    QList<QList<QString> > realAll(QString tabName);
    QString query(QString tabName, int row, int col);
    QStringList query(QString tabName, QString field, int startIndex, int num); //获取从某行开始的几行数据
    bool InsertDataUpdate(QStringList ColName, QStringList ColData, QString tablename);//添加数据
    bool DeleteDataUpdate(QString FieldName, QString FieldValue, QString tablename);//删除数据
    bool ModifyDataUpdate(QStringList ColName, QStringList ModifyData,
                          QString FieldName,QString value, QString tablename);//修改数据
    int dbDisConnect();

private:
    QString mDriverName;
    QString mDbName;
    QString mServer;   //服务器ip
    QString mUserName;
    QString mPassWord;
    int mPort;
    QString mCurrTime;

    QString mDbConnectName;

    QSqlDatabase mDbHandle;
};

#endif // YMMYSQLAPI_H

创建对应的源文件：

#include <QDebug>

#include "ymmysqlapi.h"

YmMysqlApi::YmMysqlApi()
{

}

YmMysqlApi::~YmMysqlApi()
{

}

QSqlDatabase* YmMysqlApi::getDbHandle()
{
    return &mDbHandle;
}

QString YmMysqlApi::getDbConnectName()  //标志
{
    mDbConnectName.clear();
    mDbConnectName.append("TYPE_MYSQL_");
    mDbConnectName.append(mCurrTime);
    return mDbConnectName;
}

int YmMysqlApi::dbConnect()
{
    QSqlError err;
    mDriverName = "QMYSQL";
    mCurrTime = QTime::currentTime().toString();
    mDbHandle = QSqlDatabase::addDatabase(mDriverName, getDbConnectName());
    mDbHandle.setDatabaseName(mDbName);
    mDbHandle.setHostName(mServer);
    mDbHandle.setPort(mPort);
    mDbHandle.setUserName(mUserName);
    mDbHandle.setPassword(mPassWord);

    if(!mDbHandle.open()) {
        mDbHandle = QSqlDatabase::database(mDbConnectName);
        err = mDbHandle.lastError();
        mDbHandle = QSqlDatabase();
        qDebug() << "mysql open error" << err.text();
        QSqlDatabase::removeDatabase(mDbConnectName);
        return -1;
    }
    return 0;
}

QStringList YmMysqlApi::getTabsList()
{
    return mDbHandle.tables();
}

int YmMysqlApi::getTabsCount()
{
    return mDbHandle.tables().count();
}

QStringList YmMysqlApi::getAllFieldList(QString tabName)
{
    QStringList fieldList;
    fieldList.clear();
    QSqlQuery query = QSqlQuery(mDbHandle);
    QString cmd = QString("select COLUMN_NAME from information_schema.COLUMNS where table_name = '%1';").arg(tabName);

    if(!query.exec(cmd)) {
        QSqlError lastError = query.lastError();
        qDebug() << lastError.driverText();
        return fieldList;
    }
    QSqlRecord rec = query.record();
    while(query.next())
    {
        for(int i=0; i<rec.count(); i++) {
            fieldList.append(query.value(i).toString());
        }
    }
    return fieldList;
}


int YmMysqlApi::getCurrTabRows(QString tabName)  //获取行数
{
    int rows = 0;
    QSqlQuery query = QSqlQuery(mDbHandle);
    QString cmd = QString("select count(*) from %1;").arg(tabName);
    if(!query.exec(cmd)) {
        QSqlError lastError = query.lastError();
        qDebug() << lastError.driverText() << lastError.text();
        return -1;
    }

    QSqlRecord rec = query.record();
    while(query.next())
    {
        if(rec.count() > 0) {
            rows = query.value(0).toInt();
        }
        else {
            rows = -1;
        }
    }
    return rows;
}

int YmMysqlApi::getCurrTabCols(QString tabName)  //获取列数（字段数）
{
    int cols = 0;
    QSqlQuery query = QSqlQuery(mDbHandle);
    QString cmd = QString("select count(*) from information_schema.COLUMNS where table_name='%1';").arg(tabName);
    if(!query.exec(cmd)) {
        QSqlError lastError = query.lastError();
        qDebug() << lastError.driverText() << lastError.text();
        return -1;
    }

    QSqlRecord rec = query.record();
    while(query.next())
    {
        if(rec.count() > 0) {
            cols = query.value(0).toInt();
        }
        else {
            cols = -1;
        }
    }
    return cols;
}

QList<QList<QString> > YmMysqlApi::realAll(QString tabName)
{
    QList<QList<QString> > datas;
    QStringList rowDatas;
    datas.clear();

    QSqlQuery query = QSqlQuery(mDbHandle);
    QString cmd = QString("select * from %1").arg(tabName);

    if(!query.exec(cmd)) {
        QSqlError lastError = query.lastError();
        qDebug() << lastError.driverText() << lastError.text();
        return datas;
    }

    QSqlRecord rec = query.record();  //query读的是整行
    int cols = rec.count();
    while(query.next())
    {
        rowDatas.clear();
        for(int i=0; i<cols; i++) {
            rowDatas.append(query.value(i).toString());
        }
        datas.append(rowDatas);
    }
    return datas;
}

QString YmMysqlApi::query(QString tabName, int row, int col)
{
    if(tabName.isEmpty()) {
        return NULL;
    }
    QStringList fieldList = getAllFieldList(tabName);
    QString field = fieldList.at(col);
    QStringList resp = query(tabName, field, row, 1);
    if(resp.count() <= 0) {
        return NULL;
    }
    else {
        return resp.first();
    }
}

QStringList YmMysqlApi::query(QString tabName, QString field, int startIndex, int num)
{
    QStringList dataList;
    dataList.clear();
    if(tabName.isEmpty() || field.isEmpty() || startIndex < 0 || num < 0) {
        qDebug("invalid params");
        return dataList;
    }

    QString cmd = QString("SELECT %1 FROM %2 LIMIT %3,%4").arg(field, tabName, QString::number(startIndex), QString::number(num));
    QSqlQueryModel *sqlQueryModel = new QSqlQueryModel;
    sqlQueryModel->setQuery(cmd, mDbHandle);
    QSqlQuery query = sqlQueryModel->query();
    //query.exec(cmd);
    if(!query.exec(cmd)) {
        QSqlError lastError = query.lastError();
        qDebug() << lastError.driverText();
        return dataList;
    }
    QSqlRecord rec = query.record();
    if(rec.count() <= 0) {
        return dataList;
    }

    while(query.next())
    {
        rec = query.record();
        dataList.append(query.value(0).toString());
    }
    return dataList;
}
/**
 * @brief YmMysqlApi::InsertDataUpdate 插入数据
 * @param ColName 列名
 * @param ColData 数据
 * @param tablename 表名
 * @return
 */
bool YmMysqlApi::InsertDataUpdate(QStringList ColName, QStringList ColData, QString tablename)
{
    if(ColName.isEmpty() || ColData.isEmpty() || tablename.isEmpty()){
        return false;
    }

    QSqlQuery sqlquery=QSqlQuery(mDbHandle);
    QString sqlset;
    for(int i = 0; i < ColName.count(); i++)
        sqlset += "" + ColName.at(i) + " ='" + ColData.at(i) + "',";
    sqlset = sqlset.left(sqlset.length() - 1);
    QString sq=QString("insert %1 set "+sqlset+" ").arg(tablename);
    return sqlquery.exec(sq);
}

/**
 * @brief YmMysqlApi::DeleteDataUpdate 删除数据
 * @param FieldName 字段名
 * @param FieldValue 字段值
 * @param tablename 表名
 * @return
 */
bool YmMysqlApi::DeleteDataUpdate(
        QString FieldName, QString FieldValue, QString tablename)
{
    if(FieldName.isEmpty() || FieldValue.isEmpty() || tablename.isEmpty()){
        return false;
    }

    QSqlQuery sqlquery=QSqlQuery(mDbHandle);
    QString sqlset = "delete from %3 where %1 = '%2'" ;
    QString sq= sqlset.arg(FieldName).arg(FieldValue).arg(tablename);
    qDebug()<<"删除语句:"<<sq;
    return sqlquery.exec(sq);
}

/**
 * @brief YmMysqlApi::ModifyDataUpdate 修改数据
 * @param ColName 修改列名
 * @param ModifyData 修改的内容
 * @param FieldName 字段名
 * @param value 字段值
 * @param tablename 表名
 * @return
 */
bool YmMysqlApi::ModifyDataUpdate(QStringList ColName, QStringList ModifyData,
                                      QString FieldName,QString value, QString tablename)
{
    if(ColName.isEmpty() || ModifyData.isEmpty() || FieldName.isEmpty()
            || value.isEmpty() || tablename.isEmpty()){
        return false;
    }

    QString sqlset;
    QSqlQuery sqlquery = QSqlQuery(mDbHandle);
    for(int i = 0; i < ColName.count(); i++)
        sqlset+=""+ColName.at(i)+" ='"+ModifyData.at(i)+"',";
    sqlset = sqlset.left(sqlset.length() - 1);
    QString sq = QString("update %2 set " + sqlset + " where "+FieldName+"='%1'").arg(value).arg(tablename);
    qDebug()<<"query data:"<<sq;
    return sqlquery.exec(sq);
}

int YmMysqlApi::dbDisConnect()
{
    if(mDbHandle.isOpen()) {
        mDbHandle.close();
    }
}

操作实例：

    YmMysqlApi *ymMysqlApi;
    ymMysqlApi = new YmMysqlApi;
    ymMysqlApi->setPort(3306);
    ymMysqlApi->setServer("127.0.0.1");
    ymMysqlApi->setUserName("root");
    ymMysqlApi->setPassWord("123456");
    ymMysqlApi->setDbName("mysql");
    ymMysqlApi->dbConnect();

    QStringList list =  ymMysqlApi->getAllFieldList("pctool");
    qDebug()<<"getAllFieldList："<<list;
    QStringList data = ymMysqlApi->query("pctool","*",0,3);
    qDebug()<<"query："<<data;

    QList<QList<QString> > datas = ymMysqlApi->realAll("pctool");
    qDebug()<<"realAll"<<datas;

    QSqlDatabase *mDbHandle =  ymMysqlApi->getDbHandle();
    QStringList t1 = {"test","mark"};
    QStringList t2 = {"123","321"};
     //qDebug()<<"insertProductnum："<<ymMysqlApi->InsertDataUpdate(t1,t2,"pctool");
     qDebug()<<"DeleteDataUpdate"<<ymMysqlApi->DeleteDataUpdate("test","c","pctool");

    ymMysqlApi->dbDisConnect();