SQLite3的使用
来源：互联网 发布：苹果cms api 编辑：程序博客网 时间：2024/06/05 10:21
转自：http://blog.csdn.net/mingxia_sui/article/details/8502179
1.下载sqlite3相关文件sqlite3.dll、sqlite3.h(可从http://download.csdn.net/detail/mingxia_sui/5249070下载)，添加到工程的根目录下。
2. QT += sql
LIBS += sqlite3.dllQt的.pro文件中要加上以上两句。这样就可以调用sqlite3中的数据库操作函数了。3.以下的例子是一个已经封装好的类，可以直接调用哦！******************************************************************************************************************************************************************************.h文件代码：#ifndef SKMONITORSQL_H#define SKMONITORSQL_H#include <QObject>#include <QDateTime>#include "sqlite3.h"#include <QStringList>class QSKMonitorSQL : public QObject{ Q_OBJECTpublic: explicit QSKMonitorSQL(QString sFileName, QObject *parent = 0); bool fn_connectSQL(); bool fn_closeSQL(); void fn_InitTable(QString sTableName, QStringList sNameList, QStringList sType); void fn_InitTableForBase(QString sTableName, QStringList sNameList, QStringList sType); void fn_InsertData(QString sTableName, QStringList sDataList, QDateTime dt); void fn_InsertDataForBase(QString sTableName, QStringList sDataList); int fn_UpdateData(QString sTableName, QStringList sDataList,int index); void fn_DeleteData(QString sTableName); int fn_QueryDataByIndex(QString sTableName, int iStart,int iNumber,QVector<QStringList> *pData); int fn_QueryDataByTime(QString sTableName, QDateTime dtStart,QDateTime dtEnd,QVector<QStringList> *pData); int fn_QueryDataByValue(QString sTableName, int nType, QString sFrontV, QString sBackV, QVector<QStringList> *pData); int fn_QueryAllData(QString sTableName, QVector<QStringList> *pData); int fn_RecordSize(QString sTableName); uint fn_GetMinRowID(QString sTableName); bool fn_RecordSizeByTime(QString sTableName, QDateTime dtStart, QDateTime dtEnd, int *pRows, int *pMaxID,int *pMinID); QString fn_UnicodeToUTF8(QString str); void fn_StartBegin(); void fn_StartCommit(); bool m_bDBIsOk; int m_nSetMaxRecordNum; bool m_bSetRecordFlag; void fn_SetMaxRowId(int nMaxRows);public: bool m_bStartBegin; bool m_bStartCommit;signals:public slots:private: QString m_sDB; QString m_sInsertT1; QString m_sUpdateT1; QString m_sTable; QStringList m_sTitleList; //列名 int m_nColumn; sqlite3 *m_pdb; //数据库 char *m_pzErrMsg; //出错信息 char *m_perrmsg; //出错信息 char **m_ppazResult;};#endif // SKMONITORSQL_H***************************************************************************************.cpp文件：***************************************************************************************#include "SKMonitorSQL.h"#include <QTextCodec>#include <QVector>QSKMonitorSQL::QSKMonitorSQL(QString sFileName, QObject *parent) : QObject(parent){ m_pdb = NULL; m_nSetMaxRecordNum = 0; m_bSetRecordFlag = false; //编码转换 m_sDB = fn_UnicodeToUTF8(sFileName);//编码转换 m_bStartBegin = false; //初始化没有插入BEGIN命令 m_bStartCommit = false; //初始化没有插入COMMIT命令}/********************************************************************************************* Func: 连接到指定数据库 input: null ouput:null ********************************************************************************************/bool QSKMonitorSQL::fn_connectSQL(){ if( (sqlite3_open(m_sDB.toAscii().data(), &m_pdb)) != 0 ) //打开指定的数据库文件,如果不存在将创建一个同名的数据库文件 { m_bDBIsOk = false; return false; } m_bDBIsOk = true; return true;}/********************************************************************************************* Func: 关闭指定数据库 input: null ouput:null ********************************************************************************************/bool QSKMonitorSQL::fn_closeSQL(){ if (m_pdb!=NULL) { sqlite3_close(m_pdb); } return false;}/********************************************************************************************* Func: 运行BEGIN命令 input: null ouput:null ********************************************************************************************/void QSKMonitorSQL::fn_StartBegin(){ if (m_bStartBegin == false) { if (m_pdb != NULL) { QString stmp; stmp = "begin;"; stmp = fn_UnicodeToUTF8(stmp); //编码转换 sqlite3_exec( m_pdb , stmp.toAscii().data() , 0 , 0 , &m_pzErrMsg ); m_bStartBegin = true; m_bStartCommit = false; } }}/********************************************************************************************* Func: 运行commit命令 input: null ouput:null ********************************************************************************************/void QSKMonitorSQL::fn_StartCommit(){ if (m_bStartCommit == true) //提交，存入磁盘 { if (m_pdb != NULL) { QString stmp; stmp = "commit;"; stmp = fn_UnicodeToUTF8(stmp); //编码转换 sqlite3_exec( m_pdb , stmp.toAscii().data() , 0 , 0 , &m_pzErrMsg ); m_bStartBegin = false; m_bStartCommit = false; } }}/********************************************************************************************* Func: 初始化数据表 input: 表名，表列名 ouput:null ********************************************************************************************/void QSKMonitorSQL::fn_InitTable(QString sTableName, QStringList sNameList, QStringList sType){ fn_StartBegin(); if (m_pdb!=NULL) { QString stmp; stmp = "create table " + sTableName + "(logtime datetime"; for (int i=0;i<sNameList.size();i++) { stmp = stmp + "," + sNameList.at(i) + " " + sType.at(i); } stmp = stmp + ")"; stmp = stmp + ";"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 sqlite3_exec(m_pdb, stmp.toAscii().data(), NULL, NULL, &m_pzErrMsg); m_sInsertT1 = "insert into "; m_sInsertT1 = m_sInsertT1 + sTableName + " values("; //记录插入语句部分str m_sTable = sTableName; //表的名字 m_nColumn = sNameList.size() + 1; //列总数=记录列数+时间 m_sUpdateT1 = "update " + sTableName; //记录修改语句 m_sUpdateT1 = m_sUpdateT1 + " set "; m_sTitleList = sNameList; //列名称 } fn_StartCommit();}/********************************************************************************************* Func: 初始化数据表 input: 表名，表列名 ouput:null ********************************************************************************************/void QSKMonitorSQL::fn_InitTableForBase(QString sTableName, QStringList sNameList, QStringList sType){ fn_StartBegin(); if (m_pdb!=NULL) { QString stmp; stmp = "create table " + sTableName + "("; for (int i=0;i<sNameList.size();i++) { if (i != 0) { stmp += ","; } stmp = stmp + sNameList.at(i) + " " + sType.at(i); } stmp = stmp + ")"; stmp = stmp + ";"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 sqlite3_exec(m_pdb, stmp.toAscii().data(), NULL, NULL, &m_pzErrMsg); m_sInsertT1 = "insert into "; m_sInsertT1 = m_sInsertT1 + sTableName + " values("; //记录插入语句部分str m_sTable = sTableName; //表的名字 m_nColumn = sNameList.size() + 1; //列总数=记录列数+时间 m_sUpdateT1 = "update " + sTableName; //记录修改语句 m_sUpdateT1 = m_sUpdateT1 + " set "; m_sTitleList = sNameList; //列名称 } fn_StartCommit();}/********************************************************************************************* Func: 增加数据到数据库 input: null ouput:null ********************************************************************************************/void QSKMonitorSQL::fn_InsertData(QString sTableName, QStringList sDataList, QDateTime dt){ fn_StartBegin(); if (m_pdb!=NULL) { if (m_bSetRecordFlag)//设置了最大行数 { int nMaxRecordSize = fn_RecordSize(sTableName);//当前最大记录数 if (nMaxRecordSize < 0) return; if (nMaxRecordSize >= m_nSetMaxRecordNum)//超出限制，删掉开始项 { QString stmp; stmp = "delete from "; stmp = stmp + sTableName; stmp = stmp + " where rowid <= (select min(rowid) from "; stmp = stmp + sTableName + ");"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 sqlite3_exec(m_pdb, stmp.toAscii().data(), NULL, NULL, &m_pzErrMsg); } QString stmp; stmp = "insert into "; stmp = stmp + sTableName + " values("; QString sdatetime = dt.toString("yyyy-MM-dd hh:mm:ss"); stmp = stmp + "'"; stmp = stmp + sdatetime; stmp = stmp + "'"; for (int i=0;i<sDataList.size();i++) { stmp = stmp + ",'"; stmp = stmp + sDataList.at(i) + "'"; } stmp = stmp + ")"; stmp = stmp + ";"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 sqlite3_exec(m_pdb, stmp.toAscii().data(), NULL, NULL, &m_pzErrMsg ); } else { QString stmp; stmp = "insert into "; stmp = stmp + sTableName + " values("; QString sdatetime = dt.toString("yyyy-MM-dd hh:mm:ss"); stmp = stmp + "'"; stmp = stmp + sdatetime; stmp = stmp + "'"; for (int i=0;i<sDataList.size();i++) { stmp = stmp + ",'"; stmp = stmp + sDataList.at(i) + "'"; } stmp = stmp + ")"; stmp = stmp + ";"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 sqlite3_exec(m_pdb, stmp.toAscii().data(), NULL, NULL, &m_pzErrMsg ); } } fn_StartCommit();}/********************************************************************************************* Func: 增加数据到数据库 input: null ouput:null ********************************************************************************************/void QSKMonitorSQL::fn_InsertDataForBase(QString sTableName, QStringList sDataList){ fn_StartBegin(); if (m_pdb!=NULL) { QString stmp; stmp.clear(); stmp = "insert into "; stmp = stmp + sTableName + " values("; for (int i=0; i<sDataList.size(); i++) { if (i != 0) { stmp = stmp + ","; } stmp = stmp + "'"; stmp = stmp + sDataList.at(i) + "'"; } stmp = stmp + ")"; stmp = stmp + ";"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 sqlite3_exec(m_pdb ,stmp.toAscii().data(), NULL, NULL, &m_pzErrMsg); } fn_StartCommit();}/********************************************************************************************* Func: 修改数据库数据 input: null ouput:null ********************************************************************************************/int QSKMonitorSQL::fn_UpdateData(QString sTableName, QStringList sDataList, int index){ int nRet = -1; fn_StartBegin(); if (m_pdb!=NULL) { QString stmp; int isize; stmp = "update " + sTableName; //修改语句 stmp = stmp + " set "; isize = sDataList.size(); for (int i=0;i<isize;i++) { stmp = stmp + m_sTitleList.at(i); stmp = stmp + " = "; stmp = stmp + "'"; stmp = stmp + sDataList.at(i); stmp = stmp + "'"; if (i != isize - 1) //不是最后一个时 { stmp = stmp + ","; } } stmp = stmp + " where rowid = (select MIN(rowid) + "; stmp = stmp + QString::number(index); stmp = stmp + " from "; stmp = stmp + m_sTable; stmp = stmp + ")"; stmp = stmp + ";"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 nRet = sqlite3_exec(m_pdb, stmp.toAscii().data(), NULL, NULL, &m_pzErrMsg ); } fn_StartCommit(); return nRet;}/********************************************************************************************* Func: 删除数据 input: null ouput:null ********************************************************************************************/void QSKMonitorSQL::fn_DeleteData(QString sTableName){ fn_StartBegin(); if (m_pdb!=NULL) { QString stmp; stmp = "delete from "; stmp = stmp + sTableName; stmp = stmp + ";"; stmp = fn_UnicodeToUTF8(stmp);//编码转换 sqlite3_exec(m_pdb , stmp.toAscii().data() , NULL , NULL , &m_pzErrMsg ); } fn_StartCommit();}/********************************************************************************************* Func: 上电时获得最小的rowid序号 input: null ouput:null ********************************************************************************************/uint QSKMonitorSQL::fn_GetMinRowID(QString sTableName){ if (m_pdb!=NULL) { QString stmp; stmp = "select min(rowid) from "; stmp = stmp + sTableName; stmp = stmp + ";"; int nRow; int nColumn; stmp = fn_UnicodeToUTF8(stmp);//编码转换 int result = sqlite3_get_table(m_pdb, stmp.toAscii().data(), &m_ppazResult, &nRow, &nColumn, &m_pzErrMsg ); if (result == SQLITE_OK) { QString sData; sData.sprintf("%s", m_ppazResult[1]); int nMin; nMin = sData.toInt(); sqlite3_free_table(m_ppazResult); //释放 return nMin; } else { sqlite3_free_table(m_ppazResult); //释放 return 0; } } else { return 0; }}/********************************************************************************************* Func: 获得数据库记录大小 input: null ouput:记录条数 ********************************************************************************************/int QSKMonitorSQL::fn_RecordSize(QString sTableName){ if (m_pdb!=NULL) { QString stmp; stmp = "select count(*) from "; stmp = stmp + sTableName; int nRow; int nColumn; stmp = fn_UnicodeToUTF8(stmp);//编码转换 int result = sqlite3_get_table(m_pdb , stmp.toAscii().data(), &m_ppazResult , &nRow , &nColumn , &m_pzErrMsg ); if (result == SQLITE_OK) { QString sNum; sNum.sprintf("%s",m_ppazResult[1]); sqlite3_free_table(m_ppazResult); return sNum.toInt(); } else { sqlite3_free_table(m_ppazResult); return -1; } } else { return 0; }}/********************************************************************************************* Func: 获取数据库全部数据 input: pData-返回的查询数据 ouput:返回查询结果总数 ********************************************************************************************/int QSKMonitorSQL::fn_QueryAllData(QString sTableName, QVector<QStringList> *pData){ if (m_pdb!=NULL) { QString stmp; stmp = "select * from "; stmp = stmp + sTableName; //格式化查询语句 int nRow; int nColumn; int result = sqlite3_get_table(m_pdb , stmp.toAscii().data(), &m_ppazResult , &nRow , &nColumn , &m_pzErrMsg ); if (result == SQLITE_OK) { QStringList listTmp; QString sData; for (int i=0;i<nRow;i++) { int iTmp; iTmp = (i + 1)*nColumn; //返回数据中，前nColumn个是字段名字，偏移字段数目 for (int j=0;j<nColumn;j++) { sData.sprintf("%s",m_ppazResult[iTmp + j]); listTmp.append(sData); } pData->append(listTmp); listTmp.clear(); } sqlite3_free_table(m_ppazResult); //释放 return nRow; } else { sqlite3_free_table(m_ppazResult); //释放 return -1; } } else { return -1; }}/********************************************************************************************* Func: 按序号查询指定范围的数据 input: iStart-开始rowid序号 iNumber-查询总数 pData-返回的查询数据 ouput:返回查询结果总数 ********************************************************************************************/int QSKMonitorSQL::fn_QueryDataByIndex(QString sTableName, int iStart, int iNumber, QVector<QStringList> *pData){ if (m_pdb!=NULL) { QString stmp; stmp = "select * from "; stmp = stmp + sTableName + " "; stmp = stmp + "where rowid >= "; stmp = stmp + QString::number(iStart) + " limit "; stmp = stmp + QString::number(iNumber); int nRow; int nColumn; int result = sqlite3_get_table(m_pdb , stmp.toAscii().data(), &m_ppazResult , &nRow , &nColumn , &m_pzErrMsg ); if (result == SQLITE_OK) { QStringList listTmp; QString sData; for (int i=0;i<nRow;i++) { int iTmp; iTmp = (i + 1)*nColumn; //返回数据中，前nColumn个是字段名字，偏移字段数目 for (int j=0;j<nColumn;j++) { sData.sprintf("%s",m_ppazResult[iTmp + j]); listTmp.append(sData); } pData->append(listTmp); listTmp.clear(); } sqlite3_free_table(m_ppazResult); //释放 return nRow; } else { sqlite3_free_table(m_ppazResult); //释放 return -1; } } else { return -1; }}/********************************************************************************************* Func: 按时间查询指定范围的数据 input: dtStart-开始时间 dtEnd-结束时间 pData-返回的查询数据 ouput:返回查询结果总数 ********************************************************************************************/int QSKMonitorSQL::fn_QueryDataByTime(QString sTableName, QDateTime dtStart, QDateTime dtEnd, QVector<QStringList> *pData){ if (m_pdb!=NULL) { QString stmp; stmp = "select * from "; stmp = stmp + sTableName + " "; stmp = stmp + "where logtime >= "; stmp = stmp + "'" + dtStart.toString("yyyy-MM-dd hh:mm:ss") + "'" + " and logtime <= "; stmp = stmp + "'" + dtEnd.toString("yyyy-MM-dd hh:mm:ss") + "'"; int nRow; int nColumn; int result = sqlite3_get_table(m_pdb , stmp.toAscii().data(), &m_ppazResult , &nRow , &nColumn , &m_pzErrMsg ); if (result == SQLITE_OK) { QStringList listTmp; QString sData; for (int i=0;i<nRow;i++) { int iTmp; iTmp = (i + 1)*nColumn; //返回数据中，前nColumn个是字段名字，偏移字段数目 for (int j=0;j<nColumn;j++) { sData.sprintf("%s",m_ppazResult[iTmp + j]); listTmp.append(sData); } pData->append(listTmp); listTmp.clear(); } sqlite3_free_table(m_ppazResult); //释放 return nRow; } else { sqlite3_free_table(m_ppazResult); //释放z return -1; } } else { return 0; }}/********************************************************************************************* Func: 按值查询指定范围的数据 input: nType-比较条件 nFrontV-比较前值 nBackV-比较后值 pData-返回的查询数据 ouput:返回查询结果总数 ********************************************************************************************/int QSKMonitorSQL::fn_QueryDataByValue(QString sTableName, int nType, QString sFrontV, QString sBackV, QVector<QStringList> *pData){ return true;}/********************************************************************************************* Func: 有开始时间和结束时间算出这段时间有多少数据 input: 开始时间 结束时间 返回行数 最小rowid ouput:找到数据返回1 没有找到返回0 Select count(*) From MAIN.[hisdata] where hisdata.logtime >= '2012-05-24 15:53:48'and hisdata.logtime >= '2012-05-24 15:55:48' ; ********************************************************************************************/bool QSKMonitorSQL::fn_RecordSizeByTime(QString sTableName, QDateTime dtStart, QDateTime dtEnd, int *pRows, int *pMaxID,int *pMinID){ if (m_pdb!=NULL) { QString stmp; stmp = "select count(*), MIN(rowid), MAX(rowid) from "; stmp = stmp + sTableName + " "; stmp = stmp + "where logtime >= "; stmp = stmp + "'" + dtStart.toString("yyyy-MM-dd hh:mm:ss") + "'" + " and logtime <= "; stmp = stmp + "'" + dtEnd.toString("yyyy-MM-dd hh:mm:ss") + "'"; int nRow; int nColumn; int result = sqlite3_get_table(m_pdb , stmp.toAscii().data(), &m_ppazResult , &nRow , &nColumn , &m_pzErrMsg ); if (result == SQLITE_OK) { QString sData; int counts; sData.sprintf("%s",m_ppazResult[3]); //获得返回条数 counts = sData.toInt(); //行数 if (counts == 0) //没有数据 { *pRows = counts; *pMinID = 0; *pMaxID = 0; } else { *pRows = counts; sData.sprintf("%s",m_ppazResult[4]); //获得最小rowid *pMinID = sData.toInt(); sData.sprintf("%s",m_ppazResult[5]); //获得最大rowid *pMaxID = sData.toInt(); } } sqlite3_free_table(m_ppazResult); //释放 return 1; } else { *pRows = 0; *pMinID = 0; *pMaxID = 0; return 0; }}/********************************************************************************************* Func: 设置最大行数 input: nMaxRows：最大行数值 ouput: 无 ********************************************************************************************/void QSKMonitorSQL::fn_SetMaxRowId(int nMaxRows){ m_nSetMaxRecordNum = nMaxRows; m_bSetRecordFlag = true;}/********************************************************************************************* Func: unicode 到UTF8转换 input: null ouput:null ********************************************************************************************/QString QSKMonitorSQL::fn_UnicodeToUTF8(QString str){ QString sName; QByteArray baT4; baT4.clear(); baT4=QTextCodec::codecForName("UTF8")->fromUnicode(str); sName = (QString)baT4; return sName;}******************************************************************************************************************************************************************************That's all!
0 0
- sqlite3的基本使用
- SQLite3的使用
- sqlite3的简单使用
- sqlite3的使用教学
- sqlite3 的使用
- SQLite3的使用
- Android：SQLite3的使用
- ios SQLite3的使用
- SQLite3的使用
- IOS sqlite3的使用
- sqlite3方法的使用
- sqlite3数据库的使用
- SQLite3的使用
- sqlite3 FMDB的使用
- Sqlite3的使用小结
- sqlite3触发器的使用
- sqlite3 脚本的使用
- sqlite3的使用
- Lua-Luabind上手试用
- nyoj-cigarettes
- 图解在MyEclipse 10中配置Tomcat服务器(我的是5.x)
- 解决打开Chrome出现 输入密码以解锁您的登录密钥环
- 网络流题目集锦(by 戴神)
- SQLite3的使用
- Python语法(三)
- android 真机调试无线连接和在eclipse的file explorer打开/data/data目录
- 如何导进开源库StickyListHeaders
- 算法杂货铺——分类算法之贝叶斯网络(Bayesian networks)
- Python字符串的encode与decode
- JAVA常见术语总结笔记
- 南航资深机长：飞机因自身故障失事可能性很小
- PAT 1034. Head of a Gang
