sqlite3测试代码

Posted on

c++ 测试代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
#define LOGD printf
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; ++i) {
        LOGD("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    return 0;
}

int testsqlite() {
    // 数据库版本
    const char* version = sqlite3_libversion();
    LOGD("sqlite version: %s", version);

    sqlite3 *db;
    char *errMsg;
    int rv = 0;
    // 打开数据库, 如果数据库不存在的话, 则创建
    const char* filename = "test.db";
    rv = sqlite3_open_v2(filename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    if (rv) {
        LOGD("Cannot open database: %s", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    // 创建表
    char create_table[100] = "CREATE  TABLE IF NOT EXISTS customers (id INTEGER PRIMARY KEY,name TEXT NOT NULL)";
    rv = sqlite3_exec(db, create_table, callback, 0, &errMsg);
    if (rv != SQLITE_OK) {
        LOGD("SQLite statement execution error: %s", errMsg);
    }

    // 插入数据
    char insert_value[100] = "INSERT INTO customers VALUES('1', 'roman10')";
    rv = sqlite3_exec(db, insert_value, callback, 0, &errMsg);
    if (rv != SQLITE_OK) {
        LOGD("SQLite statement execution error: %s", errMsg);
    }

    // 修改数据 : update company set address='gansu', salary=33333;
    char change_value[100] = "UPDATE customers SET name=\"newname\" WHERE id=1";
    rv = sqlite3_exec(db, change_value, callback, 0, &errMsg);
    if (rv != SQLITE_OK) {
        LOGD("SQLite statement execution error: %s", errMsg);
    }

    // 查询数据
    {
        const char *query = "SELECT ID, NAME FROM customers ORDER BY ROW;";
        sqlite3_stmt *statement;
        rv = sqlite3_prepare(db, query, -1, &statement, NULL);
        if (rv != SQLITE_OK) {
            LOGD("SQLite statement execution error: %s", errMsg);
            sqlite3_close(db);
            return 0;
        }

        int count = sqlite3_column_count(statement);
        LOGD("列大小为: %d", count);
        while (sqlite3_step(statement) == SQLITE_ROW) {
            // 第一列的数据: id
            int rowNum = sqlite3_column_int(statement, 0);
            // 第二列的数据: name
            char *rowData = (char *)sqlite3_column_text(statement, 1);

            LOGD("第%d行数据: %d - %s", rowNum, rowData);
        }
        sqlite3_finalize(statement);
    }

    sqlite3_close(db);
    return 0;
}