FMDatabase的使用方法

1、FMDatabase 的使用方法FMDatabase 的使用方法- (NSString*) getPath NSArray* paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirector y,NSUserDomainMask, YES) ; return pathsobjectAtIndex:0stringByAppendingPathComponent:MyTable ; 1 .創(chuàng)建數(shù)據(jù)庫-(void)CreateTable;dataBase =FMDatabasedatabaseWithPath:selfgetPath; if(

2、!dataBase open)NSLog(OPEN FAIL);dataBaseexecuteUpdate:CREATE TABLE IF NOT EXISTSMyTable(aa float,bb text,cc integer,dd integer,ee text);dataBaseclose; 2 .查詢數(shù)據(jù)-(void)QueryData/獲取數(shù)據(jù)recordArray = NSMutableArrayallocinit;db =FMDatabaseallocinitWithPath:selfgetPath;if (dbopen) FMResultSet *rs =dbexecuteQ

3、uery:SELECT * FROM MyTable;while (rs next)OneRecord = OneRecordallocinit;OneRecord.aa =NSNumbernumberWithFloat:rs doubleForColumn:aa;OneRecord.bb = rs stringForColumn:bb;OneRecord.cc = NSNumbernumberWithInt:rsintForColumn:cc;OneRecord.dd =NSNumbernumberWithInt:rs intForColumn:dd;OneRecord.ee = rs st

4、ringForColumn:ee;recordArrayaddObject: OneRecord;OneRecordrelease;rs close;dbclose;3 .更新數(shù)據(jù)-(void)UpdateDataif (dbopen)dbbeginTransaction;dbexecuteUpdate:UPDATE MyTable SET aa = ? WHEREdate = ?,aa1,aa2;dbexecuteUpdate:UPDATEMyTable SET bb = ? WHERE date = ?,bb1,bb2;dbexecuteUpdate:UPDATE MyTable SET

5、cc = ? WHEREdate = ?,cc1,cc2;dbexecuteUpdate:UPDATEMyTable SET dd = ? WHERE date = ?,dd1,dd2;dbcommit;db close; 4。插入數(shù)據(jù)-(void)insertData/插入數(shù)據(jù)庫 dbbeginTransaction; dbexecuteUpdate:INSERT INTOMyTable (aa,bb,cc,dd,ee) VALUES (?,?,?,?,?), NSNumber numberWithFloat:aa,bb,cc,dd,ee; db commit; db close;5。官方的

6、使用方法為:UsageThere are three main classes in FMDB:FMDatabase -Represents a single SQLite database. Used for executing SQL statements.FMResultSet - Represents the results of executing a query on anFMDatabase.FMDatabaseQueue - If youre wanting to perform queries and updates on multiple threads, youll wa

7、nt to use this class. Its described in the Thread Safety section below.Database CreationAn FMDatabase is created with a path to a SQLite database file. This path can be one of these three:A file system path. The file does not have to exist on disk. If it does not exist, it is created for you.An empt

8、y string (). An empty database is created at a temporary location. This database is deleted with theFMDatabase connection is closed.NULL. An in-memory database is created. This database will be destroyed with theFMDatabase connection is closed.(For more information on temporary and in-memory databas

9、es, read the sqlite documentation on the subject:/inmemorydb.html)FMDatabase *db = FMDatabase databaseWithPath:/tmp/tmp.db;OpeningBefore you can interact with the database, it must be opened. Opening fails if there are insufficient resources or permissions to open and/or create t

10、he database.if (!db open) db release;return;Executing UpdatesAny sort of SQL statement which is not a SELECT statement qualifies as an update. This includesCREATE, PRAGMA,UPDATE, INSERT, ALTER,COMMIT,BEGIN, DETACH,DELETE, DROP, END,EXPLAIN,VACUUM, and REPLACE statements (plus many more).Basically, i

11、f your SQL statement does not begin withSELECT, it is an update statement.Executing updates returns a single value, a BOOL. A return value of YES means the update was successfully executed, and a return value of NO means that some error was encountered. If you use the -FMDatabase executeUpdate:error

12、:withArgumentsInArray:orV AList: method to execute an update, you may supply anNSError * that will be filled in if execution fails. Otherwise you may invoke the-lastErrorMessage and-lastErrorCode methods to retrieve more information.Executing QueriesA SELECT statement is a query and is executed via

13、one of the-executeQuery.methods.Executing queries returns an FMResultSet object if successful, and nil upon failure. Like executing updates, there is a variant that accepts anNSError * parameter. Otherwise you should use the-lastErrorMessage and-lastErrorCode methods to determine why a query failed.

14、In order to iterate through theresults of your query, you use a while() loop. You also need to step from one record to the other. With FMDB, the easiest way to do that is like this:FMResultSet *s = db executeQuery:SELECT * FROM myTable;while (s next) /retrieve values for each recordYou must always i

15、nvoke -FMResultSet next before attempting to access the values returned in a query, even if youre only expectingint totalCountone:FMResultSet *s = db executeQuery:SELECTCOUNT(*) FROM myTable;if (s next) = s intForColumnIndex:0;FMResultSet has many methods to retrieve data in an appropriate format:in

16、tForColumn:longForColumn:longLongIntForColumn:b oolForColumn:doubleForColumn:stringForColumn:dateForColu mn:dataForColumn:dataNoCopyForColumn:UTF8StringForCol umnIndex:objectForColumn:Each of these methods also has a typeForColumnIndex: variant that is used to retrieve the data based on the position

17、 of the column in the results, as opposed to the columns name.Typically, theres no need to -close anFMResultSet yourself, since that happens when either the result set is deallocated, or the parent database is closed.ClosingWhen you have finished executing queries and updates on the database, you sh

18、ould -close the FMDatabaseconnection so that SQLite will relinquish any resources it has acquired during the course of its operation.db close;TransactionsFMDatabase can begin and commit a transaction by invoking one of the appropriate methods or executing a begin/end transaction statement.DataSaniti

19、zationWhen providing a SQL statement to FMDB, you should not attempt to sanitize any values before insertion.Instead, you should use the standard SQLite binding syntax:INSERT INTO myTable VALUES (?, ?, ?)The ?character is recognized by SQLite as a placeholder for a value to be inserted. The executio

20、n methods all accept a variable number of arguments (or a representation of those arguments, such as anNSArray,NSDictionary, or ava_list), which are properly escaped for you.Alternatively, you may use named parameters syntax:INSERT INTO myTable VALUES (:id, :name, :value)The parameters must start wi

21、th a colon.SQLite itself supports other characters, but internally theDictionary keys are prefixed with a colon, donot include the colon in your dictionary keys.NSDictionary *argsDict = NSDictionary dictionaryWithObjectsAndKeys:My Name, name, nil;db executeUpdate:INSERT INTO myTable (name) VALUES (:

22、name) withParameterDictionary:argsDict;Thus, you SHOULD NOT do this (or anything like this):db executeUpdate:NSString stringWithFormat:INSERT INTO myTable V ALUES (%), this has lots of bizarre quotes ;Instead, youSHOULD do:db executeUpdate:INSERT INTO myTableVALUES (?), this has lots of bizarre quot

23、es ;All arguments provided to the -executeUpdate: method (or any of the variants that accept a va_list as a parameter) must be objects.The following will not work (and will result in a crash):db executeUpdate:INSERT INTO myTable V ALUES (?), 42;The proper way to insert a number is to box it in anNSN

24、umber object:db executeUpdate:INSERT INTO myTable VALUES (?), NSNumber numberWithInt:42;Alternatively, you can use the -execute*WithFormat: variant to use NSString-style substitution:db executeUpdateWithFormat:INSERT INTO myTable VALUES (%d), 42;Internally, the -execute*WithFormat: methods are prope

25、rly boxing things for you. The following percent modifiers are recognized:%, %c, %s,%d, %D, %i,%u, %U, %hi,%hu, %qi, %qu,%f, %g, %ld,%lu, %lld, and %llu. Using a modifier other than those will have unpredictable results. If, for some reason, you need the% character to appear in your SQL statement, y

26、oushould use%.Using FMDatabaseQueue and ThreadSafety.Using a single instance of FMDatabase from multiplethreads at once is a bad idea. It has always been OK to make aFMDatabase objectper thread. Just dont share a single instanceacross threads, and definitely not across multiple threads at thesame ti

27、me. Bad things will eventually happen and youlleventually get something to crash, or maybe get an exception, ormaybe meteorites will fall out of the sky and hit your Mac Pro.This would suck.So dont instantiate a single FMDatabase objectand use it across multiple threads.Instead, useFMDatabaseQueue.

28、Its your friend and its here to help. Hereshow to use it:First, make your queue.FMDatabaseQueue *queue= FMDatabaseQueue databaseQueueWithPath:aPath;Then useit like so:queue inDatabase：A(FMDatabase *db) dbexecuteUpdate:INSERT INTO myTable V ALUES (?),NSNumber numberWithInt:1;dbexecuteUpdate:INSERT INTO myTable V ALUES (?),NSNumber numberWithInt:2;dbexecuteUpdate:INSERT INTO