1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
#
import
”Note.h”
#
import
”sqlite3.h”
#define DBFILE_NAME @”NotesList.sqlite3″
@
interface
NoteDAO : NSObject
{
sqlite3 *db;
}
+ (NoteDAO*)sharedManager;
- (NSString *)applicationDocumentsDirectoryFile;
- (
void
)createEditableCopyOfDatabaseIfNeeded;
//插入Note方法
-(
int
) create:(Note*)model;
//删除Note方法
-(
int
) remove:(Note*)model;
//修改Note方法
-(
int
) modify:(Note*)model;
//查询所有数据方法
-(NSMutableArray*) findAll;
//按照主键查询数据方法
-(Note*) findById:(Note*)model;
@end
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
- (
void
)createEditableCopyOfDatabaseIfNeeded {
NSString *writableDBPath = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([writableDBPath UTF8String], &db) != SQLITE_OK) { ①
sqlite3_close(db); ②
NSAssert(NO,@”数据库打开失败。”);
}
else
{
char *err;
NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS Note
(cdate TEXT PRIMARY KEY, content TEXT);"]; ③
if
(sqlite3_exec(db,[createSQL UTF8String],NULL,NULL,&err) != SQLITE_OK) { ④
sqlite3_close(db); ⑤
NSAssert1(NO, @”建表失败, %s”, err); ⑥
}
sqlite3_close(db); ⑦
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
-(Note*) findById:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) { ①
sqlite3_close(db); ②
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *qsql = @”SELECT cdate,content FROM Note where cdate =?”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [qsql UTF8String], -
1
, &statement, NULL) == SQLITE_OK) { ③
//准备参数
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; ④
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [nsdate UTF8String], -
1
, NULL); ⑤
//执行
if
(sqlite3_step(statement) == SQLITE_ROW) { ⑥
char *cdate = (char *) sqlite3_column_text(statement,
0
); ⑦
NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate];
char *content = (char *) sqlite3_column_text(statement,
1
);
NSString * nscontent = [[NSString alloc] initWithUTF8String: content];
Note* note = [[Note alloc] init];
note.date = [dateFormatter dateFromString:nscdate];
note.content = nscontent;
sqlite3_finalize(statement);
sqlite3_close(db);
return
note;
}
}
sqlite3_finalize(statement); ⑧
sqlite3_close(db); ⑨
}
return
nil;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
-(NSMutableArray*) findAll
{
NSString *path = [self applicationDocumentsDirectoryFile];
NSMutableArray *listData = [[NSMutableArray alloc] init];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *qsql = @”SELECT cdate,content FROM Note”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [qsql UTF8String], -
1
, &statement, NULL) == SQLITE_OK) {
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
//执行
while
(sqlite3_step(statement) == SQLITE_ROW) {
char *cdate = (char *) sqlite3_column_text(statement,
0
);
NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate];
char *content = (char *) sqlite3_column_text(statement,
1
);
NSString * nscontent = [[NSString alloc] initWithUTF8String: content];
Note* note = [[Note alloc] init];
note.date = [dateFormatter dateFromString:nscdate];
note.content = nscontent;
[listData addObject:note];
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return
listData;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
-(
int
) create:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) { ①
sqlite3_close(db); ②
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *sqlStr = @”INSERT OR REPLACE INTO note (cdate, content) VALUES (?,?)”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [sqlStr UTF8String], -
1
, &statement, NULL) == SQLITE_OK) { ③
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [nsdate UTF8String], -
1
, NULL); ④
sqlite3_bind_text(statement,
2
, [model.content UTF8String], -
1
, NULL);
//执行插入
if
(sqlite3_step(statement) != SQLITE_DONE) { ⑤
NSAssert(NO, @”插入数据失败。”);
}
}
sqlite3_finalize(statement); ⑥
sqlite3_close(db); ⑦
}
return
0
;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
-(
int
) remove:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *sqlStr = @”DELETE from note where cdate =?”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [sqlStr UTF8String], -
1
, &statement, NULL) == SQLITE_OK) {
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [nsdate UTF8String], -
1
, NULL);
//执行插入
if
(sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @”删除数据失败。”);
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return
0
;
}
NoteDAO.m中的修改Note方法:
-(
int
) modify:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *sqlStr = @”UPDATE note
set
content=? where cdate =?”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [sqlStr UTF8String], -
1
, &statement, NULL) == SQLITE_OK) {
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [model.content UTF8String], -
1
, NULL);
sqlite3_bind_text(statement,
2
, [nsdate UTF8String], -
1
, NULL);
//执行插入
if
(sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @”修改数据失败。”);
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return
0
;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
#
import
”Note.h”
#
import
”sqlite3.h”
#define DBFILE_NAME @”NotesList.sqlite3″
@
interface
NoteDAO : NSObject
{
sqlite3 *db;
}
+ (NoteDAO*)sharedManager;
- (NSString *)applicationDocumentsDirectoryFile;
- (
void
)createEditableCopyOfDatabaseIfNeeded;
//插入Note方法
-(
int
) create:(Note*)model;
//删除Note方法
-(
int
) remove:(Note*)model;
//修改Note方法
-(
int
) modify:(Note*)model;
//查询所有数据方法
-(NSMutableArray*) findAll;
//按照主键查询数据方法
-(Note*) findById:(Note*)model;
@end
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
- (
void
)createEditableCopyOfDatabaseIfNeeded {
NSString *writableDBPath = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([writableDBPath UTF8String], &db) != SQLITE_OK) { ①
sqlite3_close(db); ②
NSAssert(NO,@”数据库打开失败。”);
}
else
{
char *err;
NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS Note
(cdate TEXT PRIMARY KEY, content TEXT);"]; ③
if
(sqlite3_exec(db,[createSQL UTF8String],NULL,NULL,&err) != SQLITE_OK) { ④
sqlite3_close(db); ⑤
NSAssert1(NO, @”建表失败, %s”, err); ⑥
}
sqlite3_close(db); ⑦
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
-(Note*) findById:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) { ①
sqlite3_close(db); ②
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *qsql = @”SELECT cdate,content FROM Note where cdate =?”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [qsql UTF8String], -
1
, &statement, NULL) == SQLITE_OK) { ③
//准备参数
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; ④
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [nsdate UTF8String], -
1
, NULL); ⑤
//执行
if
(sqlite3_step(statement) == SQLITE_ROW) { ⑥
char *cdate = (char *) sqlite3_column_text(statement,
0
); ⑦
NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate];
char *content = (char *) sqlite3_column_text(statement,
1
);
NSString * nscontent = [[NSString alloc] initWithUTF8String: content];
Note* note = [[Note alloc] init];
note.date = [dateFormatter dateFromString:nscdate];
note.content = nscontent;
sqlite3_finalize(statement);
sqlite3_close(db);
return
note;
}
}
sqlite3_finalize(statement); ⑧
sqlite3_close(db); ⑨
}
return
nil;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
-(NSMutableArray*) findAll
{
NSString *path = [self applicationDocumentsDirectoryFile];
NSMutableArray *listData = [[NSMutableArray alloc] init];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *qsql = @”SELECT cdate,content FROM Note”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [qsql UTF8String], -
1
, &statement, NULL) == SQLITE_OK) {
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
//执行
while
(sqlite3_step(statement) == SQLITE_ROW) {
char *cdate = (char *) sqlite3_column_text(statement,
0
);
NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate];
char *content = (char *) sqlite3_column_text(statement,
1
);
NSString * nscontent = [[NSString alloc] initWithUTF8String: content];
Note* note = [[Note alloc] init];
note.date = [dateFormatter dateFromString:nscdate];
note.content = nscontent;
[listData addObject:note];
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return
listData;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
-(
int
) create:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) { ①
sqlite3_close(db); ②
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *sqlStr = @”INSERT OR REPLACE INTO note (cdate, content) VALUES (?,?)”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [sqlStr UTF8String], -
1
, &statement, NULL) == SQLITE_OK) { ③
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [nsdate UTF8String], -
1
, NULL); ④
sqlite3_bind_text(statement,
2
, [model.content UTF8String], -
1
, NULL);
//执行插入
if
(sqlite3_step(statement) != SQLITE_DONE) { ⑤
NSAssert(NO, @”插入数据失败。”);
}
}
sqlite3_finalize(statement); ⑥
sqlite3_close(db); ⑦
}
return
0
;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
-(
int
) remove:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *sqlStr = @”DELETE from note where cdate =?”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [sqlStr UTF8String], -
1
, &statement, NULL) == SQLITE_OK) {
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [nsdate UTF8String], -
1
, NULL);
//执行插入
if
(sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @”删除数据失败。”);
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return
0
;
}
NoteDAO.m中的修改Note方法:
-(
int
) modify:(Note*)model
{
NSString *path = [self applicationDocumentsDirectoryFile];
if
(sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO,@”数据库打开失败。”);
}
else
{
NSString *sqlStr = @”UPDATE note
set
content=? where cdate =?”;
sqlite3_stmt *statement;
//预处理过程
if
(sqlite3_prepare_v2(db, [sqlStr UTF8String], -
1
, &statement, NULL) == SQLITE_OK) {
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@
"yyyy-MM-dd HH:mm:ss"
];
NSString *nsdate = [dateFormatter stringFromDate:model.date];
//绑定参数开始
sqlite3_bind_text(statement,
1
, [model.content UTF8String], -
1
, NULL);
sqlite3_bind_text(statement,
2
, [nsdate UTF8String], -
1
, NULL);
//执行插入
if
(sqlite3_step(statement) != SQLITE_DONE) {
NSAssert(NO, @”修改数据失败。”);
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return
0
;
}
|