1.简介
简单封装sqlite数据库操作类 BaseDB 用于完成对sqlite的增删改查,使用前先导入libsqlite3.0.dylib库
2.BaseDB.h
//
// BaseDB.h
// SqliteDemo
//
// Created by 赵超 on 14-8-26.
// Copyright (c) 2014年 赵超. All rights reserved.
//
#import <Foundation/Foundation.h>
#import "sqlite3.h"
@interface BaseDB : NSObject
/**
* 创建一个表
* sql:执行的SQL语句
* dataName:数据库名称
*/
-(void) createTable:(NSString*)sql dataBaseName:(NSString*) dataName;
/**
* 执行SQL语句,主要完成增加、修改、删除
* sql:执行的SQL语句
* params:SQL语句中的参数
* dataName:数据库名称
*/
-(BOOL) execSql:(NSString*)sql parmas:(NSArray*) params dataBaseName:(NSString*)dataName;
/**
* 选择数据
* sql:查询的SQL语句
* params:查询SQL语句中的参数
* dataName:查询数据库名称
*/
-(NSMutableArray*) selectSql:(NSString*)sql parmas:(NSArray*) params dataBaseName:(NSString*)dataName;
@end
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
创建的数据库文件位于 /Users/zhaochao/Library/Application Support/iPhone Simulator/7.1/Applications/07D17328-B63C-4D87-9B6C-03AA5CD681EA/Documents/zhaochao.sqlite,
就是 NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",name]; 这个目录。文件可以直接用SQLiteManager软件打开,也可以在firefox浏览器中安装sqlitemanager插件打开,如下图
3.BaseDB.m
//
// BaseDB.m
// SqliteDemo
//
// Created by 赵超 on 14-8-26.
// Copyright (c) 2014年 赵超. All rights reserved.
//
#import "BaseDB.h"
@implementation BaseDB
/*
* 获取沙盒目录
* name:追加的目录aa
*
*/
-(NSString*) DataBaseName:(NSString *) name {
NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",name];
return fileName;
}
/**
* 选择数据
* sql:查询的SQL语句
* params:查询SQL语句中的参数
* dataName:查询数据库名称
*/
-(NSMutableArray*) selectSql:(NSString *)sql parmas:(NSArray *)params dataBaseName:(NSString *)dataName{
sqlite3 *sqlite=nil;
sqlite3_stmt *stmt=nil;
//打开数据库
NSString *fileName=[self DataBaseName:dataName];
int result= sqlite3_open([fileName UTF8String], &sqlite);
if (result!=SQLITE_OK) {
NSLog(@"打开失败");
return nil;
}
const char* sqlCh=[sql UTF8String];
//编译SQL语句
sqlite3_prepare_v2(sqlite, sqlCh, -1, &stmt, NULL);
//绑定参数
for (int i=0; i<params.count; i++) {
NSString *param=[params objectAtIndex:i];
sqlite3_bind_text(stmt, i+1, [param UTF8String], -1, NULL);
}
//执行查询语句
result=sqlite3_step(stmt);
NSMutableArray *resultData=[NSMutableArray array];
//遍历结果
while (result==SQLITE_ROW) {
NSMutableDictionary *resultRow=[NSMutableDictionary dictionary];
//获取字段个数
int col_count = sqlite3_column_count(stmt);
for (int i=0; i<col_count; i++) {
//获取字段名称
const char*columName=sqlite3_column_name(stmt,i);
//获取字段值
char* columValue=(char*) sqlite3_column_text(stmt, i);
NSString *columkeyStr=[NSString stringWithCString:columName encoding:NSUTF8StringEncoding];
NSString *columValueStr=[NSString stringWithCString:columValue encoding:NSUTF8StringEncoding];
[resultRow setObject:columValueStr forKey:columkeyStr];
}
[resultData addObject:resultRow];
result=sqlite3_step(stmt);
}
//关闭数据库句柄
sqlite3_finalize(stmt);
//关闭数据库
sqlite3_close(sqlite);
NSLog(@"查询完!");
return resultData;
}
/**
* 执行SQL语句,主要完成增加、修改、删除
* sql:执行的SQL语句
* params:SQL语句中的参数
* dataName:数据库名称
*/
-(BOOL) execSql:(NSString *)sql parmas:(NSArray *)params dataBaseName:(NSString *)dataName{
sqlite3 *sqlite=nil;
sqlite3_stmt *stmt=nil;
//打开数据库
NSString *fileName=[self DataBaseName:dataName];
int result= sqlite3_open([fileName UTF8String], &sqlite);
if (result!=SQLITE_OK) {
NSLog(@"打开失败");
return NO;
}
const char* sqlCh=[sql UTF8String];
//编译SQL语句
sqlite3_prepare_v2(sqlite, sqlCh, -1, &stmt, NULL);
//绑定参数
for (int i=0; i<params.count; i++) {
NSString *parm=[params objectAtIndex:i];
sqlite3_bind_text(stmt, i+1, [parm UTF8String], -1, NULL);
}
//执行SQL
result=sqlite3_step(stmt);
if (result==SQLITE_ERROR || result==SQLITE_MISUSE) {
NSLog(@"执行SQL语句失败");
sqlite3_close(sqlite);
return NO;
}
//关闭数据库句柄
sqlite3_finalize(stmt);
//关闭数据库
sqlite3_close(sqlite);
NSLog(@"执行成功!");
return YES;
}
/**
* 创建一个表
* sql:执行的SQL语句
* dataName:数据库名称
*/
-(void)createTable:(NSString *)sql dataBaseName:(NSString *)dataName{
sqlite3 *sqlite=nil;
NSString *fileName=[self DataBaseName:dataName];
//打开数据库
int result= sqlite3_open([fileName UTF8String], &sqlite);
if (result!=SQLITE_OK) {
NSLog(@"打开失败");
}else{
const char* sqlCh=[sql UTF8String];
char* error;
//执行SQL
int result=sqlite3_exec(sqlite, sqlCh, NULL, NULL, &error);
if (result!=SQLITE_OK) {
NSLog(@"创建失败");
NSLog(@"%s",error);
sqlite3_close(sqlite);
return ;
}
//关闭数据库
sqlite3_close(sqlite);
NSLog(@"创建成功");
}
}
@end
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139.
- 140.
- 141.
- 142.
- 143.
- 144.
- 145.
- 146.
- 147.
- 148.
4.调用格式
BaseDB *db=[[BaseDB alloc] init];
//创建表
NSString *dbCreate=@"create table zhaochao( username text primary key,userPasswd test)";
NSString *dbName=@"zhaochao.sqlite";
// [db createTable:dbCreate dataBaseName:dbName];
//添加数据
NSString *insertTable=@"insert into zhaochao (username,userPasswd) values (?,?)";
NSArray *insertParmas=@[@"acasdfaa",@"bb"];
// [db execSql:insertTable parmas:insertParmas dataBaseName:@"zhaochao.sqlite"];
//修改数据
NSString *updateTable=@"update zhaochao set username=? where username=?";
NSArray *updateParams=@[@"admin",@"zhaochao"];
// [db execSql:updateTable parmas:updateParams dataBaseName:@"zhaochao.sqlite"];
//删除数据
NSString *deleteTable=@"delete from zhaochao where username=?";
NSArray *deleteParams=@[@"aa"];
// [db execSql:deleteTable parmas:deleteParams dataBaseName:@"zhaochao.sqlite"];
//查询数据
NSString *selectTable=@"select username,userPasswd from zhaochao where userPasswd=?";
NSString *selectParam=@[@"bb"];
NSArray *result=[db selectSql:selectTable parmas:selectParam dataBaseName:@"zhaochao.sqlite"];
for (int i=0; i<result.count; i++) {
NSMutableDictionary *arr=[result objectAtIndex:i];
NSLog(@"%@",arr);
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.