iphone开发之iphone操作数据库
iphone操作数据库简单例子。
代码1
1 #import <UIKit/UIKit.h>
2 #import "sqlite3.h"
3
4 #define kFilename @"data.sqlite3"
5 @interface PersistenceViewController : UIViewController {
6 UITextField *field1;
7 UITextField *field2;
8 UITextField *field3;
9 UITextField *field4;
10 //创建了一个sqlite3类型的指针，将指向数据库对象
11 sqlite3 *database;
12
13 }
14 @property (nonatomic, retain) IBOutlet UITextField *field1;
15 @property (nonatomic, retain) IBOutlet UITextField *field2;
16 @property (nonatomic, retain) IBOutlet UITextField *field3;
17 @property (nonatomic, retain) IBOutlet UITextField *field4;
18
19 - (NSString *)dataFilePath;
20 - (void)applicationWillTerminate:(NSNotification *)notification;
21 @end
代码2
1 #import "PersistenceViewController.h"
2 @implementation PersistenceViewController
3 @synthesize field1;
4 @synthesize field2;
5 @synthesize field3;
6 @synthesize field4;
7
8 -(BOOL)openDatabase{
9 BOOL success=NO;
10 if (sqlite3_open([[self dataFilePath] UTF8String], &database)!= SQLITE_OK) {
11 sqlite3_close(database);
12 return success;
13 }
14 return YES;
15 }
16
18 - (NSString *)dataFilePath {
19 NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
20 NSString *documentsDirectory = [paths objectAtIndex:0];
21 NSLog(documentsDirectory);
22 return [documentsDirectory stringByAppendingPathComponent:kFilename];
23 }
24
25 //程序退出时，本方法执行，完成数据插入到数据库表里
26 - (void)applicationWillTerminate:(NSNotification *)notification {
28 for (int i = 1; i <= 4; i++)
29 {
30 NSString *fieldName = [[NSString alloc]initWithFormat:@"field%d", i];
31 UITextField *field = [self valueForKey:fieldName];
32 [fieldName release];
33
34 char *errorMsg;
35 //插入语句如果完整，可以直接用sqlite3_exec执行。
36 // char *update = "INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA) VALUES (1,'XX');";
37
38 //但sql语句包含 "?",需要给"?"赋值,将来用sqlite3_prepare_v2
39 char *update = "INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA) VALUES (?, ?);";
40 sqlite3_stmt *stmt;
41 //准备执行插入语句，
42 if (sqlite3_prepare_v2(database, update, -1, &stmt,nil) == SQLITE_OK) {
43 //把"i"这个整形值给第一个“?"
44 sqlite3_bind_int(stmt, 1, i);
45 //把textField的内容值给第二个“?"
46 sqlite3_bind_text(stmt, 2, [field.text UTF8String], -1, NULL);
47 }
48 if (sqlite3_step(stmt) != SQLITE_DONE)
49 NSAssert1(0, @"Error updating table: %s", errorMsg);
50 //关闭sqlite3_stmt对象
51 sqlite3_finalize(stmt);
52 }
53 //关闭数据库
54 sqlite3_close(database);
55 }
56
57 #pragma mark -
58
59 BOOL isDir;
60 -(void)setDir:(BOOL *)dir{
61 *dir=YES;
62 }
63
64
65 NSString *str=nil;
66
67 -(void)setString:(NSString **)myStr{
68 *myStr=@"新赋值";
69
70 }
71
72 - (void)viewDidLoad {
73 [self setDir:&isDir];
75 [self setString:&str];
76 NSLog(str);
78 //第一步，必须要打开数据库
79 [self openDatabase];
81 char *errorMsg=nil;//当执行语句出错的时，errorMsg记录了错误信息
82 //创建一个表
83 char *createSQL="CREATE TABLE IF NOT EXISTS FIELDS (ROW INTEGER PRIMARY KEY, FIELD_DATA TEXT);";
84 //sqlite3_exec执行一个sql语句，这个语句一般是没有返回的数据集
85
86 if (sqlite3_exec (database, createSQL ,NULL, NULL, &errorMsg) != SQLITE_OK) {
87 sqlite3_close(database);
88 NSAssert1(0, @"Error creating table: %s", errorMsg);
89 }
90
92 //查询记录，返回的结果是两个字段对应的列
93 NSString *query = @"SELECT ROW, FIELD_DATA FROM FIELDS ORDER BY ROW";
95 sqlite3_stmt *statement;//把查询后的结果保存在sqlite3_stmt对象里
96
97 //查询语句有返回结果，需要单步取值，用sqlite3_prepare_v2执行语句
98 if (sqlite3_prepare_v2(database, [query UTF8String],-1, &statement, nil) == SQLITE_OK) {
99 //每循环一次，从statement对象取得一行的值
100 while (sqlite3_step(statement) == SQLITE_ROW) {//判断表示目前有行可取
101
102 //0表示第一列，因为本列是整形，用sqlite3_column_int得到值
103 int row = sqlite3_column_int(statement, 0);
104 //1表示第二列，因为本列是text类型(字符串)，用sqlite3_column_text得到值
105 const char *rowData =(char *)sqlite3_column_text(statement, 1);
106
107 NSString *fieldName = [[NSString alloc] initWithFormat:@"field%d", row];
108 //把ｃ字符串转化为NSString
109 NSString *fieldValue = [[NSString alloc] initWithUTF8String:rowData];
110 //通过fieldName找到UITextField对象
111 UITextField *field = [self valueForKey:fieldName];
112 field.text = fieldValue;
113 [fieldName release];
114 [fieldValue release];
115 }
116 sqlite3_finalize(statement);
117 }
118
119 UIApplication *app = [UIApplication sharedApplication];
120
121 [[NSNotificationCenter defaultCenter] addObserver:self
122 selector:@selector(applicationWillTerminate:)
123 name:UIApplicationWillTerminateNotification
124 object:app];
125 [super viewDidLoad];
126 }
127
128 //删除表中的数据
129 -(void)deleteTableData{
130
131 char *deleteSql="DELETE from FIELDS";
132 // char *deleteSql="DELETE from FIELDS WHERE ROW=1";
133 if (sqlite3_exec (database, deleteSql ,NULL, NULL, NULL) != SQLITE_OK) {
134 sqlite3_close(database);
135 }
136 }
137
138
139 -(void)updateTableData{
140 char *errorMsg=nil;
141 char *updateSql="UPDATE FIELDS SET FIELD_DATA='123' WHERE ROW=1";
142 if (sqlite3_exec (database, updateSql ,NULL, NULL, &errorMsg) != SQLITE_OK) {
143 sqlite3_close(database);
144 NSAssert1(0, @"Error creating table: %s", errorMsg);
145 }
146 }
147
148 //更新指定某行的字段的值
149 -(void)updateTableDataCustom:(int)myRow content:(NSString *)content{
150 char *errorMsg=nil;
151 NSString *sql=[[NSString alloc] initWithFormat:@"UPDATE FIELDS SET FIELD_DATA='%@' WHERE ROW=%d",content,myRow];
152 NSLog(sql);
153 char *updateSql=(char *)[sql UTF8String];
154 if (sqlite3_exec (database, updateSql ,NULL, NULL, &errorMsg) != SQLITE_OK) {
155 sqlite3_close(database);
156 NSAssert1(0, @"Error creating table: %s", errorMsg);
157 }
158
159 }
160
161 //删除表
162 -(void)deleteTable{
163 char *errorMsg=nil;
164 char *deleteSql="DROP TABLE FIELDS";
165 if (sqlite3_exec (database, deleteSql ,NULL, NULL, &errorMsg) != SQLITE_OK) {
166 sqlite3_close(database);
167 NSAssert1(0, @"Error creating table: %s", errorMsg);
168 }
169 }
170
171 //选择最大行的ID
172 -(NSInteger)selectMaxID{
173 NSInteger sid=0;
174 NSString *strSQL =@"SELECT MAX(ROW) FROM FIELDS";
175 sqlite3_stmt *stmt;
176 if (sqlite3_prepare_v2(database, [strSQL UTF8String], -1, &stmt, nil) == SQLITE_OK) {
177 while (sqlite3_step(stmt) == SQLITE_ROW) {
178 sid = sqlite3_column_int(stmt, 0);
179 }
180 }
181 sqlite3_finalize(stmt);
182 NSLog(@"最大行数是:%D",sid);
183 return sid;
184 }
185
186 //查询行数
187 -(NSInteger)selectRowNums{
188
189 NSInteger sid=0;
190 NSString *strSQL =@"SELECT COUNT(ROW) FROM FIELDS";
191 sqlite3_stmt *stmt;
192 if (sqlite3_prepare_v2(database, [strSQL UTF8String], -1, &stmt, nil) == SQLITE_OK) {
193 while (sqlite3_step(stmt) == SQLITE_ROW) {
194 sid = sqlite3_column_int(stmt, 0);
195 }
196 }
197 sqlite3_finalize(stmt);
198 NSLog(@"总行数是:%D",sid);
199 return sid;
200
201 }
202
203
204 //扩充表字段，重复添加字段程序会崩溃
205 -(void)extendField:(NSString *)fn{//fn表示要增加的字段
206 [fn retain];
207 char *errorMsg=nil;
208 NSString *sql=[NSString stringWithFormat:@"ALTER TABLE FIELDS ADD %@ TEXT",fn];
209
210 const char *exSql=[sql UTF8String];
211 if (sqlite3_exec (database, exSql ,NULL, NULL, &errorMsg) != SQLITE_OK) {
212 sqlite3_close(database);
213 NSAssert1(0, @"Error creating table: %s", errorMsg);
214 }
215
216 }
217
218 //对现存列改名：
219 //ALTER TABLE distributors RENAME COLUMN address TO city;
220 //
221 //对现存表改名：
222 //ALTER TABLE distributors RENAME TO suppliers;
223
224 - (void)viewDidUnload {
225 // Release any retained subviews of the main view.
226 // e.g. self.myOutlet = nil;
227 self.field1 = nil;
228 self.field2 = nil;
229 self.field3 = nil;
230 self.field4 = nil;
231 [super viewDidUnload];
232 }
233 - (void)dealloc {
234 [field1 release];
235 [field2 release];
236 [field3 release];
237 [field4 release];
238 [super dealloc];
239 }
240 @end
代码3
1 #import <Foundation/Foundation.h>
2
3 #define kField1Key @"Field1"
4 #define kField2Key @"Field2"
5 #define kField3Key @"Field3"
6 #define kField4Key @"Field4"
7
8 @interface FourLines : NSObject <NSCoding, NSCopying> {
9 NSString *field1;
10 NSString *field2;
11 NSString *field3;
12 NSString *field4;
13 }
14 @property (nonatomic, retain) NSString *field1;
15 @property (nonatomic, retain) NSString *field2;
16 @property (nonatomic, retain) NSString *field3;
17 @property (nonatomic, retain) NSString *field4;
18 @end
代码4
#import "FourLines.h"
@implementation FourLines
@synthesize field1;
@synthesize field2;
@synthesize field3;
@synthesize field4;
#pragma mark NSCoding
- (void)encodeWithCoder:(NSCoder *)encoder {
[encoder encodeObject:field1 forKey:kField1Key];
[encoder encodeObject:field2 forKey:kField2Key];
[encoder encodeObject:field3 forKey:kField3Key];
[encoder encodeObject:field4 forKey:kField4Key];
}
- (id)initWithCoder:(NSCoder *)decoder {
if (self = [super init]) {
self.field1 = [decoder decodeObjectForKey:kField1Key];
self.field2 = [decoder decodeObjectForKey:kField2Key];
self.field3 = [decoder decodeObjectForKey:kField3Key];
self.field4 = [decoder decodeObjectForKey:kField4Key];
}
return self;
}
#pragma mark -
#pragma mark NSCopying
- (id)copyWithZone:(NSZone *)zone {
FourLines *copy = [[[self class] allocWithZone: zone] init];
copy.field1 = [[self.field1 copyWithZone:zone] autorelease];
copy.field2 = [[self.field2 copyWithZone:zone] autorelease];
copy.field3 = [[self.field3 copyWithZone:zone] autorelease];
copy.field4 = [[self.field4 copyWithZone:zone] autorelease];
return copy;
}
@end
