emsApplication/applications/emsConfigurer/mysqlutils.cpp

#include "mysqlutils.h"
#include <QDebug>

#include <hv/hlog.h>
#include "ziputils.h"
#include "kutilities.h"

#ifdef _USING_MYSQL_CONNECTOR_
#include <mysql_connection.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#endif

MysqlUtils MysqlUtils::m_instance;

MysqlUtils::MysqlUtils()
#ifdef _USING_MYSQL_CONNECTOR_
    :m_DbConnection(nullptr)
#endif

#ifdef _USING_MYSQL_51_LIB_
         :m_DbConnection(nullptr)
#endif
{}

MysqlUtils::~MysqlUtils()
{
    CloseDatabase();
}

MysqlUtils* MysqlUtils::getInstance()
{
    return &m_instance;
}

bool MysqlUtils::OpenDatabase(const std::string& server, int dbport, const std::string& dbuser, const std::string& dbpasswd, const std::string& database)
{
    bool ok = false;
#ifdef _USING_QT_MYSQL_CONNECTOR_
    //打开数据库
    if (m_DbConnection.isOpen() && m_DbConnection.isValid())
    {
        hlogd("database already connected.");
        return true;
    }

    m_DbConnection = QSqlDatabase::addDatabase("QMYSQL");
    m_DbConnection.setHostName(server.c_str());
    m_DbConnection.setDatabaseName(database.c_str());
    m_DbConnection.setUserName(dbuser.c_str());
    m_DbConnection.setPassword(dbpasswd.c_str());
    m_DbConnection.setPort(dbport);
    ok = m_DbConnection.open();
#endif

#ifdef _USING_MYSQL_CONNECTOR_
    //创建连接
    sql::Driver *driver = nullptr;
    driver = get_driver_instance();

    sql::ConnectOptionsMap connection_properties;

    driver->connect("tcp://192.168.23.253:3306", "root", "L2ysc1s1kr");
    m_DbConnection->setSchema(database.c_str());

    // connection_properties["hostName"] = server;
    // connection_properties["userName"] = dbuser;
    // connection_properties["password"] = dbpasswd;
    // connection_properties["schema"] = database;
    // connection_properties["port"] = dbport;
    // //connection_properties["OPT_RECONNECT"] = true;

    // m_DbConnection = driver->connect(connection_properties);

    if (m_DbConnection)
        ok = true;
#endif

#ifdef _USING_MYSQL_51_LIB_
    m_DbConnection = mysql_init(NULL);
    //连接到MySQL服务器
    if (!mysql_real_connect(m_DbConnection, server.c_str(), dbuser.c_str(), dbpasswd.c_str(), database.c_str(), dbport, NULL, 0))
    {
        hloge("MySQL connection error: %s",mysql_error(m_DbConnection));
    }
    else
    {
        ok = true;
        mysql_set_character_set(m_DbConnection, "utf8");
    }
#endif

    if (!ok)
    {
        hloge("Failed to connect to database.");
    }
    else
    {
        hlogd("new database connection created successfully!");
#if 0
        MYSQL_RES* res;
        MYSQL_ROW row;
        //执行SQL查询
        if (mysql_query(m_DbConnection, "SELECT * FROM tbl_device"))
        {
            qDebug() << "Error: " << mysql_error(m_DbConnection) ;
            mysql_close(m_DbConnection);
            return 1;
        }

        res = mysql_use_result(m_DbConnection);

        while ((row = mysql_fetch_row(res)) != NULL)
        {
            for (int i = 0; i < mysql_num_fields(res); ++i)
            {
                qDebug() << row[i] << "\t";
            }
            qDebug() << "\r\n";
        }

        mysql_free_result(res);
#endif
    }

    return ok;

}

void MysqlUtils::CloseDatabase()
{
#ifdef _USING_QT_MYSQL_CONNECTOR_
    if (m_DbConnection.isOpen())
        m_DbConnection.close();
#endif

#ifdef _USING_MYSQL_CONNECTOR_
    if (m_DbConnection)
    {
        m_DbConnection->close();
    }
#endif

#ifdef _USING_MYSQL_51_LIB_
    mysql_close(m_DbConnection);
#endif
}

int MysqlUtils::RetrieveTableData(TableData& tbl_data)
{
#if 1
    MYSQL_RES* res;
    MYSQL_ROW row;
    //执行SQL 查询
    if (mysql_query(m_DbConnection, "SELECT * FROM `hjems`.`tbl_data` ORDER BY data_timestamp LIMIT 0,100"))
    {
        hloge( "Error: %s", mysql_error(m_DbConnection) ) ;
        return -1;
    }

    res = mysql_use_result(m_DbConnection);

    while ((row = mysql_fetch_row(res)) != NULL)
    {
        ModelItem item;
        // item.status = STATUS_WARN;
        // item.ParameterName= "Visual Leak Detector detected 1 memory leak (84 bytes)";
        // item.sampleTime = "2024-9-10 11:22:33.444";
        // item.unit = "C";
        // item.value = "100";
        // tbl_data.append(item);

        std::string buf;
        for (unsigned int i = 0; i < mysql_num_fields(res); ++i)
        {
            qDebug() << mysql_fetch_field_direct(res,i)->name ;
            QString field_name (mysql_fetch_field_direct(res,i)->name);
            if (field_name == "data_timestamp")
            {
                item.sampleTime = row[i];
                qDebug() << row[i];
            }
            else if (field_name == "data_content")
            {
                std::string v(row[i]);
                qDebug() << Kutilities::printHex(v.c_str(),v.length()).c_str();
                ZipUtils::DecompressString(v.c_str(),v.length(),buf);
                qDebug() << buf.c_str();
            }
        }
        qDebug() << "\r\n";
    }

    unsigned long row_count = res->row_count;

    mysql_free_result(res);

    return row_count;
#endif
}