QxOrm入门教程03.01：SQL

系列索引：QxOrm入门教程索引

数据库是与ORM契合的最紧的。甚至于说ORM就是对SQL的操作进行抽象。主要在于增删改查。

工程

创建一个纯C++工程，设置pro

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
include(D:\gsdf\include\QxOrm\QxOrm.pri)

CONFIG += c++17 console
DEFINES += _BUILDING_USER
# You can make your code fail to compile if it uses deprecated APIs.
# In order to do so, uncomment the following line.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0

!contains(DEFINES, _QX_NO_PRECOMPILED_HEADER) {
    PRECOMPILED_HEADER = precompiled.h
}

INCLUDEPATH += D:\gsdf\include\QxOrm\include

LIBS += -LD:\gsdf\lib -lQxOrmd

HEADERS += \
    user.h

SOURCES += \
        main.cpp \
        user.cpp

# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target

主要就是添加QxOrm包含文件，设置预编译头文件，添加链接库

源码

预编译

对应的precompiled.h文件

1
2
3
4
5
6
#ifndef PRECOMPILED_H
#define PRECOMPILED_H

#include <QxOrm.h>

#endif // PRECOMPILED_H

注意，预编译相关设置一定要有，否则编译会报错。

User类

头文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#ifndef USER_H
#define USER_H

#include "QxCommon/QxMacro.h"
#include "QxOrm.h"
#include "QxRegister/QxRegister.h"
#include "QxPrecompiled.h"
#include "QxOrm_Impl.h"

class User
{
public:
    User() : id(0) { }
    virtual ~User() { }

    long id;
    QString name;
    int age;
};

QX_REGISTER_HPP_EXPORT_DLL(User, qx::trait::no_base_class_defined, 1)

#endif // USER_H

实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#include "user.h"

QX_REGISTER_CPP_EXPORT_DLL(User)

namespace qx {
    template <> void register_class(QxClass<User> & t)
    {
        // 注册 User::id <=> 数据库中的主键
        t.id(&User::id, "id");

        // 注册 User::name 属性，使用的 key 是 name，version 是 1。
        t.data(&User::name, "name", 1);

        // 注册 User::age 属性，使用的 key 是 age。
        t.data(&User::age, "age");
    }
}

主程序文件

其实就是main文件，功能就是连接SQLITE3数据库，创建User表，然后增删改查。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
#include "QxOrm_Impl.h"
#include "QxDao/QxSqlDatabase.h"
#include "QxDao/QxDao.h"
#include "QxCommon/QxCache.h"

#include "user.h"

int main(int argc, char *argv[])
{
    // 初始化参数，用于和数据库交互
    qx::QxSqlDatabase::getSingleton()->setDriverName("QSQLITE");
    qx::QxSqlDatabase::getSingleton()->setDatabaseName("./Users.db");
    qx::QxSqlDatabase::getSingleton()->setHostName("localhost");
    qx::QxSqlDatabase::getSingleton()->setUserName("root");
    qx::QxSqlDatabase::getSingleton()->setPassword("");

    // 在数据库中创建 User 表
    QSqlError daoError = qx::dao::create_table<User>();

    // 创建 3 个用户
    // 可以使用 std 和 Qt 智能指针：std::shared_ptr、QSharedPointer 等...
    typedef  QSharedPointer<User> UserPtr;
    UserPtr u1;
    u1.reset(new User());
    u1->name = "Jack Ma";
    u1->age = 30;

    UserPtr u2;
    u2.reset(new User());
    u2->name = "Pony";
    u2->age = 25;

    UserPtr u3;
    u3.reset(new User());
    u3->name = "Waleon";
    u3->age = 18;

    // 将所有用户插入容器中
    // 可以使用 std、boost、Qt 和 qx::QxCollection<Key，Value> 中的许多容器
    typedef QVector<UserPtr> VectorUser;
    VectorUser users;
    users.push_back(u1);
    users.push_back(u2);
    users.push_back(u3);

    // 将容器中的所有用户插入到数据库中
    // p1、p2、p3 的 id 属性会自动更新
    daoError = qx::dao::insert(users);

    // 修改第二个用户的信息，并更新到数据库中
    u2->name = "Pony modified";
    u2->age = 38;
    daoError = qx::dao::update(u2);

    // 从数据库中删除第一个用户
    daoError = qx::dao::delete_by_id(u1);

    // 计算用户的数量
    long userCount = qx::dao::count<User>();
    qDebug() << "User Count: " << userCount;

    // 将 id 为 3 的用户取出，并传给一个新变量
    UserPtr userTmp;
    userTmp.reset(new User());
    userTmp->id = 3;
    daoError = qx::dao::fetch_by_id(userTmp);
    qDebug() << "User Tmp: " << userTmp->id << userTmp->name << userTmp->age;
    return 0;
}

运行

编译运行，可以在本地目录发现创建的Users.db文件，程序输出为

1
2
3
4
5
6
7
8
9
10
[QxOrm] qx::QxSqlDatabase : create new database connection in thread '0x1ac8' with key '{be9fbd8b-7e6c-4365-bd0b-d6db34ce495c}'
[QxOrm] sql query (total: 897 ms, db_exec: 0 ms) : CREATE TABLE User (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)
[QxOrm] sql query (total: 131 ms, db_exec: 0.803 ms) : INSERT INTO User (name, age) VALUES (:name, :age)
[QxOrm] sql query (total: 99.9 ms, db_exec: 0.88 ms) : UPDATE User SET id = :id, name = :name, age = :age WHERE id = :id_bis
[QxOrm] sql query (total: 109 ms, db_exec: 108 ms) : DELETE FROM User WHERE id = :id

Process exited with code: 0.[QxOrm] sql query (total: 0.197 ms, db_exec: 0.149 ms) : SELECT COUNT(*) FROM User
User Count:  2
[QxOrm] sql query (total: 0.323 ms, db_exec: 0.1 ms) : SELECT User.id AS User_id_0, User.name AS User_name_0, User.age AS User_age_0 FROM User WHERE User.id = :id
User Tmp:  3 "Waleon" 18

再次运行，提示数据库已经存在，不能重复创建。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[QxOrm] qx::QxSqlDatabase : create new database connection in thread '0x4b90' with key '{77bd0719-5661-4acb-afd7-e4b16ee2b82d}'
[QxOrm] execute sql query failed : CREATE TABLE User (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)
Database error number '
1' :
Unable to execute statement

table User already exists
[QxOrm] sql query (total: 470 ms, db_exec: 1.25 ms) : INSERT INTO User (name, age) VALUES (:name, :age)
[QxOrm] sql query (total: 83.4 ms, db_exec: 0.876 ms) : UPDATE User SET id = :id, name = :name, age = :age WHERE id = :id_bis
[QxOrm] sql query (total: 91.1 ms, db_exec: 91 ms) : DELETE FROM User WHERE id = :id
[QxOrm] sql query (total: 0.172 ms, db_exec: 0.127 ms) : SELECT COUNT(*) FROM User
User Count:  4
[QxOrm] sql query (total: 0.298 ms, db_exec: 0.0939 ms) : SELECT User.id AS User_id_0, User.name AS User_name_0, User.age AS User_age_0 FROM User WHERE User.id = :id
User Tmp:  3 "Waleon" 18
Process exited with code: 0.

参考资料

Qt
#Qt #数据库 #ORM #QxOrm #SQL
QxOrm入门教程03.01：SQL
https://blog.jackeylea.com/qxorm/how-to-access-sqlite3-with-qxorm/
作者
JackeyLea
发布于
2025年4月28日
许可协议