QT支持多种数据库(Mysql，SqlServer，Oracle)根据配置文件动态加载相应库

一：数据库接口

 

#include <QVector>
#include <QMap>
#include <QVariant>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlError>
#include <QSqlDriver>

//#ifdef WINDOWS
//#define EXPORT __declspec(dllexport)
//#else
//#define EXPORT
//#endif

class ISql
{
public:
    virtual ~ISql(){}
    virtual bool Open(const QString& strHost,const QString& strUser,
                      const QString& strPasswd,const QString& strDbName, int nPort = 1521) = 0;
    virtual void Close() = 0;
    //增删改
    virtual bool Exec(const QString& strSql) = 0;
    virtual bool Add(const QString& strTable, const QMap<QString, QVariant> &mapData) = 0;
    virtual bool Remove(const QString& strTable, const QString & strWhere) = 0;
    virtual bool Update(const QString& strTable, const QMap<QString, QVariant> &mapData, const QString & strWhere) = 0;
    virtual QVector<QVector<QVariant>> Query(const QString& strSql) = 0;
    virtual QVector<QVector<QVariant>>  Query(const QString& strTable, const QStringList& listColumns, const QString& strWhere="") = 0;
    virtual bool IsOpen() = 0;
    virtual bool BeginTrans() = 0;
    virtual bool CommitTrans(const QVector<QString> &vecSql, bool bRollback = true) = 0;

protected:
    QSqlDatabase* m_pDB;
};

extern "C" {
    EXPORT ISql* Create(/*DATABASE_TYPE DBType*/);
    EXPORT void Release(ISql* pConnect);
}

 

二：OracleSql.h

#include "ISql.h"

class OracleSql : public ISql
{
public:
    OracleSql();

    bool Open(const QString& strHost,
                         const QString& strUser,
                         const QString& strPasswd,
                         const QString& strDbName,
                         int nPort = 1521) override;
    void Close() override;
    //增删改
    bool Exec(const QString& strSql) override;
    bool Add(const QString& strTable, const QMap<QString, QVariant> &mapData) override;
    bool Remove(const QString& strTable, const QString & strWhere) override;
    bool Update(const QString& strTable, const QMap<QString, QVariant> &mapData, const QString & strWhere) override;
    QVector<QVector<QVariant>> Query(const QString& strSql) override;
    QVector<QVector<QVariant>> Query(const QString& strTable, const QStringList& listColumns, const QString& strWhere="") override;
    bool IsOpen() override;
    bool BeginTrans() override;
    bool CommitTrans(const QVector<QString> &vecSql, bool bRollback = true) override;
};

三：OracleSql.cpp

#include "OracleSql.h"

ISql* Create(/*DATABASE_TYPE DBType*/)
{
    return new OracleSql();
}

void Release(ISql* pConnect)
{
    if (pConnect != nullptr)
    {
        delete pConnect;
        pConnect = nullptr;
    }
}

OracleSql::OracleSql()
{
    m_pDB = new QSqlDatabase(QSqlDatabase::addDatabase("QOCI"));
}

bool OracleSql::Open(const QString &strHost, const QString &strUser, const QString &strPasswd, const QString &strDbName, int nPort)
{
    if (!m_pDB->isOpen())
    {
        m_pDB->setHostName(strHost);
        m_pDB->setDatabaseName(strDbName);
        m_pDB->setUserName(strUser);
        m_pDB->setPassword(strPasswd);
        m_pDB->setPort(nPort);
        return m_pDB->open();
    }

    return true;
}

void OracleSql::Close()
{
    m_pDB->close();
    QSqlDatabase::removeDatabase(m_pDB->connectionName());
    delete m_pDB;
    m_pDB = nullptr;
}

bool OracleSql::Exec(const QString &strSql)
{
    if (!IsOpen())
    {
        return false;
    }

    QSqlQuery query;
    return query.exec(strSql);
}

bool OracleSql::Add(const QString &strTable, const QMap<QString, QVariant>& mapData)
{
    if (!IsOpen())
    {
        return false;
    }
    QString strColumns,strValues;

    QList<QString> listKeys = mapData.keys();
    foreach(const QString &strKey, listKeys)
    {
        if (!strColumns.isEmpty())
        {
            strColumns += ",";
        }
        strColumns += strKey;
        if (!strValues.isEmpty())
        {
            strValues += ",";
        }
        strValues += "?";
    }

    QSqlQuery query;
    QString strSql = QString("INSERT INTO %1 (%2) VALUES(%3)").arg(strTable).arg(strColumns).arg(strValues);
    query.prepare(strSql);

    for (int i=0; i<listKeys.size(); ++i)
    {
        query.bindValue(i, mapData.value(listKeys.at(i)));
    }

    return query.exec();
}

bool OracleSql::Remove(const QString &strTable, const QString &strWhere)
{
    if (!IsOpen())
    {
        return false;
    }

    QSqlQuery query;
    QString sql = QString("DELETE FROM %1 WHERE %2").arg(strTable).arg(strWhere);

    return query.exec(sql);
}

bool OracleSql::Update(const QString &strTable, const QMap<QString, QVariant> &mapData, const QString &strWhere)
{
    if (!IsOpen())
    {
        return false;
    }

    QSqlQuery query;
    QString datas;
    QList<QString> keyList = mapData.keys();
    foreach(QString key,keyList)
    {
        if(!datas.isEmpty())
        {
            datas += ",";
        }
        datas += QString("%1=?").arg(key);
    }

    QString sql;
    if(strWhere.isEmpty())
        sql = QString("UPDATE %1 SET %2").arg(strTable).arg(datas);
    else
        sql = QString("UPDATE %1 SET %2 WHERE %3").arg(strTable).arg(datas).arg(strWhere);
    query.prepare(sql);
    for(int i=0;i<keyList.count();++i)
    {
        query.bindValue(i, mapData.value(keyList.at(i)));
    }

    return query.exec();
}

QVector<QVector<QVariant>>  OracleSql::Query(const QString &strSql)
{
    QVector<QVector<QVariant>> vecRet;
    if (!IsOpen())
    {
        return vecRet;
    }

    QSqlQuery query;
    if (query.exec(strSql))
    {
        while (query.next())
        {
            QVector<QVariant> vecRow;
            int nRecod = query.record().count();
            for (int i=0; i<nRecod; ++i)
            {
                vecRow.push_back(query.value(i));
            }
            vecRet.push_back(vecRow);
        }
    }

    return vecRet;
}

QVector<QVector<QVariant>>  OracleSql::Query(const QString &strTable, const QStringList &listColumns, const QString &strWhere)
{
    QString strColumns;
    if (listColumns.size() > 0)
    {
        strColumns = listColumns.join(",");
    }
    else
    {
        strColumns = "*";
    }
    QString strSql;
    if(strWhere.isEmpty())
    {
        strSql = QString("SELECT %1 FROM %2").arg(strColumns).arg(strTable);
    }
    else
    {
        strSql = QString("SELECT %1 FROM %2 WHERE %3").arg(strColumns).arg(strTable).arg(strWhere);
    }

    return Query(strSql);
}

bool OracleSql::IsOpen()
{
    return m_pDB->isOpen();
}

bool OracleSql::BeginTrans()
{
    if (!m_pDB->isOpen())
    {
        return false;
    }

    return m_pDB->transaction();
}

bool OracleSql::CommitTrans(const QVector<QString> &vecSql, bool bRollback)
{
    if (!m_pDB->isOpen())
    {
        return false;
    }

    bool bRet = false;
    //m_pDB->transaction();
    QSqlQuery query;
    for (auto &strSql : vecSql)
    {
        bRet = query.exec(strSql);
        if (false == bRet)
        {
            if (bRollback)
            {
                m_pDB->rollback();
                break;
            }
        }
    }

    if (bRet)
    {
        //事务提交
        m_pDB->commit();
    }

    return bRet;
}

 

四：测试

#include <QCoreApplication>

#include <QDebug>

#include "OracleSql.h"

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

 

    ISql* pSql = Create();
    if (pSql->Open("196.168.66.87","ptuser3", "123456", "orcl", 1521))
    {
        qDebug()<<"open database success!";
    }
    else
    {
        qDebug()<<"open database failed!";
    }
    auto vec = pSql->Query("select * from customers");
    qDebug()<<vec.size();

    Release(pSql);

    return a.exec();
}

五：MySQL，SQLserver实现对应接口既可