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

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了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 function

void MysqLiteEscortStaffFingerPrint::TableQuery(const QString &idescortstaff,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

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问