QT:sqlite创建并访问数据库

最新推荐文章于 2024-05-27 17:04:03 发布

frieryumao

最新推荐文章于 2024-05-27 17:04:03 发布

阅读量3k

点赞数 1

分类专栏： QT sqlite 文章标签： QT sqlite

本文链接：https://blog.csdn.net/frieryumao/article/details/93872518

版权

QT 同时被 2 个专栏收录

9 篇文章 1 订阅

订阅专栏

sqlite

1 篇文章 0 订阅

订阅专栏

本文代码在博文：https://blog.csdn.net/frieryumao/article/details/93871774 基础上实现创建数据库并实现数据库访问。
##知识点
1.读写锁防止多线程资源竞争
2.sqlite实现数据库创建、插入、删除、查询操作
3.增加：执行的sql语句把"“替换为’’,防止sql执行时字符串内包含"使得字符串被截断；例如下图
在这里插入图片描述
上图中红框多了一个”，使得字符串被截断为绿框内容，where约束失效，使得本来只修改where rp_id=1一条语句，变成全部语句都被修改；
因此为解决上述问题，sql语句用’’，需要保存到数据库的数据中含有’要替换为’’

#ifndef DATABASESQL_H
#define DATABASESQL_H
/******************************************************************************
**FileName: sqlite操作语句
**Function: 聊天记录文件的创建、插入、删除、查询操作
**Version record:
**Version       Author         Data          Description
**v1.0.1        wlj          2019.05.17        first draft
*******************************************************************************/

#include <QString>

struct RecvMsgStruct
{
    qint32 iCmdType;
    qint32 iMsgType;
    QString sUserId;
    QString sUserName;
    qint32 iUserStatus;
    QString sTime;
    QString sMsg;
    int iMsgSize;
    QString sGrpId;
    QString sGrpName;
    QString sUserImage;
    QString sUserPicSuffix;//1表示是owner 0表示聊天对象
    QString sMsgId;
    int iMsgClass;
    struct fileClass stFileClass;
    RecvMsgStruct operator=(RecvMsgStruct& tmpRecvMsg)
    {
        iCmdType = tmpRecvMsg.iCmdType;
        iMsgType = tmpRecvMsg.iMsgType;
        sUserId = tmpRecvMsg.sUserId;
        sUserName = tmpRecvMsg.sUserName;
        iUserStatus = tmpRecvMsg.iUserStatus;
        sTime = tmpRecvMsg.sTime;
        sMsg = tmpRecvMsg.sMsg;
        iMsgSize = tmpRecvMsg.iMsgSize;
        sGrpId = tmpRecvMsg.sGrpId;
        sGrpName = tmpRecvMsg.sGrpName;
        sUserImage = tmpRecvMsg.sUserImage;
        sUserPicSuffix = tmpRecvMsg.sUserPicSuffix;
        sMsgId = tmpRecvMsg.sMsgId;
        iMsgClass = tmpRecvMsg.iMsgClass;
        stFileClass = tmpRecvMsg.stFileClass;
        //c = tmpRecvMsg.c;
        return *this;
    }
    RecvMsgStruct()
    {
        iCmdType = 0;
        iMsgType = 0;
        sUserId = "";
        sUserName = "";
        iUserStatus = 0;
        sTime = "";
        sMsg = "";
        iMsgSize = 0;
        sGrpId = "";
        sGrpName = "";
        sUserImage = "";
        sUserPicSuffix = "";
        sMsgId = "";
        iMsgClass = 0;
    }
};
// ChatMsg.db
#define SQL_CREATE_CHATMSG_SINGLE QString("create table if not exists %1(\
type_msgid varchar(128) primary key,\
type_msg varchar(4000),\
type_timer varchar(64) not null,\
type_userid varchar(8) not null,\
type_username varchar(32) not null,\
type_filepath varchar(128),\
type_msgclass varchar(1) not null,\
type_isownerflag varchar(1) not null)")
#define SQL_CREATE_CHATMSG_GROUP QString("create table if not exists \"%1\"(\
type_msgid varchar(128) primary key,\
type_msg varchar(4000),\
type_timer varchar(64) not null,\
type_userid varchar(8) not null,\
type_username varchar(32) not null,\
type_groupid varchar(16),\
type_filepath varchar(128),\
type_msgclass varchar(1) not null)")
~~#define SQL_INSERT_CHATMSG_SINGLE QString("insert into \"%1\" values(\"%2\",\"%3\",\"%4\",\"%5\",\"%6\",\"%7\",\"%8\",\"%9\")")
#define SQL_INSERT_CHATMSG_GROUP QString("insert into \"%1\" values(\"%2\",\"%3\",\"%4\",\"%5\",\"%6\",\"%7\",\"%8\",\"%9\")")
#define SQL_SELECT_CHATMSG_BY_TABLE QString("select * from \"%1\"")
#define SQL_SELECT_ONE_CHATMSG_BY_TABLE QString("select * from \"%1\" where type_msgid=\"%2\"")~~ 
#define SQL_INSERT_CHATMSG_SINGLE QString("insert into \‘%1\‘ values(\‘%2\‘,\‘%3\‘,\‘%4\‘,\‘%5\‘,\‘%6\‘,\‘%7\‘,\‘%8\‘,\‘%9\‘)")
#define SQL_INSERT_CHATMSG_GROUP QString("insert into \‘%1\‘ values(\‘%2\‘,\‘%3\‘,\‘%4\‘,\‘%5\‘,\‘%6\‘,\‘%7\‘,\‘%8\‘,\‘%9\‘)")
#define SQL_SELECT_CHATMSG_BY_TABLE QString("select * from \‘%1\‘")
#define SQL_SELECT_ONE_CHATMSG_BY_TABLE QString("select * from \‘%1\‘ where type_msgid=\‘%2\‘")

//获取最后N条数据，取出的第一条数据为时间最新的数据    参数1：表名  参数2：聊天对象ID号，参数3：获取前N条
~~#define SQL_SELECT_CHATMSG_SINGLE_LASTTOPN QString("select * from \"%1\" where type_userid=\"%2\" order by type_timer desc limit \"%3\"")
#define SQL_SELECT_CHATMSG_GROUP_LASTTOPN QString("select * from \"%1\" where type_groupid=\"%2\" order by type_timer desc limit \"%3\"")
#define SQL_DELETE_CHATMSG_BY_MSGID QString("delete from \"%1\" where type_msgid=\"%2\"")~~ 
#define SQL_SELECT_CHATMSG_SINGLE_LASTTOPN QString("select * from \‘%1\‘ where type_userid=\‘%2\‘ order by type_timer desc limit \‘%3\‘")
#define SQL_SELECT_CHATMSG_GROUP_LASTTOPN QString("select * from \‘%1\‘ where type_groupid=\‘%2\‘ order by type_timer desc limit \‘%3\‘")
#define SQL_DELETE_CHATMSG_BY_MSGID QString("delete from \‘%1\‘ where type_msgid=\‘%2\‘")


#endif // DATABASESQL_H

#ifndef APPDATABASE_H
#define APPDATABASE_H
/******************************************************************************
**FileName: 客户端数据库
**Function:
**          1:提供插入单人/群聊数据接口
**          2:提供撤销单人/群聊数据接口
**          3:提供获取单人/群聊历史聊天记录接口
**Version record:
**Version       Author         Data          Description
**v1.0.1        wlj  2019.05.22        first draft
*******************************************************************************/
#include<QtSql>
#include<QString>
#include<QMutex>
#include<QMutexLocker>
#include"connectionpool.h"
#include"databasesql.h"
#include "../public.h"
#include"../chat.h"

extern Quser g_user;
#define HISTORYNUM 50

class AppDataBase
{
public:
    /******************************************************************************
* Function: 插入单人/群聊数据
* InPut   : recvMsgStruct 一条聊天数据信息
* OutPut  : 无
* Return  : true插入成功，false插入失败
* Other   :
* Author  : wlj  2019.05.22
*******************************************************************************/
    static bool AddSingleHistoryMsg(struct RecvMsgStruct& recvMsgStruct);
    static bool AddGroupHistoryMsg(struct RecvMsgStruct& recvMsgStruct);

    /******************************************************************************
* Function: 获取一条单人/群聊数据
* InPut   : typeMsgid:聊天记录的msgId
* OutPut  : valueInfo 一条聊天数据信息
* Return  : true查询成功，false查询失败
* Other   :
* Author  : wlj  2019.05.22
*******************************************************************************/
    static bool GetSingleOneMsg(QString &typeMsgid, QVector<QVariant> &valueInfo);
    static bool GetGroupOneMsg(QString &typeMsgid, QVector<QVariant> &valueInfo);

    /******************************************************************************
* Function: 撤销单人/群聊数据
* InPut   : typeMsgid:删除该聊天记录的msgId
* OutPut  : 无
* Return  : true撤销成功，false撤销失败
* Other   :
* Author  : wlj  2019.05.22
*******************************************************************************/
    static bool DeleteSingleHistory(QString &typeMsgid);
    static bool DeleteGroupHistory(QString &typeMsgid);

    /******************************************************************************
* Function: 获取单人/群聊历史聊天记录
* InPut   : typeUserid/typeGroupid聊天对象  count默认读取聊天记录条数
* OutPut  : valuesList聊天记录列表,valuesList[0]是时间最近的数据
* Single:valuesList[0][i]:type_msgid,type_msg,type_timer,type_userid,type_username,type_filepath,type_msgclass
* Group :valuesList[0][i]:type_msgid,type_msg,type_timer,type_userid,type_username,type_groupid, type_filepath,type_msgclass
* Return  : true获取成功，false获取失败
* Other   :
* Author  : wlj  2019.05.22
*******************************************************************************/
    static bool QuerySingleHistory(QString typeUserid, QList<QVector<QVariant>> &valuesList, const int &count = HISTORYNUM);
    static bool QueryGroupHistory(QString typeGroupid, QList<QVector<QVariant>> &valuesList,const int &count = HISTORYNUM);

    /******************************************************************************
* Function: 打印数据库所有的表格内容，测试用
* InPut   : 无
* OutPut  : 所有聊天记录
* Return  : 无
* Other   :
* Author  : wlj  2019.05.22
*******************************************************************************/
    static void QueryAll();
    ~AppDataBase();

private:
    AppDataBase();
    static AppDataBase *GetInstance();
    void AddNewclient();
    bool InsertData(const QString sqlStr);
    bool SelectTopNData(const QString sqlStr, int fieldsCount, QList<QVector<QVariant>> &valuesList);
    bool DeleteData(const QString sqlStr);
    AppDataBase(const AppDataBase&){}
//    AppDataBase & operator=(const AppDataBase &){}//禁止赋值拷贝构造函数
    class GarbageCollector
    {
    public:
        ~GarbageCollector()
        {
            if(NULL != AppDataBase::m_DataBaseInstance)
            {
                qDebug()<<"release AppDataBase::m_DataBaseInstance";
                delete AppDataBase::m_DataBaseInstance;
                AppDataBase::m_DataBaseInstance = nullptr;
            }
        }
    };

private:
    static GarbageCollector m_gc;          //静态变量，只是为了释放单例
    static AppDataBase* m_DataBaseInstance;
    static QMutex m_Mutex;

    QString m_UserSingleTable;//单人聊天表名称
    QString m_UserGroupTable;//群聊天表名称
    QReadWriteLock m_rwLock;//读写锁
    QString m_FilesPath;
    QString m_ChatMsgPath;
};

#endif // APPDATABASE_H

#include<QDir>
#include"appdatabase.h"


#define HOSTNAME "127.0.0.1"
#define DATABASETYPE "QSQLITE"
#define SINGLEFILELINE 8
#define GROUPFILELINE 8
#define CHATMSGFOLDER "ChatMsg";
//QString g_DataBaseName = "/ChatMsg.db";

#define FILESFOLDER "Files";
//#define DBPATH "./ChatMsg"
#define  DATABSENAME "ChatMsg.db"
//#define TESTBUTTON 1
QMutex AppDataBase::m_Mutex;
AppDataBase::GarbageCollector AppDataBase::m_gc;
AppDataBase* AppDataBase::m_DataBaseInstance = NULL;

void AppDataBase::QueryAll()
{
    QSqlDatabase db = ConnectionPool::OpenConnection();
    QSqlQuery query(SQL_SELECT_CHATMSG_BY_TABLE.arg(AppDataBase::GetInstance()->m_UserSingleTable), db);

    qDebug()<<"print SingleTable Info!";
    while (query.next())
    {
        qDebug() << query.value(0).toString() << query.value(1).toString()
                 << query.value(2).toString() << query.value(3).toString()
                 << query.value(4).toString() << query.value(5).toString()
                 << query.value(6).toString()<< query.value(7).toString();
    }

    query = QSqlQuery(SQL_SELECT_CHATMSG_BY_TABLE.arg(AppDataBase::GetInstance()->m_UserGroupTable), db);

    qDebug()<<"print GroupTable Info!";
    while (query.next())
    {
        qDebug() << query.value(0).toString() << query.value(1).toString()
                 << query.value(2).toString() << query.value(3).toString()
                 << query.value(4).toString() << query.value(5).toString()
                 << query.value(6).toString() << query.value(7).toInt();
    }
    ConnectionPool::CloseConnection(db);
}
AppDataBase::AppDataBase()
{
    qDebug()<<"AppDataBase::AppDataBase";
    //数据库文件目录，不存在则创建
    QString qsRunPath = QCoreApplication::applicationDirPath();
    m_FilesPath = qsRunPath + "//" + FILESFOLDER;
    m_ChatMsgPath = qsRunPath + "//" + CHATMSGFOLDER;
    QDir dir;

    if(!dir.exists(m_FilesPath))//判断路径是否存在
    {
        (void)dir.mkpath(m_FilesPath); //创建路径
        qDebug()<<"mkpath: "<<qPrintable(m_FilesPath);
    }

    if(!dir.exists(m_ChatMsgPath))//判断路径是否存在
    {
        (void)dir.mkpath(m_ChatMsgPath); //创建路径
        qDebug()<<"mkpath: "<<qPrintable(m_ChatMsgPath);
    }
    QString chatFilePath = m_ChatMsgPath + "//" + DATABSENAME;
    ConnectionPool::SetParam(chatFilePath,DATABASETYPE);
    m_UserSingleTable = "Single"+ g_user.userID;
    m_UserGroupTable = "Group"+ g_user.userID;
#ifdef TESTBUTTON == 1
    m_UserSingleTable = "Single1234";
    m_UserGroupTable = "Group1234";
#endif
    AddNewclient();
}
AppDataBase::~AppDataBase()
{

}
AppDataBase *AppDataBase::GetInstance()
{
    if(NULL == m_DataBaseInstance)
    {
        QMutexLocker locker(&m_Mutex);
        if(NULL == m_DataBaseInstance)
        {
            AppDataBase *tmp = new AppDataBase();
            m_DataBaseInstance = tmp;
        }
    }
    return m_DataBaseInstance;
}
bool AppDataBase::GetSingleOneMsg(QString &typeMsgid, QVector<QVariant> &valueInfo)
{
    bool flag = true;

    QString sqlStr = SQL_SELECT_ONE_CHATMSG_BY_TABLE.arg(AppDataBase::GetInstance()->m_UserSingleTable).arg(typeMsgid);
    QList<QVector<QVariant> > valuesList;

    flag = AppDataBase::GetInstance()->SelectTopNData(sqlStr,SINGLEFILELINE,valuesList);
    if(flag && (0 != valuesList.size()) )
    {
        valueInfo = valuesList[0];
    }

    return flag;
}
bool AppDataBase::GetGroupOneMsg(QString &typeMsgid, QVector<QVariant> &valueInfo)
{
    bool flag = true;
    QString sqlStr = SQL_SELECT_ONE_CHATMSG_BY_TABLE.arg(AppDataBase::GetInstance()->m_UserGroupTable).arg(typeMsgid);
    QList<QVector<QVariant> > valuesList;

    flag = AppDataBase::GetInstance()->SelectTopNData(sqlStr,GROUPFILELINE,valuesList);
    if(flag && (0 != valuesList.size()) )
    {
        valueInfo = valuesList[0];
    }
    return flag;
}

bool AppDataBase::AddSingleHistoryMsg(struct RecvMsgStruct &recvMsgStruct)
{
    //遗留问题：sTime时间格式未转换,sFilepath未添加字段
//    QString sFilepath = AppDataBase::GetInstance()->m_FilesPath
//            + "//" + recvMsgStruct.stFileClass.sFileName
//            +"." + recvMsgStruct.stFileClass.sFileSuffix;

    QString sFilepath = recvMsgStruct.sMsg;
    QString sqlStr = SQL_INSERT_CHATMSG_SINGLE.arg(AppDataBase::GetInstance()->m_UserSingleTable)\
            .arg(recvMsgStruct.sMsgId)\
            .arg(recvMsgStruct.sMsg)\
            .arg(recvMsgStruct.sTime)\
            .arg(recvMsgStruct.sUserId)\
            .arg(recvMsgStruct.sUserName)\
            .arg(sFilepath)\
            .arg(recvMsgStruct.iMsgClass)\
            .arg(recvMsgStruct.sUserPicSuffix.toInt());

    return AppDataBase::GetInstance()->InsertData(sqlStr);
}

bool AppDataBase::AddGroupHistoryMsg(struct RecvMsgStruct &recvMsgStruct)
{
    //遗留问题：sTime时间格式未转换,sFilepath未添加字段
//     QString sFilepath = AppDataBase::GetInstance()->m_FilesPath
//             + "//" + recvMsgStruct.stFileClass.sFileName
//             +"." + recvMsgStruct.stFileClass.sFileSuffix;
    QString sFilepath = recvMsgStruct.sMsg;
    if(recvMsgStruct.sMsg.contains("'"))
    {
        recvMsgStruct.sMsg.replace("'", "''");
    }

    QString sqlStr = SQL_INSERT_CHATMSG_GROUP.arg(AppDataBase::GetInstance()->m_UserGroupTable)\
            .arg(recvMsgStruct.sMsgId)\
            .arg(recvMsgStruct.sMsg)\
            .arg(recvMsgStruct.sTime)\
            .arg(recvMsgStruct.sUserId)\
            .arg(recvMsgStruct.sUserName)\
            .arg(recvMsgStruct.sGrpId)\
            .arg(sFilepath)\
            .arg(recvMsgStruct.iMsgClass);

    return AppDataBase::GetInstance()->InsertData(sqlStr);
}
bool AppDataBase::QuerySingleHistory(QString typeUserid, QList<QVector<QVariant>> &valuesList, const int &count)
{
    QString sqlStr = SQL_SELECT_CHATMSG_SINGLE_LASTTOPN.arg(AppDataBase::GetInstance()->m_UserSingleTable).arg(typeUserid).arg(count);

    return AppDataBase::GetInstance()->SelectTopNData(sqlStr,SINGLEFILELINE,valuesList);
}
bool AppDataBase::QueryGroupHistory(QString typeGroupid, QList<QVector<QVariant>> &valuesList,const int &count)
{
    QString sqlStr = SQL_SELECT_CHATMSG_GROUP_LASTTOPN.arg(AppDataBase::GetInstance()->m_UserGroupTable).arg(typeGroupid).arg(count);

    return AppDataBase::GetInstance()->SelectTopNData(sqlStr,GROUPFILELINE,valuesList);
}
bool AppDataBase::DeleteSingleHistory(QString &typeMsgid)
{
    QString sqlStr = SQL_DELETE_CHATMSG_BY_MSGID.arg(AppDataBase::GetInstance()->m_UserSingleTable).arg(typeMsgid);

    return AppDataBase::GetInstance()->DeleteData(sqlStr);
}
bool AppDataBase::DeleteGroupHistory(QString &typeMsgid)
{
    QString sqlStr = SQL_DELETE_CHATMSG_BY_MSGID.arg(AppDataBase::GetInstance()->m_UserGroupTable).arg(typeMsgid);

    return AppDataBase::GetInstance()->DeleteData(sqlStr);
}
void AppDataBase::AddNewclient()
{
    // 从数据库连接池里取得连接
    QSqlDatabase db = ConnectionPool::OpenConnection();
    QSqlQuery query(db);

    if(!query.exec(SQL_CREATE_CHATMSG_SINGLE.arg(m_UserSingleTable)))
    {
        qWarning()<<"create "<< m_UserSingleTable<<" file fail,[SQL]= "<<qPrintable(query.lastError().text());
        ConnectionPool::CloseConnection(db);
        return;
    }

    if(!query.exec(SQL_CREATE_CHATMSG_GROUP.arg(m_UserGroupTable)))
    {
        qWarning()<<"create "<< m_UserGroupTable<<" file fail,[SQL]= "<<qPrintable(query.lastError().text());
        ConnectionPool::CloseConnection(db);
        return;
    }

    ConnectionPool::CloseConnection(db);
}

bool AppDataBase::InsertData(const QString sqlStr)
{
    QSqlDatabase db = ConnectionPool::OpenConnection();
    QWriteLocker wlocker(&m_rwLock);
    QSqlQuery query(db);

    if(!query.exec(sqlStr))
    {
        qWarning()<<"insert ChatMsg fail! [SQL] = "<<qPrintable(query.lastError().text());
        qDebug()<<"Fail Sqlite Operation : "<<qPrintable(sqlStr);
        ConnectionPool::CloseConnection(db);
        return false;
    }

    ConnectionPool::CloseConnection(db);
    return true;
}

bool AppDataBase::DeleteData(const QString sqlStr)
{
    QSqlDatabase db = ConnectionPool::OpenConnection();
    QSqlQuery query(db);

    QWriteLocker wlocker(&m_rwLock);
    if(!query.exec(sqlStr))
    {
        qWarning()<<"delete ChatMsg fail! [SQL] = "<<qPrintable(query.lastError().text());
        qDebug()<<"Fail Sqlite Operation : "<<qPrintable(sqlStr);
        ConnectionPool::CloseConnection(db);
        return false;
    }

    ConnectionPool::CloseConnection(db);
    return true;
}

bool AppDataBase::SelectTopNData(const QString sqlStr, int fieldsCount, QList<QVector<QVariant> > &valuesList)
{
    QSqlDatabase db = ConnectionPool::OpenConnection();
    QSqlQuery sql_query(db);

    QReadLocker rlocker(&m_rwLock);
    if(!sql_query.exec(sqlStr))
    {
        qWarning()<<"SelectData fail,[SQL] = "<<qPrintable(sql_query.lastError().text());
        qDebug()<<"Fail Sqlite Operation :"<<qPrintable(sqlStr);
        ConnectionPool::CloseConnection(db);
        return false;
    }

    while (sql_query.next())
    {
        QVector<QVariant> valueVector;
        valueVector.clear();
        for(int i=0; i<fieldsCount; i++)
        {
            valueVector.append(sql_query.value(i));
        }

        valuesList.append(valueVector);
    }

    return true;
}

frieryumao

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
QT:sqlite创建并访问数据库

本文代码在博文：https://blog.csdn.net/frieryumao/article/details/93871774 基础上实现创建数据库并实现数据库访问。#ifndef DATABASESQL_H#define DATABASESQL_H/*******************************************************************...
复制链接

扫一扫