SQLite3 Embedded Database

 About the application of sqlite3 database in iphone project in general, there are several steps: 1. Right-click the Frameworks folder in the new project, add the Libsqlit3.dylib library, 2, declare a variable of type sqlite3 in the header file of. h;         Here to specifically explain this variable, the beginning of the time is null, after the database opened is not empty, do not know sqlite3 in the structure of the design is for what purpose, for the time being to remember so use it, how you have a better understanding of it might as well tell me.    Sqlite3 *_database; 3, to obtain the Documents folder path, this is actually the database file final existence of the directory, the new is also built in this directory;-(NSString *) Getdocuments{nsarray *paths = Nssearchpathfordirectoriesindomains (Nsdocumentdirectory,nsuserdomainmask,yes); NSString *documentsdirectory = [Pathsobjectatindex:0];return [documentsdirectorystringbyappendingpathcomponent: kfilename];//This kfilename is your name for the database} 4, create and open the database/* If the database exists, open it directly with sqlite3_open (don't worry if the database does not exist Sqlite3_open automatically created) open the database , here [path utf8string] is to convert NSString to C string, because SQLite3 is written in portable C (not objective-c), it does not know what is nsstring.*/-(void) opendb {// Get database path NSString *path = [Selfgetdocuments];if (Sqlite3_open ([pathutf8string], &_database)!=SQLITE_OK) {//   Close Database Sqlite3_close (self._database) If the database fails to open;}} 5. Create table//CREATE table-(BOOL) Createtablemath: (sqlite3*) DB {//This sentence is familiar SQL statement char *sql = "CREATE table if not exists testtable (ID INTEGER PRIMARY KEY autoincrement, TestID int,testvalue text) "; Sqlite3_stmt *state The MENT;//SQLITE3_PREPARE_V2 interface parses an SQL statement into the statement structure. Accessing the database using this interface is currently a good method//The third parameter I wrote is-1, this parameter means the length of the preceding SQL statement. If less than 0,sqlite will automatically calculate its length (consider the SQL statement as a string ending with a. The fourth parameter is a pointer to the SQLITE3_STMT pointer. The parsed SQL statements are placed in this structure. The fifth parameter is nil. If the function succeeds (the return value is SQLITE_OK and statement is not NULL), then you can begin inserting binary data. If the SQL statement resolves an error, the program returns Nsinteger Sqlreturn =sqlite3_prepare_v2 (_database, SQL,-1, &statement,nil); if (Sqlreturn! = SQLITE_OK) {NSLog (@ "error:failed to prepare statement:create Test table"); Returnno;} NSLog (@ "... Create test table successful. >> ");//EXECUTE statement statement int success =sqlite3_step (statement);//release sqlite3_stmt sqlite3_finalize (statement);// Execute SQL statement failed if (success!=sqlite_done) {NSLog (@ "error:failed to dehydrate:create table test"); Returnno;} NSLog (@ "Create table ' testtable ' successed.");  Returnyes;} 6. Insert Data//Insert Data Mode one-(BOOL) insertrecord1{if ([selfopendb]) {sqlite3_stmt *statement;staticChar *sql= "INSERT into testtable (Testid,testvalue) values (?,?); /? Indicates an undetermined value, and its value is then inserted int success2=sqlite3_prepare_v2 (_database, SQL,-1, &statement, NULL); if (SUCCESS2!=SQLITE_OK) {sqlite3_close (_database); returnno}//here 1, 2 represents the first few question marks Sqlite3_bind_int (statement,1, @ "1"); Sqlite3_bind_text ( statement,2, @ "Test value", -1,sqlite_transient), success2=sqlite3_step (statement); Sqlite3_finalize (statement); if ( SUCCESS2!=SQLITE_OK) {sqlite3_close (_database); Returnno;} Sqlite3_close (_database); returnyes;} Returnno;} In most cases it is recommended that this approach be more concise Insert Data mode two-(void) InsertRecord2: (NSString *) tableName withField1: (nsstring*) field1 Field1value: ( nsstring*) Field1value withField2: (nsstring*) field2 field2value: (nsstring*) field2value{nsstring *sql=[nsstring stringwithformat:@ "Insert or replace into '%@ ' ('%@ ', '%@ ') VALUES ('%@ ', '%@ ')", Tablename,field1,field1value,field2, Field2value];char *err;if (sqlite3_exec (DB, [SQL Utf8string], NULL, NULL, &ERR)!=SQLITE_OK) {sqlite3_close (db);     Nsassert (0, "insert record Failed");}} 7. Traverse Data//Query record-(nsmutablearray*) GetResult: (int) Searchid{nsmutablearray *array=[nsmutablearrayarraywitharray:10];if ([ SELFOPENDB]) {sqlite3_stmt *statement=nil;char *sql= "Select Testid,testvalue from TestTable where testid=?"; if (SQLITE3_PREPARE_V2 (_database, SQL,-1, &statement, NULL)!=sqlite_ok) {Returnno;} else {sqlite3_bind_int (statement,1, Searchid);//This 1 is the question mark in the SQL statement that represents the while (Sqlite3_step (statement) ==sqlite_row) {/ /convenience result set, this is Sqlite3 own method. sqltestlist* sqllist=[[sqltestlist alloc]init];sqllist.sqlid=sqlite3_column_int (statement,0);//Remove the value of the corresponding field, The database is also an integer type char *strtext= (char*) Sqlite3_column_text (statement,1);//Convert Format sqllist.sqltext=[ Nsstringstringwithutf8string:strtext]; [Arrayaddobject:sqllist]; [Sqllistrelease];}} Sqlite3_finalize (statement); Sqlite3_close (_database);} return [Arrayretain];}

　　

SQLite3 Embedded Database