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
程序猿神奇的手，每时每刻，这双手都在改变着世界的交互方式！
本文转自当天真遇到现实博客园博客，原文链接：http://www.cnblogs.com/XYQ-208910/p/4824713.html，如需转载请自行联系原作者
