Sqlite的访问:
NoteSqlite:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 #import <Foundation/Foundation.h> 2 @class SqliteEntity; 3 #import "sqlite3.h" 4 5 @interface NoteSqlite : NSObject{ 6 NSString *databaseName; //操作的数据库名 7 NSString *TableName; //操作的表名 8 sqlite3 *database; 9 sqlite3_stmt *statement; 10 char *errorMsg; //错误信息 11 SqliteEntity *entitys; 12 } 13 14 @property (nonatomic,retain) NSString *_Execsql; 15 @property (nonatomic,retain) NSString *TableName; 16 @property (nonatomic,retain) NSString *databaseName; 17 18 //打开数据库 19 -(BOOL)Open; 20 //关闭数据库 21 -(BOOL)Close; 22 23 //创建表 24 -(BOOL)create; 25 26 //增加、删除、修改、查询 27 -(BOOL)insertDataForSql; 28 -(BOOL)deleteAll; 29 -(BOOL)deleteDataForSql; 30 -(BOOL)updateDataForSql; 31 32 -(SqliteEntity *)selectAll; 33 -(NSMutableArray *)selectForSql; 34 35 @end
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 #import "NoteSqlite.h" 2 #import "SqliteEntity.h" 3 4 @implementation NoteSqlite 5 6 - (id)init 7 { 8 self = [super init]; 9 return self; 10 } 11 12 //打开数据库 13 -(BOOL)Open{ 14 NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES); 15 NSString *documentsDirectory=[paths objectAtIndex:0]; 16 NSString *path=[documentsDirectory stringByAppendingPathComponent:databaseName]; 17 NSFileManager *fileManger=[NSFileManager defaultManager]; 18 BOOL find=[fileManger fileExistsAtPath:path]; 19 //判断文件是否存在 20 if (find) { 21 NSLog(@"数据库文件已经存在"); 22 //打开数据库、返回操作是否正确 23 if(sqlite3_open([path UTF8String], &database)==SQLITE_OK){ 24 NSLog(@"成功打开数据库"); 25 } 26 return YES; 27 }else{ 28 if(sqlite3_open([path UTF8String], &database)==SQLITE_OK){ 29 //调用createMusicList创建数据库和表 30 [self create]; 31 return YES; 32 }else{ 33 sqlite3_close(database); 34 NSLog(@"Error:open database file."); 35 return NO; 36 } 37 return NO; 38 } 39 } 40 41 //创建表 42 -(BOOL)create{ 43 if ([self ExecSql]) { 44 NSLog(@"create Ok."); 45 return YES; 46 } 47 return NO; 48 } 49 50 #pragma mark --新增、删除、修改、查询 51 -(BOOL)insertDataForSql{ 52 if ([self ExecSql]) { 53 NSLog(@"Insert Ok."); 54 return YES; 55 } 56 return NO; 57 } 58 59 -(BOOL)deleteAll{ 60 self._Execsql=[NSString stringWithFormat:@"Delete from %@",_TableName]; 61 if ([self ExecSql]) { 62 NSLog(@"Delete All Ok"); 63 return YES; 64 } 65 return NO; 66 } 67 68 -(BOOL)deleteDataForSql{ 69 if ([self ExecSql]) { 70 NSLog(@"Delete Ok"); 71 return YES; 72 } 73 return NO; 74 } 75 76 -(BOOL)updateDataForSql{ 77 if ([self ExecSql]) { 78 NSLog(@"Update Ok"); 79 return YES; 80 } 81 return NO; 82 } 83 84 -(NSMutableArray *)selecteAll{ 85 self._Execsql=[NSString stringWithFormat:@" select * from %@",TableName]; 86 entitys.array= [self ExecSlect]; 87 return entitys.array; 88 } 89 90 -(NSMutableArray *)selectForSql{ 91 entitys.array= [self ExecSlect]; 92 return entitys.array; 93 } 94 95 96 #pragma mark --sql语句执行(Insert,Update,delete) 97 -(BOOL)ExecSql{ 98 const char *execsql=[self._Execsql UTF8String]; 99 if (sqlite3_exec(database, execsql, NULL, NULL, &errorMsg)==SQLITE_OK) { 100 NSLog(@"执行语句成功"); 101 return YES; 102 } 103 NSLog(@"Error : %s",errorMsg); 104 sqlite3_free(errorMsg); 105 return NO; 106 } 107 108 #pragma mark --Sql语句查询 109 -(NSMutableArray *)ExecSlect{ 110 111 entitys=[[[SqliteEntity alloc]init]autorelease]; 112 entitys.array=[NSMutableArray arrayWithCapacity:6]; 113 114 const char *selectstr=[self._Execsql UTF8String]; 115 if (sqlite3_prepare_v2(database, selectstr, -1, &statement, Nil)==SQLITE_OK) { 116 NSLog(@"SELECT OK"); 117 //添加返回的数据 118 while (sqlite3_step(statement)==SQLITE_ROW) { 119 SqliteEntity *entity=[[SqliteEntity alloc]init]; 120 121 entity.catid=sqlite3_column_int(statement, 0); 122 entity.parentid=sqlite3_column_int(statement, 1); 123 entity.ordering=sqlite3_column_int(statement, 4); 124 125 entity.title=[NSString stringWithCString:(char *)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding]; 126 entity.entrytype=[NSString stringWithCString:(char *)sqlite3_column_text(statement, 3) encoding:NSUTF8StringEncoding]; 127 128 //字符串格式化和截取 129 //NSDateFormatter *fomater=[[NSDateFormatter alloc]init]; 130 //[fomater setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; 131 //NSData *ndate=[[fomater dateFromString:[entity.title substringToIndex:[entity.title length-5]]]; 132 133 //添加数据集 134 135 136 NSMutableDictionary *dic= [NSMutableDictionary dictionaryWithObjectsAndKeys: 137 [NSString stringWithFormat:@"%d",entity.catid],@"catid", 138 [NSString stringWithFormat:@"%d",entity.parentid],@"parentid", 139 entity.title,@"title", 140 entity.entrytype,@"entrytype", 141 [NSString stringWithFormat:@"%d",entity.ordering],@"ordering",nil]; 142 143 [entitys.array insertObject:dic atIndex:[entitys.array count]]; 144 145 [entity release]; 146 } 147 return entitys.array; 148 } 149 return entitys.array; 150 } 151 152 153 @end
SqliteEntity:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 #import <Foundation/Foundation.h> 2 3 @interface SqliteEntity : NSObject 4 5 @property(nonatomic,readwrite) int catid; 6 @property(nonatomic,readwrite) int parentid; 7 @property(nonatomic,retain) NSString *title; 8 @property(nonatomic,retain) NSString *entrytype; 9 @property(nonatomic,readwrite) int ordering; 10 @property(nonatomic,assign) NSMutableArray *array; //返回从数据库中取到的数据集 11 @end
Sqlite访问测试:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 #import <UIKit/UIKit.h> 2 #import "NoteSqlite.h" 3 4 @interface DatabaseViewController : UITableViewController{ 5 NoteSqlite *sqlite; 6 SqliteEntity *entity; 7 NSMutableArray *DataList; 8 } 9 10 @end
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 // 2 // DatabaseViewController.m 3 // UserSQLite 4 // 5 // Created by Liaoyang on 13-10-5. 6 // Copyright (c) 2013年 Liaoyang. All rights reserved. 7 // 8 9 #import "DatabaseViewController.h" 10 #import "NoteSqlite.h" 11 #import "SqliteEntity.h" 12 #import "DetilViewController.h" 13 14 @interface DatabaseViewController () 15 16 @end 17 18 @implementation DatabaseViewController 19 20 - (id)initWithStyle:(UITableViewStyle)style 21 { 22 self = [super initWithStyle:style]; 23 if (self) { 24 //读取数据库,准备好数据信息 25 26 sqlite=[[NoteSqlite alloc]init]; 27 sqlite.databaseName=@"IphoneDb"; 28 //创建表sql 29 sqlite._Execsql=@"create table MenuItems (catid int(5),parentid int(5),title varchar(32),entrytype varchar(12),ordering int(5))"; 30 31 [sqlite Open]; //打开数据库(或创建数据库) 32 33 sqlite.TableName=@"MenuItems"; 34 35 if ([sqlite create]) { 36 [sqlite deleteAll]; 37 //插入数据 38 sqlite._Execsql=@"insert into MenuItems (catid,parentid,title,entrytype,ordering) values (1,0,'First','category',1),(2,0,'Third','categroy',3),(3,0,'Second','categroy',2),(4,2,'Submenu','category',1),(5,0,'Action #1','result',4),(6,1,'Action #1B','result',1) "; 39 //数据插入 40 [sqlite insertDataForSql]; 41 } 42 43 sqlite._Execsql=@"select * from MenuItems where parentid=0 order by ordering asc"; 44 DataList= [sqlite selectForSql]; 45 if ([DataList count]>0) { //将表格下移动 46 CGPoint tablecenter=self.view.center; 47 self.view.center=CGPointMake(tablecenter.x, tablecenter.y+22); 48 [DataList retain]; 49 } 50 } 51 return self; 52 } 53 54 - (void)viewDidLoad 55 { 56 [super viewDidLoad]; 57 58 } 59 60 - (void)didReceiveMemoryWarning 61 { 62 [super didReceiveMemoryWarning]; 63 // Dispose of any resources that can be recreated. 64 } 65 66 #pragma mark - Table view data source 67 68 - (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView 69 { 70 return 1; 71 } 72 73 - (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section 74 { 75 return [DataList count]; 76 } 77 78 -(NSString *)tableView:(UITableView *)tableView titleForHeaderInSection:(NSInteger)section{ 79 return @"GetDataBase"; 80 } 81 82 - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath 83 { 84 static NSString *CellIdentifier = @"Cell"; 85 UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier]; 86 if (cell == nil) { 87 cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease]; 88 } 89 90 cell.textLabel.text=[[DataList objectAtIndex:indexPath.row]objectForKey:@"title"]; 91 if ([DataList objectAtIndex:indexPath.row]) { 92 cell.accessoryType=UITableViewCellAccessoryDisclosureIndicator; 93 } 94 95 return cell; 96 } 97 98 #pragma mark - Table view delegate 99 100 - (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath 101 { 102 entity=[DataList objectAtIndex:indexPath.row]; 103 DetilViewController *detail= [[DetilViewController alloc] init]; 104 detail.title=[[DataList objectAtIndex:indexPath.row]objectForKey:@"title"]; 105 106 [self.view addSubview:detail.view]; 107 [self.navigationController pushViewController:detail animated:YES]; 108 [detail release]; 109 110 } 111 112 @end