FMDB and iosfmdb
Most of the SQLite APIs originally generated in OS are in C language code, which is inconvenient to use. Therefore, many third-party frameworks for SQLite encapsulation have emerged, FMDB is one of the excellent frameworks. FMDB encapsulates the C language API of SQLite in OC mode. Its appearance makes the operation of SQLite more concise and easy to use. Compared with Apple's Core Data framework, FMDB is more lightweight and flexible.
FMDB: https://github.com/ccgus/fmdb
After the FMDB file is downloaded, you must import the following files in the project and use the libsqlite3.dylib dependency package.
FMDatabase: A single SQLite database used to execute SQL statements.
FMResultSet: queries an FMDatabase result set.
FMDatabaseQueue: this class is used when multiple threads are used to execute queries and updates.
1. Create and open a database
1. Obtain database objects
NSString *path=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0]; path=[path stringByAppendingPathComponent:@"test.sqlite"]; dataBase=[FMDatabase databaseWithPath:path];
2. Open the database. If it does not exist, create and open the database.
BOOL open = [dataBase open]; if (open) {NSLog (@ "dataBase opened successfully ");}
3. Create a table
NSString * create1 = @ "create table if not exists t_user (id integer autoincrement primary key, name varchar)"; BOOL c1 = [dataBase executeUpdate: create1]; if (c1) {NSLog (@ "table created successfully ");}
4. insert, delete, and modify data
NSString * insertSql = @ "insert into t_user (id, name) values (?,?) "; // Insert Statement 1 bool inflag1 = [dataBase executeUpdate: insertSql, @ (2), @" admin "]; // insert Statement 2 bool inflag2 = [dataBase executeUpdate: insertSql withArgumentsInArray: @ [@ "admin", @ (5)]; // insert Statement 3 bool inflag3 = [dataBase executeUpdateWithFormat: @ "insert into t_user (id, name) values (% @, % d) ", @" admin ", 6]; // delete statement NSString * delete = @" delete from t_user "; BOOL dflag = [dataBase executeUpdate: delete]; if (dflag) {NSLog (@ "delete ");} // Modify the NSString * update = @" update t_user set name =? "; BOOL flag = [dataBase executeUpdate: update, @" zhangsan "]; if (flag) {NSLog (@" modified successfully ");}
5. FMResultSet for Data Query FMDB provides multiple methods to obtain different types of data
NSString * sql=@" select * from t_user ";FMResultSet *result=[dataBase executeQuery:sql];while(result.next){int ids=[result intForColumn:@"id"];NSString * name=[result stringForColumn:@"name"];int ids=[result intForColumnIndex:0];NSString * name=[result stringForColumnIndex:1];NSLog(@"%@,%d",name,ids); }
If the application uses multiple threads to operate the database, you need to use FMDatabaseQueue to ensure thread security. An FMDatabase object cannot be used in multiple threads in an application to operate on the database. This will cause database data confusion. To ensure the security of multi-threaded database operations, FMDB uses FMDatabaseQueue. It is very simple to use FMDatabaseQueue. First, a database file address is used to initialize FMDatabaseQueue, and then a closure (block) can be set) passed in the inDatabase method. Operate the database in the closure, instead of directly managing the FMDatabase.
2. Multithreading
NSString * path = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) [0]; path = [path stringByAppendingPathComponent: @ "test. sqlite "]; FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath: path]; [queue inDatabase: ^ (FMDatabase * db) {NSString * create = @ "create table if not exists t_book (id integer, name varchar)"; BOOL c1 = [db executeUpdate: create]; if (c1) {NSLog (@ "successful ") ;}}]; [Queue inDatabase: ^ (FMDatabase * db) {NSString * insertSql = @" insert into t_book (id, name) values (?,?) "; // Insert Statement 1 bool inflag = [db executeUpdate: insertSql, @ (2), @" admin "]; if (inflag) {NSLog (@ "inserted successfully") ;}}]; [queue inDatabase: ^ (FMDatabase * db) {FMResultSet * data = [db executeQuery: @ "select * from t_book"]; while (data. next) {int ids = [data intForColumn: @ "id"]; NSString * name = [data stringForColumn: @ "name"]; NSLog (@ "% @", name); NSLog (@ "% I", ids) ;}}];