SQLite3的使用

概述1.下载sqlite3相关文件sqlite3.dll、sqlite3.h(可从http://download.csdn.net/detail/mingxia_sui/5249070下载)，添加到工程的根目录下。 2.  QT += sql LIBS += sqlite3.dll Qt的.pro文件中要加上以上两句。 这样就可以调用sqlite3中的数据库 *** 作函数了。 3.以下的例子是一

1.下载sqlite3相关文件sqlite3.dll、sqlite3.h(可从http://download.csdn.net/detail/mingxia_sui/5249070下载)，添加到工程的根目录下。

2. QT += sql

   liBS += sqlite3.dll
   Qt的.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 sType);  voID fn_InsertData(QString stablename,QStringList sDataList,QDateTime dt);  voID fn_InsertDataForBase(QString stablename,QStringList sDataList);  int fn_UpdateData(QString stablename,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,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(),&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,&m_pzErrMsg );      m_bStartBegin = false;      m_bStartCommit = false;    }  }}/********************************************************************************************* Func: 初始化数据表 input: 表名，表列名 ouput:null********************************************************************************************/voID QSKMonitorsql::fn_Inittable(QString stablename,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,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 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,&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,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,&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,&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 + ",&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,&m_pzErrMsg);  }  fn_StartCommit();}/********************************************************************************************* Func: 修改数据库数据 input: null ouput:null********************************************************************************************/int QSKMonitorsql::fn_UpdateData(QString stablename,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,&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,&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,&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,&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,&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,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,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,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,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,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,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,&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!

总结

以上是内存溢出为你收集整理的SQLite3的使用全部内容，希望文章能够帮你解决SQLite3的使用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。