#include "dbcache.h"
|
|
#include <QCryptographicHash>
|
#include <QJsonDocument>
|
#include <QDebug>
|
#include <QSqlQuery>
|
#include <QSqlError>
|
#include <QJsonArray>
|
#include <QJsonObject>
|
#include <QSqlRecord>
|
|
DBCache::DBCache(QObject *parent) : QObject(parent),m_redisContext(nullptr)
|
{
|
|
}
|
|
DBCache::~DBCache()
|
{
|
if (m_redisContext) {
|
redisFree(m_redisContext);
|
}
|
|
if (m_mysqlDB.isOpen()) {
|
m_mysqlDB.close();
|
}
|
}
|
|
bool DBCache::initMySQL(const QString &host, int port, const QString &user, const QString &password, const QString &database)
|
{
|
m_mysqlDB = QSqlDatabase::addDatabase("QMYSQL");
|
m_mysqlDB.setHostName(host);
|
m_mysqlDB.setPort(port);
|
m_mysqlDB.setUserName(user);
|
m_mysqlDB.setPassword(password);
|
m_mysqlDB.setDatabaseName(database);
|
|
if (!m_mysqlDB.open()) {
|
qCritical() << "Failed to connect to MySQL:" << m_mysqlDB.lastError().text();
|
return false;
|
}
|
|
// 设置连接参数优化大量数据查询
|
QSqlQuery query(m_mysqlDB);
|
query.exec("SET SESSION wait_timeout=28800");
|
query.exec("SET SESSION interactive_timeout=28800");
|
query.exec("SET NAMES 'utf8mb4'");
|
|
return true;
|
}
|
|
bool DBCache::initRedis(const QString &host, int port, const QString &password)
|
{
|
struct timeval timeout = { 1, 500000 }; // 1.5秒超时
|
m_redisContext = redisConnectWithTimeout(host.toUtf8().constData(), port, timeout);
|
|
if (m_redisContext == nullptr || m_redisContext->err) {
|
if (m_redisContext) {
|
qCritical() << "Redis connection error:" << m_redisContext->errstr;
|
redisFree(m_redisContext);
|
m_redisContext = nullptr;
|
} else {
|
qCritical() << "Can't allocate Redis context";
|
}
|
return false;
|
}
|
|
// 如果有密码，进行认证
|
if (!password.isEmpty()) {
|
redisReply *reply = (redisReply *)redisCommand(m_redisContext, "AUTH %s", password.toUtf8().constData());
|
if (reply == nullptr || reply->type == REDIS_REPLY_ERROR) {
|
qCritical() << "Redis authentication failed";
|
freeReplyObject(reply);
|
return false;
|
}
|
freeReplyObject(reply);
|
}
|
|
return true;
|
}
|
|
QVector<QHash<QString, QVariant> > DBCache::query(const QString &sql)
|
{
|
QString key = generateCacheKey(sql);
|
|
// 1. 尝试从Redis获取
|
QVector<QHash<QString, QVariant>> result = getFromRedis(key);
|
if (!result.isEmpty()) {
|
qDebug() << "Cache hit for query:" << sql;
|
return result;
|
}
|
|
qDebug() << "Cache miss for query:" << sql;
|
|
// 2. 从MySQL查询
|
result = queryMySQL(sql);
|
if (result.isEmpty()) {
|
return result;
|
}
|
|
// 3. 将结果存入Redis
|
if (!setToRedis(key, result)) {
|
qWarning() << "Failed to cache query result in Redis";
|
}
|
|
return result;
|
}
|
|
void DBCache::clearCache()
|
{
|
if (!m_redisContext) {
|
return;
|
}
|
|
redisReply *reply = (redisReply *)redisCommand(m_redisContext, "FLUSHDB");
|
if (reply == nullptr || reply->type == REDIS_REPLY_ERROR) {
|
qWarning() << "Failed to clear Redis cache";
|
}
|
freeReplyObject(reply);
|
}
|
|
QString DBCache::generateCacheKey(const QString &sql) const
|
{
|
// 使用SHA1哈希生成唯一的缓存键
|
QByteArray hash = QCryptographicHash::hash(sql.toUtf8(), QCryptographicHash::Sha1);
|
return "sql_cache:" + hash.toHex();
|
}
|
|
QVector<QHash<QString, QVariant> > DBCache::getFromRedis(const QString &key)
|
{
|
if (!m_redisContext) {
|
return QVector<QHash<QString, QVariant>>();
|
}
|
|
redisReply *reply = (redisReply *)redisCommand(m_redisContext, "GET %s", key.toUtf8().constData());
|
if (reply == nullptr || reply->type == REDIS_REPLY_ERROR) {
|
if (reply) {
|
qWarning() << "Redis GET error:" << reply->str;
|
freeReplyObject(reply);
|
}
|
return QVector<QHash<QString, QVariant>>();
|
}
|
|
if (reply->type == REDIS_REPLY_NIL) {
|
freeReplyObject(reply);
|
return QVector<QHash<QString, QVariant>>(); // 缓存未命中
|
}
|
|
// 解析JSON格式的缓存数据
|
QJsonParseError error;
|
QJsonDocument doc = QJsonDocument::fromJson(QByteArray(reply->str, reply->len), &error);
|
freeReplyObject(reply);
|
|
if (error.error != QJsonParseError::NoError) {
|
qWarning() << "Failed to parse cached JSON:" << error.errorString();
|
return QVector<QHash<QString, QVariant>>();
|
}
|
|
QVector<QHash<QString, QVariant>> result;
|
QJsonArray array = doc.array();
|
for (const QJsonValue &value : array) {
|
QJsonObject obj = value.toObject();
|
QHash<QString, QVariant> row;
|
for (auto it = obj.begin(); it != obj.end(); ++it) {
|
row[it.key()] = it.value().toVariant();
|
}
|
result.append(row);
|
}
|
|
return result;
|
}
|
|
bool DBCache::setToRedis(const QString &key, const QVector<QHash<QString, QVariant> > &data)
|
{
|
if (!m_redisContext) {
|
return false;
|
}
|
|
// 将数据转换为JSON格式
|
QJsonArray array;
|
for (const auto &row : data) {
|
QJsonObject obj;
|
for (auto it = row.begin(); it != row.end(); ++it) {
|
obj[it.key()] = QJsonValue::fromVariant(it.value());
|
}
|
array.append(obj);
|
}
|
|
QJsonDocument doc(array);
|
QByteArray json = doc.toJson(QJsonDocument::Compact);
|
|
// 使用管道批量操作提高性能
|
redisAppendCommand(m_redisContext, "MULTI");
|
redisAppendCommand(m_redisContext, "SET %s %b", key.toUtf8().constData(), json.constData(), json.size());
|
redisAppendCommand(m_redisContext, "EXPIRE %s 3600", key.toUtf8().constData()); // 1小时过期
|
redisAppendCommand(m_redisContext, "EXEC");
|
|
// 读取所有响应
|
redisReply *reply = nullptr;
|
for (int i = 0; i < 4; ++i) {
|
if (redisGetReply(m_redisContext, (void**)&reply) != REDIS_OK) {
|
qWarning() << "Redis pipeline error";
|
if (reply) freeReplyObject(reply);
|
return false;
|
}
|
freeReplyObject(reply);
|
}
|
|
return true;
|
}
|
|
QVector<QHash<QString, QVariant> > DBCache::queryMySQL(const QString &sql)
|
{
|
QVector<QHash<QString, QVariant>> results;
|
|
QSqlQuery query(m_mysqlDB);
|
if (!query.exec(sql)) {
|
qCritical() << "MySQL query failed:" << query.lastError().text();
|
return results;
|
}
|
|
// 获取列名
|
QSqlRecord record = query.record();
|
QStringList columnNames;
|
for (int i = 0; i < record.count(); ++i) {
|
columnNames << record.fieldName(i);
|
}
|
|
// 分批获取结果，避免内存问题
|
const int batchSize = 1000;
|
int count = 0;
|
while (query.next()) {
|
QHash<QString, QVariant> row;
|
for (const QString &column : columnNames) {
|
row[column] = query.value(column);
|
}
|
results.append(row);
|
|
// 分批处理
|
if (++count % batchSize == 0) {
|
//qDebug() << "Processed" << count << "rows...";
|
}
|
}
|
|
return results;
|
}
|
