Qt資料庫相關應用開發總結

2022-02-19 13:00:44

一、前言

近期花了兩個多月時間，將資料庫相關的程式碼重新封裝成了各種輪子（這條路必須打通，打通以後，相關專案只需要引入這個元件pri即可），測試了從Qt4.7到Qt6.1的各種版本，測試了odbc、sqlite、mysql、postgresql、sqlserver、oracle、人大金倉等資料庫，測試了本地連線、遠端連線、阿里雲連線等，測試了windows、linux、mac等系統，將所有專案資料庫相關的程式碼全部更新了一遍。能夠相容這麼多Qt版本和資料庫外掛以及測試驗證系統，估計全網也沒幾個人，全國11W Qter開發者中應該也是最多不超過10人。

二、資料庫開發經驗總結

在資料庫相關的應用中，如果僅僅是單機版本，沒有特別的需要（比如領導指定，或者需要遠端存放資料），強烈建議使用sqlite資料庫，這是本人經過無數次的對比測試和N個商業專案應用得出的結論。

Qt天生內建了sqlite資料庫，只需要釋出的時候帶上外掛就行（可以看到外掛動態庫檔案比其他幾種都要大，那是因為直接將資料庫的原始碼都編譯進去了，而其他只編譯了中間通訊互動的外掛原始碼），其他資料庫要麼還要帶上動態庫，要麼還需要建立資料來源；速度上，絕對無與倫比的出類拔萃，同樣的資料庫結構（表結構、索引等完全一致），查詢速度和批次更新速度、資料庫事務等，速度都是其他幾種的至少3倍以上，而且隨著資料量的增大對比越發明顯；

幾千萬的資料量完全沒問題，而且速度和效能都還可以，不要以訛傳訛網上部分菜雞說的不支援百萬以上的資料量，本人親測億級別，資料量建議千萬級別以下，著重注意資料庫表和索引的設計；

其他資料庫還要注意版本的區別，ODBC資料來源形式還容易出錯和執行失敗；

sqlite資料庫也有幾個重大缺點：不支援加密，不支援網路存取，不支援部分資料庫高階特性，不支援海量資料（億級別以上），但是對於絕大部分Qt專案還是足夠；

資料庫支援友好度大致是 sqlite > postgresql > mysql > odbc ;

以上都是在Qt環境中個人測試得出的結論，結果未必正確，作為參考即可，其他程式設計環境比如C#、JAVA請忽略，也許差別可能在中間通訊的效率造成的；

三、資料庫連線說明

Qt5預設提供的資料庫外掛包括了QSQLITE、QMYSQL、QPSQL、QODBC四種，後期版本比如5.12開始把mysql也移除了（可能是因為開源協定的問題），其中驅動列印中還有個QMYSQL3是表示mysql3舊版本，現在預設一般都mysql5以上，QPSQL7表示postgres7舊版本，現在預設一般都postgres9以上。
根據字面意思很容易理解QSQLITE用來連線sqlite資料庫，QMYSQL用來連線mysql資料庫，QPSQL用來連線postgres資料庫，QODBC理論上可以用來連線任何支援ODBC資料來源的資料庫，比如access、sqlserver、mysql、postgres、oracle等。
Qt4預設提供的資料庫外掛只有QSQLITE、QODBC兩種，因為QODBC理論上可以用來連線任何支援ODBC資料來源的資料庫，只是通過了微軟的資料來源中介軟體，效率上可能會有損耗，所以在Qt5又新增了其他幾個常用資料庫的外掛比如QMYSQL、QPSQL，而其他資料庫由於協定的要求並沒有提供對應的外掛需要自行編譯比如oracle。
Qt內建了sqlite資料庫，可以觀察到qsqlite4.dll檔案大小明顯比其他資料庫外掛大很多，理論上光一個外掛應該小很多才對，畢竟sqlite屬於小型資料庫，所以肯定是將sqlite的原始碼直接編譯到外掛了，所以我們在使用sqlite資料庫的時候無需帶一個sqlite.dll，而使用mysql則需要帶上libmysql.dll。
使用mysql、postgres等支援遠端存取的資料庫的時候，並不需要本地安裝資料庫，只需要釋出程式的時候帶上對應資料庫的動態庫即可，比如mysql對應帶上libmysql.dll即可，這樣程式指定資料庫主機地址就可以連線上，比如阿里雲的mysql、postgres等雲端資料庫。
mysql、posgrest等支援遠端連線的資料庫，預設安裝以後出於安全性考慮只支援本地存取，需要做設定才能支援遠端存取，mysql需要增加使用者root@%即主機設定為%，postgres需要開啟安裝目錄下的C:PostgreSQL10datapg_hba.conf檔案，增加一行 host all all 192.168.1.0/24 md5 表示支援192.168.1.1到192.168.1.255的IP存取，同時將C:PostgreSQL10datapostgresql.conf改成listen_addresses = '*'表示支援所有地址，具體這個的含義可以自行搜尋。
mysql資料庫通訊的預設埠是3306，postgres的是5432，這些埠都可以在安裝的時候或者後期更改。
資料庫也有位數的區別，比如你連線的是64位元的資料庫那就需要用64位元的Qt以及64位元的資料庫外掛和對應的動態庫檔案，位數一定要完全一致才行，否則連不上，很多人會在這個地方摔一跤。除了位數的區別可能還要注意版本的區別，畢竟資料庫一直在更新升級換代，有些版本變動比較大，未必Qt釋出版本的時候對應就支援最新的資料庫，所以一般建議用稍微老一點的資料庫版本，比如mysql本人一直用5.6，測試到現在Qt5.13版本都支援。
一般的軟體預設都只需要連線一個資料庫，所以建議直接在程式啟動以後就開啟好資料庫，然後其他需要用到資料庫的地方就執行即可，最後程式關閉的時候關閉資料庫。很多初學者每次增刪改查都開啟資料庫執行完成操作以後然後關閉資料庫，這樣效率極其低下。如果需要連線多個資料庫，則以資料庫連線名稱作為區分，Qt支援同時多個資料庫連線的，資料庫跨執行緒不安全，要加鎖，所以建議在哪個執行緒使用到的資料庫就在那個執行緒中開啟，而不要主執行緒開啟資料庫子執行緒使用資料庫，很可能會出問題。Qt5.10開始增加了資料庫跨執行緒使用的安全性檢查，執行時會列印提示。
建立資料庫、建立表、建立索引、初始化資料等這些都可以通過執行sql語句來實現，強烈建議在對常用的資料量比較多的表建立表的時候要建立索引，在大量的資料查詢更新操作的時候先啟動資料庫事務，執行完成以後提交資料庫事務。

四、資料庫操作流程程式碼

void MainWindow::testDb()
{
    //列印當前Qt對應支援的資料庫驅動名稱
    qDebug() << QSqlDatabase::drivers();

    //建立資料庫物件,驅動名稱根據列印的填寫,"QSQLITE", "QMYSQL", "QMYSQL3", "QODBC", "QODBC3", "QPSQL", "QPSQL7"
    QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
    //設定資料庫引數,要檢視Qt檔案是否支援該資料庫,一般建議預設的就好不用設定
    //db.setConnectOptions("MYSQL_OPT_RECONNECT=1;MYSQL_OPT_CONNECT_TIMEOUT=1;");
    //設定資料庫的主機地址
    db.setHostName("127.0.0.1");
    //設定資料庫通訊埠,預設值  mysql:3306  postgres:5432  sqlserver:1433
    db.setPort(5433);
    //設定資料庫名稱,預設值  mysql:mysql  postgres:postgres  sqlserver:master
    //如果是sqlite資料庫只需要設定這個引數即可,其餘引數都不用設定,因為sqlite不需要主機埠和使用者
    //引數內容為資料庫檔案的路徑 db.setDatabaseName("c:/test.db");
    db.setDatabaseName("postgres");
    //設定登入使用者名稱稱,預設值  mysql:root  postgres:postgres  sqlserver:sa
    db.setUserName("postgres");
    //設定登入使用者密碼
    db.setPassword("admin");
    //開啟資料庫,如果失敗列印錯誤資訊
    if (!db.open()) {
        qDebug() << db.lastError();
        return;
    }

    //執行增刪改查
    //常規查詢語句
    QString sql = "select UserName,UserPwd from UserInfo";
    //帶條件+排序+分組的查詢語句
    sql = "select UserName,UserPwd from UserInfo where UserName='admin' order by UserName asc group by UserGroup";

    //構建查詢物件,傳入sql語句查詢,可以先判斷執行成功與否再來取值
    QSqlQuery query;
    if (query.exec(sql)) {
        //迴圈取出所有查詢結果,對應結果是QVariant型別可以自行to到其他型別
        while(query.next()) {
            qDebug() << query.value(0).toString() << query.value(1).toString();
        }
    }

    //新增資料,拼接字串的形式比較通用,還有預留位置的形式
    sql = "insert into UserInfo(UserName,UserPwd) values('ceshi', '12345')";
    //刪除資料,如果不加where條件則表示刪除整個表的資料
    sql = "delete from UserInfo where UserName='ceshi'";
    //更新資料,如果不加where條件則表示更新整個表的資料
    sql = "update UserInfo set UserPwd='admin123' where UserName='ceshi'";

    //可以複用上面的QSqlQuery物件,也可以重新new,複用的話需要先呼叫clear
    query.clear();
    //新增+刪除+更新 資料只需要知道執行成功與否就行
    if (!query.exec(sql)) {
        qDebug() << "執行sql語句失敗";
    }

    //關閉資料庫,程式自動關閉的時候也會關閉,所以只是用一個資料庫的情況下無需手動關閉
    db.close();
}

五、資料庫綜合應用元件

（一）功能特點

同時支援多種資料庫比如odbc、sqlite、mysql、postgresql、sqlserver、oracle、人大金倉等。
一個資料庫類即可管理本地資料庫通訊，也支援遠端資料庫通訊等。
資料庫執行緒支援執行各種sql語句，包括單條和批次。
元件中的所有類列印資訊、錯誤資訊、執行結果都訊號發出去。
整合資料庫通用翻頁類（負責具體處理邏輯），搭配分頁導航控制元件（負責外觀），形成超級牛逼的翻頁控制元件。
整合資料庫自動清理類，設定最大記錄數後臺自動清理早期資料。
整合自定義委託類，支援核取方塊、文字方塊、下拉框、日期框、微調框、進度條等。
同時支援Qt4-Qt6，親測Qt4.6到Qt6.1任意版本，任意系統和編譯器。
本元件無故障 360天7乘24小時執行在至少上萬個現場，商業級別品質保證。
每個類都對應完整詳細的使用範例，註釋詳細，非常適合閱讀學習。
可以作為獨立的程式執行，比如自動清理早期資料，同步資料到雲端。
全部執行緒處理，不卡介面，自動重連資料庫。
普通測試情況，sqlite資料庫，資料庫發生器每秒鐘插入1000條記錄約0.003秒鐘，同時自動清理資料類每秒鐘刪除1000條記錄約0.13秒，不同執行緒互不干擾。

（二）資料庫通訊管理執行緒類