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

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

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 function

void 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