FMDB for iOS development and fmdb for ios development

FMDB for iOS development and fmdb for ios development
IOSDevelopment DatabaseFMDB　　1.Introduction

Purpose: If you need to save a large amount of complex data, use a database, such as a pass test item.

Common databases:

(1) Microsoft SQL Server 2000/2008, which is widely used by SMEs

(2) Oracle is complicated and large enterprises use more

(3) Mysql database, many websites used

(4) sqlite: local database, access data fast enough, direct access to files

Simple enough, not very complete with functions than other database software, enough to use

Small enough, the system should not exceed 1 M, suitable for use on mobile terminals

2. MesaSQlite Use

Example: Use data storage to store information of students in a class

Student ID sid username password score

1501 zhangsan 123 100

1502 lilei 321 90

1503 wangwu 222 80

(1) Create a database

(2) create a data table

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

(4) Common Database Operations

Add, delete, modify, and query

3. SQL Structured query statement

　　

SQL, Structure Query Language, and structured Query Language are used to operate databases (create tables, add, delete, modify, and Query data)

(1) create a data table

create table StudentInfo(sid integer, username varchar(20), password varchar(20),score varchar(20)) create table if not exists StudentInfo(sid integer, username varchar(20), password varchar(20),score varchar(20)) 

(2) Insert data

insert into StudentInfo(sid,username,password,score) values(1503,'wangwu','222','80')

(3) query data

<1> query all data in a table

select * from StudentInfo;

<2> query the specified field

Instance: Query all usernames

select username from StudentInfo

<3> query based on specified conditions

Instance: Find all information whose name is zhansan

 

select * from StudentInfo where username='zhangsan' 

<4> query based on multiple conditions

Instance: Find all information whose uname is zhansan and whose gender is boy

select * from StudentInfo where username='zhangsan' and password='123'

<5> sort data after Query

// Select * from StudentInfo order by score in ascending order of age // select * from StudentInfo order by score desc

<6> get the number of data rows

select count(*) from StudentInfo 

(4) modify data

update StudentInfo set score='100'  where username='zhangsan';

(5) delete data

delete from StudentInfo where sid='1503'

4. FMDBDatabase Operations

(1) Configuration

Import file,

Add the binary library libsqlite3.dylib,

Include header file # import "FMDatabase. h"

 

5. Database Used in projects - Singleton Design Mode (1) Statement and implementation of a singleton

# Import <Foundation/Foundation. h> # import "FirstLevelModel. h "# import" SecondLevelModel. h "# import" LeafLevelModel. h "@ interface DatabaseManager: NSObject // obtain the singleton object method + (id) your instance; // obtain the first-level directory-(NSArray *) firstLevels; // obtain the second-level directory-(NSArray *) secondLevels :( NSString *) str; // obtain the third-level directory-(NSArray *) leafLevels :( NSString *) str; @ end

# Import "DatabaseManager. h "# import" FMDatabase. h "@ interface DatabaseManager () {FMDatabase * _ database;} @ end @ implementation DatabaseManager // obtain the singleton object method + (id) Wait instance {static DatabaseManager * dc = nil; if (dc = nil) {dc = [[DatabaseManager alloc] init];} return dc;}-(id) init {if (self = [super init]) {[self openDatabase];} return self;}-(void) openDatabase {NSString * path = [[NSBundle mainBundle] PathForResource: @ "data. sqlite" ofType: nil]; _ database = [[FMDatabase alloc] initWithPath: path]; if (! _ Database. open) {NSLog (@ "failed to open") ;}// obtain 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];} return muArr;} // obtain the 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: @ "sco Unt "]; [muArr addObject: model];} return muArr;} // get level 3 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];} return muArr;} @ end

Click to download the code