SQLite ：轻量级数据库，与其他数据库管理系统不同，其安装和运行非常简单，我们以创建Student类为例 通过SQLite数据库实现对Student实例变量的增，删，改，查等操作
Student类
#import <Foundation/Foundation.h>
@interface Student : NSObject
@property(nonatomic,strong)NSString *name;
@property(nonatomic,strong)NSString *gender;
@property(nonatomic,assign)int age;
@property(nonatomic,assign)int Stu_ID;
-(instancetype)initWithName:(NSString *)name
age:(int)age
gender:(NSString *)gender
stu_ID:(int)stu_ID;
@end
#import "Student.h"
@implementation Student
-(void)setValue:(id)value forUndefinedKey:(NSString *)key{
}
//重写description
-(NSString *)description{
return [NSString stringWithFormat:@"%@,%@,%d,%d",_name,_gender,_age,_Stu_ID];
}
//初始化
-(instancetype)initWithName:(NSString *)name age:(int)age gender:(NSString *)gender stu_ID:(int)stu_ID{
if (self = [super init]) {
_name = name;
_age = age;
_gender = gender;
_Stu_ID = stu_ID;
}return self;
}
@end
SQLite创建Database 声明和实现基本的方法 代码如下：
#import <Foundation/Foundation.h>
@class Student;
@interface Database : NSObject
//创建单例
+(instancetype)sharedDataBase;
-(void)openDB;
-(void)closeDB;
//添加
-(void)insertStudent:(Student *)stu;
//删除
-(void)deleteStudent:(int )stu_ID;
//修改
-(void)updataStudentWithGender:(NSString *)gender andStu_ID:(int)stu_ID;
//选择全部
-(NSArray *)selectAll;
//选择学生单个
-(Student *)selectStudentWithStu_ID:(int)stu_ID;
@end
Database.m 以下对数据操作的各个方法做了较为详细的介绍
#import "Database.h"
#import <sqlite3.h>
#import "Student.h"
@implementation Database
static Database *dataBase = nil;
+(instancetype)sharedDataBase{
//加锁
@synchronized(self) {
if (nil == dataBase) {
dataBase = [[Database alloc]init];
[dataBase openDB];//打开数据库
}
}
return dataBase;
}
#pragma mark--------//创建数据库对象
static sqlite3 *db = nil;
-(void)openDB{
//如果数据库已经打开 则不需要执行后面的操作 直接return
if (db!= nil) {
return;
}
//创建保存数据库的路径
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
documentPath = [documentPath stringByAppendingString:@"/LOClass.sqlite"];
NSLog(@"%@",documentPath);
//如果改数据库存在 则直接打开 否则 自动创建一个数据库
int result = sqlite3_open([documentPath UTF8String], &db);
if (result == SQLITE_OK) {
// NSLog(@"数据库成功打开");
//建表
NSString *sql = @"CREATE TABLE Class43 (Stu_ID INTEGER PRIMARY KEY NOT NULL UNIQUE, name TEXT NOT NULL, gender TEXT NOT NULL DEFAULT M, age INTEGER NOT NULL);";
//执行语句
sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
}else{
NSLog(@"%d",result);
}
}
//关闭数据库
-(void)closeDB{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"数据库关闭成功");
//当关闭数据库的时候将数据库置为nil 是因为打开数据库的时候需要使用nil做判断 循环的理论
db = nil; //数据库是在静态去
}else{
NSLog(@"数据库关闭失败%d",result);
}
}
//插入单个student对象
-(void)insertStudent:(Student *)stu{
// 1 打开数据库
[self openDB];
// 2 创建跟随指针（也叫伴随指针）
sqlite3_stmt *stmt = nil;
// 3 准备SQL语句 问好就是占位 ？？？？？
NSString *sqlString = @"insert into Class43 (Stu_ID, name, gender, age) values (?, ?, ?, ?)";
// 4 验证SQL语句的正确性
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
// 5 绑定
if (result == SQLITE_OK) {
NSLog(@"数据库添加成功");
//一旦SQL语句没有问题就要开始绑定数据替换 ？
#pragma mark ---第一个参数是跟随指针 第二个参数是 ？的顺序 是从1开始的-第三个是要绑定的值--
sqlite3_bind_int(stmt, 1, stu.Stu_ID);
sqlite3_bind_text(stmt, 2, [stu.name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 3, [stu.gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 4, stu.age);
//6 单步执行 将伴随指针传进去
sqlite3_step(stmt);
}else{
NSLog(@"数据库添加失败%d",result);
}
//7 释放跟随指针占用的内存
sqlite3_finalize(stmt);
}
//删除学生对象
-(void)deleteStudent:(int)stu_ID{
[self openDB];
sqlite3_stmt *stmp = nil;
NSString *sqlString = @"delete from Class43 where Stu_ID = ?";
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmp, NULL);
if (result == SQLITE_OK) {
NSLog(@"数据库是删除成功");
//开始绑定
sqlite3_bind_int(stmp, 1, stu_ID);
sqlite3_step(stmp);
}else{
NSLog(@"数据库删除失败");
}
sqlite3_finalize(stmp);
}
//更新学生信息 在此根据学号改变学生性别
-(void)updataStudentWithGender:(NSString *)gender andStu_ID:(int)stu_ID{
[self openDB];
sqlite3_stmt *stmt = nil;
NSString *sql = @"update Class43 set gender = ? where Stu_ID = ?";
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"修改成功");
sqlite3_bind_text(stmt, 1,[gender UTF8String], -1, nil);
sqlite3_bind_int(stmt, 2, stu_ID);
sqlite3_step(stmt);
}else{
NSLog(@"修改失败");
}
sqlite3_finalize(stmt);
}
//查询所有数据库中
-(NSArray *)selectAll{
[self openDB];
sqlite3_stmt *stmt = nil;
NSString *sql = @"select * from Class43";
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"查询全部成功");
//创建可变数组用来存放查询到的学生
NSMutableArray *muArray = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
//根据SQL语句将搜索到的符合条件的值取出来
//0 代表数据库表的第一列
int stu_id =sqlite3_column_int(stmt, 0);
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
int age = sqlite3_column_int(stmt, 3);
//将取出来的信息 赋值给学生的Model
Student *stu = [[Student alloc]initWithName:name age:age gender:gender stu_ID:stu_id];
//将学生添加到可变数组里面
[muArray addObject:stu];
}
sqlite3_finalize(stmt);
return muArray;
}else{
NSLog(@"查询全部失败%d",result);
}
sqlite3_finalize(stmt);
return nil;
}
//根据学生的学号来进行查询单个学生对象
-(Student *)selectStudentWithStu_ID:(int)stu_ID{
[self openDB];
sqlite3_stmt *stmt = nil;
NSString *str = @"select * from Class43 where Stu_ID =?";
int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"查询单个学生完成");
sqlite3_bind_int(stmt, 1, stu_ID);
Student *stu = [Student new];
while (sqlite3_step(stmt)== SQLITE_ROW) {
int stu_id = sqlite3_column_int(stmt, 0);
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
int age = sqlite3_column_int(stmt, 3);
stu.Stu_ID = stu_id;
stu.name = name;
stu.age = age;
stu.gender = gender;
sqlite3_finalize(stmt);
return stu;
}
}
else{
NSLog(@"查询数据库单个学生失败%d",result);
}
sqlite3_finalize(stmt);
return nil;
}
@end
下面我们在ViewController.m 中实现数据的 增 删 改 查 的功能
#import "ViewController.h"
#import "Database.h"
#import "Student.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// [[Database sharedDataBase]openDB];
}
- (IBAction)add:(id)sender {
NSLog(@"增加");
Student *stu = [Student new];
stu.name = @"刘高见";
stu.gender = @"男";
stu.Stu_ID = 1;
stu.age = 18;
Student *stu1 = [[Student alloc]initWithName:@"王" age:21 gender:@"女" stu_ID:2];
Student *stu2 = [[Student alloc]initWithName:@"刘" age:21 gender:@"男" stu_ID:3];
Student *stu3 = [[Student alloc]initWithName:@"赵" age:21 gender:@"女" stu_ID:4];
Student *stu4 = [[Student alloc]initWithName:@"谢" age:21 gender:@"男" stu_ID:5];
Database *database =[Database sharedDataBase];
[database insertStudent:stu];
[database insertStudent:stu1];
[database insertStudent:stu2];
[database insertStudent:stu3];
[database insertStudent:stu4];
}
- (IBAction)delete:(id)sender {
// NSLog(@"删除");
[[Database sharedDataBase]deleteStudent:3];
}
- (IBAction)selectAll:(id)sender {
// NSLog(@"查询所有");
for (Student *stu in [[Database sharedDataBase]selectAll]) {
NSLog(@"%@",stu);
}
}
- (IBAction)selectSingle:(id)sender {
// NSLog(@"查询单个");
Student *stu =[Student new];
stu = [[Database sharedDataBase]selectStudentWithStu_ID:1];
NSLog(@"%@",stu);
}
- (IBAction)updata:(id)sender {
// NSLog(@"修改");
Database *database = [Database sharedDataBase];
[database updataStudentWithGender:@"女" andStu_ID:5];
NSLog(@"%@",[[Database sharedDataBase]selectStudentWithStu_ID:5]);
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
以上就是本文的全部内容 期待大家的批评指导~~~~
网友评论