本文是主要实现了三个函数：
- testSQLite3 是测试系统自带的sqlite3的demo
- testFMDB是测试FMDB存取简单的数据类型的 的demo
- testFMDB2是将任意对象作为一个整体存入到FMDB的Demo
首先先定义了一个Person类，实现了<NSCoding>协议，对Person对象进行字段存取和整体存取
//Person.h
#import <Foundation/Foundation.h> @interface Person : NSObject<NSCoding>
@property (nonatomic ,assign)int id;
@property (nonatomic ,copy) NSString *name;
@property (nonatomic ,assign)int age;
@end //Person.m
#import "Person.h" @implementation Person
-(NSString *)description{
return [NSString stringWithFormat:@"id:%d--name:%@--age:%d",self.id,self.name,self.age];
}
-(instancetype)initWithCoder:(NSCoder *)decoder{
if (self = [super init]) {
self.id = [decoder decodeIntForKey:@"id"];
self.name = [decoder decodeObjectForKey:@"name"];
self.age = [decoder decodeIntForKey:@"age"];
}
return self;
}
-(void)encodeWithCoder:(NSCoder *)encoder{
[encoder encodeInt:self.id
forKey:@"id"];
[encoder encodeObject:self.name forKey:@"name"];
[encoder encodeInt:self.age forKey:@"age"];
}
@end
Person
-(void)testSQLite3{
//1. 打开(建立)数据库
NSString *cacheDir = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
NSString *filename = [cacheDir stringByAppendingPathComponent:@"test.sqlite"];
sqlite3 * db = nil;
//0表示成功，1表示失败
sqlite3_open(filename.UTF8String, &db);
if (!db) {
NSLog(@"数据库打开失败，建立失败");
return;
}
char *errmsg = nil;
//2.一般用来执行无返回结果的sql 命令,（可以执行任何SQL语句）
//创建数据库
char * createTableSql = "create table t_person(id integer primary key,name text not null unique,age integer);";
int rs = sqlite3_exec(db, createTableSql, NULL, NULL, &errmsg);
if (errmsg) {
NSLog(@"%s--errorCode:%d",errmsg,rs);
}
//3.插入数据
for(NSUInteger i = ;i < ;i ++){
NSString *insertSql = [NSString stringWithFormat:@"insert into t_person(name,age) values('小hong%lu',%d);",(unsigned long)i,arc4random()%+];
char *errmsg = nil;
sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, &errmsg);
if (errmsg) {
NSLog(@"insert error:%s",errmsg);
}
}
//4.查询数据 尽量使用const char * 不要用NSString ,容易出错
const char* selectSql = "select * from t_person where name like '小明%' and age < 20 order by age desc limit 1,10";
sqlite3_stmt * stmt = nil;
sqlite3_prepare(db, selectSql, (int)strlen(selectSql), &stmt, NULL);
if (stmt) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
Person *person = [[Person alloc]init];
person.id = sqlite3_column_int(stmt, );
person.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, )];
person.age = sqlite3_column_int(stmt, );
NSLog(@"%@",person);
}
}
testSQLite3
-(void)testFMDB{
NSString *cacheDir = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
NSString *filename = [cacheDir stringByAppendingPathComponent:@"new.sqlite"];
//1.建立数据库对象，一个数据库对应一个数据库对象
FMDatabase * db = [FMDatabase databaseWithPath:filename];
//2.打开数据库
if (![db open]) {
NSLog(@"数据库连接或者创建失败");
return;
}
//3. 建表
NSString *createSql = @"create table if not exists t_person(id integer primary key,name text not null unique,age integer);";
if (![db executeUpdate:createSql]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
//4.插入数据
for (int i = ; i< ; i++) {
//fmdb 最好自己把你的sql字符串用NSString写出来
NSString * insertSql = [NSString stringWithFormat:@"insert into t_person(name,age) values('xiaoming%d',%d)",i,arc4random()%];
if (![db executeUpdate:insertSql]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
}
//5.修改数据
NSString *updateStr = [NSString stringWithFormat:@"update t_person set age = 1000"];
if (![db executeUpdate:updateStr]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
//6. 删除数据
NSString *deleteStr= [NSString stringWithFormat:@"delete from t_person where id =1;"];
if (![db executeUpdate:deleteStr]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
//7.查询数据
NSString *querySql = [NSString stringWithFormat:@"select * from t_person"];
FMResultSet * set = [db executeQuery:querySql];
while (set.next) {
Person *person = [[Person alloc]init];
person.id = [set intForColumn:@"id"];
person.name = [set stringForColumn:@"name"];
person.age = [set intForColumn:@"age"];
NSLog(@"%@",person);
}
}
testFMDB
//存取任意对象
-(void)testFMDB2{
NSString *cacheDir = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
NSString *filename = [cacheDir stringByAppendingPathComponent:@"Person.sqlite"];
//1.建立数据库对象，一个数据库对应一个数据库对象
FMDatabase * db = [FMDatabase databaseWithPath:filename];
//2.打开数据库
if (![db open]) {
NSLog(@"数据库连接或者创建失败");
return;
}
//3. 建表
NSString *createSql = @"create table if not exists t_persons(id integer primary key,person blob not null unique,age integer);";
if (![db executeUpdate:createSql]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
//4.插入数据
for (int i = ; i< ; i++) {
//将对象作为一个整体存入到数据库中
Person *person = [[Person alloc]init];
person.name = [NSString stringWithFormat:@"xiaoming%d",i];
person.age = arc4random()%;
person.id = arc4random();
NSData *data = [NSKeyedArchiver archivedDataWithRootObject:person];
//插入blob 数据一定要使用excuteUpdateWithFormat:， 直接用executeUpdate:会报错的，因为你的数据使用NSLog打印出来会带有<>符号
if (![db executeUpdateWithFormat:@"insert into t_persons(person,age) values(%@,%d)",data,person.age]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
}
//5.修改数据
NSString *updateStr = [NSString stringWithFormat:@"update t_persons set age = 1000"];
if (![db executeUpdate:updateStr]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
//6. 删除数据
NSString *deleteStr= [NSString stringWithFormat:@"delete from t_persons where id =1;"];
if (![db executeUpdate:deleteStr]) {
NSLog(@"error:%@",[db lastErrorMessage]);
}
//7.查询数据
NSString *querySql = [NSString stringWithFormat:@"select * from t_persons "];
FMResultSet * set = [db executeQuery:querySql];
while (set.next) {
NSData *data = [set dataForColumn:@"person"];
// NSLog(@"%@",data);
Person *person = [NSKeyedUnarchiver unarchiveObjectWithData:data];
person.id = [set intForColumn:@"id"];
NSLog(@"%@",person);
} }
testFMDB2