前言
如果你不想了解sql语句,又想快速上手数据库操作,不凡用下已经封装好的接口。
采用工厂模式的设计方式,这样方便我们后期对接其他数据类型。
同时我们将接口抽象化,这样保证了我们应用层的接口都是通用的。
一、关键程序
代码如下:
#include "SqlLiteObject.h"
#include <QDir>
#include <QDateTime>
#include <QApplication>
#define SAVE_DIR_NAME QString("Database")
CSqlLiteObject::CSqlLiteObject(QObject *parent)
: CSqlBase(parent)
{
}
CSqlLiteObject::~CSqlLiteObject()
{
closeDb();
}
void CSqlLiteObject::createDir()
{
QDir dir;
m_strConfigPath = m_strExeDirPath + QString("/Config");
if (!dir.exists(m_strConfigPath))
dir.mkpath(m_strConfigPath);
m_strCsvPath = m_strExeDirPath + QString("/Config/CSV");
if (!dir.exists(m_strCsvPath))
dir.mkpath(m_strCsvPath);
m_strDbDirPath = m_strExeDirPath + QString("/Config/") + SAVE_DIR_NAME + QString("/");
if (!dir.exists(m_strDbDirPath))
dir.mkpath(m_strDbDirPath);
}
void CSqlLiteObject::setExeDirPath(QString strPath)
{
m_strExeDirPath = strPath;
createDir();
}
QSqlDatabase CSqlLiteObject::openDb(QString strDatabaseName)
{
m_strDatabaseName = strDatabaseName;
QSqlDatabase db;
if (QSqlDatabase::contains(m_strDatabaseName))
db = QSqlDatabase::database(m_strDatabaseName);
else
{
QString strTempName = m_strDbDirPath + strDatabaseName;
db = QSqlDatabase::addDatabase("QSQLITE", m_strDatabaseName);
db.setDatabaseName(strTempName);
db.setPassword("8888");
db.setHostName("root");
db.setUserName("root");
}
if (!db.open())
qDebug() << db.lastError().text();
else
m_bConnected = true;
return db;
}
bool CSqlLiteObject::closeDb()
{
QSqlDatabase::removeDatabase(m_strDatabaseName);
return true;
}
bool CSqlLiteObject::createTable(QString strTableName, QStringList strHeaderNameList)
{
bool bRet = true;
if (true)
{
if (strTableName.isEmpty() || strHeaderNameList.size() <= 0)
{
bRet = false;
return bRet;
}
auto findItem = m_strTableNameHeaderHash.find(strTableName);
if (findItem == m_strTableNameHeaderHash.end())
m_strTableNameHeaderHash.insert(strTableName, strHeaderNameList);
QString strCreateTable = QString(u8"CREATE TABLE %1(").arg(strTableName);
for (int i = 0; i < strHeaderNameList.size(); i++)
{
if (i < (strHeaderNameList.size() - 1))
strCreateTable = strCreateTable + strHeaderNameList[i] + QString(u8" VARCHAR(256)") + QString(",");
else
strCreateTable = strCreateTable + strHeaderNameList[i] + QString(u8" VARCHAR(256)") + QString(")");
}
bRet = excute(strCreateTable);
}
if (m_bSaveCsv)
{
QString strFilePath = m_strCsvPath + "/" + QString(u8"【%1】 %2.csv").arg(strTableName).arg(QDateTime::currentDateTime().toString("yyyy.MM.dd"));
static QMutex mutex;
QFile fileCSV;
if (!fileCSV.exists(strFilePath))
{
mutex.lock();
QFile file(strFilePath);
if (file.open(QIODevice::WriteOnly | QIODevice::Append | QIODevice::Text))
{
QTextStream in(&file);
QString strText("");
for (int i = 0; i < strHeaderNameList.size(); i++)
{
if (i == 0)
strText = strHeaderNameList[i] + ",";
else if (i < (strHeaderNameList.size() - 1))
strText = strText + strHeaderNameList[i] + ",";
else
strText = strText + strHeaderNameList[i];
}
in << strText << '\n';
file.close();
}
mutex.unlock();
}
}
return bRet;
}
bool CSqlLiteObject::dropTable(QString strTableName)
{
if (strTableName.isEmpty())
return false;
QString strDrop = QString("DROP TABLE %1").arg(strTableName);
return excute(strDrop);
}
bool CSqlLiteObject::excute(QString strSql)
{
static QMutex mutex;
mutex.lock();
bool bRet = true;
QSqlDatabase db = openDb(m_strDatabaseName);
QString strConnectionName = db.connectionName();
QSqlQuery query(db);
query.prepare(strSql);
bool success = query.exec(strSql);
if (!success)
{
qDebug() << "Error:" << query.lastError();
bRet = false;
}
closeDb();
mutex.unlock();
return bRet;
}
QList< QHash<QString/*name*/, QString/*value*/> > CSqlLiteObject::select(QString strTableName, QString strName, QString strValue)
{
QList< QHash<QString, QString> > temp;
if (strTableName.isEmpty() || strTableName.isEmpty())
return temp;
QSqlDatabase db = openDb(m_strDatabaseName);
QString strConnectionName = db.connectionName();
QString strSelect("");
if (!strValue.isEmpty()&& !strName.isEmpty())
strSelect = QString("SELECT * FROM %1 WHERE %2 = '%3';").arg(strTableName).arg(strName).arg(strValue);
else
strSelect = QString("SELECT * FROM %1;").arg(strTableName);
db.transaction();
QSqlQuery query("", db);
query.exec(strSelect);
db.commit();
while (query.next())
{
QHash<QString, QString> keyValueHash;
int nCount = query.record().count();
for (size_t i = 0; i < nCount; i++)
keyValueHash.insert(query.record().fieldName(i), query.record().value(i).toString());
if (keyValueHash.size() > 0)
temp << keyValueHash;
}
closeDb();
return temp;
}
bool CSqlLiteObject::insert(QString strTableName, QHash<QString/*name*/, QString/*value*/> dataHash)
{
bool bRet = false;
if (strTableName.isEmpty())
return bRet;
QStringList strHeaderList;
auto findItem = m_strTableNameHeaderHash.find(strTableName);
if (findItem == m_strTableNameHeaderHash.end())
return bRet;
else
strHeaderList = m_strTableNameHeaderHash[strTableName];
if (m_bSaveCsv)
{
QString strFilePath = m_strCsvPath + "/" + QString(u8"【%1】 %2.csv").arg(strTableName).arg(QDateTime::currentDateTime().toString("yyyy.MM.dd"));
static QMutex mutex;
mutex.lock();
QFile file(strFilePath);
if (file.open(QIODevice::WriteOnly | QIODevice::Append | QIODevice::Text))
{
QTextStream in(&file);
QString strMessage;
for (size_t j = 0; j < strHeaderList.size(); j++)
{
QString strName = strHeaderList[j];
QString strValue = "";
auto findName = dataHash.find(strName);
if (findName != dataHash.end())
strValue = findName.value();
if (j == 0)
strMessage = strValue + ",";
else if (j < (strHeaderList.size() - 1))
strMessage = strMessage + strValue + ",";
else
strMessage = strMessage + strValue;
}
in << strMessage << '\n';
file.close();
}
mutex.unlock();
}
if (true)
{
QString strInsert = QString(u8"INSERT INTO %1 VALUES(").arg(strTableName);
for (size_t j = 0; j < strHeaderList.size(); j++)
{
QString strName = strHeaderList[j];
QString strValue = "";
auto findName = dataHash.find(strName);
if (findName != dataHash.end())
strValue = findName.value();
if (j < (strHeaderList.size() - 1))
strInsert = strInsert + QString(u8"'%1'").arg(strValue) + QString(",");
else
strInsert = strInsert + QString(u8"'%1'").arg(strValue) + QString(")");
}
bRet = excute(strInsert);
}
return bRet;
}
bool CSqlLiteObject::update(QString strTableName, QString strWhereName, QString strWhereValue, QString strUpdateName, QString strUpdateValue)
{
bool bRet = false;
if (strTableName.isEmpty())
return bRet;
QStringList strHeaderList;
auto findItem = m_strTableNameHeaderHash.find(strTableName);
if (findItem == m_strTableNameHeaderHash.end())
return bRet;
if (true)
{
QString strUpdate = QString(u8"UPDATE %1 SET %2='%3' WHERE %4='%5';")
.arg(strTableName)
.arg(strUpdateName)
.arg(strUpdateValue)
.arg(strWhereName)
.arg(strWhereValue);
bRet = excute(strUpdate);
}
return bRet;
}
bool CSqlLiteObject::remove(QString strTableName, QString strWhereName, QString strWhereValue)
{
bool bRet = false;
if (strTableName.isEmpty())
return bRet;
QStringList strHeaderList;
auto findItem = m_strTableNameHeaderHash.find(strTableName);
if (findItem == m_strTableNameHeaderHash.end())
return bRet;
if (true)
{
QString strUpdate = QString(u8"DELETE FROM %1 WHERE %2='%3';")
.arg(strTableName)
.arg(strWhereName)
.arg(strWhereValue);
bRet = excute(strUpdate);
}
return bRet;
}
QStringList CSqlLiteObject::fuzzySearch(QString strTableName, QString strName, QString strLike)
{
QStringList temp;
if (strTableName.isEmpty() || strTableName.isEmpty())
return temp;
QSqlDatabase db = openDb(m_strDatabaseName);
QString strConnectionName = db.connectionName();
QString strSelect("");
if (!strLike.isEmpty())
strSelect = QString("SELECT %1 FROM %2 WHERE %3 LIKE '%%4%' LIMIT 0,10;").arg(strName).arg(strTableName).arg(strName).arg(strLike);
else
return temp;
db.transaction(); // 开启事务查询
QSqlQuery query("", db);
query.exec(strSelect);
while (query.next())
{
QHash<QString, QString> keyValueHash;
for (size_t i = 0; i < query.record().count(); i++)
{
QString strName = query.record().fieldName(i);
QString strValue = query.record().value(i).toString();
if (!temp.contains(strValue) && (strLike != strValue))
temp << strValue;
}
}
db.commit();
closeDb();
return temp;
}