IOS-sqlite3 & amp; FMDB use code demonstration, iossqlite3 use

IOS-sqlite3 & FMDB use code demonstration, iossqlite3 use

Database operations are a frequently used operation. We have to consider how to use the database and what database to use in iOS.

Small data can be stored in plist or NSUserDefaults. When the Data volume is large, we can use sqlite or Core Data.

Here, we will first introduce the system API of sqlite, and then introduce the third-party database FMDB, which is easier to use than using the system's sqlite.

For database operations, we can simply refer to adding, deleting, modifying, and querying. The following code is used to add, delete, modify, and query operations.

# Import <UIKit/UIKit. h> # import <sqlite3.h> # import "Person. h "@ interface ViewController: UIViewController {NSMutableArray * _ arrayData;}-(IBAction) addButtonClick :( id) sender;-(IBAction) deleteButtonClick :( id) sender;-(IBAction) updateButtonClick :( id) sender;-(IBAction) selectButtonClick :( id) sender; @ end # import "ViewController. h "@ interface ViewController () @ endsqlite3 * _ database; @ implementation ViewContr Oller-(void) viewDidLoad {[super viewDidLoad]; // Do any additional setup after loading the view, typically from a nib. _ arrayData = [[NSMutableArray alloc] init]; for (int I = 0; I <5; I ++) {Person * p = [[Person alloc] init]; p. name = [NSString stringWithFormat: @ "wyg % d", I]; p. age = 20 + I; [_ arrayData addObject: p];} [self createTable];}-(void) didreceivemorywarning {[super didreceivemorywarnin G]; // Dispose of any resources that can be recreated .} -(IBAction) addButtonClick :( id) sender {[self insertData];}-(IBAction) deleteButtonClick :( id) sender {[self deleteData];}-(IBAction) updateButtonClick :( id) sender {[self updateData];}-(IBAction) selectButtonClick :( id) sender {[self selectData];}-(void) openDatabase {if (sqlite3_open ([[self getFilePath] UTF8String], & _ database) = SQLITE_ OK) {NSLog (@ "Open success");} else {NSLog (@ "open failed") ;}}-(void) createTable {[self openDatabase]; NSString * SQL = @ "create table if not exists students (name text, age integer)"; sqlite3_stmt * stmt = nil; if (sqlite3_prepare_v2 (_ database, [SQL UTF8String], -1, & stmt, nil) = SQLITE_ OK) {if (sqlite3_step (stmt) = SQLITE_DONE) {NSLog (@ "create table success ");} else {NSLog (@ "create table failed ");}} Sqlite3_finalize (stmt); sqlite3_close (_ database);}-(void) insertData {[self openDatabase]; NSString * SQL = @ "insert into students (name, age) values (?,?) "; Sqlite3_stmt * stmt = nil; for (int I = 0; I <_ arrayData. count; I ++) {Person * p = _ arrayData [I]; if (sqlite3_prepare_v2 (_ database, [SQL UTF8String],-1, & stmt, nil) = SQLITE_ OK) {sqlite3_bind_text (stmt, 1, [p. name UTF8String], strlen ([p. name UTF8String]), nil); sqlite3_bind_int (stmt, 2, p. age); if (sqlite3_step (stmt) = SQLITE_DONE) {NSLog (@ "insert success");} else {NSLog (@ "insert failed ");}}} sqlite3_finalize (stmt); sqlite3_close (_ database);}-(void) deleteData {[self openDatabase]; sqlite3_stmt * stmt = nil; NSString * SQL = @ "delete from students where age> 23"; if (sqlite3_prepare_v2 (_ database, [SQL UTF8String],-1, & stmt, nil) = SQLITE_ OK) {if (sqlite3_step (stmt) = SQLITE_DONE) {NSLog (@ "delete success");} else {NSLog (@ "delete failed") ;}} sqlite3_finalize (stmt ); sqlite3_close (_ database);}-(void) updateData {[self openDatabase]; sqlite3_stmt * stmt = nil; NSString * SQL = @ "update students set name = 'www 'where age> 22"; if (sqlite3_prepare_v2 (_ database, [SQL UTF8String],-1, & stmt, nil) = SQLITE_ OK) {if (sqlite3_step (stmt) = SQLITE_DONE) {NSLog (@ "update success");} else {NSLog (@ "update failed ");}} sqlite3_finalize (stmt); sqlite3_close (_ database);}-(void) selectData {[self openDatabase]; NSString * SQL = @ "select * from students "; sqlite3_stmt * stmt = nil; NSMutableArray * array = [[NSMutableArray alloc] init]; if (sqlite3_prepare_v2 (_ database, [SQL UTF8String],-1, & stmt, nil) = SQLITE_ OK) {while (sqlite3_step (stmt) = SQLITE_ROW) {char * name = (char *) sqlite3_column_text (stmt, 0); int age = sqlite3_column_int (stmt, 1); Person * p = [[Person alloc] init]; p. name = [NSString stringwithuf8string: name]; p. age = age; [array addObject: p] ;}} NSLog (@ "--- % @", array); sqlite3_finalize (stmt); sqlite3_close (_ database );} -(NSString *) getFilePath {NSString * docuPath = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) [0]; NSString * filePath = [docuPath stringByAppendingPathComponent: @ "database. db "]; return filePath;} @ endSqlite

FMDB is an encapsulation of the system sqlite, which is more convenient to use. We can download it from github.

The following code encapsulates FMDB.

These include opening and closing operations on the database and basic operations on the database.

Database creation, opening, closing, and other operations are encapsulated in the DatabaseTool class.

Basic database operations, such as creating tables, adding, deleting, modifying, and querying, are encapsulated in the ContactDAO class.

The operation data is encapsulated in the Contact.

**************************************** * ****************** DatabaseTool ** # import <Foundation/Foundation. h> # import "FMDB. h "@ interface DatabaseTool: NSObject + (FMDatabase *) Restore database; + (BOOL) close; @ end # import" DatabaseTool. h "static FMDatabase * _ db = nil; @ implementation DatabaseTool + (FMDatabase *) relational database {if (_ db = nil) {_ db = [[FMDatabase alloc] initWithPath: [self getFilePath];} [self op En]; return _ db;} + (NSString *) getFilePath {NSString * documentPath = canonical (NSDocumentDirectory, NSUserDomainMask, YES) [0]; NSString * path = [documentPath stringByAppendingPathComponent: @ "file. db "]; return path;} + (BOOL) open {if ([_ db open] = NO) {[_ db close]; NSAssert (NO, @ "database opening failed");} // sets the database cache mechanism [_ db setShouldCacheStatements: YES]; return YES ;}+ (BOOL) close {if ([_ db Close] = NO) {NSAssert (NO, @ "database close failed");} return YES ;} @ end ************************************** * ************************ Contact ** # import <Foundation/Foundation. h> // modal Model @ interface Contact: NSObject {int _ cid; NSString * _ name; NSString * _ phone;} @ property (nonatomic, assign) int cid; @ property (nonatomic, copy) NSString * name; @ property (nonatomic, copy) NSString * phone; // custom initialization method-(id) initWithID :( I Nt) ID name :( NSString *) aName phone :( NSString *) aPhone; @ end # import "Contact. h "@ implementation Contact @ synthesize cid = _ cid; @ synthesize name = _ name; @ synthesize phone = _ phone;-(id) initWithID :( int) ID name :( NSString *) aName phone :( NSString *) aPhone {if (self = [super init]) {self. cid = ID; self. name = aName; self. phone = aPhone;} return self;}-(void) dealloc {[_ name release]; [_ phone release]; [sup Er dealloc];} @ end ************************************** * ************************ ContactDAO ** # import <Foundation/Foundation. h> # import "DatabaseTool. h "// connect the data model and database object to create the table. add, delete, modify, and query @ interface ContactDAO: NSObject + (void) createContactTable; + (void) insertData; + (NSMutableArray *) queryData; + (void) updateDataWithID :( int) cid; + (void) deleteDataWithID :( int) cid; @ end # import "ContactDAO. h "# import" Contact. h "@ I Mplementation ContactDAO + (void) createContactTable {FMDatabase * database = [DatabaseTool sharesharedatabase]; if ([database tableExists: @ "contact"] = NO) {[database executeUpdate: @ "create table contact (id integer primary key autoincrement not null, name text, phone text)"] ;}[ DatabaseTool close] ;}+ (void) insertData {FMDatabase * database = [DatabaseTool sharesharedatabase]; [database executeUpdate: @ "insert in To contact (name, phone) values (?,?) ", @" Wyg ", @" 1992 "]; [DatabaseTool close];} + (NSMutableArray *) queryData {NSMutableArray * array = [[NSMutableArray alloc] init]; FMDatabase * database = [DatabaseTool shareDatabase]; FMResultSet * set = [database executeQuery: @ "select * from contact"]; while ([set next]) {int cid = [set intForColumn: @ "id"]; NSString * name = [set stringForColumn: @ "name"]; NSString * phone = [set stringForColumn: @ "phone"]; Contact * c = [[Contact alloc] initWithID: cid name: name phone: phone]; [array addObject: c]; [c release];} [set close]; [DatabaseTool close]; return array;} + (void) updateDataWithID :( int) cid {NSNumber * num = [NSNumber numberWithInt: cid]; FMDatabase * database = [DatabaseTool sharesharedatabase]; [database executeUpdate: @ "update contact set name = 'www 'where id =? ", Num]; [DatabaseTool close] ;}+ (void) deleteDataWithID :( int) cid {NSNumber * num = [NSNumber numberWithInt: cid]; FMDatabase * database = [DatabaseTool sharesharedatabase]; [database executeUpdate: @ "delete from contact where id =? ", Num]; [DatabaseTool close];} @ endFMDB