http://blog.csdn.net/kanghua68/article/details/6168897
Sqlite是一个轻量级的数据库引擎,大概几百K左右,所以在嵌入式设备中应用广泛;而且由于接口简单,使其在非嵌入式领域中也被大量使用; 详细内容请参考官方介绍:http://www.sqlite.org/about.html,这里就不再赘述;
一、工程中引入sqlite3
数据库创建前需要在程序中引入sqlite3库文件以及在代码中添加sqlite3头文件;以XCode开发环境为例,需要在“Target->Build->Other Linker Flags”中加入“-lsqlite3”链接选项;代码文件中加入#import <sqlite3.h>;
二、数据库创建
数据库创建类似文件创建,使用sqlite3_open()函数,如果不存在就创建,如果存在就打开;
- sqlite3 *db = NULL;
- int rc;
- char * dbPath = "myTest.db3";
- rc = sqlite3_open(dbPath, &db);
- if(rc)
- {
- printf("Open database failed!");
- sqlite3_close(db);
- return 1;
- }
使用sqlite3创建的数据库文件名称以db3作为文件类型并非必需,而是和sqlite文件有个区别;数据文件首次创建后,还需要创建表;代码如下:
- //create or open Table
- char *sql = " CREATE TABLE ContactTbl("/
- "ID INTEGER PRIMARY KEY,"/
- "Name VARCHAR(80),"/
- "TEL1 VARCHAR(20),"/
- "EMAIL VARCHAR(40)"/
- ");";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
网上有许多查看和编辑sqlite3数据库文件的可视化小工具,例如:SQLiteSpy.exe;这样进行数据库分析和修改时就不必每次都自己写代码来操作了;
三、数据库插入
向刚才新建的表中添加一个条目,由于sqlite网站中都是英文数据类型,我使用UTF8编码格式的代码文件(XCode默认代码文件格式为UTF8编码)直接输入中文字符,能够正常执行,其他编码格式没有试过,感觉应该不会有什么问题,改天有时间尝试一下;
- //insert three item
- sql = "INSERT INTO /"ContactTbl/" VALUES(NULL, '小康', '18602914000', 'xiaokang@183.com');";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
四、数据库查询
使用sqlite3_get_table()进行数据查询时,返回一个char **类型的字符数组;返回的行列值分别是表中数据的行和列数目;需要注意的是,返回的字符数组比实际数据多了一行表头;所以在使用和输出时需要注意;按照上面的数据格式,行、列值分别为1、4;而字符数组的数目为(nRow+1)*nColumn;
- //query data, don't forget to free the result
- char **aResult = NULL;
- int nRow = 0;
- int nColumn = 0;
- sql = "SELECT * FROM ContactTbl";
- sqlite3_get_table(db, sql, &aResult, &nRow, &nColumn, &zErrMsg);
- printf("%s/n",zErrMsg);
下面是格式输出代码:
- //printf("ID/t/t姓名/t/t电话/t/t电子邮件/t/t/n");
- //此处不需要输出表头,因为字符数组中已经包括表头
- int rowIndex;
- for(rowIndex=0; rowIndex<(nRow+1)*nColumn; rowIndex++)
- {
- printf("%s/t/t", aResult[rowIndex]);
- if(0==(rowIndex+1)%nColumn)
- {
- printf("/n");
- }
- }
- sqlite3_free_table(aResult);
例如下面这张表以及查询结果数据格式列举如下:
Name | Age
-----------------------
Alice | 43
Bob | 28
Cindy | 21
aResult[0] = "Name";
aResult[1] = "Age";
aResult[2] = "Alice";
aResult[3] = "43";
aResult[4] = "Bob";
aResult[5] = "28";
aResult[6] = "Cindy";
aResult[7] = "21";
简单说,获取到的字符数组就是将表中某行某列(包括表头),作为一个字符串,这样依次存储下来,成为一个字符串数组,编码格式为UTF8;
另外,由于查询返回的字符数组是在sqlite3_get_table()函数内部分配的内存空间,使用完成后需要使用sqlite3_free_table()函数进行释放,此处不能简单的通过sqlite3_free()函数释放;
五、数据库删除
数据库条目的删除和插入类似,代码如下:
- //delete item
- sql = "DELETE FROM ContactTbl WHERE ID = 3;";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
下面附上整个main.m的源代码:
- //
- // main.m
- // sqlite3Test
- //
- // Created by kh on 10-11-22.
- // Copyright 2010 cx. All rights reserved.
- //
- #import <sqlite3.h>
- #import <Cocoa/Cocoa.h>
- int main(int argc, char *argv[])
- {
- sqlite3 *db = NULL;
- int rc;
- char * zErrMsg = NULL;
- char * dbPath = "myTest.db3";
- //create or open database
- rc = sqlite3_open(dbPath, &db);
- if(rc)
- {
- printf("Open database failed!");
- sqlite3_close(db);
- return 1;
- }
- //create or open Table
- char *sql = " CREATE TABLE ContactTbl("/
- "ID INTEGER PRIMARY KEY,"/
- "Name VARCHAR(80),"/
- "TEL1 VARCHAR(20),"/
- "EMAIL VARCHAR(40)"/
- ");";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
- //insert three item
- sql = "INSERT INTO /"ContactTbl/" VALUES(NULL, '小康', '18602914000', 'xiaokang@183.com');";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
- sql = "INSERT INTO /"ContactTbl/" VALUES(NULL, '小陈', '18602935000', 'xiaochen@183.com');";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
- sql = "INSERT INTO /"ContactTbl/" VALUES(NULL, '小霞', '18602966000', 'xiaoxia@183.com');";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
- //query data, don't forget to free the result
- char **aResult = NULL;
- int nRow = 0;
- int nColumn = 0;
- sql = "SELECT * FROM ContactTbl";
- sqlite3_get_table(db, sql, &aResult, &nRow, &nColumn, &zErrMsg);
- printf("%s/n",zErrMsg);
- //printf("ID/t/t姓名/t/t电话/t/t电子邮件/t/t/n");
- int rowIndex;
- for(rowIndex=0; rowIndex<(nRow+1)*nColumn; rowIndex++)
- {
- printf("%s/t/t", aResult[rowIndex]);
- if(0==(rowIndex+1)%nColumn)
- {
- printf("/n");
- }
- }
- sqlite3_free_table(aResult);
- //delete item
- sql = "DELETE FROM ContactTbl WHERE ID = 3;";
- sqlite3_exec(db, sql, 0, 0, &zErrMsg);
- printf("%s/n",zErrMsg);
- //and requery the data
- sql = "SELECT * FROM ContactTbl";
- sqlite3_get_table(db, sql, &aResult, &nRow, &nColumn, &zErrMsg);
- printf("After delete the ID 3/n");
- for(rowIndex=0; rowIndex<(nRow+1)*nColumn; rowIndex++)
- {
- printf("%s/t/t", aResult[rowIndex]);
- if(0==(rowIndex+1)%nColumn)
- {
- printf("/n");
- }
- }
- sqlite3_close(db);
- char para[50] = {0};
- sprintf(para, "rm %s", dbPath);
- system(para);
- return 0;
- }
最后的删除语句用于在测试后删除数据库文件,避免重复执行后数据库中有重复条目,呵呵;针对以上代码,Consel输出如下:
- (null)
- (null)
- (null)
- (null)
- (null)
- ID Name TEL1 EMAIL
- 1 小康 18602914000 xiaokang@183.com
- 2 小陈 18602935000 xiaochen@183.com
- 3 小霞 18602966000 xiaoxia@183.com
- (null)
- After delete the ID 3
- ID Name TEL1 EMAIL
- 1 小康 18602914000 xiaokang@183.com
- 2 小陈 18602935000 xiaochen@183.com