一、什么是SQLite
SQLite是一款轻型的嵌入式数据库,它占用资源非常的低,处理速度快,非常适合用于移动端开发。
二、使用
创建DataBaseHandle.h & DataBaseHandle.m
DataBaseHandle.h
#import <Foundation/Foundation.h>
#import "Student.h"
@interface DataBaseHandle : NSObject
+ (DataBaseHandle *)shareDataBase;
- (void)openDB;
- (void)closeDB;
//添加数据
- (void)insertNewStudent:(Student *)student;
/**
* 根据学号查询学生
*/
- (Student *)selectStudentWithNumber:(NSInteger)number;
/**
* 查询表中所有数据
*/
- (NSMutableArray *)selectAllStudents;
/**
* 根据学号删除
*/
- (void)deleteStudentWithNumber:(NSInteger)number;
- (void)updateStudent:(NSString *)gender WithNumber:(NSInteger)number;
@end
DataBaseHandle.m
+ (DataBaseHandle *)shareDataBase {
@synchronized (self){
if (handle == nil) {
handle = [[DataBaseHandle alloc] init];
// [handle closeDB];
}
}
return handle;
}
sqlite3 *db = nil;
打开数据库
- (void)openDB {
NSString *str = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
NSString *path = [str stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",path);
//打开数据库
//UTF8String 将oc字符串转化为C语言字符串
//方法执行完会返回一个数据库对象,这个对象已被初始化
int result = sqlite3_open([path UTF8String], &db);
//如果等于SQLITE_OK说明sql语句执行成功
if (result == SQLITE_OK) {
NSLog(@"数据库打开成功");
//创建表格
NSString *sqlString = @"create table if not exists Student (number integer primary key autoincrement,name text,gender text,age integer)";
int result = sqlite3_exec(db, [sqlString UTF8String], NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"见表成功");
}
} else {
NSLog(@"数据库打开失败");
}
}
关闭数据库
- (void)closeDB {
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"关闭成功");
}else {
NSLog(@"关闭失败");
}
}
插入数据
- (void)insertNewStudent:(Student *)student {
[self openDB];
//准备sql语句
NSString *sqlString = @"insert into Student (name,gender, age) values (?, ?, ?)";
/*第一个参数,数据库指针,
第二个参数,sql语句
第三个参数,sql语句的长度,写成-1,自动计算
第四个参数,创建管理sql语句的类,statement
第五个参数,预留参数
*/
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"添加语句成功");
//绑定参数
//绑定的参数:1.管理类指针,2.第几个问号,3.绑定的数据, 4.绑定数据的长度 -1 5.
sqlite3_bind_text(stmt, 1, [student.name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [student.gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 3, (int)student.age);
/**/
sqlite3_step(stmt);
} else {
NSLog(@"添加语句失败");
}
sqlite3_finalize(stmt);
[self closeDB];
}
查询数据
- (NSMutableArray *)selectAllStudents {
[self openDB];
NSString *sqlString = @"select * from student";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, nil);
if (result == SQLITE_OK) {
NSLog(@"查询全部成功");
//循环的条件:下一行还有数据,这时就能一直循环下去
NSMutableArray *array = [NSMutableArray arrayWithCapacity:0];
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
NSInteger age = sqlite3_column_int(stmt, 3);
Student *stu = [[Student alloc] init];
stu.name = name;
stu.age = age;
stu.gender = gender;
[array addObject:stu];
[stu release];
}
sqlite3_finalize(stmt);
[self closeDB];
return array;
} else {
NSLog(@"error");
return nil;
}
}
- (Student *)selectStudentWithNumber:(NSInteger)number {
[self openDB];
NSString *sqlString = @"select * from Student where number = ?";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"查询成功");
//绑定参数
sqlite3_bind_int(stmt, 1, (int)number);
Student *student = [[[Student alloc] init]autorelease];
while (sqlite3_step(stmt) == SQLITE_ROW) {
student.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
student.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
student.age = sqlite3_column_int(stmt, 3);
}
sqlite3_finalize(stmt);
[self closeDB];
return student;
} else {
NSLog(@"不OK");
return nil;
}
}
删除数据
- (void)deleteStudentWithNumber:(NSInteger)number {
[self openDB];
NSString *sqlString = @"delete from Student where number = ?";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
sqlite3_bind_int(stmt, 1, (int)number);
//执行sql语句
sqlite3_step(stmt);
}
//释放stmt的内存资源
sqlite3_finalize(stmt);
[self closeDB];
}
修改数据
- (void)updateStudent:(NSString *)gender WithNumber:(NSInteger)number {
[self openDB];
NSString *sqlString = @"update Student set gender = ? where number = ?";
sqlite3_stmt *stmt = nil;
i nt result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
sqlite3_bind_int(stmt, 2, (int)number);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
[self closeDB];
}