1 sqlite3 * db=NULL;2 int rc = 0;3 char * sql = new char[800];4 char * zErrMsg =NULL;5
6 //for test
7 string id1 = "aaa";8 std::vector feature1{0.12, 0.23, 0.34, 0.45, 0.56, 0.67};//用来模拟人脸特征值
9
10 string id2 = "bbb";11 std::vector feature2{1.12, 1.23, 1.34, 1.45, 1.56, 1.67};//用来模拟人脸特征值12
13 //先把人脸特征值的float数组转成json,然后保存到数据库中。cjson的源码和例程在你自己的github上保存了
14 cJSON *root1, *js_feature1;15 root1 =cJSON_CreateObject();16 cJSON_AddItemToObject(root1, "face_feature1", js_feature1 =cJSON_CreateArray());17 for(int i = 0; i < feature1.size(); i++)18 {19 cJSON_AddItemToArray(js_feature1, cJSON_CreateNumber(feature1.at(i)));20 }21 char *s1 =cJSON_PrintUnformatted(root1);22 printf("s1:%s\n", s1);23
24
25 cJSON *root2, *js_feature2;26 root2 =cJSON_CreateObject();27 cJSON_AddItemToObject(root2, "face_feature2", js_feature2 =cJSON_CreateArray());28 for(int i = 0; i < feature2.size(); i++)29 {30 cJSON_AddItemToArray(js_feature2, cJSON_CreateNumber(feature2.at(i)));31 }32 char *s2 =cJSON_PrintUnformatted(root2);33 printf("s2:%s\n", s2);34
35
36 sqlite3_initialize();37 rc = sqlite3_open_v2("featureList.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);//这时候登录嵌入式设备,你会看见创建了一个featureList.db的文件。
38 if(rc !=SQLITE_OK)39 {40 sqlite3_close_v2(db);41 printf("open featureList sql fail\n");42 }43 else
44 {45 printf("open featureList sql success\n");46 }47
48
49 sprintf(sql, "CREATE TABLE featureList(ID INTEGER PRIMARY KEY AUTOINCREMENT, uuid VARCHAR(10),feature VARCHAR(10));"); //这里的table名字featureList为前面open函数时的名字featureList
50 sqlite3_exec(db, sql, 0, 0, &zErrMsg);51
52
53 //插入数据
54 sprintf(sql, "INSERT INTO featureList VALUES(NULL, '%s','%s');", id1.c_str(), s1);//ID传入NULL,那么会自动递增,这里的%s一定要用单引号括起来,
55 sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );56
57 sprintf(sql, "INSERT INTO featureList VALUES(NULL, '%s','%s');", id2.c_str(), s2);//ID传入NULL,那么会自动递增,这里的%s一定要用单引号括起来,
58 sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );59
60
61 //查询数据
62 int nrow = 0, ncolumn = 0;63 char **azResult; //二维数组存放结果
64
65 sprintf(sql, "SELECT * FROM featureList");66 sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );67 printf("row:%d, column:%d\n", nrow, ncolumn);68
69 for(int i = 0; i < (nrow + 1)*ncolumn; i++)70 {71 printf("azResult[%d] = %s\n", i, azResult[i]);72 }73
74 int deleteId = 1;75 sprintf(sql, "DELETE FROM featureList WHERE ID = %d", deleteId);76 printf("delete sql is:%s\n", sql);77 rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);78 if(rc ==SQLITE_OK)79 {80 //作用是重新构建数据库文件,回收空白空间,减小数据库文件的大小。
81 printf("delete success\n");82 }83 else
84 {85 printf("delete fail!\n");86 }87
88
89 sprintf(sql, "UPDATE featureList SET ID=ID-1 WHERE ID>1");//如果本来的ID是1 2 3 4,我们把3删除,那么ID变为1 2 4,UPDATE语句的作用是把ID重新排序成1 2 3.
90 rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);91
92 sprintf(sql, "SELECT * FROM featureList");93 sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );94 printf("row:%d, column:%d\n", nrow, ncolumn);95 for(int i = 0; i < (nrow + 1)*ncolumn; i++)96 {97 printf("azResult[%d] = %s\n", i, azResult[i]);98 }99
100 delete[] sql;101 sqlite3_free_table(azResult);102 sqlite3_close(db);