iOS中原生的SQLite API在使用上相当不友好,在使用时,非常不便。于是,就出现了一系列将SQLite API进行封装的库,例如FMDB、PlausibleDatabase、sqlitepersistentobjects等,FMDB (https://github.com/ccgus/fmdb) 是一款简洁、易用的封装库,这一篇文章简单介绍下FMDB的使用。
在FMDB下载文件后,工程中必须导入如下文件,并使用 libsqlite3.dylib 依赖包。
FMDB同时兼容ARC和非ARC工程,会自动根据工程配置来调整相关的内存管理代码。
FMDB常用类:
FMDatabase : 一个单一的SQLite数据库,用于执行SQL语句。
FMResultSet :执行查询一个FMDatabase结果集,这个和android的Cursor类似。
FMDatabaseQueue :在多个线程来执行查询和更新时会使用这个类。
下面是一些代码,使用时要先初始化一个FMDB的类。
//数据库路径
NSArray *paths =NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory,NSUserDomainMask, YES);
NSString *doucmentDirectory = [paths objectAtIndex:0];
NSString *dbPath = [doucmentDirectory stringByAppendingString:@"tb.db"];
//初始化
self.database = [FMDatabasedatabaseWithPath:dbPath];
if(![self.databaseopen])
{
NSLog(@"can't open!");
return;
}
//数据库操作
//创建表
[self.databaseexecuteUpdate:@"CREATE TABLE TEST (name test,age int)"];
//插入数据
[self.databaseexecuteUpdate:@"insert into test values('joy','12')"];
[self.databaseexecuteUpdate:@"insert into test values('tici','10')"];
[self.databaseexecuteUpdate:@"insert into test values('moni','23')"];
[self.databaseexecuteUpdate:@"insert into test values('sun','23')"];
[self.databaseexecuteUpdate:@"insert into test values('flow','23')"];
[self.databaseexecuteUpdate:@"insert into test values('nasd',90)"];
//更新数据
[self.databaseexecuteUpdate:@"update test set name = 'nnnn' where name = 'nasd'"];
//删除数据
[self.databaseexecuteUpdate:@"delete from test where name = 'sun'"];
//查询数据,用了executeQuery方法,并涉及到FMResultSet类。
FMResultSet *set = [self.databaseexecuteQuery:@"select *from test where age = '23'"];
while([set next])
{
NSLog(@"%@,%@",[setstringForColumn:@"name"],[setstringForColumn:@"age"]);
}
MDB的FMResultSet提供了多个方法来获取不同类型的数据:
官方介绍:
Usage
There 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 you're wanting to perform queries and updates on multiple threads, you'll want to use this class. It's described in the "Thread Safety" section below.
Database Creation
An
- A file system path. The file does not have to exist on disk. If it does not exist, it is created for you.
- An empty 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 theFMDatabaseconnection is closed.
(For more information on temporary and in-memory databases, read the sqlite documentation on the subject:http://www.sqlite.org/inmemorydb.html)
FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];
Opening
Before you can interact with the database, it must be opened. Opening fails if there are insufficient resources or permissions to open and/or create the database.
if (![db open]) {
}
Executing Updates
Any sort of SQL statement which is not a
Executing updates returns a single value, a
Executing Queries
A
Executing queries returns an
In order to iterate through the results of your query, you use a
FMResultSet *s = [db executeQuery:@"SELECT * FROM myTable"];
while ([s next]) {
}
You must always invoke
FMResultSet *s = [db executeQuery:@"SELECT COUNT(*) FROM myTable"];
if ([s next]) {
}
FMResultSet
- intForColumn:
- longForColumn:
- longLongIntForColumn:
- boolForColumn:
- doubleForColumn:
- stringForColumn:
- dateForColumn:
- dataForColumn:
- dataNoCopyForColumn:
- UTF8StringForColumnIndex
: - objectForColumn:
Each of these methods also has a
Typically, there's no need to
Closing
When you have finished executing queries and updates on the database, you should
[db close];
Transactions
FMDatabase
Data Sanitization
When 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
Alternatively, you may use named parameters syntax:
INSERT INTO myTable VALUES (:id, :name, :value)
The parameters
NSDictionary *argsDict = [NSDictionary dictionaryWithObjectsAnd
[db executeUpdate:@"INSERT INTO myTable (name) VALUES (:name)" withParameterDictionary:argsDict];
Thus, you SHOULD NOT do this (or anything like this):
[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has " lots of ' bizarre " quotes '"]];
Instead, you SHOULD do:
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has " lots of ' bizarre " quotes '"];
All arguments provided to the
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", 42];
The proper way to insert a number is to box it in an
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:42]];
Alternatively, you can use the
[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (%d)", 42];
Internally, the
Using FMDatabaseQueue and Thread Safety.
Using a single instance of FMDatabase from multiple threads at once is a bad idea. It has always been OK to make a FMDatabase objectper thread. Just don't share a single instance across threads, and definitely not across multiple threads at the same time. Bad things will eventually happen and you'll eventually get something to crash, or maybe get an exception, or maybe meteorites will fall out of the sky and hit your Mac Pro.
So don't instantiate a single FMDatabase object and use it across multiple threads.
Instead, use FMDatabaseQueue. It's your friend and it's here to help. Here's how to use it:
First, make your queue.
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];
Then use it like so:
[queue inDatabase:^(FMDatabase *db) {
}];
An easy way to wrap things up in a transaction can be done like this:
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
}];
FMDatabaseQueue will make a serialized GCD queue in the background and execute the blocks you pass to the GCD queue. This means if you call your FMDatabaseQueue's methods from multiple threads at the same time GDC will execute them in the order they are received. This means queries and updates won't step on each other's toes, and every one is happy