Qt提供一组访问关系型数据库的通用类:QSqlDataBase表示一个数据库连接,更底层通过驱动(drivers)与不同的数据库api就行交互。创建了QSqlDatabase对象之后,使用setDatabaseName()、setUserName()、setPassword()、setHostName()、setPort()和setConnectOptions()设置连接参数。然后调用open()来激活到数据库的物理连接。该连接直到open陈功才能使用的。强烈建议不要保留QSqlDatabase的副本作为类的成员,因为这将阻止实例在关闭时被正确清理。如果需要访问一个现有的QSqlDatabase,应该使用database()来访问它。如果您选择使用QSqlDatabase成员变量,则需要在删除QCoreApplication实例之前删除该成员变量,否则可能会导致未定义的行为。如果创建多个数据库连接,请在调用addDatabase()时为每个连接指定唯一的连接名称。使用带有连接名称的database()来获得该连接。使用带有连接名称的removeDatabase()来删除连接。如果试图删除由其他QSqlDatabase对象引用的连接,QSqlDatabase将输出一个警告。使用contains()查看给定的连接名是否在连接列表中。
目前可通过QSqlDatabase::drivers()获取当前可用的数据库驱动。一般提供如下几种:"QSQLITE", "QODBC", "QODBC3", "QPSQL", "QPSQL7"
添加Mysql数据库驱动
第一:编译安装mysql数据库:直接到官网下载
第二:以Qt安装路径是D:\Qt为例:打开Qt安装路径:D:\Qt\5.15.2\Src\qtbase\src\plugins\sqldrivers\mysql,编译mysql驱动工程。注释mysql.pro 第六行#QMAKE_USE += mysql并且添加mysql相关头文件和lib库文件INCLUDEPATH += D:\Devlib_2019\mysql\includeLIBS += D:\Devlib_2019\mysql\lib\libmysql.lib 设置编译生成的路径DESTDIR = ../mysql/lib/,防止找半天生成的文件
第三:将生成的mysql驱动的lib、dll文件放入D:\Qt\5.15.2\msvc2019\plugins\sqldrivers
最后:还需要将mysql的dll文件放入D:\Qt\5.15.2\msvc2019\bin,至此也可通过QSqlDatabase访问mysql数据库了。
以下提供使用QSqlDatabase维护的一个数据库连接池代码:
#ifndef DBCONNECTIONPOOL_H
#define DBCONNECTIONPOOL_H
#include <QFlags>
#include <QObject>
#include <QMutex>
#include <QSqlDatabase>
class DBConnectionPool
{
public:
typedef enum{
MYSQL,
SQLITE,
OCCI,
PGSQL
} ConnectType;
Q_ENUMS(ConnectType)
DBConnectionPool();
~DBConnectionPool();
//databaseName sqlite数据库绝对路
bool initConnectPool(const
void setHostName(const QStr
void setUserName(const QStr
void setPassWord(const QStr
void setPort(int port);
QSqlDatabase getDbConnectio
//释放连接
void releaseConnection(cons
static DBConnectionPool* ge
protected:
QSqlDatabase getConnection(
void addDatabaseType(const
private:
Q_DISABLE_COPY(DBConnection
// 数据库信息
QString m_hostName;
QString m_databaseName;
QString m_username;
QString m_password;
QString m_databaseType;
int m_port;
int m_nMaxConnCount
QStringList m_connectionNam
DBConnectionPool::ConnectTy
QString m_testSql;
static QMutex m_mutex;
};
#endif // DBCONNECTIONPOOL_H
#include "dbconnectionpool.h"
#include <QtDebug>
#include <QSqlQuery>
#include <QSqlError>
#include <QDateTime>
QMutex DBConnectionPool::m_mutex;
DBConnectionPool::DBConnectionPool()
:m_nMaxConnCount(3)
,m_testSql("SELECT 1")
{
qInfo()<<"drivers:"<<QSqlDatabase::drivers();
}
DBConnectionPool::~DBConnectionPool()
{
for (auto &k : m_connectionNames)
QSqlDatabase::removeDatabase(k);
}
bool DBConnectionPool::initConnectPool(const ConnectType &type, const QString &databaseName, int maxConn)
{
m_databaseName = databaseName;
m_nMaxConnCount = maxConn;
m_type = type;
for (int i = 0; i < m_nMaxConnCount; ++i)
{
qInfo()<<"begin..."<<QDateTime::currentDateTime();
addDatabaseType(type, databaseName, i);
qInfo()<<"end..."<<QDateTime::currentDateTime();
}
return (m_connectionNames.size() == m_nMaxConnCount);
}
void DBConnectionPool::setHostName(const QString &hostName)
{
m_hostName = hostName;
}
void DBConnectionPool::setUserName(const QString &username)
{
m_username = username;
}
void DBConnectionPool::setPassWord(const QString &password)
{
m_password = password;
}
void DBConnectionPool::setPort(int port)
{
m_port = port;
}
QSqlDatabase DBConnectionPool::getDbConnection()
{
QMutexLocker lock(&m_mutex);
if (!m_connectionNames.isEmpty())
{
return getConnection();
}
addDatabaseType(m_type, m_databaseName,m_nMaxConnCount);
m_nMaxConnCount++;
return getConnection();
}
void DBConnectionPool::releaseConnection(const QSqlDatabase &db)
{
QMutexLocker lock(&m_mutex);
m_connectionNames.append(db.connectionName());
}
DBConnectionPool *DBConnectionPool::getInstance()
{
static DBConnectionPool instance;
return &instance;
}
inline QSqlDatabase DBConnectionPool::getConnection()
{
QString connection = m_connectionNames.front();
QSqlDatabase db = QSqlDatabase::database(connection);
QSqlQuery query(m_testSql, db);
if (query.lastError().type() != QSqlError::NoError && !db.open())
{
qWarning() << "Open datatabase error:" << db.lastError().text();
return QSqlDatabase();
}
return db;
}
void DBConnectionPool::addDatabaseType(const ConnectType &type, const QString &databaseName,int connIdx)
{
QSqlDatabase db;
QString connStr;
switch (type) {
case DBConnectionPool::ConnectType::MYSQL:
{
connStr = QString("QMYSQL_Connection-%1").arg(connIdx);
db = QSqlDatabase::addDatabase("QMYSQL", connStr);
db.setDatabaseName(databaseName);
db.setHostName(m_hostName);
db.setPort(m_port);
if (!db.open(m_username, m_password)) {
qDebug() << "Open datatabase error:" << db.lastError().text();
return;
}
}
break;
case DBConnectionPool::ConnectType::SQLITE:
{
connStr = QString("QSQLITE_Connection-%1").arg(connIdx);
db = QSqlDatabase::addDatabase("QSQLITE", connStr);
db.setDatabaseName("D:/QtProject/build-untitled1-Desktop_Qt_5_15_2_MSVC2019_64bit-Debug/test.db");
db.setUserName("root");
db.setPassword("123456");
if (!db.open()) {
qDebug() << "Open datatabase error:" << db.lastError().text();
return;
}
}
break;
case DBConnectionPool::ConnectType::OCCI:
break;
case DBConnectionPool::ConnectType::PGSQL:
break;
}
m_connectionNames.append(connStr);
}
使用方式:
#include "dbconnectionpool.h"
#include <QDateTime>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
int main(int argc, char *argv[])
{
DBConnectionPool::getInstance()->setHostName("192.168.239.128");
DBConnectionPool::getInstance()->setUserName("root");
DBConnectionPool::getInstance()->setPassWord("123456@familY");
DBConnectionPool::getInstance()->setPort(3306);
DBConnectionPool::getInstance()->initConnectPool(DBConnectionPool::ConnectType::MYSQL,"dcs");
QSqlDatabase db = DBConnectionPool::getInstance()->getDbConnection();
if (!db.isValid())
return;
qInfo()<<"onSearchClicked begin..."<<QDateTime::currentDateTime();
db.transaction();
qInfo()<<"sqlQuery..."<<QDateTime::currentDateTime();
QSqlQuery sql(db);
sql.prepare("insert into taginfo(tagid,tagname,tagtype,tagdesc) values(?,?,?,?);");
qInfo()<<"prepare..."<<QDateTime::currentDateTime();
QVariantList tagIdList;
QVariantList tagNameList;
QVariantList tagTypeList;
QVariantList tagDescList;
for (int i = 1; i <= 10000; ++i) {
tagIdList.append(i);
tagNameList.append("tagBool"+QString::number(i));
tagTypeList.append("bool");
tagDescList.append("describe"+QString::number(i));
}
qInfo()<<"prepare..."<<QDateTime::currentDateTime();
sql.addBindValue(tagIdList);
sql.addBindValue(tagNameList);
sql.addBindValue(tagTypeList);
sql.addBindValue(tagDescList);
sql.execBatch();
qInfo()<<"execBatch..."<<QDateTime::currentDateTime();
db.commit();
qInfo()<<"commit..."<<QDateTime::currentDateTime();
if (QSqlError::NoError != sql.lastError().type())
db.rollback();
qInfo()<<"onSearchClicked end..."<<QDateTime::currentDateTime();
if (sql.lastError().isValid())
qInfo()<< "error:"<<sql.lastError();
}