SQLite编程操作

最新推荐文章于 2024-09-04 21:28:23 发布

wanglx2012

最新推荐文章于 2024-09-04 21:28:23 发布

阅读量1.2k

点赞数

分类专栏：数据库文章标签： SQLite

本文链接：https://blog.csdn.net/wanglx_/article/details/11964537

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

打开数据库链接sqlite3_open用法

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

用这个函数开始数据库操作。需要传入两个参数，一是数据库文件名，比如：比如：E:/test.db.文件名不需要一定存在，如果此文件不存在，sqlite会自动建立它。如果它存在，就尝试把它当数据库文件来打开。二是sqlite3**，即前面提到的关键数据结构。这个结构底层细节如何，你不要管它。函数返回值表示操作是否正确，如果是SQLITE_OK则表示操作正常。相关的返回值sqlite定义了一些宏。具体这些宏的含义可以参考sqlite3.h文件。里面有详细定义。

关闭数据库链接sqlite3_close用法

ppDb为刚才使用sqlite3_open打开的数据库链接

执行sql操作sqlite3_exec用法

 int sqlite3_exec(
   sqlite3* ppDb,                             /* An open database */
   const char *sql,                           /* SQL to be evaluated */
   int (*callback)(void*,int,char**,char**),  /* Callback function */
   void *,                                    /* 1st argument to callback */
   char **errmsg                             /* Error msg written here */
 );

这就是执行一条sql语句的函数。第1个参数不再说了，是前面open函数得到的指针。第2个参数constchar*sql是一条sql语句，以\0结尾。第3个参数sqlite3_callback是回调，当这条语句执行之后，sqlite3会去调用你提供的这个函数。第4个参数void*是你所提供的指针，你可以传递任何一个指针参数到这里，这个参数最终会传到回调函数里面，如果不需要传递指针给回调函数，可以填NULL。等下我们再看回调函数的写法，以及这个参数的使用。第5个参数char** errmsg 是错误信息。注意是指针的指针。sqlite3里面有很多固定的错误信息。执行sqlite3_exec之后，执行失败时可以查阅这个指针（直接cout<<errmsg得到一串字符串信息，这串信息告诉你错在什么地方。sqlite3_exec函数通过修改你传入的指针的指针，把你提供的指针指向错误提示信息，这样sqlite3_exec函数外面就可以通过这个char*得到具体错误提示。
说明：通常，sqlite3_callback和它后面的void*这两个位置都可以填NULL。填NULL表示你不需要回调。比如你做insert操作，做delete操作，就没有必要使用回调。而当你做select时，就要使用回调，因为sqlite3把数据查出来，得通过回调告诉你查出了什么数据。

exec 的回调

typedefint(*sqlite3_callback)(void*,int,char**,char**);你的回调函数必须定义成上面这个函数的类型。下面给个简单的例子： //sqlite3的回调函数 //sqlite每查到一条记录，就调用一次这个回调 int LoadMyInfo(void* para, intn_column, char** column_value, char** column_name);

//para是你在sqlite3_exec里传入的void*参数通过para参数，你可以传入一些特殊的指针（比如类指针、结构指针），然后在这里面强制转换成对应的类型（这里面是void*类型，必须强制转换成你的类型才可用）。然后操作这些数据

//n_column是这一条记录有多少个字段(即这条记录有多少列)

//char**column_value 是个关键值，查出来的数据都保存在这里，它实际上是个1维数组（不要以为是2维数组），每一个元素都是一个char*值，是一个字段内容（用字符串来表示，以\0结尾）

//char**column_name 跟column_value是对应的，表示这个字段的字段名称

sqlite3_exec是使用回调来执行对select结果的操作，你得声明一个函数，如果这个函数是类成员函数，你还不得不把它声明成static的（要问为什么？这又是C++基础了。C++成员函数实际上隐藏了一个参数：this，C++调用类的成员函数的时候，隐含把类指针当成回调函数的第一个参数传递进去。结果，这造成跟前面说的sqlite回调函数的参数不相符。只有当把成员函数声明成static时，它才没有多余的隐含的this参数）。

例子：

 #include <iostream>
 using namespace std;
 #include "sqlite/sqlite3.h"
 int callback(void*,int,char**,char**);
 int main()
 {
     sqlite3* db;
     int nResult = sqlite3_open("test.db",&db);
     if (nResult != SQLITE_OK)
     {
         cout<<"打开数据库失败："<<sqlite3_errmsg(db)<<endl;
         return 0;
     }
     else
     {
         cout<<"数据库打开成功"<<endl;
     }
 
     char* errmsg;
 
     nResult = sqlite3_exec(db,"create table MyTable(id integer primary key autoincrement,name varchar(100))",NULL,NULL,&errmsg);
      if (nResult != SQLITE_OK)
      {
          sqlite3_close(db);
          cout<<errmsg;
          sqlite3_free(errmsg);
         return 0;
     }
     string strSql;
     strSql+="begin;\n";
     for (int i=0;i<100;i++)
     {
         strSql+="insert into MyTable values(null,'heh');\n";
     }
     strSql+="commit;";
     //cout<<strSql<<endl;
 
     nResult = sqlite3_exec(db,strSql.c_str(),NULL,NULL,&errmsg);
 
     if (nResult != SQLITE_OK)
     {
         sqlite3_close(db);
         cout<<errmsg<<endl;
         sqlite3_free(errmsg);
         return 0;
     }
 
     strSql = "select * from MyTable";
     nResult = sqlite3_exec(db,strSql.c_str(),callback,NULL,&errmsg);
       if (nResult != SQLITE_OK)
     {
         sqlite3_close(db);
         cout<<errmsg<<endl;
         sqlite3_free(errmsg);
         return 0;
     }
 
     sqlite3_close(db);
     return 0;
 }
 
 int callback(void* ,int nCount,char** pValue,char** pName)
 {
     string s;
     for(int i=0;i<nCount;i++)
     {
         s+=pName[i];
         s+=":";
         s+=pValue[i];
         s+="\n";
     }
     cout<<s<<endl;
     return 0;
 }

非回调函数执行select调用sqlite3_get_table

有时候你还是想要非回调的select查询。这可以通过sqlite3_get_table函数做到。

int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
char ***pazResult,   /* Results of the query */
int *pnRow,          /* Number of result rows written here */
int *pnColumn,       /* Number of result columns written here */
char **pzErrmsg     /* Error msg written here */
 );
 void sqlite3_free_table(char **result);

第1个参数不再多说，看前面的例子。第2个参数是sql语句，跟sqlite3_exec里的sql是一样的。是一个很普通的以\0结尾的char*字符串。第3个参数是查询结果，它依然一维数组（不要以为是二维数组，更不要以为是三维数组）。它内存布局是：字段名称，后面是紧接着是每个字段的值。下面用例子来说事。第4个参数是查询出多少条记录（即查出多少行，不包括字段名那行）。第5个参数是多少个字段（多少列）。第6个参数是错误信息，跟前面一样，这里不多说了。

pazResult返回的字符串数量实际上是(*pnRow+1)*(*pnColumn),因为前(*pnColumn)个是字段名

修改例子，使用 sqlite3_get_table, 来去的结果集：

 #include <iostream>
 using namespace std;
 #include "sqlite/sqlite3.h"
 int main()
 {
     sqlite3* db;
     int nResult = sqlite3_open("test.db",&db);
     if (nResult != SQLITE_OK)
     {
         cout<<"打开数据库失败："<<sqlite3_errmsg(db)<<endl;
         return 0;
     }
     else
     {
         cout<<"数据库打开成功"<<endl;
     }
 
     char* errmsg;
 
     nResult = sqlite3_exec(db,"create table MyTable(id integer primary key autoincrement,name varchar(100))",NULL,NULL,&errmsg);
      if (nResult != SQLITE_OK)
      {
          sqlite3_close(db);
          cout<<errmsg;
          sqlite3_free(errmsg);
         return 0;
     }
     string strSql;
     strSql+="begin;\n";
     for (int i=0;i<100;i++)
     {
         strSql+="insert into MyTable values(null,'heh');\n";
     }
     strSql+="commit;";
 
     nResult = sqlite3_exec(db,strSql.c_str(),NULL,NULL,&errmsg);
 
     if (nResult != SQLITE_OK)
     {
         sqlite3_close(db);
         cout<<errmsg<<endl;
         sqlite3_free(errmsg);
         return 0;
     }
 
     strSql = "select * from MyTable";
     char** pResult;
     int nRow;
     int nCol;
     nResult = sqlite3_get_table(db,strSql.c_str(),&pResult,&nRow,&nCol,&errmsg);
       if (nResult != SQLITE_OK)
     {
         sqlite3_close(db);
         cout<<errmsg<<endl;
         sqlite3_free(errmsg);
         return 0;
     }
 
     string strOut;
     int nIndex = nCol;
     for(int i=0;i<nRow;i++)
     {
         for(int j=0;j<nCol;j++)
         {
             strOut+=pResult[j];
             strOut+=":";
             strOut+=pResult[nIndex];
             strOut+="\n";
             ++nIndex;
         }
     }
     sqlite3_free_table(pResult);
     cout<<strOut<<endl;
     sqlite3_close(db);
     return 0;
 }

sqlite3_prepare_v2/sqlite3_step

如果既不想写回调函数，又想避免sqlite3_get_table之后麻烦的一维数组遍历，那么利用sqlite3_prepare_v2执行sql select语句，让后sqlite3_step遍历select执行的返回结果是一个非常方便的solution. 当然，你必须要明白sqlite3_prepare_v2不仅仅能够执行table的queryselection，也能方便地进行sql Delete,Insert, Update等其他一些操作。它能帮你把sql语句的执行操作变的更加优雅。

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */</span>
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

int sqlite3_step(sqlite3_stmt*);

例子：

#include <sqlite3.h>
#include <string>
#include <stdio.h>
using namespace std;
void doTest() {
	sqlite3* conn = NULL;
	//1. 打开数据库
	int result = sqlite3_open("D:/mytest.db",&conn);
	if (result != SQLITE_OK) {
		sqlite3_close(conn);
		return;
	}
	const char* createTableSQL ="CREATE TABLE TESTTABLE (int_col INT,
		float_col REAL, string_col TEXT)";
		sqlite3_stmt* stmt = NULL;
	int len = strlen(createTableSQL);
		//2. 准备创建数据表，如果创建失败，需要用sqlite3_finalize释放sqlite3_stmt对象以防止内存泄露。
		if (sqlite3_prepare_v2(conn,createTableSQL,len,&stmt,NULL) != SQLITE_OK) {
			if (stmt)
				sqlite3_finalize(stmt);
			sqlite3_close(conn);
			return;
		}
			//3. 通过sqlite3_step命令执行创建表的语句。对于DDL和DML语句而言，sqlite3_step执行正确的返回值
			//只有SQLITE_DONE，对于SELECT查询而言，如果有数据返回SQLITE_ROW，当到达结果集末尾时则返回
			//SQLITE_DONE。
			if (sqlite3_step(stmt) != SQLITE_DONE) {
				sqlite3_finalize(stmt);
				sqlite3_close(conn);
				return;
			}
			//4. 释放创建表语句对象的资源。
			sqlite3_finalize(stmt);
			printf("Succeed to create test table now.\n");
			//5. 显式的开启一个事物。
			sqlite3_stmt* stmt2 = NULL;
			const char* beginSQL = "BEGIN TRANSACTION";
			if (sqlite3_prepare_v2(conn,beginSQL,strlen(beginSQL),&stmt2,NULL) !=
				SQLITE_OK) {
					if (stmt2)
						sqlite3_finalize(stmt2);
					sqlite3_close(conn);
					return;
			}
			if (sqlite3_step(stmt2) != SQLITE_DONE) {
				sqlite3_finalize(stmt2);
				sqlite3_close(conn);
				return;
			}
			sqlite3_finalize(stmt2);
			//6. 构建基于绑定变量的插入数据。
			const char* insertSQL = "INSERT INTO TESTTABLE VALUES(?,?,?)";
			sqlite3_stmt* stmt3 = NULL;
			if (sqlite3_prepare_v2(conn,insertSQL,strlen(insertSQL),&stmt3,NULL) !=
				SQLITE_OK) {
					if (stmt3)
						sqlite3_finalize(stmt3);
					sqlite3_close(conn);
					return;
			}
			int insertCount = 10;
			const char* strData = "This is a test.";
			//7. 基于已有的SQL语句，迭代的绑定不同的变量数据
			for (int i = 0; i < insertCount; ++i) {
				//在绑定时，最左面的变量索引值是1。
				sqlite3_bind_int(stmt3,1,i);
				sqlite3_bind_double(stmt3,2,i * 1.0);
				sqlite3_bind_text(stmt3,3,strData,strlen(strData),SQLITE_TRANSIENT);
				if (sqlite3_step(stmt3) != SQLITE_DONE) {
					sqlite3_finalize(stmt3);
					sqlite3_close(conn);
					return;
				}
				//重新初始化该sqlite3_stmt对象绑定的变量。
				sqlite3_reset(stmt3);
				printf("Insert Succeed.\n");
			}
			sqlite3_finalize(stmt3);
			//8. 提交之前的事物。
			const char* commitSQL = "COMMIT";
			sqlite3_stmt* stmt4 = NULL;
			if (sqlite3_prepare_v2(conn,commitSQL,strlen(commitSQL),&stmt4,NULL) !=
				SQLITE_OK) {
					if (stmt4)
						sqlite3_finalize(stmt4);
					sqlite3_close(conn);
					return;
			}
			if (sqlite3_step(stmt4) != SQLITE_DONE) {
				sqlite3_finalize(stmt4);
				sqlite3_close(conn);
				return;
			}
			sqlite3_finalize(stmt4);
			//9. 为了方便下一次测试运行，我们这里需要删除该函数创建的数据表，否则在下次运行时将无法
			//创建该表，因为它已经存在。
			const char* dropSQL = "DROP TABLE TESTTABLE";
			sqlite3_stmt* stmt5 = NULL;
			if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt5,NULL) != SQLITE_OK)
			{
				if (stmt5)
					sqlite3_finalize(stmt5);
				sqlite3_close(conn);
				return;
			}
			if (sqlite3_step(stmt5) == SQLITE_DONE) {
				printf("The test table has been dropped.\n");
			}
			sqlite3_finalize(stmt5);
			sqlite3_close(conn);
}
int main() {
	doTest();
	return 0;
}
//输出结果如下：
//Succeed to create test table now.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//Insert Succeed.
//The test table has been dropped.

这个例子中给出了事物的操作，下面进行介绍

事物/高效批量插入

在给出操作步骤之前先简单说明一下批量插入的概念，以帮助大家阅读其后的示例代码。事实上，批量插入并不是什么新的概念，在其它关系型数据库的C接口API中都提供了一定的支持，只是接口的实现方式不同而已。纵观众多流行的数据库接口，如OCI(Oracle API)、MySQL API和PostgreSQL API等，OCI提供的编程接口最为方便，实现方式也最为高效。SQLite作为一种简单灵活的嵌入式数据库也同样提供了该功能，但是实现方式并不像其他数据库那样方便明显只是通过一种隐含的技巧来达到批量插入的目的，其逻辑如下：

1). 开始一个事物，以保证后面的数据操作语句均在该事物内完成。在SQLite中，如果没有手工开启一个事物，其所有的DML语句都是在自动提交模式下工作的，既每次操作后数据均被自动提交并写入磁盘文件。然而在非自动提交模式下，只有当其所在的事物被手工COMMIT之后才会将修改的数据写入到磁盘中之前修改的数据都是仅仅驻留在内存中。显而易见，这样的批量写入方式在效率上势必会远远优于多迭代式的单次写入操作。

2). 基于变量绑定的方式准备待插入的数据，这样可以节省大量的sqlite3_prepare_v2函数调用次数，从而节省了多次将同一SQL语句编译成SQLite内部识别的字节码所用的时间。事实上，SQLite的官方文档中已经明确指出，在很多时候sqlite3_prepare_v2函数的执行时间要多于sqlite3_step函数的执行时间，因此建议使用者要尽量避免重复调用sqlite3_prepare_v2函数。在我们的实现中，如果想避免此类开销，只需将待插入的数据以变量的形式绑定到SQL语句中，这样该SQL语句仅需调用sqlite3_prepare_v2函数编译一次即可，其后的操作只是替换不同的变量数值。