• QT mysql 数据库线程池 与数据库操作封装

    最近事情比较多很久没有写学习笔记了，数据库线程池，+ 数据库封装，虽说数据库操作有很多不需要写sql 的，ORM 封装的方式去操作数据库。但是从业这些年一直是自己动手写sql ，还是改不了这个习惯。不说了直接上代码。

    数据库线程池

    1. ConnectionPool.h 文件
    2.  
    3. #ifndef CONNECTIONPOOL_H
    4. #define CONNECTIONPOOL_H
    5.  
    6. #include 
    7. #include 
    8. #include 
    9. #include 
    10. #include 
    11.  
    12. #include "ConnectionPool.h"
    13.  
    14. class ConnectionPool {
    15. public:
    16.     static void release(); // 关闭所有的数据库连接
    17.     static QSqlDatabase createConnection();                 // 获取数据库连接
    18.     static void closeConnection(QSqlDatabase connection); // 释放数据库连接回连接池
    19.  
    20.     ~ConnectionPool();
    21.  
    22. private:
    23.     static ConnectionPool& getInstance();
    24.  
    25.     ConnectionPool();
    26.     ConnectionPool(const ConnectionPool &other);
    27.     ConnectionPool& operator=(const ConnectionPool &other);
    28.     QSqlDatabase createConnection(const QString &connectionName); // 创建数据库连接
    29.     void initialize();
    30.     void loadConfigFile();
    31.  
    32. public:
    33.  
    34.     QQueue usedConnectionNames;   // 已使用的数据库连接名
    35.     QQueue unusedConnectionNames; // 未使用的数据库连接名
    36.  
    37.     QJsonObject mJsonObject;
    38.     // 数据库信息
    39.     QString hostName;
    40.     QString databaseName;
    41.     QString username;
    42.     QString password;
    43.     QString databaseType;
    44.     int     port;
    45.  
    46.     bool    testOnBorrow;    // 取得连接的时候验证连接是否有效
    47.     QString testOnBorrowSql; // 测试访问数据库的 SQL
    48.  
    49.     int maxWaitTime;  // 获取连接最大等待时间
    50.     int waitInterval; // 尝试获取连接时等待间隔时间
    51.     int maxConnectionCount; // 最大连接数
    52.  
    53.  
    54.  
    55.     static QMutex mutex;
    56.     static QWaitCondition waitConnection;
    57.     static ConnectionPool *instance;
    58. };
    59.  
    60. #endif // CONNECTIONPOOL_H
    1. ConnectionPool.cpp
    2.  
    3.  
    4. #include "ConnectionPool.h"
    5. #include 
    6.  
    7. QMutex ConnectionPool::mutex;
    8. QWaitCondition ConnectionPool::waitConnection;
    9. ConnectionPool* ConnectionPool::instance = NULL;
    10.  
    11. ConnectionPool::ConnectionPool()
    12. {
    13.     initialize();
    14. }
    15.  
    16. ConnectionPool::~ConnectionPool()
    17. {
    18.     // 销毁连接池的时候删除所有的连接
    19.     foreach(QString connectionName, usedConnectionNames)
    20.     {
    21.         QSqlDatabase::removeDatabase(connectionName);
    22.     }
    23.  
    24.     foreach(QString connectionName, unusedConnectionNames)
    25.     {
    26.         QSqlDatabase::removeDatabase(connectionName);
    27.     }
    28. }
    29.  
    30. void ConnectionPool::loadConfigFile()
    31. {
    32.     QString path = qApp->applicationDirPath();
    33.     QString strFile;
    34.     strFile = path + "/config/DBConfig.json";
    35.     QFile file(strFile);
    36.     if(!file.open(QIODevice::ReadOnly | QIODevice::Text))
    37.     {
    38.         qDebug() << "could't open projects json";
    39.         return;
    40.     }
    41.  
    42.     QString value = file.readAll();
    43.     file.close();
    44.  
    45.     QJsonParseError parseJsonErr;
    46.     QJsonDocument document = QJsonDocument::fromJson(value.toUtf8(), &parseJsonErr);
    47.     if(!(parseJsonErr.error == QJsonParseError::NoError))
    48.     {
    49.         qDebug() << parseJsonErr.errorString();
    50.         return;
    51.     }
    52.  
    53.     mJsonObject=document.object();
    54. }
    55.  
    56. void ConnectionPool::initialize()
    57. {
    58.     //loadConfigFile();
    59.     hostName     = "127.0.0.1";
    60.     databaseName = "test";
    61.     username     = "root";
    62.     password     = "admin";
    63.     databaseType = "QMYSQL";
    64.     port         = 3306;
    65.     testOnBorrow = true;
    66.     testOnBorrowSql = "SELECT 1";
    67.     waitInterval = 200;
    68.     maxWaitTime     = 5000;
    69.     maxConnectionCount = 10;
    70.  
    71. }
    72.  
    73.  
    74. ConnectionPool& ConnectionPool::getInstance()
    75. {
    76.     if (NULL == instance)
    77.     {
    78.         QMutexLocker locker(&mutex);
    79.  
    80.         if (NULL == instance)
    81.         {
    82.             instance = new ConnectionPool();
    83.         }
    84.     }
    85.  
    86.     return *instance;
    87. }
    88.  
    89. void ConnectionPool::release()
    90. {
    91.     QMutexLocker locker(&mutex);
    92.     delete instance;
    93.     instance = NULL;
    94. }
    95.  
    96. QSqlDatabase ConnectionPool::createConnection()
    97. {
    98.     ConnectionPool& pool = ConnectionPool::getInstance();
    99.     QString connectionName;
    100.  
    101.     QMutexLocker locker(&mutex);
    102.  
    103.     // 已创建连接数
    104.     int connectionCount = pool.unusedConnectionNames.size() + pool.usedConnectionNames.size();
    105.  
    106.     // 如果连接已经用完，等待 waitInterval 毫秒看看是否有可用连接，最长等待 maxWaitTime 毫秒
    107.     for (int i = 0;
    108.          i < pool.maxWaitTime
    109.          && pool.unusedConnectionNames.size() == 0 && connectionCount == pool.maxConnectionCount;
    110.          i += pool.waitInterval)
    111.     {
    112.         waitConnection.wait(&mutex, pool.waitInterval);
    113.  
    114.         // 重新计算已创建连接数
    115.         connectionCount = pool.unusedConnectionNames.size() + pool.usedConnectionNames.size();
    116.     }
    117.  
    118.     qDebug() << "connectionCount:"<
    119.     qDebug() << "pool.maxConnectionCount:"<
    120.     if (pool.unusedConnectionNames.size() > 0)
    121.     {
    122.         // 有已经回收的连接，复用它们
    123.         connectionName = pool.unusedConnectionNames.dequeue();
    124.     }
    125.     else if (connectionCount < pool.maxConnectionCount)
    126.     {
    127.         // 没有已经回收的连接，但是没有达到最大连接数，则创建新的连接
    128.         connectionName = QString("Connection-%1").arg(connectionCount + 1);
    129.     }
    130.     else
    131.     {
    132.         // 已经达到最大连接数
    133.         qDebug() << "Cannot create more connections.";
    134.         return QSqlDatabase();
    135.     }
    136.  
    137.     // 创建连接
    138.     QSqlDatabase db = pool.createConnection(connectionName);
    139.  
    140.     // 有效的连接才放入 usedConnectionNames
    141.     if (db.isOpen())
    142.     {
    143.         pool.usedConnectionNames.enqueue(connectionName);
    144.     }
    145.  
    146.     return db;
    147. }
    148.  
    149. void ConnectionPool::closeConnection(QSqlDatabase connection)
    150. {
    151.     ConnectionPool& pool = ConnectionPool::getInstance();
    152.     QString connectionName = connection.connectionName();
    153.  
    154.     // 如果是我们创建的连接，从 used 里删除，放入 unused 里
    155.     if (pool.usedConnectionNames.contains(connectionName))
    156.     {
    157.         QMutexLocker locker(&mutex);
    158.         pool.usedConnectionNames.removeOne(connectionName);
    159.         pool.unusedConnectionNames.enqueue(connectionName);
    160.         waitConnection.wakeOne();
    161.     }
    162. }
    163.  
    164. QSqlDatabase ConnectionPool::createConnection(const QString &connectionName)
    165. {
    166.     // 连接已经创建过了，复用它，而不是重新创建
    167.     if (QSqlDatabase::contains(connectionName))
    168.     {
    169.         QSqlDatabase db1 = QSqlDatabase::database(connectionName);
    170.  
    171.         if (testOnBorrow)
    172.         {
    173.             // 返回连接前访问数据库，如果连接断开，重新建立连接
    174.             qDebug() << "Test connection on borrow, execute:" << testOnBorrowSql << ", for" << connectionName;
    175.             QSqlQuery query(testOnBorrowSql, db1);
    176.  
    177.             if (query.lastError().type() != QSqlError::NoError && !db1.open())
    178.             {
    179.                 qDebug() << "Open datatabase error:" << db1.lastError().text();
    180.                 return QSqlDatabase();
    181.             }
    182.         }
    183.  
    184.         return db1;
    185.     }
    186.  
    187.     // 创建一个新的连接
    188.     QSqlDatabase db = QSqlDatabase::addDatabase(databaseType, connectionName);
    189.     db.setHostName(hostName);
    190.     db.setDatabaseName(databaseName);
    191.     db.setUserName(username);
    192.     db.setPassword(password);
    193.     db.setPort(port);
    194.     if (!db.open())
    195.     {
    196.         qDebug() << "Open datatabase error:" << db.lastError().text();
    197.         return QSqlDatabase();
    198.     }
    199.  
    200.     return db;
    201. }

    数据库封装：

    1. SqlDatabase.h
    2.  
    3.  
    4. #ifndef SQLDATABASE_H
    5. #define SQLDATABASE_H
    6.  
    7. #include 
    8. #include 
    9. #include 
    10.  
    11. class  SqlDatabase
    12. {
    13. public:
    14.     SqlDatabase();
    15.     ~SqlDatabase();
    16.  
    17. public:
    18.     static void InsertDB(QString strTableName,QJsonArray jsonValue);
    19.     static void UpdateDB(QString strTableName,QJsonArray jsonValue,QString strColumnCondition);
    20.     static int  InsertDB(QString strTableName,QJsonObject jsonValue);
    21.     static void UpdateDB(QString strTableName,QJsonObject jsonValue,QString strCondition);
    22.     static void QueryRecord(QString strSql,QJsonArray &nJsonValue);
    23.     static int  QueryCount(QString strSql);
    24.     static bool Delete(QString strSql);
    25.     static bool Delete(QString strTableName,QString strCondition);
    26. };
    27.  
    28. #endif // SQLDATABASE_H

     

    1. SqlDatabase.cpp
    2.  
    3.  
    4.  
    5. #include "SqlDatabase.h"
    6. #include "ConnectionPool.h"
    7. #include 
    8.  
    9.  
    10. #pragma execution_character_set("utf-8")
    11.  
    12. SqlDatabase::SqlDatabase()
    13. {
    14.  
    15. }
    16.  
    17. SqlDatabase::~SqlDatabase()
    18. {
    19.     ConnectionPool::Destroy();
    20. }
    21.  
    22. void SqlDatabase::InsertDB(QString strTableName,QJsonArray jsonValues)
    23. {
    24.     QSqlDatabase db =ConnectionPool::CreateConnection();
    25.     QString strValues="";
    26.     QString strNames="";
    27.     bool iskeyName=false;
    28.     for(int j=0;jsize();j++)
    29.     {
    30.         QJsonObject::iterator it;
    31.         QString strKeyValue;
    32.         QJsonObject tmpObj =jsonValues.at(j).toObject();
    33.         for(it=tmpObj.begin();it!=tmpObj.end();it++)
    34.         {
    35.             if(strKeyValue.isEmpty())
    36.             {
    37.                 if(it.value().isDouble())
    38.                 {
    39.                     it.value()=QString::number(it.value().toDouble(),'f',12);
    40.                 }
    41.                 strKeyValue=QString("'%1'").arg(it.value().toString());
    42.                 if(!iskeyName)
    43.                 {
    44.                     strNames=QString("%1").arg(it.key());
    45.                 }
    46.             }
    47.             else
    48.             {
    49.                 if(it.value().isDouble())
    50.                 {
    51.                     it.value()=QString::number(it.value().toDouble(),'f',12);
    52.                 }
    53.                 strKeyValue+=QString(",'%1'").arg(it.value().toString());
    54.                 if(!iskeyName)
    55.                 {
    56.                     strNames+=QString(",%1").arg(it.key());
    57.                 }
    58.             }
    59.         }
    60.  
    61.         iskeyName =true;
    62.         if(strValues.isEmpty())
    63.         {
    64.             strValues +="("+strKeyValue+")";
    65.         }
    66.         else
    67.         {
    68.             strValues +=",("+strKeyValue+")";
    69.         }
    70.     }
    71.     QString strSql=QString("INSERT INTO %1 (%2)  VALUES %3 ").arg(strTableName).arg(strNames).arg(strValues);
    72.     QSqlQuery query(db);
    73.     if(!query.exec(strSql))
    74.     {
    75.         qDebug()<<"Failed to INSERT:"<
    76.     }
    77.     ConnectionPool::CloseConnection(db);
    78. }
    79.  
    80. void SqlDatabase::UpdateDB(QString strTableName,QJsonArray jsonValue,QString strColumnCondition)
    81. {
    82. 	QString mHeadSql= QString(" UPDATE  %1 m,( ").arg(strTableName);
    83.     QString mEndSql=" ) n ";
    84. 	QString mSetConditionSql="";
    85.     QString mValueSql="";
    86.     QString mCondition="";
    87.     QString strSql="";
    88. 	for(int i=0;isize();i++)
    89.     {
    90.         QJsonObject jsonObject=jsonValue.at(i).toObject();
    91.         QJsonObject::iterator it;
    92.         QString strValue="";
    93.         if(!mValueSql.isEmpty())
    94.         {
    95.             mValueSql +=  " UNION ";
    96.         }
    97.         for(it=jsonObject.begin();it!=jsonObject.end();it++)
    98.         {
    99.             if(it.value().isDouble())
    100.             {
    101.                 it.value()=QString::number(it.value().toDouble(),'f',3);
    102.             }
    103.             if(strValue =="")
    104. 			{
    105.                 strValue =QString(" SELECT '%0' as `%1`").arg(it.value().toString()).arg(it.key());
    106.                 mSetConditionSql =QString(" SET m.%0 = n.%1").arg(it.key()).arg(it.key());
    107. 			}
    108. 			else
    109. 			{
    110.                 strValue +=QString(",'%0' as `%1`").arg(it.value().toString()).arg(it.key());
    111.                 mSetConditionSql += QString(" ,m.%0 = n.%1").arg(it.key()).arg(it.key());
    112. 			}	
    113. 		}
    114.  
    115.         mValueSql += strValue;
    116. 	}
    117.  
    118.     mCondition += QString(" WHERE m.%0 = n.%1").arg(strColumnCondition).arg(strColumnCondition);
    119.  
    120.     strSql =mHeadSql +mValueSql + mEndSql + mSetConditionSql+ mCondition;
    121.     qDebug()<
    122. }
    123.  
    124. int SqlDatabase::InsertDB(QString strTableName,QJsonObject jsonValue)
    125. {
    126.     QSqlDatabase db =ConnectionPool::CreateConnection();
    127.     QString strValues="";
    128.     QString strNames="";
    129.     int nLastNum=0;
    130.     QJsonObject::iterator it;
    131.     for(it=jsonValue.begin();it!=jsonValue.end();it++)
    132.     {
    133.         if(strValues.isEmpty())
    134.         {
    135.             if(it.value().isDouble())
    136.             {
    137.                 it.value()=QString::number(it.value().toDouble(),'f',12);
    138.             }
    139.             strValues=QString("'%1'").arg(it.value().toString());
    140.             strNames=QString("%1").arg(it.key());
    141.         }
    142.         else
    143.         {
    144.             if(it.value().isDouble())
    145.             {
    146.                 it.value()=QString::number(it.value().toDouble(),'f',12);
    147.             }
    148.             strValues+=QString(",'%1'").arg(it.value().toString());
    149.             strNames+=QString(",%1").arg(it.key());
    150.         }
    151.     }
    152.     QString strSql=QString("INSERT INTO %1 (%2)  VALUES(%3) ").arg(strTableName).arg(strNames).arg(strValues);
    153.  
    154.     QSqlQuery query(db);
    155.     if(!query.exec(strSql))
    156.     {
    157.         qDebug()<<"Failed to InsertDB:"<lastError().text();
    158.     }
    159.     else
    160.     {
    161.         nLastNum=query.lastInsertId().toInt();
    162.     }
    163.  
    164.     ConnectionPool::CloseConnection(db);
    165.     return  nLastNum;
    166. }
    167.  
    168. void SqlDatabase::UpdateDB(QString strTableName,QJsonObject jsonValue,QString strCondition)
    169. {
    170.     QSqlDatabase db =ConnectionPool::CreateConnection();
    171.     QString strValues="";
    172.     QString strNames="";
    173.     QJsonObject::iterator it;
    174.     for(it=jsonValue.begin();it!=jsonValue.end();it++)
    175.     {
    176.         if(strValues.isEmpty())
    177.         {
    178.             if(it.value().isDouble())
    179.             {
    180.                 it.value()=QString::number(it.value().toDouble(),'f',12);
    181.             }
    182.             strValues=QString("%1='%2'").arg(it.key()).arg(it.value().toString());
    183.         }
    184.         else
    185.         {
    186.             if(it.value().isDouble())
    187.             {
    188.                 it.value()=QString::number(it.value().toDouble(),'f',12);
    189.             }
    190.             strValues+=QString(",%1='%2'").arg(it.key()).arg(it.value().toString());
    191.         }
    192.     }
    193.  
    194.     QString strSql=QString("UPDATE  %1  SET %2  %3 ").arg(strTableName).arg(strValues).arg(strCondition);
    195.     QSqlQuery query(db);
    196.     if(!query.exec(strSql))
    197.     {
    198.         qDebug()<<"Failed to UpdateDB:"<lastError().text();
    199.     }
    200.  
    201.     ConnectionPool::CloseConnection(db);
    202. }
    203.  
    204. void SqlDatabase::QueryRecord(QString strSql,QJsonArray &nJsonValue)
    205. {
    206.     QSqlDatabase db =ConnectionPool::CreateConnection();
    207.     QSqlQuery query(db);
    208.     if(!query.exec(strSql))
    209.     {
    210.        qDebug()<<"Failed to QueryRecord:"<lastError().text();
    211.     }
    212.  
    213.     while(query.next())
    214.     {
    215.         QSqlRecord qResultRecord=query.record();
    216.         QJsonObject jsonObject;
    217.         for(int fileIndex =0; fileIndexcount();fileIndex++)
    218.         {
    219.             if(query.value(fileIndex).isNull())
    220.             {
    221.                 jsonObject.insert(qResultRecord.fieldName(fileIndex),QJsonValue::Null);
    222.             }
    223.             else if(query.value(fileIndex).type() ==QVariant::Int)
    224.             {
    225.                 jsonObject.insert(qResultRecord.fieldName(fileIndex),query.value(fileIndex).toInt());
    226.             }
    227.             else if(query.value(fileIndex).type() == QVariant::Double)
    228.             {
    229.                 jsonObject.insert(qResultRecord.fieldName(fileIndex),query.value(fileIndex).toDouble());
    230.             }
    231.             else if(query.value(fileIndex).type() == QVariant::LongLong)
    232.             {
    233.                 jsonObject.insert(qResultRecord.fieldName(fileIndex),query.value(fileIndex).toLongLong());
    234.             }
    235.             else
    236.             {
    237.                 jsonObject.insert(qResultRecord.fieldName(fileIndex),query.value(fileIndex).toString());
    238.             }
    239.         }
    240.         nJsonValue.append(jsonObject);
    241.     }
    242.     ConnectionPool::CloseConnection(db);
    243. }
    244.  
    245. int SqlDatabase::QueryCount(QString strSql)
    246. {
    247.    QSqlDatabase db =ConnectionPool::CreateConnection();
    248.    QSqlQuery query(db);
    249.    int totalCnt=0;
    250.    QString strSqlCount=QString(" SELECT COUNT(*) AS Cnt FROM  (%1) t ").arg(strSql);
    251.    if(!query.exec(strSqlCount))
    252.    {
    253.        qDebug()<<"Failed to QueryCount:"<lastError().text();
    254.    }
    255.  
    256.    while(query.next())
    257.    {
    258.         totalCnt=query.value("Cnt").toInt();
    259.    }
    260.    ConnectionPool::CloseConnection(db);
    261.    return totalCnt;
    262. }
    263.  
    264. bool SqlDatabase::Delete(QString strSql)
    265. {
    266.     bool bRet =true;
    267.     QSqlDatabase db =ConnectionPool::CreateConnection();
    268.     QSqlQuery query(db);
    269.     if(!query.exec(strSql))
    270.     {
    271.         qDebug()<<"Failed to Delete:"<lastError().text();
    272.         bRet = false;
    273.     }
    274.  
    275.     ConnectionPool::CloseConnection(db);
    276.     return bRet;
    277. }
    278.  
    279. bool SqlDatabase::Delete(QString strTableName,QString strCondition)
    280. {
    281.     bool bRet =true;
    282.     QSqlDatabase db =ConnectionPool::CreateConnection();
    283.     QSqlQuery query(db);
    284.     QString strSql=QString(" DELETE FROM %1 %2 ").arg(strTableName).arg(strCondition);
    285.     if(!query.exec(strSql))
    286.     {
    287.         qDebug()<<"Failed to Delete:"<lastError().text();
    288.         bRet = false;
    289.     }
    290.  
    291.     ConnectionPool::CloseConnection(db);
    292.     return bRet;
    293. }
    294.

    此处说一下批量更新

    源数据是这样需要更新

    id：1      shorName  ：aaaaa       score1:56.9   ranking  :22

    id：2      shorName  ：bbbbb       score1:89.9  score3:59.9  ranking  :27

    id：3      shorName  ：ccccc       score1:76.9   score2:81.9  ranking  :29

    sql 语句 批量更新

    1. UPDATE test m,(
    2. 	SELECT
    3. 		'1.000' AS `id`,
    4. 		'a' AS `name`,
    5. 		'22.000' AS `ranking`,
    6. 		'56.900' AS `score1`,
    7. 		'39.500' AS `score2`,
    8. 		'56.700' AS `score3`,
    9. 		'aaaa' AS `shorName` UNION
    10. 	SELECT
    11. 		'2.000' AS `id`,
    12. 		'b' AS `name`,
    13. 		'27.000' AS `ranking`,
    14. 		'89.900' AS `score1`,
    15. 		'39.500' AS `score2`,
    16. 		'59.900' AS `score3`,
    17. 		'bbbbb' AS `shorName` UNION
    18. 	SELECT
    19. 		'3.000' AS `id`,
    20. 		'c' AS `name`,
    21. 		'29.000' AS `ranking`,
    22. 		'76.900' AS `score1`,
    23. 		'72.900' AS `score2`,
    24. 		'81.900' AS `score3`,
    25. 		'ccccc' AS `shorName` 
    26. 	) n 
    27. 	SET m.id = n.id,
    28. 	m.NAME = n.NAME,
    29. 	m.ranking = n.ranking,
    30. 	m.score1 = n.score1,
    31. 	m.score2 = n.score2,
    32. 	m.score3 = n.score3,
    33. 	m.shorName = n.shorName 
    34. WHERE
    35. 	m.id = n.id

  • 相关阅读:
    cap分布式理论
    idea一键打包docker镜像并推送远程harbor仓库的方法（包含spotify和fabric8两种方法）--全网唯一正确，秒杀99%水文
    Java.lang.Character类中isLetter()方法具有什么功能呢？
    OpenCV 颜色检测| color detection
    Spring AI 接入OpenAI实现文字生成图片功能
    HTML+CSS+JS网页设计期末课程大作业——上海旅游景点（10页）web前端开发技术 web课程设计 网页规划与设计
    干货 | RDBMS 索引类型概述
    【每日一题】线段重叠
    文件管理工具“三剑客” #Everything #SpaceSniffer #Clover
    DAIR-V2X-V 3D检测数据集 转为Kitti格式 | 可视化
  • 原文地址：https://blog.csdn.net/windows_oracle/article/details/127325522