本文代码在博文：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;
}