Iphone中Sqlite的使用

 1 #import 
 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

  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

 1 #import 
 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

 1 #import 
 2 #import "NoteSqlite.h"
 3 
 4 @interface DatabaseViewController : UITableViewController{
 5     NoteSqlite *sqlite;
 6     SqliteEntity *entity;
 7     NSMutableArray *DataList;
 8 }
 9 
10 @end

  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