Detailed explanation of iOS database sqlite for addition, deletion, modification, and query

Detailed explanation of iOS database sqlite for addition, deletion, modification, and query

//// CLViewController. m // LessonDatabase // Created by lanouhn on 14-9-19. // Copyright (c) 2014 vaercly@163.com Chen conglei. all rights reserved. // # import "CLViewController. h "# import" DatabaseHelper. h "# import" Student. h "@ interface CLViewController () @ end @ implementation CLViewController-(void) viewDidLoad {[super viewDidLoad]; // Do any additional setup after loading the view, typically from a nib. // database (D Atabase): a data storage warehouse that stores a table, especially Excel and Numbers, all of which store data in the form of tables. You can create multiple tables // common databases: sqlite, MySQL, SQLServer, Oracle, Access // Why do I need to read and write data from the database 1 file and archive the data at one time? The memory usage is high. 2. Database Data efficiency is high, it is reflected in addition, deletion, modification, Query, and Query. // SQL Structured Query Language is used to Query database operation statements (addition, deletion, modification, and Query). // SQL statements are case insensitive, the string must contain "" or ''// primary key: it is the unique identifier of a data record. A table can only have one primary key, and the primary key cannot be repeated, generally, if you set the primary key name to "id" without assigning a value, auto-increment // * indicates that all fields/where are the conditions. // create a table: creat table name (whether the field Name field data type is a primary key, number of field name fields Data Type, field Name field data type ...) // query: select field name (or *) from table name where field name = value // Add: insert into Table Name (Field 1, Field 2 ...) values (value 1, value 2 ...) // modify: update table name set field = value where field = value // delete: delete from table name where field = value}-(void) didreceivemorywarning {[super didreceivemorywarning]; // Dispose of any resources that can be recreated .} -(IBAction) selectAll :( id) sender {NSMutableArray * array = [DatabaseHelper getAllStudents]; f Or (Student * stu in array) {NSLog (@ "% @", stu) ;}}-(IBAction) selectOne :( id) sender {Student * stu = [DatabaseHelper getStudentWithID: 2]; NSLog (@ "% @", stu);}-(IBAction) insetOne :( id) sender {Student * stu = [[Student alloc] init]; stu. name = @ "vaercly"; stu. sex = @ "man"; stu. age = 22; BOOL result = [DatabaseHelper insertStudent: stu]; NSLog (@ "% d", result);}-(IBAction) updateName :( id) sender {[Databas EHelper updateStudentName: @ "Chen Cong lei" byID: 5];}-(IBAction) deleteOne :( id) sender {[DatabaseHelper deleteStudentWithID: 5] ;}@ end // Datebase. m // LessonDatabase // Created by lanouhn on 14-9-19. // Copyright (c) 2014 vaercly@163.com Chen conglei. all rights reserved. // # define FILE_NAME @ "Database. sqlite "# import" Database. h "static sqlite3 * db = nil; @ implementation Database // open the Database + (sqlite3 *) openDB {if (! Db) {// 1 obtain the path of the document folder // parameter 1: folder name parameter 2: Search domain parameter 3: whether to use the absolute path NSString * docPath = [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) firstObject]; // obtain the path of the database file NSString * dbPath = [docPath stringByAppendingPathComponent: FILE_NAME]; // class for file management in iOS, responsible for copying files, delete the file. Move the file NSFileManager * fm = [NSFileManager defaultManager]; // determine whether the sqlite file exists in the document if (! [Fm fileExistsAtPath: dbPath]) {// obtain it in *. path of the sqlite file in the app NSString * boundlePath = [[NSBundle mainBundle] pathForResource: @ "Database" ofType: @ "sqlite"]; NSError * error = nil; // Set *. copy the sqlite file in the app to dbPath BOOL result = [fm copyItemAtPath: boundlePath toPath: dbPath error: & error]; // if the file fails to be copied, print the error message if (! Result) {NSLog (@ "% @", error) ;}// open database parameter 1: file path (UTF8String can convert the NSString of OC to char in C) parameter 2: accept the database pointer sqlite3_open ([dbPath UTF8String], & db);} return db;} // close the database + (void) closeDB {sqlite3_close (db); db = nil ;} @ end /// DatabaseHelper. m // LessonDatabase // Created by lanouhn on 14-9-19. // Copyright (c) 2014 vaercly@163.com Chen conglei. all rights reserved. // # import "DatabaseHelper. h "# import" Student. h "# import" Database. h "@ implementation DatabaseHelper // query all students + (NSMutableArray *) getAllStudents {// open the Database sqlite3 * db = [Database openDB]; // Database operation pointer stmt: statement sqlite3_stmt * stmt = nil; // verify SQL correctness parameter 1: Database pointer, parameter 2: SQL statement, parameter 3: the length of an SQL statement-1 indicates an infinite length (the length will be automatically matched). Parameter 4: returns the database operation pointer. Parameter 5: Reserved parameters for future preparation, NULL int result = sqlite3_prepare_v2 (db, "select * from Student",-1, & stmt, NULL); NSMutableArray * studentArr = [NSMutableArray array]; // determine the SQL Execution result if (result = SQLITE_ OK) {while (sqlite3_step (stmt) = SQLITE_ROW) {// a row of data exists. // The number of columns starts from 0. int ID = sqlite3_column_int (stmt, 0); const unsigned char * name = sqlite3_column_text (stmt, 1 ); const unsigned char * sex = sqlite3_column_text (stmt, 2); int age = sqlite3_column_int (stmt, 3 ); // get blob type // 1 get length int length = sqlite3_column_bytes (stmt, 4); // 2 get data const void * photo = sqlite3_column_blob (stmt, 4 ); // 3 to NSData * photoData = [NSData dataWithBytes: photo length: length]; // 4 to UIImage * image = [UIImage imageWithData: photoData]; // encapsulate the Student model Student * student = [[Student alloc] init]; student. ID = ID; student. name = [NSString stringwithuf8string :( const char *) name]; student. sex = [NSString stringwithuf8string :( const char *) sex]; student. age = age; student. photo = image; // Add it to the array [studentArr addObject: student] ;}// release the stmt pointer sqlite3_finalize (stmt); // close the Database [Database closeDB]; return studentArr;} // query a single Student + (Student *) getStudentWithID :( NSInteger) aID {sqlite3 * db = [Database openDB]; sqlite3_stmt * stmt = nil; NSString * sqlStr = [NSString stringWithFormat: @ "select * from Student where id = % d", aID]; int result = sqlite3_prepare_v2 (db, [sqlStr UTF8String],-1, & stmt, NULL); Student * student = nil; if (result = SQLITE_ OK) {if (sqlite3_step (stmt) = SQLITE_ROW) {int ID = sqlite3_column_int (stmt, 0); const unsigned char * name = sqlite3_column_text (stmt, 1); const unsigned char * sex = sqlite3_column_text (stmt, 2); int age = sqlite3_column_int (stmt, 3 ); int length = sqlite3_column_bytes (stmt, 4); const unsigned char * photo = sqlite3_column_blob (stmt, 4); NSData * photoData = [NSData dataWithBytes: photo length: length]; UIImage * image = [UIImage imageWithData: photoData]; student = [[Student alloc] init]; student. ID = ID; student. name = [NSString stringwithuf8string :( const char *) name]; student. sex = [NSString stringwithuf8string :( const char *) sex]; student. age = age; student. photo = image ;}} sqlite3_finalize (stmt); [Database closeDB]; return student ;}// Add a new Student + (BOOL) insertStudent :( student *) aStudent {sqlite3 * db = [Database openDB]; sqlite3_stmt * stmt = nil; NSString * sqlStr = [NSString stringWithFormat: @ "insert into Student (name, sex, age) values ('% @', '% @', '% D') ", aStudent. name, aStudent. sex, aStudent. age]; int result = sqlite3_prepare_v2 (db, [sqlStr UTF8String],-1, & stmt, NULL); if (result = SQLITE_ OK) {// if (sqlite3_step (stmt) = SQLITE_DONE) {sqlite3_finalize (stmt); [Database closeDB]; return YES ;}} sqlite3_finalize (stmt ); [Database closeDB]; return NO;} // modify the Student name + (BOOL) updateStudentName :( NSString *) aName byID :( NSInteger) aID {sqlite3 * db = [Database openDB]; sqlite3_stmt * stmt = nil; NSString * sqlStr = [NSString stringWithFormat: @ "update Student set name = '% @ 'where id = % d", aName, aID]; int result = sqlite3_prepare_v2 (db, [sqlStr UTF8String],-1, & stmt, NULL); if (result = SQLITE_ OK) {if (sqlite3_step (stmt) = SQLITE_ROW) {// a judgment should be added to the perception. if this row exists, modify if (sqlite3_step (stmt) = SQLITE_DONE) {sqlite3_finalize (stmt); [Database closeDB]; return YES ;}} sqlite3_finalize (stmt); [Database closeDB]; return NO ;}// delete a student + (BOOL) deleteStudentWithID :( NSInteger) aID {sqlite3 * db = [Database openDB]; sqlite3_stmt * stmt = nil; NSString * sqlStr = [NSString stringWithFormat: @ "delete from Student where id = % d", aID]; int result = sqlite3_prepare_v2 (db, [sqlStr UTF8String],-1, & stmt, NULL); if (result = SQLITE_ OK) {if (sqlite3_step (stmt) = SQLITE_ROW) {// a judgment should be added to the perception. if this row exists, delete if (sqlite3_step (stmt) = SQLITE_DONE) {sqlite3_finalize (stmt); [Database closeDB]; return YES ;}} sqlite3_finalize (stmt); [Database closeDB]; return NO ;}@ end /// Student. h // LessonDatabase // Created by lanouhn on 14-9-19. // Copyright (c) 2014 vaercly@163.com Chen conglei. all rights reserved. // # import
 
  
@ Interface Student: NSObject @ property (nonatomic, assign) NSInteger ID; @ property (nonatomic, retain) NSString * name; @ property (nonatomic, retain) NSString * sex; @ property (nonatomic, assign) NSInteger age; @ property (nonatomic, retain) UIImage * photo; @ end