IOS -FMDB Tutorial

I. Introduction to FMDB

1. SQLite, is a lightweight database, is a C language library, a small, fast, high reliability. SQLite file formats are stable, cross-platform, and backward compatible.
2. FMDB encapsulates SQLite

FMDB common classes

1. FMDatabase: A single SQLite database for executing SQL statements. SQL statement learning links
2. FMResultSet: Perform a query on an FMDatabase result set
3. FMDatabaseQueue: This class is used to perform queries and updates in multiple threads

FMDB integration

1. Cocoapdos integration

• Initialize thePodfilefile

pod init
Copy the code

• The editorPodfileTo addFMDB

# Uncomment the next line to define a global platform for your project
# platform :ios, '9.0'

target 'MyApp' do
    # Comment the next line if you're not using Swift and don't want to use dynamic frameworks
    use_frameworks!

    # Pods for MyApp2

    pod 'FMDB'
    # pod 'FMDB/FTS'   # FMDB with FTS
    # pod 'FMDB/standalone'   # FMDB with latest SQLite amalgamation source
    # pod 'FMDB/standalone/FTS'   # FMDB with latest SQLite amalgamation source and FTS
    # pod 'FMDB/SQLCipher'   # FMDB with SQLCipher
end

Copy the code

• The installationFMDB

 pod install
Copy the code

2. Carthage installation

• Make sure that yourCarthageUse the command line terminal to navigate to your project home directory and execute the following command:

$ echo ' github "ccgus/fmdb" ' > ./Cartfile
$ carthage update
Copy the code

3. Install Swift Package Manager

Package (the name: "FMDB", url: "https://github.com/ccgus/fmdb",. UpToNextMinor (from: "2.7.7")), the product (name: "FMDB", package: "FMDB")Copy the code

4: FMDB use

1. Database creation, I use singleton here to do FMDB add, delete, change, check operations, as shown below:

• FMDBManager initialization, as well as database initialization

+ (instancetype)shareInstance{ static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ _instance = [[FMDBManager alloc]init]; }); return _instance; } // init - (instanceType)init {self = [super init]; if (self) { [self initDataBase]; } return self; } /// initDataBase - (void)initDataBase{///library path NSString *libPath = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES)[0]; /// database path NSString *dbPath = [libPath stringByAppendingString:@"/user.db"]; _db = [FMDatabase databaseWithPath:dbPath]; BOOL isOpen = [_db open]; if (! IsOpen) {NSLog(@" database open failed "); } else {/// CREATE TABLE NSString * SQL = @"CREATE TABLE IF NOT EXISTS Student (ID INTEGER PRIMARY KEY AUTOINCREMENT,name Text,userID text, className text, age text , grade text)"; BOOL result = [_db executeUpdate:sql]; If (result) {NSLog(@" create database successfully %@",dbPath); } [_db close]; }}Copy the code

2. Common database operations

• Add (to add a record)

- (void)addStudent:(Student *)student{ [_db open]; BOOL result = [_db executeQuery:@"INSERT INTO Student (name,userID,className,age,grade) values(?,?,?,?,?)",student.name,student.userID,student.className,student.age,student.grade]; If (result) {NSLog(@" insert success "); } else {NSLog(@" insert failed "); } [self.db close]; }Copy the code

• To delete (a record).

[self.db open]; FMResultSet *result = [self.db executeQuery:@"DELETE FROM Student WHERE id = ?",ID]; If (result) {NSLog(@" delete successfully "); } [self.db close];Copy the code

• To look up (a record).

- (NSArray *)searchStudent:(NSString *)name{
    
    [self.db open];
    NSMutableArray *studentArr = [NSMutableArray array];
    FMResultSet *result = [self.db executeQuery:@"SELECT * FROM Student WHERE name = ?", name];
    while ([result next]) {
        Student *s = [[Student alloc]init];
        s.ID = [[result stringForColumn:@"id"] integerValue];
        s.name = [result stringForColumn:@"name"];
        s.className = [result stringForColumn:@"className"];
        s.grade = [result stringForColumn:@"grade"];
        s.userID = [result stringForColumn:@"userID"];
        s.age = [result stringForColumn:@"age"];
        [studentArr addObject:s];
    }
    
    return [studentArr mutableCopy];;
    
}
Copy the code

• Search (query all records)

- (NSArray *)getAllStudentList{
    
    [self.db open];
    NSMutableArray *studentArr = [NSMutableArray array];
    FMResultSet *result = [self.db executeQuery:@"SELECT * FROM Student"];
    while ([result next]) {
        Student *s = [[Student alloc]init];
        s.ID = [[result stringForColumn:@"id"] integerValue];
        s.name = [result stringForColumn:@"name"];
        s.className = [result stringForColumn:@"className"];
        s.grade = [result stringForColumn:@"grade"];
        s.userID = [result stringForColumn:@"userID"];
        s.age = [result stringForColumn:@"age"];
        [studentArr addObject:s];
    }
    
    return [studentArr mutableCopy];;
}

Copy the code

• Modify (modify database records)

- (void)updateStudentGrade:(Student *)student{ [self.db open]; FMResultSet *result = [self.db executeQuery:@"UPDATE Student SET name = ? WHERE id = ?",student.grade,student.ID]; If (result) {NSLog(@" update successful "); } [self.db close]; }Copy the code

The demo portal