iOS：面向对象的思想使用sqlite数据库

SQLite支持的常见数据类型如下所示。
–INTEGER 有符号的整数类型
–REAL 浮点类型
–TEXT 字符串类型,采用UTF-8和UTF-16字符编码
–BLOB 二进制大对象类型,能够存放任何二进制数据
 
(C语言中)使用步骤：
1.新建项目时,先导入系统框架(C语言). （libsqlite3）
 
2.头文件#import<sqlite3.h>
 
3. sqlite3_open(fileName.UTF8String, &_db); 打开或者创建一个数据

    *_db自己定义一个sqlite3的成员变量.进行增删改查时要用

 
4.sqlite3_exec(_db, sql, NULL, NULL,&error);  //不带结果集的语句，只是对表做操作，不会返回出结果
*该函数可进行insert,delete,update操作.
 
5.查询操作select. //带结果集的查询语句，会返回出结果，从表中查询到的数据都会放到stmt结构体中
*sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);做查询前准备,检测SQL语句是否正确.
*sqlite3_step(stmt) 提取查询到的数据,一次提取一条.//通过循环可以取出所有数据
*sqlite3_column_text(stmt, 0)取出第0列的数据.
 
6.关闭数据库 sqlite3_close(sqlite3 *);
 
————————————————————————————————————————————————————
————————————————————————————————————————————————————
执行带参数的SQL语句

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);

 

单例模式：（这里主要用来保证初始化的数据库是唯一的，只要创建了一次，那么它就不会被再创建）

+ (NoteDAO*)sharedManager 

{ 

  static dispatch_once_t once; 

  dispatch_once(&once, ^{ 

  sharedManager = [[self alloc] init]; 

  [sharedManager   createEditableCopyOfDatabaseIfNeeded]; }

  );

   return sharedManager;

 }

 

举例代码如下：

Student.h

 1 #import <Foundation/Foundation.h>
 2 
 3 @interface Student : NSObject
 4 @property (assign,nonatomic)NSInteger ID;
 5 @property (copy,nonatomic)NSString *name;
 6 @property (assign,nonatomic)NSInteger age;
 7 @property (assign,nonatomic)char gender;
 8 @property (assign,nonatomic)float math;
 9 @property (assign,nonatomic)float english;
10 @property (assign,nonatomic)float chinese;
11 @end

 

Student.m

1 #import "Student.h"
2 
3 @implementation Student
4 -(NSString*)description
5 {
6     return [NSString stringWithFormat:@"ID:%ld,name:%@,age:%ld,gender:%c,math:%.2lf,english:%.2lf,chinese:%.2lf",_ID,_name,_age,_gender,_math,_english,_chinese];
7 }
8 @end

 

StudentDAO.h(对每一个实体类的数据库封装起来，就采用这种XXXDAO.h/m的命名风格)

 1 #import <Foundation/Foundation.h>
 2 #import <sqlite3.h>
 3 @class Student;
 4 @interface StudentDAO : NSObject
 5 {
 6     sqlite3 *_db;
 7 }
 8 //单例的方法
 9 +(StudentDAO*)shareManager;
10 
11 //初始化数据库：创建表，添加数据
12 -(void)initDb;
13 
14 //添加学生记录
15 -(BOOL)addStudent:(Student *)stu;
16 
17 //删除学生记录
18 -(BOOL)deleteStudentByName:(NSString *)name;
19 
20 //修改学生记录
21 -(BOOL)updateStudnet:(Student*)stu;
22 
23 //查询学生记录
24 //1.查询全部
25 -(NSArray *)queryStudentAll;
26 //2.查询符合条件的
27 -(Student *)queryStudentByName:(NSString*)name;
28 
29 @end

 

StudentDAO.m

  1 #import "StudentDAO.h"
  2 #import "Student.h"
  3 
  4 
  5 static StudentDAO *instance = nil;
  6 @implementation StudentDAO
  7 
  8 //对象只创建一次
  9 +(StudentDAO*)shareManager
 10 {
 11     static dispatch_once_t once;
 12     dispatch_once(&once, ^{
 13         //只执行一次
 14         instance = [StudentDAO new];
 15         
 16         [instance initDb];
 17     });
 18      
 19     return instance;
 20 }
 21 
 22 //执行不返回结果集的sql语句
 23 -(BOOL)execSql:(NSString *)sql
 24 {
 25     char *errmsg = NULL;
 26     //增删改操作
 27     sqlite3_exec(_db, [sql UTF8String], NULL, NULL, &errmsg);
 28     
 29     if(errmsg)
 30     {
 31         return NO;
 32     }
 33     else
 34     {
 35         return YES;
 36     }
 37 }
 38 //创建数据库文件路径
 39 -(NSString*)pathForDB
 40 {
 41     NSArray *dirs = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 42     NSString *documentPath = [dirs lastObject];
 43     
 44     //NSLog(@"%@",documentPath);
 45     
 46     return [documentPath stringByAppendingPathComponent:@"student.db"];
 47 }
 48 
 49 //初始化数据库：创建表，添加数据
 50 -(void)initDb
 51 {
 52     //打开数据库
 53     //sqlite3_open(fileName.UTF8String,&_db);打开或创建一个数据
 54     //*_db自己定义一个sqlite3的成员变量.进行增删改查时要用
 55     if(sqlite3_open([[self pathForDB] UTF8String], &_db) == SQLITE_OK)
 56     {
 57         //创建学生表
 58         if([self execSql:@"create table student (id integer primary key autoincrement,name text,age integer,gender integer,math real,english real,chinese real)"])
 59         {
 60             //表中添加测试数据
 61             for(int i=0; i<5; i++)
 62             {
 63                 Student *stu = [Student new];
 64                 stu.name = [NSString stringWithFormat:@"stu%d",i+1];
 65                 stu.age = 20+i;
 66                 stu.gender = 'M';
 67                 stu.math = 80+i;
 68                 stu.english = 90+i;
 69                 stu.chinese = 80+i;
 70                 
 71                 [self addStudent:stu];
 72             }
 73         }
 74         //关闭数据库
 75        sqlite3_close(_db);
 76     }
 77 }
 78 
 79 //添加学生记录
 80 -(BOOL)addStudent:(Student *)stu
 81 {
 82     NSString *insertSql = @"insert into student (name,age,gender,math,english,chinese) values(?,?,?,?,?,?)";
 83     
 84     //打开数据库
 85     if(sqlite3_open([[self pathForDB]UTF8String], &_db) == SQLITE_OK)
 86     {
 87         //准备结果集
 88         //提取数据一次一条
 89         sqlite3_stmt *Pstmt = NULL;
 90         //查询前准备，看是否正确
 91         if(sqlite3_prepare_v2(_db, [insertSql UTF8String], -1, &Pstmt, NULL) == SQLITE_OK)
 92         {
 93             //绑定参数
 94             sqlite3_bind_text(Pstmt, 1, [stu.name UTF8String], -1, NULL);
 95             sqlite3_bind_int(Pstmt, 2, (int)stu.age);
 96             sqlite3_bind_int(Pstmt, 3, stu.gender);
 97             sqlite3_bind_double(Pstmt, 4, stu.math);
 98             sqlite3_bind_double(Pstmt, 5, stu.english);
 99             sqlite3_bind_double(Pstmt, 6, stu.chinese);
100             
101             //执行SQL语句
102             if(sqlite3_step(Pstmt) == SQLITE_DONE)
103             {
104                 return YES;
105             }
106         }
107         //清理结果集
108         sqlite3_finalize(Pstmt);
109         
110         //关闭数据库
111         sqlite3_close(_db);
112     }
113     return NO;
114 }
115 
116 //删除学生记录
117 -(BOOL)deleteStudentByName:(NSString *)name
118 {
119     NSString *sql = @"delete from student where name = ?";
120     
121     //打开数据库
122     if(sqlite3_open([[self pathForDB]UTF8String], &_db) == SQLITE_OK)
123     {
124         //准备结果集
125         sqlite3_stmt *Pstmt = NULL;
126         if(sqlite3_prepare_v2(_db, [sql UTF8String], -1, &Pstmt, NULL)== SQLITE_OK)
127         {
128             //绑定参数
129             sqlite3_bind_text(Pstmt, 1, [name UTF8String], -1, NULL);
130             
131             
132             //执行SQL语句
133             if(sqlite3_step(Pstmt) == SQLITE_DONE)
134             {
135                 return YES;
136             }
137         }
138         //清理结果集
139         sqlite3_finalize(Pstmt);
140         
141         //关闭数据库
142         sqlite3_close(_db);
143     }
144     return NO;
145 }
146 
147 //修改学生记录
148 -(BOOL)updateStudnet:(Student*)stu
149 {
150     NSString *sql = @"update student set math = ? where name = ?";
151     
152     //打开数据库
153     if(sqlite3_open([[self pathForDB]UTF8String], &_db) == SQLITE_OK)
154     {
155         //准备结果集
156         sqlite3_stmt *Pstmt = NULL;
157         if(sqlite3_prepare_v2(_db, [sql UTF8String], -1, &Pstmt, NULL) == SQLITE_OK)
158         {
159             //绑定参数
160             sqlite3_bind_double(Pstmt,1,stu.math);
161             sqlite3_bind_text(Pstmt, 2, [stu.name UTF8String], -1, NULL);
162             
163             //执行SQL语句
164             if(sqlite3_step(Pstmt) == SQLITE_DONE)
165             {
166                 return YES;
167             }
168         }
169         //清理结果集
170         sqlite3_finalize(Pstmt);
171         
172         //关闭数据库
173         sqlite3_close(_db);
174     }
175     return NO;
176 }
177 
178 //查询学生记录
179 //1.查询全部
180 -(NSArray *)queryStudentAll
181 {
182     NSMutableArray *arrayM = [NSMutableArray array];
183     
184     //打开数据库
185     if(sqlite3_open([[self pathForDB]UTF8String], &_db) == SQLITE_OK)
186     {
187         //准备结果集
188         sqlite3_stmt *Pstmt = NULL;
189         if(sqlite3_prepare_v2(_db, "select * from student", -1, &Pstmt, NULL) == SQLITE_OK)
190         {
191             //遍历结果集
192             while (sqlite3_step(Pstmt) == SQLITE_ROW)
193             {
194                 Student *stu = [Student new];
195                 
196                 stu.ID = sqlite3_column_int(Pstmt, 0);
197                 stu.name = [NSString stringWithFormat:@"%s",sqlite3_column_text(Pstmt, 1)];
198                 stu.age = sqlite3_column_int(Pstmt, 2);
199                 stu.gender = sqlite3_column_int(Pstmt, 3);
200                 stu.math = sqlite3_column_double(Pstmt, 4);
201                 stu.english = sqlite3_column_double(Pstmt, 5);
202                 stu.chinese = sqlite3_column_double(Pstmt, 6);
203                 
204                 [arrayM addObject:stu];
205             }
206         }
207         //清理结果集
208         sqlite3_finalize(Pstmt);
209         
210         //关闭数据库
211         sqlite3_close(_db);
212     }
213     return arrayM;
214 }
215 //2.查询符合条件的
216 -(Student *)queryStudentByName:(NSString*)name
217 {
218     Student *stu;
219     NSString *sql = @"select * from student where name = ?";
220     
221     //打开数据库
222     if(sqlite3_open([[self pathForDB]UTF8String], &_db) == SQLITE_OK)
223     {
224         //准备结果集
225         sqlite3_stmt *Pstmt = NULL;
226         if(sqlite3_prepare_v2(_db, [sql UTF8String], -1, &Pstmt, NULL) == SQLITE_OK)
227         {
228             //绑定参数
229             sqlite3_bind_text(Pstmt, 1, [name UTF8String], -1, NULL);
230             
231             //执行SQL语句
232             if(sqlite3_step(Pstmt) == SQLITE_ROW)
233             {
234                 stu = [Student new];
235                 
236                 stu.ID = sqlite3_column_int(Pstmt, 0);
237                 stu.name = [NSString stringWithFormat:@"%s",sqlite3_column_text(Pstmt, 1)];
238                 stu.age = sqlite3_column_int(Pstmt, 2);
239                 stu.gender = sqlite3_column_int(Pstmt, 3);
240                 stu.math = sqlite3_column_double(Pstmt, 4);
241                 stu.english = sqlite3_column_double(Pstmt, 5);
242                 stu.chinese = sqlite3_column_double(Pstmt, 6);
243             }
244         }
245         
246         //清理结果集
247         sqlite3_finalize(Pstmt);
248         
249         //关闭数据库
250         sqlite3_close(_db);
251     }
252     return stu;;
253 }
254 @end

 

ViewController.m

 1 #import "ViewController.h"
 2 #import "Student.h"
 3 #import "StudentDAO.h"
 4 
 5 @interface ViewController ()
 6 
 7 @end
 8 
 9 @implementation ViewController
10 
11 - (void)viewDidLoad {
12     [super viewDidLoad];
13     //测试stuDAO
14     //创建单例对象
15     StudentDAO *stuDAO = [StudentDAO shareManager];
16     
17     
18     //查询所有的记录
19     NSArray *students = [stuDAO queryStudentAll];
20     NSLog(@"%@",students);
21     
22     //删除一条记录
23     [stuDAO deleteStudentByName:@"stu3"];
24     
25     //修改记录
26     Student *stu = [Student new];
27     stu.name = @"stu4";
28     stu.math = 94.5f;
29     [stuDAO updateStudnet:stu];
30     
31     
32     //通过名字查询学生
33     Student *stu2 = [stuDAO queryStudentByName:@"stu5"];
34     NSLog(@"%@",stu2);
35     
36     //查询所有的记录
37     students = [stuDAO queryStudentAll];
38     NSLog(@"%@",students);
39 }
40 @end

 

 

posted @ 2015-09-20 23:15  XYQ全哥  阅读(399)  评论(0)  编辑  收藏  举报