//
// SqlManager.h
// 数据库SQLite
//
// Created by jerehedu on 15/4/30.
// Copyright (c) 2015年 jerehedu. All rights reserved.
//
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface SqlManager : NSObject
//获取链接
+ (sqlite3 *)getConn;
//执行语句(创建、插入、删除、修改)
+ (BOOL)exec:(NSString *)sql andTips:(NSString *)tips;
//查询书籍
+ (NSArray *)getAllBooksWithSql:(NSString *)sql;
//判断是否存在
+ (BOOL) isExistWithSql:(NSString *)sql;
//获取当前账号
+ (NSString *) getAccount;
//关闭链接
+ (void)closeConn;
@end
//
// SqlManager.m
// 数据库SQLite
//
// Created by jerehedu on 15/4/30.
// Copyright (c) 2015年 jerehedu. All rights reserved.
//
#import "SqlManager.h"
#import "Book.h"
@implementation SqlManager
/**
* 获取链接
* @return 链接对象
*/
+ (sqlite3 *)getConn{
//0 定义链接
static sqlite3 *sqlite;
if (sqlite==nil)
{
NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
path = [path stringByAppendingPathComponent:@"book.sqlite"];
if (SQLITE_OK==sqlite3_open(path.UTF8String, &sqlite))
{
NSLog(@"创建成功");
}
else
{
NSLog(@"创建失败");
}
}
return sqlite;
}
/**
* 执行语句
*
* @param sql 执行的sql
* @param tips 提示
*
* @return 是否成功
*/
+(BOOL)exec:(NSString *)sql andTips:(NSString *)tips{
sqlite3 *sqlite = [SqlManager getConn];
char *errmsg;
if (SQLITE_OK==sqlite3_exec(sqlite, sql.UTF8String, nil, nil, &errmsg)) {
NSLog(@"%@成功",tips);
return YES;
}
else
{
NSLog(@"%@失败,原因%s",tips,errmsg);
return NO;
}
}
/**
* 查询出人员信息
*
* @param sql <#sql description#>
*
* @return <#return value description#>
*/
+(NSArray *)getAllBooksWithSql:(NSString *)sql{
//1.获取链接
sqlite3 *sqlite = [SqlManager getConn];
NSMutableArray *array = [NSMutableArray array];
sqlite3_stmt *stmt;
if (SQLITE_OK==sqlite3_prepare_v2(sqlite, sql.UTF8String, -1, &stmt, nil))
{
while (SQLITE_ROW==sqlite3_step(stmt))
{
int bookId = sqlite3_column_int(stmt, 0);
const unsigned char *bookName = sqlite3_column_text(stmt, 1);
//获取书的详情
const unsigned char *summary = sqlite3_column_text(stmt, 2);
//将结果集数据放入数组
Book *book=[[Book alloc] init];
book.book_id=bookId;
book.book_name=[NSString stringWithUTF8String:(const char *) bookName];
book.summary=[NSString stringWithUTF8String:(const char *) summary];
[array addObject:book];
}
}
else
{
NSLog(@"查询失败");
}
sqlite3_finalize(stmt);
return array;
}
/**
* 判断是不是存在
*
* @param sql <#sql description#>
*
* @return 是否存在
*/
+(BOOL)isExistWithSql:(NSString *)sql{
//1 获取链接
sqlite3 * sqlite=[SqlManager getConn];
//2 查询数据
sqlite3_stmt *stmt;
if(SQLITE_OK== sqlite3_prepare_v2(sqlite, sql.UTF8String, -1, &stmt, nil)){
//遍历当前结果集,并且取出数据
while (sqlite3_step(stmt)==SQLITE_ROW) {
//取出结果集数据
int count= sqlite3_column_int(stmt, 0);
return count>0;
}
}else{
NSLog(@"查询失败");
}
//关闭链接
sqlite3_close(sqlite);
return NO;
}
/**
* 获取账号信息
*
* @return <#return value description#>
*/
+ (NSString *) getAccount{
NSString *path1 = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
//将登录的人员信息写入沙盒
NSString *path=[path1 stringByAppendingPathComponent:@"user.tt"];
NSFileManager * manager =[NSFileManager defaultManager];
NSData * data=[manager contentsAtPath:path];
NSString * account=[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];
return account;
}
+ (void)closeConn
{
sqlite3 * sqlite=[SqlManager getConn];
sqlite3_close(sqlite);
sqlite = nil;
}
@end