#pragma mark-----iOS SQLite数据库/**
1.添加libsqlite3.dylib库
2.加入头文件#import<sqlite3.h>
3.
static sqlite3* dataBase = nil;
static sqlite3_stmt* statement = nil;
4.创造数据库
5.打开数据库存储数据
6.取出数据
*/
- (void)initSQLite{
UIButton* saveBut = [UIButton buttonWithType:UIButtonTypeCustom];
[self.view addSubview:saveBut];
saveBut.frame = CGRectMake([UIScreen mainScreen].bounds.size.width/2.0-100/2.0, 80, 100, 30);
[saveBut setTitle:@"保存" forState:UIControlStateNormal];
[saveBut addTarget:self action:@selector(didSaveData) forControlEvents:UIControlEventTouchUpInside];
[saveBut setTitleColor:[UIColor blackColor] forState:UIControlStateNormal];
UIButton* findBut = [UIButton buttonWithType:UIButtonTypeCustom];
[self.view addSubview:findBut];
findBut.frame = CGRectMake([UIScreen mainScreen].bounds.size.width/2.0-100/2.0, 120, 100, 30);
[findBut setTitle:@"查找" forState:UIControlStateNormal];
[findBut addTarget:self action:@selector(didFindData) forControlEvents:UIControlEventTouchUpInside];
[findBut setTitleColor:[UIColor blackColor] forState:UIControlStateNormal];
}
#pragma mark---保存数据
- (void)didSaveData{
if ([self createDB]) {
[self saveData:@"123" name:@"zhaobin" department:@"ios" year:@"2017"];
}
}
- (void)didFindData{
NSArray* data = [self findByRegisterAccount:@"1234"];
if (data == nil) {
UIAlertController* alertController = [UIAlertController alertControllerWithTitle:@"查无数据" message:nil preferredStyle:UIAlertControllerStyleAlert];
UIAlertAction* cancleAction = [UIAlertAction actionWithTitle:@"取消" style:UIAlertActionStyleCancel handler:^(UIAlertAction * _Nonnull action) {
}];
[alertController addAction:cancleAction];
[self presentViewController:alertController animated:YES completion:nil];
}
else{
NSLog(@"查出的数据：－%@",data);
}
}
#pragma mark---创造数据库
- (BOOL)createDB
{
NSString* docsDirStr;
NSArray* dirPathsArr;
dirPathsArr =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDirStr = dirPathsArr[0];
//生成数据库路径
dataBasePath = [[NSString alloc]initWithString:[docsDirStr stringByAppendingPathComponent:@"db.db"]];
BOOL orSuccess = YES;
NSFileManager* fileManager = [NSFileManager defaultManager];
if ([fileManager fileExistsAtPath:dataBasePath] == NO) {
const char* dbPath = [dataBasePath UTF8String];
if (sqlite3_open(dbPath, &dataBase) == SQLITE_OK) {
char* errMsg;
const char* sql_stmt = "create table if not exists studentsDetail (regno integer primary key, name text, department text, year text)";
if (sqlite3_exec(dataBase, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK) {
orSuccess = NO;
NSLog(@"创造表格失败");
}
sqlite3_close(dataBase);
return orSuccess;
}
else{
orSuccess = NO;
NSLog(@"打开数据库失败");
}
}
return orSuccess;
}
#pragma mark---存数据
- (BOOL)saveData:(NSString *)registerAccount name:(NSString *)name department:(NSString *)department year:(NSString *)year
{
const char* dbPath = [dataBasePath UTF8String];
if (sqlite3_open(dbPath, &dataBase) == SQLITE_OK) {
NSString* insertSQL = [NSString stringWithFormat:@"insert into studentsDetail (regno,name, department, year) values (\"%ld\",\"%@\", \"%@\", \"%@\")",(long)[registerAccount integerValue],name, department, year];
const char* insert_stmt = [insertSQL UTF8String];
sqlite3_prepare_v2(dataBase, insert_stmt, -1, &statement, NULL);
if (sqlite3_step(statement) == SQLITE_DONE) {
return YES;
}else{
return NO;
}
sqlite3_reset(statement);
}
return NO;
}
#pragma mark---取数据
- (NSArray *)findByRegisterAccount:(NSString *)registerAccount
{
const char* dbpath = [dataBasePath UTF8String];
if (sqlite3_open(dbpath, &dataBase) == SQLITE_OK) {
NSString *querySQL = [NSString stringWithFormat:@"select name, department, year from studentsDetail where regno=\"%@\"",registerAccount];
const char *query_stmt = [querySQL UTF8String];
NSMutableArray *resultArray = [[NSMutableArray alloc]init];
if (sqlite3_prepare_v2(dataBase,query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
if (sqlite3_step(statement) == SQLITE_ROW)
{
NSString *name = [[NSString alloc] initWithUTF8String:
(const char *) sqlite3_column_text(statement, 0)];
[resultArray addObject:name];
NSString *department = [[NSString alloc] initWithUTF8String:
(const char *) sqlite3_column_text(statement, 1)];
[resultArray addObject:department];
NSString *year = [[NSString alloc]initWithUTF8String:
(const char *) sqlite3_column_text(statement, 2)];
[resultArray addObject:year];
return resultArray;
}
else{
NSLog(@"Not found");
return nil;
}
sqlite3_reset(statement);
}
}
return nil;
}
