Welcome to follow my official account. I will regularly share some solutions to the problems I encounter in the project and some practical skills of iOS. At the present stage, I will mainly sort out some basic knowledge and record it

The post will also be synchronized to my blog: ppsheep.com

Sqlite will be involved in almost every APP code. It is a lightweight database, which is extremely convenient to use on the mobile end, because sqLite’s native interface is written in C, which is not particularly friendly. Therefore, FMDB emerged when there was a powerful library

The project structure

First, let’s look at the structure of my project

Packaging ideas

The basic encapsulation idea of data layer is as follows:

  • First we need a database management class. This management class mainly manages database files, because we may have many database files and need such a management class
  • A basic BaseDBStore, where we need to perform some basic operations, including add, delete, change, search, and then each module needs to use the database, they have separate sub-modules, inherited from Base

Database management class implementation code:

#import #import "FMDB.h" @interface PPSDBManager : NSObject /** * DB queues can define other databases */ @Property (nonatomic, strong) FMDatabaseQueue *commonQueue; + (PPSDBManager *)sharedInstance; /** create a database file @param userId @return database file address */ + (NSString *)dbPath:(NSString *)userId; @endCopy the code

The idea is that each user is a database file, so it’s easy to distinguish, and each user can have different database files, so we’ve created only one, but of course we can create many

#import "PPSDBManager.h" static PPSDBManager *manager; @implementation PPSDBManager +(PPSDBManager *)sharedInstance{ static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ NSString *userID = @"ppsheep"; Manager = [[PPSDBManager alloc] initWithUserId:userID]; }); return manager; } /** In general, if there is a user APP, So I can pass in the id of the current user to distinguish between the database. @param userId userId @return instance */ - (instancetype)initWithUserId:(NSString *)userId{if (self = [super init]) { NSString *commonQueuePath = [PPSDBManager dbPath:userId]; self.commonQueue = [FMDatabaseQueue databaseQueueWithPath:commonQueuePath]; } return self; } /** @return address */ + (NSString *)dbPath: (NSString *)userId{ NSString *path = [NSString stringWithFormat:@"%@/User/%@/Setting/DB/", NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0], userId]; if (! [[NSFileManager defaultManager] fileExistsAtPath:path]) {// Create database file NSError *error; [[NSFileManager defaultManager] createDirectoryAtPath:path withIntermediateDirectories:YES attributes:nil error:&error];  If (error) {NSLog(@" failed to create database file: %@",path); } } return [path stringByAppendingString:@"common.sqlite3"]; } @endCopy the code

BaseStore

#import #import "PPSDBManager.h" @interface PPSDBBaseStore : NSObject // Database operation queue (from NADBManager, default commonQueue) @property (nonatomic, weak) FMDatabaseQueue *dbQueue; /** * createTable */ - (BOOL)createTable:(NSString*)tableName withSQL:(NSString*)sqlString; /* * execute SQL statement with array parameters (add, delete, modify) */ -(BOOL)excuteSQL:(NSString*)sqlString withArrParameter:(NSArray*)arrParameter; */ -(BOOL)excuteSQL:(NSString*)sqlString withDicParameter:(NSDictionary*)dicParameter; /* * execute formatted SQL statements (add, delete, modify) */ - (BOOL)excuteSQL:(NSString *)sqlString,... ; */ - (void)excuteQuerySQL (NSString*)sqlStr resultBlock (void (^)(FMResultSet * rsSet))resultBlock; @endCopy the code

Some of the basic operations defined by Base are clearly commented, so I won’t explain them all

// query statement - (BOOL)excuteSQL:(NSString *)sqlString,... { __block BOOL ok = NO; if (self.dbQueue) { va_list args; va_list *p_args; p_args = &args; va_start(args, sqlString); [self.dbQueue inDatabase:^(FMDatabase *db) { ok = [db executeUpdate:sqlString withVAList:*p_args]; }]; va_end(args); } return ok; }Copy the code

Query method with parameters:

- (BOOL)excuteSQL:(NSString *)sqlString withArrParameter:(NSArray *)arrParameter
{
    __block BOOL ok = NO;
    if (self.dbQueue) {
        [self.dbQueue inDatabase:^(FMDatabase *db) {
            ok = [db executeUpdate:sqlString withArgumentsInArray:arrParameter];
        }];
    }
    return ok;
}Copy the code

All the methods in Base call FMDB

The User module

Now, we have a User module and need to implement User access. First, we need a file to hold the SQL, and separate the SQL

PPSDBUserSQL.h

#ifndef PPSDBUserSQL_h #define PPSDBUserSQL_h #define USER_TABLE_NAME @"user" /** * @param uid Current user Corp mailbox prefix * @param fid Corp email prefix * @param email Corp email * * @return */ #define SQL_CREATE_USER_TABLE @"CREATE TABLE IF NOT EXISTS %@(\ uid TEXT,\ name TEXT,\ email TEXT,\ phoneNum TEXT,\ ext1 TEXT,\ ext2 TEXT,\ ext3 TEXT,\ ext4 INTEGER DEFAULT (0),\ ext5 INTEGER DEFAULT (0),\ ext6 INTEGER DEFAULT (0),\ PRIMARY KEY(uid))" #define SQL_ADD_USER @"INSERT OR REPLACE INTO %@ ( uid, name, email, phoneNum, ext1, ext2, ext3, ext4, ext5, ext6) VALUES ( ? ,? ,? ,? ,? ,? ,? ,? ,? ,?) " #define SQL_SELECT_USER @"SELECT * FROM %@ WHERE uid = '%@'" #define SQL_GET_ALL_USER @"SELECT * FROM %@ " #define SQL_DELETE_USER @"DELETE FROM %@ WHERE uid = '%@'" #define SQL_UPDATE_USER @"UPDATE %@ SET name = ? , email = ? , phoneNum = ? WHERE uid = '%@' " #endif /* PPSDBUserSQL_h */Copy the code

Then there are the concrete operations. User’s Store contains the basics

#import "PPSDBBaseStore.h" @class PPSUser; @interface PPSDBUserStore: PPSDBBaseStore // modified - (BOOL)updateUserData:(NSArray *)users; // add - (BOOL)addUser:(NSArray *)users; // query - (NSMutableArray *)usersByUserIds:(NSArray *)userIds; // delete - (BOOL)deleteUserByUid:(NSArray *)userIds; - (NSArray *)allUsers; @endCopy the code

Batch query user, pass id array

-(NSMutableArray *)usersByUserIds:(NSArray *)userIds{ NSArray *ids = [userIds copy]; __block NSMutableArray *data = [[NSMutableArray alloc] init]; for (NSString *userId in ids) { NSString *sqlString = [NSString stringWithFormat:SQL_SELECT_USER, USER_TABLE_NAME, userId]; [self excuteQuerySQL:sqlString resultBlock:^(FMResultSet *retSet) { while ([retSet next]) { NSDictionary *dic = @{ @"userId" : [retSet stringForColumn:@"uid"], @"email" : [retSet stringForColumn:@"email"], @"name" : [retSet stringForColumn:@"name"], @"phoneNum" : [retSet stringForColumn:@"phoneNum"], }; PPSUser *user = [[PPSUser alloc] initWithDic:dic]; [data addObject:user];  } [retSet close]; }]; } return data; }Copy the code

Update users in batches, passing in an array of users

-(BOOL)updateUserData:(NSArray *)users{
    NSArray *userCopy = [users copy];
    BOOL ok = YES;
    for (PPSUser *user in userCopy) {
        NSString *sql = [NSString stringWithFormat:SQL_UPDATE_USER, USER_TABLE_NAME, user.userId];
        NSArray *arrPara = [NSArray arrayWithObjects:
                            PPSNoNilString(user.name),
                            PPSNoNilString(user.email),
                            PPSNoNilString(user.phoneNum),
                            @"", @"", @"", @0, @0, @0,  nil];
        ok = [self excuteSQL:sql withArrParameter:arrPara];
    }
    return ok;
}Copy the code

use

I’m going to write it directly in the ViewController for convenience, but in real development, you’re going to do database operations in the admin class

Lazy loading of a user database

-(PPSDBUserStore *)store{ if (! _store) { _store = [[PPSDBUserStore alloc] init]; } return _store; }Copy the code

Initialize the user and insert it directly

- (void)initUsers{
    NSMutableArray *arr = [NSMutableArray array];
    for (int i=0; i<10; i++) {
        PPSUser *user = [[PPSUser alloc] init];
        user.userId = [NSString stringWithFormat:@"%ld_userId",(long)i];
        user.name = [NSString stringWithFormat:@"%ld_userName",(long)i];
        user.email = [NSString stringWithFormat:@"%ld_email",(long)i];
        user.phoneNum = [NSString stringWithFormat:@"%ld_phoneNum",(long)i];
        [arr addObject:user];
    }
    [self.store addUser:arr];
}Copy the code

I’m not going to post the code anymore, project project, I put it

Github.com/yangqian111…