From C # To Objective-C, learn Apple development step by step (7) -- use FMDB to operate Sqlite Databases

This series mainly introduces a series of thoughts and experiences from C # development on a Windows platform to Apple development on the Mac platform. This series of articles is gradually accumulated in the initial stage, we hope to provide you with a better and more real experience on the conversion process. This article mainly introduces IOS Program Development Based on XCode, introduces how to use FMDB to operate Sqlite databases, and designs database operation classes in an abstract way to achieve reuse, simplification, and efficient development. In. the NET field has been developed for many years. Common projects require database operations, including Oracle, SqlServer, Mysql, Sqlite, and Access databases. These databases are very common. the various systems developed in the. NET environment may have to deal with more or less one of the databases. This is what I am dedicated to refining. to simplify, reuse, and efficiently develop Winform development frameworks, Web development frameworks, and hybrid development frameworks. Although I have made some research and development in the IOS field, even if IOS devices emphasize more about a multimedia device, database operations are still indispensable, therefore, I will first start with the familiar database to learn how the database operates and what ready-made components are available for reference and learning. Developed in IOS and talked about dealing with databases. Many people may be familiar with the FMDB database component. It greatly simplifies the operation of Sqlite databases in IOS, in most cases, we only need to deal with FMDatabase and FMResultSet, which is very convenient to use. 1. In order to better introduce the overall content of FMDB operations, we will first introduce various FMDB operations. 1) Open Database Operation FMDatabase * db = [FMDatabase databaseWithPath: dbPath]; if (! [Db open]) {NSLog (@ "unable to open Database"); return;} 2) use FMDB for database operation executeUpdate. For operations without returning records, you can use executeUpdate to delete records. The following is the function for deleting records-(BOOL) deleteByCondition :( NSString *) condition {NSString * query = [NSString stringWithFormat: @ "Delete FROM % @ Where % @", self. tableName, condition]; BOOL result = [self. database executeUpdate: query]; return result;} Of course, for many SQLite database operations, we can use parameterized statements for operations, as shown in the following example [db executeUpdate: @ "INS Ert into User (Name, Age) VALUES (?,?) ", @" Zhang San ", [NSNumber numberWithInt: 30] parameterization can also be used: character as the parameter identifier, as shown below, is a database operation function copy code encapsulated by me-(BOOL) deleteById :( id) key {NSDictionary * argsDict = [NSDictionary dictionaryWithObjectsAndKeys: key, @ "id", nil]; NSString * query = [NSString stringWithFormat: @ "Delete FROM % @ Where % =: id", self. tableName, self. primaryKey]; BOOL result = [self. database executeUpdate: query withParameterDictionary: argsDict]; return r Esult;} copy code 3) if the operation to return the set has a statement to return the set, we need to use the FMResultSet object. This object is similar to the cursor we have seen before, but it is just a different thing. FMResultSet * rs = [db executeQuery: @ "SELECT * FROM User"]; rs = [db executeQuery: @ "SELECT * FROM User WHERE Age =? ", @" 20 "]; while ([rs next]) {NSLog (@" % @ ", [rs stringForColumn: @" Name "], [rs stringForColumn: @ "Age"]);} the above Code operation returns a Resultset set for traversal. We can obtain different data content according to some methods of Resultset. Compared with the FMResult operation method, the original Sqlite database operation code is as follows. Is it much easier to use the FMDB class library to operate databases. Copy the code NSString * sqlQuery = @ "SELECT * FROM User"; sqlite3_stmt * statement; if (sqlite3_prepare_v2 (db, [sqlQuery UTF8String],-1, & statement, nil) = SQLITE_ OK) {while (sqlite3_step (statement) = SQLITE_ROW) {char * name = (char *) sqlite3_column_text (statement, 1 ); NSString * nsNameStr = [[NSString alloc] initwithuf8string: name]; int age = sqlite3_column_int (statement, 2); char * address = (char *) SQL Ite3_column_text (statement, 3); NSString * nsAddressStr = [[NSString alloc] initwithuf8string: address]; NSLog (@ "name: % @ age: % d address: % @", nsNameStr, age, nsAddressStr) ;}} sqlite3_close (db); the copy code FMResultSet method can be used to obtain different data: intForColumn: longForColumn: longLongIntForColumn: boolForColumn: doubleForColumn: stringForColumn: dateForColumn: dataForColumn: dataNoCopyForColumn: UTF8StringForColumnIndex: objectF OrColumn: 2. database operation layer design the above section describes how to use the FMDB class library to operate the Sqlite database. This gives us a general understanding of the database operation process in IOS. However, I think this is too generic and inconvenient to use. In many cases, I always want to design it, to simplify my operations. As we know, Objective C in IOS also provides attributes that are available in many advanced languages, such as Object Inheritance, interfaces, and polymorphism. I wrote a short article titled "design of the data access layer of Winform Development Framework", which introduced me in. the design of the database access layer in the NET field can greatly reduce the amount of code and improve the development efficiency. Therefore, I also want to create such a data access design in IOS. Although IOS may mainly use single-host databases, such as SQLite databases, I want to simplify it. NET. In. NET, my framework layering is mainly as follows. The design mode of this framework has been well applied to my Winform development framework, WCF, hybrid development framework, and Web framework. Their common layering modes can be divided into the UI Layer, BLL layer, DAL layer, IDAL layer, Entity layer, and public class library layer. The DAL layer is designed as follows, the DAL layer is mainly inherited from the BaseDAL base class (such as BaseDALSQL), and BaseDALSQL is abstracted at a higher level to achieve better application purposes. In IOS, we can mainly consider the SQLite database. Therefore, I simplified the design and constructed the following design model in the project, the data access layer file is as follows (to facilitate the demonstration test, use the User table for operations ). To carry data, we need to convert table data into Entity classes for display and operation. Therefore, the design of entity classes also needs to be considered. Because the base class of the data access layer encapsulates most data operations, including returned data objects and sets, the base class of the data access layer also involves the problem of data type return. There is no such thing as generics in Objective C in IOS, so there are two ways to implement the processing of base class entity classes: one is to use the dynamic type id type as the object class type, the other is to use a semi-typed type (the base class of the object class) as the object, I prefer to use the latter, because it is close to the actual type. Copy the code /// BaseEntity. h // MyDatabaseProject // Created by Wu huacong on 14-4-2. // Copyright (c) 2014 Wu huacong. all rights reserved. // # import <Foundation/Foundation. h> @ interface BaseEntity: NSObject @ end copy the code and copy the code. // UserInfo. h // MyDatabaseProject // Created by Wu huacong on 14-3-30. // Copyright (c) 2014 Wu huacong. all rights reserved. // # import <Foundation/Foundation. h> # import "BaseEntity. h "@ interface UserInfo: Base Entity @ property (nonatomic, copy) NSString * ID; @ property (nonatomic, copy) NSString * name; @ property (nonatomic, copy) NSString * fullName; @ end: copy the code in this inheritance mode and.. NET. The data access layer design code defines the data access base class as follows. The base class uses the data access class of FMDB for operations, many of the operations in the interface is to imitate me in. the design of the data access layer in the NET field. Copy the code /// BaseDAL. h // MyDatabaseProject // Created by Wu huacong on 14-3-30. // Copyright (c) 2014 Wu huacong. all rights reserved. // # import <Foundation/Foundation. h> # import "FMDatabase. h "# import" BaseEntity. h "@ interface BaseDAL: NSObject {NSString * pathToDatabase;} # pragma database attributes @ property (nonatomic, strong) NSString * pathToDatabase; @ property (nonatomic, readonly) FMDatabase * database; // database operation object @ property (Nonatomic, strong) NSString * tableName; // table name @ property (nonatomic, strong) NSString * primaryKey; // primary key @ property (nonatomic, strong) NSString * sortField; // sort. The default value is the primary key @ property (nonatomic, assign, getter = isDescending) BOOL descending; // whether to query in descending order // convert the attribute value of DataReader to the attribute value of the object class, returns the object class (subclass must be overwritten)-(id) rsToEntity :( FMResultSet *) rs; // converts the attribute value of the object to the key value corresponding to the dictionary list (subclass must be overwritten) -(NSDictionary *) dictByEntity :( BaseEntity *) info; # pr Agma basic operation interface // Delete the specified object-(BOOL) deleteById (ID) key from the database based on the id of the specified object; // according to the specified condition, delete the specified object-(BOOL) deleteByCondition :( NSString *) condition from the database; // update the object attribute to the database-(BOOL) update :( BaseEntity *) info byKey :( id) key; // insert the specified object to the database-(BOOL) insert :( BaseEntity *) info; // insert the specified object to the database and return the last inserted ID-(NSInteger) insert2 :( BaseEntity *) info; // query the database and check whether there is an object with the specified ID-(BaseEntity *) findById :( id) key; // query the database based on conditions, if yes, return the first object-(Bas EEntity *) findSingle :( NSString *) condition; // query the database based on conditions. If the first object-(BaseEntity *) findSingle :( NSString *) condition orderBy :( NSString *) is returned *) orderBy; // query the database based on conditions and return the object set-(NSArray *) find :( NSString *) condition; // query the database based on conditions, return the object set-(NSArray *) find :( NSString *) condition orderBy :( NSString *) orderBy; // obtain all the table data-(NSArray *) getAll; // obtain all the table data-(NSArray *) getAll :( NSString *) orderBy; // obtain the data dictionary list of a field -(NSArray *) getFieldList :( NSString *) fieldName; // obtain the number of all records in the table-(int) getRecordCount; // according to the condition, obtain the number of records queried in the table-(int) getRecordCount :( NSString *) condition; // determine whether a record exists based on the condition-(BOOL) isExistRecord :( NSString *) condition; // query the database and check whether there is an object with the specified key value-(BOOL) isExist :( NSString *) fieldName value :( id) value; // based on the primary key and field name, get the content of the corresponding field-(NSString *) getFieldValue :( NSString *) key fieldName :( NSString *) fieldName; // execute the SQL query statement and return the query result The first field of all records, separated by commas. -(NSString *) sqlValueList :( NSString *) query; # initialize the pragma database function and close the operation // initialize the database based on the SQLite database address-(id) initWithPath :( NSString *) filePath; // initialize the database-(id) initWithFileName :( NSString *) fileName according to the SQLite database name; // close the connection-(void) close; @ end copy the code and my. the same as the data access layer design in the. NET Framework, the Data Access Base class has encapsulated most data access operations, so the data access objects of each table can be very concise in its code. From the above base class interface design, we can see that some entity class return functions or list return functions use BaseEntity as the object. When we use the subclass, convert the returned object again. For database access base classes, we use an interface that returns a set for implementation. Copy the code-(NSArray *) find :( NSString *) condition orderBy :( NSString *) orderBy {NSString * query = [NSString stringWithFormat: @ "SELECT * FROM % @", self. tableName]; if (condition! = Nil) {query = [query stringByAppendingFormat: @ "where % @", condition];} if (orderBy! = Nil) {query = [query stringByAppendingString: orderBy];} else {query = [query stringByAppendingFormat: @ "order by % @", self. sortField, self. isDescending? @ "DESC": @ "ASC"];} FMResultSet * rs = [self. database executeQuery: query]; NSMutableArray * array = [NSMutableArray arrayWithCapacity: [rs columnCount]; BaseEntity * info = nil; // default Initialization is empty while ([rs next]) {info = [self rsToEntity: rs]; [array addObject: info];} [rs close]; return array;} copy the code above and use a parameterized SQL statement for query, in addition, the ResultSet Of The Returned Database is converted to an entity class. Info = [self rsToEntity: rs]; since the base class encapsulates most database operation functions, the implementation classes of specific tables in the data access layer can be very concise, however, common CRUD operations and complex data operations such as paging query are available. Copy the code /// UserDAL. h // MyDatabaseProject // Created by Wu huacong on 14-3-30. // Copyright (c) 2014 Wu huacong. all rights reserved. // # import <Foundation/Foundation. h> # import "FMDatabase. h "# import" BaseDAL. h "# import" UserInfo. h "@ interface UserDAL: BaseDAL {} // Singleton mode + (UserDAL *) defaultDAL; @ end