2-SQLite中间件_sqlite 中间件

原文地址：https://xie-peiquan.gitee.io/tags/%E6%9C%AC%E5%9C%B0%E6%95%B0%E6%8D%AE%E5%BA%93/

SQLite 提供的 C/CPP API 实在是太简陋，使用C++开发的伙伴一定会感到极为不方便。事实上有很多用C++包装的中间件，可以极大方便我们操作数据库。这里我整理了 sqlite_modern_cpp 的使用方法，相比于一些 orm 中间件，它简单而纯粹。如果对 orm 感兴趣的话，也可以看一下 sqlite_orm 这份代码。这两份代码都是 header_only的，部署起来十分简单。

开发环境

apt install sqlite3
apt install libsqlite3-dev
1
2

基本操作

包含头文件

#include <iostream>
#include "sqlite_modern_cpp.h"
using namespace  sqlite;
using namespace std;
1
2
3
4

连接数据库

// creates a database file 'dbfile.db' if it does not exists.
database db("dbfile.db");

//带上参数设置
sqlite_config config;
config.flags = OpenFlags::READONLY
database db("some_db", config);
1
2
3
4
5
6
7

创建表

// executes the query and creates a 'user' table
  db <<
     "create table if not exists user ("
     "   _id integer primary key autoincrement not null,"
     "   age int,"
     "   name text,"
     "   weight real"
     ");";
1
2
3
4
5
6
7
8

增

// inserts a new user record.
// binds the fields to '?' .
// note that only types allowed for bindings are :
//      int ,long, long long, float, double
//      string, u16string
// sqlite3 only supports utf8 and utf16 strings, you should use std::string for utf8 and std::u16string for utf16.
// If you're using C++17, `std::string_view` and `std::u16string_view` can be used as string types.
// note that u"my text" is a utf16 string literal of type char16_t * .
db << "insert into user (age,name,weight) values (?,?,?);"
   << 20
   << "bob"
   << 83.25;
1
2
3
4
5
6
7
8
9
10
11
12

查

返回多个条目

其中db << "select age,name,weight from user where age > ? ;" << 18; 的输出结果是一个临时的 sqlite::database_binder 对象。

auto && infer  = db << "select age,name,weight from user where age > ? ;" << 18;
for(auto &row : infer) {
 int age; string name; double weight;
 row >> age >> name >> weight;
 cout << age << ' ' << name << ' ' << weight << endl;
}

//或者是以下使用lambda函数
db << "select age,name,weight from user where age > ? ;" << 18
   >> [&](int age, string name, double weight) {
      cout << age << ' ' << name << ' ' << weight << endl;
   };
1
2
3
4
5
6
7
8
9
10
11
12

返回单个条目

// selects the count(*) from user table
// note that you can extract a single column single row result only to : int,long,long,long,float,double,string,u16string
int count = 0;
db << "select count(*) from user" >> count;
cout << "cout : " << count << endl;

// you can also extract multiple column rows
db << "select age, name from user where _id=1;" >> tie(age, name);
cout << "Age = " << age << ", name = " << name << endl;

// this also works and the returned value will be automatically converted to string
string str_count;
db << "select count(*) from user" >> str_count;
cout << "scount : " << str_count << endl;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

改

db << "update user set age = ? where age = ?;" << 22 << 20;
1

删

db << "delete from user where age = ?;" << 20;
1

事务操作

db << "begin;"; // begin a transaction ...   
db << "insert into user (age,name,weight) values (?,?,?);"
   << 20
   << u"bob"
   << 83.25f;
db << "insert into user (age,name,weight) values (?,?,?);" // utf16 string
   << 21
   << u"jack"
   << 68.5;
db << "commit;"; // commit all the changes.

db << "begin;"; // begin another transaction ....
db << "insert into user (age,name,weight) values (?,?,?);" // utf16 string
   << 19
   << u"chirs"
   << 82.7;
db << "rollback;"; // cancel this transaction ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

有没有发现，这个中间件保留了原始的 SQL 语句，同时它让数据的存取变得简单，简单而灵活！其他更多的操作照搬 SQLite3 的语句就可以了，请参考 1-SQLite语法一页纸 ，这里不再赘述。

https://github.com/SqliteModernCpp/sqlite_modern_cpp