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的连接
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 名称:ConnectMySqlite 2 * 功能:确认数据库连接 3 * 输入:_database需要连接的数据库 4 * 返回:true连接成功,false连接失败 5 */ 6 bool MySql::ConnectMySqlite(QSqlDatabase &_database) 7 { 8 if(QSqlDatabase::contains(mySqlConnectName)) 9 _database = QSqlDatabase::database(mySqlConnectName); 10 else 11 _database = QSqlDatabase::addDatabase("QSQLITE", mySqlConnectName); 12 _database.setDatabaseName(mySqlName); 13 14 if(!_database.open()) { 15 qDebug()<<"ConnectMySqlite:SQLite connected failed."; 16 return false; 17 } 18 19 return true; 20 }
三、qt中QSqlQuery使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 名称:UpdateAlertTableValue 2 * 功能:更新myAlertTable结构 3 * 输入:无 4 * 返回:0成功,-1数据库连接失败-2数据更新不全 5 */ 6 int MySql::UpdateAlertTableValue() 7 { 8 QString sqlSelect = QString("select * from %1").arg(mySqlAlertTableName); 9 int i=0; 10 bool status = false; 11 QSqlDatabase myDataBase; 12 status = ConnectMySqlite(myDataBase); 13 if(!status) 14 { 15 qDebug()<<"UpdateAlertTableValue>>ConnectMySqlite failed."; 16 return -1; 17 } 18 19 if(!myDataBase.open()) 20 return -1; 21 22 QSqlQuery query(myDataBase); 23 query.prepare(sqlSelect); 24 if(!query.exec()) { 25 qDebug()<<"exec error:"<<query.lastError(); 26 } else { 27 for(i =0;i<6 && query.next();++i) 28 { 29 myAlertTable[i].id = query.value(0).toInt(); 30 myAlertTable[i].alertName = query.value(1).toString(); 31 myAlertTable[i].alertValue= query.value(2).toInt(); 32 myAlertTable[i].alertInterval = query.value(3).toInt(); 33 myAlertTable[i].relayStatus0 = query.value(4).toInt(); 34 myAlertTable[i].relayStatus1 = query.value(5).toInt(); 35 myAlertTable[i].relayStatus2 = query.value(6).toInt(); 36 myAlertTable[i].relayStatus3 = query.value(7).toInt(); 37 myAlertTable[i].diffQuitiety = query.value(8).toInt(); 38 } 39 } 40 41 if(i!=6) 42 return -2; 43 return 0; 44 }
四、qt中QSqlQueryModel使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 名称:ExportSqliteDataWithModel 2 * 功能:导出为excel 3 * 输入:无 4 * 返回:无 5 */ 6 void MySql::ExportSqliteDataWithModel(QDateTime _startDate, QDateTime _endDate, QString filePath) 7 { 8 QString path = filePath; 9 QString sqlSelect = QString("select * from %1 where %1.sampleDate between '%2' and '%3' ") 10 .arg(mySqlStormTableName) 11 .arg(_startDate.toString("yyyy-MM-dd hh:mm:ss")) 12 .arg(_endDate.toString("yyyy-MM-dd hh:mm:ss")); 13 14 bool status = false; 15 QSqlDatabase myDataBase; 16 status = ConnectMySqlite(myDataBase); 17 if(!status) 18 { 19 qDebug()<<"ExportSqliteDataWithModel>>ConnectMySqlite failed."; 20 return; 21 } 22 23 if(!myDataBase.open()) 24 return; 25 26 QSqlQueryModel *model = new QSqlQueryModel(this); 27 QFile file(path); 28 QTextStream out(&file); 29 30 if(!file.open(QIODevice::WriteOnly | QIODevice::Text)) { 31 qDebug()<<file.errorString(); 32 return; 33 } else { 34 uint excelMaxrows = 1000000; 35 model->setQuery(sqlSelect, myDataBase); 36 37 while(model->canFetchMore()) 38 { 39 model->fetchMore(); 40 } 41 qDebug()<<"path:"<<path<<"filename:"<<file.fileName(); 42 43 uint tableRows = model->rowCount(); 44 uint64_t row = 0; 45 for(uint i=1;row<tableRows;++i) 46 { 47 if(i!=1) //如果数据超过1000000行,创建新文件 48 { 49 path = path.replace(".xls",QString("(%1).xls").arg(i)); 50 file.setFileName(path); 51 out.setDevice(&file); 52 if(!file.open(QIODevice::WriteOnly | QIODevice::Text)) { 53 qDebug()<<file.errorString(); 54 return; 55 } 56 } 57 58 for(;row<tableRows && row<excelMaxrows*i;++row) 59 { 60 QModelIndex indexId = model->index(row,0); 61 QModelIndex indexHighValue = model->index(row,1); 62 QModelIndex indexLowValue = model->index(row,2); 63 QModelIndex indexTempValue = model->index(row,3); 64 QModelIndex indexSampleData = model->index(row,4); 65 66 out<<indexId.data().toInt()<<"\t"<<indexHighValue.data().toDouble() 67 <<"\t"<<indexLowValue.data().toDouble()<<"\t"<<indexTempValue.data().toDouble() 68 <<"\t"<<indexSampleData.data().toString(); 69 out<<"\n"; 70 } 71 file.close(); 72 } 73 } 74 }
无欲速,无见小利。欲速,则不达;见小利,则大事不成。