QT5下SQLite的增删改查示例代码

Author: Kagula

Date: 2016-8-10

Envrionment: Qt 5.6

Introduction:

   从项目中抽出来的如何操作sqlite的代码，这里记下，免得时间久忘记了。

测试示例代码

void MainWindow::OnAdd(){    MySQLiteEscortStaffFingerPrint db;    std::vector<EscortStaffFingerPrint> vecAdd;    vecAdd.push_back(EscortStaffFingerPrint("0","0","2","fp","fp-md5"));    vecAdd.push_back(EscortStaffFingerPrint("1","3","4","fp2","fp2-md5"));    db.TableAdd(vecAdd);}void MainWindow::OnDelete(){    std::vector<QString> vecDel;    vecDel.push_back("0");    vecDel.push_back("1");    MySQLiteEscortStaffFingerPrint db;    db.TableDelete(vecDel);}void MainWindow::OnModify(){    std::vector<EscortStaffFingerPrint> vecModify;    vecModify.push_back(EscortStaffFingerPrint("2","23","24","fp22","fp22-md5"));    MySQLiteEscortStaffFingerPrint db;    db.TableModify(vecModify);}

新建数据库的示例代码

头文件

#ifndef MYSQLITE_H#define MYSQLITE_H#include <Qtsql/QSqlDatabase>/* * parent class * function * [1]打开或新建DB * [2]关闭DB * */class MySQLite{public:    MySQLite();    ~MySQLite();private:    QSqlDatabase m_dbconn;};#endif // MYSQLITE_H

实现文件

#include "mysqlite.h"#include <Qtsql/QSqlQuery>#include <QDebug>MySQLite::MySQLite(){    m_dbconn = QSqlDatabase::addDatabase("QSQLITE");    //添加SQLite数据库驱动    QString fullPath = "mytest.db";    m_dbconn.setDatabaseName(fullPath);  //在工程目录新建一个mytest.db的文件    if(!m_dbconn.open())    {        qDebug()<<"open "<<fullPath<<" failed!";    }}MySQLite::~MySQLite(){    m_dbconn.close();}

增删改查的示例代码

头文件

#ifndef MYSQLITEESCORTSTAFFFINGERPRINT_H#define MYSQLITEESCORTSTAFFFINGERPRINT_H#include "mysqlite.h"#include <QString>#include <vector>/* * [1]不支持线程，不建议long life span! * [2]class实例化后，使用完毕，应立即释放。 * [3]服务端会把modify动作拆成delete,add两个动作，所以理论上不会有修改现存记录的动作。 *    除非用户直接修改数据库EscortStaffFingerPrint表记录。 * */struct EscortStaffFingerPrint{    QString idEscortStaffFingerPrint;    QString idescortstaff;    QString fingersequence;    QString fingerprint;    QString fingerprintmd5;    EscortStaffFingerPrint(){}    EscortStaffFingerPrint(QString idEscortStaffFingerPrint,                           QString idescortstaff,                           QString fingersequence,                           QString fingerprint,                           QString fingerprintmd5)    {        this->idEscortStaffFingerPrint = idEscortStaffFingerPrint;        this->idescortstaff = idescortstaff;        this->fingersequence = fingersequence;        this->fingerprint = fingerprint;        this->fingerprintmd5=fingerprintmd5;    }};struct EscortStaffFingerPrintSimple{    QString idEscortStaffFingerPrint;    QString fingerprintmd5;};class MySQLiteEscortStaffFingerPrint:public MySQLite{public:    MySQLiteEscortStaffFingerPrint();    int m_maxid;    std::vector<EscortStaffFingerPrintSimple> GetTableInfo();    void TableQuery(const QString &idescortstaff,                    const QString &fingersequence,                    QString &sRegTemplate,QString &sRegTemplate10);    void TableDelete(std::vector<QString> vecIdEscortStaffFingerPrint);    void TableAdd(std::vector<EscortStaffFingerPrint> vecAdd);    void TableModify(std::vector<EscortStaffFingerPrint> vecModify);};#endif // MYSQLITEESCORTSTAFFFINGERPRINT_H

实现文件

#include "mysqliteescortstafffingerprint.h"#include <QDebug>#include <QSqlQuery>MySQLiteEscortStaffFingerPrint::MySQLiteEscortStaffFingerPrint():MySQLite(){    m_maxid = 0;    QSqlQuery query;    //sql originated from visual paradigm project.    QString sql="CREATE TABLE EscortStaffFingerPrint (idEscortStaffFingerPrint number(10) NOT NULL, "                "idescortstaff number(10) NOT NULL, fingersequence number(2) DEFAULT 0 NOT NULL, "                "fingerprint varchar2(3072) NOT NULL, fingerprintmd5 varchar2(32) NOT NULL);";    bool success = query.exec(sql);    if(success){        qDebug()<<"Create Table Success! \n";    }else{        //table is already exist!        QString sql = "select idEscortStaffFingerPrint from EscortStaffFingerPrint "                      "order by idEscortStaffFingerPrint desc limit 0,1";        QSqlQuery sql_query;        sql_query.prepare(sql);        if(sql_query.exec())        {            if(sql_query.next())            {                m_maxid = sql_query.value(0).toInt();            }        }    }    qDebug()<<"m_maxid="<<m_maxid<<"\n";}std::vector<EscortStaffFingerPrintSimple> MySQLiteEscortStaffFingerPrint::GetTableInfo(){    std::vector<EscortStaffFingerPrintSimple> vecR;    if(m_maxid<=0)        return vecR;    QString sql = "select idEscortStaffFingerPrint,fingerprintmd5 from EscortStaffFingerPrint "                  "order by idEscortStaffFingerPrint";    QSqlQuery sql_query;    sql_query.prepare(sql);    if(sql_query.exec())    {        while(sql_query.next())        {            EscortStaffFingerPrintSimple rec;            rec.idEscortStaffFingerPrint = sql_query.value(0).toString();            rec.fingerprintmd5 = sql_query.value(1).toString();            vecR.push_back(rec);        }    }    return vecR;}void MySQLiteEscortStaffFingerPrint::TableDelete(std::vector<QString> vecIdEscortStaffFingerPrint){    for(unsigned int i=0;i<vecIdEscortStaffFingerPrint.size();i++)    {        QSqlQuery sql_delete;        sql_delete.prepare("delete from EscortStaffFingerPrint where idEscortStaffFingerPrint=?");        int idEscortStaffFingerPrint = vecIdEscortStaffFingerPrint[i].toInt();        sql_delete.addBindValue(idEscortStaffFingerPrint);        if(!sql_delete.exec())        {            qDebug()<<"delete record ["<<vecIdEscortStaffFingerPrint[i]<<"] in EscortStaffFingerPrint table failed!";        }    }}void MySQLiteEscortStaffFingerPrint::TableAdd(std::vector<EscortStaffFingerPrint> vecAdd){    for(int i=0;i<vecAdd.size();i++)    {        QSqlQuery sql_add;        sql_add.prepare("insert into EscortStaffFingerPrint values(?,?,?,?,?)");        sql_add.addBindValue(vecAdd[i].idEscortStaffFingerPrint.toInt());        sql_add.addBindValue(vecAdd[i].idescortstaff.toInt());        sql_add.addBindValue(vecAdd[i].fingersequence.toInt());        sql_add.addBindValue(vecAdd[i].fingerprint);        sql_add.addBindValue(vecAdd[i].fingerprintmd5);        if(!sql_add.exec())        {            qDebug()<<"insert record ["<<vecAdd[i].idEscortStaffFingerPrint<<"] in EscortStaffFingerPrint table failed!";        }    }}void MySQLiteEscortStaffFingerPrint::TableModify(std::vector<EscortStaffFingerPrint> vecModify){    for(int i=0;i<vecModify.size();i++)    {        QSqlQuery sql_modify;        sql_modify.prepare("update EscortStaffFingerPrint set idescortstaff=?,"                           "fingersequence=?,fingerprint=?,fingerprintmd5=?"                           " where idEscortStaffFingerPrint=?");        sql_modify.addBindValue(vecModify[i].idescortstaff.toInt());        sql_modify.addBindValue(vecModify[i].fingersequence.toInt());        sql_modify.addBindValue(vecModify[i].fingerprint);        sql_modify.addBindValue(vecModify[i].fingerprintmd5);        sql_modify.addBindValue(vecModify[i].idEscortStaffFingerPrint.toInt());        if(!sql_modify.exec())        {            qDebug()<<"modify record ["<<vecModify[i].idEscortStaffFingerPrint<<"] in EscortStaffFingerPrint table failed!";        }//end if    }//end for}//end functionvoid MySQLiteEscortStaffFingerPrint::TableQuery(const QString &idescortstaff,                                                const QString &fingersequence,                                                QString &sRegTemplate,QString &sRegTemplate10){    std::vector<EscortStaffFingerPrintSimple> vecR;    if(m_maxid<=0)        return;    QString sql = "select fingerprint from EscortStaffFingerPrint "                  "where idescortstaff=? and fingersequence=?";    QSqlQuery sql_query;    sql_query.prepare(sql);    sql_query.bindValue(0,idescortstaff.toInt());    sql_query.bindValue(1,fingersequence.toInt());    if(sql_query.exec())    {        if(sql_query.next())        {            QString fingerPrint = sql_query.value(0).toString();            QStringList Str_List = fingerPrint.split(",");            if(Str_List.size()==2)            {                sRegTemplate = Str_List[0];                sRegTemplate10 = Str_List[1];            }        }    }//end if}//end function

附: .pro文件片段

QT       += core gui printsupport axcontainer sql