iOS Development Database Fmdb

IOS Database of development FMDB 　　 1. Introduction

Requirements: If you need to save a large amount of complex data, use a database, such as a compliance test project

Commonly used databases:

(1) Microsoft SQL Server 2000/2008, SMB uses more

(2) Oracle is more complex and large enterprises use more

(3) MySQL database, web site use more

(4) SQLite: Local database, access to data fast enough, direct access to files

Simple enough, the functionality is not particularly complete with respect to other database software, enough

Small enough that the system does not exceed 1M and is suitable for use on the mobile side

2. Mesasqlite Use

Example: Using a data store to store information about a class student

Learning number SID user name username password Password Score score

1501 Zhangsan 123 100

1502 Lilei 321 90

1503 WANGWU 222 80

(1) Create a database

(2) Creating a data table

(3) Design data table (add multiple fields/columns)

(4) Common operation of database

increase, delete, change, check

3. SQL Structured Query Statements

　　

SQL, Structure query Language, Structured Query Language, the role of the operation of the database (create table, data additions and deletions)

(1) Creating a data table

Create TableStudentinfo (SIDinteger, usernamevarchar( -), Passwordvarchar( -), scorevarchar( -)) Create Table if  not existsStudentinfo (SIDinteger, usernamevarchar( -), Passwordvarchar( -), scorevarchar( -))

(2) Inserting data

Insert  into Values (1503,'wangwu','222','  the ')

(3) Query data

<1> querying all data in a table

Select *  from Studentinfo;

<2> querying the specified fields

Example: Query all names username

Select  from Studentinfo

<3> query based on specified criteria

Example: Find all information for name Zhansan

Select *  from where username='zhangsan'

<4> query based on multiple criteria

Example: Find all the information that uname is Zhansan and gender is boy

Select *  from where username='zhangsan' and password='  123'

<5> need to sort after query

// Sort by age ascending Select *  from Order  by  // in descending order of age Select*fromorder by   desc

<6> Get rows of data

Select Count (* from

(4) Modify the data

Update Set score='+'  where username=' Zhangsan ';

(5) Delete data

Delete  from where sid='1503'

4. FMDB Operation Database

(1) configuration

Import files,

Add a binary library libsqlite3.dylib,

Contains header file #import "FMDatabase.h"

5. the database is used in the project - single case design mode (1) Declaration and implementation of a single case

#import<Foundation/Foundation.h>#import "FirstLevelModel.h"#import "SecondLevelModel.h"#import "LeafLevelModel.h"@interfaceDatabasemanager:nsobject//get a Singleton object method+(ID) shareinstance;//get the first level directory-(Nsarray *) Firstlevels;//get second level directory-(Nsarray *) Secondlevels: (NSString *) str;//get the third level directory-(Nsarray *) Leaflevels: (NSString *) str;@end

#import "DatabaseManager.h"#import "FMDatabase.h"@interfaceDatabasemanager () {fmdatabase*_database;}@end@implementationDatabasemanager//get a Singleton object method+(ID) shareinstance{StaticDatabasemanager *DC =Nil; if(dc==Nil) {DC=[[Databasemanager alloc] init]; }    returnDC;}- (ID) init{if(self =[Super Init])    {[Self opendatabase]; }    returnSelf ;}- (void) opendatabase{NSString*path = [[NSBundle mainbundle] Pathforresource:@"Data.sqlite"Oftype:nil]; _database=[[Fmdatabase alloc] initwithpath:path]; if(!_database.open) {NSLog (@"Open Failed"); }}//get the first level directory-(Nsarray *) firstlevels{NSString*sql =@"SELECT * from Firstlevel"; Fmresultset*resultset =[_database Executequery:sql]; Nsmutablearray*muarr =[[Nsmutablearray alloc] init];  while([ResultSet next]) {//Firstlevelmodel *model =[[Firstlevelmodel alloc] init]; Model.pid= [ResultSet stringforcolumn:@"PID"]; Model.pname= [ResultSet stringforcolumn:@"pname"]; Model.pcount= [ResultSet stringforcolumn:@"Pcount"];            [Muarr Addobject:model]; }    returnMuarr;}//get second level directory-(Nsarray *) Secondlevels: (NSString *) str{NSString*sql =@"SELECT * from Secondlevel where pid=?"; Fmresultset*resultset =[_database Executequery:sql,str]; Nsmutablearray*muarr =[[Nsmutablearray alloc] init];  while([ResultSet next]) {//Secondlevelmodel *model =[[Secondlevelmodel alloc] init]; Model.pid= [ResultSet stringforcolumn:@"PID"]; Model.sid= [ResultSet stringforcolumn:@"Sid"]; Model.sname= [ResultSet stringforcolumn:@"sname"]; Model.scount= [ResultSet stringforcolumn:@"Scount"];            [Muarr Addobject:model]; }    returnMuarr;}//get the third level directory-(Nsarray *) Leaflevels: (NSString *) str{NSString*sql =@"SELECT * from Leaflevel where sid=?"; Fmresultset*resultset =[_database Executequery:sql,str]; Nsmutablearray*muarr =[[Nsmutablearray alloc] init];  while([ResultSet next]) {//Leaflevelmodel *model =[[Leaflevelmodel alloc] init]; Model.pid= [ResultSet stringforcolumn:@"PID"]; Model.sid= [ResultSet stringforcolumn:@"Sid"]; Model.sname= [ResultSet stringforcolumn:@"sname"]; Model.pname= [ResultSet stringforcolumn:@"pname"]; Model.mquestion= [ResultSet stringforcolumn:@"mquestion"]; Model.mdesc= [ResultSet stringforcolumn:@"Mdesc"]; Model.mid= [ResultSet stringforcolumn:@"Mid"]; Model.manswer= [ResultSet stringforcolumn:@"Manswer"]; Model.munknow= [ResultSet stringforcolumn:@"Munknow"]; Model.mtype= [ResultSet stringforcolumn:@"Mtype"]; Model.mimage= [ResultSet stringforcolumn:@"Mimage"];            [Muarr Addobject:model]; }    returnMuarr;}@end

Click to download the code

iOS Development Database Fmdb