Qt下简单工厂模式实现多种数据库操作

简单工厂

基于简单的工厂设计模式，由单一工厂生产数据库连接基类的派生类对象。

数据库连接基类

配合Qt自带的数据库类。

#ifndef SQLCOMMON_H
#define SQLCOMMON_H

#include <QtSql>

class QSqlCommon
{
public:
    virtual bool connectDataBase(QString dbName, QString connectName, QString ip, QString user, QString pwd, int port, QString& errorinfo) = 0;
    virtual void disconnectDataBase();
    virtual QSqlDatabase& getDBHandle();
private:
    QSqlDatabase m_db;		// 数据库连接句柄
};

#endif // SQLCOMMON_H

cpp：

#include "sqlcommon.h"

void QSqlCommon::disconnectDataBase()
{
    if(m_db.open())
    {
        m_db.close();
    }
}

QSqlDatabase &QSqlCommon::getDBHandle()
{
    return m_db;
}

数据库连接派生类

#ifndef QSQLCONNECTPRODUCT_H
#define QSQLCONNECTPRODUCT_H

#include "sqlcommon.h"

class MySQLConnect: public QSqlCommon
{
public:
    bool connectDataBase(QString dbName, QString connectName, QString ip, QString user, QString pwd, int port, QString& errorinfo);

};

class SQLServerConnect: public QSqlCommon
{
public:
    bool connectDataBase(QString dbName, QString connectName, QString ip, QString user, QString pwd, int port, QString& errorinfo);
};

#endif // QSQLCONNECTPRODUCT_H

cpp：

#include "QSqlConnectProduct.h"

bool MySQLConnect::connectDataBase(QString dbName, QString connectName, QString ip, QString user, QString pwd, int port, QString& errorinfo)
{
    if(getDBHandle().isOpen())
    {
        getDBHandle().close();
    }

    getDBHandle() = QSqlDatabase::addDatabase("QMYSQL", connectName);
    getDBHandle().setDatabaseName(dbName);
    getDBHandle().setHostName(ip);
    getDBHandle().setUserName(user);
    getDBHandle().setPassword(pwd);
    getDBHandle().setPort(port);
    if(getDBHandle().open())
    {
        return true;
    }
    else
    {
        errorinfo = getDBHandle().lastError().text();
        return false;
    }

}

bool SQLServerConnect::connectDataBase(QString dbName, QString connectName, QString ip, QString user, QString pwd, int port, QString& errorinfo)
{
    if(getDBHandle().isOpen())
    {
        getDBHandle().close();
    }

    getDBHandle() = QSqlDatabase::addDatabase("QODBC", connectName);
    getDBHandle().setDatabaseName(QString("DRIVER={SQL SERVER};SERVER=%1;DATABASE=%2").arg(ip).arg(dbName));
    getDBHandle().setUserName(user);
    getDBHandle().setPassword(pwd);
    getDBHandle().setPort(port);
    if(getDBHandle().open())
    {
        return true;
    }
    else
    {
        errorinfo = getDBHandle().lastError().text();
        return false;
    }
}

数据库连接工厂类

#ifndef QSQLFACTORY_H
#define QSQLFACTORY_H

#include "QSqlConnectProduct.h"

class QSqlFactory
{

    QSqlFactory();
public:
    enum SQLTYPE
    {
        MYSQL = 0,
        SQLSERVER
    };
    static QSqlCommon*CreateConnect(SQLTYPE sqlType);
};

#endif // QSQLFACTORY_H

cpp：

#include "QSqlFactory.h"

QSqlFactory::QSqlFactory()
{

}

QSqlCommon *QSqlFactory::CreateConnect(QSqlFactory::SQLTYPE sqlType)
{
    QSqlCommon *pSqlConnect = NULL;
    switch(sqlType)
    {
    case MYSQL:
    {
        pSqlConnect = new MySQLConnect();
        break;
    }
    case SQLSERVER:
    {
        pSqlConnect = new SQLServerConnect();
        break;
    }
    default:
    {
        break;
    }
    }
    return pSqlConnect;
}

调用的时候只需要创建产品的基类指针,创建不同的类型连接，就可以操作多中数据库。

QSqlCommon *m_dbCommon;	// 成员对象

m_dbCommon = QSqlFactory::CreateConnect(QSqlFactory::SQLSERVER);
m_dbCommon = QSqlFactory::CreateConnect(QSqlFactory::MYSQL);
// 若要同时操作需要多个指针

结合Qt的Model-View可以对数据库进行快捷操作。

结合Qt类库操作数据库

比如我的UI界面创建了一个treewidget和tableview两个控件,举个例子，连接数据库后显示库名，表名等，双击表名显示数据的功能（Navicat：我有理由怀疑你再搞事情…）。
在比如按钮的槽函数(消息函数)中,

	m_dbCommon = QSqlFactory::CreateConnect(QSqlFactory::MYSQL);
	connect(ui->treeWidget, SIGNAL(itemDoubleClicked(QTreeWidgetItem*,int)), this, SLOT(onItemDoubleClicked(QTreeWidgetItem*,int)));
    QString errorInfo;
    if(m_dbCommon->connectDataBase("expressway", "mysqlConnection", "localhost", "root", "soloman", 3306, errorInfo))
    {
        qDebug() << "open mysql success";
        ui->treeWidget->clear();
        m_treeItemRoot = new QTreeWidgetItem(QStringList(m_dbCommon->getDBHandle().hostName()));
        QList<QTreeWidgetItem*> _itemList;
        QSqlQuery _query(m_dbCommon->getDBHandle());
        _query.exec("show databases;");
        while(_query.next())
        {
            QString _strName = _query.value(0).toString();
            QTreeWidgetItem *_tItem = new QTreeWidgetItem(m_treeItemRoot, QStringList(_strName));
            _itemList << _tItem;
        }
        m_treeItemRoot->setChildIndicatorPolicy(QTreeWidgetItem::ShowIndicator);

        foreach (QTreeWidgetItem* item, _itemList)
        {
            _query.exec(QString("show tables in %1;").arg(item->text(0)));
            while(_query.next())
            {
                QString _strName = _query.value(0).toString();
                QTreeWidgetItem *_childItem = new QTreeWidgetItem(item, QStringList(_strName));
                _childItem->setData(0, 0, _strName);
                _childItem->setData(0, 1, "mysqltable");
            }
        }
        ui->treeWidget->addTopLevelItem(m_treeItemRoot);
    }
    else
    {
        qDebug() << "failed open" << errorInfo;
    }

connect可以放在别的地方，这里只是告诉读者有这个操作。
在treewidget的item双击事件里，

void QSqlConnect::onItemDoubleClicked(QTreeWidgetItem *item, int column)
{
    if(item->data(0, 1).toString() == "mysqltable")
    {
        QString _str = QString("select * from %1 limit 0 ,1000").arg(item->text(column).trimmed());
        QSqlQuery _query(_str, m_dbCommon->getDBHandle());
        QSqlQueryModel *_queryModel = new QSqlQueryModel();
        _queryModel->setQuery(_query);
        ui->tableView->setModel(_queryModel);
    }
    else
    {
        QString _str = QString("select top 1000 * from dbo.%1").arg(item->text(column).trimmed());
        QSqlQuery _query(_str, m_dbCommon->getDBHandle());
        QSqlQueryModel *_queryModel = new QSqlQueryModel();
        _queryModel->setQuery(_query);
        ui->tableView->setModel(_queryModel);
    }
}

关于ModelView更多的操作与技巧，有兴趣的读者可以自行查阅相关资料。