1 #import "ViewController.h"
2 #import <sqlite3.h>
3 #import "DataBaseManager.h"
4 @interface ViewController ()
5
6 @end
7
8 @implementation ViewController
9
10 - (void)viewDidLoad {
11 [super viewDidLoad];
12 /*
13 //获取documents的路径
14 NSString *doctmentsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
15 //创建数据库路径
16 NSString *sqlPath = [doctmentsPath stringByAppendingPathComponent:@"student.sqlite"];
17
18 //打开数据库,将文件路径转化为char类型的,二阶指针是指针的地址
19 //数据库指针sqlite3
20 #warning 二阶指针 db
21 sqlite3 *db = nil;
22 NSLog(@"%p",db);
23 sqlite3_open(sqlPath.UTF8String, &db);
24 NSLog(@"%p",db);
25
26 //创建stu表,包含字段s_id,s_name,s_age
27 NSString *sqlStr = @"create table if not exists stu (s_id integer primary key,s_name text ,s_age integer)";
28 //通过result接受执行结果
29 int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, NULL);
30
31 NSLog(@"%@",result == SQLITE_OK ? @"建表成功":@"建表失败");
32 NSLog(@"%@",sqlPath);
33
34 //插入数据insert into
35 NSString *insertSql = @"insert into stu (s_name,s_age) values ('zhangsan',20)";
36 int resultInsert = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, NULL);
37 NSLog(@"%@",resultInsert == SQLITE_OK ? @"插入成功":@"插入失败");
38
39 //更改数据
40 NSString *upSql = @"update stu set s_age = 100 where s_age = 20";
41 int resultUpData = sqlite3_exec(db, upSql.UTF8String, NULL, NULL, NULL);
42 NSLog(@"%@",resultUpData == SQLITE_OK ? @"更改成功":@"更改失败");
43
44 //删除数据
45
46 NSString *deleteSql = @"delete from stu";
47 int deleteResult = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, NULL);
48 NSLog(@"%@",deleteResult == SQLITE_OK ? @"删除成功":@"删除失败");
49
50 //关闭数据库
51 int closeResult = sqlite3_close(db);
52 NSLog(@"%@",closeResult == SQLITE_OK ? @"关闭成功":@"关闭失败");
53 */
54
55
56 DataBaseManager *dbManager = [DataBaseManager shareInstance];
57 //使用自定义DataBaseManager,进行数据库操作
58 //打开数据库
59 [dbManager openDB];
60 //创建表
61 [dbManager createTable];
62 [dbManager insertName:@"张全蛋" Age:20];
63
64 NSArray *stuArray =[dbManager selectAll];
65 NSLog(@"%@",stuArray);
66 NSArray *nameArray = [dbManager selectWithName:@"张全蛋"];
67 NSLog(@"张全蛋%@",nameArray);
68 [dbManager closeDBm];
69
70
71 }
1 #import <Foundation/Foundation.h>
2
3 @interface DataBaseManager : NSObject
4 //单例
5 +(instancetype)shareInstance;
6 //打开数据库
7 -(void)openDB;
8 //创建表
9 -(void)createTable;
10 //插入数据
11 -(void)insertName:(NSString *)name Age:(NSInteger) age;
12 //删除数据
13 -(void)deleteWithName:(NSString *)name;
14 //更新数据
15 -(void)updateTable;
16 //查询数据
17 -(NSArray *)selectAll;
18 -(NSArray *)selectWithName:(NSString *)name;
19 //关闭数据库
20 -(void)closeDBm;
21 @end
1 #import "DataBaseManager.h"
2 #import <sqlite3.h>
3 @interface DataBaseManager()
4 //数据库路径
5 @property (nonatomic,copy)NSString *sqlPath;
6
7 @end
8
9 @implementation DataBaseManager
10 //单例
11 +(instancetype)shareInstance
12 {
13 static DataBaseManager *manager = nil;
14 //两种方式创建单例
15 //1.这种方式在工程的整个生命周期只被执行一次,一直到工程关闭被释放
16 static dispatch_once_t onceToken;
17 dispatch_once(&onceToken,^{
18 manager = [[DataBaseManager alloc] init];
19 });
20 // //2.第二种方法
21 // if (manager == nil) {
22 // manager = [[DataBaseManager alloc] init];
23 // }
24 return manager;
25 }
26 //重写getter方法
27 -(NSString *)sqlPath
28 {
29 //if判断避免每次都进行创建
30 if (_sqlPath == nil) {
31 _sqlPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0] stringByAppendingPathComponent:@"student.sqlite"];
32 }
33 return _sqlPath;
34 }
35 //由于数据库的表指针全局都要用,所以定义为全局变量
36 static sqlite3 *db = nil;
37 //打开数据库
38 -(void)openDB
39 {
40 #warning 如果sqlPath写成实例变量的形式则不会走getter方法,
41 int result = sqlite3_open(self.sqlPath.UTF8String, &db);
42 NSLog(@"%@",result == SQLITE_OK ?@"打开成功":@"打开失败");
43 }
44 //创建表
45 -(void)createTable
46 {
47 //创建表的SQL
48 #warning 要把类型放前边,约束放后边 如果把主键约束放前边,integer放后边会出错.
49 NSString *sqlString = @"create table if not exists stu(s_id integer primary key autoincrement,s_name text,s_age integer)";
50 //执行SQL
51 int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
52 NSLog(@"%@", result == SQLITE_OK ?@"建表成功": @"建表失败");
53 }
54 //插入数据
55 -(void)insertName:(NSString *)name Age:(NSInteger) age;
56 {
57 NSString *sqlString = [NSString stringWithFormat:@"insert into stu (s_name,s_age) values ('%@','%ld')",name,age];
58 int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
59 NSLog(@"%@", result == SQLITE_OK ?@"插入成功": @"插入失败");
60 }
61 //删除数据
62 -(void)deleteWithName:(NSString *)name
63 {
64 NSString *sqlString = [NSString stringWithFormat:@"delete from stu where s_name = '%@'",name];
65 int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
66 NSLog(@"%@", result == SQLITE_OK ?@"删除成功": @"删除失败");
67 }
68 //更新数据
69 -(void)updateTable
70 {
71 NSString *sqlString = @"updata stu set s_age = 10 ";
72 int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
73 NSLog(@"%@", result == SQLITE_OK ?@"更新成功": @"更新失败");
74
75 }
76
77 //查询数据
78 -(NSArray *)selectAll
79 {
80 NSMutableArray *array =[NSMutableArray new];
81 //查询SQL语句
82 NSString *sqlString = @"select * from stu";
83 //执行查询
84
85 //stmt伴随指针
86 sqlite3_stmt *stmt = nil;
87 //先创建伴随指针,再进行预执行,stmt指向的是step将要执行的语句
88 int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
89 if (result == SQLITE_OK) {
90 //预执行成功,条件循环取出所有数据
91 while (sqlite3_step(stmt) == SQLITE_ROW) {
92 //第二个参数是指第几列
93 NSInteger s_id = sqlite3_column_int(stmt, 0);
94 //NSString *name = [NSString stringWithUTF8String:sqlite3_column_text(stmt, 1)];
95 //上面的写法报黄,下面的写法没问题
96 NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
97
98 NSInteger age = sqlite3_column_int(stmt, 2);
99 //由于s_id,age不是对象类型的,所以需要用@()这种形式强转
100 NSDictionary *studentDic = [NSDictionary dictionaryWithObjectsAndKeys:@(s_id),@"s_id",name,@"s_name",@(age),@"s_age", nil];
101 [array addObject:studentDic];
102 }
103 //释放伴随指针
104 sqlite3_finalize(stmt);
105 //返回数组
106 return array;
107 }
108
109 return nil;
110 }
111 -(NSArray *)selectWithName:(NSString *)name
112 {
113 NSMutableArray *array = [NSMutableArray array];
114 //SQL语句
115 NSString *sqlString = @"select * from stu where s_name = ?";
116 //预执行
117 //伴随指针
118 sqlite3_stmt *stmt = nil;
119 int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
120 if (result == SQLITE_OK) {
121 //预执行成功,绑定参数 bind
122 //sqlite3_column查询获得数据,列数从0开始
123 //第二个参数:sqilit3_bind_绑定参数,表示参数所在的列,第三个参数是绑定的参数
124 sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
125 while (sqlite3_step(stmt) == SQLITE_ROW) {
126 //第二个参数是指第几列
127 NSInteger s_id = sqlite3_column_int(stmt, 0);
128 //NSString *name = [NSString stringWithUTF8String:sqlite3_column_text(stmt, 1)];
129 //上面的写法报黄,下面的写法没问题
130 NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
131
132 NSInteger age = sqlite3_column_int(stmt, 2);
133 //由于s_id,age不是对象类型的,所以需要用@()这种形式强转
134 NSDictionary *studentDic = [NSDictionary dictionaryWithObjectsAndKeys:@(s_id),@"s_id",name,@"s_name",@(age),@"s_age", nil];
135 [array addObject:studentDic];
136 }
137
138 }
139 //释放伴随指针
140 sqlite3_finalize(stmt);
141 return array;
142 }
143 //关闭数据库
144 -(void)closeDBm
145 {
146 int result = sqlite3_close(db);
147 NSLog(@"%@", result == SQLITE_OK ?@"关闭成功": @"关闭失败");
148
149 }
150 @end
