基本
#import "UserDB.h"
#import <sqlite3.h>
@implementation UserDB
- (void)queryTable
{
sqlite3 *sqlite = nil;
NSString *filePath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/data.sqlite"];
int result = sqlite3_open([filePath UTF8String], &sqlite);
if (result != SQLITE_OK){
NSLog(@"open failed");
return;
}
NSString *sql = @"select * from user";
sqlite3_stmt *stmt = nil;
//编译
sqlite3_prepare(sqlite, [sql UTF8String], -1, &stmt, NULL);
//查询
while (sqlite3_step(stmt) == SQLITE_ROW){
char *username = (char *)sqlite3_column_text(stmt, 0);
char *password = (char *)sqlite3_column_text(stmt, 1);
char *email = (char *)sqlite3_column_text(stmt, 2);
NSLog(@"%@, %@, %@", [NSString stringWithCString:username encoding:NSUTF8StringEncoding],
[NSString stringWithCString:password encoding:NSUTF8StringEncoding],
[NSString stringWithCString:email encoding:NSUTF8StringEncoding]);
}
sqlite3_finalize(stmt);
sqlite3_close(sqlite);
}
- (void)createTable
{
//
sqlite3 *sqlite = nil;
NSString *filePath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/data.sqlite"];
NSLog(@"%@", filePath);
// open database
int result = sqlite3_open([filePath UTF8String], &sqlite);
if (result != SQLITE_OK){
NSLog(@"open failed");
return;
}
NSString *sql = @"create table if not exists user (username text primary key, password text, email text)";
char *error;
result = sqlite3_exec(sqlite, [sql UTF8String], NULL, NULL, &error);
if (result != SQLITE_OK){
NSLog(@"create table error %s", error);
return;
}
sqlite3_close(sqlite);
NSLog(@"create successful");
}
- (void)insertTable
{
sqlite3 *sqlite = nil;
NSString *filePath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/data.sqlite"];
int result = sqlite3_open([filePath UTF8String], &sqlite);
if (result != SQLITE_OK){
NSLog(@"open failed");
return;
}
NSString *sql = @"insert into user(username, password, email) values(?,?,?)";
sqlite3_stmt *stmt = nil;
sqlite3_prepare(sqlite, [sql UTF8String], -1, &stmt, NULL);
NSString *username = @"Lance lan";
NSString *password = @"lance";
NSString *email = @"lance@qq.com";
sqlite3_bind_text(stmt, 1, [username UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [password UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 3, [email UTF8String], -1, NULL);
//执行插入数据
result = sqlite3_step(stmt);
if (result == SQLITE_ERROR || result == SQLITE_MISUSE){
NSLog(@"falied");
return;
}
//关闭句柄
sqlite3_finalize(stmt);
sqlite3_close(sqlite);
}
@end
封装
#import "BaseDB.h"
@implementation BaseDB
//数据库名字
- (void) setDatabaseName:(NSString *)name
{
if (DBname){
[DBname release];
}
DBname = [name retain];
}
//数据库完整路径
- (NSString *)filePath
{
return [NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",DBname];
}
//创建数据库表
- (void)createTable:(NSString *)sql
{
sqlite3 *sqlite3 = nil;
if (sqlite3_open([[self filePath] UTF8String], &sqlite3) != SQLITE_OK){
NSLog(@"打开数据库失败");
//sqlite3_close(sqlite3);
return;
}
//创建表
char *error;
int result = sqlite3_exec(sqlite3, [sql UTF8String], NULL, NULL, &error);
if (result != SQLITE_OK){
NSLog(@"创建表失败 %s", error);
sqlite3_close(sqlite3);
return;
}
sqlite3_close(sqlite3);
}
//数据库的操作,增删查改
- (BOOL)dealData:(NSString *)sql params:(NSArray *)params
{
sqlite3 *sqlite3 = nil;
if (sqlite3_open([[self filePath] UTF8String], &sqlite3) != SQLITE_OK){
NSLog(@"打开数据库失败");
//sqlite3_close(sqlite3);
return NO;
}
//数据操作句柄
sqlite3_stmt *stmt = nil;
if (sqlite3_prepare_v2(sqlite3, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK){
NSLog(@"sql语句编译失败");
sqlite3_close(sqlite3);
return NO;
}
//绑定数据
if (params != nil){
for (int i = 0; i < [params count]; i ++){
sqlite3_bind_text(stmt, i + 1, [[params objectAtIndex:i] UTF8String], -1, NULL);
}
}
//执行查询
if (sqlite3_step(stmt) == SQLITE_ERROR){
NSLog(@"sql语句执行失败");
sqlite3_close(sqlite3);
return NO;
}
sqlite3_finalize(stmt);
sqlite3_close(sqlite3);
return YES;
}
- (NSMutableArray *)queryData:(NSString *)sql params:(NSArray *)params
{
sqlite3 *sqlite3 = nil;
if (sqlite3_open([[self filePath] UTF8String], &sqlite3) != SQLITE_OK){
NSLog(@"打开数据库失败");
return nil;
}
//数据库句柄
sqlite3_stmt *stmt = nil;
if (sqlite3_prepare_v2(sqlite3, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK){
NSLog(@"sql语句编译失败");
sqlite3_close(sqlite3);
return nil;
}
//绑定参数
if (params != nil){
for (int i = 0; i < [params count]; i ++){
sqlite3_bind_text(stmt, i + 1, [[params objectAtIndex:i] UTF8String], -1, NULL);
}
}
//执行查询
int result = sqlite3_step(stmt);
int columns = sqlite3_column_count(stmt);
NSMutableArray *list = [[NSMutableArray alloc]init];
while (result == SQLITE_ROW){
NSMutableArray *row = [[NSMutableArray alloc]init];
for (int i = 0; i < columns; i ++){
[row addObject:[NSString stringWithCString:(char *)sqlite3_column_text(stmt, i) encoding:NSUTF8StringEncoding]];
}
[list addObject:row];
result = sqlite3_step(stmt);
}
//关闭资源
sqlite3_finalize(stmt);
sqlite3_close(sqlite3);
return list;
}
@end