qt中使用sqlite存储数据

一、sqilte的安装

在Windows上安装SQLite:

请访问 SQLite 下载页面，从 Windows 区下载预编译的二进制文件。

您需要下载 sqlite-tools-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件。

创建文件夹 C:sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。

添加 C:sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果。

C:>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

二、sqilte的连接

/* 名称：ConnectMySqlite
 * 功能：确认数据库连接
 * 输入：_database需要连接的数据库
 * 返回：true连接成功，false连接失败
 */
bool MySql::ConnectMySqlite(QSqlDatabase &_database)
{
    if(QSqlDatabase::contains(mySqlConnectName))
        _database = QSqlDatabase::database(mySqlConnectName);
    else
        _database = QSqlDatabase::addDatabase("QSQLITE", mySqlConnectName);
    _database.setDatabaseName(mySqlName);

    if(!_database.open()) {
        qDebug()<<"ConnectMySqlite:SQLite connected failed.";
        return false;
    }

    return true;
}

三、qt中QSqlQuery使用

/* 名称：UpdateAlertTableValue
 * 功能：更新myAlertTable结构
 * 输入：无
 * 返回：0成功，-1数据库连接失败-2数据更新不全
 */
int MySql::UpdateAlertTableValue()
{
    QString sqlSelect = QString("select * from %1").arg(mySqlAlertTableName);
    int i=0;
    bool status = false;
    QSqlDatabase myDataBase;
    status = ConnectMySqlite(myDataBase);
    if(!status)
    {
        qDebug()<<"UpdateAlertTableValue>>ConnectMySqlite failed.";
        return -1;
    }

    if(!myDataBase.open())
        return -1;

    QSqlQuery query(myDataBase);
    query.prepare(sqlSelect);
    if(!query.exec()) {
        qDebug()<<"exec error:"<<query.lastError();
    } else {
        for(i =0;i<6 && query.next();++i)
        {
            myAlertTable[i].id = query.value(0).toInt();
            myAlertTable[i].alertName = query.value(1).toString();
            myAlertTable[i].alertValue= query.value(2).toInt();
            myAlertTable[i].alertInterval = query.value(3).toInt();
            myAlertTable[i].relayStatus0 = query.value(4).toInt();
            myAlertTable[i].relayStatus1 = query.value(5).toInt();
            myAlertTable[i].relayStatus2 = query.value(6).toInt();
            myAlertTable[i].relayStatus3 = query.value(7).toInt();
            myAlertTable[i].diffQuitiety = query.value(8).toInt();
        }
    }

    if(i!=6)
        return -2;
    return 0;
}

四、qt中QSqlQueryModel使用

/* 名称：ExportSqliteDataWithModel
 * 功能：导出为excel
 * 输入：无
 * 返回：无
 */
void MySql::ExportSqliteDataWithModel(QDateTime _startDate, QDateTime _endDate, QString filePath)
{
    QString path = filePath;
    QString sqlSelect = QString("select * from %1 where %1.sampleDate between '%2' and '%3' ")
            .arg(mySqlStormTableName)
            .arg(_startDate.toString("yyyy-MM-dd hh:mm:ss"))
            .arg(_endDate.toString("yyyy-MM-dd hh:mm:ss"));

    bool status = false;
    QSqlDatabase myDataBase;
    status = ConnectMySqlite(myDataBase);
    if(!status)
    {
        qDebug()<<"ExportSqliteDataWithModel>>ConnectMySqlite failed.";
        return;
    }

    if(!myDataBase.open())
        return;

    QSqlQueryModel *model = new QSqlQueryModel(this);
    QFile file(path);
    QTextStream out(&file);

    if(!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
        qDebug()<<file.errorString();
        return;
    } else {
        uint excelMaxrows = 1000000;
        model->setQuery(sqlSelect, myDataBase);

        while(model->canFetchMore())
        {
            model->fetchMore();
        }
        qDebug()<<"path:"<<path<<"filename:"<<file.fileName();

        uint tableRows = model->rowCount();
        uint64_t row = 0;
        for(uint i=1;row<tableRows;++i)
        {
            if(i!=1)                //如果数据超过1000000行，创建新文件
            {
                path = path.replace(".xls",QString("(%1).xls").arg(i));
                file.setFileName(path);
                out.setDevice(&file);
                if(!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
                    qDebug()<<file.errorString();
                    return;
                }
            }

            for(;row<tableRows && row<excelMaxrows*i;++row)
            {
                QModelIndex indexId = model->index(row,0);
                QModelIndex indexHighValue = model->index(row,1);
                QModelIndex indexLowValue = model->index(row,2);
                QModelIndex indexTempValue = model->index(row,3);
                QModelIndex indexSampleData = model->index(row,4);

                out<<indexId.data().toInt()<<"	"<<indexHighValue.data().toDouble()
                  <<"	"<<indexLowValue.data().toDouble()<<"	"<<indexTempValue.data().toDouble()
                 <<"	"<<indexSampleData.data().toString();
                out<<"
";
            }
            file.close();
        }
    }
}