Windows下C++使用SQLite

1、安装

  进入SQLite Download Page页面，下载sqlite-dll-win-x86-*.zip、sqlite-amalgamation-*.zip、sqlite-tools-win-x64-*.zip三个包，这三个包里分别包含dll文件和def文件、头文件、exe工具。

  使用vs命令行工具生成.lib文件：进入dll和def文件所在的目录，执行lib /DEF:sqlite3.def /OUT:sqlite3.lib /MACHINE:x86后获得.lib文件，这样就可以通过dll文件、lib文件、头文件来开发SQLite了。

  exe工具可以进行SQLite操作，比如下面为创建数据库和表（输入.quit结束命令，SQL语句以分号结尾）：

2、代码示例

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

//SQL语句查询结果回调，比如对于select来说，查询到的每一条数据都会执行callback方法一次
static int callback(void* data/*sqlite3_exec()的第四个参数*/, int argc/*结果的列个数*/, 
    char** argv/*结果字段值*/, char** azColName/*结果列名*/) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");

    return 0;
}


int main()
{
    sqlite3* pDB = nullptr;
    char* zErrMsg = nullptr;
    const char* sql = nullptr;
    const char* data = "Callback function called";
    //打开数据库，不存在则创建
    int nRes = sqlite3_open("D:\\sqlite\\test.db", &pDB);
    if (nRes) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(pDB));
        exit(0);
    }

    //创建表，已存在则创建失败
    sql = "create table company("\
            "id integer primary key autoincrement,"\
            "name text not null,"\
            "age integer not null);";
    nRes = sqlite3_exec(pDB, sql, 0, 0, &zErrMsg);
    if (nRes != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }

    //插入数据
   sql = "insert into company (id,name,age) values(1, 'Paul', 32); " \
        "insert into company (id,name,age) values (2, 'Allen', 25); " \
        "insert into company (id,name,age) values (3, 'Teddy', 23);";
    nRes = sqlite3_exec(pDB, sql, 0, 0, &zErrMsg);
    if (nRes != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }

    //查询数据
    sql = "select * from company";
    nRes = sqlite3_exec(pDB, sql, callback, (void*)data, &zErrMsg);
    if (nRes != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }

    printf("==============================================\r\n");

    //修改数据
    sql = "update company set age = 25 where id=1; select * from company";
    nRes = sqlite3_exec(pDB, sql, callback, (void*)data, &zErrMsg);
    if (nRes != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }

    printf("==============================================\r\n");

    //删除数据
    sql = "delete from company where id=3; select * from company";
    nRes = sqlite3_exec(pDB, sql, callback, (void*)data, &zErrMsg);
    if (nRes != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }

    sqlite3_close(pDB);
}