#import "DBTool.h"
#import <sqlite3.h>
staticDBTool* dbTool;
@interface DBTool()
{
sqlite3* db;
}
@end
@implementation DBTool
-(id)init
{
self = [superinit];
if (self) {
//打开数据库
NSString* path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES)lastObject];
path = [path stringByAppendingPathComponent:@"GETVIEW.aqlite"];
const char* dbpath = [pathUTF8String];
if (sqlite3_open(dbpath, &db) ==SQLITE_OK) {
char *errMsg;
constchar* sql_stmt ="CREATE TABLE IF NOT EXISTS showimage(imgUrl TEXT, title TEXT,toothID TEXT,id INTEGER PRIMARY KEY AUTOINCREMENT)";
if (sqlite3_exec(db, sql_stmt,NULL, NULL, &errMsg)) {
NSLog(@"创建成功");
}else{
printf("%s", errMsg);
}
}
}
return self;
}
//数据库添加
-(BOOL)insertImage:(NSString*)imgUrl insertName:(NSString* )title insertToothID:(NSString* )toothID
{
NSString* SQL = [NSStringstringWithFormat:@"INSERT INTO showimage(imgUrl,title,toothID) VALUES (\"%@\",\"%@\",\"%@\")",imgUrl,title,toothID];
char* errMsg;
if (sqlite3_exec(db, [SQLUTF8String], NULL,NULL, &errMsg)) {
return YES;
}
return NO;
}
//删除
- (BOOL)deleteWithId:(NSInteger )sId
{
NSLog(@"%li",sId);
NSString* SQL = [NSStringstringWithFormat:@"DELETE FROM showimage WHERE id = %i", sId];
NSLog(@"%@",SQL);
char *errMsg;
int ret = sqlite3_exec(db, [SQLUTF8String], NULL,NULL, &errMsg);
if (ret == SQLITE_OK) {
NSLog(@"%i,%s",ret,errMsg);
return YES;
}else
{
NSLog(@"%i,%s",ret,errMsg);
}
return NO;
}
//查询
- (NSArray*)select
{
NSMutableArray* data = [NSMutableArrayarray];
NSString* SQL =@"SELECT * FROM showimage";
sqlite3_stmt *statement;
const char *query_stmt = [SQLUTF8String];
if (sqlite3_prepare_v2(db, query_stmt, -1, &statement,NULL) == SQLITE_OK)
{
NSMutableDictionary* dic =nil;
while (sqlite3_step(statement) ==SQLITE_ROW)
{
dic = [NSMutableDictionarydictionary];
NSString *imgUrl = [[NSStringalloc] initWithUTF8String:(constchar *)sqlite3_column_text(statement,0)];
NSString *title = [[NSStringalloc] initWithUTF8String:
(constchar *)sqlite3_column_text(statement,1)];
NSString *toothID = [[NSStringalloc] initWithUTF8String:
(constchar *)sqlite3_column_text(statement,2)];
NSInteger sId = sqlite3_column_int(statement,3);
int newsId = sqlite3_column_int(statement,3);
NSLog(@"%i,%i",sId,newsId);
[dicsetObject:imgUrl forKey:@"imgUrl"];
[dicsetObject:title forKey:@"title"];
[dicsetObject:toothID forKey:@"toothID"];
[dic setObject:[NSNumbernumberWithInteger:sId] forKey:@"s_Id"];
[dataaddObject:dic];
}
sqlite3_finalize(statement);
}
return data;
}
//根据id来修改图片img和昵称title
- (BOOL)updateImgUrlWithTitle:(NSString*)imgUrl andName:(NSString*)title bySid:(NSInteger)Sid
{
constchar* sql ="update showimage set imgUrl = ?,title = ? where id = ?";
sqlite3_stmt *statement;
int ret = 0;
if (sqlite3_prepare_v2(db, sql, -1, &statement,NULL) == SQLITE_OK)
{
//准备语句
ret =sqlite3_bind_text(statement, 1, [imgUrl UTF8String], -1,NULL);
ret =sqlite3_bind_text(statement, 2, [title UTF8String], -1,NULL);
ret =sqlite3_bind_int(statement, 3, (int)Sid);
ret =sqlite3_step(statement);
if (SQLITE_DONE == ret) {
//查询语句
ret =sqlite3_finalize(statement);
return YES;
}else{
return NO;
}
}else {
NSLog(@"Error:%s",sqlite3_errmsg(db));
}
return NO;
}
+ (id)shareDBTool
{
if (!dbTool) {
dbTool = [[DBToolalloc] init];
}
return dbTool;
}