IOS-SQLite3的封装

IWStudent.h

 1 //
 2 //  IWStudent.h
 3 //  02-SQLite的封装
 4 //
 5 //  Created by apple on 14-5-22.
 6 //  Copyright (c) 2014年 itcast. All rights reserved.
 7 //
 8 
 9 #import <Foundation/Foundation.h>
10 
11 @interface IWStudent : NSObject
12 @property (nonatomic, assign) int ID;
13 @property (nonatomic, copy) NSString *name;
14 @property (nonatomic, assign) int age;
15 @end
16 
17 
18 
19 //
20 //  IWStudent.m
21 //  02-SQLite的封装
22 //
23 //  Created by apple on 14-5-22.
24 //  Copyright (c) 2014年 itcast. All rights reserved.
25 //
26 
27 #import "IWStudent.h"
28 
29 @implementation IWStudent
30 
31 @end

IWStudentTool.h

  1 //
  2 //  IWStudentTool.h
  3 //  02-SQLite的封装
  4 //
  5 //  Created by apple on 14-5-22.
  6 //  Copyright (c) 2014年 itcast. All rights reserved.
  7 //  学生数据的CRUD（增删改查）
  8 
  9 #import <Foundation/Foundation.h>
 10 @class IWStudent;
 11 
 12 @interface IWStudentTool : NSObject
 13 
 14 /**
 15  *  添加学生
 16  *
 17  *  @param student 需要添加的学生
 18  */
 19 + (BOOL)addStudent:(IWStudent *)student;
 20 
 21 /**
 22  *  获得所有的学生
 23  *
 24  *  @return 数组中装着都是IWStudent模型
 25  */
 26 + (NSArray *)students;
 27 
 28 /**
 29  *  根据搜索条件获得对应的学生
 30  *
 31  *  @param condition 搜索条件
 32  */
 33 + (NSArray *)studentsWithCondition:(NSString *)condition;
 34 
 35 @end
 36 
 37 
 38 
 39 //
 40 //  IWStudentTool.m
 41 //  02-SQLite的封装
 42 //
 43 //  Created by apple on 14-5-22.
 44 //  Copyright (c) 2014年 itcast. All rights reserved.
 45 //
 46 
 47 #import "IWStudentTool.h"
 48 #import "IWStudent.h"
 49 #import <sqlite3.h>
 50 
 51 @implementation IWStudentTool
 52 
 53 // static的作用：能保证_db这个变量只被IWStudentTool.m直接访问
 54 static sqlite3 *_db;
 55 
 56 + (void)initialize
 57 {
 58     // 0.获得沙盒中的数据库文件名
 59     NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
 60     
 61     // 1.创建(打开)数据库（如果数据库文件不存在，会自动创建）
 62     int result = sqlite3_open(filename.UTF8String, &_db);
 63     if (result == SQLITE_OK) {
 64         NSLog(@"成功打开数据库");
 65         
 66         // 2.创表
 67         const char *sql = "create table if not exists t_student (id integer primary key autoincrement, name text, age integer);";
 68         char *errorMesg = NULL;
 69         int result = sqlite3_exec(_db, sql, NULL, NULL, &errorMesg);
 70         if (result == SQLITE_OK) {
 71             NSLog(@"成功创建t_student表");
 72         } else {
 73             NSLog(@"创建t_student表失败:%s", errorMesg);
 74         }
 75     } else {
 76         NSLog(@"打开数据库失败");
 77     }
 78 }
 79 
 80 + (BOOL)addStudent:(IWStudent *)student
 81 {
 82     NSString *sql = [NSString stringWithFormat:@"insert into t_student (name, age) values('%@', %d);", student.name, student.age];
 83 
 84     char *errorMesg = NULL;
 85     int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errorMesg);
 86     
 87     return result == SQLITE_OK;
 88 }
 89 
 90 + (NSArray *)students
 91 {
 92     // 0.定义数组
 93     NSMutableArray *students = nil;
 94     
 95     // 1.定义sql语句
 96     const char *sql = "select id, name, age from t_student;";
 97     
 98     // 2.定义一个stmt存放结果集
 99     sqlite3_stmt *stmt = NULL;
100     
101     // 3.检测SQL语句的合法性
102     int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);
103     if (result == SQLITE_OK) {
104         NSLog(@"查询语句是合法的");
105         students = [NSMutableArray array];
106         
107         // 4.执行SQL语句，从结果集中取出数据
108         while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据
109             // 获得这行对应的数据
110             
111             IWStudent *student = [[IWStudent alloc] init];
112             
113             // 获得第0列的id
114             student.ID = sqlite3_column_int(stmt, 0);
115             
116             // 获得第1列的name
117             const unsigned char *sname = sqlite3_column_text(stmt, 1);
118             student.name = [NSString stringWithUTF8String:(const char *)sname];
119             
120             // 获得第2列的age
121             student.age = sqlite3_column_int(stmt, 2);
122             
123             // 添加到数组
124             [students addObject:student];
125         }
126     } else {
127         NSLog(@"查询语句非合法");
128     }
129     
130     return students;
131 }
132 
133 + (NSArray *)studentsWithCondition:(NSString *)condition
134 {
135     // 0.定义数组
136     NSMutableArray *students = nil;
137     
138     // 1.定义sql语句
139     const char *sql = "select id, name, age from t_student where name like ?;";
140     
141     // 2.定义一个stmt存放结果集
142     sqlite3_stmt *stmt = NULL;
143     
144     // 3.检测SQL语句的合法性
145     int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);
146     if (result == SQLITE_OK) {
147         NSLog(@"查询语句是合法的");
148         students = [NSMutableArray array];
149         
150         // 填补占位符的内容
151         NSString *newCondition = [NSString stringWithFormat:@"%%%@%%", condition];
152 //        NSLog(@"%@", newCondition);
153         sqlite3_bind_text(stmt, 1, newCondition.UTF8String, -1, NULL);
154         
155         // 4.执行SQL语句，从结果集中取出数据
156         while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据
157             // 获得这行对应的数据
158             
159             IWStudent *student = [[IWStudent alloc] init];
160             
161             // 获得第0列的id
162             student.ID = sqlite3_column_int(stmt, 0);
163             
164             // 获得第1列的name
165             const unsigned char *sname = sqlite3_column_text(stmt, 1);
166             student.name = [NSString stringWithUTF8String:(const char *)sname];
167             
168             // 获得第2列的age
169             student.age = sqlite3_column_int(stmt, 2);
170             
171             // 添加到数组
172             [students addObject:student];
173         }
174     } else {
175         NSLog(@"查询语句非合法");
176     }
177     
178     return students;
179 }
180 @end

IWViewController.m

  1 //
  2 //  IWViewController.m
  3 //  01-SQLite的基本使用
  4 //
  5 //  Created by apple on 14-5-22.
  6 //  Copyright (c) 2014年 itcast. All rights reserved.
  7 //
  8 
  9 #import "IWViewController.h"
 10 #import "IWStudent.h"
 11 #import "IWStudentTool.h"
 12 
 13 //Core Data : 苹果官方自带，可以让程序员不用写任何一句SQL
 14 //FMDB
 15 
 16 @interface IWViewController () <UISearchBarDelegate>
 17 
 18 /*
 19 - (IBAction)insert;
 20 - (IBAction)update;
 21 - (IBAction)delete;
 22 - (IBAction)query;
 23 */
 24 
 25 @property (nonatomic, strong) NSArray *students;
 26 @end
 27 
 28 @implementation IWViewController
 29 
 30 - (NSArray *)students
 31 {
 32     if (_students == nil) {
 33         _students = [IWStudentTool students];
 34     }
 35     return _students;
 36 }
 37 
 38 - (void)viewDidLoad
 39 {
 40     [super viewDidLoad];
 41     
 42     UISearchBar *searchBar = [[UISearchBar alloc] initWithFrame:CGRectMake(0, 0, 320, 44)];
 43     searchBar.delegate = self;
 44     self.tableView.tableHeaderView = searchBar;
 45 }
 46 
 47 #pragma mark - 搜索框代理
 48 - (void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText
 49 {
 50     self.students = [IWStudentTool studentsWithCondition:searchText];
 51     [self.tableView reloadData];
 52 }
 53 
 54 #pragma mark - tableView代理方法
 55 - (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
 56 {
 57     return self.students.count;
 58 }
 59 
 60 - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
 61 {
 62     // 1.创建cell
 63     static NSString *ID = @"student";
 64     UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:ID];
 65     if (cell == nil) {
 66         cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:ID];
 67     }
 68     
 69     // 2.设置cell的数据
 70     IWStudent *stu = self.students[indexPath.row];
 71     cell.textLabel.text = stu.name;
 72     cell.detailTextLabel.text = [NSString stringWithFormat:@"%d", stu.age];
 73     
 74     return cell;
 75 }
 76 
 77 /*
 78 - (IBAction)insert
 79 {
 80     for (int i = 0; i<30; i++) {
 81         // 创建学生
 82         IWStudent *student = [[IWStudent alloc] init];
 83         student.name = [NSString stringWithFormat:@"Jack-%d", arc4random()%100];
 84         student.age = arc4random()%100;
 85         
 86         // 添加学生
 87         BOOL result = [IWStudentTool addStudent:student];
 88         
 89         if (result) {
 90             NSLog(@"添加成功");
 91         }
 92     }
 93 }
 94 
 95 - (IBAction)update
 96 {
 97 
 98 }
 99 
100 - (IBAction)delete
101 {
102     
103 }
104 
105 - (IBAction)query
106 {
107     NSArray *students = [IWStudentTool students];
108     
109     for (IWStudent *stu in students) {
110         NSLog(@"%d %@ %d", stu.ID, stu.name, stu.age);
111     }
112 }
113  */
114 @end