存储在SQLite数据库中的数据具有以下几种存储类型:
存储类型 | 描述 |
NULL | 一个NULL值 |
INTEGER | 一个带符号的整数,根据值得大小存储在1、2、3、4、6或8字节中 |
REAL | 一个浮点值,存储为8字节的IEEE浮点数字 |
TEXT | 一个文本字符串,使用数据库编码()存储 |
BLOB | 一个blob数据,完全根据它的输入存储 |
创建一个SQLite数据库。
在控制器类中添加一个sqlite3类型的属性。
#import <sqlite3.h>
@property(nonatomic, assign) sqlite3 *db;
@end
// 创建一个SQLite数据库。
-(BOOL)openDatabaseWithName:(NSString *)databaseName {
NSString *docsDir = nil;
NSArray *dirpath = nil;
// 获取文档目录路径
dirpPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPath objectAtInedx];
// 拼接数据库路径
NSString *databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: databaseName]];
NSFileManager *fileManager = [NSFileManager defaultManager];
const char *dbPath = [databasePath UTF8String];
if (![fileManager fileExistsAtPath: databasePath]) {
if (sqlite3_open(daPath, &_db) == SQLITE_OK) {
NSLog(@"create success");
return YES;
}
} else {
if (sqlite3_open(daPath, &_db) == SQLITE_OK) {
NSLog(@"open success");
return YES;
}
}
}
// 创建表CONTACTS
-(BOOL)createTable {
if (NULL == _db) {
return NO;
}
char *errMsg = NULL;
const char *sql = "CREATE TABLE IFNOT EXISTS CONTACTS(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, address TEXT phone TEXT)";
if (sqlite3_exec(_db, sql, NULL, NULL, &errMag) != SQLITE_OK) {
return NO;
}
return YES;
}
// 删除表
-(BOOL)dropTable {
if (NULL == _db) {
return NO;
}
char *errMsg = NULL;
const char *sql = "DROP TABLE CONTACTS";
if (sqlite3_exec(_db, sql, NULL, NULL, &errMag) != SQLITE_OK) {
return NO;
}
return YES;
}
// 添加数据
-(BOOL)insertContact:(NSString *)name address:(NSString *)address phone:(NSString *)phone {
// ...
// 拼接SQL语句
NSString *sqlString = [NSString stringWithFromat:@"INSERT INTO CONTACTS (name, address, phone) VALUES('%@','%@','%@');" name, address, phone];
const char *sql = [sqlString UTF8String];
// ...
}
// 查询表
-(void)printContacts {
// ...
const char *sql = "SELECT * FROM CONTACTS";
sqlite3_stmt *stmt = NULL;
if (sqlite_prepare_v2(_db, sql, -1, &stmt, nil) == SQLITE_OK) {
while (sqlite3_step(stmt) = SQLITE_ROW) {
char *name = (char *)sqlite3_column_text(stmt, 1);
NSString *nameString = [[NSString alloc] initWithUTF8String:name];
// ...
}
// sqlite3_ finalize语句需要和sqlite3_prepare_v2成对出现,否则会造成内存泄漏。
sqlite3_finalize(stmt);
}
}
// 删除数据
-(BOOL)deleteAllContactWithName:(NSString *)name {
// ...
NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM CONTACTS WHERE name='%@'", name];
const char *sql = [sqlString UTF8String];
// ...
}
// 修改数据
-(BOOL)uodatePhone:(NSString *)phone forContactWithName:(NSString *)name {
// ...
NSString *sqlString = [NSString stringWithFormat:@"UPDATE CONTACTS SET phone='%@' WHERE name='%@'", phone, name];
const char *sql = [sqlString UTF8String];
// ...
}