介绍
适用于C++的SQLite数据库的orm,https://github.com/fnc12/sqlite_orm
基本语法
创建表
表结构
struct User{
int id;
std::string firstName;
std::string lastName;
int birthDate;
std::unique_ptr<std::string> imageUrl;
int typeId;
};
struct UserType {
int id;
std::string name;
};
SQL语句
CREATE TABLE users (id integer primary key autoincrement, first_name text not null, last_name text not null, birth_date integer not null, image_url text, type_id integer not null)
CREATE TABLE user_types (id integer primary key autoincrement, name text not null DEFAULT 'name_placeholder')
sqlite_orm语句
using namespace sqlite_orm;
auto storage = make_storage("db.sqlite",
make_table("users",
make_column("id", &User::id, autoincrement(), primary_key()),
make_column("first_name", &User::firstName),
make_column("last_name", &User::lastName),
make_column("birth_date", &User::birthDate),
make_column("image_url", &User::imageUrl),
make_column("type_id", &User::typeId)),
make_table("user_types",
make_column("id", &UserType::id, autoincrement(), primary_key()),
make_column("name", &UserType::name, default_value("name_placeholder"))));
- 在创建表时不需要指明表的列的变量类型,在创建时会通过传入的参数指针进行确定
- 对于私有数据类型,可以通过创建setter和getter进行赋值和访问
- 创建表时要指明存储的文件名,如“db.sqlite" 和表名 如 “users”,“user_types”,若要存储在内存里,文件名为":memory:"或者为空
- 可为列添加的属性,如自增autoincrement(),主键 primary_key(),初始值default_value(“0”)
CRUD
插入 insert
User user{-1, "Jonh", "Doe", 664416000, std::make_unique<std::string>("url_to_heaven"), 3 };
auto insertedId = storage.insert(user);
- 返回用户id或者抛出异常
- 如果要指明id,用replace()
查找
try{
auto user = storage.get<User>(insertedId);
cout << "user = " << user.firstName << " " << user.lastName << endl;
}catch(std::system_error e) {
cout << e.what() << endl;
}catch(...){
cout << "unknown exeption" << endl;
}```
或者
```cpp
if(auto user = storage.get_pointer<User>(insertedId)){
cout << "user = " << user->firstName << " " << user->lastName << endl;
}else{
cout << "no user with id " << insertedId << endl;
}
- 第一种失败会抛出异常,第二种成功返回std::unique_ptr 失败返回nullptr
修改 updata
user.firstName = "Nicholas";
user.imageUrl = "https://cdn1.iconfinder.com/data/icons/man-icon-set/100/man_icon-21-512.png"
storage.update(user);
- 根据id修改某一行的为主属性值
storage.update_all(set(c(&User::lastName) = "Hardey",
c(&User::typeId) = 2),
where(c(&User::firstName) == "Tom"));
- 修改符合条件的行
删除 remove
storage.remove<User>(insertedId)
- 参数是id,而不是整个对象
遍历所有对象 get.all()
auto allUsers = storage.get_all<User>();
cout << "allUsers (" << allUsers.size() << "):" << endl;
for(auto &user : allUsers) {
cout << storage.dump(user) << endl;
}
- 默认返回一个vector容器,可以指明返回list auto allUsersList = storage.get_all<User, std::list>();
- get.all() 内存开销较大,可以逐行遍历
for(auto &user : storage.iterate<User>()) {
cout << storage.dump(user) << endl;
}
复杂查询
// SELECT doctor_id
// FROM visits
// WHERE LENGTH(patient_name) > 8
auto selectStatement = storage.prepare(select(&Visit::doctor_id, where(length(&Visit::patient_name) > 8)));
cout << "selectStatement = " << selectStatement.sql() << endl; // prints "SELECT doctor_id FROM ..."
auto rows = storage.execute(selectStatement); // rows is std::vector<decltype(Visit::doctor_id)>
// SELECT doctor_id
// FROM visits
// WHERE LENGTH(patient_name) > 11
get<0>(selectStatement) = 11;
auto rows2 = storage.execute(selectStatement);
聚合函数
avg() 平均值 .avg()
count() 统计值 .count()
MAX() 最大值 .max() return std::unique_ptr
MIN() 最小值 min() return std::unique_ptr
SUM() 求和 return std::unique_ptr
TOTAL()
条件查询
auto id5and7 = storage.get_all<User>(where(c(&User::id) <= 7 and c(&User::id) >= 5 and not (c(&User::id) == 6)));
cout << "id5and7 count = " << id5and7.size() << endl;
for(auto &user : id5and7) {
cout << storage.dump(user) << endl;
}
- 可以使用=, !=, >, >=, <, <=, IN, BETWEEN ,LIKE,AND,OR
查询某列成员
// SELECT id FROM users WHERE last_name = 'Doe'
auto doeIds = storage.select(&User::id, where(c(&User::lastName) == "Doe"));
cout << "doeIds count = " << doeIds.size() << endl; // doeIds is std::vector<int>
for(auto &doeId : doeIds) {
cout << doeId << " ";
}
cout << endl;
查询若干列
// `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id`
auto partialSelect = storage.select(columns(&User::firstName, &User::lastName),
where(c(&User::id) > 250),
order_by(&User::id));
cout << "partialSelect count = " << partialSelect.size() << endl;
for(auto &t : partialSelect) {
auto &firstName = std::get<0>(t);
auto &lastName = std::get<1>(t);
cout << firstName << " " << lastName << endl;
}
ORDER BY
// `SELECT * FROM users WHERE id < 250 ORDER BY first_name`
auto orderedUsers2 = storage.get_all<User>(where(c(&User::id) < 250), order_by(&User::firstName));
cout << "orderedUsers2 count = " << orderedUsers2.size() << endl;
for(auto &user : orderedUsers2) {
cout << storage.dump(user) << endl;
}