QxOrm入门教程03.01:SQL

数据库是与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.

参考资料


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