基于Qt实现的数据库链接池

只需要修改void CDatabaseConnectionPool::CreateConnection()接口中对应的驱动，即可适应任何Qt驱动支持的数据库，代码留给需要的人。

#ifndef DataBaseConnectMgr_h__
#define DataBaseConnectMgr_h__

#include <QSqlDatabase>
#include <QVariantList>

class QString;
class CDatabaseHandler
{
public:
	CDatabaseHandler();

	~CDatabaseHandler();

	static bool ConnectDB();

	static bool IsConnected(QSqlDatabase& db);

	static bool Transaction(QSqlDatabase& db);

	static bool Commit(QSqlDatabase& db);

	static bool Rollback(QSqlDatabase& db);

	static bool DisconnectDB(QSqlDatabase& db);

	static QVector<QVariantList> SelectQuery(QSqlDatabase& db, const QString& strQuery);

	static bool Exec(QSqlDatabase& db, const QString& strQuery);

	static bool ExecByPrePare(QSqlDatabase& db, const QString& strQuery, const QVariantList& params);

	static bool ExecByPrePare(QSqlDatabase& db, const QString& strQuery, const QList<QVariantList>& params);

};

#endif // DataBaseConnectMgr_h__

#include "DatabaseHandler.h"

#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>
#include <qDebug>
#include <QCoreApplication>

CDatabaseHandler::CDatabaseHandler()
{

}

CDatabaseHandler::~CDatabaseHandler()
{

}

bool CDatabaseHandler::IsConnected(QSqlDatabase& db)
{
	return db.isOpen();
}

bool CDatabaseHandler::Transaction(QSqlDatabase& db)
{
	// 开始事务
	if (!db.transaction())
	{
		qDebug() << "Failed to start transaction:" << db.lastError().text().toLocal8Bit();
		return false;
	}
	return true;
}

bool CDatabaseHandler::Commit(QSqlDatabase& db)
{
	if (!db.commit())
	{
		qDebug() << "Failed to commit transaction:" << db.lastError().text().toLocal8Bit();
		return false;
	}
	return true;
}

bool CDatabaseHandler::Rollback(QSqlDatabase& db)
{
	if (!db.isOpen())
	{
		qDebug() << "Database is not open, cannot rollback";
		return false;
	}

	if (!db.rollback())
	{
		qDebug() << "Failed to rollback transaction:" << db.lastError().text().toLocal8Bit();
		return false;
	}
	LogInfo() << "Transaction rolled back successfully";

	return true;
}

bool CDatabaseHandler::DisconnectDB(QSqlDatabase& db)
{
	if (db.isOpen())
	{
		db.close();
		qDebug() << "Database disconnected successfully";
	}
	else
	{
		qDebug() << "Database is not open, no need to disconnect";
	}

	// Removing the database connection from the connection pool
	const QString strConnectionName = db.connectionName();
	QSqlDatabase::removeDatabase(strConnectionName);

	return true;
}

QVector<QVariantList> CDatabaseHandler::SelectQuery(QSqlDatabase& db, const QString& strQuery)
{
	QVector<QVariantList> vecValues;
	QSqlQuery query(db);
	if (!query.exec(strQuery))
	{
		qDebug() << "Exec failed:" << query.lastError().text().toLocal8Bit();
		return vecValues;
	}

	QSqlRecord record = query.record();
	int nCount = record.count();

	while (query.next())
	{
		QVariantList variantList;

		for (int i = 0; i < nCount; i++)
		{
			QVariant value = query.value(i);
			variantList.append(value);
		}

		vecValues.append(variantList);
	}

	return vecValues;
}

bool CDatabaseHandler::Exec(QSqlDatabase& db, const QString& strQuery)
{
	QSqlQuery query(db);
	if (!query.exec(strQuery))
	{
		qDebug() << "Exec failed:" << query.lastError().text().toLocal8Bit();
		return false;
	}
	return true;
}

bool CDatabaseHandler::ExecByPrePare(QSqlDatabase& db, const QString& strQuery, const QVariantList& params)
{
	QSqlQuery query(db);
	query.prepare(strQuery);

	for (int i = 0; i < params.size(); ++i)
	{
		query.addBindValue(params.at(i));
	}

	if (!query.exec())
	{
		qDebug() << "ExecByPrePare failed:" << query.lastError().text().toLocal8Bit();
		return false;
	}
	return true;
}

bool CDatabaseHandler::ExecByPrePare(QSqlDatabase& db, const QString& strQuery, const QList<QVariantList>& params)
{
	QSqlQuery query(db);
	query.prepare(strQuery);

	for (int i = 0; i < params.size(); ++i)
	{
		QVariantList values = params.at(i);
		for (int j = 0; j < values.size(); ++j)
		{
			query.addBindValue(values.at(j));
		}

		if (!query.exec())
		{
			qDebug() << "ExecByPrePare failed:" << query.lastError().text().toLocal8Bit();
			return false;
		}

		query.finish();
	}

	return true;
}

#ifndef DATABASE_CONNECTION_POOL_H
#define DATABASE_CONNECTION_POOL_H

#include <QQueue>
#include <QMutex>
#include <QSettings>
#include <QSqlDatabase>
#include <QWaitCondition>

class CDatabaseConnectionPool : public QObject
{
	Q_OBJECT
public:
	static CDatabaseConnectionPool& Instance();

	QSqlDatabase GetConnection();

	void ReleaseConnection(QSqlDatabase db);

private:
	explicit CDatabaseConnectionPool(QObject *parent = NULL);

	~CDatabaseConnectionPool();

	void CreateConnection();

	void RemoveConnection(QSqlDatabase db);

	void PrintSqlError(QSqlDatabase& db);

private:
	static CDatabaseConnectionPool* m_pInstance;
	int m_nMaxConnections;
	QMutex m_mutex;
	QWaitCondition m_condition;
	QQueue<QSqlDatabase> m_connectionPool;
};

#endif // DATABASE_CONNECTION_POOL_H

#include "DatabaseConnectionPool.h"

#include <QCoreApplication>
#include <qDebug>

QString strHostName = "192.168.1.1";
QString strDatabaseName = "test";
QString strUserName = "root";
QString strPassword = "test";
QString strPort = "3306";

const int nMaxConnectCount = 8;

CDatabaseConnectionPool* CDatabaseConnectionPool::m_pInstance = NULL;

CDatabaseConnectionPool& CDatabaseConnectionPool::Instance()
{
	if (NULL == m_pInstance)
	{
		static CDatabaseConnectionPool pool;
		m_pInstance = &pool;
	}
	return *m_pInstance;
}

CDatabaseConnectionPool::CDatabaseConnectionPool(QObject *parent) : QObject(parent)
{
	m_nMaxConnections = nMaxConnectCount;

	// Create initial connections
	for (int i = 0; i < m_nMaxConnections; ++i)
	{
		CreateConnection();
	}
}

CDatabaseConnectionPool::~CDatabaseConnectionPool()
{
	while (!m_connectionPool.isEmpty())
	{
		RemoveConnection(m_connectionPool.dequeue());
	}
}

QSqlDatabase CDatabaseConnectionPool::GetConnection()
{
	m_mutex.lock();
	while (m_connectionPool.isEmpty())
	{
		m_condition.wait(&m_mutex);
	}
	QSqlDatabase db = m_connectionPool.dequeue();
	m_mutex.unlock();
	return db;
}

void CDatabaseConnectionPool::ReleaseConnection(QSqlDatabase db)
{
	if (db.isValid() && db.isOpen())
	{
		m_mutex.lock();
		m_connectionPool.enqueue(db);
		m_mutex.unlock();
		m_condition.wakeOne();
	}
}

void CDatabaseConnectionPool::CreateConnection()
{
	static int nCount = 0;

	// mysql链接代码
	QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", QString("%1").arg(nCount));
	db.setHostName(strHostName);
	db.setDatabaseName(strDatabaseName);
	db.setUserName(strUserName);
	db.setPassword(strPassword);
	db.setPort(strPort.toInt());

	// sqlite链接代码
	//QString strDBFileName = QString("TestData.db").arg(nCount);
	//QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", QString("%1").arg(nCount));

	// 设置数据库文件的路径
	//db.setDatabaseName(strDBFileName);

	if (db.open())
	{
		m_connectionPool.enqueue(db);
	}
	else
	{
		qDebug() << "Failed to open database connection!";
	}

	nCount++;
}

void CDatabaseConnectionPool::RemoveConnection(QSqlDatabase db)
{
	if (db.isOpen())
	{
		db.close();
	}
	QSqlDatabase::removeDatabase(db.connectionName());
}