sqlite简单教程

SQLite3 C++接口提供了丰富的功能来与SQLite数据库进行交互。以下是一些基本的SQLite3 C++接口使用方法：

打开数据库连接：

#include <sqlite3.h>
 
sqlite3 *db;
int rc = sqlite3_open("my_database.db", &db);
if (rc) {
    std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
    sqlite3_close(db);
    return -1;
}

执行SQL语句（不带参数）：

const char *sql = "CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY, name TEXT)";
char *zErrMsg = nullptr;
rc = sqlite3_exec(db, sql, nullptr, nullptr, &zErrMsg);
if (rc != SQLITE_OK) {
    std::cerr << "SQL error: " << zErrMsg << std::endl;
    sqlite3_free(zErrMsg);
}

执行SQL语句（带参数）：

const char *sql = "INSERT INTO my_table (name) VALUES (?)";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);
if (rc == SQLITE_OK) {
    rc = sqlite3_bind_text(stmt, 1, "John Doe", -1, SQLITE_TRANSIENT);
    if (rc == SQLITE_OK) {
        rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE) {
            std::cout << "Insertion successful." << std::endl;
        } else {
            std::cerr << "Error inserting data: " << sqlite3_errmsg(db) << std::endl;
        }
    } else {
        std::cerr << "Binding error: " << sqlite3_errmsg(db) << std::endl;
    }
    sqlite3_finalize(stmt);
} else {
    std::cerr << "Preparation error: " << sqlite3_errmsg(db) << std::endl;
}

查询数据：

const char *sql = "SELECT * FROM my_table WHERE id = ?";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);
if (rc == SQLITE_OK) {
    rc = sqlite3_bind_int(stmt, 1, 1); // 假设我们要查询id为1的记录
    if (rc == SQLITE_OK) {
        while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 0);
            std::cout << "Name: " << reinterpret_cast<const char*>(name) << std::endl;
        }
        if (rc != SQLITE_DONE) {
            std::cerr << "Error fetching data: " << sqlite3_errmsg(db) << std::endl;
        }
    } else {
        std::cerr << "Binding error: " << sqlite3_errmsg(db) << std::endl;
    }
    sqlite3_finalize(stmt);
} else {
    std::cerr << "Preparation error: " << sqlite3_errmsg(db) << std::endl;
}

关闭数据库连接：
```
sqlite3_close(db);
```

以上代码片段展示了如何使用SQLite3 C++ API进行数据库操作，包括创建表、插入数据、查询数据以及关闭数据库连接。在实际应用中，请确保正确处理错误和资源释放。

加密：SQLCipher sqleet sqlcipher/sqlcipher: SQLCipher is a standalone fork of SQLite that adds 256 bit AES encryption of database files and other security features. (github.com) SQLite3：对SQLite3加密-CSDN博客

-- 创建houses表
CREATE TABLE houses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) -- 假设每个房子都有一个名称
);
 
-- 创建rooms表
CREATE TABLE rooms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    house_id INTEGER,
    name VARCHAR(255), -- 房间名称
    FOREIGN KEY (house_id) REFERENCES houses(id) ON DELETE CASCADE
);
 
-- 创建objects表
CREATE TABLE objects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id INTEGER,
    name VARCHAR(255), -- 对象名称
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
);
 
-- 插入一个房子
INSERT INTO houses (name) VALUES ('My House');
 
-- 插入一个房间到这个房子
INSERT INTO rooms (house_id, name) VALUES (1, 'Living Room'); -- 1
 
INSERT INTO rooms (house_id, name) VALUES (1, 'Bed Room'); -- 2
 
-- 插入一个对象到这个房间
INSERT INTO objects (room_id, name) VALUES (1, 'Chair'),(1,'television'),(1,'fan'),(1,'light'),(1,'air conditioner');
INSERT INTO objects (room_id, name) VALUES (2,'bed'),(2,'closet'),(2,'desk');

#include <sqlite3.h>
#include <iostream>
#include <map>
#include <vector>
 
struct Object {
    int id;
    int room_id;
    std::string name;
};
 
struct Room {
    int id;
    int house_id;
    std::string name;
    std::vector<Object> objects;
};
 
struct House {
    int id;
    std::string name;
    std::vector<Room> rooms;
};
 
static int callback(void *data, int argc, char **argv, char **azColName) {
    auto *houses = static_cast<std::map<int, House>*>(data);
 
    int house_id = std::stoi(argv[0]);
    std::string house_name = argv[1];
 
    if (houses->find(house_id) == houses->end()) {
        (*houses)[house_id] = {house_id, house_name};
    }
    //没有初始化rooms
    return 0;
}
 
static int roomCallback(void *data, int argc, char **argv, char **azColName) {
    auto *rooms = static_cast<std::map<int, Room>*>(data);
 
    int room_id = std::stoi(argv[0]);
    std::string room_name = argv[1];
    int house_id = std::stoi(argv[2]);
 
    if (rooms->find(room_id) == rooms->end()) {
        (*rooms)[room_id] = {room_id,house_id, room_name};
    }
 
    // // 关联房间到房子
    // if ((*rooms)[room_id].house_id == 0) {
    //     (*rooms)[room_id].house_id = house_id;
    // }
 
    return 0;
}
 
static int objectCallback(void *data, int argc, char **argv, char **azColName) {
    auto *objects = static_cast<std::map<int, Object>*>(data);
 
    int object_id = std::stoi(argv[0]);
    std::string object_name = argv[1];
    int room_id = std::stoi(argv[2]);
 
    if (objects->find(object_id) == objects->end()) {
        (*objects)[object_id] = {object_id, room_id, object_name};
    }
 
 
    return 0;
}
 
int main() {
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
 
    rc = sqlite3_open("test.db", &db);
    if (rc) {
        std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_close(db);
        return 1;
    }
 
    // 获取所有房子
    std::map<int, House> houses;
    const char *sql = "SELECT id, name FROM houses";
    rc = sqlite3_exec(db, sql, callback, &houses, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
 
    // 获取所有房间
    std::map<int, Room> rooms;
    sql = "SELECT id, name, house_id FROM rooms";
    rc = sqlite3_exec(db, sql, roomCallback, &rooms, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
 
    // 获取所有对象
    std::map<int, Object> objects;
    sql = "SELECT id, name, room_id FROM objects";
    rc = sqlite3_exec(db, sql, objectCallback, &objects, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
 
    //注意顺序 
    // 将对象关联到房间
    for (auto &obj : objects) {
        if (rooms.find(obj.second.room_id) != rooms.end()) {
            rooms[obj.second.room_id].objects.push_back(obj.second);
        }
    }
    // 将房间关联到房子
    for (auto &room : rooms) {
        if (houses.find(room.second.house_id) != houses.end()) {
            houses[room.second.house_id].rooms.push_back(room.second); //这里是copy 或许可以改成指针或者引用？
        }
    }
    
    // 打印树状数据
    for (const auto &house : houses) {
        std::cout << "House ID: " << house.first << ", Name: " << house.second.name << std::endl;
        for (const auto &room : house.second.rooms) {
            std::cout << "  Room ID: " << room.id << ", Name: " << room.name << std::endl;
            std::cout << "  Objects:"<< room.objects.size()<< std::endl;//0
            for (const auto &obj : room.objects) {
                std::cout << "    Object ID: " << obj.id << ", Name: " << obj.name << std::endl;
            }
        }
    }
 
    sqlite3_close(db);
 
    return 0;
}

🪴 BetterYimi's blog

Explorer

sqlite简单教程

Graph View

Backlinks