EJM_Display/DataCenter/MySQL.cpp

#include "MySQL.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QDateTime>
#include <QLoggingCategory>
#include <QSqlRecord>
#include <GlobalDefinitions/Variable.h>
/* 方便统一加时间前缀 */
static QString timeStamp()
{
    return QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
}

MySQL::MySQL(QObject *parent) : QObject(parent) {}

bool MySQL::open(const QString &host,
                 int            port,
                 const QString &user,
                 const QString &pwd,
                 const QString &dbName)
{
    if (QSqlDatabase::contains("qt_mysql_conn"))
        db_ = QSqlDatabase::database("qt_mysql_conn");
    else
        db_ = QSqlDatabase::addDatabase("QMYSQL", "qt_mysql_conn");

    db_.setHostName(host);
    db_.setPort(port);
    db_.setDatabaseName(dbName);
    db_.setUserName(user);
    db_.setPassword(pwd);

    if (!db_.open()) {
        qWarning() << timeStamp() << "【错误】连接失败：" << db_.lastError().text();
        return false;
    }
    qInfo() << timeStamp() << "【信息】数据库连接成功";
    return true;
}

void MySQL::close()
{
    db_.close();
    qInfo() << timeStamp() << "【信息】数据库已关闭";
}

bool MySQL::createTable(const QString &tableName,
                        const QMap<QString, QString> &otherFields)
{
    if (otherFields.isEmpty()) {
        qWarning() << timeStamp() << "【错误】字段信息为空，无法建表";
        return false;
    }

    QStringList fields;
    fields << "`00_记录时间` DATETIME DEFAULT CURRENT_TIMESTAMP";
    for (auto it = otherFields.begin(); it != otherFields.end(); ++it)
        fields << QString("`%1` %2").arg(it.key(), it.value());

    QString sql = QString("CREATE TABLE IF NOT EXISTS `%1` (%2) "
                          "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")
                      .arg(tableName, fields.join(", "));

    QSqlQuery q(db_);
    if (!q.exec(sql)) {
        qWarning() << timeStamp() << "【错误】建表失败：" << q.lastError().text();
        return false;
    }
    //qInfo() << timeStamp() << "【信息】表" << tableName << "创建/确认成功";
    return true;
}

bool MySQL::insertPartialV(const QString &tableName,
                          const QStringList &fieldNames,
                          const QVariantList &values)
{
    if (fieldNames.size() != values.size()) {
        qWarning() << timeStamp() << "【错误】字段名与值数量不一致";
        return false;
    }

    QStringList ph;
    for (int i = 0; i < values.size(); ++i) ph << "?";
    QString placeHolders = ph.join(",");

    QStringList cols;
    for (const QString &f : fieldNames)
        cols << QString("`%1`").arg(f);
    QString colStr = cols.join(",");

    QString sql = QString("INSERT INTO `%1` (%2) VALUES (%3)")
                      .arg(tableName, colStr, placeHolders);

    QSqlQuery q(db_);
    q.prepare(sql);
    for (const QVariant &v : values) q.addBindValue(v);

    if (!q.exec()) {
        qWarning() << timeStamp() << "【错误】插入指定字段失败：" << q.lastError().text();
        return false;
    }
    return true;
}
bool MySQL::insertPartial(const QString &tableName,
                          const QStringList &fieldNames,
                          const QStringList &values)
{
    if (fieldNames.size() != values.size()) {
        qWarning() << timeStamp() << "字段与值数量不一致";
        return false;
    }

    QStringList cols;
    for (const QString &f : fieldNames)
        cols << QString("`%1`").arg(f);
    QString colStr = cols.join(",");

    QStringList ph;
    for (int i = 0; i < values.size(); ++i)
        ph << "?";                     // 一个字段对应一个 ?
    QString placeholders = ph.join(",");

    QString sql = QString("INSERT INTO `%1` (%2) VALUES (%3)")
                      .arg(tableName, colStr, placeholders);

    QSqlQuery q(db_);
    q.prepare(sql);
    for (const QString &v : values) q.addBindValue(v); // 保证传字符串

    if (!q.exec()) {
        qWarning() << timeStamp() << "插入失败：" << q.lastError().text();
        return false;
    }
    return true;
}
bool MySQL::insertFull(const QString &tableName,
                       const QVariantList &values)
{
    if (values.isEmpty()) {
        qWarning() << timeStamp() << "【错误】插入值列表为空";
        return false;
    }

    /* 1. 在最前面补当前时间 */
    QVariantList realValues;
    realValues << QDateTime::currentDateTime();
    realValues.append(values);

    /* 2. 生成对应占位符 */
    QStringList ph;
    for (int i = 0; i < realValues.size(); ++i) ph << "?";
    QString placeHolders = ph.join(",");

    QString sql = QString("INSERT INTO `%1` VALUES (%2)")
                      .arg(tableName, placeHolders);

    QSqlQuery q(db_);
    q.prepare(sql);
    for (const QVariant &v : realValues) q.addBindValue(v);

    if (!q.exec()) {
        qWarning() << timeStamp() << "【错误】插入全部字段失败：" << q.lastError().text();
        return false;
    }
    qInfo() << timeStamp() << "【信息】插入全部字段成功";
    return true;
}
/* 读取最新 maxRows 条完整记录（不足时有多少给多少） */
QList<QVariantList> MySQL::selectLatest(const QString &tableName,
                                        int maxRows)
{
    QList<QVariantList> rows;

    QString sql = QString(
        "SELECT * FROM `%1` ORDER BY `00_记录时间` DESC LIMIT %2")
        .arg(tableName).arg(maxRows);

    QSqlQuery q(db_);
    if (!q.exec(sql)) {
        qWarning() << timeStamp() << "【错误】读取最新记录失败：" << q.lastError().text();
        return rows;
    }

    while (q.next()) {
        QVariantList cols;
        for (int i = 0; i < q.record().count(); ++i)
            cols << q.value(i);
        rows << cols;
    }
    return rows;
}

/* 读取最新 maxRows 条，只拿指定字段 */
QList<QVariantList> MySQL::selectLatest(const QString &tableName,
                                        const QStringList &fieldNames,
                                        int maxRows)
{
    QList<QVariantList> rows;

    QString colStr = fieldNames.join("`,`").prepend("`").append("`");
    QString sql = QString(
        "SELECT %1 FROM `%2` ORDER BY `00_记录时间` DESC LIMIT %3")
        .arg(colStr, tableName).arg(maxRows);

    QSqlQuery q(db_);
    if (!q.exec(sql)) {
        qWarning() << timeStamp() << "【错误】读取最新记录(指定字段)失败：" << q.lastError().text();
        return rows;
    }

    while (q.next()) {
        QVariantList cols;
        for (const QString &f : fieldNames)
            cols << q.value(f);
        rows << cols;
    }
    return rows;
}