如何在C++中操作SQLite数据库_SQLite接口使用教程

const char *sql_insert = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (?, ?, ?, ?, ?);";
sqlite3_stmt *stmt;

rc = sqlite3_prepare_v2(db, sql_insert, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;
    sqlite3_close(db);
    return -1;
}

int id = 6;
const char *name = "John Doe";
int age = 30;
const char *address = "Anytown";
double salary = 50000.0;

sqlite3_bind_int(stmt, 1, id);
sqlite3_bind_text(stmt, 2, name, -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, age);
sqlite3_bind_text(stmt, 4, address, -1, SQLITE_STATIC);
sqlite3_bind_double(stmt, 5, salary);

rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
    std::cerr << "Execution failed: " << sqlite3_errmsg(db) << std::endl;
} else {
    std::cout << "Inserted successfully" << std::endl;
}

sqlite3_finalize(stmt);

// 插入BLOB数据
FILE *fp = fopen("image.jpg", "rb");
fseek(fp, 0, SEEK_END);
long fsize = ftell(fp);
fseek(fp, 0, SEEK_SET);  /* same as rewind(f); */

unsigned char *blob_data = (unsigned char*)malloc(fsize + 1);
fread(blob_data, fsize, 1, fp);
fclose(fp);

const char *sql_insert_blob = "INSERT INTO Images (id, data) VALUES (?, ?);";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql_insert_blob, -1, &stmt, 0);

sqlite3_bind_int(stmt, 1, 1);
sqlite3_bind_blob(stmt, 2, blob_data, fsize, SQLITE_STATIC);

sqlite3_step(stmt);
sqlite3_finalize(stmt);
free(blob_data);

// 读取BLOB数据
const char *sql_select_blob = "SELECT data FROM Images WHERE id = 1;";
rc = sqlite3_prepare_v2(db, sql_select_blob, -1, &stmt, 0);

if (sqlite3_step(stmt) == SQLITE_ROW) {
    const void *blob = sqlite3_column_blob(stmt, 0);
    int bytes = sqlite3_column_bytes(stmt, 0);

    FILE *fp_out = fopen("image_out.jpg", "wb");
    fwrite(blob, 1, bytes, fp_out);
    fclose(fp_out);
}
sqlite3_finalize(stmt);

#include <iostream>
#include <sqlite3.h>
#include 
#include 

class SQLiteDB {
public:
    SQLiteDB(const std::string& db_path) {
        int rc = sqlite3_open(db_path.c_str(), &db_);
        if (rc) {
            throw std::runtime_error("Can't open database: " + std::string(sqlite3_errmsg(db_)));
        }
    }

    ~SQLiteDB() {
        if (db_) {
            sqlite3_close(db_);
        }
    }

    void execute(const std::string& sql) {
        char *errMsg = 0;
        int rc = sqlite3_exec(db_, sql.c_str(), 0, 0, &errMsg);
        if (rc != SQLITE_OK) {
            std::string errorMsg = "SQL error: " + std::string(errMsg);
            sqlite3_free(errMsg);
            throw std::runtime_error(errorMsg);
        }
    }

    sqlite3* getDB() const {
        return db_;
    }

private:
    sqlite3 *db_ = nullptr;
};

int main() {
    try {
        SQLiteDB db("mydatabase.db");
        db.execute("CREATE TABLE IF NOT EXISTS COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);");
        db.execute("INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );");

        // 使用db.getDB()来执行更复杂的操作
        sqlite3_stmt *stmt;
        const char *sql_select = "SELECT * FROM COMPANY;";
        int rc = sqlite3_prepare_v2(db.getDB(), sql_select, -1, &stmt, 0);
        // ... (处理查询结果)
        sqlite3_finalize(stmt);

    } catch (const std::runtime_error& e) {
        std::cerr << "Exception: " << e.what() << std::endl;
        return -1;
    }
    return 0;
}