QT-Mysql序列化读取

将Mysql数据库数据按照结构体方式读取，读取方法如下：

数据库表结构

读取方法

#pragma pack(push,1)//按字节对齐begin

struct pstruct{
    int a;
    double b;
    float c;
    char d[255];
};

#pragma pack(pop)//按字节对齐end

//使用方法
QList<TSerialisation> qList;
qList << (TSerialisation(Int, sizeof(int)))
      << (TSerialisation(Double, sizeof(double)))
      << (TSerialisation(Float, sizeof(float)))
      << (TSerialisation(String, 255));
int nQuerySize = 0;
char* pReturn = m_MySqlOper.SQL_QuerySerialisation("SELECT * FROM table1", nQuerySize, qList);
if(pReturn != nullptr && nQuerySize != 0)
{
     for(int i = 0; i < nQuerySize; i ++)
     {
         pstruct* p = (pstruct*)pReturn;
         qDebug() << p[i].a << p[i].b << p[i].c << p[i].d;
     }
}
delete [] pReturn;

 源码如下：

.Pro加入

QT +=  sql

1.mysqloper.h

/*************************************************************************
接口描述：Myqsl数据库类
拟制：
接口版本：V1.0
时间：20220922
说明：Mysql数据库操作类，GetInstance获取全局唯一实例，请勿多次创建实例
*************************************************************************/

#ifndef MYSQLOPER_H
#define MYSQLOPER_H
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QtCore>

enum EType {
    eBool,          //bool              显示是否
    eChar,          //char
    eByte,          //unsigned char
    eCharNumber,    //char              显示数字
    eByteNumber,    //byte              显示数字
    euShort,        //unsigned short
    eShort,         //short
    euInt,          //unsigned int
    eInt,           //int
    eFloat,         //float
    eDouble,        //double
    euLong,         //unsigned long
    eLong,          //long
    euLongLong,     //unsigned long long
    eLongLong,      //long long
    eString,        //char[]
    eDate,          //距离1970年世界时间s  显示日期yyyy-MM-dd
    eTime,          //距离1970年世界时间s  显示时间HH:mm:ss
    eTimeForDay,    //距离当天0点时间ms    显示时间HH:mm:ss 注意也是传入毫秒
    eTimes,         //距离1970年世界时间ms 显示时间HH:mm:ss:zzz
    eTimesForDay,   //距离当天0点时间ms    显示时间HH:mm:ss:zzz
    eDateTime,      //距离1970年世界时间s  显示日期时间yyyy-MM-dd HH:mm:ss
    eDateTimes,     //距离1970年世界时间ms 显示日期时间yyyy-MM-dd HH:mm:ss:zzz
};

struct TSerialisation
{
    EType eType;//序列化类型
    int nLen;//长度
    TSerialisation(EType eType, int nLen)
    {
        this->eType = eType;
        this->nLen = nLen;
    }
};

class MySqlOper
{
public:
    MySqlOper();
    ~MySqlOper();

private:
    QString m_csHost;//数据库地址
    QString m_csUser;//用户名
    QString m_csPasswd;//密码
    QString m_csDB;//数据库名
    int m_nPort;//数据端口
    QSqlDatabase* m_pSQL_Sock; //声明MySQL的句柄
    int m_nConnect;// -1失败 0等待连接 1正在连接 2连接成功

private:
    void SerialisationData(char*& pData, TSerialisation t, QString qValue);

public:
    void SQL_SetPro(QString csUser, QString csPasswd, QString csDB, QString csHost = "127.0.0.1", int nPort = 3306);//设置连接属性
    bool SQL_Connect();
    char* SQL_QuerySerialisation(QString csSql, int& nQuerySize/*结果集个数*/, QList<TSerialisation> tSerialisationList = QList<TSerialisation>());//返回序列化结果集
    QList<QStringList> SQL_Query(QString csSql);//查询返回结果集
    int SQL_Update(QString csSql);//适用于增删改,如果是插入单行则返回插入行的ID
    QSqlDatabase* SQL_DataBase();

public:
    static MySqlOper* GetInstance();//获取数据库唯一实例
    static void ReleaseInstance();//删除数据库唯一实例
};

#endif // MYSQLOPER_H

2.mysqloper.cpp

#include "mysqloper.h"
#include <QSqlError>
#include <QSqlField>
#include <QSqlQuery>
#include <QSqlRecord>

static QMutex mutexStatic;
static MySqlOper *pMySqlOperStatic = nullptr;

MySqlOper::MySqlOper()
{
    m_csDB = "127.0.0.1";
    m_nPort = 3306;
    m_pSQL_Sock = nullptr;
    m_nConnect = 0;
    m_pSQL_Sock = new QSqlDatabase(QSqlDatabase::addDatabase("QMYSQL"));
}

MySqlOper::~MySqlOper()
{
    m_pSQL_Sock->close();
    delete m_pSQL_Sock;
}

void MySqlOper::SQL_SetPro(QString csUser,
                           QString csPasswd,
                           QString csDB,
                           QString csHost /*= "127.0.0.1"*/,
                           int nPort /*= 3306*/)
{
    m_csUser = csUser;
    m_csPasswd = csPasswd;
    m_csDB = csDB;
    m_csHost = csHost;
    m_nPort = nPort;
}

//void MySqlOper::SQL_Init()
//{
//	mysql_init(&m_SQL);
//}

bool MySqlOper::SQL_Connect()
{
    m_pSQL_Sock->setHostName(m_csHost);
    m_pSQL_Sock->setDatabaseName(m_csDB);
    m_pSQL_Sock->setUserName(m_csUser);
    m_pSQL_Sock->setPassword(m_csPasswd);
    m_pSQL_Sock->setPort(m_nPort);
    if (!m_pSQL_Sock->open()) /*测试数据库是否链接成功*/
    {
        qDebug() << m_pSQL_Sock->lastError();
        m_nConnect = -1;
        return false;
    } else {
        m_nConnect = 2;
        qDebug() << "mysql connect success!" << endl;
    }
    return true;
}

char *MySqlOper::SQL_QuerySerialisation(QString csSql, int& nQuerySize, QList<TSerialisation> tSerialisationList)
{
    QMutexLocker locker(&mutexStatic);
    nQuerySize = 0;
    char *pRetrun = nullptr;
    if (m_nConnect == 2 && m_pSQL_Sock != nullptr && m_pSQL_Sock->isOpen()) {
        QSqlQuery pQSqlQuery = m_pSQL_Sock->exec(csSql);
        if (tSerialisationList.length() == 0) //不需要结果
        {
            return nullptr;
        }
        int nSize = pQSqlQuery.size(); //结果集个数;
        int nSerialisationLength = 0;  //结果序列化长度
        foreach (TSerialisation t, tSerialisationList) {
            nSerialisationLength += t.nLen;
        }
        QSqlRecord rec = pQSqlQuery.record();
        if (rec.count() != tSerialisationList.size()) //列数和序列化数目不一致
        {
            return nullptr;
        }
        pRetrun = new char[nSize * nSerialisationLength]; //分配空间
        memset(pRetrun, 0, nSize * nSerialisationLength);
        nQuerySize = nSize; //个数
        char *pData = pRetrun;
        while (pQSqlQuery.next()) {
            rec = pQSqlQuery.record();
            if (rec.count() == tSerialisationList.size()) //列数和序列化数目不一致
            {
                for (int i = 0; i < rec.count(); i++) {
                    if (pQSqlQuery.value(i).type() == QVariant::DateTime) {
                        QDateTime qDateTime = pQSqlQuery.value(i).toDateTime();
                        SerialisationData(pData,
                                          tSerialisationList[i],
                                          qDateTime.toString("yyyy/MM/dd_HH:mm:ss:zzz"));
                    } else
                        SerialisationData(pData,
                                          tSerialisationList[i],
                                          pQSqlQuery.value(i).toString());
                }
            } else {
                delete[] pRetrun;
                nQuerySize = 0;
                return nullptr;
            }
        }
    } else {
        m_nConnect = -1;
        SQL_Connect();
    }
    return pRetrun;
}

QList<QStringList> MySqlOper::SQL_Query(QString csSql)
{
    QMutexLocker locker(&mutexStatic);
    QList<QStringList> qList;
    if (m_nConnect == 2) {
        if (m_pSQL_Sock != nullptr && m_pSQL_Sock->isOpen()) {
            QSqlQuery pQSqlQuery = m_pSQL_Sock->exec(csSql);
            while (pQSqlQuery.next()) {
                QStringList qs;
                QSqlRecord rec = pQSqlQuery.record();
                for (int i = 0; i < rec.count(); i++) {
                    qs.append(pQSqlQuery.value(i).toString());
                }
                qList.append(qs);
            }
        } else {
            m_nConnect = -1;
            SQL_Connect();
        }
    }
    return qList;
}

int MySqlOper::SQL_Update(QString csSql)
{
    QMutexLocker locker(&mutexStatic);
    QList<QStringList> qList;
    int nLastID = -1;
    if (m_nConnect == 2) {
        if (m_pSQL_Sock != nullptr && m_pSQL_Sock->isOpen()) {
            QSqlQuery pQSqlQuery = m_pSQL_Sock->exec(csSql);
            if(pQSqlQuery.lastInsertId().isValid())
                nLastID = pQSqlQuery.lastInsertId().toInt();
        } else {
            m_nConnect = -1;
            SQL_Connect();
        }
    }
    return nLastID;
}

QSqlDatabase *MySqlOper::SQL_DataBase()
{
    return m_pSQL_Sock;
}

void MySqlOper::SerialisationData(char *&pData, TSerialisation t, QString qValue)
{
    if (t.eType == eBool) //bool
    {
        pData[0] = qValue.toInt();                 //字符串0,1转数值0,1
    } else if (t.eType == eChar || t.eType == eByte) //字节
    {
        pData[0] = Qstr2Char(qValue)[0];
    } else if (t.eType == euShort) //uShort
    {
        ((ushort *) pData)[0] = qValue.toUShort();
    } else if (t.eType == eShort) //Short
    {
        ((short *) pData)[0] = qValue.toShort();
    } else if (t.eType == euInt) //uInt
    {
        ((uint *) pData)[0] = qValue.toUInt();
    } else if (t.eType == eInt) //Int
    {
        ((int *) pData)[0] = qValue.toInt();
    } else if (t.eType == eFloat) //FLoat
    {
        ((float *) pData)[0] = qValue.toFloat();
    } else if (t.eType == eDouble) //Double
    {
        ((double *) pData)[0] = qValue.toDouble();
    } else if (t.eType == euLong) //uLong
    {
        ((ulong *) pData)[0] = qValue.toULong();
    } else if (t.eType == eLong) //Long
    {
        ((ulong *) pData)[0] = qValue.toULong();
    } else if (t.eType == euLongLong) //uLongLong
    {
        ((qulonglong *) pData)[0] = qValue.toULongLong();
    } else if (t.eType == eLongLong) //LongLong
    {
        ((qlonglong *) pData)[0] = qValue.toLongLong();
    } else if (t.eType == eDateTime) //DateTime
    {
        QDateTime qDataTime = QDateTime::fromString(qValue, "yyyy/MM/dd_HH:mm:ss:zzz");
        ((qint64 *) pData)[0] = qDataTime.toSecsSinceEpoch();
    } else if (t.eType == eDateTimes) //DateTimes
    {
        QDateTime qDataTime = QDateTime::fromString(qValue, "yyyy/MM/dd_HH:mm:ss:zzz");
        ((qint64 *) pData)[0] = qDataTime.toMSecsSinceEpoch();
    } else if (t.eType == eString) //字符串
    {
        qstrcpy(pData, Qstr2Char(qValue));
    }
    pData = pData + t.nLen;
}

MySqlOper *MySqlOper::GetInstance()
{
    QMutexLocker locker(&mutexStatic);
    if (pMySqlOperStatic == nullptr)
        pMySqlOperStatic = new MySqlOper;
    return pMySqlOperStatic;
}

void MySqlOper::ReleaseInstance()
{
    QMutexLocker locker(&mutexStatic);
    if (pMySqlOperStatic != nullptr)
        delete pMySqlOperStatic;
    pMySqlOperStatic = nullptr;
}