【 声明：版权所有，欢迎转载，请勿用于商业用途。 联系信箱：feixiaoxing @163.com】
嵌入式设备下面，有的时候也要对数据进行处理和保存。如果处理的数据不是很多，一般用json就可以。但是数据如果量比较大，但是还没有达到要用大型数据库的时候，这种情况下选择一个sqlite3这样的数据库，其实就可以了。所以，不管是上位机，还是在linux开发板上面，大家都喜欢用sqlite3来对数据进行处理和保存。今天，正好借助于这样一个机会，学习下sqlite3。
1、安装sqlite3开发库
安装的方法不复杂，直接sudo apt-get安装即可，
sudo apt-get install libsqlite3-dev2、准备测试代码
准备的测试代码不复杂，主要就是创建一个student.db。创建好了之后，建设一张表。有了这张表，就可以做增、删、改、查的动作了。最后肯定就是关闭数据库。
#include <sqlite3.h>
#include <stdio.h>
int main() {
sqlite3 *db;
char *err_msg = 0;
// Open the database
int rc = sqlite3_open("student.db", &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
// SQL statement to create a table
const char *create_table_sql = "CREATE TABLE IF NOT EXISTS student_table (id INTEGER PRIMARY KEY, name TEXT)";
// Execute the SQL statement to create the table
rc = sqlite3_exec(db, create_table_sql, 0, 0, &err_msg);
if (rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
sqlite3_close(db);
return 1;
}
// SQL statement to insert data
const char *insert_sql = "INSERT INTO student_table (id, name) VALUES (?, ?)";
// Prepare the insert statement
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, insert_sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare insert statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
// Bind parameters and execute the insert statement
int id = 1;
const char *name = "John";
sqlite3_bind_int(stmt, 1, id);
sqlite3_bind_text(stmt, 2, name, -1, SQLITE_STATIC);
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
fprintf(stderr, "Error inserting data: %s\n", sqlite3_errmsg(db));
sqlite3_finalize(stmt);
sqlite3_close(db);
return 1;
}
// Finalize the insert statement
sqlite3_finalize(stmt);
// SQL query to select data
const char *select_sql = "SELECT * FROM student_table";
// Prepare and execute the query
rc = sqlite3_prepare_v2(db, select_sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
fprintf(stderr, "Failed to execute select statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
// Iterate over the results
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
// Process each row
int id = sqlite3_column_int(stmt, 0);
const unsigned char *name = sqlite3_column_text(stmt, 1);
// Process the data...
printf("ID: %d, Name: %s\n", id, name);
}
// Check for errors or end of data
if (rc != SQLITE_DONE) {
fprintf(stderr, "Error reading data: %s\n", sqlite3_errmsg(db));
}
// Finalize the query statement
sqlite3_finalize(stmt);
// SQL statement to update data
const char *update_sql = "UPDATE student_table SET name = ? WHERE id = ?";
// Prepare the update statement
rc = sqlite3_prepare_v2(db, update_sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare update statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
// Bind parameters and execute the update statement
const char *new_name = "Alice";
sqlite3_bind_text(stmt, 1, new_name, -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 2, id);
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
fprintf(stderr, "Error updating data: %s\n", sqlite3_errmsg(db));
sqlite3_finalize(stmt);
sqlite3_close(db);
return 1;
}
// Finalize the update statement
sqlite3_finalize(stmt);
// SQL statement to delete data
const char *delete_sql = "DELETE FROM student_table WHERE id = ?";
// Prepare the delete statement
rc = sqlite3_prepare_v2(db, delete_sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
fprintf(stderr, "Failed to prepare delete statement: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
// Bind parameter and execute the delete statement
sqlite3_bind_int(stmt, 1, id);
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
fprintf(stderr, "Error deleting data: %s\n", sqlite3_errmsg(db));
sqlite3_finalize(stmt);
sqlite3_close(db);
return 1;
}
// Finalize the delete statement
sqlite3_finalize(stmt);
// Close the database
sqlite3_close(db);
return 0;
}
3、编译测试代码
编译也可以直接用g++编译，需要注意的就是链接的时候把sqlite3加上。
g++ db.cpp -g -o db -lsqlite34、测试和验证
测试有两种，一种是直接执一下./db，看看结果如何。还有一种就是gdb db，然后单步看一下过程。个人是比较推崇后面一种方式。当然，执行的过程中，我们也可以添加一些别的数据之后，用navicat之类的软件，看下db里面是不是真的存在相关的数据。
