viApDnSr

1static sqlite3 *sqlDB = nil;
2#pragma mark - SQLite操作
3// 创建数据库
4- (void)createSQLiteDB
5{
6    NSLog(@"%s", __func__);
7    if (sqlDB != nil) {
8        NSLog(@"sql db is opened");
9        return ;
10    }
11    
12    NSString *docPath = NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES).firstObject;
13    NSString *sqlFilePath = [docPath stringByAppendingPathComponent:@"zichen.db"];
14    NSFileManager *fileManeger = [NSFileManager defaultManager];
15    if ([fileManeger fileExistsAtPath:docPath] == NO) {
16        // 创建文件夹
17        BOOL res = [fileManeger createFileAtPath:sqlFilePath contents:nil attributes:nil];
18        NSLog(@"创建文件: %@", res == YES ? @"成功":@"失败");
19    }
20    else {
21        NSLog(@"找到了文件 %@", sqlFilePath);
22        [self openDataBase];
23    }
24}
25
26// 打开数据表
27- (sqlite3 *)openDataBase
28{
29    if (sqlDB != nil) {
30        return sqlDB;
31    }
32    
33    NSString *docPath = NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES).firstObject;
34    NSString *sqlFilePath = [docPath stringByAppendingPathComponent:@"zichen.db"];
35    const char *dbPath = [sqlFilePath UTF8String];
36    if (sqlite3_open(dbPath, &sqlDB) == SQLITE_OK) {
37        NSLog(@"open success");
38    }
39    else {
40        NSLog(@"open fail");
41    }
42    return sqlDB;
43}
44
45// 创建数据表
46- (void)createSQLiteTable
47{
48    NSLog(@"%s", __func__);
49    [self openDataBase];
50    NSString *sql = @"create table if not exists orderTable (ID integer primary key, productName text not null, price text)";
51    int result = sqlite3_exec(sqlDB, sql.UTF8String, nil, nil, nil);
52    if (result == SQLITE_OK) {
53        NSLog(@"Create Table success");
54    }
55    else {
56        NSLog(@"Create Table fail");
57    }
58}
59
60// 插入数据
61- (void)InsertSQLiteTable
62{
63    NSLog(@"%s", __func__);
64    [self openDataBase];
65    NSString *sql = @"insert into orderTable values(?,?,?)";
66    sqlite3_stmt *stmt = nil;
67    
68    NSString *price = [NSString stringWithFormat:@"%lf",20.99];
69    
70    int result = sqlite3_prepare_v2(sqlDB, sql.UTF8String, -1, &stmt, nil);
71    if (result == SQLITE_OK) {
72        // 下标从1开始
73        sqlite3_bind_int(stmt, 1, 20180417);
74        sqlite3_bind_text(stmt, 2, "团购商品", -1, nil);
75        sqlite3_bind_text(stmt, 3, price.UTF8String, -1, nil);
76        
77        sqlite3_step(stmt);
78    }
79    sqlite3_finalize(stmt);
80}
81
82// 查询数据
83- (void)SelectSQLiteTable
84{
85    NSLog(@"%s", __func__);
86    [self openDataBase];
87    NSString *sql = @"select * from orderTable";
88    sqlite3_stmt *stmt = nil;
89    
90    int result = sqlite3_prepare_v2(sqlDB, sql.UTF8String, -1, &stmt, nil);
91    if (result == SQLITE_OK) {
92        while (sqlite3_step(stmt) == SQLITE_ROW) {
93            // 下标从0开始
94            int orderId = sqlite3_column_int(stmt, 0);
95            NSString *colIdName = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_name(stmt, 0)];
96            NSString *productName = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
97            NSString *colProName = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_name(stmt, 1)];
98            NSString *price = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
99            NSString *colPriceName = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_name(stmt, 2)];
100            NSLog(@"%@:%d - %@:%@ - %@:%@", colIdName, orderId, colProName,productName, colPriceName,price);
101        }
102    }
103    sqlite3_finalize(stmt);
104}
105
106// 更新数据
107- (void)updateSQLiteTable
108{
109    NSLog(@"%s", __func__);
110    [self openDataBase];
111    
112    NSString *sql = @"update orderTable set productName = ? where ID = ?";
113    sqlite3_stmt *stmt = nil;
114    
115    int result = sqlite3_prepare_v2(sqlDB, sql.UTF8String, -1, &stmt, nil);
116    if (result == SQLITE_OK) {
117        sqlite3_bind_text(stmt, 1, "更新团购", -1, nil);
118        sqlite3_bind_int(stmt, 2, 20180417);
119        sqlite3_step(stmt);
120    }
121    sqlite3_finalize(stmt);
122}
123
124// 删除数据
125- (void)DeleteSQLiteTable
126{
127    NSLog(@"%s", __func__);
128    [self openDataBase];
129    
130    NSString *sql = @"delete from orderTable where ID = ?";
131    sqlite3_stmt *stmt = nil;
132    
133    int result = sqlite3_prepare_v2(sqlDB, sql.UTF8String, -1, &stmt, nil);
134    if (result == SQLITE_OK) {
135        sqlite3_bind_double(stmt, 1, 20180417);
136        sqlite3_step(stmt);
137    }
138    sqlite3_finalize(stmt);
139}
140
141// 删除数据表
142- (void)DropSQLiteTable
143{
144    NSLog(@"%s", __func__);
145    [self openDataBase];
146    NSString *sql = @"drop table orderTable";
147    sqlite3_stmt *stmt = nil;
148    int result = sqlite3_prepare_v2(sqlDB, sql.UTF8String, -1, &stmt, nil);
149    if (result == SQLITE_OK) {
150        sqlite3_step(stmt);
151    }
152    sqlite3_finalize(stmt);
153}
154
155// 执行sql
156- (void)execSQLiteTable
157{
158    NSLog(@"%s", __func__);
159    [self openDataBase];
160    
161    NSString *execSql = [NSString stringWithFormat:@"update orderTable set productName = '%@' where ID = '%@'", @"团购",@"20180417"];
162    char *errorMsg;
163    if (sqlite3_exec(sqlDB, execSql.UTF8String, NULL, NULL, &errorMsg) == SQLITE_OK) {
164        NSLog(@"执行成功");
165    }
166}