Database Operations (SQLite)

SQLite is a lightweight relational database. SQLite originally designed to be used in embedded systems, it occupies very little resources, in the embedded device, only need hundreds of K of memory is enough, currently used in Android, IOS, Windows phone and other smartphones. When using SQLite with IOS, you only need to add libsqlite3.dylib dependencies and introduce sqlite3.h header files.

Database operations include opening the database, creating tables, adding, deleting, changing, and checking tables. The following code gives the database operation.

To create and open a database:

//Get Database pathNsarray *paths =nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES); NSString*documents = [Paths Objectatindex:0]; NSString*database_path =[Documents Stringbyappendingpathcomponent:dbname]; //If the database exists, open it directly with sqlite3_open (don't worry if the database does not exist Sqlite3_open will be created automatically)//Open the database, where [path utf8string] is to convert NSString to C string, because SQLite3 is portable C (instead of//objective-c), it does not know what is nsstring.     if(Sqlite3_open ([Database_path utf8string], &db) = =SQLITE_OK) {          returnYES; }Else{          returnNO; NSLog (@"Database open Failed");      Sqlite3_close (DB); }  

There is no command to create a database in iOS, and when using Sqlite3_open, if the database file does not exist, it creates the database itself and opens the database if it exists. After opening the database, you can create tables and manipulate the contents of the table, and Sqlite3 in iOS uses Sqlite3_exec to create tables, insert table content, modify table contents, delete table contents, and so on, using SQLITE3_PREPARE_V2 to query tables.

-(void ) Execsql: (nsstring *) SQL { if   ([self opendb]) { char  *err;  if  (sqlite3_exec (DB, [SQL Utf8string], NULL, NULL, &ERR)!= SQLITE_OK) {NSLog ( @ "  database operation data failed!
     );  else  {NSLog ( @ " %@  "  

To create a table:

NSString *sqlcreatetable =  [NSString stringWithFormat:@ "CREATE TABLE IF not EXISTS '%@ ' ('%@ ' INTEGER PR Imary KEY autoincrement, '%@ ' text, '%@ ' INTEGER, '%@ ' text)', tablename,id,name,age,address];      [Self execsql:sqlcreatetable];  

Insert data:

-(void) insertdata{NSString*insertsql1=[NSString stringWithFormat:@"INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')", TABLENAME, NAME, age, ADDRESS,@"Zhang San",@" -",@"Jinan"];            [Self EXECSQL:INSERTSQL1]; NSString*INSERTSQL2 =[NSString stringWithFormat:@"INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')", TABLENAME, NAME, age, ADDRESS,@"John Doe",@" A",@"Jinan"];        [Self EXECSQL:INSERTSQL2]; }  

Update data

-(void) updatedata{      *updatesql = [NSString stringwithformat:                        @ "  UPDATE '%@ ' SET '%@ ' = '%@ ' WHERE '%@ ' = '%@ '",                        TABLENAME, age   ,  @"   ", age,  @");      [Self execsql:updatesql];  }  

Delete Table contents:

-(void) deletedata{      *sdeletesql = [NSString stringwithformat:                        @ "  Delete from%@ where%@ = '%@ '",                        @" Zhang San "];      [Self execsql:sdeletesql];  }  

Inquire:

-(void) selectdata{[self opendb]; NSString*sqlquery =[NSString stringWithFormat:@"SELECT * from%@", TABLENAME]; Sqlite3_stmt*statement; if(SQLITE3_PREPARE_V2 (DB, [SQLQuery utf8string],-1, &statement, nil) = =SQLITE_OK) {                    //The query results set a single line to traverse all the records, where the numbers correspond to the column values, note the column values here           while(Sqlite3_step (statement) = =Sqlite_row) {              Char*name = (Char*) Sqlite3_column_text (statement,1); NSString*nsnamestr =[[NSString Alloc]initwithutf8string:name]; intAge = Sqlite3_column_int (statement,2); Char*address = (Char*) Sqlite3_column_text (statement,3); NSString*nsaddressstr =[[NSString alloc]initwithutf8string:address]; NSLog (@"name:%@ age:%d address:%@", Nsnamestr,age, NSADDRESSSTR); }      }Else{NSLog (@"Select error:%@", sqlquery);  } sqlite3_close (db); }